ACT MySQL(8.0) ODBC 5.3 Error

Vae

I'm looking to export the majority of ACT tables into my mysql 8.0.13 DB - I'm able to get Combatant, Current and Encounter tables but I run into the following error when validating table setup to go any further:

ERROR [42000] [MySQL][ODBC 5.3(w) Driver][mysqld-8.0.13]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 'grouping VARCHAR(92), type VARCHAR(64), starttime TIMESTAMP, endtime TIMESTAMP, ' at line 1

Any ideas?

EQAditu

I'm afraid I've never used MySQL 8 as many people complained about breaking changes, inability to be smoothly configured as a portable service, etc.

This falls under the breaking change category, I guess.  There is no MySQL 6 or 7 for whatever reason... I guess the changes were major, so they went the route of skipping numbers.

ACT's error logs will show the full SQL statement that was sent before the error occurred.  Usually MySQL errors like this are actually having issues with what is immediately to the left or right of the quoted string.  If you get something like the MySQL Workbench or other 3rd party clients, you may be able to fiddle with the syntax until it is happy.

Once you figure out what is wrong with the syntax, you might be able to fix it with ACT's Datasource Compatibility Hacks.  If you cannot figure out what Oracle changed for version 8 and you have no specific reason to be using it, you might want to try the current version of the 5.7 branch.

Vae

Thanks! I'm not married to MySQL 8 by any means so I'm going to try implementation using 5.7. Fingers crossed!

Vae

Looks like 5.7 did the trick. Thanks

[8:12:14 PM] ODBC Connection Succeeded

[8:12:17 PM] 'encounter_table' created successfully.

[8:12:17 PM] 'combatant_table' created successfully.

[8:12:17 PM] 'current_table' created successfully.

[8:12:17 PM] 'damagetype_table' created successfully.

[8:12:17 PM] 'attacktype_table' created successfully.

[8:12:17 PM] 'swing_table' created successfully.

EQAditu

So the specific problem is that grouping became a reserved keyword in MySQL 8 and cannot be used as an unquoted name in syntax.  ACT does not quote names in commands because the quote types change from database to database.  MySQL uses `backticks`, while SQL Server uses [square brackets] and ACT must try to not default to specific flavors.

https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-G

{...}

• GLOBAL
• GRANT (R)
• GRANTS
• GROUP (R)
• GROUPING (R); added in 8.0.1 (reserved)
• GROUPS (R); added in 8.0.2 (reserved)
• GROUP_REPLICATION

{...}

You can use a compatibility hack in ACT and hope that it won't affect anything else.  Copy the below text into the clipboard and use the Import XML button in the upper-right corner of the main ACT window.  You should see a new entry for "MySQL ODBC 8.0".

<Config Xml="&lt;Config&gt;&lt;OdbcHacks&gt;&lt;Hack ConnectionStringMatch=&quot;MySQL ODBC 8.0&quot; SqlFind=&quot;, ?grouping\b&quot; SqlReplace=&quot;, `grouping`&quot; /&gt;&lt;/OdbcHacks&gt;&lt;/Config&gt;" />

As a side note, it seems that the threatdelta column isn't necessarily big enough for EQ2 right now.  You might need to change that column from INT to BIGINT.