Pull Request for Issue #32542 (part).

Summary of Changes

This pull request (PR) removes display width modifiers for integer data types in DDL statements in all SQL scripts for installation and updates and from PHP code (small piece of code in the extension installer's database model to create the utf8 conversion table when updating very old installations) for MySQL and MariaDB databases.

It is only relevant for MySQL and MariaDB databases, i.e. not for PostgreSQL or MS SQL Server.

These reason for these changes is that MySQL have deprecated the display width modifier for integer data type columns beginning with server version 8.0.17, see here in their documentation, which causes warnings on database servers of these versions when running the relevant SQL statements e.g. in phpMyAdmin, and as MySQL don't use semantic versioning (at least not in x.0 versions), they might stop to support it with any future version, and so these statement will cause SQL errors, and Joomla installation or updates will fail.

In opposite to this, MariaDB still continue to use and support the display width modifier for integer data type columns.

That means we have to kinds of database servers regarding these modifiers:

1. MySQL server versions lower than 8.0.17 or any version of MariaDB server which use these modifiers
2. MySQL server versions 8.0.17 or later which don't use these modifiers

As explained in issue #32542, MySQL docs and MariaDB docs, the display width modifier for integer data type has no impact on storage size or value range. It is only relevant when using the zerofill attribute on such a database column, what the CMS core never did.

In my opinion there are only 2 possible reasons why the attribute has been used by the CMS core:

1. The database schema checker and fixer needed it because it is part of the type returned for a database column when checking the data type if not a MySQL server versions 8.0.17 or later, so you had to specify it in the DDL.
   This has already been fixed with PRs #28501 and #32605 , so that's not necessary anymore.
2. When looking on how much effort people have put into the display width values, it seems there was a misunderstanding so that many contributors and maintainers thought it an impact on value range or storage size, maybe due to the similar look to e.g. float(10), real(10), double(10), where the 10 is the precision and not a display width.

The solution provided by this PR here in order to be safe from future drop of support for display width modifiers by MySQL is not so use the display width modifier anymore in any SQL statement.

For DDL statements like e.g. CREATE TABLE or ALTER TABLE ... MODIFY ..., the result is different for the 2 types of databases when not using the display width modifier:

1. On MySQL server versions lower than 8.0.17 or any version of MariaDB server, the columns will have the default display width for the particular data type, which are:

• tinyint(4)
• tinyint(3) unsigned
• smallint(6)
• smallint(5) unsigned
• mediumint(9)
• mediumint(8) unsigned
• int(11)
• int(10) unsigned
• bigint(20)
• bigint(20) unsigned

2. On MySQL server versions 8.0.17 or later the columns will not have any display width.

Testing Instructions

Requirement

You need a MySQL or MariaDB database.

Testers please report back which of the following 2 kinds of databases you have used for the test:

• MySQL database server with versions lower than 8.0.17 or any server version of MariaDB
• MySQL database server with version 8.0.17 or later

If you have both scenarios available, please test both.

Test 1: Reproduce the issue

This test is optional and can be only executed when having a MySQL database server of version 8.0.17 or later.

If you don't have that kind of MySQL database server, skip this test.

During my tests I wasn't able to configure my MySQL 8.0.20 server in a way that the deprecation warnings for the display widths could be shown in the server log file, and you can believe me I've tried a lot of things.

Furthermore, it appeared that when importing an SQL script using display widths for integers in DDL into phpMyAdmin, there are also no warnings shown.

So if you want to see the issue you have to do as follows:

1. In phpMyadmin, select a database.

2. Copy the first CREATE TABLE statements for the #__assets table (or any other which contains display widths) from file installation/sql/mysql/joomla.sql of the staging branch (i.e. not from this PR) into the SQL command window.
   Hint: You can use also several statements at once, but you should not use the complete content of the SQL script since in this case phpMyAdmin (or the server?) seem to suppress the warnings due to their high count.

3. Execute the SQL in the SQL command window (no need to replace the table name prefix before).

Result: You get warnings Warning: #1681 Integer display width is deprecated and will be removed in a future release..

4. Remove the table created in the previous step.

5. Repeat steps 2 and 3 but using a statement from the installation/sql/mysql/joomla.sql file of this PR.

Result: Don't get the warnings from step 3.

Test 2: New installation

The automated system tests make already sure that this PR doesn't produce an SQL error in the installation SQL, but it doesn't check if we have database problems in the schema checker after that or not, therefore this test.

1. Make again a new installation with the patch of this PR applied.

2. Login to backend.

3. Go to "Extensions -> Manage -> Database" and verify that no database problems are shown.

Result: No database problems found.

4. Using a tool like e.g. phpMyAdmin, check data type of columns with integer data types.

Result:

• On MySQL server versions lower than 8.0.17 or any version of MariaDB server, the display width values of the integer type columns have the default values for that data type. See above in the summary of changes for the complete list.
• On MySQL server versions 8.0.17 or later, the integer type columns don't have any display width values.

Test 3: Update from a Joomla version older than 3.9.0.

Unfortunately the packages built by drone are only for updating previous 3.9.x versions, beginning with 3.9.0, but not older.

This means that the changes on the update SQL scripts can't be tested with such an update package.

Therefore I've build a modified update package for updating older versions.

This can be downloaded from here and then used for the test with "Upload & Update": https://test5.richard-fath.de/Joomla_3.9.26-dev+pr.32606-Development-Update_Package.zip.

The starting point is a clean Joomla installation with a version older than 3.9.0 (the older, the better, as long as possible on your testing environment).

1. Login to backend.

2. Make sure to see any database errors or warnings by switching on "Debug System" and setting "Error Reporting" to "Maximum" or "Development" in Global Configuration.

3. Update to current staging + the patch of this PR applied by using the update package mentioned above, using the "Upload & Update" method.

4. While the update runs, verify that no SQL errors or warnings are shown in backend or in your MySQL or MariaDB server's log file or in the PHP error log.

5. Go to "Extensions -> Manage -> Database" and verify that no database problems are shown.

Result: No database problems found.

6. Using a tool like e.g. phpMyAdmin, check data type of columns with integer data types.

Result:

1. Display widths of database columns which existed already before the update and which haven't been modified for other changes haven't been changed.
2. New tables have been created with display widths for columns with integer data types depending on the type of database:

• On MySQL server versions lower than 8.0.17 or any version of MariaDB server, the display width values of the new integer type columns have the default values for that data type with this PR applied, see above in the summary of changes for the complete list.
• On MySQL server versions 8.0.17 or later, the new integer type columns don't have any display width values with this PR applied.

3. The same as 2. applies to database columns with integer data types which have been modified for other purpose during the update, e.g. type change or change of default value or null values.

Actual result BEFORE applying this Pull Request

SQL scripts and at one place the installer's database model use the unnecessary and for MySQL versions 8.0.17 or later deprecated display width modifier for integer data types.

Expected result AFTER applying this Pull Request

No display width modifier is used for integer data types at any place in SQL or other code except of unit tests coming with the framework package.

After a new installation on MySQL database servers with versions lower than 8.0.17 or any version of MariaDB servers, database columns with integer data types have the default display widths for the particular data type. On MySQL database servers with versions 8.0.17 or newer, such database columns have no display widths.

When updating from 3.8.x or older, display widths haven't been changed for database columns which existed already before the update and haven't been modified by the update for other changes than display widths. For newly created or modified columns, the above statement for new installations applies.

Documentation Changes Required

None.

Other information

@zero-24 @wilsonge This PR needs to be merged up into 3.10-dev sooner of later after it has been merged into staging, so that with PR #32607 the 3.10-dev branch will be complete regarding the issue fixed here. But this PR here does not need to be merged up into 4.0-dev, i.e. any conflicts resulting in an upmerge in 4.0-dev done after my PR #32608 has been merged into the 4.0-dev branch could be solved by accepting the 4.0-dev version of the particular conflict.