customer_id = 0 gets assigned

[pbolgar]

I don't understand either, but how do I make it an autoincrement field?

if I do the code you gave me,

Code: Select all

ALTER TABLE `customer_1_tbl` MODIFY `customer_id` int(11) AUTO_INCREMENT; 

I get an error message as you saw - and if I do what I did which is

Code: Select all

alter table customer_1_tbl change customer_id customer_id Int(11) unsigned not null auto_increment primary key;

then I get

"A general error occurred. Please check your input.
loading ... stopped"

in openemm when I try to view or add customers.

I don't know enough about SQL to know what the problem is, what am I doing wrong?

[maschoff]

Plese post the output of

Code: Select all

desc customer_1_tbl;

and

Code: Select all

desc customer_1_tbl_seq;

first, as well as your MySQL version.

[pbolgar]

Code: Select all

mysql> desc customer_1_tbl;
+---------------+--------------+------+-----+--------------------------+-------+
| Field         | Type         | Null | Key | Default                  | Extra |
+---------------+--------------+------+-----+--------------------------+-------+
| customer_id   | int(11)      | NO   |     | 0                        |       |
| email         | varchar(100) | YES  |     | NULL                     |       |
| gender        | int(11)      | NO   |     | 2                        |       |
| mailtype      | int(11)      | YES  |     | 0                        |       |
| firstname     | varchar(100) | YES  |     | NULL                     |       |
| lastname      | varchar(100) | YES  |     | NULL                     |       |
| creation_date | timestamp    | NO   |     | 0000-00-00 00:00:00      |       |
| change_date   | timestamp    | NO   |     | 0000-00-00 00:00:00      |       |
| title         | varchar(100) | YES  |     | NULL                     |       |
| datasource_id | int(11)      | NO   |     | 0                        |       |
| traininglevel | varchar(100) | YES  |     | Unknown                  |       |
| trainingnr    | double       | YES  |     | 0                        |       |
| processingl   | varchar(100) | YES  |     | Processing Level Unknown |       |
| processingnr  | double       | YES  |     | 0                        |       |
| country       | varchar(100) | YES  |     | NULL                     |       |
| city          | varchar(100) | YES  |     | NULL                     |       |
| d4a           | double       | YES  |     | 0                        |       |
| addoid        | double       | YES  |     | NULL                     |       |
| middlename    | varchar(100) | YES  |     | NULL                     |       |
| mailstatus    | varchar(100) | YES  |     | NULL                     |       |
| idealccdonor  | double       | YES  |     | 0                        |       |
| linkclicks    | double       | YES  |     | 0                        |       |
| paypalclicks  | double       | YES  |     | 0                        |       |
| lastclickdate | date         | YES  |     | NULL                     |       |
| otc           | double       | YES  |     | 0                        |       |
+---------------+--------------+------+-----+--------------------------+-------+
25 rows in set (0.00 sec)

Code: Select all

mysql> desc customer_1_tbl_seq;
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| customer_id | int(11) | NO   | PRI | NULL    | auto_increment |
+-------------+---------+------+-----+---------+----------------+
1 row in set (0.00 sec)

and mySQL:

Code: Select all

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+--------------------+
| Variable_name           | Value              |
+-------------------------+--------------------+
| protocol_version        | 10                 |
| version                 | 5.1.41-3ubuntu12.8 |
| version_comment         | (Ubuntu)           |
| version_compile_machine | i486               |
| version_compile_os      | debian-linux-gnu   |
+-------------------------+--------------------+

[maschoff]

Oops, your customer_id isn't even key! Parameter of customer_id in table customer_1_tbl must be identical to customer_id in customer_1_tbl_seq, i.e. the field must be a primary key and a auto_increment field. Try

Code: Select all

ALTER TABLE customer_1_tbl ADD PRIMARY KEY (customer_id);

and then

Code: Select all

ALTER TABLE `customer_1_tbl` MODIFY `customer_id` int(11) AUTO_INCREMENT;

[pbolgar]

Fantastic, that did the trick!

[maschoff]

Good! With

Code: Select all

SELECT MAX(customer_id) FROM customer_1_tbl;

you can check the recipient with the highest number. When you have inserted a new one, the same command should show a value increased by one. This demonstrates that the auto_increment works correctly.

[pbolgar]

I tested it and it does what it is supposed to, thank you very much! Very happy!