Query SQL error

gabsik · 07-12-2006, 05:24 AM

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 !!!!

spirit receiver · 07-12-2006, 05:39 AM

I guess that "id" has to be unique in that table, and there exists already a row with id=0.

gabsik · 07-12-2006, 08:13 AM

Ok!
Is anyway to get over this error ... ???

spirit receiver · 07-12-2006, 08:23 AM

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

gabsik · 07-12-2006, 08:09 PM

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 ...

gabsik · 07-13-2006, 09:50 AM

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 ????????????????????????????????????????????????????????????????????????????????????????

spirit receiver · 07-13-2006, 10:29 AM

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.

gabsik · 07-14-2006, 06:00 AM

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 .... !

spirit receiver · 07-14-2006, 06:12 AM

At least I can't see any problem with that.

gabsik · 07-14-2006, 07:14 AM

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 ??????????????

gabsik · 07-14-2006, 08:53 AM

what's the id number i have to use for auto_increment?

Code:

ALTER TABLE jos_users AUTO_INCREMENT = 1

1?

spirit receiver · 07-14-2006, 08:58 AM

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. ... );

gabsik · 07-14-2006, 11:32 PM

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 ....

spirit receiver · 07-15-2006, 02:13 AM

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',...);

gabsik · 07-15-2006, 10:22 AM

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 ????????????