MySQL Forums
Forum List  »  InnoDB

table storage engine for 'angestellte' doesn't have this option
Posted by: Markus Christian
Date: November 17, 2017 03:37PM

ladies and gentlemen,

I've installed the mysql server 5.7.20.0. I want to create a database with an existing database backup file from an previous mysql-version with the following create table query:

DROP TABLE IF EXISTS `angestellte`;
CREATE TABLE `angestellte` (
`id` int(11) NOT NULL auto_increment,
`abtID` int(11) NOT NULL default '0',
`versicherungsnr` varchar(11) NOT NULL,
`gehalt` float NOT NULL default '0',
`einstellungsdatum` date NOT NULL default '0000-00-00',
`ausscheidedatum` date default '0000-00-00',
`geburtsdatum` date NOT NULL default '0000-00-00',
`geschlecht` char(1) NOT NULL,
`personID` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `abtID` (`abtID`),
KEY `FK_angestellte_2` (`personID`),
CONSTRAINT `angestellte_ibfk_1` FOREIGN KEY (`abtID`) REFERENCES `abteilungen` (`id`),
CONSTRAINT `FK_angestellte_2` FOREIGN KEY (`personID`) REFERENCES `personen` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1752 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;

The following query has to fill the created table:

/*!40000 ALTER TABLE `angestellte` DISABLE KEYS */;
INSERT INTO `angestellte` (`id`,`abtID`,`versicherungsnr`,`gehalt`,`einstellungsdatum`,`ausscheidedatum`,`geburtsdatum`,`geschlecht`,`personID`) VALUES
(1,10,'0',0,'0000-00-00','0000-00-00','0000-00-00','N',2),
(102,100,'017349033',45700,'1984-08-02','0000-00-00','1958-06-01','F',3),
(105,100,'052345739',62000,'1985-01-01','0000-00-00','1960-12-01','M',4),
(129,200,'024608923',38500,'1985-02-01','0000-00-00','1966-10-03','M',5),
(148,300,'501704733',51432,'1985-04-01','0000-00-00','1951-12-01','F',6),
(160,100,'025487623',57490,'1985-06-01','0000-00-00','1947-05-01','M',7),
(184,400,'025481943',36490,'1985-10-01','0000-00-00','1939-12-01','F',8),
(191,500,'017348821',29800,'1985-11-01','0000-00-00','1964-12-02','F',9),
(195,200,'079486634',54800,'1985-12-01','0000-00-00','1963-07-01','M',10),
(207,400,'501708992',53870,'1986-02-01','0000-00-00','1954-09-01','F',11),
(243,100,'043216799',72995,'1986-06-01','0000-00-00','1949-04-02','F',12),
(247,100,'024601768',48023.7,'1986-07-01','0000-00-00','1955-03-01','M',13),
(249,100,'084329990',42998,'1986-10-01','0000-00-00','1956-11-02','M',14),
(266,100,'017346122',59840,'1986-11-03','0000-00-00','1947-10-01','M',15),
(278,100,'087602311',48500,'1986-12-01','0000-00-00','1966-05-01','F',16),
(299,200,'025487133',39300,'1987-02-01','0000-00-00','1964-03-01','M',17),
(316,100,'048667211',74500,'1987-04-02','0000-00-00','1962-07-01','F',18),
(318,400,'079349168',41700.8,'1987-05-02','0000-00-00','1962-04-02','M',19),
(390,300,'027341657',57090,'1987-06-01','0000-00-00','1957-02-01','F',20),
(409,400,'048781192',46550,'1987-06-01','0000-00-00','1946-04-01','M',21),
(445,100,'017508821',87900,'1987-06-01','0000-00-00','1955-01-01','M',22),
(453,100,'029458129',64500,'1987-06-01','0000-00-00','1957-08-01','M',23),
(467,200,'034281032',49500,'1987-07-01','0000-00-00','1952-11-01','M',24),
(479,100,'022415639',39875.5,'1987-07-02','0000-00-00','1967-09-02','F',25),
(501,100,'064983327',96300,'1987-08-01','0000-00-00','1947-03-01','M',26),
(529,100,'501324492',67890,'1988-02-01','0000-00-00','1950-04-01','F',27),
(582,100,'038218867',37400,'1988-03-02','0000-00-00','1968-02-02','M',28),
(586,300,'031281245',42300,'1988-04-01','0000-00-00','1966-03-01','M',29),
(591,400,'056872399',45450,'1988-07-01','0000-00-00','1957-01-03','F',30),
(604,100,'023486621',68400,'1988-09-02','0000-00-00','1958-12-02','M',31),
(641,200,'038726633',54600,'1988-10-01','0000-00-00','1951-10-03','M',32),
(667,200,'042706188',39800,'1988-11-02','0000-00-00','1963-01-02','F',33),
(690,200,'087236702',46200,'1988-11-02','0000-00-00','1965-09-02','F',34),
(703,500,'012896755',55500.8,'1988-12-01','0000-00-00','1953-07-02','M',35),
(750,500,'012459381',34300,'1989-01-01','0000-00-00','1939-08-01','F',36),
(757,300,'067829311',43700,'1989-02-02','0000-00-00','1968-05-01','F',37),
(839,100,'034629123',42500,'1989-04-02','0000-00-00','1966-05-02','M',38),
(856,200,'011349786',34892,'1989-06-01','0000-00-00','1959-04-01','M',39),
(862,100,'018458291',87900,'1989-06-01','0000-00-00','1955-09-02','M',40),
(868,500,'043592831',28200,'1989-07-01','0000-00-00','1968-07-02','F',41),
(879,300,'027581035',36500,'1989-07-01','0000-00-00','1965-11-01','F',42),
(888,400,'036102935',28300,'1989-09-01','0000-00-00','1966-01-02','M',43),
(902,200,'015923467',87500,'1989-10-01','0000-00-00','1950-08-01','F',44),
(913,200,'022783569',55700,'1989-10-01','0000-00-00','1943-04-02','M',45),
(921,500,'034568789',41700,'1989-10-02','0000-00-00','1960-09-01','M',46),
(930,200,'062890293',46890,'1989-11-01','0000-00-00','1962-06-01','F',47),
(949,200,'018762936',72300,'1989-11-01','0000-00-00','1955-07-02','F',48),
(958,100,'017283997',42100,'1990-01-01','0000-00-00','1969-10-01','M',49),
(992,400,'023487085',34011,'1990-02-01','0000-00-00','1960-04-01','F',50),
(1013,500,'023470756',27290,'1990-03-01','0000-00-00','1969-02-01','M',51),
(1021,200,'037846595',64900,'1990-04-02','0000-00-00','1950-02-02','M',52),
(1039,200,'046973741',33890,'1990-05-01','0000-00-00','1969-12-01','M',53),
(1062,400,'019887257',54900,'1990-05-02','0000-00-00','1953-11-01','F',54),
(1090,100,'013558813',51411,'1990-06-01','0000-00-00','1950-11-03','F',55),
(1101,200,'027663454',37803,'1990-07-01','0000-00-00','1966-09-01','M',56),
(1142,200,'055913622',45000,'1990-07-02','0000-00-00','1957-05-01','F',57),
(1157,100,'023434587',39075,'1990-07-03','0000-00-00','1970-03-01','M',58),
(1162,200,'075679377',37900,'1990-08-01','0000-00-00','1952-04-01','M',59),
(1191,400,'023749270',45900,'1990-08-01','0000-00-00','1944-11-01','M',60),
(1250,100,'023476492',54900,'1990-08-01','0000-00-00','1936-01-01','M',61),
(1293,300,'019641485',138948,'1990-09-01','0000-00-00','1955-03-01','F',62),
(1336,300,'017326112',31200,'1992-02-02','0000-00-00','1950-07-02','F',63),
(1390,300,'034786565',58930,'1992-04-01','0000-00-00','1948-04-01','F',64),
(1446,200,'038475825',32300,'1992-10-02','0000-00-00','1971-06-02','F',65),
(1483,300,'079372285',75400,'2006-03-01','0000-00-00','1939-04-02','M',66),
(1507,400,'035673688',35745,'2006-04-01','0000-00-00','1959-07-01','F',67),
(1570,500,'034576347',34576,'2006-05-02','0000-00-00','1963-04-01','M',68),
(1576,400,'017429926',68940,'2006-07-01','0000-00-00','1960-11-01','M',69),
(1596,200,'028375086',47653,'2006-08-01','0000-00-00','1959-11-01','F',70),
(1607,400,'501787474',61300,'2006-10-01','0000-00-00','1941-01-01','M',71),
(1615,500,'501175874',27500,'2006-11-01','0000-00-00','1972-09-01','F',72),
(1643,400,'023857807',29384,'2006-12-01','0000-00-00','1968-09-01','F',73),
(1658,500,'023850840',24903,'2007-02-02','0000-00-00','1973-01-01','M',74),
(1684,400,'038957036',45829,'2007-03-01','0000-00-00','1955-10-03','F',75),
(1740,400,'037534650',34889,'2007-06-02','0000-00-00','1965-06-01','M',76),
(1751,400,'032547384',34992,'2007-07-01','0000-00-00','1963-12-01','M',77);
/*!40000 ALTER TABLE `angestellte` ENABLE KEYS */;

Options: ReplyQuote


Subject
Views
Written By
Posted
table storage engine for 'angestellte' doesn't have this option
63
November 17, 2017 03:37PM


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.