LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices



Reply
 
Search this Thread
Old 07-12-2006, 06:24 AM   #1
gabsik
Member
 
Registered: Dec 2005
Location: This planet
Distribution: Debian,Xubuntu
Posts: 546

Rep: Reputation: 30
Query SQL error


On my site when a user tries to register he gets this error:
Quote:
store:mosuser::store failed <br/>Duplicate entry '0' for key 1 SQL=INSERT INTO jos_users('id','name','username','email','password','usertype','gid','registerDate')
VALUES('0','gabrix','smith','smith@yahoo.it', ... ecc. ... )
I'm really bad in SQL please help !!!!
 
Old 07-12-2006, 06:39 AM   #2
spirit receiver
Member
 
Registered: May 2006
Location: Frankfurt, Germany
Distribution: SUSE 10.2
Posts: 424

Rep: Reputation: 33
I guess that "id" has to be unique in that table, and there exists already a row with id=0.
 
Old 07-12-2006, 09:13 AM   #3
gabsik
Member
 
Registered: Dec 2005
Location: This planet
Distribution: Debian,Xubuntu
Posts: 546

Original Poster
Rep: Reputation: 30
Ok!
Is anyway to get over this error ... ???
 
Old 07-12-2006, 09:23 AM   #4
spirit receiver
Member
 
Registered: May 2006
Location: Frankfurt, Germany
Distribution: SUSE 10.2
Posts: 424

Rep: Reputation: 33
Of course it is. You must either provide an id that's not already present or provide none at all and define your table in such a way that id will be incremented automatically. Have a look at the database documentation and search for "increment".
 
Old 07-12-2006, 09:09 PM   #5
gabsik
Member
 
Registered: Dec 2005
Location: This planet
Distribution: Debian,Xubuntu
Posts: 546

Original Poster
Rep: Reputation: 30
that's what i was after isn't it ?

Quote:
When multiple servers are configured as replication masters, special steps must be taken to prevent key collisions when using AUTO_INCREMENT columns, otherwise multiple masters may attempt to use the same AUTO_INCREMENT value when inserting rows.
and that's the right documentation where i have to look for :
Quote:
How to assign the AUTO_INCREMENT attribute to a column: Section 13.1.5, “CREATE TABLE Syntax”, and Section 13.1.2, “ALTER TABLE Syntax”.
I have multiple master sites could you show me how you would do it ?
It's my first sql .... it looks like there is lot of writing down !
http://dev.mysql.com/doc/refman/5.0/...ate-table.html
don't know where to start ...

Last edited by gabsik; 07-12-2006 at 09:15 PM.
 
Old 07-13-2006, 10:50 AM   #6
gabsik
Member
 
Registered: Dec 2005
Location: This planet
Distribution: Debian,Xubuntu
Posts: 546

Original Poster
Rep: Reputation: 30
Quote:
The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:
I create a table let's say : users.
and than add this tags to SQL:
Quote:
CREATE TABLE users (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);

INSERT INTO users (name) VALUES
('admin')
Admin is the only registred user i have .
Is it correct ????????????????????????????????????????????????????????????????????????????????????????
 
Old 07-13-2006, 11:29 AM   #7
spirit receiver
Member
 
Registered: May 2006
Location: Frankfurt, Germany
Distribution: SUSE 10.2
Posts: 424

Rep: Reputation: 33
Looks fine to me, why don't you just try it? You'll see that 'admin' automatically gets an id, and if you insert a new row, it'll get another id. I don't know anything about replication, but I guess it's nothing you should do unless you've got some experience with database management.
 
Old 07-14-2006, 07:00 AM   #8
gabsik
Member
 
Registered: Dec 2005
Location: This planet
Distribution: Debian,Xubuntu
Posts: 546

Original Poster
Rep: Reputation: 30
I cannot f***k up with SQL if something goes wrong .... that's how i did :
Quote:
mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| cilento |
| italian |
| mysql |
| test |
| wwwgab |
+----------+
5 rows in set (0.00 sec)

