Re: Modify column to auto_increment

[Aigini Navaneethan]

mysql> Show Create Table lz_chat_archive_dup2;
+----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| lz_chat_archive_dup2 | CREATE TABLE `lz_chat_archive_dup2` (
`time` int(11) unsigned NOT NULL DEFAULT '0',
`endtime` int(11) unsigned NOT NULL DEFAULT '0',
`closed` int(11) unsigned NOT NULL DEFAULT '0',
`chat_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`external_id` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`fullname` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`internal_id` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`group_id` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`area_code` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`html` longtext COLLATE utf8_bin NOT NULL,
`plaintext` longtext COLLATE utf8_bin NOT NULL,
`transcript_text` text COLLATE utf8_bin NOT NULL,
`transcript_html` text COLLATE utf8_bin NOT NULL,
`email` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`company` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`phone` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`call_me_back` tinyint(1) unsigned NOT NULL DEFAULT '0',
`iso_language` varchar(8) COLLATE utf8_bin NOT NULL DEFAULT '',
`iso_country` varchar(5) COLLATE utf8_bin NOT NULL DEFAULT '',
`host` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`ip` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`gzip` tinyint(1) unsigned NOT NULL DEFAULT '0',
`transcript_sent` tinyint(1) unsigned NOT NULL DEFAULT '1',
`transcript_receiver` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`question` text COLLATE utf8_bin NOT NULL,
`customs` text COLLATE utf8_bin NOT NULL,
`subject` text COLLATE utf8_bin NOT NULL,
`voucher_id` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`wait` int(11) unsigned NOT NULL DEFAULT '0',
`duration` int(11) unsigned NOT NULL DEFAULT '0',
`accepted` tinyint(1) unsigned NOT NULL DEFAULT '0',
`ended` tinyint(1) unsigned NOT NULL DEFAULT '0',
`chat_type` tinyint(1) unsigned NOT NULL DEFAULT '1',
`ref_url` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
PRIMARY KEY (`chat_id`),
KEY `closed` (`closed`),
KEY `chat_type` (`chat_type`),
KEY `endtime` (`endtime`),
KEY `transcript_sent` (`transcript_sent`),
KEY `duration` (`duration`),
KEY `time` (`time`)
) ENGINE=MyISAM AUTO_INCREMENT=383933 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>


mysql> select min(chat_id), max(chat_id), count(*) from lz_chat_archive_dup2;
+--------------+--------------+----------+
| min(chat_id) | max(chat_id) | count(*) |
+--------------+--------------+----------+
| NULL | NULL | 0 |
+--------------+--------------+----------+
1 row in set (0.00 sec)

mysql>


Number of rows expected : 2589.

I tried this method :

1) Create a duplicate of the table only with the structure (as pasted in the first command above).
2) Set the auto_increment value to 383933 (I want this value to be the first row).
3) Use a csv file (with information extracted from the original table, which has chat_id column value as empty) to push the data into this table.
Commands used to achieve this :

select * from lz_chat_archive_dup1 INTO OUTFILE '/usr/local/LivezillaDB/mysql/lz_chat_archivedup1.csv' FIELDS TERMINATED BY ',';

LOAD DATA INFILE '/usr/local/LivezillaDB/mysql/lz_chat_archivedup1.csv' INTO TABLE lz_chat_archive_dup2 FIELDS TERMINATED BY ',';

However, I receive an error here :

mysql> LOAD DATA INFILE '/usr/local/LivezillaDB/mysql/lz_chat_archivedup1.csv' INTO TABLE lz_chat_archive_dup2 FIELDS TERMINATED BY ',';
ERROR 1366 (HY000): Incorrect integer value: '' for column 'chat_id' at row 1
mysql>