Bug #99746 wrong table definition in connector Python docs
Submitted: 29 May 2020 17:04 Modified: 30 May 2020 4:04
Reporter: Giuseppe Maxia (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / Python Documentation Severity:S2 (Serious)
Version: OS:Any
Assigned to: CPU Architecture:Any

[29 May 2020 17:04] Giuseppe Maxia
Description:
In the connector python tutorial (https://dev.mysql.com/doc/connector-python/en/connector-python-example-ddl.html), one of the tables has the wrong structure.

Currently, it is:

TABLES['dept_manager'] = (
    "  CREATE TABLE `dept_manager` ("
    "  `dept_no` char(4) NOT NULL,"
    "  `emp_no` int(11) NOT NULL,"
    "  `from_date` date NOT NULL,"
    "  `to_date` date NOT NULL,"
    "  PRIMARY KEY (`emp_no`,`dept_no`),"
    "  KEY `emp_no` (`emp_no`),"
    "  KEY `dept_no` (`dept_no`),"
    "  CONSTRAINT `dept_manager_ibfk_1` FOREIGN KEY (`emp_no`) "
    "     REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,"
    "  CONSTRAINT `dept_manager_ibfk_2` FOREIGN KEY (`dept_no`) "
    "     REFERENCES `departments` (`dept_no`) ON DELETE CASCADE"
    ") ENGINE=InnoDB")

The correct structure should be:

TABLES['dept_manager'] = (
    "  CREATE TABLE `dept_manager` ("
    "  `emp_no` int(11) NOT NULL,"
    "  `dept_no` char(4) NOT NULL,"
    "  `from_date` date NOT NULL,"
    "  `to_date` date NOT NULL,"
    "  PRIMARY KEY (`emp_no`,`dept_no`),"
    "  KEY `emp_no` (`emp_no`),"
    "  KEY `dept_no` (`dept_no`),"
    "  CONSTRAINT `dept_manager_ibfk_1` FOREIGN KEY (`emp_no`) "
    "     REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,"
    "  CONSTRAINT `dept_manager_ibfk_2` FOREIGN KEY (`dept_no`) "
    "     REFERENCES `departments` (`dept_no`) ON DELETE CASCADE"
    ") ENGINE=InnoDB")

The problem is that the tutorial suggests loading the table dept_manager from the test_db project, where the data structure is different.
The result is that someone following these instructions will get a failure, and blame it on test_db rather than the incorrect tutorial
(https://github.com/datacharmer/test_db/issues/22)

How to repeat:
Follow the instructions in the page https://dev.mysql.com/doc/connector-python/en/connector-python-example-ddl.html

Suggested fix:
Swap the order of the first two fields in the dept_manager table
[29 May 2020 20:29] Philip Olson
Thank you for the detailed bug report, this is fixed.
[30 May 2020 4:04] Giuseppe Maxia
Thanks for a quick action!