Understanding the Causes of ERROR 1064 (42000) in MySQL: Delimiter Issues and How to Resolve Them

Understanding the MySQL Syntax Error: A Deep Dive into ERROR 1064 (42000)

Introduction

When working with MySQL, it’s not uncommon to encounter syntax errors that can be frustrating and time-consuming to resolve. One such error is ERROR 1064 (42000), which indicates an error in the SQL syntax. In this article, we’ll delve into the world of MySQL syntax and explore the causes of this particular error.

What are Delimiters in MySQL?

In MySQL, a delimiter is a character that separates commands or statements from one another. By default, the semicolon (;) serves as the delimiter, which means that any occurrence of ; is treated as the end of a statement. This can sometimes lead to unexpected results when working with MySQL.

For example, consider the following query:

SELECT * FROM table;
DELIMITER ;
CREATE TABLE new_table (id INT PRIMARY KEY);

In this scenario, the semicolon (;) after SELECT is treated as the end of the first statement, and the CREATE TABLE statement that follows is not executed. To avoid such issues, it’s essential to use a different delimiter.

MySQL 5.1.25 vs MySQL 5.7.19: What’s the Difference?

MySQL 5.1.25 and MySQL 5.7.19 are two distinct versions of MySQL with different syntax features. The query in question was executed successfully on MySQL 5.1.25, but not on MySQL 5.7.19.

One key difference between these versions is the way they handle SQL statements with semicolons (;). In MySQL 5.1.25, the default delimiter is indeed the semicolon (;), which allows the query to be executed without issues. However, in MySQL 5.7.19, the default delimiter has been changed to the comment character (#) by default.

This change can lead to unexpected behavior when working with MySQL queries that rely on semicolons to separate statements.

The Mysterious Dollar Sign ($)

The error message mentions a dollar sign ($) near line 6. This is where things get interesting.

In MySQL, you can use the DELIMITER statement to change the default delimiter. When you create a view or stored procedure using this statement, you can specify an alternative delimiter that allows you to separate statements more effectively.

However, in the query provided, there’s a dollar sign ($) appended to the end of the SQL statement:

CREATE ALGORITHM=UNDEFINED DEFINER=`acc_webdev`@`%` SQL SECURITY DEFINER VIEW `view_dash_total` AS 
SELECT
  COUNT(0) AS `jumlah`,
  SYSDATE() AS `tanggal`
FROM `table_laporan`
WHERE (STR_TO_DATE(`table_laporan`.`dt_added`,'%d-%m-%Y') &lt; (SYSDATE() + INTERVAL - (1)DAY))$$

The dollar sign ($) is not a valid character in MySQL syntax. It’s possible that the developer intended to use this as an alternative delimiter, but forgot to specify it correctly.

The Solution: Prepend DELIMITER or Change the Delimiter

To resolve the error, you have two options:

Prepend DELIMITER: Before creating the view, prepend the DELIMITER statement to change the default delimiter:

DELIMITER //;

CREATE ALGORITHM=UNDEFINED DEFINER=acc_webdev@% SQL SECURITY DEFINER VIEW view_dash_total AS SELECT COUNT(0) AS jumlah, SYSDATE() AS tanggal FROM table_laporan WHERE (STR_TO_DATE(table_laporan.dt_added,’%d-%m-%Y’) < (SYSDATE() + INTERVAL - (1)DAY))$$;


2.  **Change the Delimiter**: Alternatively, you can change the delimiter to a character that's not present in your SQL statements:
    ```markdown
SET @delim = '#';

CREATE ALGORITHM=UNDEFINED DEFINER=`acc_webdev`@`%` SQL SECURITY DEFINER VIEW `view_dash_total` AS 
SELECT
  COUNT(0) AS `jumlah`,
  SYSDATE() AS `tanggal`
FROM `table_laporan`
WHERE (STR_TO_DATE(`table_laporan`.`dt_added`,'%d-%m-%Y') &lt; (@delim + INTERVAL - (1)DAY))$$;

By changing the delimiter, you can avoid issues with semicolons (;) separating statements.

Conclusion

In this article, we’ve explored the causes of ERROR 1064 (42000) in MySQL and discovered a common issue related to delimiters. By understanding how delimiters work and knowing the alternatives, you can prevent this error from occurring in your MySQL queries. Remember to use DELIMITER //; or another suitable delimiter when creating views or stored procedures that rely on semicolons (;) to separate statements.

Additional Tips

Here are some additional tips for working with MySQL syntax:

Use meaningful variable names: Avoid using single-letter variable names, as they can lead to confusion and errors.
Specify the database schema: When querying a specific database schema, make sure to include the schema name in your query.
Test your queries thoroughly: Before executing a query, test it in a development environment or use an online tool to ensure it works correctly.

By following these tips and staying up-to-date with MySQL syntax features, you can write efficient and effective SQL queries that help you achieve your goals.

Last modified on 2025-03-18