Database Issue after update

[tryimg]

🎯Description of the issue

I updated the version and it gave an error that Can't connect to https://tryimg.com/update. After the page was refreshed it showed me another error screen that I need to manually update the SQL with a query. When I'm trying that query in phpMyAdmin, its giving me errors as well. Screenshot attached.

▶🚶‍Reproduction steps

1. Go to Dashboard > Update

😢Unexpected result

Attached is the image.

📃Error log message

NA

 

[tryimg]

Here's the details of the phpmyadmin error.

 

[Rodolfo]

Usually you don't need the quotes there unless you are running mysql in strict mode. Try using these queries:

[CODE lang="sql" title="sql"]ALTER TABLE `chv_albums` CONVERT TO CHARACTER SET "utf8mb4" COLLATE "utf8mb4_unicode_ci";
ALTER TABLE `chv_categories` CONVERT TO CHARACTER SET "utf8mb4" COLLATE "utf8mb4_unicode_ci";
ALTER TABLE `chv_deletions` CONVERT TO CHARACTER SET "utf8mb4" COLLATE "utf8mb4_unicode_ci";
ALTER TABLE `chv_images` CONVERT TO CHARACTER SET "utf8mb4" COLLATE "utf8mb4_unicode_ci";
ALTER TABLE `chv_ip_bans` CONVERT TO CHARACTER SET "utf8mb4" COLLATE "utf8mb4_unicode_ci";
ALTER TABLE `chv_pages` CONVERT TO CHARACTER SET "utf8mb4" COLLATE "utf8mb4_unicode_ci";
ALTER TABLE `chv_settings` CONVERT TO CHARACTER SET "utf8mb4" COLLATE "utf8mb4_unicode_ci";
ALTER TABLE `chv_storages` CONVERT TO CHARACTER SET "utf8mb4" COLLATE "utf8mb4_unicode_ci";
ALTER TABLE `chv_users` CONVERT TO CHARACTER SET "utf8mb4" COLLATE "utf8mb4_unicode_ci";[/CODE]

If that doesn't work then the actual problem is that you don't have that charset in your mysql server.

 

[tryimg]

Even with quotes it’s showing the exact tooltip error, but when I run the query it shows the following. So what’s the issue with the column size?

 

[tryimg]

I just noticed that the collation of the tables is something else? And not utf8mb4. See the attached screenshot. Is that a problem?

 

[Rodolfo]

The whole query includes changes in the columns to fit the key size. You are skipping these queries? If so, the thing will always fail to convert the charset.

 

[tryimg]

I’m just simply copy/pasting your entire code from here.

 

[zeb]

Hello,
Same for me, it's not good

 

[bilderupload]

Guys open my.cnf in /etc/mysql/ under linux, and change following

[client]
default-character-set = utf8mb4

[mysqld]
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix

character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-character-set-client-handshake

[mysql]
default-character-set = utf8mb4

Than restart the mysql server, then run the cli command line script to set and convert new charset on new collation:

php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci

Thats it..

 

[zeb]

Hello,
Not possible for me, I'm on a shared server

 

[Rodolfo]

Tested on MariaDB and the whole query should look like this:

