Forum for discussing general topics related to Couch.
5 posts Page 1 of 1
Hi,

Quick question. Is there a problem with Couch and SQL 8?
I know when connect to jdbc I need to use this String:
Code: Select all
jdbc:mysql://192.168.0.4:3306/Travel?autoReconnect=true&useSSL=false

I can't remember which way I installed it, but think I chose backwards compatibility.

Anyway I'm setting up my MacBook - running Mojave.
I have downloaded version 2.1 of Couch and when trying to connect to the database I get the error:
Couldn't connect to database if I leave the host as localhost.

When I change it to 127.0.0.1 it allows me to create the super user but then fails creating some of the tables. See below:

Code: Select all
Installation failed!
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 'system int DEFAULT '0', last_failed bigint(11) DEFAULT ' at line 12

CREATE TABLE couch_users ( id int AUTO_INCREMENT NOT NULL, name varchar(255) NOT NULL, title varchar(255), password varchar(64) NOT NULL, email varchar(128) NOT NULL, activation_key varchar(64), password_reset_key varchar(64), registration_date datetime, access_level int DEFAULT '0', disabled int DEFAULT '0', system int DEFAULT '0', last_failed bigint(11) DEFAULT '0', failed_logins int DEFAULT '0', PRIMARY KEY (id) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;


It used to be fine in High Sierra and SQL 5 so think that is the only change. Probably missing something very obvious.

Point out the obvious this is being tested locally before being uploaded to server. The java String is different thing all together.
Perhaps something needs to be tweaked to support latest MySQL v8? I'm still on 5.7 and have no issues with it. I am downloading now 8.0.12, will see how it goes.
Issue confirmed..
2018-11-29-003.png
MySQL 8.0x64
2018-11-29-003.png (57.95 KiB) Viewed 3821 times
The source of the trouble is the newly added "reserved word" : SYSTEM (R); added in 8.0.3. https://dev.mysql.com/doc/refman/8.0/en ... detailed-S

Some keywords may be reserved in MySQL 8.0 that were not reserved in MySQL 5.7. See Section 9.3, “Keywords and Reserved Words”. This can cause words previously used as identifiers to become illegal. To fix affected statements, use identifier quoting. See Section 9.2, “Schema Object Names”.


The immediate remedy seem to work well - open couch/install.php and apply the changes (backquotes around the word system) -
(1) line 254
`system` int DEFAULT '0',

(2) line 545
$k_stmts[] = "INSERT INTO ".K_TBL_USERS." (id, name, title, password, email, activation_key, password_reset_key, registration_date, access_level, disabled, `system`, last_failed, failed_logins) VALUES (1, '".$name."', '".$name."', '".$pwd."', '".$email."', '', '', '".$creation_time."', 10, 0, 1, 0, 0);";


After 2 edits, installation went smooth. Don't forget to drop all tables in your database before re-installation, because Couch expects a clean db to install itself and will cry otherwise.
Can confirm this has helped me with my problem too.
Thank you trendoman for your detailed response.

MySQL 8 is such a pain in the arse.
5 posts Page 1 of 1