why does INSERT INTO overwrites existing rows in my table instead of adding them?
i got a simple query https://www.db-fiddle.com/f/pAVBB86LCZ8qUe5Jy4ZV6s/1
here is my log from the import:
191011 17:11:16 21 Query SET NAMES 'utf8'
21 Query # ************************************************************
# Sequel Pro SQL dump
# Version 4541
#
# http://www.sequelpro.com/
# https://github.com/sequelpro/sequelpro
#
# Host: xxx (MySQL 5.6.19-67.0-log)
# Database: xxx
# Generation Time: xxx
# ************************************************************
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */
21 Query /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */
21 Query /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */
21 Query /*!40101 SET NAMES utf8 */
21 Query /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */
21 Query /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */
21 Query /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */
21 Query # Dump of table options
# ------------------------------------------------------------
DROP TABLE IF EXISTS `options`
21 Query /*!40101 SET @saved_cs_client = @@character_set_client */
21 Query /*!40101 SET character_set_client = 'utf8mb4' */
21 Query CREATE TABLE `options` (
`option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`option_name` varchar(191) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
`option_value` longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
`autoload` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'yes',
PRIMARY KEY (`option_id`),
UNIQUE KEY `option_name` (`option_name`)
) ENGINE=InnoDB AUTO_INCREMENT=69226 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
21 Query /*!40101 SET character_set_client = @saved_cs_client */
21 Query --
-- Backup data for table `options`
--
LOCK TABLES `options` WRITE
21 Query /*!40000 ALTER TABLE `options` DISABLE KEYS */
21 Query INSERT INTO `options` (`option_id`, `option_name`, `option_value`, `autoload`) VALUES
(1, 'siteurl', 'https://www.example.de', 'yes')
21 Query INSERT INTO `options` (`option_id`, `option_name`, `option_value`, `autoload`) VALUES
(2, 'home', 'https://www.example.de/home', 'yes')
21 Query /*!40000 ALTER TABLE `options` ENABLE KEYS */
21 Query UNLOCK TABLES
21 Query /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */
21 Query /*!40101 SET SQL_MODE=@OLD_SQL_MODE */
21 Query /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */
21 Query /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */
21 Query /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */
21 Query /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */
21 Query SET NAMES 'utf8mb4'
21 Query SET NAMES 'utf8'
21 Query SHOW DATABASES
21 Query SET NAMES 'utf8mb4'
21 Query SELECT DATABASE()
21 Query SET NAMES 'utf8'
21 Query SHOW /*!50002 FULL*/ TABLES
21 Query SELECT * FROM information_schema.routines WHERE routine_schema = 'test' ORDER BY routine_name
21 Query SET NAMES 'utf8mb4'
21 Query SET NAMES 'utf8'
21 Query SHOW TABLE STATUS LIKE 'options'
21 Query SHOW CREATE TABLE `options`
21 Query SET NAMES 'utf8mb4'
22 Query SHOW FULL COLUMNS FROM `options` FROM `test`
22 Query SELECT SPECIFIC_NAME, ROUTINE_TYPE, DTD_IDENTIFIER, IS_DETERMINISTIC, SQL_DATA_ACCESS, SECURITY_TYPE, DEFINER FROM `information_schema`.`ROUTINES` WHERE `ROUTINE_SCHEMA` = 'test'
21 Query SELECT * FROM `options` LIMIT 0,1000
191011 17:11:17 22 Query SHOW FULL COLUMNS FROM `options` FROM `test`
22 Query SELECT SPECIFIC_NAME, ROUTINE_TYPE, DTD_IDENTIFIER, IS_DETERMINISTIC, SQL_DATA_ACCESS, SECURITY_TYPE, DEFINER FROM `information_schema`.`ROUTINES` WHERE `ROUTINE_SCHEMA` = 'test'
here we have my my.cnf file
# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
Problem As result i just have one row with the ID 2 in my created table after an import.
| option_id | option_name | option_value | autoload |
| --------- | ----------- | --------------------------- | -------- |
| 2 | home | https://www.example.de/home | yes |