MySQL Bugs: #83326: Table generated on export as placeholder for view has bad syntax

Bug #83326	Table generated on export as placeholder for view has bad syntax
Submitted:	11 Oct 2016 0:54	Modified:	19 Oct 2016 1:16
Reporter:	C D Tavares	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7.15	OS:	MacOS (10.11.6)
Assigned to:		CPU Architecture:	Any

Description:
Export is generating syntax that import will not accept. 

Exported database in 5.7.8-rc preparatory to upgrading to 5.7.15.  Import threw a bad syntax error.  Syntax error was in a "placeholder" table generated by the export utility to temporarily represent a view.

Here is the description of the actual view, as generated by export:

# Replace placeholder table for invoice_intermediate with correct view syntax
# ------------------------------------------------------------

CREATE ALGORITHM=UNDEFINED DEFINER=`me`@`localhost` SQL SECURITY DEFINER VIEW `invoice_intermediate`
AS SELECT
   (case when ((`locations_full`.`tempfee` < 0) or (`locations_full`.`monthlyfee` <= 0)) then substr(`locations_full`.`ipaddress`,4) else `locations_full`.`customername` end) 
  AS `customername`,
   (case when (`locations_full`.`tempfee` is not null) then round(`locations_full`.`tempfee`,2) else round(`locations_full`.`monthlyfee`,2) end)
  AS `fee`,
  (case when (`locations_full`.`tempfee` is not null) then round(((`locations_full`.`tempfee` + 0.005) / 2),2) else round(((`locations_full`.`monthlyfee` + 0.005) / 2),2) end)
  AS `share`,
  (case when ((`locations_full`.`billingcomment` is not null) and (`locations_full`.`billingcomment` <> '')) then `locations_full`.`billingcomment` else '' end)
  AS `note`
FROM `locations_full` where (`locations_full`.`status` = 'subscriber');

Here is the placeholder table generated by export and inserted early in the export file:

CREATE TABLE `invoice_intermediate` (
   `customername` VARCHAR(42) NULL DEFAULT NULL,
   `fee` DOUBLE(19) NULL DEFAULT NULL,
   `share` DOUBLE(19) NULL DEFAULT NULL,
   `note` VARCHAR(200) NULL DEFAULT NULL
) ENGINE=MyISAM;

The import process chokes on the SQL starting at "NULL DEFAULT NULL".

How to repeat:
I have provided the export file, minus actual data, in the private area.  You should be able to duplicate the problem by importing the file.

Using 5.7.15 this imports

CREATE TABLE `invoice_intermediate` (
   `customername` VARCHAR(42) NULL DEFAULT NULL,
   `fee` DOUBLE NULL DEFAULT NULL,
   `share` DOUBLE NULL DEFAULT NULL,
   `note` VARCHAR(200) NULL DEFAULT NULL
) ENGINE=MYISAM;

.. but this does not 

CREATE TABLE `invoice_intermediate` (
   `customername` VARCHAR(42),
   `fee` DOUBLE(19),
   `share` DOUBLE(19),
   `note` VARCHAR(200) 
) ENGINE=MYISAM;

So it rather seems that "DOUBLE(19)" is the culprit here. According to http://dev.mysql.com/doc/refman/5.7/en/floating-point-types.html the legth is optional but if specified it should be a number pair like "DOUBLE(19,0)". This works:

CREATE TABLE `invoice_intermediate` (
   `customername` VARCHAR(42) NULL DEFAULT NULL,
   `fee` DOUBLE(19,0) NULL DEFAULT NULL,
   `share` DOUBLE(19,0) NULL DEFAULT NULL,
   `note` VARCHAR(200) NULL DEFAULT NULL
) ENGINE=MYISAM;

