search

TheNAF / naflm

NAFLM - NAF League Manager
Other
35 stars 34 forks source link

failure to create table 'tours' on new installation php 5, mysql 8. #284

Closed snotlingorc closed 4 years ago

snotlingorc commented 4 years ago

php 5.6.40 mysql 8.0.18

On new installation, creating the tables for first time installation gives: FAILED — tours MySQL error (errno 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 'empty,begun,finished BOOLEAN; DECLARE winner MEDIUMINT UNSIGNED; DEC' at line 8

Printing out they query: CREATE TABLE tours ( tour_id MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, f_did MEDIUMINT UNSIGNED, name VARCHAR(60), type TINYINT UNSIGNED, date_created DATETIME, rs TINYINT UNSIGNED DEFAULT 1, locked BOOLEAN NOT NULL DEFAULT 0, empty BOOLEAN DEFAULT TRUE, begun BOOLEAN DEFAULT FALSE, finished BOOLEAN DEFAULT FALSE, winner MEDIUMINT UNSIGNED, allow_sched BOOLEAN DEFAULT FALSE )

it looks like the value "empty" is a reserved word. If I change it to : "empty2 BOOLEAN DEFAULT TRUE" it works and the table is created.

snotlingorc commented 4 years ago

According to https://dev.mysql.com/doc/refman/8.0/en/keywords.html EMPTY (R); added in 8.0.4 (reserved)

Perhaps change empty -> is_empty, begun -> is_begun, finished to is_finished. This way it would match the properties values stored in the Tour class.

snotlingorc commented 4 years ago

I did not add this into the pull request. But to update the database to use the new columns, the following upgrade would need to be in place : `102 => array(

    SQLUpgrade::runIfColumnNOTExists('tours', 'is_begun', 'ALTER TABLE tours CHANGE begun is_begun BOOLEAN DEFAULT TRUE'),

    SQLUpgrade::runIfColumnNOTExists('tours', 'is_finished', 'ALTER TABLE tours CHANGE finished is_finished BOOLEAN DEFAULT FALSE'),

    SQLUpgrade::runIfColumnNOTExists('tours', 'is_empty', 'ALTER TABLE tours CHANGE empty is_empty BOOLEAN DEFAULT FALSE'),

),`