MySQL Insert Fails With Errors 1264 & 1292 When Creating Brokers Table (Phone Field & Timestamp Issues)

Question

I am working on a MySQL database table (brokers) and encountering two confusing SQL errors during the INSERT process. I want to understand what is causing these errors and how to resolve them correctly.

The Table Definition

CREATE TABLE brokers (

id int(11) NOT NULL,

name varchar(100) NOT NULL,

email varchar(100) NOT NULL,

phone varchar(20) DEFAULT NULL,

password varchar(255) NOT NULL,

role enum('admin','broker') NOT NULL DEFAULT 'broker',

created_at timestamp NOT NULL DEFAULT current_timestamp(),

updated_at datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

The Insert Statement Triggering Errors

INSERT INTO brokers

(id, name, email, phone, password, role, created_at, updated_at) VALUES

(1, 'admin', 'admin@example.com', '1231231234', '$2y$10$D3YhzuXVgy2dUevNEfOuhO/Ir7h59UH13i3EpvxX3HDbKTv641bpe', 'admin', '2025-07-12 21:49:11', NULL),

(2, 'Test Broker', 'test@example.com', 18884001969, 'dummy', 'broker', '2025-07-28 08:51:07', NULL),

-- ERROR #1: phone inserted as integer instead of string

(12, 'Martin Russell', '******@kcre.com', '18884004136',

'$2y$10$HV0OT73La4UmYAaRlcQg/esEpa8dY/H4C9vKrYNDM3TCLobZEe4Y6',

'admin', '2025-07-16 22:26:31', NULL),

(13, 'Sam Broker', '******@kcre.com', '18884001969',

'$2y$10$O.exedP7ZS5Ei7r/9IvDVuv8L76sku3enVDoPFlL2khh0VXW8MPz.',

'broker', '2025-07-20 20:40:09', NULL),

(14, 'Tony Broker', '******@kcre.com', 18884004136,

'$2y$10$RTgvYxAERPcI8a7ouTYrG.Wxr1Mryz0e/bQXWwJU4K9jl88TyatgG',

'broker', '2025-07-20 20:40:61', NULL),

-- ERROR #2: phone not quoted

-- ERROR #3: invalid timestamp (seconds value = 61)

(17, 'Marv Wick', '******@kcre.com', '18884001969',

'$2y$10$JSMdZKg7ha0QKuwcjAfxNefpv953yGPMkErxTUElU4UOpfyADXX.C',

'broker', '2025-07-28 18:44:56', NULL);

Errors Generated

1. Error #1 – Phone Field as Integer

ERROR 1264 (22003): Out of range value for column 'phone' at row 2

Why it happens:

The phone field is defined as a VARCHAR(20) but the value is inserted without quotes:

phone = 18884001969

phone = 18884004136

MySQL interprets it as an integer.

In strict SQL modes, large numbers may overflow and trigger error 1264.

2. Error #2 – Second Phone Field Not Quoted

ERROR 1264 (22003): Out of range value for column 'phone' at row 5

Same issue as above. Phone numbers must always be treated as strings, not integers.

3. Error #3 – Invalid Timestamp

ERROR 1292 (22007): Incorrect datetime value: '2025-07-20 20:40:61' at row 5

Cause:

A timestamp is inserted with seconds = 61, which is invalid:

20:40:61

Allowed seconds range is 00–59, so MySQL rejects the value.

What I Need Help With

I would appreciate guidance on the following:

1. Best way to validate data before inserting into MySQL

Should I enforce stricter validation at the application layer or inside MySQL with CHECK constraints?

2. How to prevent numeric phone values from causing errors

Is it recommended to always wrap phone numbers in quotes? Should the schema be changed?

3. Recommended practices for handling timestamps in bulk insert scripts

Is there a tool or MySQL mode that can auto-correct invalid timestamps or at least highlight them?

4. Any MySQL settings that make these errors more descriptive?

Particularly for strict mode or sql_mode configuration.

Closing

I am posting this here hoping the community can help me understand the correct approach to fixing these SQL errors and preventing similar issues in large migration or seeding scripts.

Thank you!

Answer 1

Hi Zina Rendon,
It sounds like you're running into some frustrating SQL errors while trying to insert data into your MySQL brokers table. Here’s a breakdown of the issues you're facing and some ways to resolve them:

Understanding the Errors

1. Error #1 & #2: Phone Field as Integer
   • Cause: The phone numbers you’re trying to insert (18884001969 and 18884004136) aren't wrapped in quotes, which makes MySQL interpret them as integers. Since the phone column is defined as VARCHAR(20), it leads to a value that's out of range for this type.
   • Solution: Always wrap phone numbers in quotes in your SQL statements to treat them as strings. For example, change 18884001969 to '18884001969'.
2. Error #3: Invalid Timestamp**
   • Cause: You’re attempting to insert a timestamp with an invalid seconds value (20:40:61), as seconds can only range from 00 to 59.
   • Solution: Ensure that any timestamp value follows the correct format. Update 20:40:61 to 20:40:00 or adjust it according to your intended time.

Recommendations for Data Validation

To avoid similar errors in the future:

• At the Application Layer: It’s generally best to perform data validation at your application layer. This ensures that only correctly formatted values are sent to the database.
• Within MySQL: If you'd like, you can also utilize CHECK constraints to enforce certain data rules directly in your MySQL schema. However, this might not cover all types of validations like formatting which is better handled in the application.
• Schema Changes: You might consider reviewing the schema design to enforce stricter types if you frequently face these errors. Also, always treat phone numbers as strings and keep them appropriately formatted before database insertion.

Tools and Settings

• Invalid Timestamp Handling: Unfortunately, MySQL doesn’t have a built-in tool for auto-correcting timestamps but using strict SQL mode will help you identify issues before they get inserted.
• Descriptive Error Settings: You can set the sql_mode in your MySQL configuration to include settings like STRICT_ALL_TABLES to help catch more errors before they arise.

Follow-Up Questions

1. What SQL mode settings are currently configured in your MySQL instance?
2. Are you using any specific programming language or framework to interact with MySQL?
3. Would you be interested in a specific tool or additional resources to help automate data validation before insertion?

Hope this helps! If you have more questions or need further clarification, feel free to ask!

References

• Azure Database for MySQL Documentation
• Error Creating and Inserting Tables During MySQL Migration
• Troubleshoot common errors