Altering tables

Client Joined: Feb 18, 2004 Posts: 22 Location: Israel

Hi Guys,

I'm trying to alter my users table with the following format;

ALTER TABLE nuke_users (
ADD user_color_gc varchar(6),
ADD user_color_gi int(10) DEFAULT '0',
ADD user_quickreply tinyint(1) DEFAULT '0' NOT NULL,
ADD user_allow_arcadepm tinyint(4) DEFAULT '1' NOT NULL,
ADD kick_ban int(2) DEFAULT '0' NOT NULL,
ADD user_wordwrap smallint(2) DEFAULT '70' NOT NULL,
ADD agreedtos tinyint(1) DEFAULT '0' NOT NULL,

And phpmyadmin keeps kicking back this error;

#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 '(
ADD user_color_gc varchar(6),
ADD user_color_gi int(1

Can some of you experts help me on this???

TIA

JLart

Posted: Mon May 02, 2005 5:29 pm

Sorry to bump like this you guys but I really need help on this. The site I'm building on a local server is almos ready to upload and I got a few errors because of the non addition of these tabls.

TIA

JLart

Posted: Tue May 03, 2005 12:45 am

Try this:

Code:

ALTER TABLE `nuke_users` (


ADD `user_color_gc` varchar(6) DEFAULT NULL, 


ADD `user_color_gi` int(10) DEFAULT '0', 


ADD `user_quickreply` tinyint(1) NOT NULL DEFAULT '0', 


ADD `user_allow_arcadepm` tinyint(4) NOT NULL DEFAULT '1', 


ADD `kick_ban` int(2) NOT NULL DEFAULT '0', 


ADD `user_wordwrap` smallint(2) NOT NULL DEFAULT '70', 


ADD `agreedtos` tinyint(1) NOT NULL DEFAULT '0',


);

Hope this helps.
Steph

Posted: Tue May 03, 2005 1:51 am

Thank's for anwering Smile

The error persists, here it 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 '(
ADD `user_color_gc` varchar(6) DEFAULT NULL,
ADD `user_colo

but a little different this time, any other suggestion??

TIA

JLart

Posted: Tue May 03, 2005 2:30 am

Doh...

Okay, I must be on something tonight... oh yeah.. I know ... a 20-hour day.

Try this:

Code:

ALTER TABLE `nuke_users`


ADD `user_color_gc` varchar(6) DEFAULT NULL,


ADD `user_color_gi` int(10) DEFAULT '0',


ADD `user_quickreply` tinyint(1) NOT NULL DEFAULT '0',


ADD `user_allow_arcadepm` tinyint(4) NOT NULL DEFAULT '1',


ADD `kick_ban` int(2) NOT NULL DEFAULT '0',


ADD `user_wordwrap` smallint(2) NOT NULL DEFAULT '70',


ADD `agreedtos` tinyint(1) NOT NULL DEFAULT '0'

Sorry about that.. long day.

Posted: Tue May 03, 2005 2:49 am

I tried again with the same error but I think the problem is in the core table, here is what I found out;

On the original nuke_user table we have this:

CREATE TABLE nuke_users (
user_id int(11) NOT NULL auto_increment,
name varchar(60) NOT NULL default '',
username varchar(25) NOT NULL default '',
user_email varchar(255) NOT NULL default '',
femail varchar(255) NOT NULL default '',
user_website varchar(255) NOT NULL default '',
user_avatar varchar(255) NOT NULL default '',
user_regdate varchar(20) NOT NULL default '',
user_icq varchar(15) default NULL,
user_occ varchar(100) default NULL,
user_from varchar(100) default NULL,
user_interests varchar(150) NOT NULL default '',
user_sig varchar(255) default NULL,
user_viewemail tinyint(2) default NULL,
user_theme int(3) default NULL,
user_aim varchar(1 Cool

default NULL,
user_yim varchar(25) default NULL,
user_msnm varchar(25) default NULL,
user_password varchar(40) NOT NULL default '',
storynum tinyint(4) NOT NULL default '10',
umode varchar(10) NOT NULL default '',
uorder tinyint(1) NOT NULL default '0',
thold tinyint(1) NOT NULL default '0',
noscore tinyint(1) NOT NULL default '0',
bio tinytext NOT NULL,
ublockon tinyint(1) NOT NULL default '0',
ublock tinytext NOT NULL,
theme varchar(255) NOT NULL default '',
commentmax int(11) NOT NULL default '4096',
counter int(11) NOT NULL default '0',
newsletter int(1) NOT NULL default '0',
user_posts int(10) NOT NULL default '0',
user_attachsig int(2) NOT NULL default '0',
user_rank int(10) NOT NULL default '0',
user_level int(10) NOT NULL default '1',
broadcast tinyint(1) NOT NULL default '1',
popmeson tinyint(1) NOT NULL default '0',
user_active tinyint(1) default '1',
user_session_time int(11) NOT NULL default '0',
user_session_page smallint(5) NOT NULL default '0',
user_lastvisit int(11) NOT NULL default '0',
user_timezone tinyint(4) NOT NULL default '10',
user_style tinyint(4) default NULL,
user_lang varchar(255) NOT NULL default 'english',
user_dateformat varchar(14) NOT NULL default 'D M d, Y g:i a',
user_new_privmsg smallint(5) unsigned NOT NULL default '0',
user_unread_privmsg smallint(5) unsigned NOT NULL default '0',
user_last_privmsg int(11) NOT NULL default '0',
user_emailtime int(11) default NULL,
user_allowhtml tinyint(1) default '1',
user_allowbbcode tinyint(1) default '1',
user_allowsmile tinyint(1) default '1',
user_allowavatar tinyint(1) NOT NULL default '1',
user_allow_pm tinyint(1) NOT NULL default '1',
user_allow_viewonline tinyint(1) NOT NULL default '1',
user_notify tinyint(1) NOT NULL default '0',
user_notify_pm tinyint(1) NOT NULL default '0',
user_popup_pm tinyint(1) NOT NULL default '0',
user_avatar_type tinyint(4) NOT NULL default '3',
user_sig_bbcode_uid varchar(10) default NULL,
user_actkey varchar(32) default NULL,
user_newpasswd varchar(32) default NULL,
points int(10) default '0',
last_ip varchar(15) NOT NULL default '0',
PRIMARY KEY (user_id),
KEY uid (user_id),
KEY uname (username),
KEY user_session_time (user_session_time)
) TYPE=MyISAM;

Take a look into the red part it seems a syntax error that occurs with mysql Version 12.22 Distrib 4.0.16. Here bellow is a snapshot of the table structure under phpmyadmin:

Can you figure this out?? I lack the knowledge to go any further on this.

TIA,

JLart

Posted: Tue May 03, 2005 3:29 am

Just delete the uid key. I found MANY nuke tables had that problem and I went through and delete the duplicated keys in my tables. (Never delete the primary key, only the duplicated index key of the primary key). I never figured out why Nuke does this, but that's what I ended up doing on my domain last year.

That latest code I provided should have worked though!

But you would need to click the nuke_users table (in the left pane of phpmyadmin) and then after the nuke_users table comes up in the right pane, click the SQL option at the top of the screen... and then paste that code into the SQL block and hit "go" for it to take.

I did test it (using that method) and it did work.

Posted: Tue May 03, 2005 4:21 am

Well, I delete the duplicate key that solved one problem. The solution that I found for the other was to rebuild(insert) those 7 fields manually and it is working. Wink

Thank's for the help there.

BTW: check the Platinum sql the offending key is there also.

Take care,

JLart