MySQL Bugs: #59146: nist.nist_all fails: 1.uses deprecated syntax; 2.test result needs to be updated

Bug #59146	nist.nist_all fails: 1.uses deprecated syntax; 2.test result needs to be updated
Submitted:	24 Dec 2010 7:14	Modified:	25 Oct 2018 5:46
Reporter:	Alexander Nozdrin	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Tests: Server	Severity:	S3 (Non-critical)
Version:	5.6.1,mysql-trunk	OS:	Any
Assigned to:	Bernt Marius Johnsen	CPU Architecture:	Any
Tags:	pb2, test failure

Description:
Tree: mysql-5.6.1-m5-release

nist.nist_all.test fails with the following symptoms:

@@ -1,5 +1,7 @@
 SET @global_storage_engine = @@global.storage_engine;
 SET GLOBAL STORAGE_ENGINE=INNODB;
+Warnings:
+Warning	1287	'@@storage_engine' is deprecated and will be removed in a future release. Please use '@@default_storage_engine' instead
 SET AUTOCOMMIT = 0;
 SET @global_sql_mode=@@global.sql_mode;
 SET GLOBAL sql_mode = 'PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,PAD_CHAR_TO_FULL_LENGTH' ;
@@ -981,7 +983,7 @@
 TEST-INFO: passed
 
 GRANT SELECT ON CUGINI.BADG2 TO SCHANZLE@localhost;
-ERROR 42000: SELECT,GRANT command denied to user 'FLATER'@'localhost' for table 'BADG2'
+ERROR 42000: SELECT, GRANT command denied to user 'FLATER'@'localhost' for table 'BADG2'
 
 TEST-INFO: passed
 
@@ -11943,7 +11945,7 @@
 E3 	13	Vienna         
 COMMIT WORK;
 DROP VIEW STAFF CASCADE;
-ERROR 42S02: Unknown table 'STAFF'
+ERROR 42S02: Unknown table 'FLATER.STAFF'
 
 TEST-INFO: The last statement failed, because MySQL does not
  - understand the syntax or
@@ -16161,6 +16163,8 @@
 -------------- suite/nist/include/nist-drop.inc -------------------------
 
 SET GLOBAL STORAGE_ENGINE=@global_storage_engine;
+Warnings:
+Warning	1287	'@@storage_engine' is deprecated and will be removed in a future release. Please use '@@default_storage_engine' instead
 SET GLOBAL SQL_MODE = @global_sql_mode;
 SET SESSION SQL_MODE = @global_sql_mode;
 SET GLOBAL CHARACTER_SET_SERVER= @global_character_set_server;
@@ -16210,6 +16214,8 @@
 -------------- suite/nist/include/nist-drop.inc -------------------------
 
 SET GLOBAL STORAGE_ENGINE=@global_storage_engine;
+Warnings:
+Warning	1287	'@@storage_engine' is deprecated and will be removed in a future release. Please use '@@default_storage_engine' instead
 SET GLOBAL SQL_MODE = @global_sql_mode;
 SET SESSION SQL_MODE = @global_sql_mode;
 SET GLOBAL CHARACTER_SET_SERVER= @global_character_set_server;

mysqltest: Result content mismatch

How to repeat:
Check out: http://pb2.norway.sun.com/?template=show_pushes&branch=mysql-5.6.1-m5-release
Log: http://pb2.norway.sun.com/?action=archive_download&archive_id=2711200&pretty=please

The "bug" is partly a consequence of WL#5185, and reflects a wanted change.

The warnings about "'@@storage_engine' is deprecated and will be removed in a future release." would be best addressed by replacing every occurrence of 'storage_engine' with 'default_storage_engine' in the test case.  The variable 'storage_engine' will be removed in a soon-to-come release.

nist suite should be included in the daily/weekly runs for earlier detection of issues

Also see WL#5265

* WL#4738:

Deprecate the storage_engine system variable.

Reason: WL#4738 added default_storage_engine, which has the same
meaning, but the name matches the --default-storage-engine command-line
option more closely. storage_engine does not have the same obvious
correspondence to --default-storage-engine.

After changing storage_engine to default storage engine the test still fails with the following error 

+++ /home/anitha/mysql/build/mysql-trunk-bugfixing/usr/local/mysql/mysql-test/suite/nist/r/nist_all.reject	2010-12-29 07:05:36.310741622 +0300
@@ -981,7 +981,7 @@
 TEST-INFO: passed
 
 GRANT SELECT ON CUGINI.BADG2 TO SCHANZLE@localhost;
-ERROR 42000: SELECT,GRANT command denied to user 'FLATER'@'localhost' for table 'BADG2'
+ERROR 42000: SELECT, GRANT command denied to user 'FLATER'@'localhost' for table 'BADG2'
 
 TEST-INFO: passed
 
@@ -11943,7 +11943,7 @@
 E3 	13	Vienna         
 COMMIT WORK;
 DROP VIEW STAFF CASCADE;
-ERROR 42S02: Unknown table 'STAFF'
+ERROR 42S02: Unknown table 'FLATER.STAFF'
 
 TEST-INFO: The last statement failed, because MySQL does not
  - understand the syntax or

mysqltest: Result content mismatch

http://dev.mysql.com/doc/refman/5.5/en/news-5-5-3.html

The storage_engine system variable is deprecated in favor of the new system variable default_storage_engine. This enables pairing of the --default-storage-engine command-line option with a system variable of a more closely corresponding name.

The difference in error message is not seen in 5.5. The changes needed to fix the test will make it incompatible with 5.1 and 5.5. This test is maintained in mysql-test-extra and that makes it hard to handle this. We either have to create separate folders for the different versions in mysql-test-extra or move nist suite under mysql code tree. Do we have any particular reason to keep it out of mysql

Patch for trunk

Attachment: BUG59146.txt (text/plain), 5.04 KiB.

Complete patch for trunk

Attachment: BUG59146.txt (text/plain), 6.28 KiB.

Hi Anitha,

your patch fixes the reported problem.

But in order to ensure the consistency of the test suite
some other files have to be patched in addition.
mysql-test/suite/nist/r/nist_data.result
   Some modified message from test schema5 was missing.
mysql-test/suite/nist/r/schema/schema5.result
mysql-test/suite/nist/r/sql/dml114.result
   Again some modified messages were missing.
From the logics;
   nist_data.result and nist_all.result are basically
   concatenations of the results of the performed
   sub tests (e.g. schema5, dml114, ....).
   Maintaining result snipplets like dml114.result
   allows to easy remove sub tests from nist_all
   temporarily without loosing the already checked
   expected results. 
   
I have uploaded a BUG59146.txt which contains the
additional patches.
ready to push