LinuxQuestions.org - Query SQL error

- Linux - Software (https://www.linuxquestions.org/questions/linux-software-2/)

- - Query SQL error (https://www.linuxquestions.org/questions/linux-software-2/query-sql-error-463251/)

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

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

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

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

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

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

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.

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

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

I'm sorry but it is still giving me the error i missed something sure ... !

Quote:

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

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

Code:

ALTER TABLE jos_users AUTO_INCREMENT = 1

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

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

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

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