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 MySQL and MariaDB databases.

It is only relevant for MySQL and MariaDB databases, i.e. not PostgreSQL.

It's the J4 equivalent of PR #32606 for staging. See that PR for a detailed description of the reasons for this change.

In addition to the change for the display width modifier, this PR fixes also old null dates being used in SQL scripts for the integration tests. I had to touch the script for MySQL anyway, so I decided to fix that, too, also for PostgreSQL. This change will not need any tests beside the integration tests passing in drone.

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/base.sql of the 4.0-dev 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/base.sql file of this PR.

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

Test 2: New installation

1. Make a new installation with the patch of this PR applied, using an empty database.

2. Login to backend.

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

Result: No database problems found (except of one problem about database version not matching the CMS version when using the installation package created by drone for this PR).

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 the summary of changes of PR #32606 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 Joomla 3.10

The starting point is a clean Joomla 3.10 installation (3.10-dev branch or latest 3.10 nightly build).

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 4.0-dev + the patch of this PR applied by using one of the following 2 methods:

• When using "Live Update", use the custom update URL https://ci.joomla.org/artifacts/joomla/joomla-cms/4.0-dev/32608/downloads/42443/pr_list.xml created by drone.
• When using "Upload &Update", use the update package https://ci.joomla.org/artifacts/joomla/joomla-cms/4.0-dev/32608/downloads/42443/Joomla_4.0.0-beta8-dev+pr.32608-Development-Update_Package.zip built by drone.

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 "System-> Manage -> Database" and verify that no database problems are shown.

Result: No database problems found except of one problem about database version not matching the CMS version. This is normal when using updates package created by drone for PR's.

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 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 statements.

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.10, 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

@wilsonge When PR's #32606 and #32607 will be merged up from staging or 3.10-dev into 4.0-dev after this PR has been merged, it will cause merge conflicts in the installation/sql/mysql/base.sql file. In this case use the file from the 4.0-dev branch, there should not be any changes on this file coming from staging or 3.10-dev as far as I know.