Error found in init.sql

[John Tsui]

Hi Ronald,

I am trying to install 2.10 in red hat 8.6

mysql  Ver 8.0.26

When executing the step " Create and initialise the database tables"

it gives:

[schedulix@schdap01 sql]$ mysql --user=schedulix --password=schedulix_password --database=schedulixdb --execute="source mysql/install.sql"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1064 (42000) at line 32 in file: 'mysql/init.sql': 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 'GROUPS (ID, NAME,     DELETE_VERSION, CREATOR_U_ID, CREATE_TS, CHANGER_U_ID, CHA' at line 1

Seems there is something in the table "GROUP"

[schedulix@schdap01 sql]$ mysql --user=schedulix --password=schedulix_password --database=schedulixdb --execute="desc GROUPS;"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1064 (42000) at line 1: 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 'GROUPS' at line 1

After adding schedulixdb.GROUPS instead of GROUPS in mysql/init.sql.

The step can be executed without error.

[schedulix@schdap01 sql]$ mysql --user=schedulix --password=schedulix_password --database=schedulixdb --execute="desc schedulixdb.GROUPS;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------+---------------+------+-----+---------+-------+
| Field          | Type          | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| ID             | decimal(20,0) | NO   | PRI | NULL    |       |
| NAME           | varchar(64)   | NO   |     | NULL    |       |
| DELETE_VERSION | decimal(20,0) | NO   |     | NULL    |       |
| CREATOR_U_ID   | decimal(20,0) | NO   |     | NULL    |       |
| CREATE_TS      | decimal(20,0) | NO   |     | NULL    |       |
| CHANGER_U_ID   | decimal(20,0) | NO   |     | NULL    |       |
| CHANGE_TS      | decimal(20,0) | NO   |     | NULL    |       |
+----------------+---------------+------+-----+---------+-------+

No sure there are any other scripts or configuration table need to change or not.

Thanks.

John Tsui

11.May.2022

[Ronald Jeninga]

Hi John,

thank you for reporting this!

I'll have a look what's going on there and fix it.

It's probably something silly and I'm pretty sure I'll be able to fix it today.

Sorry for the hassle!

Best regards,

Ronald

[Ronald Jeninga]

Hi John,

some googling revealed that GROUPS was added as a reserved word to MySQL 8.02 (or so).

This means it can only be used as an identifier either by specifying the schema name or by quoting it.

Would it be too much to ask if you can test it with quoted identifiers?

I've attached the mySQL init.sql file with all identifiers quoted to this post (I hope I didn't miss a quote).

If you replace your init.sql file with the posted one, the initialisation of the database should run without issues.

I'll test it from my side as well.

If our tests show success, I'll add the changes to github.

Best regards,

Ronald

[John Tsui]

Hi Ronald,

It works fine now.

Thank you very much for your help.

Regards,

John Tsui

12.May.2022

ronald....@independit.de 在 2022年5月11日 星期三下午6:13:56 [UTC+8] 的信中寫道：

[Ronald Jeninga]

Hi John,

thank you for your feedback!

In my environment it worked perfectly as well.

I've now modified the mysql/init.sql file in the current development branch, the 2.10 and 2.9 branch and pushed it.

Tomorrow I'll probably find time to create and upload the rpms for 2.9 and 2.10.

Best regards,

Ronald