[MDEV-13006] update_time column of on i_s.tables remains NULL for sequence objects Created: 2017-06-06  Updated: 2018-04-07

Status: Open
Project: MariaDB Server
Component/s: Sequences, Storage Engine - InnoDB
Affects Version/s: 10.3
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Andrii Nikitin (Inactive) Assignee: Marko Mäkelä
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-4697 UPDATE_TIME field for XtraDB / InnoDB Closed
relates to MDEV-10139 Support for SEQUENCE objects Closed
relates to MDEV-12930 Testing SEQUENCE object Closed

 Description   

Following code returns NULL for InnoDB

create or replace sequence test.x engine = innodb;
 
select next value for test.x;
 
select update_time from information_schema.tables where (table_name, table_schema) = ('x','test');

The same for aria or myisam returns correct timestamp:

create or replace sequence test.x1 engine = myisam;
 
select next value for test.x1;
 
create or replace sequence test.x2 engine = aria;
 
select next value for test.x2;
 
select update_time from information_schema.tables where (table_name, table_schema) in ( ('x2','test'),('x1','test'));



 Comments   
Comment by Elena Stepanova [ 2017-06-06 ]

For a note, update_time for InnoDB tables in I_S is pretty useless anyway, as it doesn't survive server restart. If it can be fixed as well, it would be great of course.
That's an old limitation (which just recently replaced an even older limitation when update_time wasn't populated for InnoDB tables at all).

Comment by Marko Mäkelä [ 2017-06-06 ]

I wouldn’t fix this before we get a persistent update_time. That field would have to be stored somewhere in the data file (clustered index root page?). It is doable for sequences (which consist of a single page anyway), but could create a performance bottleneck for general tables if we really update it in real-time. On the other hand, that is what we are doing to the persistent AUTO_INCREMENT, and nobody complained yet that the root page is becoming a bottleneck.

Comment by Marko Mäkelä [ 2017-06-07 ]

A simple change that we could do is to report the .ibd file timestamp as the update_time, and NULL if the sequence is stored in a shared tablespace file.

Comment by Marko Mäkelä [ 2017-11-23 ]

Starting with MariaDB 10.2.2, InnoDB maintains a non-persistent update_time for persistent transactional tables (it will be forgotten across server restart). Sequence objects are not transactional; they are only persisted. Thus, sequence objects would requires some other kind of update_time mechanism.

Maybe at the same time with implementing update_time for InnoDB sequences, we should implement persistent update_time for InnoDB tables.

Generated at Thu Feb 08 08:02:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.