Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

v2.2.0 : MySQL 8 adds reserved keywords that appears to break BCOEMS #1244

Closed
BrewDrinkRepeat opened this issue Oct 18, 2021 · 18 comments
Closed

v2.2.0 : MySQL 8 adds reserved keywords that appears to break BCOEMS #1244

BrewDrinkRepeat opened this issue Oct 18, 2021 · 18 comments
Assignees
@geoffhumphrey
Labels
bug confirmed Bug report confirmed in test environment in latest master commit Fix is in latest commit to master. Not in release. MySQL 8 Issues related to MySQL 8 v2.3.2 v2.5.0
Milestone
v2.5.0 Beta Release

Comments

@BrewDrinkRepeat
Copy link

Version: 2.2.0

Installation URL: https://brewdrinkrepeat.com/valhalla/

Is your installation hosted on brewcompetition.com or brewcomp.com? N

Description of Issue:

My webhost informed me that they updated to MySQL 8 on 10/12, after which I can no longer access the site, receiving instead an SQL error: "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 WHERE id='1'' at line 1"

It appears the v8 introduces new reserved keywords, not sure if that is the issue but here is the info on that:
https://dev.mysql.com/doc/refman/8.0/en/keywords.html

I left BCOEMS @ 2.2 for this competition, but it is over now so if updating to a later version will work (and allow entrants to retrieve their scoresheets) please advise and I will do so.

(Alternately, I can go into the database to search for judging numbers, and email the PDFs manually to the folks who haven't yet gotten them)

Thanks!
@geoffhumphrey
Copy link
Owner

I'm looking into a fix for this in the next version - will involve changing the name of the "system" table since that word is now reserved for MySQL 8.

From the error statement you provided, it looks as though you're not employing a prefix. Perhaps adding a prefix variable in /site/config.php AND adding that prefix to all of your current DB table names will fix the issue. In the interim, of course.

geoffhumphrey added a commit that referenced this issue Oct 21, 2021
@geoffhumphrey
Address Issue #1244 and #1236; Barcode Scanning Bug
fbef48f
@geoffhumphrey
Copy link
Owner

@BrewDrinkRepeat, I have tentative confirmation that adding the prefix resolves the issue. As of today, I've been unable to test it due to issues installing MySQL 8 on my local test server. I will post back when I've confirmed.

For the next release, the system table in the BCOE&M DB will be renamed to avoid this issue.

@geoffhumphrey geoffhumphrey self-assigned this Oct 21, 2021
@geoffhumphrey geoffhumphrey added bug confirmed Bug report confirmed in test environment v2.3.2 labels Oct 21, 2021
@geoffhumphrey geoffhumphrey added this to the 2.3.2 Release milestone Oct 21, 2021
@Fencer04
Copy link

I can confirm that adding the table prefix fixed this issue. Rather than update the table name I would suggest just having a default prefix similar to how WordPress handles it.

@geoffhumphrey geoffhumphrey added the in latest master commit Fix is in latest commit to master. Not in release. label Oct 22, 2021
@BrewDrinkRepeat
Copy link
Author

BrewDrinkRepeat commented Oct 23, 2021 via email

@BrewDrinkRepeat
Copy link
Author

I'm not skilled enough at SQL to know if I could've renamed the tables using an SQL command, but after manually renaming them all in phpMyAdmin (fun! LOL) and adding the prefix to config.php everything seems to be working again. Thanks so much!

@graemecoates
Copy link

graemecoates commented Oct 23, 2021 via email

@geoffhumphrey
Copy link
Owner

Renaming tables shouldn't be necessary here?
Having quoted table names (with backticks) should be enough to make them identifiers and avoid reserved name issues.

@graemecoates, absolutely true, but that would entail changing a whole lot of SQL statements in the codebase. Renaming tables, in this instance, is the most expedient methodology.

@r-squared r-squared mentioned this issue Dec 29, 2021
Open
@BrewDrinkRepeat
Copy link
Author

Just FYI I just updated a different competition site to the latest version (2.4.0.3), without adding the prefix, and am getting the same error.

I know how to resolve it, but just in case this version was supposed to correct the issue with the MySQL statements it doesn't appear to have. :(

@geoffhumphrey
Copy link
Owner

Ah, dammit! :) More testing needed, obviously. Thanks for letting me know.

