Thanks for the report.

First of all, the InnoDB message is really misleading, because in fact the column is TIMESTAMP, both in MySQL and MariaDB. Probably a different internal format of timestamp.

I cannot reproduce it if in the described scenario I run mysql_upgrade on 10.3 before further upgrading to 10.6; but otherwise, starting from 10.4, it is easily reproducible.

I suppose the reason is that while mysql_upgrade actually runs an unconditional ALTER on the table

alter table mysql.innodb_table_stats modify last_update timestamp not null default current_timestamp on update current_timestamp, modify table_name varchar(199)

even if the columns are already of the same type, in 10.3 it works via table-copy and thus upgrades it, while in 10.4+ it remains essentially a no-op.

If so, then

alter table mysql.innodb_table_stats force;

alter table mysql.innodb_index_stats force;

should help as a workaround, to get rid of the error messages. Possibly server restart will also be needed.

I reproduced this with a copy of a MySQL 5.7 data directory that is attached to MDEV-30311. I started the server with --skip-grant-tables.

For the first ALTER TABLE, the ha_innobase::commit_inplace_alter_table() would do nothing, because ha_alter_info->handler_flags=ALTER_COLUMN_DEFAULT, a flag that InnoDB does not care about.

alter table mysql.innodb_table_stats modify last_update timestamp not null default current_timestamp on update current_timestamp, modify table_name varchar(199);

--returns 4 rows with prtype=525575

select prtype from information_schema.innodb_sys_columns where name='last_update';

alter table mysql.innodb_table_stats modify last_update timestamp not null default current_timestamp on update current_timestamp, modify table_name varchar(199), force;

--returns 3 rows with prtype=525575 and 1 row with prtype=526087

select prtype from information_schema.innodb_sys_columns where name='last_update';

thirumarko If you give some pointers on how this should be fixed and how PRTYPE is supposed to work in upgrades, Christianggm could take a stab at fixing this. He has already sent fixes to other bugs in `mariadb-upgrade` before that you merged.

otto and Christianggm, the .frm data type should be mapped to an InnoDB mtype in get_innobase_type_from_mysql_type(). The prtype should be determined by its callers, such as create_table_info_t::create_table_def(). I did not dig deeper into this, but I suspect that some logic in sql/sql_alter.cc fails to take this conversion into account. For string data types, a special interface was defined to allow metadata-only conversion of compatible string encodings. Its latest revision is called ha_innobase::can_convert_nocopy(). I suspect that something in the data type compatibility checks in the SQL layer was relaxed too much, or this function needs to be called (and account for) other data types as well.

One thing worth noting is that in InnoDB, signed integer types use a modified two’s complement notation where the sign bit is inverted, to allow memcmp() to sort negative values first. InnoDB uses big-endian storage format. I think that BINARY(4) should be compatible with INT UNSIGNED. That is, a metadata-only conversion is definitely possible and desirable.

I've submitted a PR (https://github.com/MariaDB/server/pull/2555) to address this failure at scripts/mysql_system_tables_fix.sql using the force flag as marko did in previous comments. So at least mysql_upgrade tool can ensure that the PRTYPE is being properly updated for this specific case until we figure out and fix the real cause of the issue.

This issue is assigned to thiru - could you please review https://github.com/MariaDB/server/pull/2555 so we can merge the fix and close this issue?

That pull request looks more like a work around than a fix.

I think that we must fully understand the reason of the failure.

In hexadecimal notation, the prtype​s in question are 0x80707 and 0x80507. The most significant hexadecimal digit refers to my_charset_latin1.number==8, which actually does not matter at all here. The difference is in the DATA_UNSIGNED flag (0x200). I tried the following:

--source include/have_innodb.inc

CREATE TABLE t(a INT PRIMARY KEY, b INT UNSIGNED NOT NULL) ENGINE=InnoDB;

ALTER TABLE t MODIFY b INT NOT NULL, ALGORITHM=INSTANT;

SELECT prtype FROM information_schema.innodb_sys_columns WHERE name='b';

ALTER TABLE t FORCE;

SELECT prtype FROM information_schema.innodb_sys_columns WHERE name='b';

DROP TABLE t;

This would fail with ER_ALTER_OPERATION_NOT_SUPPORTED_REASON (1846): ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY. Apparently, this problem is specific to the internal implementation of the TIMESTAMP data type. Possibly, mysql56_temporal_format (see MDEV-15225) plays a role here.

I see that bar has changed something related to timestamps in the past. I think that he should best explain what is going on here, how to reproduce the bug in the regression test suite and how to fix it.

After further investigation, it looks like the PRTYPE for the last_update field at both innodb_table_stats and innodb_index_stats tables was being properly updated for MariaDB 10.6.10, so it looks like some kind of regression was included on MariaDB 10.6.11.

It does not look like a regression introduces in 10.6.11.

Tried with MariaDB-10.6.10:

commit fd0bdd3180a7d5f4b9804d372d6a63b6a202818c (HEAD -> 10.6, tag: mariadb-10.6.10)

as follows:

