Troubleshooting MySQL Syntax Errors

Question

I am trying to create a new table which has always been pretty easy in the past. This time I am trying to increase the quality of the way I do things by researching the collation I use as the default for my table. After reading a bit I decided utf8mb4_unicode_ci was the way to go.

At this point I create a table with 11 rows. Everytime I try and submit it tells me,

  #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 
') NOT NULL , `password` INT(10000) NOT NULL , `hash` VARCHAR(10000) NOT 
NULL ,' at line 1

I have never had auto generated sql give me an error before. So, I think this has to do with the coalition I chose. Also it appears to have a problem close to my double. Which leads me to believe I can't use the collation I am using with a Double?

Any help would be appreciated.

Thank you,

    CREATE TABLE `skel`.`accounts` ( `id` INT(11) NOT NULL AUTO_INCREMENT 
, `username` VARCHAR(255) NOT NULL , `email` VARCHAR(255) NOT NULL , 
`first` VARCHAR(255) NOT NULL , `last` VARCHAR(255) NOT NULL , `phone` 
VARCHAR(50) NOT NULL , `address` VARCHAR(255) NOT NULL , `rate` 
DOUBLE(20) NOT NULL , `password` VARCHAR(40) NOT NULL , `hash` 
VARCHAR(40) NOT NULL , `confirmed` VARCHAR(5) NOT NULL , PRIMARY KEY 
(`id`)) ENGINE = InnoDB;

Is double with without precision specification legal? Also, what are you thinking int(10000) accomplishes? — Uueerdo
– Uueerdo, Commented Feb 20, 2016 at 0:52
Why do you think the problem has anything to do with collation? Collation doesn't change MySQL syntax rules. — Barmar
– Barmar, Commented Feb 20, 2016 at 1:18
I was not sure why the problem was happening. Per the reason I asked the question. I have never paid attention to the collation before it was the only thing I changed and as far as for the double being a stupid question "which you have all made your point very well" I just was not thinking obviously. — wuno
– wuno, Commented Feb 20, 2016 at 1:29

Gordon Linoff · Accepted Answer · 2016-02-20 00:54:25Z

4

DOUBLE(20) is not a valid data type. In MySQL, double requires both scale and precision to be specified (if either is). This is explained in the documentation.

So, specifying something like DOUBLE(20, 5) should be acceptable.

Also, INT(10000) doesn't really make sense. You don't need the length specifier, but perhaps you intend INT(5) (5 digits of precision). Or perhaps you should use DECIMAL() for the column.

edited Feb 20, 2016 at 0:54

answered Feb 20, 2016 at 0:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Barmar Over a year ago

He's edited the question to remove INT(10000). That was clearly a mistake, since no one would use an integer for a password.

Collectives™ on Stack Overflow

Troubleshooting MySQL Syntax Errors

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related