The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients, depending on the value of the sql_mode system variable. DBAs can set the global SQL mode to match site server operating requirements, and each application can set its session SQL mode to its own requirements.
Questions tagged [sql-mode]
102 questions
0
votes
1 answer
General error: 1364 with STRICT_TRANS_TABLES disabled
I have a Mariadb 10.2.26 server, with "STRICT_TRANS_TABLES" mode disabled. Only mode enabled is "NO_ENGINE_SUBSTITUTION". But even like this, I am getting "General error: 1364 Field '' doesn't have a default value". Does anyone have any idea why?…
user3199269
- 33
- 1
- 7
0
votes
0 answers
Disabling only_full_group_by in mysql using sql_mode
I have migrated my database from older version to newer one.
I have mysql version 5.7.24 now.
I am getting ERROR 1055 (sql_mode=only_full_group_by) for a query which has group by.
I have set sql_mode='' in both global variable as well as my.cnf file…
0
votes
0 answers
MySQL replication with NULL-able column on Master and NOT NULL on Slave
I got Master table with such column defined: INT DEFAULT NULL; and the Slave having that column defined as NOT NULL;. InnoDB engine.
SQL mode on both servers…
Max32Nov
- 13
- 4
0
votes
1 answer
How to deal with 0000-00-00 00:00:00 datetimes in MySQL 5.7 without changing SQL mode
I've recently switched to MySQL 5.7 which causes troubles around datetimes. I have datetimes in my database with the default value of 0000-00-00 00:00:00. MySQL 5.7 does not seem to like that type of default value. I understand I can set sql_mode =…
bart
- 14,958
- 21
- 75
- 105
0
votes
2 answers
MySQL LEFT OUTER JOIN select best match without changing sql_mode
I'm trying to get a list of students with their best matching subject from subject_inv table. The issue with my query is it requires to change sql_mode. Is there a way to modify this query without changing sql_mode parameter.
SELECT `student`.*,…
Dumindu Madushanka
- 494
- 1
- 9
- 19
0
votes
1 answer
MySQL only_full_group_by error
I am not a DBA and I inherited this query and i'm not quite sure why I'm getting a sql_mode=only_full_group_by error. Especially since the error is talking about result.fullURL and i don't see that in the query. Could someone please explain this to…
ryanpitts1
- 862
- 1
- 16
- 33
0
votes
1 answer
MySQL code that I try to run on RDS gets a syntax error, but in a syntax checker for the same version, it runs fine
So I have an AWS RDS instance with a MySQL 5.6.37 engine, and I'm checking the syntax of my code on sqlfiddle.com with mySQL version 5.6.
The code I put in sqlfiddle is
CREATE TABLE Teacher_Times(
TEACHER_ID TEXT,
BEG_TIME TIME,
END_TIME…
Avi Rudich
- 11
- 3
0
votes
1 answer
Why can't a timestamp field get a default value?
I try the following alter table:
ALTER TABLE test MODIFY COLUMN a timestamp NULL DEFAULT '1970-01-01 00:00:01';
The table structure is:
CREATE TABLE `test` (
`a` timestamp NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
My…
Itay Moav -Malimovka
- 52,579
- 61
- 190
- 278
0
votes
2 answers
mysql 5.7 query with group by error
select caten as category, titleen as title from pages where pshow = 1 group by caten ASC
getting this error:
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mytable.pages.titleen' which is not functionally…
Lilit Tarposhian
- 43
- 6
0
votes
0 answers
Missing value for a non-nullable column was added while insert by pymysql to MariaDB
In the following scenario, what I expected is an exception, but I got a warning:
# MariaDB: 10
create table tm
(
id int auto_increment primary key,
col1 int not null
) ENGINE = InnoDB;
My script:
# Python: 3.6.1
# PyMySQL: 0.7.11
import…
chehsunliu
- 1,559
- 1
- 12
- 22
0
votes
1 answer
Truncate on INSERT as an exception while in strict mode?
I'm using MariaDB 10.2.4. By default, sql_mode includes STRICT_TRANS_TABLES, which is good for the most part. But in specific cases I would like the data to just be truncated. Is there a way to override the sql_mode temporarily, or an option for the…
OsakaWebbie
- 645
- 1
- 7
- 21
0
votes
1 answer
SQL_MODE not working on MySQL 5.7.18
I'm getting the following error from my PHP application:
Incorrect SQL query (Database) In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'db_20172201.cm_order.buyer'; this is incompatible with…
500 Server error
- 644
- 13
- 28
0
votes
1 answer
How to disable "ONLY_FULL_GROUP_BY" in sql server
I know that in mysql we can disable ONLY_FULL_GROUP_BY to do aggregations in relation to other fields than the selected ones. But in sql server i don't know how.
Here is the query i tried :
select Arrival_Date, sum(Rate)
from Stay
Group by…
Salim Lyoussi
- 236
- 3
- 9
0
votes
2 answers
How do I aggregate with group by in mysql withou setting sql mode?
This query gets the output I want. In order to run it I have to run
SET sql_mode = '';
Because otherwise I get an error:
SELECT list is not in GROUP BY clause and contains nonaggregated column 'knownLoss.t1.loss' which is not functionally…
Aaron Martin
- 397
- 2
- 6
- 17
0
votes
1 answer
Mysql 5.7 sql-mode for avoid strictness
I have an application working with mysql 5.6
Trying mysql 5.7 there are many problems with strictmode, but setting sql-mode = "" let the app works again.
My doubt is: in future mysql version the feature sql-mode will be mantained? Or it's better try…
user2940867