customer_id = 0 gets assigned

Use this forum to report bugs and to check for bugfixes and new releases of OpenEMM

Moderator: moderator

pbolgar
Posts: 32
Joined: Sat Feb 20, 2010 6:13 pm

customer_id = 0 gets assigned

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

maschoff
Site Admin
Posts: 2177
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

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

Which version of OpenEMM do you use?
OpenEMM Maintainer

pbolgar
Posts: 32
Joined: Sat Feb 20, 2010 6:13 pm

version

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.

maschoff
Site Admin
Posts: 2177
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

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

pbolgar
Posts: 32
Joined: Sat Feb 20, 2010 6:13 pm

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

maschoff
Site Admin
Posts: 2177
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

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

pbolgar
Posts: 32
Joined: Sat Feb 20, 2010 6:13 pm

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

hmmm, good question, I think 6.0.1.

pbolgar
Posts: 32
Joined: Sat Feb 20, 2010 6:13 pm

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

maschoff
Site Admin
Posts: 2177
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

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

pbolgar
Posts: 32
Joined: Sat Feb 20, 2010 6:13 pm

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?

maschoff
Site Admin
Posts: 2177
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

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)
OpenEMM Maintainer

pbolgar
Posts: 32
Joined: Sat Feb 20, 2010 6:13 pm

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

maschoff
Site Admin
Posts: 2177
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

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

pbolgar
Posts: 32
Joined: Sat Feb 20, 2010 6:13 pm

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

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?

maschoff
Site Admin
Posts: 2177
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

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

Post Reply