The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MYSQL create table does not work

Discussion in 'Database Discussions' started by toplisek, Jan 26, 2011.

  1. toplisek

    toplisek Active Member

    Joined:
    Jan 7, 2010
    Messages:
    36
    Likes Received:
    0
    Trophy Points:
    6
    I have tried to execure CREATE TABLE with simple code like:
    CREATE TABLE example_timestamp (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    cur_timestamp TIMESTAMP(8),
    key VARCHAR(100),
    searchterms VARCHAR(100),
    ip_geo VARCHAR(100)
    )

    Why is not working actually?

    Error report is:
    #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 'VARCHAR(100), searchterms VARCHAR(100), ip_geo VARCHAR(100) ' at line 4
     
  2. cPanelDavidN

    cPanelDavidN Integration Developer
    Staff Member

    Joined:
    Dec 17, 2009
    Messages:
    571
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    Hi toplisek,

    I problem is that the column name "key" is being interpreted as a special MySQL syntax word. You could quote it, but I'd suggest naming it something else. That way, if you have software that isn't smart enough (or that the language's connector isn't robust enough) to handle the column name unquoted when making SELECT and INSERT commands, it will still be safe.

    Quoting the column name:
    Code:
    CREATE TABLE example_timestamp (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, cur_timestamp TIMESTAMP, `key` VARCHAR(100), searchterms VARCHAR(100), ip_geo VARCHAR(100));
    
    NOTE: those are backticks, the SQL style of quote marks.

    Different column name:
    Code:
    CREATE TABLE example_timestamp (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, cur_timestamp TIMESTAMP, key_id VARCHAR(100), searchterms VARCHAR(100), ip_geo VARCHAR(100));
    
    Can be just about anything, I figured you were using "key" as an index or identification column, so "key_id" seemed appropriate...but use whatever you want.

    Regards,
    -DavidN
     
Loading...

Share This Page