3

I an currently using mysql server 5.6 in windows 10. while using mysql, an error keeps appearing that goes as something like this "the following column does not have a default value" i read on the internet that i can solve this problem if i change the sql_mode. It is currently set to 

 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

I took this from "my-default.ini" file in "mysql server 5.6" folder. I read several other answers that say that if you want to permanently change your sql_mode, you should do it through the "my-default.ini" file. So what i did was, i run my notepad as administrator, then i browsed to open "my-default.ini" file that is found in: 

  c:\programs files (x86)\mysql\mysql server 5.6\my-default.ini

when i opened the file, i modified the last line:

  sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

to

  sql_mode=

then i saved the file, closed it. then by using task manager i restarted my sql sever:

  task manager >> services >> mysql56

This is the whole process that should permanently modify my sql_mode. when i look in my "my-default.ini" file, the last line is still modified. but if i log into mysql through the command line and view my sql_mode with the following code:

  select @@sql_mode;

I get that my sql_mode is

  STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

I want all this to be removed (permanently) in order for my error to disappear. IF i try to modify the sql_mode from the command line by using:

  set @@global.sql_mode="";

It worked, but when i restarted MySQL, everything was back as it was before.

Can someone please tell me what i am doing wrong? and if there is another way to solve this problem.

CerebralCortexan
  • 299
  • 2
  • 17
mohamed
  • 31
  • 1
  • 6

4 Answers4

4

UPDATE: Sorry, forgot you were using Windows 10. I'm not sure how to deal with this issue in Windows but maybe try copying my-default.ini to my.ini and changing that setting. Sorry for confusion.

Not sure if you can set your sql_mode to nothing but try changing the value in your /etc/mysql/my.cnf file. If that folder/file doesn't exist, create it and try setting sql_mode under [mysqld] like this:

[mysqld]
sql_mode=

I just did this same thing only I set the sql_mode to NO_ENGINE_SUBSTITUTION.

Cory Harter
  • 124
  • 8
0

If you have MySQL Workbench installed, check the Server Status for the base directory of your instance. You should find a my.ini file there that keeps your changes.

LBJ
  • 1
0

sql_mode need to add to file /etc/init.d/mysql as a argument to start like:

$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" --sql-mode="" $other_args >/dev/null 2>&1 &

Restart MySQL database server to apply new config.

Đàm Văn Hồng
  • 1
0

In MYSQL 8 and ubuntu server 20.xx I had to add:

sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIO

To file /etc/mysql/mysql.conf.d/mysqld.cnf

In order that the setting stay persistent after server restart

Disclaimer:

this was the only place it actually worked, I have no idea what's the good practice where to add these custom settings but it 'ain't stupid if it works'. Somebody can tell me.

I tried the other options I found on forums:

  • my.cnf did not work creating
  • custom cnf did not work
  • adding it to mysql.cnf did not work
GentSVK
  • 324
  • 1
  • 3
  • 12