@BrewDrinkRepeat
Copy link
Author

BrewDrinkRepeat commented Feb 3, 2022 •
edited
Loading

Sorry to be the bearer of bad news... if you need any info about what versions my webhost (Dreamhost) is running let me know!

BUT... unlike last time, adding the prefix to the table names and the config.php file isn't fixing it. I get to the point where it asks me to confirm that I want to update the software, and then it starts throwing errors like:

Unknown column 'brewerJudgeAssignedLocation' in 'buzzoff_brewer'

Not sure if there are any options, other than for me to do a fresh install of the software and database...I won't be doing this until at least tomorrow, if not the weekend, so if you have any suggestions I'm all ears!

Thanks so much Geoff!

Edit: sorry, should have posted that I'm trying to update the BCOE&M database from its current version, 1.3.2.0, to the latest version, 2.4.0.0.

@geoffhumphrey
Copy link
Owner

OK, after a few days of wrangling things, I believe that things are worked out here. The latest master commit seems to fix the above issues @BrewDrinkRepeat - I was able to successfully install an instance on a server running PHP8 and MySQL 8 several times. If you wouldn't mind testing using that commit, I'd appreciate it.

@geoffhumphrey geoffhumphrey reopened this Feb 8, 2022
geoffhumphrey added a commit that referenced this issue Feb 8, 2022
@geoffhumphrey
(Re)Address Issue #1244
b66a65b
@BrewDrinkRepeat
Copy link
Author

BrewDrinkRepeat commented Feb 8, 2022 via email

@Fencer04
Copy link

Fencer04 commented Feb 8, 2022 via email

@Fencer04
Copy link

Fencer04 commented Feb 8, 2022 via email

@geoffhumphrey
Copy link
Owner

Right on. Appreciate you testing it out! If @BrewDrinkRepeat is able to install without issues, I'll wrap up some loose ends and prepare to integrate it into the 2.4.1 release.

@BrewDrinkRepeat
Copy link
Author

Sorry this took me so long to reply to... I grabbed the latest commit, setup the config settings, and uploaded to my existing install... unfortunately still getting the error "Unknown column 'brewerJudgeAssignedLocation' in 'buzzoff_brewer'"

So I guess a fresh install is going to have to happen, as I need to get the site up and running for the comp as soon as possible.

I'll report back if the fresh install fails also... hope to do that tonight, but I'm not certain.

Thanks again!

@BrewDrinkRepeat
Copy link
Author

Update: a fresh install worked, so perhaps the bug(s) had to do with updating from the older version (and possibly specifically MY older install!).

So, all is good for the most part... users will have to create new accounts, but that is often the case with comps anyway so I'm not too worried about it.

Thanks!

@brouse69
Copy link

I am trying to update an existing site to ver 2.4.0.3, and I am getting the "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 WHERE id='1'' at line 1" error. I have added the prefix to the Config.php file, but that did not resolve the problem.

In case it matters, my site is hosted on "DreamHost.com", which appears to be using MySQL ver 8

@geoffhumphrey geoffhumphrey added the MySQL 8 Issues related to MySQL 8 label Jul 19, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@geoffhumphrey geoffhumphrey

Labels
bug confirmed Bug report confirmed in test environment in latest master commit Fix is in latest commit to master. Not in release. MySQL 8 Issues related to MySQL 8 v2.3.2 v2.5.0
Projects
None yet
Milestone
v2.5.0 Beta Release
Development

No branches or pull requests
5 participants
@Fencer04 @geoffhumphrey @graemecoates @BrewDrinkRepeat @brouse69