Как отключить strict trans tables в phpmyadmin

Disabling MySQL strict mode on the server

MySQL Strict Mode controls how invalid or missing values in data changing queries are handled. This includes INSERT , UPDATE and CREATE TABLE statements. With MySQL Strict Mode enabled, which is the default state, invalid or missing data may cause warnings or errors when attempting to process the query.

When Strict Mode is disabled, the same query would have its invalid or missing values adjusted and would produce a simple warning. This may seem like the preferred result; however, with Strict Mode disabled, specific actions may cause unexpected results. For instance, when the value being inserted exceeds the maximum character limit, it is truncated to fit the limit.

For GFI HelpDesk to run on your server, you need to disable Strict Mode in your MySQL database.

This topic provides the instructions to make changes to the configuration to disable the Strict Mode.

To disable the Strict Mode, do the following:

1. Open the my.ini or my.cnf file for editing (the file you have depends on whether you are running Windows or Linux).
2. Find the following line:

How to disable/enable STRICT_TRANS_TABLES

Sambo Member

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

Comments

Giedrius Administrator

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

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 Member

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 Administrator

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 Member

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 Administrator

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 Member

set global sql_mode='';

Giedrius Administrator

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:

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.

How do I disable Strict Mode in MySQL?

Please follow the instructions given below to turn off the MySQL Strict Mode. The latest versions of PHPKB Knowledge Base Management Software does not require MySQL Strict Mode to be turned OFF. It was a requirement for older versions (version 8 or older) of PHPKB software.

1. Disable Strict Mode via my.cnf/my.ini

This method disables it by changing the value of SQL_MODE in my.cnf file (for Linux) OR my.ini file (for windows server) and restarting the MySQL server. my.cnf file can be found in one of a few locations (depending on which distribution you’re using). The most common locations are /etc/my.cnf and /etc/mysql/my.cnf .

NOTE: Make the following changes in the my.ini (for Windows Operating System) or my.cnf (for Linux Operating System). Inside that file, look for a heading like [mysqld] and then look for the value of sql_mode. It might look like this (the actual value of sql_mode may vary):

1. Look for the following line:
   sql-mode = «STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION»
2. You can change the value of sql_mode to NO_ENGINE_SUBSTITUTION to completely disable strict mode, but you may want to look up each mode that is configured before disabling it or you can simply change it to:
   sql-mode=»» (i.e. Blank)
   If sql_mode isn’t set, you can add it under the [mysqld] heading, then save the file, and restart MySQL.
3. Restart the MySQL Service.

2. To Disable Strict Mode via SQL

This method allows you to disable the strict mode on your MySQL server by running the following command.

$ mysql -u root -p -e "SET GLOBAL sql_mode = ’NO_ENGINE_SUBSTITUTION’;"

Now, you can verify that the mode is set by running the following:

$ mysql -u root -p -e "SELECT @@GLOBAL.sql_mode;"

That’s all! Full documentation for setting MySQL modes is available on the Server SQL Modes page in the MySQL Documentation. A description of all of the modes is also available on that page.

• Applicable To: Standard Edition, Standard (Multi-Language) Edition, Enterprise Edition (MySQL), Enterprise Multi-Language Edition (MySQL), Enterprise Edition (SQL Server), Enterprise Multi-Language Edition (SQL Server)

30 people found this article helpful what about you?