The bug is when exporting not when importing IMO. In other ords the test case tells it is the old version that is buggy/generates invalid SQL. 
There was some "turbulence" and bugs with mysqldump' around 5.7.8 release due to the introduction of 'virtual columns' in MySQL. I wrote this blog http://blog.webyog.com/beware-virtual-columns-may-render-backups-unusable/ at the time (commenting on another issue). As far as I remember virtual columns were introduced in 5.7.7 but only handled by mysldump from 5.7.8. So if I am right, mysqldmp code had an overhaul in 5.7.8 - and an overhaul that may have introduced this bug. But I don't know if this is fixed in mysldump as of 5.7.15. 

-- Peter
-- not a MySQL/Oracle person

.. and accordingly category should be "clients and not "server" IMO.

hmmm .. it seems that virtual columns arrived in MySQL 5.7.8 and only started working with mysqldump as of 5.7.9. I read up my on bug report http://bugs.mysql.com/bug.php?id=79148. So this seems unrelated to changes to mysldump in 5.7.9.

No matter what: the CREATE TABLE statement, as you pasted it, has a syntax error for DOUBLE()s.

I cannot reproduce the bugw ith mysqldump as of 5.7.15.  

It also seems thw recent mysqldump introduces another concept than the placeholder table. It creates a a temporary VIEW instead like this

/*!50001 CREATE VIEW `invoice_intermediate` AS SELECT 
 1 AS `customername`,
 1 AS `fee`,
 1 AS `share`,
 1 AS `note`*/;
SET character_set_client = @saved_cs_client;

My testcase was 

CREATE TABLE `invoice` (
   `customername` VARCHAR(42) NULL DEFAULT NULL,
   `fee` DOUBLE(19,0) NULL DEFAULT NULL,
   `share` DOUBLE(19,0) NULL DEFAULT NULL,
   `note` VARCHAR(200) NULL DEFAULT NULL
) ENGINE=MYISAM;

CREATE VIEW `invoice_intermediate` AS SELECT * FROM `invoice`;

.. and as said it dumped and imported fine.

Which tool you have used to export the database, because the issue looks like Peter commented:

c:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17 Source distribution PULL: 2016-OCT-14

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use test
Database changed
mysql 5.7 > CREATE TABLE `invoice_intermediate` (
    ->    `customername` VARCHAR(42) NULL DEFAULT NULL,
    ->    `fee` DOUBLE(19) NULL DEFAULT NULL,
    ->    `share` DOUBLE(19) NULL DEFAULT NULL,
    ->    `note` VARCHAR(200) NULL DEFAULT NULL
    -> ) ENGINE=MyISAM;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') NULL DEFAULT NULL,
   `share` DOUBLE(19) NULL DEFAULT NULL,
   `note` VARCHAR(' at line 3

mysql 5.7 > CREATE TABLE `invoice_intermediate` (
    ->    `customername` VARCHAR(42) NULL DEFAULT NULL,
    ->    `fee` DOUBLE(19,0) NULL DEFAULT NULL,
    ->    `share` DOUBLE(19,0) NULL DEFAULT NULL,
    ->    `note` VARCHAR(200) NULL DEFAULT NULL
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.06 sec)

Sequel Pro (sequelpro.com), on MacOS El Capitan.

Thank you for the feedback. Please try the MySQL client tool mysqldump:

c:\dbs>5.7\bin\mysqldump -uroot -p --port=3570 u2
Enter password: ******
-- MySQL dump 10.13  Distrib 5.7.17, for Win64 (x86_64)
--
-- Host: localhost    Database: u2
-- ------------------------------------------------------
-- Server version       5.7.17

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `invoice_intermediate`
--

DROP TABLE IF EXISTS `invoice_intermediate`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `invoice_intermediate` (
  `customername` varchar(42) DEFAULT NULL,
  `fee` double(19,0) DEFAULT NULL,
  `share` double(19,0) DEFAULT NULL,
  `note` varchar(200) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `invoice_intermediate`
--

LOCK TABLES `invoice_intermediate` WRITE;
/*!40000 ALTER TABLE `invoice_intermediate` DISABLE KEYS */;
/*!40000 ALTER TABLE `invoice_intermediate` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2016-10-18 23:11:46