Collation

[Midnight Modding]

Through testing, in my app, there was an "incorrect string value" exception.

I glanced in the database and noticed that my own apps' tables are not the same collation as the IPB ones. Mine say utf8, and the ones IPS updated for 4.x say utfmb4_.

Since I already have my schema all set up and am basically done with this app, what is the best way for me to update my tables since they weren't the right collation when I imported them into db schema? And can you tell me exactly the steps in me doing this?

I assume these was indeed what would cause that error I mentioned? It was when trying to save editor data with emoticons in my table.

And I guess I should do like IPS and have it have backups of the old ones? This will likely be complicated for me to get right.

(haven't looked to see if I can do it in db schema, but doubt it,a s I doubt IPS wanted to add ability to change table types etc... there)

Thanks!

[Midnight Modding]

I see in the schema file where I can change collation on them. But I assume when upgrading to 4.x the IPB installer itself won't upldate third party tables, so me editing this file would only work for fresh install, right?

[Midnight Modding]

I looked in my database for one of my main sites and I see apparently the upgrader upgrades all tables, even our third aprty ones. However, that is probably only if the site uses prefixes, right? Because otherwise, you'd have no idea if all of the tables are related to the suite.

[Midnight Modding]

Still stumped. I see in utf8mb4.php, it gets all tables (with prefix, if you use it, otherwise all tables) and then it only updates the ones whose "beginning of the table name" is a valid app key.

But on my live site, my third party apps' tables were indeed updated to utf8mb4 by the upgrader and those had not been converted to 4.x yet, so they would have been bypassed, yet they weren't.

As I dig deeper, I "think" this file is for something else and convert.php is the file that does it during the upgrade? But I still haven't tracked down how it chooses which tables to update the charset and collation for.

And even on my dev site, it's a headache deciding how to best update my live tables there to this charset and collation, since indexes and columns are also involved. I am tempted to create the files for the first version of this app and then uninstall the app and reinstall it, where my edited schema file will take care of this on install. But I still need to know if I need to do anything in the upgrader... which it seems no, from experience of my own site updated to 4.x, but the code so far isn;'t clear to me on whether it updates all or not.

[bfarber]

Your post(s ... x4) are a bit confusing.

What is the actual issue? If you just want to change the collation on your development installation, go into the database and change it. You can safely convert utf8 to utf8mb4 in phpmyadmin without doing anything special.

When someone installs your application, the charset/collation appropriate for their site is used, so you really don't need to sweat it much beyond that. If they are using utf8, then your tables will be created as utf8. If they are using utf8mb4 their tables will be created as utf8mb4.

I doubt this is the cause of a "incorrect string value" message at the end of the day, but you didn't really provide much info on that so I can't be sure.

[Midnight Modding]

4 hours ago, bfarber said:

Your post(s ... x4) are a bit confusing.

What is the actual issue? If you just want to change the collation on your development installation, go into the database and change it. You can safely convert utf8 to utf8mb4 in phpmyadmin without doing anything special.

When someone installs your application, the charset/collation appropriate for their site is used, so you really don't need to sweat it much beyond that. If they are using utf8, then your tables will be created as utf8. If they are using utf8mb4 their tables will be created as utf8mb4.

I doubt this is the cause of a "incorrect string value" message at the end of the day, but you didn't really provide much info on that so I can't be sure.

I'm pretty sure this is the problem because another time for the same situation the error was mixing of collation or w/e it's called. And the time that it gave the string value error I looked it up and saw where someone else said it was due to the collation of a table.

What triggered it is simply that someone selected an emoticon in the editor, clicked the button to submit the form, it then gave that error. So nothing unusual done, at all. Selected an emoticon and that's it.

Basically, here is what I am asking. When someone already has my 3.x version installed, will the IPS 4.x upgrader convert their table charset/collation for my tables when it does for the others? Jungle said he doesn't think so, but on my live site somehow my own apps' tables did get converted to utf8mb4 without me ever doing it myself.

So that's what I was asking. I know how to change my test site tables and I even saw in the IPS files where IPS does the charset/collation changing, but it was done in multiple places, so I couldn't quite figure out if it does it for all tables.

I saw in one place it does it to all tables if the beginning of their table names is a valid app key, but will my apps be considered valid loaded apps before updated to 4.x?

The reason I post so many posts is because as time goes by, I find out more myself, so I am trying to save time for the reader by letting them know if I already figure out more details. Also, this is a complicated situation.

[bfarber]

When the UTF8MB4 converter is ran, any tables with the same table prefix are converted, so yes the tables for your app will also be converted.

[Midnight Modding]

48 minutes ago, bfarber said:

When the UTF8MB4 converter is ran, any tables with the same table prefix are converted, so yes the tables for your app will also be converted.

That's what I figured, in the beginning, but when I went through the code, I wasn't sure. What if they don't use a table prefix, though? That was one concern I had. When I looked through the files, it appeared it would still do it, but there seems to be 2 totally different places in the files where it does this type of thing, so I am not quite sure.

Anyway, thanks so much for all of your help! I'm finally totally done with everything for a fresh install, including tons of new features. I am very pleased with how it's working and think I have the bugs ironed out and good performance.

[Midnight Modding]

Also, great point about fresh installs of my app doing the charset based on what the site is using. So I assume the problem was more that my imported tables on my test site were set up differently, not that a table MUST be utf8mb4, as long as that isn't what they have their db set to.