Category Archives: Databases

The solution of error “ERROR 1067 (42000) at line 211: Invalid default value for ‘blablabla'”

I noticed once when importing a sql file the following error:

ERROR 1067 (42000) at line 211: Invalid default value for ‘blablabla’

It arises because new versions of MySQL server use strict mode and parameters such as NO_ZERO_DATE do not allow entering date values like ‘0000-00-00’ into the database.

Connect to mysql server:

mysql -u root -p

Execute a query that displays the values of sql_mode:

show variables like 'sql_mode';

Copy the string with these values and exit mysql:

exit

Open the configuration file for example in the text editor nano (Ctrl+X for exit, y/n for saving or canceling the changes):

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

I did not have sql_mode = in the file, so at the end of the file I inserted the line with the previously copied values, removing NO_ZERO_IN_DATE, NO_ZERO_DATE from it, in my case, the following happened:

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Restart mysql to apply the changes:

sudo service mysql restart

Done, now when importing this error should not be.

SMS sending script via Goip4 gateway

Here is an example of a script written in PHP, for sending SMS messages through the Goip4 gateway.
The script receives data from the SQL database with a query and alternately sends SMS to each number, and also writes an entry about sending it to a special sms table.
Continue reading “SMS sending script via Goip4 gateway”

Change wait_timeout and interactive_timeout in MySQL

wait_timeout – The number of seconds that the server waits for activity in a non-interactive connection before closing it.
At the time of connection, wait_timeout is taken from the global value wait_timeout or interactive_timeout depending on the client type (as defined by the CLIENT_INTERACTIVE connect option for mysql_real_connect ())

Connect to MySQL and see the current value:

mysql -u USER -p
show variables like "wait_timeout";
show variables like "interactive_timeout";
quit;

By default, the values wait_timeout and interactive_timeout are 28800 seconds = 8 hours.
You can set minimum 1, maximum – 31536000, maximum (for Windows) – 2147483.

You can change the value of wait_timeout by executing the SQL query, for example:

set global wait_timeout = 28800;
set global interactive_timeout = 28800;

That the set value has not been reset, it needs to be specified in the file /etc/mysql/my.cnf, in the mysqld block:

[mysqld]
wait_timeout = 28800
interactive_timeout = 28800

Change connect_timeout in MySQL

connect_timeout – the number of seconds that the mysql server waits for the connection package before terminating the connection.

Connect to MySQL and see the current value:

mysql -u USER -p
show variables like "connect_timeout";
quit;

The value of connect_timeout can be specified in the file /etc/mysql/my.cnf, for example:

[mysqld]
connect_timeout=10

In real time, you can change by executing the SQL query (after restarting MySQL it will be reset to the standard or specified in the configuration file):

SET GLOBAL connect_timeout=10;

The standard value is 10, the minimum value is 2, the maximum is 31536000.