Search code examples
mysqlforeign-keysconstraints

mysql server does not return full error message

I'm trying to write a recursive function to delete records using mysql-python-client: each time the function call depends on the last returning IntegrityError message which can be used to create a new sql to be executed. At the beginning, the code ran smoothly, but it stops with an AssertionError which cause by the err is not a complete info like:

Cannot delete or update a parent row: a foreign key constraint fails (`db_name`.`a_table_name`, CONSTRAINT `FK_xxx` FOREIGN KEY (`foreign_key_name`) REFERENCES `incomplete_reference_table_name).

So, WHY is the error message incomplete and HOW to make it return a full complete message?

Additional Info: I tried with golang>"github.com/go-sql-driver/mysql" and even navicat IDE the result is all the same. It seems it's a server side BUG?

Update: test result with CLI: enter image description here test reulst with CLI in text:

mysql> delete from `bnb_system`.`traveler_checkin_handle_record` where `room_id` in (select `id` from `room` where `fk_roomtype_rooms` in (select `id` from `room_type` where `fk_inn_roomtype` in (select `id` from `inn` where landlord_id in (select id from landlord where user_id in (select id from user where tel='15779313733')))));
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`bnb_system`.`concerned_checkin_traveler_check_in_handle_record`, CONSTRAINT `FKc2166ey4rteqht79ahel72scu` FOREIGN KEY (`traveler_check_in_handle_record_id`) REFERENCES `traveler_checkin_handl)

import mysql.connector
import re

host = "some_host"
user = "some_user"
password = "some_pwd"
database = "some_system"

conn = mysql.connector.connect(host=host,
                                     user=user,
                                     password=password,
                                     database=database)
cur = conn.cursor()
org_sql = "some_sql"


def recursive_delete(sql, cur, conn):
    sql_post = sql[sql.index('where'):]
    try:
        print(sql)
        cur.execute(sql)
        conn.commit()
    except mysql.connector.IntegrityError as e:
        err = e.msg
        print(err)
        ss = r'`[\w_]+`'
        info = re.findall(ss, err)
        assert len(info) == 6, "error message: {}".format(err)
        db, table, foreign_key, reference_table, reference_key = info[0], info[1], info[3], info[4], info[5]
        new_sql = "delete from {db}.{table} where {foreign_key} in (select {reference_key} from {reference_table} {sql_post})".format(db=db, table=table, foreign_key=foreign_key, reference_key=reference_key, reference_table=reference_table, sql_post=sql_post)
        recursive_delete(sql=new_sql, cur=cur, conn=conn)


recursive_delete(sql=org_sql, cur=cur, conn=conn)
Solution

  • Finally, I changed the implementation of how to query the reference tables in information_schema, that solved the problem.

    def recursive_delete(all_sql, cur, conn):
    for sql in all_sql[::-1]:
        sql_post = sql[sql.index('where'):]
        try:
            print(sql)
            cur.execute(sql)
            conn.commit()
        except mysql.connector.IntegrityError as e:
            err = e.msg
            ss = r'`([\w_]+)`'
            info = re.findall(ss, err)
            db, table, constraint_name, foreign_key = info[0], info[1], info[2], info[3]
            sql_reference_table_and_reference_key = 'select REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME from information_schema.KEY_COLUMN_USAGE where TABLE_NAME="{table}" and CONSTRAINT_NAME="{constraint_name}" and COLUMN_NAME="{foreign_key}"'.format(table=table, constraint_name=constraint_name,
                                                                                                                                                                                                                                                              foreign_key=foreign_key)
            print(sql_reference_table_and_reference_key)
            cur.execute(sql_reference_table_and_reference_key)
            result = cur.fetchone()
            reference_table, reference_key = result[0], result[1]
            new_sql = "delete from {db}.{table} where {foreign_key} in (select {reference_key} from {reference_table} {sql_post})".format(db=db, table=table, foreign_key=foreign_key, reference_key=reference_key, reference_table=reference_table, sql_post=sql_post)
            sql_strings.append(new_sql)
            recursive_delete(all_sql=all_sql, cur=cur, conn=conn)