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.