Page 1 of 2
customer_id = 0 gets assigned
Posted: Sat Dec 11, 2010 9:53 pm
by pbolgar
I recently imported a large amount of data - and then I noticed that all of the customer_IDs that imported are 0 - it is not incrementing any more. I fixed that with some SQL gymnastics and I also entered the highest customer_id I generated from customer_1_tbl into customer_1_tbl_seq - but then I tried adding a test customer and it again got 0 as customer_id in the customer_1_tbl and no new entry was created in the customer_1_tbl_seq.
What do I do?
I appreciate your help
Posted: Mon Dec 13, 2010 8:29 am
by maschoff
Which version of OpenEMM do you use?
version
Posted: Mon Dec 13, 2010 3:20 pm
by pbolgar
ma wrote:Which version of OpenEMM do you use?
I use 6.2 but and I noticed the bug after I did the update.
Posted: Tue Dec 14, 2010 8:28 am
by maschoff
Table customer_1_tbl_seq is not used any more by OpenEMM 6.2 because field customer_id in table customer_1_tbl is an auto-increment field. Did you use the import wizard of OpenEMM or did you import directly into MySQL?
Posted: Tue Dec 14, 2010 1:17 pm
by pbolgar
Oh, good customer_1_tbl_seq is not used any more. Can I just drop that table then?
I just looked, I think that's what the bug is.
gives
| Field | Type | Null | Key | Default | Extra |
customer_id | int(11) | NO | | 0 | |
so that might be the bug - that customer_id is not set to autonumber.
I haven't added any customers via SQL but both import wizard ones and individually entered ones come up with 0 as customer_id.
Best,
Peter
Posted: Tue Dec 14, 2010 1:23 pm
by maschoff
Question remains, why your customer_id is no auto_increment field. What was the first version of OpenEMM you used?
Posted: Tue Dec 14, 2010 1:25 pm
by pbolgar
hmmm, good question, I think 6.0.1.
Posted: Tue Dec 14, 2010 1:32 pm
by pbolgar
and I am not sure how to fix it...
After backing up my customer_1_tbl, I did
Code: Select all
alter table customer_1_tbl change customer_id customer_id Int(11) auto_increment not null;
So now
| Field | Type | Null | Key | Default | Extra
| customer_id | int(11) unsigned | NO | PRI | NULL | auto_increment |
But now what I get when I try to get an overview of recipients or manually add one, I get:
"A general error occurred. Please check your input.
loading ... stopped"
Posted: Tue Dec 14, 2010 1:34 pm
by maschoff
The oldest seed file for the OpenEMM DB I could find in our repository right now is for OpenEMM 5.4, and even it contains
Code: Select all
CREATE TABLE `customer_1_tbl` (
`customer_id` int(11) NOT NULL auto_increment,
Are you sure you did not tamper with the DB?
Posted: Tue Dec 14, 2010 1:40 pm
by pbolgar
I see. Well I can't remember changing anything on that - I did get some error message on the update to 6.2 but I did not see that it would have caused this - and I did not have this problem up until 3 days ago.
how should I handle it?
Posted: Tue Dec 14, 2010 1:40 pm
by maschoff
Back to your question. We use
Code: Select all
ALTER TABLE `customer_1_tbl` MODIFY `customer_id` int(11) AUTO_INCREMENT;
to set a field to auto_increment. If you insert a new recipient in this table manually what happens? Maybe customer_id wants to start at 0? (You can define a start value <> 0)
Posted: Tue Dec 14, 2010 1:43 pm
by pbolgar
thanks - I tried that, see result:
Code: Select all
mysql> ALTER TABLE `customer_1_tbl` MODIFY `customer_id` int(11) AUTO_INCREMENT;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
Posted: Tue Dec 14, 2010 1:45 pm
by maschoff
Sure, you already set customer_id to auto_increment. But what happens if you want to insert a recipient manually via SQL?
Posted: Tue Dec 14, 2010 2:02 pm
by pbolgar
before I did this I restored the original customer_1_tbl so there wasn't an auto increment column, the current structure is:
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+--------------------------+-------+
| customer_id | int(11) | NO | | 0 | |
I haven't tried the manual SQL insert yet, should we try and make it auto increment first?
Posted: Tue Dec 14, 2010 2:26 pm
by maschoff
Field customer_id of table customer_1_tbl has to be an auto_increment field. Otherwise you will run in all kinds of trouble. (I still do not understand why it isn't in your installation.)