How to write in MySQL more characters in user_nicename

Question

I install the plugin on the site for registration in Russian(Allow Cyrillic Usernames) and now I need to change in the sql database, in the table wp_users, in the column user_nicename permission to write more characters from by default 50 to 255. How to do this? Tried, did not work: ALTER TABLE wp_users ALTER COLUMN user_nicename varchar(200) NOT NULL -error

ALTER TABLE wp_users CHANGE user_nicename user_nicename VARCHAR(255) NOT NULL DEFAULT ''; -error #1067 Found in net tip: show variables like 'sql_mode' ; And remove the sql_mode "NO_ZERO_IN_DATE,NO_ZERO_DATE" to make it work. How to do this one?

Answer 1

Here's the solution guys, Steve from SkyVerge helped me figure it out, sorry for no editing it's 2am and i'm super tired:

Hi Steve, It looks like the problem is now fixed.I t was not necessary to change the 'allow guest login' as the problem was something else in the end.

After much figuring out the problem and solution was this: I will also post this online as I found several other people who could not figure out how to solve this problem, and there's no instructions on how to do it.

Here's a brief that i will post online, also just in case you guys will have other people who use special characters in usernames (chinese, japanese, russian, greek, etc) who also seem to have this problem, here's the solution:

The problem was this:

1. When a user creates a username eg: with russian cyrillic , wordpress initially does not accept such special characters
2. Thus other people (like me) use a plugin (eg: Wordpress Special Characters in Usernames (I have this one, but there's others)) which translates the cyrillic letters into html format
3. This allows manual creation of usernames with cyrillic letters from within wordpress
4. However it breaks automatic creation of usernames by wordpress
5. This break happens because when the transformation takes place, for 1 russian cyrillic letter, i think around 5 html characters are created, so a russian name/surname like Анастасия Владимировна (for example) would max out the database allowance of characters (which I think is around 50 or 60) - specifically max out this: db > username > user_nicename
6. The solution is to change user_nicename in database from the typical 50 (or 60) to 255 charactersBUT
7. SQL will not allow you to do this with the typical command for this change: ALTER TABLE wp_users MODIFY user_nicename VARCHAR(255);
8. It will give an error: Invalid default value for ‘user_registered’
9. This error is created because of wordpress mode changes (whatever the heck that means)
10. To bypass this mode one has to access ROOT SUPERVISOR sql console and do this:
    1. first check and record the current sql mode by using: show variables like 'sql_mode' ;    , and write down what mode is given (because we will need to rever to that mode after change), next
    2. set a new global mode to something that does not have "NO_ZERO_IN_DATE,NO_ZERO_DATE" , for example: set global sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
11. This now allows for this command to go through: ALTER TABLE wp_users MODIFY user_nicename VARCHAR(255);
12. run that command
13. it goes through
14. now revert back to sql mode that was before by using: set global sql_mode = 'type whatever mode was recorded in step 10.1
15. That's it Now username with special characters is automatically processed as user created, no errors. I tested it with an unusually long russian made up name of: Эльмираанастасяитест ТестиновывоваятестоAlso, before all of the above was done (straight after your email to me) I also tested a super short made up russian name of Ж Ю (only 1 letter name and 1 letter surname) to see if wordpress would process it as it should, and of course it did (membership was created) which made me realise the problem was in length of varchar for user_nicename since longer names did not go through.

So, this is all good now, there is only 1 risk that somehow the increased varchar to 255 will somehow break the database in the long run. But thats not your problem guys, if anything happens I'll try to find a programmer to help with that part.

So, thank you for all your effort and not giving up on me, you guys were the best customer support I literally ever had, you never gave up on me and were very patient with me, I'm very grateful to you all for your help and guidance :)