MySQL Forums
Forum List  »  Data Recovery

Re: Modify column to auto_increment
Posted by: Aigini Navaneethan
Date: August 16, 2020 02:38AM

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>

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Modify column to auto_increment
72
August 16, 2020 02:38AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.