Bug #86303 The --init-file functionality does not accept valid SQL remarks

• Submitted: 12 May 2017 11:19
• Modified: 28 Jul 2017 10:00
• Reporter: Roel Van de Paar
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.7,8.0.1
• OS: Any
• CPU Architecture: Any

[12 May 2017 11:19] Roel Van de Paar

Description:
The --init-file functionality does not accept valid SQL remarks. This file (initfile);

SELECT 1; #
SELECT 2;

Will fail to execute correctly (even though it is valid SQL) when called with --init-file=/path/initfile like so;

2017-05-12T10:42:15.125627Z 2 [ERROR] 1064  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 2;' at line 2

Making a small change;

SELECT 1;
SELECT 2;

Will make it work again.

How to repeat:
As per above. Checked on 5.7 and 8.0

[12 May 2017 11:33] Roel Van de Paar

Also, the SYSTEM command cannot be used from the --init-file.

[18 May 2017 3:18] MySQL Verification Team

[root@localhost mysql]# bin/mysqld_safe --init-file=/tmp/brisi.sql &
[1] 9047
[root@localhost mysql]# 170518 05:16:45 mysqld_safe Logging to '/usr/local/mysql/data/localhost.localdomain.err'.
170518 05:16:45 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@localhost mysql]# bin/mysql -e "select * from test.t1;"
+------+
| i    |
+------+
|    1 |
+------+
[root@localhost mysql]# cat /tmp/brisi.sql
use test;
SELECT 1; #
SELECT 2;
insert into test.t1 values (1);

[root@localhost mysql]# bin/mysql -e "select @@version;"
+-------------------------------+
| @@version                     |
+-------------------------------+
| 5.1.67-ndb-6.3.51-cluster-gpl |
+-------------------------------+
[root@localhost mysql]#

[18 May 2017 3:31] MySQL Verification Team

[root@localhost mysql]# bin/mysqld_safe --init-file=/tmp/brisi.sql
2017-05-18T03:28:33.521126Z mysqld_safe Logging to '/usr/local/mysql/data/localhost.localdomain.err'.
2017-05-18T03:28:33.534998Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
^Z
[1]+  Stopped                 bin/mysqld_safe --init-file=/tmp/brisi.sql
[root@localhost mysql]# bg
[1]+ bin/mysqld_safe --init-file=/tmp/brisi.sql &
[root@localhost mysql]# bin/mysql -p123 -e "select * from test.t1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]# cat /tmp/brisi.sql
use test;
SELECT 1; #
SELECT 2;
insert into test.t1 values (1);

[root@localhost mysql]# vi /tmp/brisi.sql
[root@localhost mysql]# bin/mysqladmin -p123 shutdown
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
2017-05-18T03:30:06.461353Z mysqld_safe mysqld from pid file /usr/local/mysql/data/localhost.localdomain.pid ended
[1]+  Done                    bin/mysqld_safe --init-file=/tmp/brisi.sql
[root@localhost mysql]# bin/mysqld_safe --init-file=/tmp/brisi.sql
2017-05-18T03:30:16.988966Z mysqld_safe Logging to '/usr/local/mysql/data/localhost.localdomain.err'.
2017-05-18T03:30:17.003977Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
^Z
[1]+  Stopped                 bin/mysqld_safe --init-file=/tmp/brisi.sql
[root@localhost mysql]# bg
[1]+ bin/mysqld_safe --init-file=/tmp/brisi.sql &
[root@localhost mysql]# bin/mysql -p123 -e "select * from test.t1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| i    |
+------+
|    1 |
+------+
[root@localhost mysql]# cat /tmp/brisi.sql
use test;
SELECT 1;
SELECT 2;
insert into test.t1 values (1);

[root@localhost mysql]# bin/mysql -p123 -e "select @@version;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| @@version |
+-----------+
| 5.7.18    |
+-----------+
[root@localhost mysql]#

[21 Jul 2017 0:05] Roel Van de Paar

Correcting category, this is not CLI related.

[28 Jul 2017 10:00] Erlend Dahl

This is a documented limitation:

https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_init-file

Please reopen as an feature request if there is a reasonable usecase for this functionality.