another INSERT INTO overwrites existing data

Question

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      |

See the `DROP TABLE`? It could have been suppressed when creating the dump. — Rick James, Oct 17 '19 at 04:55

score 0 · Answer 1 · answered Oct 09 '20 at 12:48

it was the case that the socket file could not get connected

Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

That was the only thing which helped in the end: https://serverfault.com/a/459403/316405

Try using one of the following to see if you can connect without a socket file

mysql -u root -p -h127.0.0.1

mysql -u root -p -h127.0.0.1 --protocol=tcp

If you can connect with one of these, the shutdown mysql like this

mysqladmin -u root -p -h127.0.0.1 --protocol=tcp shutdown

another INSERT INTO overwrites existing data

1 Answers1

Linked