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:Mac OS X (10.11.6)
Assigned to: CPU Architecture:Any

[11 Oct 2016 0:54] C D Tavares
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.
[11 Oct 2016 12:11] Peter Laursen
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
[11 Oct 2016 12:18] Peter Laursen
.. and accordingly category should be "clients and not "server" IMO.
[11 Oct 2016 12:31] Peter Laursen
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.
[11 Oct 2016 14:33] Peter Laursen
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.
[18 Oct 2016 23:40] Miguel Solorzano
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)
[19 Oct 2016 0:24] C D Tavares
Sequel Pro (sequelpro.com), on MacOS El Capitan.
[19 Oct 2016 1:16] Miguel Solorzano
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