MySQL Auto_increment Error

A problem happens with the following code:

$db = new mysqli(host, user, pass, dbname);

$query = "
create table stuff
( stuffid int not null auto_increment,
  username varchar not null
$db->query($query); // There is an error near "stuffid int not null auto_increment"

The “auto_increment” bug appears. How do I fix this?

On another site, I found something that said to use

create table stuff
( stuffid int not null auto_increment primary key,
  name varchar(30) not null

alter table stuff auto_increment = 1;

but it didn’t work.

Your second suggestion is on the right track. But looking at the syntax, it seems like it’s SQL Server syntax, not MySQL.

In your first example, you are indeed lacking a primary key. But the syntax for setting it in MySQL is a bit different.

I think this is correct:

    name varchar(30) NOT NULL,
    PRIMARY KEY (stuffid)

I have tried it but that didn’t work. I found out that it was a bug of auto_increment. Case didn’t matter.

I shall try the alter table statement.

It didn’t seem to work, though.

So what DID happen?

No, case doesn’t matter. What does matter is that a primary key is defined on the table and how it is defined. In your first example, you didn’t specify a primary key at all. In the second one you specified it in a format that doesn’t work on MySQL. That’s not a “bug” in MySQL. You’re sending queries for a different database system to MySQL, of course that won’t work.

Just a suggestion, but maybe you can add the table in phpMyAdmin with their form interface? That way, phpMyAdmin can generate the correct SQL code for you (which you can view and backup if you want).


I would like you to read this.

I am trying in an online IDE, and here is what I get:

#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
)’ at line 3

As it says, check the manual that corresponds to your MySQL server version

Check out my code:

id INT NOT NULL auto_increment,
name varchar(100) NOT NULL,
1 Like

yes that is right MySQL has a error with the auto_increment

That cause is the MySQL error auto_increment. The auto_increment bug is an effect on the MySQL Database. It is not a syntax error.

Yes, auto_increment doesn’t work (and on, you cannot use InnoDB.) Nice test, though.

Yes, you can, I tried it already

Then why did you write this:

No, I was just telling the error, and I tried fixing it…


1 Like

Good Heavens, no more posts I see!

This topic was automatically closed 15 days after the last reply. New replies are no longer allowed.