Daily Lucky Numbers:
4
5
6
29
38
39

Can't install SMF Arcade - Specified key was too long

Started by Daniel Hofverberg, November 09, 2020, 08:30:02 PM

Previous topic - Next topic

Daniel Hofverberg

SMF Arcade seems excellent, but unfortunately I can't manage to install it. The test in the Package Manager shows no warnings or errors, but when I click on Install Modification I keep getting the following error:

QuoteSpecified key was too long; max key length is 1000 bytes
File: /var/www/html/forum/Packages/temp/arcadeinstall/Subs-Install.php
Line: 34

I first thought it had something to do with switching from e-Arcade, but I have now deleted all old tables from e-Arcade and still gets the same error. So no matter what I do, I can't get past that error message, and nothing gets installed

When I look at the database after getting that error, only smf_arcade_pdl1 and smf_arcade_pdl2 has been created and no other tables.

Any idea why that is, or how to resolve the issue? I'm running PHP 7.4.11 and MySQL 8.0.22 under Ubuntu 20.04.

Chen Zhen


Uninstall and delete the existing Arcade package.

Download & install the attachment.
Please let me know if it works without any errors.

Thank you.

Daniel Hofverberg

Sorry, with that file I get almost the same error, but a different line number:

Specified key was too long; max key length is 1000 bytes
File: /var/www/html/forum/Packages/temp/arcadeinstall/Subs-Install.php
Line: 52

The same error both with existing e-Arcade tables and with no arcade related tables. Any ideas?

Chen Zhen


I'm not sure what's going on at the moment.
Installing the arcade using MySQL v8.0.22 does not show any problems.
I have also tested installing E-Arcade 3.0 with adding a few games, uninstalling it, running the conversion tool & then installing SMF Arcade (the version I adjusted above in this thread) with no problems.


Do the pdl tables that it does get to create show as MyISAM or InnoDB (engine) ?




You can try uninstalling the arcade & then run the arcade database purge tool:
https://web-develop.ca/index.php?action=downloads;area=stable_smf_utilities;file=64;download

After using the tool & uninstalling it you can try installing the arcade again.


Daniel Hofverberg

I have tested using the arcade database purge tool, and then trying to install the arcade again, but still the same error message. :(

The two created tables smf_arcade_pdl1 and smf_arcade_pdl2 show as MyISAM, and both have the collation utf8_mb4_0900_ai_ci. Neither have any indexes apart from PRIMARY KEY.


Is there any way of finding out which table and index the error message is referring to? "Specified key was too long" obviously imply that some index key was too long, but I have no idea which one. But I can only assume it must be a more complicated one than just a primary key.

Chen Zhen

#5
Are you using SMF 2.1 or SMF 2.0.17 ?

Try creating a new database as UTF8 Unicode ci and then import everything into that database.
The problem may be that you created a mb4 database.
I see that seems to be the default setting that phpmyadmin wants to use but use the option I suggested from the collation drop down.

Daniel Hofverberg

I'm using SMF 2.0.17.

My SMF is using ISO-8859-1 (Latin1), so my default collation in the database is latin1_swedish_ci (in case it matters); but the SMF Arcade tables created has automatically been set to utf8_mb4_0900_ai_ci.

Chen Zhen

#7
The arcade does not pick a database collation.
You likely created a new database as utf8_mb4_0900_ai_ci and then imported your ISO-8859-1 tables into it.
As I said before.. phpmyadmin seems to be on that database collation setting when you first create a database but it is up to you to click on the collation drop-down menu and pick another.
Having mixed collations will cause problems.

I advise people to use UTF-8 Unicode ci for good reason.
Arcade games come from a variety of sources that may have game descriptions using a language that has characters which do not exist in an ISO-8859-1 environment.
These characters will end up being converted to � because it can't find the ISO equivalent.

utf8_mb4 database environments will not work when varchar tables are set above 191 bytes in size.
The arcade attempts to create varchar columns at 255 bytes because it assumes you are using a UTF-8 which is 3 bytes in size which is stipulated during installation and also on the arcade info page of this website.

Your solution:

[1]Make sure you have a copy of you current forum database on your computer.
Using phpmyadmin click on your forum database, use export but make sure to use the drop-down to select compressed output as gzip. (you may already have this on file? no problem... use that file below..)

[2]Create a new database and make sure it is set at UTF8 Unicode ci, this way any modifications you install will be automatically set to that collation.

[3]Click on the new database and import your gzipped database.

[4]Make sure your forum Settings.php file has the proper database name if it is different than before.

[5]Use this tool to convert all collations to UTF-8: https://web-develop.ca/index.php?action=downloads;area=stable_smf_utilities;file=36;download
Only use the top option from that database tool to convert all database collations to UTF8 Unicode ci.

[6]Use the SMF built in tool to convert your database to UTF-8 character set.

[7]Install the arcade.
   




As long as you have a proper copy of your database on your computer you can attempt to make changes like above without fear of losing your forum data.
Assuming there isn't any newer data after the exported copy of your db.

Chen Zhen

#8
Let me know if this worked for you else I can render more assistance.
No matter what you are going to have trouble with your forum if you do not fix the collation issue.

If you leave it in the state it was in at the onset of this thread you will likely not be able to install any modifications that contain varchar columns with a size over 191 bytes.
SMF is working for you because you copied those tables to the new database but any mod will install using the mb4 collation.

Daniel Hofverberg

That worked, thank you so much for your helå. :) I have been able to install it now.

