Installation page: SQLSTATE[42000]: Syntax error or access violation

Oze

Hey guys, I just finished setting up the files and all. Once I go to website to finish installation I enter my MYSQL details and get the error below

NOTE, MYSQL INFO: DigitalOcean 1 GB RAM / 1vCPU / 10 GB Disk / Primary only / NYC1 - MySQL 8

Something went wrong: 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 '-yuflags CHANGE time created_at DATETIME NOT NULL' at line 1 (SQL: ALTER TABLE flarum-yuflags CHANGE time created_at DATETIME NOT NULL)

luceos

Oze what is the mysql version you are using? mysql -V

Oze

luceos mysql Ver 14.14 Distrib 5.7.35, for Linux (x86_64) using EditLine wrapper

Oze

I installed mysql Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL) now I just get the error "Something went wrong:" and that's it...

luceos

Oze is anything logged in the files under storage/logs?

Oze

luceos [2021-08-11 22:06:57] Flarum Installer.ERROR: Symfony\Component\Console\Exception\NamespaceNotFoundException: There are no commands defined in the "cache" namespace. in /var/www/flarum/vendor/symfony/console/Application.php:612 Stack trace: #0 /var/www/flarum/vendor/symfony/console/Application.php(663): Symfony\Component\Console\Application->findNamespace() #1 /var/www/flarum/vendor/symfony/console/Application.php(255): Symfony\Component\Console\Application->find() #2 /var/www/flarum/vendor/symfony/console/Application.php(167): Symfony\Component\Console\Application->doRun() #3 /var/www/flarum/vendor/flarum/core/src/Console/Server.php(42): Symfony\Component\Console\Application->run() #4 /var/www/flarum/flarum(24): Flarum\Console\Server->listen() #5 {main}

Thats it

luceos

Oze try deleting your config.php and truncating your database to start fresh. Seems the installer did its job only partially. On which of the aforementioned MySQL versions are you now?

Oze

luceos Which config.php?

and to match my DigitalOcean Mysql 8 server version I got on my Ubuntu server mysql Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL)

clarkwinkelmann

It looks like the issue could be that the table name isn't escaped in the SQL query? That would be odd because it looks like it's this migration https://github.com/flarum/flags/blob/master/migrations/2018_09_15_043621_add_flags_indices.php which uses the Laravel Schema builder so everything should be automatically escaped for the database engine.

I have never tried a database prefix with a dash in it.

Oze

clarkwinkelmann Should I try to get back to latest version of MySQL and remove the dash?

luceos

Oze if you could try that, that would be excellent 👍️

Oze

luceos
Something went wrong: SQLSTATE[HY000]: General error: 3750 Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting. (SQL: create tableyeshivaposts_likes(post_idint unsigned not null,user_idint unsigned not null) default character set utf8mb4 collate 'utf8mb4_unicode_ci')

I reinstalled Flarum completely idk anymore... mysql version is mysql Ver 14.14 Distrib 5.7.35, for Linux (x86_64) using EditLine wrapper

Oze

It seems that sql_require_primary_key needs to be disabled yet DigitalOcean forces it to be enabled. Anyway to bypass this?

PascalBoschma

Oze I've had this issue once before and had to contact them. Not sure though if that's the only way.

luceos

Is this a MySQL instance on a digital ocean droplet or their hosted MySQL?

Oze

luceos Their hosted MYSQL

Oze

Tried it all still no luck sadly :/

clarkwinkelmann

I have tried this this locally and can confirm dashes in the table prefix will prevent Flarum installation. I have created an issue for it here flarum/core3022 I recommend trying without a dash in the meantime. An underscore can be used instead.

The problem is with Blueprint::renameColumn in this migration https://github.com/flarum/flags/blob/master/migrations/2018_06_27_101500_change_flags_rename_time_to_created_at.php

I have tried to do the same with a base Laravel installation and a single migration file and I get the same error, so it's something with Laravel or DBAL.

I can't find any existing issue for this though. The closest I saw was this issue about issues with dashes in database names doctrine/dbal1735

I cannot find any description of what Laravel considers a valid database prefix. All examples I can find only ever use underscores. I wonder if the easiest solution wouldn't be to just disallow dashes in the prefix field during installation.

Oze

clarkwinkelmann

This is great thanks, but now the main issue has changed. I am using MYSQL hosted by DigitalOcean, and they force the sql_require_primary_key option for MYSQL. For some reason Flarum only adds primary keys AFTER the tables are added, or not at all, I am unsure. There must be a way to fix this, I am not that knowledgeable on this subject so dont know if this was done purposely, or this was just the way this was built.

clarkwinkelmann

Oze oh sorry I focused on the original issue and didn't even notice the latest message.

It seems like the way the migrations work Flarum will be unable to create composite primary keys in this circumstance. For reference, the migration in question

Creating the database locally and then copying it over would work around that issue. But the same problem will likely happen again when you install extensions 😬

I found this SO question https://stackoverflow.com/q/62418099/3133038 and this Laravel issue laravel/framework33238 . From the comments in those two threads it seems like people were able to simply ask DigitalOcean to disable the setting on their instance.

Laravel doesn't consider it a bug and the various workarounds described in the comments would require rewriting a large part of Flarum's migrations so I don't think we would add a special case to handle this.