Jump to content

Archived

This topic is now archived and is closed to further replies.

bluemicrobyte

MySQL syntax error

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";

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

×

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.