Ticket #1051 (closed defect: duplicate)

Opened 2 years ago

Last modified 2 years ago

delete_unique method not working with MySQL 5.5.19

Reported by: rduenasf@… Owned by: andrew
Priority: blocker Milestone:
Component: databaseapi Version: 0.7.3
Keywords: delete_unique, unique, mysql Cc:

Description

Currently, the delete_unique method makes a call to _constraints_affecting_columns, which executes the following SQL Query

SELECT kc.constraint_name, kc.column_name
            FROM information_schema.key_column_usage AS kc
            JOIN information_schema.table_constraints AS c ON
                kc.table_schema = c.table_schema AND
                kc.table_name = c.table_name AND
                kc.constraint_name = c.constraint_name
            WHERE
                kc.table_schema = %s AND
                kc.table_catalog IS NULL AND
                kc.table_name = %s AND
                c.constraint_type = %s

By definition of the information_schema.key_column_usage table, table_catalog can't be null:

| KEY_COLUMN_USAGE | CREATE TEMPORARY TABLE `KEY_COLUMN_USAGE` (
  `CONSTRAINT_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `CONSTRAINT_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `CONSTRAINT_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
  `ORDINAL_POSITION` bigint(10) NOT NULL DEFAULT '0',
  `POSITION_IN_UNIQUE_CONSTRAINT` bigint(10) DEFAULT NULL,
  `REFERENCED_TABLE_SCHEMA` varchar(64) DEFAULT NULL,
  `REFERENCED_TABLE_NAME` varchar(64) DEFAULT NULL,
  `REFERENCED_COLUMN_NAME` varchar(64) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8 |

Therefore, the delete_unique command will never find the corresponding constraint that it should delete.

The fix should be just to remove the table_catalog IS NULL condition from the query

Change History

comment:1 Changed 2 years ago by andrew

  • Status changed from new to closed
  • Resolution set to duplicate

Duplicate of #747.

Note: See TracTickets for help on using tickets.