CSWeb SQLSTATE[42000] Specified key was too long

Discussions about syncing data via Bluetooth, Dropbox, FTP, and using CSWeb
Forum rules
New release: CSPro 8.0
Post Reply
mka.surveys
Posts: 6
Joined: July 18th, 2017, 4:06 pm

CSWeb SQLSTATE[42000] Specified key was too long

Post by mka.surveys »

Hello,

while trying to upload a dictionary to the web app, I get an error. By looking at the logs I see this error message.

[2018-04-16 15:32:28] CSPro API.CRITICAL: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (uncaught exception) at /home/mkaconsu/public_html/csweb/vendor/aura/sql/src/ExtendedPdo.php line 288 {"exception":"[object] (PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes at /home/mkaconsu/public_html/csweb/vendor/aura/sql/src/ExtendedPdo.php:288)"} []

Any ideas how I could solve it?
Thanks in advance
Attachments
2018-04-16_1746.png
2018-04-16_1746.png (10.37 KiB) Viewed 4879 times
josh
Posts: 2399
Joined: May 5th, 2014, 12:49 pm
Location: Washington DC

Re: CSWeb SQLSTATE[42000] Specified key was too long

Post by josh »

Can you post your dictionary here or send it to us at cspro@lists.census.gov. Also please let us know what version of MySQL you are using.
mka.surveys
Posts: 6
Joined: July 18th, 2017, 4:06 pm

Re: CSWeb SQLSTATE[42000] Specified key was too long

Post by mka.surveys »

Thank you josh,

My shared hosting uses MySQL 5.6 and PHP 5.6. I attach a screenshot of phpMyAdmin and also the dictionary I am trying to upload. Also, I tried uploading other dictionaries and got the same error.
phpMyAdmin
phpMyAdmin
mysql.png (18.9 KiB) Viewed 4867 times
Thanks again!
Attachments
ua_splashes.zip
Dictionary
(3.9 KiB) Downloaded 270 times
josh
Posts: 2399
Joined: May 5th, 2014, 12:49 pm
Location: Washington DC

Re: CSWeb SQLSTATE[42000] Specified key was too long

Post by josh »

I'm able to use your dictionary with no problem on my test server running MySQL 5.6.35. I'm guessing that there is a difference in our settings for MySQL. If you are able to set startup options for MySQL on your server you could try setting innodb_large_prefix=1 as described here:

https://dev.mysql.com/doc/refman/5.6/en ... rge_prefix

If you are able to upgrade to MySQL 5.7 that should also solve the problem as the index size has been increased by default in that version.

If neither of the above is possible please be patient. We are looking at how to modify the code to reduce the index size to avoid this error.
mka.surveys
Posts: 6
Joined: July 18th, 2017, 4:06 pm

Re: CSWeb SQLSTATE[42000] Specified key was too long

Post by mka.surveys »

My server indeed has the following configuration

Code: Select all

innodb_large_prefix=OFF
I'll contact my shared hosting provider and check if I can do either of those things. I'll post here if this solves the problem.

Thanks a lot josh!
Post Reply