Category Archives: MYSQL

When mysql creates a table, set timestamp DEFAULT NULL error 1067-Invalid default value for’updated_at’

Problem background:

The structure of the exported database in the mysql server on the online linux server. Want to create a test version locally

After exporting and running on local mysql, it reports error 1067-Invalid default value for

 

The timestamp column needs to be used in the mysql database to store the creation time and update time of the data

For example, to create a background management menu table, only look at the two fields created_at and updated_at

DROP TABLE IF EXISTS `admin_menu`;
CREATE TABLE `admin_menu`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL DEFAULT 0,
  `order` int(11) NOT NULL DEFAULT 0,
  `title` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `icon` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `uri` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `permission` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` timestamp(0) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 15 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Compact;

An error was found after execution

1067-Invalid default value for’updated_at’. The default value of the updated_at field is invalid. The created_ad field type is the datetime date and time type. The default value is NULL and no error occurs.

 At first I thought it was the sql_mode mode problem, but when the sql_mode mode is strict or loose mode, an error will be reported.
The same mode of local and online servers will not work either
sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

 

Next, check the comparison between the online server version and the local version,
Use select VERSION()
Online mysql version: 5.7.22-log
Local mysql version: 5.7.23-log
There is not much difference between the versions, so version problems are excluded. That can only find information.
Solution:
method 1
Since timestamp will report an error, it can be solved by directly using datetime to save the time.
Change the updated_at type timestamp to datetime, the process of creating a table can run normally
Method 2
Since it cannot be NULL, can I change it to a fixed value?
`updated_at` timestamp(0) DEFAULT '1970-01-01 08:00:00',
Set the time to the date corresponding to the unix timestamp of 0 (the default is 1970-01-01 00:00:00, and the Chinese time must be increased by 8).

 

Method 3

Search online, add configuration parameters in the mysql configuration file

[mysqld] Add under node

explicit_defaults_for_timestamp = ON

Restart the mysql database for trial configuration to take effect.

After that, the above data can be executed normally

 

The following is a specific explanation of method three, it is only for record, and it is not necessary to know

Search information online,
mysql5.6.6Previously, the timestamp time type had a default behavior.
TIMESTAMP columns that did not explicitly declare the NULL attribute defaulted to NOT NULL. (And other data types that do not show a declaration of NOT NULL allow NULL values.)
insert inserts a piece of data with a TIMESTAMP column value of NULL, it will automatically store the current timestamp into this timestamp column when it is stored.
In other words, the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes will be automatically assigned.
Each time the record is updated, the timestamp column will be updated to the time value corresponding to the current timestamp
Now after mysql5.6 version, the default behavior of the timestamp field has changed, and there are some more restrictions.
If the timestamp column is set to the default value of NULL,
Default NULL This will give error 1067-Invalid default value for
If you need to make the timestamp column a NULL value when creating a table, you need to set explicit_defaults_for_timestamp to ON
explicit_defaults_for_timestamp is OFF by default, and it can prevent the default behavior of timestamp when it is turned on.

MySQL he server quit without updating PID file [How to Solve]

1 Question

[root@localhost mysql]# /etc/rc.d/init.d/mysql status
MySQLis not running, but lock file (/var/lock/subsys/mysql[FAILED]
[root@localhost mysql]# /etc/rc.d/init.d/mysql start
Starting MySQL…The server quit without updating PID file (/usr/local/mysql/data/localhost.localdomain.pid).[FAILED]

2 Reasons

没有初始化权限表

3 Solutions

#cd /usr/local/mysql（进入mysql安装目录）
#chown -R mysql.mysql .
#su – mysql
$cd server
$scripts/mysql_install_db

4 I solve the process

[root@localhost ~]#cd /usr/local/mysql

[root@localhost mysql]#chown -R mysql.mysql .
[root@localhost mysql]#su – mysql
[mysql@localhost ~]$cd /usr/local/mysql
[mysql@localhost mysql]$scripts/mysql_install_db
Installing MySQL system tables…
OK
Filling help tables…
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

./bin/mysqladmin -u root password ‘new-password’
./bin/mysqladmin -u root -h localhost.localdomain password ‘new-password’

Alternatively you can run:
./bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd ./mysql-test ; perl mysql-test-run.pl

Please report any problems with the ./bin/mysqlbug script!

[mysql@localhost mysql]$/usr/local/mysql/bin/mysqld_safe –user=mysql &
[1] 11767
[mysql@localhost mysql]$ 120502 07:01:17 mysqld_safe Logging to ‘/usr/local/mysql/data/localhost.localdomain.err’.
120502 07:01:17 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
[mysql@localhost mysql]$/etc/rc.d/init.d/mysql status
MySQL running (11830)[OK]
[mysql@localhost mysql]$/etc/rc.d/init.d/mysql start
Starting MySQL[OK]

MySQL: Starting MySQL….. ERROR! The server quit without updating PID file

FROM：http://icesquare.com/wordpress/mysql-starting-mysql-error-the-server-quit-without-updating-pid-file/

This step-by-step guide is mainly for FreeBSD, however the idea is the same for Linux. Every once a while, when I update my FreeBSD box, the system likes to shutdown my MySQL server. Therefore, I need to start it again after the update is done. Unfortunately, the upgrade process is not smooth every time. Sometimes it will throw me some error.

/usr/local/etc/rc.d/mysql.server start

Oh well, I got the following error messages:

Starting MySQL..... ERROR! The server quit without updating PID file.

Sometimes, the message will tell you the exact location of which PID file:

Starting MySQL..... ERROR! The server quit without updating PID file (/var/db/mysql/www.icesquare.com.pid).

There are several solutions to troubleshoot these problems. I will go over each one by one.

Solution 1: Reboot The Computer

Although it sounds simple, but it really works. During the system upgrade, the OS may disable some of your daemons. Instead of troubleshooting each one by one, the easiest way is to start everything over. For example, I experienced this problem today after upgrading theApache and Ruby(Yes, MySQL is not part of the update), and I got this error message afterward. After rebooting the computer, the error message is gone.

Solution 2: Remove Your MySQL Config File

If you have modified your MySQL configuration file, MySQL may not like it few versions after (MySQL is not backward compatibility friendly). It can be the problem of using an unsupported variable, or something similar. The easiest way is to remove your configuration file, and try to start the MySQL server again:

Backup your MySQL configuration first.

mv /etc/my.cnf /etc/my.cnf.backup

And restart the MySQL server again:

/usr/local/share/mysql/mysql.server start

Hopefully you will see the following message:

Starting MySQL. SUCCESS!

Solution 3: Upgrade Your Database File

Sometimes, the newer MySQL doesn’t like the database created in earlier version. I discovered this when I upgrade to MySQL 5.5.7:

Starting MySQL..... ERROR! The server quit without updating PID file (/var/db/mysql/www.icesquare.com.pid).

Since MySQL tells me which PID file causes the problem, I open the file and take a look what’s going on:

sudo tail /var/db/mysql/www.icesquare.com.err

And I saw something interesting:tables: Table ‘mysql.proxies_priv’ doesn’t exist:

101112 10:49:16 InnoDB: Initializing buffer pool, size = 128.0M 101112 10:49:16 InnoDB: Completed initialization of buffer pool 101112 10:49:16 InnoDB: highest supported file format is Barracuda. 101112 10:49:17 InnoDB: 1.1.3 started; log sequence number 1589404 101112 10:49:17 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.proxies_priv' doesn't exist 101112 10:49:17 mysqld_safe mysqld from pid file /var/db/mysql/www.icesquare.com.pid ended

The reason is very simple. MySQL could not open a table created in the earlier version (< 5.7.7) because it is not compatible with the current version. So, we can try to start the MySQL in safe mode through rc.d. First, you can edit the/etc/rc.confand put the following into the file:

mysql_enable="YES" mysql_args="--skip-grant-tables --skip-networking"

Restart MySQL through rc.d:

/usr/local/etc/rc.d/mysql-server start

If you did it right, you should see something like the following:

Starting MySQL.. SUCCESS!

Now, MySQL is already running the safe-mode. We want to perform a MySQL upgrade on all tables:

sudo mysql_upgrade

You should see something like this:

Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock' Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock' mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.general_log OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.servers OK mysql.slow_log OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK Running 'mysql_fix_privilege_tables'... OK

Now, we want to switch the MySQL back to normal mode by commenting the extra options in /etc/rc.conf:

mysql_enable="YES" #mysql_args="--skip-grant-tables --skip-networking"

And restart MySQL through /etc/rc.d:

/usr/local/etc/rc.d/mysql-server restart

Now the MySQL is up and running again!

Happy MySQLing.

[How to Solve] Lost connection to MySQL server at ‘reading i…

When connecting to a MySQL remote host through TCP/IP, error 2013 (HY000): lost connection to MySQL server at ‘reading initial communication packet’, system error: 104 appears.

 

If you directly type MySQL in the Linux shell command line, you can connect to MySQL smoothly and execute the query statement normally, but if you execute stop slave; The problem of error 2013 (HY000): lost connection to MySQL server during query appears at random. If you write the operation command to the script file and then execute the script file, there will be lost connection to MySQL server at ‘reading initial communication packet’, system error: 111

 

If there are errors in remote access by any means, it can be considered that the system has firewall restrictions, but now this strange cramp phenomenon makes people puzzled. The final solution is to add a startup parameter in the [mysqld] section of my. CNF

 

skip-name-resolve

 

The problem disappears. But I can’t figure out why

The reason is that I set skip name resolve in my.cnf, and skip name resolve is to disable DNS resolution, so I can’t use the host name in the authorization table of MySQL, I can only use IP

[Solved] MYSQL Startup Error: server PID file could not be found

[root@centos var]# service mysqld stop

MySQL manager or server PID file could not be found![ FAILED]

Delete superfluous

Notes

#log-bin=mysql-bin

If you kill the process, you still can’t

If the log path is not specified, you can see the error information in the localhost.err file in/data/MySQL

If there is no dead process in the thread, you can first look at the service MySQL status to see the status and delete the superfluous things according to the prompts

Go to/data/MySQL, which is your database directory, and delete the unnecessary items, including localhost. Err and other indexes

If you restart service mysql start several times, you can restart successfully

[Solved] MySql Host is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts…

The solution is as follows:

method 1, online modification to improve the allowable max_ connection_ Number of errors: 

A. Log in to the Mysql database to view max_connection_errors. 
　　mysql>show variables like '%max_connect_errors%'; 

B. Change the number of max_connection_errors to 1000.    
　　mysql>set global max_connect_errors = 1000;
C. To see if the change was successful.                          
　　mysql>show variables like '%max_connect_errors%';

method 2. Use mysqladmin flush hosts command to clean up the hosts file

[root@192-168-7-77 ~]# mysqladmin --socket=/tmp/mysql.sock --port=3306 -uroot -p flush-hosts

Note: Configure master/slave master/slave databases to modify both master and slave databases.

mysql> flush hosts; is also ok

finally, modify my.cnf configuration file 

[root@192-168-7-77 ~]# vi /etc/my.cnf
max_connect_errors = 1000

note: Max_ connect_ Related notes on errors

max_ connect_ Errors is a security related counter value in mysql, which is responsible for preventing too many failed clients from brute force password cracking. max_ connect_ The value of errors has little to do with performance

When this value is set to 10, it means that if a client attempts to connect to the MySQL server, but fails (such as wrong password, etc.) 10 times, MySQL will unconditionally force the client to block the connection. If you want to reset the value of this counter, you must restart the MySQL server or execute MySQL > flush hosts; Orders. When the client successfully connects to the MySQL server once, the max_ connect_ Errors will be cleared

If Max_ connect_ If the setting of errors is too small, the web page may prompt that the database server cannot be connected; If you connect to the database through the MySQL command of SSH, you will return error 1129 (00000): host ‘gateway’ is blocked because of many connection errors; Unblock with ‘mysqladmin flush hosts’ error
function and function

Generally speaking, it is suggested that the database server should not listen to the connection from the network, but only connect through the socket, which can prevent the vast majority of attacks against mysql; If you have to open the network connection of MySQL, you’d better set this value to prevent the attack of exhausting password

[Solved] MYSQL ERROR 1093 – You can’t specify target table ‘readbook’ for update in FROM clause

 

Do PHP development, to write an SQL statement. That is, if there is a corresponding record in the table, the update will be executed, and if there is no record, the insert will be executed

Remember that when doing SQL server before, there was a merge into statement. You can select the operation to be performed, plug-in or update according to the query conditions. But MySQL doesn’t seem to have this function. It is distinguished by whether the primary key exists or not

So I habitually wrote the following sentence:

INSERT INTO readbook (readid,readpage,readcount,bookid,userid,readtime) VALUES ( (SELECT  readid From readbook where
userid=1 and bookid=1 and readpage=5),5,1,1,1,now()) ON duplicate key UPDATE 

readcount=readcount + 1,readtime=now()

The results show that there are errors as follows:

Static analysis.

11 errors were found during the analysis.

Unrecognized keyword. (near "key" at position 185)
Unexpected token. (near "=" at position 209)
Unexpected token. (near "readcount" at position 210)
Unexpected token. (near "+" at position 220)
Unexpected token. (near "1" at position 222)
Unexpected token. (near "," at position 223)
Unexpected token. (near "readtime" at position 224)
Unexpected token. (near "=" at position 232)
Unrecognized keyword. (near "now" at position 233)
Unexpected token. (near "(" at position 236)
Unexpected token. (near ")" at position 237)
SQL Inquiry: Documentation

INSERT INTO readbook (readid,readpage,readcount,bookid,userid,readtime) VALUES ( (SELECT readid From readbook where userid=1 and bookid=1 and readpage=5) ,5,1,1,1,now()) ON duplicate key UPDATE readcount=readcount + 1,readtime=now()

MySQL Back to: Documentation

#1093 - You can't specify target table 'readbook' for update in FROM clause

error code

So change the statement:

INSERT INTO readbook (readid,readpage,readcount,bookid,userid,readtime) VALUES ( (SELECT * from (SELECT readid From readbook where userid=1 and bookid=1 and readpage=5) as a ),5,1,1,1,now()) ON duplicate key UPDATE readcount=readcount + 1,readtime=now()

It indicates that the insertion is successful

The reason for the success is that the 1093 error is: when modifying a table, the subquery cannot be the same table. The solution is to set the subquery to another level and change it into the grandson query of the original table

In fact, in this process, because I am not familiar with MySQL, I encountered many other problems

For example

(SELECT * from (SELECT readid From readbook where userid=1 and bookid=1 and readpage=5) as a

There must be as a, because the table must be given an alias, but I didn’t have this requirement when I used SQL server before

And that’s what I wrote

INSERT INTO readbook (readid,readpage,readcount,bookid,userid,readtime) VALUES ( (SELECT 

CASE WHEN a.readid>0 THEN a.readid ELSE NULL END from(SELECT readid From readbook where 

userid=1 and bookid=1 and readpage=3) as a) ,3,1,1,1,now()) ON duplicate key UPDATE 

readcount=readcount + 1,readtime=now()

Later, I gradually tried to find out that this case wehn is unnecessary. It seems that MySQL can automatically replace the value that cannot be found with the default value

[Solved] PhpMyAdmin #1089 – Incorrect prefix key; the storage engine doesn’t support unique pre…

First, give the solution directly:

Click a_ I, do not enter the size, directly click to execute

Analysis

When you use phpMyAdmin to create a database table, we usually need to set a primary key and let it grow by itself. But sometimes when you finish setting, you may find such an error:

1089 - Incorrect prefix key; the used key part isn’t a string, the 
used length is longer than the key part, or the storage engine doesn’t 
support unique prefix keys

The picture is shown as follows:

Fault analysis:

References

Preview SQL statement:

We found that there is indeed an additional 4 in primary key, so how to solve the problem
do you really need the command line to handle it?Of course not

Solution:

When setting self growth, we will see this interface:

Tips:
it is worth noting that this size is not a required value, but it is not very familiar with phpMyAdmin. It is easy to subconsciously set a value for the first time. Once it is set, the above error will appear
so the final solution is the size in the diagram. Here, we just need to leave the blank and click execute to save the table successfully

Incorrect key file for table [How to Solve]

Problem phenomenon:

alter table portal_ app_ xxxx_ XXX add devno varchar (64) not null default ” comment ‘device machine coding’, add serialno varchar (64) not
null default ” comment ‘binding process serial number’, error_ code: 1034

MySQLerrorcode1034(ER_ NOT_ KEYFILE):Incorrectkeyfilefortable’XXXXXX’; trytorepairit

Find solutions:

After searching on the Internet, it is found that most of the solutions are repair, and then MyISAM is used to check and recover

However, we found that this method is only applicable to the table whose storage engine is MyISAM, so we continued to guess the search method

When using the repair table, the InnoDB table is reported directly, and the repair method is not supported

Guess the solution:

Since it is a 39 GB large table alter operation, it is assumed that the temporary table will not be used. Therefore, the temporary space of the slave library is checked to be/tmp, which is only half of the total memory, 16 GB. As a result, the temporary table space is not enough, and the alter process fails, which leads to the failure of modifying the table structure, and finally the failure of master-slave replication

The solution to this problem is as follows:

(1) Increase/tmp directory space

(2) Modify the TMP directory of MySQL database, migrate the TMP corresponding directory to a larger space, and then restart the database

| innodb_tmpdir | /data/mysql/mysql3306/data/inno_tmp_dir | <- This parameter specifies the TMP directory of the alter behavior
| tmpdir | /data/mysql/mysql3306/tmp

 

MYSQL Ignoring query to other database [How to Solve]

MySQL encountered the problem of igning query to other database

root@support~]#mysql-root-p--socket=/tmp/mysql.sock
Enterpassword:
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis5
Serverversion:5.6.22-logSourcedistribution
Copyright(c)2000,2011,Oracleand/oritsaffiliates.Allrightsreserved.
OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.
Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
mysql>showdatabases;
Ignoringquerytootherdatabase
mysql>showslavestatus\G
Ignoringquerytootherdatabase
->Ctrl-C--exit!
Aborted

 

Ignoring query to other database

 

I couldn’t figure it out, but later I found out the reason for the error. It turned out that the username was missing -u

Wrong: mysql -root -p –socket=/tmp/mysql.sock

Right: mysql -uroot -p –socket=/tmp/mysql.sock

 

[root@support~]#mysql-uroot-p--socket=/tmp/mysql.sock
Enterpassword:
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis6
Serverversion:5.6.22-logSourcedistribution
Copyright(c)2000,2011,Oracleand/oritsaffiliates.Allrightsreserved.
OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.
Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|_client|
|mfi_appstore|
|mfi_boss_admin|
|mysql|
|performance_schema|
|test|
+--------------------+