Mybb: DB show_fields_from() method's return varies in format for different db engines

[yuliu]

The function show_fields_from($table) that shows the "show fields from" command for a specific table returns results in different formats for different db engines. It makes some particular uses of this function fail, for example, the merge system's column length calculation (https://github.com/mybb/merge-system/pull/260 ).

For mysql (now deprecated) and mysqli, the method directly uses SQL SHOW FIELDS FROM table_name to query, the results have columns (on MySQL 5.7) "Field", "Type", "Null", "Key", "Default", "Extra" and returns them.
mysql: https://github.com/mybb/mybb/blob/mybb_1824/inc/db_mysql.php#L1111
mysqli: https://github.com/mybb/mybb/blob/mybb_1824/inc/db_mysqli.php#L1105

For pqsql and sqlite, the method uses some different SQL queries to get an intermediate result and reformat it as the final results that have columns "Field", "Extra" and returns them. It's worth noting that the two methods have different results in the "Extra" field. For pgsql, the information of column types is totally forgotten.
pqsql: https://github.com/mybb/mybb/blob/mybb_1824/inc/db_pgsql.php#L1134
sqlite: https://github.com/mybb/mybb/blob/mybb_1824/inc/db_sqlite.php#L954

IMO we could use the skeleton of MySQL's SHOW FIELDS FROM's result to format results of pqsql and sqlite.

Original thread: DB show_fields_from() method's return varies in format for different db engines

[yuliu]

This function for MySQL and probably MariaDB, or SHOW FIELDS FROM/SHOW COLUMNS FROM, won't return integer length anymore as of MySQL 8.0.19.

Quoted from Changes in MySQL 8.0.19 (2020-01-13, General Availability):

Display width specification for integer data types was deprecated in MySQL 8.0.17, and now statements that include data type definitions in their output no longer show the display width for integer types, with these exceptions:

The type is TINYINT(1). MySQL Connectors make the assumption that TINYINT(1) columns originated as BOOLEAN columns; this exception enables them to continue to make that assumption.

The type includes the ZEROFILL attribute.

This change applies to tables, views, and stored routines, and affects the output from SHOW CREATE and DESCRIBE statements, and from INFORMATION_SCHEMA tables.

For DESCRIBE statements and INFORMATION_SCHEMA queries, output is unaffected for objects created in previous MySQL 8.0 versions because information already stored in the data dictionary remains unchanged. This exception does not apply for upgrades from MySQL 5.7 to 8.0, for which all data dictionary information is re-created such that data type definitions do not include display width. (Bug #30556657, Bug #97680)

A user on MySQL 8.0.22 reported faulty conversion results by using the merge system which greatly relies on this function to get column lengths in a community thread: Merge from phpbb 3.2.0 to 1.8.24 not transvering forums, treads and posts correctly.

This function needs an overhaul IMO and I marked this issue with high priority. And because MySQL won't return integer length with that query anymore, I'll open an issue in the merge system's repo to address this problem.

Any thoughts on this, @mybb/developers ?

[euantorano]

So it sounds like MySQL has dropped integer widths, which actually makes life much easier. We can now just always assume max width for the type when using new MySQL versions (and this should be backwards compatible too I believe).

So, instead of writing INT(11), we now write INT.

Looking at what we actually need from show_fields_from, I believe it's the following:

• Field Name
• Field Type
• Nullable?

Do we need any other information in any scenarios?

We should make the method always return the same type/keys, which may mean parsing output on other platforms.

[yuliu]

IMO the merge system would just need the above -- column length could be inferred from Field Type. We might need to make a decision on having column length info in show_fields_from() or just in the merge system. Hmm, varchar needs length info to be meaningful.

Oh sorry varchar(length) is a Field Type.

We might need to make a decision on having column length info as a dedicated field in show_fields_from() or just in the merge system.

And I think the followings are also needed (at least they're already there):

• auto_increment in Extra

Don't know if default is a candidate.