E107 Stating MySQL updates needed and trying to add INT lengths which will be deprecated (MySQL 8.0)

[dimante]

Bug Description

Scripts are not looking for MySQL version and in version 8.0 INT lengths are to be deprecated. This causes the script not to apply to the database and the update button remains even after it says it was successful (That's a bug too).

Here is what it is proposing with debug:

Here is what happens when you try with each first core:
Successfully updated [ALTER TABLE e107_admin_log CHANGE dblog_id dblog_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT]
Successfully updated [ALTER TABLE e107_admin_log CHANGE dblog_datestamp dblog_datestamp INT(10) UNSIGNED NOT NULL DEFAULT '0']
Successfully updated [ALTER TABLE e107_admin_log CHANGE dblog_microtime dblog_microtime INT(10) UNSIGNED NOT NULL DEFAULT '0']
Successfully updated [ALTER TABLE e107_admin_log CHANGE dblog_type dblog_type TINYINT(3) NOT NULL DEFAULT '0']
Successfully updated [ALTER TABLE e107_admin_log CHANGE dblog_user_id dblog_user_id INT(10) UNSIGNED NOT NULL DEFAULT '0']
Successfully updated [ALTER TABLE e107_audit_log CHANGE dblog_id dblog_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT]
Successfully updated [ALTER TABLE e107_audit_log CHANGE dblog_datestamp dblog_datestamp INT(10) UNSIGNED NOT NULL DEFAULT '0']
Successfully updated [ALTER TABLE e107_audit_log CHANGE dblog_microtime dblog_microtime INT(10) UNSIGNED NOT NULL DEFAULT '0']
Successfully updated [ALTER TABLE e107_audit_log CHANGE dblog_user_id dblog_user_id INT(10) UNSIGNED NOT NULL DEFAULT '0']
Successfully updated [ALTER TABLE e107_banlist CHANGE banlist_id banlist_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT]
Successfully updated [ALTER TABLE e107_banlist CHANGE banlist_bantype banlist_bantype TINYINT(3) NOT NULL DEFAULT '0']
Successfully updated [ALTER TABLE e107_banlist CHANGE banlist_datestamp banlist_datestamp INT(10) UNSIGNED NOT NULL DEFAULT '0']
Successfully updated [ALTER TABLE e107_banlist CHANGE banlist_banexpires banlist_banexpires INT(10) UNSIGNED NOT NULL DEFAULT '0']
Successfully updated [ALTER TABLE e107_banlist CHANGE banlist_admin banlist_admin SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0']
Successfully updated [ALTER TABLE e107_comments CHANGE comment_id comment_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT]
Successfully updated [ALTER TABLE e107_comments CHANGE comment_pid comment_pid INT(10) UNSIGNED NOT NULL DEFAULT '0']
Successfully updated [ALTER TABLE e107_comments CHANGE comment_item_id comment_item_id INT(10) UNSIGNED NOT NULL DEFAULT '0']
Successfully updated [ALTER TABLE e107_comments CHANGE comment_author_id comment_author_id INT(10) UNSIGNED NOT NULL DEFAULT '0']
Successfully updated [ALTER TABLE e107_comments CHANGE comment_datestamp comment_datestamp INT(10) UNSIGNED NOT NULL DEFAULT '0']
Successfully updated [ALTER TABLE e107_comments CHANGE comment_blocked comment_blocked TINYINT(3) UNSIGNED NOT NULL DEFAULT '0']
Successfully updated [ALTER TABLE e107_comments CHANGE comment_lock comment_lock TINYINT(1) UNSIGNED NOT NULL DEFAULT '0']
Successfully updated [ALTER TABLE e107_comments CHANGE comment_share comment_share TINYINT(1) UNSIGNED NOT NULL DEFAULT '0']
Successfully updated [ALTER TABLE e107_core_media CHANGE media_id media_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT]
Successfully updated [ALTER TABLE e107_core_media CHANGE media_datestamp media_datestamp INT(10) UNSIGNED NOT NULL DEFAULT '0']
Successfully updated [ALTER TABLE e107_core_media CHANGE media_author media_author INT(10) UNSIGNED NOT NULL DEFAULT '0']
Successfully updated [ALTER TABLE e107_core_media CHANGE media_size media_size INT(20) UNSIGNED NOT NULL DEFAULT '0']
Successfully updated [ALTER TABLE e107_core_media_cat CHANGE media_cat_id media_cat_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT]
Successfully updated [ALTER TABLE e107_core_media_cat CHANGE media_cat_class media_cat_class INT(5) DEFAULT '0']
Successfully updated [ALTER TABLE e107_core_media_cat CHANGE media_cat_order media_cat_order INT(3) UNSIGNED NOT NULL DEFAULT '0']
Successfully updated [ALTER TABLE e107_cron CHANGE cron_id cron_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT]
Successfully updated [ALTER TABLE e107_cron CHANGE cron_lastrun cron_lastrun INT(13) UNSIGNED NOT NULL]
Successfully updated [ALTER TABLE e107_cron CHANGE cron_active cron_active INT(1) UNSIGNED NOT NULL]
[more}
Successfully updated [ALTER TABLE e107_logstats CHANGE log_uniqueid log_uniqueid INT(11) NOT NULL AUTO_INCREMENT]
But it doesn't and leaves this on reload.

Jimako @Jimmi08 07:29
@dimante what happens if you click on AppStrap update? if nothing, go to Database Tools, Db table verify and check that plugin manually. There wasn't any update. Thanks

dimante @dimante 07:30
Supporting database log:
This is a real issue..
Please let me post the output when I try to execute the proposed script on the database server
1 3 07:30:25 ALTER TABLE e107_admin_log CHANGE dblog_id dblog_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT 0 row(s) affected, 1 warning(s):
1681 Integer display width is deprecated and will be removed in a future release.
Records: 0 Duplicates: 0 Warnings: 1 0.062 sec
1 4 07:30:25 ALTER TABLE e107_admin_log CHANGE dblog_datestamp dblog_datestamp INT(10) UNSIGNED NOT NULL DEFAULT '0' 0 row(s) affected, 1 warning(s):
1681 Integer display width is deprecated and will be removed in a future release.
Records: 0 Duplicates: 0 Warnings: 1 0.031 sec
1 5 07:30:25 ALTER TABLE e107_admin_log CHANGE dblog_microtime dblog_microtime INT(10) UNSIGNED NOT NULL DEFAULT '0' 0 row(s) affected, 1 warning(s):
1681 Integer display width is deprecated and will be removed in a future release.
Records: 0 Duplicates: 0 Warnings: 1 0.047 sec
1 6 07:30:26 ALTER TABLE e107_admin_log CHANGE dblog_type dblog_type TINYINT(3) NOT NULL DEFAULT '0' 0 row(s) affected, 1 warning(s):
1681 Integer display width is deprecated and will be removed in a future release.
Records: 0 Duplicates: 0 Warnings: 1 0.031 sec
1 7 07:30:26 ALTER TABLE e107_admin_log CHANGE dblog_user_id dblog_user_id INT(10) UNSIGNED NOT NULL DEFAULT '0' 0 row(s) affected, 1 warning(s):
1681 Integer display width is deprecated and will be removed in a future release.
Records: 0 Duplicates: 0 Warnings: 1 0.015 sec
1 8 07:30:26 ALTER TABLE e107_audit_log CHANGE dblog_id dblog_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT 0 row(s) affected, 1 warning(s):
1681 Integer display width is deprecated and will be removed in a future release.
Records: 0 Duplicates: 0 Warnings: 1 0.031 sec
1 9 07:30:26 ALTER TABLE e107_audit_log CHANGE dblog_datestamp dblog_datestamp INT(10) UNSIGNED NOT NULL DEFAULT '0' 0 row(s) affected, 1 warning(s):
1681 Integer display width is deprecated and will be removed in a future release.
Records: 0 Duplicates: 0 Warnings: 1 0.015 sec
1 10 07:30:26 ALTER TABLE e107_audit_log CHANGE dblog_microtime dblog_microtime INT(10) UNSIGNED NOT NULL DEFAULT '0' 0 row(s) affected, 1 warning(s):
1681 Integer display width is deprecated and will be removed in a future release.
Records: 0 Duplicates: 0 Warnings: 1 0.016 sec
1 11 07:30:26 ALTER TABLE e107_audit_log CHANGE dblog_user_id dblog_user_id INT(10) UNSIGNED NOT NULL DEFAULT '0' 0 row(s) affected, 1 warning(s):
1681 Integer display width is deprecated and will be removed in a future release.
Records: 0 Duplicates: 0 Warnings: 1 0.016 sec
1 12 07:30:26 ALTER TABLE e107_banlist CHANGE banlist_id banlist_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT 0 row(s) affected, 1 warning(s):
1681 Integer display width is deprecated and will be removed in a future release.
Records: 0 Duplicates: 0 Warnings: 1 0.031 sec
1 13 07:30:26 ALTER TABLE e107_banlist CHANGE banlist_bantype banlist_bantype TINYINT(3) NOT NULL DEFAULT '0' 0 row(s) affected, 1 warning(s):
1681 Integer display width is deprecated and will be removed in a future release.
Records: 0 Duplicates: 0 Warnings: 1 0.031 sec
See, it's not wanting to do what the software is proposing and it ignores the directives. As far as AppStrap, I think you and I have an understanding, I don't mess with it because doing things like updates to the database for it make my site pages, etc go away sometimes and yesterday was already a lost day :-)
The update script needs to be tuned to know what MySQL version is being run methinks.
@Jimmi08 But just to tell you, the app strap update from what I can see in the codebase is making similar suggestions.

[dimante]

MYSQL version 8.0.21 (But has been a problem since 8) Sorry I forgot to add this!

[dimante]

Object info from the database for a table:

dblog_id | int UN AI PK
dblog_datestamp | int UN
dblog_microtime | int UN
dblog_type | tinyint
dblog_eventcode | varchar(10)
dblog_user_id | int UN
dblog_ip | varchar(45)
dblog_title | varchar(255)
dblog_remarks | text

This line in the update states it wants to do INT(10) and is getting denied:
ALTER TABLE e107_admin_log CHANGE dblog_id dblog_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

Hope this helps!

[Deltik]

I can confirm this bug using /e107_admin/db.php?mode=verify_sql with this MySQL version:

www-data@e107dev:~/html/e107$ mysql --version
mysql  Ver 8.0.20-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

(Click image for full screenshot)

[Deltik]

Fixed by aee67c1: