How to disable/enable STRICT_TRANS_TABLES

Sambo

Hi,

how to disable The sql-mode STRICT_TRANS_TABLES ?
step by step please 

Giedrius

Hey Sambo,

you may check the following article for the explanation to your question:

https://stackoverflow.com/questions/40881773/how-to-turn-on-off-mysql-strict-mode-in-localhost-xampp

If you need specific commands to your server, I would like to ask you to open a ticket in our system, so that we could check what OS you are running, at very least.

Sambo

I am using Cpanel /CentOs7

when i run it it show me

MySQL said: 

#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Giedrius

The commands works for me while I follow the instructions, however, your case could be different. What user do you use to execute commands, which command exactly result in this error code?

Sambo

Giedrius said:

The commands works for me while I follow the instructions, however, your case could be different. What user do you use to execute commands, which command exactly result in this error code?

i only have one user with all  privileges

Giedrius

Can you provide more details? It would help us to help you, if you could just provide the list of commands you have executed before getting this error you mentioned.

If you don't want to provide it here, open a ticket in our system.

Sambo

i only run 

set global sql_mode='';




Giedrius

Once you are connected to your VPS via SSH,  please try connecting to your mysql with "root" user:

mysql -u root -p

Enter "root" user password and you will be in the mysql environment (mysql>), then simply check what is sql_mode, with the following command:

SHOW VARIABLES LIKE 'sql_mode';

Basically, you will see the table as your result, if the table has a value of STRICT_TRANS_TABLES, it means that this option is enabled, so you need to remove the value from this table with the following command:

set global sql_mode='';

This will set your table's value to empty and disable this setting. Like this:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+

Please make sure to perform these commands within the mysql environment and not simply via SSH. I think this moment was missed in the article provided below and the author assumes that the reader understands it intuitively.

Sambo

I did it in phpMyAdmin
sql tab

I did 

SHOW VARIABLES LIKE 'sql_mode';

then get this
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_...

thin i run

set global sql_mode='';
then got this


ErrorSQL query:set global sql_mode='' MySQL said: #1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation





Giedrius

your phpmyadmin user may not have all the privileges, that is why my suggestion was to do it over command line - it works flawlessly. Just follow the tutorial in my previous reply.