[CODE lang="sql" title="3.13.0 update query" highlight="2-13"]
UPDATE `chv_settings` SET `setting_value` = 1 WHERE `setting_name` = 'maintenance';
ALTER TABLE `chv_logins` MODIFY `login_secret` text DEFAULT NULL COMMENT 'The secret part';
ALTER TABLE `chv_settings` MODIFY `setting_value` text ;
ALTER TABLE `chv_settings` MODIFY `setting_default` text ;
ALTER TABLE `chv_storages` MODIFY `storage_key` text ;
ALTER TABLE `chv_storages` MODIFY `storage_secret` text ;
ALTER TABLE `chv_ip_bans` MODIFY `ip_ban_ip` varchar(191) NOT NULL;
ALTER TABLE `chv_ip_bans` MODIFY `ip_ban_message` text DEFAULT NULL;
ALTER TABLE `chv_pages` MODIFY `page_internal` varchar(191) DEFAULT NULL;
ALTER TABLE `chv_users` MODIFY `user_username` varchar(191) NOT NULL;
ALTER TABLE `chv_users` MODIFY `user_email` varchar(191) DEFAULT NULL;
ALTER TABLE `chv_users` MODIFY `user_image_expiration` varchar(191) DEFAULT NULL;
ALTER TABLE `chv_users` MODIFY `user_registration_ip` varchar(191) NOT NULL;
ALTER TABLE `chv_images` ENGINE=InnoDB;
ALTER TABLE `chv_albums` ENGINE=InnoDB;
ALTER TABLE `chv_users` ENGINE=InnoDB;
ALTER TABLE `chv_redirects` ENGINE=InnoDB;
ALTER TABLE `chv_albums` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `chv_categories` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `chv_deletions` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `chv_images` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `chv_ip_bans` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `chv_pages` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `chv_settings` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `chv_storages` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `chv_users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
UPDATE `chv_settings` SET `setting_value` = "3.13.0" WHERE `setting_name` = "chevereto_version_installed";
UPDATE `chv_settings` SET `setting_value` = 0 WHERE `setting_name` = "maintenance";
[/CODE]

As you may notice, the system changes the table columns before the charset change. If you are having issues here chances are that you modified the default database structure or you modified something else in the code.

If you didn't make any change then check your DB server for support for UTF8MB4.

 

[tryimg]

I didn't update anything on my own.
Again when I pasted the code in the SQL window of phpmyadmin, I am seeing the same alert error message. Where the hell is this COMMA missing?
See attached!

@bilderupload I can't play with that because I'm on shared hosting as well.

Let me check with my hosting service as well, but the problem is this missing comma somewhere.

 

[tryimg]

I found these two things while googling.

https://github.com/phpmyadmin/sql-parser/issues/218
https://github.com/phpmyadmin/sql-parser/issues/229

 

[tryimg]

Ok so I asked my hosting company to check the issue. They made some changes at the backend and now I was able to run the https://tryimg.com/update. Looks like everything is working fine now

 

[zeb]

I was not so lucky, I'm still stuck, I'm going to look for myself ...

 

[tryimg]

My hosting company changed innodb_default_row_format in to dynamic at the server end.

 

[friv]

For everyone which has faced with those problem this is due PHP 7.3 version. Please be sure that you have enabled PHP 7.3 in WHM.Maybe you still using 7.2 and because of that error appear.

 

[Rodolfo]

For everyone which has faced with those problem this is due PHP 7.3 version. Please be sure that you have enabled PHP 7.3 in WHM.Maybe you still using 7.2 and because of that error appear.

Talking about php running in phpmyadmin right?

 

[friv]

Talking about php running in phpmyadmin right?

Go to WHM then in search bar at left side up type "Php manager",then scroll down and tick domain which you want to work with PHP 7.3.

 

[Rodolfo]

My comment is because you are saying that this issue is PHP 7.3 related and the query in this case was executed directly in phpmyadmin. Meaning that you are saying that the problem is somehow related to how php "pass" the queries?

The demo runs 7.2 + MySQL 5.5 (not Maria). My dev machine uses 7.3 + MariaDB 10 and I never encountered this issue. Beta testers also didn't noticed any related issue. Try to execute the queries directly in the MySQL using another client (not PHP based) so you can discard that the issue is PHP related, in other words, don't say that is PHP 7.3 without actually isolating the issue. Please be more responsible when posting.

I think that the problem here is very clear, the charset change is restricted because you haven't modified yet the other colums (see previous post). I won't explain the implications, but the thing is that the columns needs to be trimmed before changing to utf8mb4. The new charset takes more chars to store the same data and in the conversion we manage to convert from utf8 (base) -> utf8 (trimmed) -> utf8mb4 (including data and indexes).

I prepare the system to fit my db schema, and my schema works with this update. If you changed the DB tables, columns, indexes, you will have to accommodate your own changes to fit my schema.