Jump to content

MySQL syntax error


bluemicrobyte

Recommended Posts

So I need to do the following things to my MySQL table but its giving me errors about it not being formatted correctly. Please help!!

 

"CREATE TABLE " . LOGS_TABLE . " (
                id_log MEDIUMINT(10) NOT NULL DEFAULT '0' AUTO_INCREMENT,
                mode VARCHAR(50) NULL DEFAULT '',
                topic_id MEDIUMINT(10) NULL DEFAULT '0',
                user_id MEDIUMINT(8) NULL DEFAULT '0',
                username VARCHAR(255) NULL DEFAULT '',
                user_ip CHAR(8) DEFAULT '0' NOT NULL,
                time INT(11) NULL DEFAULT '0',
                PRIMARY KEY (id_log))";

-------------------------------------------------

"CREATE TABLE " . LOGS_CONFIG_TABLE . " (
                config_name varchar(255) NOT NULL,
                config_value varchar(255) NOT NULL,
                PRIMARY KEY (config_name))";

------------------------------------------------

"INSERT INTO " . LOGS_CONFIG_TABLE . " (
                config_name, config_value)
                VALUES ('all_admin', 0)";

----------------------------------------------

"ALTER TABLE " . USERS_TABLE . "
                ADD user_view_log TINYINT NOT NULL DEFAULT '0'";
            break;
        
         case 'postgresql':

--------------------------------------

"CREATE TABLE " . LOGS_TABLE . " (
                id_log SERIAL NOT NULL PRIMARY KEY,
                mode VARCHAR(50) DEFAULT '',
                topic_id INT4 DEFAULT 0,
                user_id INT4 DEFAULT 0,
                username VARCHAR(255) DEFAULT '',
                user_ip CHAR(8) DEFAULT '0' NOT NULL,
                time INT4 DEFAULT 0 )";

----------------------------------------------

"CREATE TABLE " . LOGS_CONFIG_TABLE . " (
                config_name varchar(255) NOT NULL PRIMARY KEY,
                config_value varchar(255) NOT NULL )";

-------------------------------------------------

"INSERT INTO " . LOGS_CONFIG_TABLE . " (
                config_name, config_value )
                VALUES ( 'all_admin', 0 )";

---------------------------------------

"ALTER TABLE " . USERS_TABLE . "
                ADD user_view_log INT2 NOT NULL DEFAULT 0";

Link to comment
Share on other sites

The first thing that jumps out at me is the DEFAULT value on your primary key column -- that's a no-no, unless it's NULL, AFAIK. The manual has the following to say about this:

 

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Note: There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. As of MySQL 3.23, an AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0.

 

I don't know if there are any other syntax errors, but that was the most apparent.

 

Hope that helps.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.