mysql> use wwwgab;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table users (
-> id mediumint not null auto_increment,
-> name CHAR(30) NOT NULL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO users (name) VALUES
-> ('admin');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM users;
+----+-------+
| id | name |
+----+-------+
| 1 | admin |
+----+-------+
1 row in set (0.00 sec)
It looks like a success .... !
 
Old 07-14-2006, 07:12 AM   #9
spirit receiver
Member
 
Registered: May 2006
Location: Frankfurt, Germany
Distribution: SUSE 10.2
Posts: 424

Rep: Reputation: 33
At least I can't see any problem with that.
 
Old 07-14-2006, 08:14 AM   #10
gabsik
Member
 
Registered: Dec 2005
Location: This planet
Distribution: Debian,Xubuntu
Posts: 546

Original Poster
Rep: Reputation: 30
I'm sorry but it is still giving me the error i missed something sure ... !
Quote:
store:mosuser::store failed <br/>Duplicate entry '0' for key 1 SQL=INSERT INTO jos_users('id','name','username','email','password','usertype','gid','registerDate')
VALUES('0','gabrix','smith','smith@yahoo.it', ... ecc. ... )
Anyway,i don't think is necessary to create a new table.If you see the error message it says of a table jos_users and maybe i have to auto_icrement the id and modify that table ... but how ??????????????

Last edited by gabsik; 07-14-2006 at 08:38 AM.
 
Old 07-14-2006, 09:53 AM   #11
gabsik
Member
 
Registered: Dec 2005
Location: This planet
Distribution: Debian,Xubuntu
Posts: 546

Original Poster
Rep: Reputation: 30
what's the id number i have to use for auto_increment?
Code:
ALTER TABLE jos_users AUTO_INCREMENT = 1
1?
 
Old 07-14-2006, 09:58 AM   #12
spirit receiver
Member
 
Registered: May 2006
Location: Frankfurt, Germany
Distribution: SUSE 10.2
Posts: 424

Rep: Reputation: 33
Quote:
i don't think is necessary to create a new table
Why would you want to create a new table? If you want a column to be incremented automatically, you'll have to specify that during creation of the table or in an "ALTER TABLE" statement. For example, if you had forgotten "AUTO_INCREMENT" in that "CREATE TABLE users" statement above, then you could add it later using
Code:
ALTER TABLE users MODIFY id MEDIUMINT NOT NULL AUTO_INCREMENT;
And if the AUTO_INCREMENT property is set for column 'id' in table 'jos_users' already, you can use the following command to add a new row, which will be assigned a new id automatically:
Code:
INSERT INTO jos_users('name','username','email','password','usertype','gid','registerDate')
VALUES('gabrix','smith','smith@yahoo.it', ... ecc. ... );
 
Old 07-15-2006, 12:32 AM   #13
gabsik
Member
 
Registered: Dec 2005
Location: This planet
Distribution: Debian,Xubuntu
Posts: 546

Original Poster
Rep: Reputation: 30
Quote:
mysql> ALTER TABLE users MODIFY id MEDIUMINT NOT NULL AUTO_INCREMENT;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
This the second code
Quote:
mysql> INSERT INTO jos_users('id','name','username','email','password','usertype','gid','registerDate')
-> VALUES('0','marzia','marzia','marzia@gabrix.ath.cx',154b4ffb0028f31e7bcba5226ce4247d','','1','18','2 006-07-15',06:45:50','acf90311f1fbaf836e422b5073b6de8e')
I really need detailed SQL auto_increment instructions ...

Quote:
mosuers::store failed </br>Unknown column 'activation' in 'filed list' SQL=INSERT INTO jos_users ('id',....
failed user registration ....

Last edited by gabsik; 07-15-2006 at 12:56 AM.
 
Old 07-15-2006, 03:13 AM   #14
spirit receiver
Member
 
Registered: May 2006
Location: Frankfurt, Germany
Distribution: SUSE 10.2
Posts: 424

Rep: Reputation: 33
OK, so after the first statement from your last post, the 'id' column will be incremented automatically. Try "DESCRIBE users;", it will also give you that information.

But then, I think the error message you just gave doesn't belong to the INSERT statement above, as it complains about a nonexistent column named "activation" which didn't appear in that statement. And have a look at the INSERT statement from my previous post: If you want the value of 'id' to be inserted automatically, you must not specify a value in the statement. So the list of column names begins with 'name' instead of 'id':
Code:
INSERT INTO jos_users('name','username',...);
 
Old 07-15-2006, 11:22 AM   #15
gabsik
Member
 
Registered: Dec 2005
Location: This planet
Distribution: Debian,Xubuntu
Posts: 546

Original Poster
Rep: Reputation: 30
Step by step ...
Code:
mysql> DESCRIBE jos_users;
+-----------------------+-----------------------+------+-----+---------------------+-------+
| Field                 | Type                  | Null | Key | Default             | Extra |
+-----------------------+-----------------------+------+-----+---------------------+-------+
| id                    | mediumint(8)          |      | PRI | 0                   |       |
| user_active           | tinyint(1)            | YES  |     | 1                   |       |
| username              | varchar(25)           |      |     |                     |       |
| password              | varchar(32)           |      |     |                     |       |
| name                  | varchar(50)           |      |     |                     |       |
| usertype              | varchar(25)           |      | MUL |                     |       |
| block                 | tinyint(4)            |      |     | 0                   |       |
| sendEmail             | tinyint(4)            | YES  |     | 1                   |       |
| gid                   | tinyint(3) unsigned   |      |     | 1                   |       |
| registerDate          | datetime              |      |     | 0000-00-00 00:00:00 |       |
| lastvisitDate         | datetime              |      |     | 0000-00-00 00:00:00 |       |
| user_session_time     | int(11)               |      | MUL | 0                   |       |
| user_session_page     | smallint(5)           |      |     | 0                   |       |
| user_lastvisit        | int(11)               |      |     | 0                   |       |
| user_regdate          | int(11)               |      |     | 0                   |       |
| user_level            | tinyint(4)            | YES  |     | 0                   |       |
| user_posts            | mediumint(8) unsigned |      |     | 0                   |       |
| user_timezone         | decimal(5,2)          |      |     | 0.00                |       |
| user_style            | tinyint(4)            | YES  |     | NULL                |       |
| user_lang             | varchar(255)          | YES  |     | NULL                |       |
| user_dateformat       | varchar(14)           |      |     | d M Y H:i           |       |
| user_new_privmsg      | smallint(5) unsigned  |      |     | 0                   |       |
| user_unread_privmsg   | smallint(5) unsigned  |      |     | 0                   |       |
| user_last_privmsg     | int(11)               |      |     | 0                   |       |
| user_emailtime        | int(11)               | YES  |     | NULL                |       |
| user_viewemail        | tinyint(1)            | YES  |     | NULL                |       |
| user_attachsig        | tinyint(1)            | YES  |     | NULL                |       |
| user_allowhtml        | tinyint(1)            | YES  |     | 1                   |       |
| user_allowbbcode      | tinyint(1)            | YES  |     | 1                   |       |
| user_allowsmile       | tinyint(1)            | YES  |     | 1                   |       |
| user_allowavatar      | tinyint(1)            |      |     | 1                   |       |
| user_allow_pm         | tinyint(1)            |      |     | 1                   |       |
| user_allow_viewonline | tinyint(1)            |      |     | 1                   |       |
| user_notify           | tinyint(1)            |      |     | 1                   |       |
| user_notify_pm        | tinyint(1)            |      |     | 0                   |       |
| user_popup_pm         | tinyint(1)            |      |     | 0                   |       |
| user_rank             | int(11)               | YES  |     | 0                   |       |
| user_avatar           | varchar(100)          | YES  |     | NULL                |       |
| user_avatar_type      | tinyint(4)            |      |     | 0                   |       |
| email                 | varchar(255)          | YES  |     | NULL                |       |
| user_icq              | varchar(15)           | YES  |     | NULL                |       |
| user_website          | varchar(100)          | YES  |     | NULL                |       |
| user_from             | varchar(100)          | YES  |     | NULL                |       |
| user_from_flag        | varchar(25)           | YES  |     | NULL                |       |
| user_sig              | text                  | YES  |     | NULL                |       |
| user_sig_bbcode_uid   | varchar(10)           | YES  |     | NULL                |       |
| user_aim              | varchar(255)          | YES  |     | NULL                |       |
| user_yim              | varchar(255)          | YES  |     | NULL                |       |
| user_msnm             | varchar(255)          | YES  |     | NULL                |       |
| user_occ              | varchar(100)          | YES  |     | NULL                |       |
| user_interests        | varchar(255)          | YES  |     | NULL                |       |
| user_actkey           | varchar(32)           | YES  |     | NULL                |       |
| user_newpasswd        | varchar(32)           | YES  |     | NULL                |       |
+-----------------------+-----------------------+------+-----+---------------------+-------+
I better remove the id VALUE and get users id auto incremented,right ???
Once in the proper database i should launch:
Code:
INSERT INTO jos_users('name','username','email','password','usertype','gid','registerDate')
and in the above code i should insert
Code:
"activation"
So it looks like
Code:
INSERT INTO jos_users('name','username','email','password','usertype','gid','registerDate','activation')
The
Code:
VALUES('gabrix','smith','smith@yahoo.it', ... ecc. ... );
How should it be then ... ?
Should it be related to a real user already registred ?, to a fake one ? It's not necessary at ALL ????????????

Last edited by gabsik; 07-15-2006 at 11:25 AM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL query performance smaida Programming 6 06-08-2005 10:22 AM
Query tool for MS-SQL server dunric Linux - Software 0 11-14-2004 12:39 PM
SQL query help pls. vickr1z Programming 8 10-19-2004 12:25 AM
Massive SQL Query patpawlowski Programming 7 03-05-2004 05:24 PM
SQL Query question oulevon Programming 7 01-16-2004 02:50 AM


All times are GMT -5. The time now is 06:08 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration