Mysql workbench ошибка 1064

It looks like you’ve taken the COMMENT strings a little too far. According to the MySQL CREATE TABLE syntax, a COMMENT attribute is only permitted on a column definition. That means it’s invalid on the INDEX or PRIMARY KEY definitions you have listed near the end of your CREATE statements.

The COMMENT '' aren’t necessary and can be omitted entirely, especially since you are leaving them blank. Otherwise, they would be used for a little bit of extra human-readable metadata on your column definitions.

To get this working with what you have, remove the COMMENT attributes from your index and primary key definitions.

CREATE TABLE IF NOT EXISTS `university`.`CITY` (
  `ID_CITY` INT NOT NULL COMMENT '',
  `CNAME` TEXT(15) NULL COMMENT '',
  `POPULATION` INT NULL COMMENT '',
  -- No COMMENT on PK
  PRIMARY KEY (`ID_CITY`)
) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `sakila`.`actor` (
  `actor_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '',
  `first_name` VARCHAR(45) NOT NULL COMMENT '',
  `last_name` VARCHAR(45) NOT NULL COMMENT '',
  `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
  -- No COMMENT on PK or INDEX
  PRIMARY KEY (`actor_id`),
  INDEX `idx_actor_last_name` (`last_name` ASC)
) ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;

Or the whole thing without any blank COMMENTs:

CREATE TABLE IF NOT EXISTS `university`.`CITY` (
  `ID_CITY` INT NOT NULL COMMENT 'A comment that is not blank!',
  `CNAME` TEXT(15) NULL,
  `POPULATION` INT NULL,
  PRIMARY KEY (`ID_CITY`)
) ENGINE = InnoDB;

(Same for the other table)

MySQL is generally quite good at pointing you directly to the source of your syntax error with the error message:

check the manual that corresponds to your MySQL server version for the right syntax to use near ‘COMMENT »),

With the exception of errors occurring at the end of the statement, which get a little ambiguous, the right syntax to use near will show you exactly what’s amiss. In the above case, the COMMENT '') should direct you to the only COMMENT attribute followed by a ), which was the one at PRIMARY KEY. From there, check the manual (linked above) for legal syntax in each segment of your statement.

I have a problem with my database. I have created the model and now I want to sync it with my database on my WAMP Server (local) but it keeps giving me an error. I searched for the cause of the error for several days. Since I cannot find the problem, I’ve decided to share it with you.

Message log:

Executing SQL script in server

ERROR: Error 1064: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near ') NULL DEFAULT NULL , n_category VARCHAR(45) NULL DEFAULT NULL ,    
PRIMARY K' at line 3

CREATE TABLE IF NOT EXISTS `telo2p`.`d_category` 
(
  `idd_category` INT(11) NOT NULL AUTO_INCREMENT,
  `f_sells` DOUBLE(11) NULL DEFAULT NULL,
  `n_category` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`idd_category`) 
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci

SQL script execution finished: statements: 6 succeeded, 1 failed

Any advice about what I am doing wrong here?

If you’ve been using WordPress for a while, you may have decided to get into more advanced database management. This often involves using the MySQL command line, which can, in turn, lead to confusing problems such as MySQL 1064 errors.

Fortunately, while resolving this error can be confusing at first due to its many potential causes, its solutions tend to be relatively simple. Once you determine the reason behind the database error you’re seeing, you should be able to fix it fairly quickly.

In this post, we’ll cover the various possible causes of the MySQL 1064 error. Then we’ll share solutions for each common situation, to help you get your database and your site back up and running.

Let’s get started!

Why the MySQL 1064 Error Occurs

The MySQL 1064 error is a syntax error. This means the reason there’s a problem is because MySQL doesn’t understand what you’re asking it to do. However, there are many different situations that can lead to this type of miscommunication between you and your database.

The simplest cause is that you’ve made a mistake while typing in a command and MySQL can’t understand your request. Alternatively, you may be attempting to use outdated or even obsolete commands that can’t be read.

In other cases, you may have attempted to include a ‘reserved word’ in one of your commands. Reserved words are terms that can only be used in specific contexts in MySQL. If you attempt to use them in other ways, you’ll be faced with an error.

It’s also possible that there is some data missing from your database. When you make a request via MySQL which references data that isn’t where it’s supposed to be, you’ll also see the 1064 error. Finally, transferring your WordPress database to another server can also lead to the same issue.

As you can see, there are many potential causes for this problem, which can make it tricky to resolve. Unless you’re in the process of moving your database or taking some other action that points to a specific cause, you’ll likely need to try a few different solutions before you land on the right one. Fortunately, none of them are too difficult to execute, as we’ll see next.

Oh no, you’re getting the MySQL 1064 Error…😭 Don’t despair! Here are 5 proven solutions to get it fixed immediately 🙏Click to Tweet

How to Fix the MySQL 1064 Error (5 Methods)

If you already have an idea of what’s causing your MySQL 1064 error, you can simply skip down to the resolution for your specific situation. However, if you’re not sure why the error has occurred, the simplest strategy is to try the easiest solution first.

In that case, we’d suggest testing out the five most likely fixes in the following order.

1. Correct Mistyped Commands

The good thing about MySQL typos is that they’re the simplest explanation for syntax issues such as the 1064 error. Unfortunately, they can also be the most tedious to correct. Generally speaking, your best option is to manually proofread your code and look for any mistakes you may have made.

We suggest using the MySQL Manual as a reference while you do so, double-checking anything you’re not sure about. As you might imagine, this can get pretty time-consuming, especially if you’ve been working in the MySQL command line for a while or if you’re new to this task.

An alternative to manually checking your work is to employ a tool such as EverSQL:

With this solution, you can simply input your MySQL to check for errors automatically. However, keep in mind that these platforms aren’t always perfect and you may still want to validate the results yourself.

2. Replace Obsolete Commands

As platforms grow and change, some commands that were useful in the past are replaced by more efficient ones. MySQL is no exception. If you’re working on your database following a recent update or have referenced an outdated source during your work, it’s possible that one or more of your commands are no longer valid.

You can check to see whether this is the case using the MySQL Reference Manual. You’ll find mentions of commands that have been made obsolete by each MySQL version in the relevant sections:

Once you’ve determined which command is likely causing the problem, you can simply use the ‘find and replace’ function to remove the obsolete command and add in the new version. For example, if you were using storage_engine and find that it no longer works, you could simply replace all instances with the new default_storage_engine command.

3. Designate Reserved Words

In MySQL, using a reserved word out of context will result in a syntax error, as it will be interpreted as incorrect. However, you can still use reserved words however you please by containing them within backticks, like this: `select`

Each version of MySQL has its own reserved words, which you can read up on in the MySQL Reference Manual. A quick find and replace should enable you to resolve this issue if you think it may be causing your 1064 error.

4. Add Missing Data

If your latest MySQL query attempts to reference information in a database and can’t find it, you’re obviously going to run into problems. In the event that none of the preceding solutions resolves your MySQL 1064 error, it may be time to go looking for missing data.

Unfortunately, this is another solution that can be quite tedious and has to be done by hand. The best thing you can do in this situation is to work backward, starting with your most recent query. Check each database it references, and make sure all the correct information is present. Then move on to the next most recent query, until you come to the one that’s missing some data.

5. Use Compatibility Mode to Transfer WordPress Databases

This final 1064 error solution isn’t as straightforward as the others on our list. However, if you’re migrating your WordPress site to a new host or otherwise moving it to a different server, you’ll need to take extra steps to avoid causing problems with your database.

The simplest solution is to use a migration plugin that includes a compatibility mode, such as WP Migrate DB:

This will enable an auto-detection feature that will make sure your latest site backup and database are compatible with multiple versions of MySQL. You can access the compatibility mode setting by navigating to Tools > Migrate DB > Advanced Options:

Check the box next to Compatible with older versions of MySQL before starting your site migration. This way, you should be able to avoid any issues during the process.

Summary

Database errors can throw a wrench in your plans, and may even compromise your website’s stability. Knowing how to resolve issues such as the MySQL 1064 error can help you react quickly, and minimize downtime on your site.

There are five methods you can try to fix the MySQL 1064 error when you encounter it, depending on its most likely cause:

1. Correct mistyped commands.
2. Replace obsolete commands.
3. Designate reserved words.
4. Add missing data.
5. Transfer WordPress databases in compatibility mode.

Solution 1

It looks like you’ve taken the COMMENT strings a little too far. According to the MySQL CREATE TABLE syntax, a COMMENT attribute is only permitted on a column definition. That means it’s invalid on the INDEX or PRIMARY KEY definitions you have listed near the end of your CREATE statements.

The COMMENT '' aren’t necessary and can be omitted entirely, especially since you are leaving them blank. Otherwise, they would be used for a little bit of extra human-readable metadata on your column definitions.

To get this working with what you have, remove the COMMENT attributes from your index and primary key definitions.

CREATE TABLE IF NOT EXISTS `university`.`CITY` (
  `ID_CITY` INT NOT NULL COMMENT '',
  `CNAME` TEXT(15) NULL COMMENT '',
  `POPULATION` INT NULL COMMENT '',
  -- No COMMENT on PK
  PRIMARY KEY (`ID_CITY`)
) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `sakila`.`actor` (
  `actor_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '',
  `first_name` VARCHAR(45) NOT NULL COMMENT '',
  `last_name` VARCHAR(45) NOT NULL COMMENT '',
  `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
  -- No COMMENT on PK or INDEX
  PRIMARY KEY (`actor_id`),
  INDEX `idx_actor_last_name` (`last_name` ASC)
) ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;

Or the whole thing without any blank COMMENTs:

CREATE TABLE IF NOT EXISTS `university`.`CITY` (
  `ID_CITY` INT NOT NULL COMMENT 'A comment that is not blank!',
  `CNAME` TEXT(15) NULL,
  `POPULATION` INT NULL,
  PRIMARY KEY (`ID_CITY`)
) ENGINE = InnoDB;

(Same for the other table)

MySQL is generally quite good at pointing you directly to the source of your syntax error with the error message:

check the manual that corresponds to your MySQL server version for the right syntax to use near ‘COMMENT »),

With the exception of errors occurring at the end of the statement, which get a little ambiguous, the right syntax to use near will show you exactly what’s amiss. In the above case, the COMMENT '') should direct you to the only COMMENT attribute followed by a ), which was the one at PRIMARY KEY. From there, check the manual (linked above) for legal syntax in each segment of your statement.

Solution 2

Well, the BIG probleme is that Mysql Workbench adds itself indexes comments that generate an error, when using «Forward Engineer» or «Synchronize Model»

This problem did not exist when I was using version 6.0.