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

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

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.

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.

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.)

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.

That's surely a different bug to this one then.
This bug is fixed in 8.0 GA versions.

Thanks Shane Bester, I will post other item for this.