customer_id = 0 gets assigned

pbolgar · Post by **pbolgar** » Sat Dec 11, 2010 9:53 pm

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

Post by **maschoff** » Mon Dec 13, 2010 8:29 am

Which version of OpenEMM do you use?

pbolgar · Post by **pbolgar** » Mon Dec 13, 2010 3:20 pm

ma wrote:Which version of OpenEMM do you use?

I use 6.2 but and I noticed the bug after I did the update.

Post by **maschoff** » Tue Dec 14, 2010 8:28 am

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?

pbolgar · Post by **pbolgar** » Tue Dec 14, 2010 1:17 pm

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.

Code: Select all

DESCRIBE customer_1_tbl;

Post by **maschoff** » Tue Dec 14, 2010 1:23 pm

Question remains, why your customer_id is no auto_increment field. What was the first version of OpenEMM you used?

pbolgar · Post by **pbolgar** » Tue Dec 14, 2010 1:25 pm

hmmm, good question, I think 6.0.1.

pbolgar · Post by **pbolgar** » Tue Dec 14, 2010 1:32 pm

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;

"A general error occurred. Please check your input.

loading ... stopped"

Post by **maschoff** » Tue Dec 14, 2010 1:34 pm

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?

pbolgar · Post by **pbolgar** » Tue Dec 14, 2010 1:40 pm

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?

Post by **maschoff** » Tue Dec 14, 2010 1:40 pm

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)

pbolgar · Post by **pbolgar** » Tue Dec 14, 2010 1:43 pm

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

Post by **maschoff** » Tue Dec 14, 2010 1:45 pm

Sure, you already set customer_id to auto_increment. But what happens if you want to insert a recipient manually via SQL?

pbolgar · Post by **pbolgar** » Tue Dec 14, 2010 2:02 pm

Post by **maschoff** » Tue Dec 14, 2010 2:26 pm

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