I have a bash script that runs mysqldump on my backup NAS (where I have MariaDB 10.3.32) to make a backup of my MariaDB 11 on my primary NAS.
From reading many articles, mysqldump should escape single quotes in a string by default, but it does not do so in my case. Can someone help me understand why and help me fix the upload? The dump seems to work fine, allbeit without escaped single quotes, but then the upload fails due to the single quotes not being escaped:
ERROR 1064 (42000) at line 153: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 's','{Blue/Yellow} ','2015-04-09 12:56:48','2015-04-09 14:16:14',NULL,'01:19:2...' at line 1
To create the backup:
mysqldump --defaults-extra-file=$SCRIPT_DIR/my.cnf --defaults-group-suffix=.nas1 $DB > $BACKUP_DIR/$DB.sql
defaults-group-suffix .nas1 contains the host, port and login details for MariaDB 11 as well as these mysqldump options:
[mysqldump]
allow-keywords
comments
databases
events
force
routines
single-transaction
verbose
To load the backup: mysql --defaults-extra-file=$SCRIPT_DIR/my.cnf --defaults-group-suffix=.nas2 --database=$DB < $BACKUP_DIR/$DB.sql
using the same my.cnf, where .nas2 contains the login details for MariaDB 10 as well as the above mysqldump options.
An example row in the output file, where the single quote isn't escaped:
('816962786',NULL,'1st Run with Kayano's','{Blue/Yellow} ','2015-04-09 12:56:48','2015-04-09 14:16:14',NULL,'01:19:25','01:19:26','01:19:25','14.39','10.88','10.8761','21.30','00:05:31','00:05:31','00:02:49','210',NULL,'61','113','0',NULL,NULL,'1252',NULL,NULL,NULL,'93','112','130','149','167','00:00:26','00:00:58','00:51:05','00:01:12','00:00:00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'+02:00','50.988255','5.879338',NULL,NULL,'242')
Which is part of the larger dataset/.sql-file/values:
-- MySQL dump 10.19 Distrib 10.3.32-MariaDB, for Linux ()
--
-- Host: 192.168.1.104 Database: garmin_connect
-- ------------------------------------------------------
-- Server version 11.0.2-MariaDB-1:11.0.2+maria~ubu2204
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `activities_gc_export`
--
DROP TABLE IF EXISTS `activities_gc_export`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `activities_gc_export` (
`id` bigint(20) NOT NULL,
`url` varchar(128) DEFAULT NULL,
`name` varchar(128) DEFAULT NULL,
`description` varchar(128) DEFAULT NULL,
`time_start` datetime DEFAULT NULL,
`time_end` datetime DEFAULT NULL,
`location_name` varchar(128) DEFAULT NULL,
`duration` time DEFAULT NULL,
`duration_elapsed` time DEFAULT NULL,
`duration_moving` time DEFAULT NULL,
`distance` float(5,2) unsigned DEFAULT NULL,
`speed_avg` float(5,2) unsigned DEFAULT NULL,
`speed_avg_moving` float unsigned DEFAULT NULL,
`speed_max` float(5,2) unsigned DEFAULT NULL,
`pace_avg` time DEFAULT NULL,
`pace_avg_moving` time DEFAULT NULL,
`pace_max` time DEFAULT NULL,
`elevation_gain` smallint(6) unsigned DEFAULT NULL,
`elevation_loss` smallint(6) unsigned DEFAULT NULL,
`elevation_min` smallint(6) DEFAULT NULL,
`elevation_max` smallint(6) DEFAULT NULL,
`elevation_corrected` tinyint(1) unsigned DEFAULT NULL,
`hr_max` tinyint(4) unsigned DEFAULT NULL,
`hr_avg` tinyint(4) unsigned DEFAULT NULL,
`calories` mediumint(8) unsigned DEFAULT NULL,
`vo2max` tinyint(4) unsigned DEFAULT NULL,
`training_effect_aerobic` float(2,1) unsigned DEFAULT NULL,
`training_effect_anaerobic` float(2,1) unsigned DEFAULT NULL,
`hr_zone_1_low` tinyint(4) unsigned DEFAULT NULL,
`hr_zone_2_low` tinyint(4) unsigned DEFAULT NULL,
`hr_zone_3_low` tinyint(4) unsigned DEFAULT NULL,
`hr_zone_4_low` tinyint(4) unsigned DEFAULT NULL,
`hr_zone_5_low` tinyint(4) unsigned DEFAULT NULL,
`hr_zone_1_time` time DEFAULT NULL,
`hr_zone_2_time` time DEFAULT NULL,
`hr_zone_3_time` time DEFAULT NULL,
`hr_zone_4_time` time DEFAULT NULL,
`hr_zone_5_time` time DEFAULT NULL,
`cadence_avg` float(5,2) unsigned DEFAULT NULL,
`cadence_max` float(5,2) unsigned DEFAULT NULL,
`stride_length` float(5,2) unsigned DEFAULT NULL,
`steps` mediumint(6) unsigned DEFAULT NULL,
`strokes` mediumint(6) unsigned DEFAULT NULL,
`temp_avg` float(3,1) DEFAULT NULL,
`temp_min` float(3,1) DEFAULT NULL,
`temp_max` float(3,1) DEFAULT NULL,
`device` varchar(64) DEFAULT NULL,
`gear` varchar(128) DEFAULT NULL,
`activity_type_key` varchar(64) DEFAULT NULL,
`activity_type` varchar(64) DEFAULT NULL,
`activity_parent` varchar(64) DEFAULT NULL,
`event_type_key` varchar(32) DEFAULT NULL,
`event_type` varchar(32) DEFAULT NULL,
`privacy` varchar(32) DEFAULT NULL,
`file_format` varchar(3) DEFAULT NULL,
`tz` varchar(32) DEFAULT NULL,
`tz_offset` varchar(6) DEFAULT NULL,
`start_lat` float(20,6) DEFAULT NULL,
`start_lon` float(20,6) DEFAULT NULL,
`end_lat` float(20,6) DEFAULT NULL,
`end_lon` float(20,6) DEFAULT NULL,
`sample_count` smallint(6) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `activities_gc_export`
--
LOCK TABLES `activities_gc_export` WRITE;
/*!40000 ALTER TABLE `activities_gc_export` DISABLE KEYS */;
INSERT INTO `activities_gc_export` VALUES ("example_above_and_many_more_rows_of_data")
Following @O. Jones's comment, I traced my .cnf files:
/usr/local/mariadb10/etc/mysql/my.cnf:
# DO NOT EDIT THIS FILE !!!
# You can change the port on user interface of MariaDB10.
# Please add other custom configuration to /var/packages/MariaDB10/etc/my.cnf
#
[client]
socket = /run/mysqld/mysqld10.sock
[mysqld]
bind-address = 0.0.0.0
socket = /run/mysqld/mysqld10.sock
pid-file = /run/mysqld/mysqld10.pid
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 240K
innodb_data_home_dir = /var/packages/MariaDB10/target/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/packages/MariaDB10/target/mysql
innodb_buffer_pool_size = 16M
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
innodb_file_per_table = 1
synology_password_check = FORCE_PLUS_PERMANENT
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
tmpdir=/var/services/tmp
key_buffer_size = 8M
sort_buffer_size = 8M
[mysqlhotcopy]
interactive-timeout
[mariadb]
plugin_load_add = synology_password_check
!include /var/packages/MariaDB10/etc/my.cnf
!include /var/packages/MariaDB10/etc/my_port.cnf
!include /var/packages/MariaDB10/etc/synology.cnf
~/.my.cnf: No such file or directory
/var/packages/MariaDB10/etc/my.cnf: No such file or directory
/var/packages/MariaDB10/etc/my_port.cnf
# DO NOT EDIT THIS FILE !!!
# You can change the port on user interface of MariaDB10.
# Please add other custom configuration to /var/packages/MariaDB10/etc/my.cnf
[mysqld]
port=3306
[client]
port=3306
/var/packages/MariaDB10/etc/synology.cnf:
# DO NOT EDIT THIS FILE !!!
# You can change the port on user interface of MariaDB10.
# Please add other custom configuration to /var/packages/MariaDB10/etc/my.cnf
[mysqld]
skip_networking=0
