Description:
Using mysql-trunk-wl6392 (dated Jun.13)
Functions are not restored in 5.8.0 even if they are included in the dump file in the mysql.proc table.
It seems that in 5.7.14, mysql.proc table was used to store routine definitions while in 5.8.0, routines are stored in Data dictionary tables (as per WL7897)
How to repeat:
Steps:
1) Start a 5.7.13 MySQL Server
2) Execute these commands:
create database temp;
use temp;
DROP FUNCTION IF EXISTS version_after;
DELIMITER /
CREATE FUNCTION version_after(ver CHAR(3), minor INT) RETURNS BOOLEAN
BEGIN
DECLARE i BOOLEAN DEFAULT FALSE;
SET @j = (SELECT VERSION());
SET @currver = (SELECT SUBSTRING(@j,1,3));
SET @minordigit = (SELECT SUBSTRING_INDEX(@j,'.',-1));
IF ((@currver > ver) || (@currver = ver && @minordigit >= minor)) THEN SET i = TRUE;
END IF;
RETURN i;
END
/
DELIMITER ;
SELECT temp.version_after('5.5',3);
3) Take a dump of Data
./bin/mysqldump -uroot -pnewpwd --port=$PORT --socket=$SOCKET --add-drop-table --max_allowed_packet=257M --events --all-databases --ignore-table=mysql.innodb_table_stats --ignore-table=mysql.innodb_index_stats --force > $LOGDIR/Dumpdata.dmp 2> $LOGDIR/Dumpdata.err
4) Shutdown 5.7.13 server
./bin/mysqladmin --port=$PORT --socket=$SOCKET -uroot -pnewpwd shutdown
5) Move 5.7.13 Data folder to a different location.
echo "Moving $DATADIR to $OLDDATA ..."
rm -rf $DATADIR
echo "Removing $DATADIR just in case ..."
mkdir -p $DATADIR
echo "Creating $DATADIR again to be used in 5.8.0 ..."
6) Start 5.8.0 (wl6392 pb2 release) with the same DataDir (empty) as 5.7.13 server.
./bin/mysqld --no-defaults --initialize-insecure --datadir=$DATADIR --basedir=. > $LOGDIR/install5800_init.out 2> $LOGDIR/install5800_init.err
./bin/mysqld_safe --no-defaults --datadir=$DATADIR --basedir=. --port=$PORT --socket=$SOCKET &
7) Restore the dump
./bin/mysql --no-defaults -uroot -pnewpwd --port=$PORT --socket=$SOCKET --execute="source $LOGDIR/Dumpdata.dmp" --max_allowed_packet=257M --force -vvv > $LOGDIR/Dumpdata.loadout 2> $LOGDIR/Dumpdata.loaderr
8) Notice that the function was NOT restored correctly (it fails).
cat <<!ESQL > $SQLIN
use temp;
SELECT temp.version_after('5.5',3);
!ESQL
./bin/mysql --table --force --socket=$SOCKET -vvv -uroot -pnewpwd < "$SQLIN"
---------------------------------------
SELECT temp.version_after('5.5',3)
--------------
ERROR 1305 (42000) at line 3: FUNCTION temp.version_after does not exist
**********************************
Notice that if you change the release 5.8.0 to be 5.7.14, the function is restored successfully!
**********************************
Notice that if you add the --routines flag to the mysqldump command, the function is restored successfully also on 5.8.0:
./bin/mysqldump -uroot -pnewpwd --port=$PORT --socket=$SOCKET --add-drop-table --max_allowed_packet=257M --events --all-databases --ignore-table=mysql.innodb_table_stats --ignore-table=mysql.innodb_index_stats --force --routines > $LOGDIR/Dumpdata.dmp 2> $LOGDIR/Dumpdata.err
SELECT temp.version_after('5.5',3)
--------------
+-----------------------------+
| temp.version_after('5.5',3) |
+-----------------------------+
| 1 |
+-----------------------------+
