MySQL错误1109至1242排查：用户中断时长计算存储过程问题

Hey there, let's break down those MySQL errors (1109-1242) you're hitting with your contingency tracking stored procedure. I've dealt with similar issues before, so here's what to check step by step:

Error 1109: Unknown table 'xxx' in field list #

This usually means one of two things:

• You've misspelled a table name in your stored procedure (double-check for typos, and remember MySQL is case-sensitive on some systems like Linux)
• You're referencing a cross-database table without adding the database prefix (e.g., use your_db.treatment_completion_table instead of just treatment_completion_table)

Error 1242: Subquery returns more than 1 row #

This is super common when assigning values for id_user, date_start, or other fields. If your subquery pulls multiple records (like all of a user's treatment entries instead of the most recent one), MySQL can't pick a single value to insert.

• Fix: Add LIMIT 1 to subqueries that should return only one row, or use aggregate functions like MAX()/MIN() to narrow down results. If you need to log multiple contingencies at once, switch to an INSERT ... SELECT pattern instead of single-row assignment.

Other Key Errors in the 1109-1242 Range #

• Error 1110: Column 'xxx' specified twice: You've listed the same column more than once in your INSERT statement's field list. Double-check your column names against the values you're passing.
• Error 1241: Operand should contain 1 column(s): Your subquery is returning multiple columns, but you're trying to assign it to a single variable/field. Trim the subquery to only the column you need.
• Error 1142: INSERT command denied to user...: The user running your stored procedure doesn't have INSERT permissions on the contingency table. Verify the user's database privileges.

Since your contingency procedure runs after the treatment completion check:

• Ensure the preceding stored procedure commits its transactions properly—uncommitted transactions can lock tables or cause inconsistent data reads.
• Verify that the data types of values you're inserting match the contingency table's schema (e.g., don't pass a string to a date type column like date_start).

A quick debugging tip: Extract the INSERT statement from your stored procedure, plug in test values, and run it directly in MySQL. This will help you isolate whether the issue is in the procedure logic or the query itself.

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