Bug #85704 mysql 8.0.x crashes when old-style trigger misses the "created" line in .TRG

Submitted: 30 Mar 2017 8:54 Modified: 17 Apr 2017 14:27
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:8.0.1 OS:Any
Assigned to: CPU Architecture:Any

[30 Mar 2017 8:54] Shane Bester
Description:
When in-place upgrading an existing datadir to 8.0.x server crashes on startup:

[Note] InnoDB: 8.0.2 started; log sequence number 0
[Note] InnoDB: Loading buffer pool(s) from E:\mysql-5.7.17-winx64\data\ib_buffer_pool
[Note] InnoDB: Buffer pool(s) load completed at 170330 10:44:08
[Note] Created Data Dictionary for upgrade
 mysqld got exception 0xc0000005 ;
This could be because you hit a bug. It is also possible that this binary
...
mysqld-debug.exe!dd::Trigger_loader::load_triggers()[dd_upgrade.cc:717]
mysqld-debug.exe!dd::add_triggers_to_table()[dd_upgrade.cc:1637]
mysqld-debug.exe!dd::migrate_table_to_dd()[dd_upgrade.cc:2302]
mysqld-debug.exe!dd::find_files_with_metadata()[dd_upgrade.cc:3519]
mysqld-debug.exe!dd::bootstrap::upgrade_fill_dd_and_finalize()[bootstrapper.cc:1515]
mysqld-debug.exe!handle_bootstrap()[bootstrap.cc:333]
mysqld-debug.exe!pfs_spawn_thread()[pfs.cc:2409]
mysqld-debug.exe!win_thread_start()[my_thread.cc:42]

Crash is at:

// Set timeval to use for Created field.
timeval timestamp_value;
timestamp_value.tv_sec= static_cast<long>(*created_timestamp / 100); <<---
timestamp_value.tv_usec= (*created_timestamp % 100) * 10000;

How to repeat:
on a fresh 5.7.17, create a trigger.

drop table if exists t;
create table t(a int)engine=innodb;
drop trigger if exists trg;
create trigger trg after insert on t for each row begin end;
show create trigger trg;
shutdown;

Now edit t.TRG and remove the "created=xxxxxxxxx" line.

Startup 8.0 binary and point to 5.7 datadir.

Suggested fix:
The "created" line is new as of 5.7.2, so most likely when this crash is seen,  this was a datadir from 5.6.  but I think 8.0 can be more robust/forgiving here.
[30 Mar 2017 22:20] Jesper wisborg Krogh
Posted by developer:
 
The workaround is:

1. Add as the last line (copied from sys/sys_config.TRG) to the .TRG file of
the trigger with the missing created time (no spaces in front):
 
created=149090559224 149090559224
 
2. Restart 8.0 - this fails with (expected):
 
2017-03-30T20:35:01.165251Z 1 [ERROR] Found partially upgraded DD. Aborting
upgrade and deleting all DD tables. Start the upgrade process again.
...
2017-03-30T20:35:01.431151Z 0 [ERROR] Data Dictionary initialization failed.
2017-03-30T20:35:01.431168Z 0 [ERROR] Aborting
 
3. Restart 8.0 again (to try upgrade again) - this time success.
 
4. Execute mysql_upgrade
 
5. Now the trigger data is working.
[17 Apr 2017 14:27] Paul DuBois
Posted by developer:
 
Noted in 8.0.2 changelog.

An in-place MySQL upgrade failed if a .TRG file was missing the
created line for the trigger definition. (This problem is unlikely to
occur for upgrades to MySQL 8.0 from 5.7 because the created line is
present for MySQL 5.7.2 and higher.)
[6 Oct 2018 8:21] Truong Duong
Add create time doesn't help. I have to delete all .TRG file to a backup location then start mysql.
I have to re-execute the create trigger statement after executed mysql_upgrade.
[6 Oct 2018 8:26] Shane Bester
That's surely a different bug to this one then.
This bug is fixed in 8.0 GA versions.
[6 Oct 2018 8:38] Truong Duong
Thanks Shane Bester, I will post other item for this.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.055 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.