• Copied data directory from MySQL-5.7 to MariaDB-10.6.10
• Started mariadbd --skip-grant-tables
• Run this SQL script in the comman line client:

  SET @@global.mysql56_temporal_format=1;

  use mysql;

  alter table innodb_table_stats modify last_update timestamp not null default current_timestamp on update current_timestamp, modify table_name varchar(199);

  create or replace table t1 (a timestamp) ENGINE=InnoDB;

  flush tables;

  SHOW FIELDS IN t1;

  SHOW FIELDS IN innodb_table_stats LIKE 'last_update';

  SELECT table_name, last_update FROM innodb_table_stats WHERE table_name='t1';

• Found these lines in the error log:

  2023-04-17 14:39:50 4 [ERROR] InnoDB: Column last_update in table mysql.innodb_index_stats is BINARY(4) NOT NULL but should be INT UNSIGNED NOT NULL

  2023-04-17 14:39:50 4 [ERROR] InnoDB: Column last_update in table mysql.innodb_index_stats is BINARY(4) NOT NULL but should be INT UNSIGNED NOT NULL

  2023-04-17 14:39:50 4 [ERROR] InnoDB: Fetch of persistent statistics requested for table `mysql`.`t1` but the required system tables mysql.innodb_table_stats an d mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

Reassigning to marko, as the problem resides in this code in innobase/dict/dict0stats.c:

static const dict_table_schema_t table_stats_schema =

{

  {C_STRING_WITH_LEN(TABLE_STATS_NAME)}, TABLE_STATS_NAME_PRINT, 6,

  {

    {"database_name", DATA_VARMYSQL, DATA_NOT_NULL, 192},

    {"table_name", DATA_VARMYSQL, DATA_NOT_NULL, 597},

    {"last_update", DATA_INT, DATA_NOT_NULL | DATA_UNSIGNED, 4},

The data type for last_update should be DATA_FIXBINARY.
It will correspond to the default value of @@global.mysql56_temporal_format which is 1.

However, it's not clear what to do on installations with @@global.mysql56_temporal_format set to 0.

Hi bar maybe I'm missing something, but it seems that when you were trying to reproduce the issue you just altered the field for innodb_table_stats:

alter table innodb_table_stats modify last_update timestamp not null default current_timestamp on update current_timestamp, modify table_name varchar(199);

But the errors you're getting are referencing innodb_index_stats. It looks like both of them needs to be updated (ref).

Theoretically, we could relax the schema check (which was refactored in 10.6) and accept both 32-bit formats.

We would need to debug and understand what happens with each value of @@global.mysql56_temporal_format, and to check which internal representations are compatible.

We might also simply ignore differences in the timestamp column definitions and on mismatch, refrain from updating the columns. When InnoDB internally updates the statistics tables in the background, it will use the InnoDB internal SQL interpreter, which does not implement or support any DEFAULT or ON UPDATE clauses.

Hello marko,
Can you please review a patch:

https://github.com/MariaDB/server/commit/fdd338fc735697d2def4c19dd92dedfd3a7bef85

Thanks!

OK to push after fixing the link issue related to the experimental data type plugin that you added for mimicing the MySQL 5.7 signed timestamp type.

The InnoDB fix simply makes the InnoDB schema check ignore the DATA_UNSIGNED flag differences for the timestamp column. When the DATA_UNSIGNED flag is missing, InnoDB will invert the sign bit both when reading and writing the data, so that comparisons of signed integers stored in big-endian format can use straight memcmp().

According to bar, the timestamps are 31-bit unsigned time_t (never before the Unix epoch of January 1970). Therefore, the sign bit in InnoDB should always be the same for all rows of a given table. Even if timestamps before 1970 were allowed in the statistics tables, this should not matter, because those columns are not indexed and therefore no other than equality comparisons inside InnoDB should take place.

According to Marko, InnoDB stores last_update column values (of the two mentioned statistical tables) directly to InnoDB record buffer without using Field methods. Values are stored directly to the InnoDB record using big-endian format.

These scripts prove that this should work with both:

• Field_timestamp0 - UINT4 based - mysql56_temporal_format=0
• Field_timestampf - BINARY(4) based - mysql56_temporal_format=1

SQL layer, which used Field methods, also always produces big-engian values, no matter what mysql56_temporal_format is:

mysql test <<END

SET @@global.mysql56_temporal_format=0;

CREATE OR REPLACE TABLE t1 (a CHAR(4), b TIMESTAMP, c CHAR(4)) ENGINE=InnoDB;

INSERT INTO t1 VALUES ('xxxx', from_unixtime(0x61626364), 'xxxx');

FLUSH TABLES t1 for EXPORT;

UNLOCK TABLES;

END

strings /opt/mariadb-10.6/data/test/t1.ibd

infimum

supremum

*xxxxabcdxxxx

mysql test <<END

SET @@global.mysql56_temporal_format=1;

CREATE OR REPLACE TABLE t1 (a CHAR(4), b TIMESTAMP, c CHAR(4)) ENGINE=InnoDB;

INSERT INTO t1 VALUES ('yyyy', from_unixtime(0x61626364), 'yyyy');

FLUSH TABLES t1 for EXPORT;

UNLOCK TABLES;

END

strings /opt/mariadb-10.6/data/test/t1.ibd

infimum

supremum

*yyyyabcdyyyy

As discussed, I think that it is a good idea to extend the regression test suite with some validation of the contents of last_update.