Fatal error during installation at page_tablescreate.php

[skenow]

Describe the bug

Uncaught exception
Message: SQLSTATE[42000]: Syntax error or access violation: 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 'rank smallint(5) unsigned NOT NULL default '0', level varchar(3) NOT NULL defa' at line 16
Code: 42000
File: /var/www/html/dev/vendor/aura/sql/src/AbstractExtendedPdo.php
Line: 649
Trace:

#0 /var/www/html/dev/vendor/aura/sql/src/AbstractExtendedPdo.php(649): PDO->query('CREATE TABLE de...')
#1 /var/www/html/dev/libraries/icms/db/Connection.php(53): Aura\Sql\AbstractExtendedPdo->query('CREATE TABLE de...')
#2 /var/www/html/dev/htdocs/install/class/dbmanager.php(96): icms_db_Connection->query('CREATE TABLE de...')
#3 /var/www/html/dev/htdocs/install/page_tablescreate.php(61): db_manager->queryFromFile('./sql/mysql.str...')
#4 {main}

To Reproduce

Steps to reproduce the behavior:

1. Fresh installation
2. composer install from the root
3. proceed through the installation until you reach page_tablescreate

Expected behavior

Tables get added to the database

Screenshots

If applicable, add screenshots to help explain your problem.

Server

• PHP version: 7.3.11-0ubuntu0.19.10.1
• PHP is 32/64bit: 64
• HTTP Server : Apache
• HTTP Server version: Apache/2.4.41 (Ubuntu)
• HTTP Server is 32/64 bit: 64

Desktop (please complete the following information):

• OS: [e.g. iOS] Ubuntu 19.10
• Browser Firefox
• Version 71.0 (64-bit)

[skenow]

Tables do get created - here is what I have
'prefix_avatar'
'prefix_avatar_user_link'
'prefix_block_module_link'
'prefix_block_positions'
'prefix_config'
'prefix_configcategory'
'prefix_configoption'
'prefix_group_permission'
'prefix_groups'
'prefix_groups_users_link'
'prefix_icmspage'
'prefix_image'
'prefix_imagebody'
'prefix_imagecategory'
'prefix_imgset'
'prefix_imgset_tplset_link'
'prefix_imgsetimg'
'prefix_modules'
'prefix_newblocks'
'prefix_online'
'prefix_priv_msgs'
'prefix_ranks'
'prefix_session'
'prefix_smiles'
'prefix_tplfile'
'prefix_tplset'
'prefix_tplsource'
'prefix_xoopscomments'
'prefix_xoopsnotifications'

And they all have columns, and it looks like the indexes are added, too.

[skenow]

Not all tables - any table in the sql file starting after tplsource: users, invites, system_customtag, system_mimetype, system_adsense, system_rating, system_autotasks, icms_urllink, icms_file, icms_data_file

[skenow]

Database info:
collation - utf8_general_ci
characterset - utf8
engine - InnoDB
mysql version - 8.0.18-0ubuntu0.19.10.1
x86_64

[skenow]

There's something with this version of MySQL that is causing the problem - I cannot connect with any other of my test sites either.

[skenow]

After adding these lines to mysql.conf.d in the [mysqld] section, I was able to connect to the server, create the database, and begin to add the tables. The 1.4 install also failed to complete, failing at the users table -

default-authentication-plugin=mysql_native_password
collation-server = utf8_unicode_ci
character-set-server = utf8

[fiammybe]

That would indicate a problem with the MySQL server rather than with the installer, because I have been doing installations on multiple versions of mysql server with 1.4 without a problem. It would be good to know what is causing the problem though.

[skenow]

There are several column names that are reserved words in MySQL 8. Enclosing the column names in back-ticks does alleviate the problems.

level, rank, name - https://dev.mysql.com/doc/mysqld-version-reference/en/keywords-8-0.html

[fiammybe]

ah, column names. And that would be because the makedata script is using a file containing plain SQL instead of commands generated using PDO. That might be an issue wiht some modules as well then

[fiammybe]

to be on the safe side, we embark everything in back-ticks?

[skenow]

I would enclose the column names in back-ticks.

As for the MySQL server configuration and default authentication plugin, this is something to take into consideration for the install - https://www.php.net/manual/en/ref.pdo-mysql.php "When running a PHP version before 7.1.16, or PHP 7.2 before 7.2.4, set MySQL 8 Server's default password plugin to mysql_native_password"