BiTel

show create table organizer_records

show create table organizer_journal

  Клиент: вер. 6.2.709 / 15.05.2015 13:05:45
    os: Linux; java: Java HotSpot(TM) 64-Bit Server VM, v.1.8.0_45
  Сервер: вер. 6.2.875 / 18.05.2015 17:26:02
    os: Linux; java: Java HotSpot(TM) 64-Bit Server VM, v.1.8.0_45

Информация о версии:

  Клиент: вер. 6.2.714 / 25.05.2015 17:27:15
    os: Linux; java: Java HotSpot(TM) 64-Bit Server VM, v.1.8.0_45
  Сервер: вер. 6.2.881 / 22.05.2015 17:56:55
    os: Linux; java: Java HotSpot(TM) 64-Bit Server VM, v.1.8.0_45

SHOW CREATE TABLE organizer_records;

> show create table organizer_records;
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table             | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| organizer_records | CREATE TABLE `organizer_records` (
  `id` int(11) NOT NULL DEFAULT '0',
  `cid` int(11) DEFAULT NULL,
  `uid` int(11) DEFAULT NULL,                                                                                                                                           
  `created` date DEFAULT NULL,                                                                                                                                         
  `title` varchar(70) CHARACTER SET cp1251 DEFAULT NULL,                                                                                                               
  `comment` text CHARACTER SET cp1251,                                                                                                                                 
  `date_from` date DEFAULT NULL,
  `date_to` date DEFAULT NULL,
  `done` tinyint(2) DEFAULT NULL,
  `todo_uid` int(11) DEFAULT NULL,
  `todo_gid` int(11) DEFAULT NULL,
  `completed_uid` int(11) DEFAULT NULL,
  `completed_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

> show create table organizer_journal;
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table             | Create Table                                                                                                                                                                                                                                |
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| organizer_journal | CREATE TABLE `organizer_journal` (
  `id` int(11) NOT NULL DEFAULT '0',
  `rid` int(11) DEFAULT NULL,
  `uid` int(11) DEFAULT NULL,
  `message` text CHARACTER SET cp1251,
  `jdate` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

ALTER TABLE `organizer_records` ADD PRIMARY KEY (`id`);
ALTER TABLE `organizer_journal` ADD PRIMARY KEY (`id`);
ALTER TABLE `organizer_records` MODIFY COLUMN `id` INTEGER AUTO_INCREMENT;
ALTER TABLE `organizer_journal` MODIFY COLUMN `id` INTEGER AUTO_INCREMENT;

> ALTER TABLE `organizer_records` ADD PRIMARY KEY (`id`);
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'

> ALTER TABLE `organizer_journal` ADD PRIMARY KEY (`id`);
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'

> ALTER TABLE `organizer_records` MODIFY COLUMN `id` INTEGER AUTO_INCREMENT;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

> ALTER TABLE `organizer_journal` MODIFY COLUMN `id` INTEGER AUTO_INCREMENT;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

| organizer_journal                          |
| organizer_journal_old                      |
| organizer_records                          |
| organizer_records_old 

INSERT INTO organizer_records (id,cid,uid,created,title, `comment`,date_from,date_to,done,todo_uid,todo_gid,completed_uid,completed_date)
SELECT @row := @row + 1 as row, rec.cid, rec.uid, rec.created, rec.title, rec.comment, rec.date_from, rec.date_to, rec.done, rec.todo_uid, rec.todo_gid, rec.completed_uid, rec.completed_date
FROM organizer_records as rec, (SELECT @row := 3) r where id=0

DELETE FROM organizer_records WHERE id=0

ALTER TABLE `organizer_records` ADD PRIMARY KEY (`id`)
ALTER TABLE `organizer_records` MODIFY COLUMN `id` INTEGER AUTO_INCREMENT

INSERT INTO organizer_journal (id,rid,uid,message, jdate ) 
SELECT @row := @row + 1 as row, jour.rid, jour.uid, jour.message, jour.jdate
FROM organizer_journal as jour, (SELECT @row := 1559) r where id=0

DELETE FROM organizer_journal WHERE id=0;
ALTER TABLE `organizer_journal` ADD PRIMARY KEY (`id`);
ALTER TABLE `organizer_journal` MODIFY COLUMN `id` INTEGER AUTO_INCREMENT;

UPDATE organizer_journal set rid = ( SELECT @row := @row + 1 from (SELECT @row := 737) as tt ) where rid=-1