-
Notifications
You must be signed in to change notification settings - Fork 235
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.
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
InnoDB tables for MySQL #137
Comments
Thanks for the feedback on InnoDB compatibility! I think you may have to change the two columns Preferably, you would change this right in the schema that you copy from this library and use to create the tables. Does that work for you? Any other feedback on how this library works with InnoDB is also much appreciated. |
You can increase the index length to a max of 767-1024 chars by adding these to my.cnf and restarting:
At least this is what I use and it works. |
@darkalchemy Thank you! This is a more fundamental and intrusive change than changing the length of the two email columns. So if you’re fine with email addresses being limited to 191 characters (instead of 249 characters), that is probably the easier fix (if it does indeed work). Again, any other feedback on InnoDB support is also appreciated. If the above is the only problem, that would be great, of course. |
With those additions to my.cnf, I still limit column lengths to 255. |
I've changed my Tables to InnoDb after setting the email columns to varchar(191) without any problems. But that leads to another question, have you ever seen a real email adress with more than 150 chars? Or 240 to 250? And even if there are a few, these should be more an exception, i guess.
I've created, for the sake of curiosity, around 15.000 Users (with an additional "user_settings" table containing some extra columns and data for each User) and i've encountered no problems at all, so: yes. With a Pagination-script, i've also not seen any difference when loading 15 from 15 Users or 15 from 15.000. Just blazing fast.
It seems the day will come where you will be forced to change it, wanted or not ;) https://www.percona.com/blog/2016/10/11/mysql-8-0-end-myisam/ PS: Composer will cause problems in the future because of the Exceptions, that are all combined in "src/Exceptions.php". I've created all of them (21) separately, have thrown them in the "src/" folder, outcommented all the content in "Exceptions.php" and that's all. It looks pretty messed up currently, yes, absolutely. Composer Notice was:
BTW, great work. Really appreciate it |
@eypsilon Thanks a lot for sharing your experience with InnoDB instead of MyISAM here. It’s great to have another confirmation that it’s really only a little extra work that is required for the change. The Composer/exception/namespace problem is already tracked in another issue and will be taken care of shortly. |
Hi, any cheatsheet or docs to convert all PHP-Auth tables to InnoDB on an existing app correctly? |
I need to migrate at InnoDB tables but I have this error
#1071 - Specified key was too long; max key length is 767 bytes
Is correct if I execute this script?
ALTER TABLE
usersALTER
emailDROP DEFAULT, ALTER
passwordDROP DEFAULT; ALTER TABLE
usersCHANGE COLUMN
emailVARCHAR(150) NOT NULL COLLATE 'utf8mb4_unicode_ci' AFTER
id, CHANGE COLUMN
passwordVARCHAR(150) NOT NULL COLLATE 'latin1_general_cs' AFTER
email, CHANGE COLUMN
usernameVARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci' AFTER
password;
Thanks
The text was updated successfully, but these errors were encountered: