如何获取脚本运行时的MySQL错误报告？以及如何在表间数据迁移中捕获插入错误并存储？

Hey there! Let's tackle your two MySQL questions one by one—they're super common in data workflows, so I've got practical solutions for you.

1. How to get MySQL error reports during script execution? #

The approach depends on where you're running your script, so here are the most common scenarios:

• Command-line scripts
  When using the mysql client to run scripts, use these flags to capture errors:

  • Add --show-warnings to display warnings alongside errors in the output.
  • Use --log-error=your_error_log.log to write all errors to a dedicated file (great for debugging later).
  • Increase verbosity with -v or -vv to see exactly which statements triggered errors.
    You can also add SHOW ERRORS; or SHOW WARNINGS; directly in your script right after a potentially problematic statement to fetch the latest error details.

• Integrated with programming languages
  If you're using Python, PHP, or another language to run MySQL queries, catch exceptions thrown by your database driver to extract error details. For example, in Python with mysql-connector:

  import mysql.connector
  from mysql.connector import Error
  
  try:
      conn = mysql.connector.connect(host="localhost", user="your_user", password="your_pass", database="your_db")
      cursor = conn.cursor()
      cursor.execute("INVALID SQL STATEMENT HERE")
  except Error as e:
      print(f"Error Details: Message = {e.msg}, Code = {e.errno}, SQL State = {e.sqlstate}")
  finally:
      if conn.is_connected():
          cursor.close()
          conn.close()
  

  For PHP with PDO, set PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION and catch PDOException to get similar error metadata.

• Server-side error logs
  For server-level errors (like connection issues or permission problems), check MySQL's built-in error log. On Linux, it's usually at /var/log/mysql/error.log; on Windows, look in C:\ProgramData\MySQL\MySQL Server X.X\Data. This log captures all critical errors from the MySQL daemon.

2. Capture insertion errors during T1 → T2 migration and store them #

To skip bad records while logging their details (either to a file or a table), here are three reliable methods:

Method 1: Stored Procedure with Error Handlers #

Create a stored procedure that iterates over T1's data, attempts to insert into T2, and logs failures to an error table. First, create your error log table:

CREATE TABLE migration_errors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    error_timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    error_code INT,
    error_message TEXT,
    original_col1 VARCHAR(255), -- Match columns from T1
    original_col2 VARCHAR(255)
);

Then the stored procedure:

DELIMITER //
CREATE PROCEDURE migrate_T1_to_T2_with_log()
BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE t1_col1, t1_col2 VARCHAR(255); -- Adjust to match T1's schema
    DECLARE err_code INT;
    DECLARE err_msg TEXT;

    -- Cursor to fetch T1 data
    DECLARE data_cursor CURSOR FOR SELECT col1, col2 FROM T1;
    -- Handler for no more rows
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    -- Handler for SQL exceptions (e.g., constraint violations)
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 err_code = MYSQL_ERRNO, err_msg = MESSAGE_TEXT;
        -- Insert error details into log table
        INSERT INTO migration_errors (error_code, error_message, original_col1, original_col2)
        VALUES (err_code, err_msg, t1_col1, t1_col2);
    END;

    OPEN data_cursor;
    data_loop: LOOP
        FETCH data_cursor INTO t1_col1, t1_col2;
        IF done THEN
            LEAVE data_loop;
        END IF;
        -- Attempt insert into T2
        INSERT INTO T2 (col1, col2) VALUES (t1_col1, t1_col2);
    END LOOP;
    CLOSE data_cursor;
END //
DELIMITER ;

-- Run the migration
CALL migrate_T1_to_T2_with_log();

Method 2: Command-Line Script with Logging #

If you're running a bulk insert script via the mysql client, use these flags to keep going on errors and log them:

mysql -u your_user -p your_db --force --log-error=migration_errors.log < your_migration_script.sql

The --force flag tells MySQL to continue executing the script even after errors, and --log-error writes all errors to the specified file. Note that this logs the error messages and problematic statements, but you'll need to parse the log to map errors to specific records.

Method 3: Programming Language Exception Handling #

Use a script in Python/PHP/etc. to fetch T1 data, attempt inserts, and log failures to a file or table. Here's a Python example that writes errors to a CSV:

import mysql.connector
from mysql.connector import Error
import csv
from datetime import datetime

def migrate_and_log_errors():
    try:
        conn = mysql.connector.connect(host="localhost", user="your_user", password="your_pass", database="your_db")
        cursor = conn.cursor()
        cursor.execute("SELECT col1, col2 FROM T1")
        t1_rows = cursor.fetchall()

        # Open CSV for error logging
        with open("migration_errors.csv", "w", newline="") as csv_file:
            writer = csv.writer(csv_file)
            writer.writerow(["Timestamp", "Error Code", "Error Message", "Col1", "Col2"])

            for row in t1_rows:
                col1, col2 = row
                try:
                    cursor.execute("INSERT INTO T2 (col1, col2) VALUES (%s, %s)", (col1, col2))
                    conn.commit()
                except Error as e:
                    conn.rollback()
                    # Write error to CSV
                    writer.writerow([datetime.now(), e.errno, e.msg, col1, col2])
                    print(f"Failed to insert ({col1}, {col2}): {e.msg}")

    except Error as e:
        print(f"Connection failed: {e.msg}")
    finally:
        if conn.is_connected():
            cursor.close()
            conn.close()

if __name__ == "__main__":
    migrate_and_log_errors()

内容的提问来源于stack exchange，提问作者Abhi