For some reason, the database had a default collation of that weird utf8_mb4_0900_ai_ci, which I have never willingly opted for.

I can only assume it must have happened when I recently switched to a new server host (as the old host shut down) - it seems that utf8mb4_0900_ai_ci is the default collation since MySQL 8.0 (for some strange reason), so apparently it must have used the default for the database itself when I imported the dump from the old server. Very annoying... :(

I'm so sorry for wasting your time with something that apparently wasn't SMF Arcade's fault. :-[

Chen Zhen

I'm glad I was able to help out and everything is working properly now.

mb4 collations are likely going to be mainstream in the near future.
Although how MySQL and other DB software deals with them may change.
The next version of the Arcade that I release is going to be mb4 compliant and will install proper sized varchar columns depending on the database collation setting.
Something good came out of all of this in the end.



Did you end up using the RC version I gave you to keep your E-Arcade scores?
I'm curious about my adjustments working properly for people switching from E-Arcade.

Daniel Hofverberg

Quote from: Chen Zhen on November 12, 2020, 07:35:02 PMDid you end up using the RC version I gave you to keep your E-Arcade scores?
I'm curious about my adjustments working properly for people switching from E-Arcade.
Yes, I'm using the RC version. All games from e-Arcade shows up, as well as all old scores. Some of them also submits and saves correctly. But with a lot of games, I can't submit/save the score, because I keep getting the following error message after submitting:
"This game requires a score greater than 0."

Even though I have scored greater than zero.

This applies at least to Avalanche Run, Sonic Bowling, ScoobyDoo Plane Trip, SpongeBob SquarePants: The Snowboard Race, Tom And Jerry In Midnight Snack, Tom & Jerry - Run Jerry Run, Bugs Bunny Hopping Carrot Hunt and Krabby Ball - probably other games as well. But some games do work properly, so I'm not sure what makes some games work and some result in that error message.

Any idea why?

Chen Zhen

You need to copy the arcade folder from your files you had on your old host.

It contains gamedata files that are necessary for some IBP flash games.

Daniel Hofverberg

Quote from: Chen Zhen on November 13, 2020, 07:18:29 AMYou need to copy the arcade folder from your files you had on your old host.

It contains gamedata files that are necessary for some IBP flash games.
Thank you. I thought I had copied everything over from the old host, but I see now that some files and folders under arcade was corrupt in the backup for some strange reason.

I have now copied over everything again manually, and it seems to work. :)

2 or 3 times I have received this error message when submitting scores though:
"Score was not saved because session is missing"

But so far I haven't been able to get the same message twice, and it seems to work when re-playing the same game one more time. Still a little bit annoying to not get to save the score, when you have played for quite some time though... :( Any idea why?

Chen Zhen

I assume this is only occurring for games from E-Arcade?

If you have the "Auto Adjust Improper Save Types" option enabled then it is likely adjusting the save type the first time when played and a score save has been attempted.

E-Arcade did not have game save types in the database therefore some games that were from E-Arcade & never played on SMF Arcade will have the wrong save type in the database (default is SMF v1).
With the above option enabled, SMF Arcade should attempt to adjust the save type for you as scores are saved to the database.
Unfortunately the game may not save a score the first time because the arcade had to adjust it afterward. 

You can also use "Enable save type debug logging" temporarily in the arcade settings to see if this is happening so when it adjusts a game it will put a message in the error log.
You don't have to keep the setting enabled all the time but it's there to debug these type of problems so you can see what's going on.