2
Copyright (c) 2000, 2012, Oracle and/or its affiliates.
4
This program is free software; you can redistribute it and/or modify
5
it under the terms of the GNU General Public License as published by
6
the Free Software Foundation; version 2 of the License.
8
This program is distributed in the hope that it will be useful,
9
but WITHOUT ANY WARRANTY; without even the implied warranty of
10
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
11
GNU General Public License for more details.
13
You should have received a copy of the GNU General Public License
14
along with this program; if not, write to the Free Software
15
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
19
** mysqlimport.c - Imports all given files
22
** *************************
24
** * AUTHOR: Monty & Jani *
25
** * DATE: June 24, 1997 *
27
** *************************
29
#define IMPORT_VERSION "3.7"
31
#include "client_priv.h"
32
#include "mysql_version.h"
33
#ifdef HAVE_LIBPTHREAD
34
#include <my_pthread.h>
37
#include <welcome_copyright_notice.h> /* ORACLE_WELCOME_COPYRIGHT_NOTICE */
40
/* Global Thread counter */
42
#ifdef HAVE_LIBPTHREAD
43
pthread_mutex_t counter_mutex;
44
pthread_cond_t count_threshhold;
47
static void db_error_with_table(MYSQL *mysql, char *table);
48
static void db_error(MYSQL *mysql);
49
static char *field_escape(char *to,const char *from,uint length);
50
static char *add_load_option(char *ptr,const char *object,
51
const char *statement);
53
static my_bool verbose=0,lock_tables=0,ignore_errors=0,opt_delete=0,
54
replace=0,silent=0,ignore=0,opt_compress=0,
55
opt_low_priority= 0, tty_password= 0;
56
static my_bool debug_info_flag= 0, debug_check_flag= 0;
57
static uint opt_use_threads=0, opt_local_file=0, my_end_arg= 0;
58
static char *opt_password=0, *current_user=0,
59
*current_host=0, *current_db=0, *fields_terminated=0,
60
*lines_terminated=0, *enclosed=0, *opt_enclosed=0,
61
*escaped=0, *opt_columns=0,
62
*default_charset= (char*) MYSQL_AUTODETECT_CHARSET_NAME;
63
static uint opt_mysql_port= 0, opt_protocol= 0;
64
static char * opt_mysql_unix_port=0;
65
static char *opt_plugin_dir= 0, *opt_default_auth= 0;
66
static longlong opt_ignore_lines= -1;
67
#include <sslopt-vars.h>
69
static char **argv_to_free;
72
static char *shared_memory_base_name=0;
75
static struct my_option my_long_options[] =
77
{"character-sets-dir", OPT_CHARSETS_DIR,
78
"Directory for character set files.", (char**) &charsets_dir,
79
(char**) &charsets_dir, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
80
{"default-character-set", OPT_DEFAULT_CHARSET,
81
"Set the default character set.", &default_charset,
82
&default_charset, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
84
"Use only these columns to import the data to. Give the column names in a comma separated list. This is same as giving columns to LOAD DATA INFILE.",
85
&opt_columns, &opt_columns, 0, GET_STR, REQUIRED_ARG, 0, 0, 0,
87
{"compress", 'C', "Use compression in server/client protocol.",
88
&opt_compress, &opt_compress, 0, GET_BOOL, NO_ARG, 0, 0, 0,
90
{"debug",'#', "Output debug log. Often this is 'd:t:o,filename'.", 0, 0, 0,
91
GET_STR, OPT_ARG, 0, 0, 0, 0, 0, 0},
92
{"debug-check", OPT_DEBUG_CHECK, "Check memory and open file usage at exit.",
93
&debug_check_flag, &debug_check_flag, 0,
94
GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
95
{"debug-info", OPT_DEBUG_INFO, "Print some debug info at exit.",
96
&debug_info_flag, &debug_info_flag,
97
0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
98
{"default_auth", OPT_DEFAULT_AUTH,
99
"Default authentication client-side plugin to use.",
100
&opt_default_auth, &opt_default_auth, 0,
101
GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
102
{"delete", 'd', "First delete all rows from table.", &opt_delete,
103
&opt_delete, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
104
{"fields-terminated-by", OPT_FTB,
105
"Fields in the input file are terminated by the given string.",
106
&fields_terminated, &fields_terminated, 0,
107
GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
108
{"fields-enclosed-by", OPT_ENC,
109
"Fields in the import file are enclosed by the given character.",
110
&enclosed, &enclosed, 0,
111
GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
112
{"fields-optionally-enclosed-by", OPT_O_ENC,
113
"Fields in the input file are optionally enclosed by the given character.",
114
&opt_enclosed, &opt_enclosed, 0,
115
GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
116
{"fields-escaped-by", OPT_ESC,
117
"Fields in the input file are escaped by the given character.",
118
&escaped, &escaped, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0,
120
{"force", 'f', "Continue even if we get an SQL error.",
121
&ignore_errors, &ignore_errors, 0, GET_BOOL, NO_ARG, 0, 0,
123
{"help", '?', "Displays this help and exits.", 0, 0, 0, GET_NO_ARG, NO_ARG,
125
{"host", 'h', "Connect to host.", ¤t_host,
126
¤t_host, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
127
{"ignore", 'i', "If duplicate unique key was found, keep old row.",
128
&ignore, &ignore, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
129
{"ignore-lines", OPT_IGN_LINES, "Ignore first n lines of data infile.",
130
&opt_ignore_lines, &opt_ignore_lines, 0, GET_LL,
131
REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
132
{"lines-terminated-by", OPT_LTB,
133
"Lines in the input file are terminated by the given string.",
134
&lines_terminated, &lines_terminated, 0, GET_STR,
135
REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
136
{"local", 'L', "Read all files through the client.", &opt_local_file,
137
&opt_local_file, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
138
{"lock-tables", 'l', "Lock all tables for write (this disables threads).",
139
&lock_tables, &lock_tables, 0, GET_BOOL, NO_ARG,
141
{"low-priority", OPT_LOW_PRIORITY,
142
"Use LOW_PRIORITY when updating the table.", &opt_low_priority,
143
&opt_low_priority, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
145
"Password to use when connecting to server. If password is not given it's asked from the tty.",
146
0, 0, 0, GET_STR, OPT_ARG, 0, 0, 0, 0, 0, 0},
148
{"pipe", 'W', "Use named pipes to connect to server.", 0, 0, 0, GET_NO_ARG,
149
NO_ARG, 0, 0, 0, 0, 0, 0},
151
{"plugin_dir", OPT_PLUGIN_DIR, "Directory for client-side plugins.",
152
&opt_plugin_dir, &opt_plugin_dir, 0,
153
GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
154
{"port", 'P', "Port number to use for connection or 0 for default to, in "
155
"order of preference, my.cnf, $MYSQL_TCP_PORT, "
156
#if MYSQL_PORT_DEFAULT == 0
159
"built-in default (" STRINGIFY_ARG(MYSQL_PORT) ").",
161
&opt_mysql_port, 0, GET_UINT, REQUIRED_ARG, 0, 0, 0, 0, 0,
163
{"protocol", OPT_MYSQL_PROTOCOL, "The protocol to use for connection (tcp, socket, pipe, memory).",
164
0, 0, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
165
{"replace", 'r', "If duplicate unique key was found, replace old row.",
166
&replace, &replace, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
168
{"shared-memory-base-name", OPT_SHARED_MEMORY_BASE_NAME,
169
"Base name of shared memory.", &shared_memory_base_name, &shared_memory_base_name,
170
0, GET_STR_ALLOC, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
172
{"silent", 's', "Be more silent.", &silent, &silent, 0,
173
GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
174
{"socket", 'S', "The socket file to use for connection.",
175
&opt_mysql_unix_port, &opt_mysql_unix_port, 0, GET_STR,
176
REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
177
#include <sslopt-longopts.h>
178
{"use-threads", OPT_USE_THREADS,
179
"Load files in parallel. The argument is the number "
180
"of threads to use for loading data.",
181
&opt_use_threads, &opt_use_threads, 0,
182
GET_UINT, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
183
#ifndef DONT_ALLOW_USER_CHANGE
184
{"user", 'u', "User for login if not current user.", ¤t_user,
185
¤t_user, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
187
{"verbose", 'v', "Print info about the various stages.", &verbose,
188
&verbose, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
189
{"version", 'V', "Output version information and exit.", 0, 0, 0, GET_NO_ARG,
190
NO_ARG, 0, 0, 0, 0, 0, 0},
191
{ 0, 0, 0, 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0}
195
static const char *load_default_groups[]=
196
{ "mysqlimport","client", "client-server", "client-mariadb", 0 };
199
static void print_version(void)
201
printf("%s Ver %s Distrib %s, for %s (%s)\n" ,my_progname,
202
IMPORT_VERSION, MYSQL_SERVER_VERSION,SYSTEM_TYPE,MACHINE_TYPE);
206
static void usage(void)
208
puts("Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.");
209
puts("Copyright 2008-2011 Oracle and Monty Program Ab.");
211
puts(ORACLE_WELCOME_COPYRIGHT_NOTICE("2000"));
213
Loads tables from text files in various formats. The base name of the\n\
214
text file must be the name of the table that should be used.\n\
215
If one uses sockets to connect to the MySQL server, the server will open and\n\
216
read the text file directly. In other cases the client will open the text\n\
217
file. The SQL command 'LOAD DATA INFILE' is used to import the rows.\n");
219
printf("\nUsage: %s [OPTIONS] database textfile...",my_progname);
220
print_defaults("my",load_default_groups);
221
my_print_help(my_long_options);
222
my_print_variables(my_long_options);
227
get_one_option(int optid, const struct my_option *opt __attribute__((unused)),
232
if (argument == disabled_my_option)
233
argument= (char*) ""; /* Don't require password */
236
char *start=argument;
237
my_free(opt_password);
238
opt_password=my_strdup(argument,MYF(MY_FAE));
239
while (*argument) *argument++= 'x'; /* Destroy argument */
241
start[1]=0; /* Cut length of argument */
249
opt_protocol = MYSQL_PROTOCOL_PIPE;
253
case OPT_MYSQL_PROTOCOL:
254
opt_protocol= find_type_or_exit(argument, &sql_protocol_typelib,
258
DBUG_PUSH(argument ? argument : "d:t:o");
261
#include <sslopt-case.h>
262
case 'V': print_version(); exit(0);
272
static int get_options(int *argc, char ***argv)
276
if ((ho_error=handle_options(argc, argv, my_long_options, get_one_option)))
279
my_end_arg= MY_CHECK_ERROR | MY_GIVE_INFO;
280
if (debug_check_flag)
281
my_end_arg= MY_CHECK_ERROR;
283
if (enclosed && opt_enclosed)
285
fprintf(stderr, "You can't use ..enclosed.. and ..optionally-enclosed.. at the same time.\n");
288
if (replace && ignore)
290
fprintf(stderr, "You can't use --ignore (-i) and --replace (-r) at the same time.\n");
298
current_db= *((*argv)++);
301
opt_password=get_tty_password(NullS);
307
static int write_to_table(char *filename, MYSQL *mysql)
309
char tablename[FN_REFLEN], hard_path[FN_REFLEN],
310
escaped_name[FN_REFLEN * 2 + 1],
311
sql_statement[FN_REFLEN*16+256], *end, *pos;
312
DBUG_ENTER("write_to_table");
313
DBUG_PRINT("enter",("filename: %s",filename));
315
fn_format(tablename, filename, "", "", 1 | 2); /* removes path & ext. */
317
strmov(hard_path,filename);
319
my_load_path(hard_path, filename, NULL); /* filename includes the path */
324
fprintf(stdout, "Deleting the old data from table %s\n", tablename);
326
snprintf(sql_statement, FN_REFLEN*16+256, "DELETE FROM %s", tablename);
328
sprintf(sql_statement, "DELETE FROM %s", tablename);
330
if (mysql_query(mysql, sql_statement))
332
db_error_with_table(mysql, tablename);
336
to_unix_path(hard_path);
340
fprintf(stdout, "Loading data from LOCAL file: %s into %s\n",
341
hard_path, tablename);
343
fprintf(stdout, "Loading data from SERVER file: %s into %s\n",
344
hard_path, tablename);
346
mysql_real_escape_string(mysql, escaped_name, hard_path,
347
(unsigned long) strlen(hard_path));
348
sprintf(sql_statement, "LOAD DATA %s %s INFILE '%s'",
349
opt_low_priority ? "LOW_PRIORITY" : "",
350
opt_local_file ? "LOCAL" : "", escaped_name);
351
end= strend(sql_statement);
353
end= strmov(end, " REPLACE");
355
end= strmov(end, " IGNORE");
356
end= strmov(end, " INTO TABLE `");
357
/* Turn any ` into `` in table name. */
358
for (pos= tablename; *pos; pos++)
364
end= strmov(end, "`");
366
if (fields_terminated || enclosed || opt_enclosed || escaped)
367
end= strmov(end, " FIELDS");
368
end= add_load_option(end, fields_terminated, " TERMINATED BY");
369
end= add_load_option(end, enclosed, " ENCLOSED BY");
370
end= add_load_option(end, opt_enclosed,
371
" OPTIONALLY ENCLOSED BY");
372
end= add_load_option(end, escaped, " ESCAPED BY");
373
end= add_load_option(end, lines_terminated, " LINES TERMINATED BY");
374
if (opt_ignore_lines >= 0)
375
end= strmov(longlong10_to_str(opt_ignore_lines,
376
strmov(end, " IGNORE "),10), " LINES");
378
end= strmov(strmov(strmov(end, " ("), opt_columns), ")");
381
if (mysql_query(mysql, sql_statement))
383
db_error_with_table(mysql, tablename);
388
if (mysql_info(mysql)) /* If NULL-pointer, print nothing */
390
fprintf(stdout, "%s.%s: %s\n", current_db, tablename,
399
static void lock_table(MYSQL *mysql, int tablecount, char **raw_tablename)
401
DYNAMIC_STRING query;
403
char tablename[FN_REFLEN];
406
fprintf(stdout, "Locking tables for write\n");
407
init_dynamic_string(&query, "LOCK TABLES ", 256, 1024);
408
for (i=0 ; i < tablecount ; i++)
410
fn_format(tablename, raw_tablename[i], "", "", 1 | 2);
411
dynstr_append(&query, tablename);
412
dynstr_append(&query, " WRITE,");
414
if (mysql_real_query(mysql, query.str, query.length-1))
415
db_error(mysql); /* We shall countinue here, if --force was given */
421
static MYSQL *db_connect(char *host, char *database,
422
char *user, char *passwd)
426
fprintf(stdout, "Connecting to %s\n", host ? host : "localhost");
427
if (!(mysql= mysql_init(NULL)))
430
mysql_options(mysql,MYSQL_OPT_COMPRESS,NullS);
432
mysql_options(mysql,MYSQL_OPT_LOCAL_INFILE,
433
(char*) &opt_local_file);
436
mysql_ssl_set(mysql, opt_ssl_key, opt_ssl_cert, opt_ssl_ca,
437
opt_ssl_capath, opt_ssl_cipher);
438
mysql_options(mysql,MYSQL_OPT_SSL_VERIFY_SERVER_CERT,
439
(char*)&opt_ssl_verify_server_cert);
442
mysql_options(mysql,MYSQL_OPT_PROTOCOL,(char*)&opt_protocol);
444
if (shared_memory_base_name)
445
mysql_options(mysql,MYSQL_SHARED_MEMORY_BASE_NAME,shared_memory_base_name);
448
if (opt_plugin_dir && *opt_plugin_dir)
449
mysql_options(mysql, MYSQL_PLUGIN_DIR, opt_plugin_dir);
451
if (opt_default_auth && *opt_default_auth)
452
mysql_options(mysql, MYSQL_DEFAULT_AUTH, opt_default_auth);
454
mysql_options(mysql, MYSQL_SET_CHARSET_NAME, default_charset);
455
if (!(mysql_real_connect(mysql,host,user,passwd,
456
database,opt_mysql_port,opt_mysql_unix_port,
459
ignore_errors=0; /* NO RETURN FROM db_error */
464
fprintf(stdout, "Selecting database %s\n", database);
465
if (mysql_select_db(mysql, database))
475
static void db_disconnect(char *host, MYSQL *mysql)
478
fprintf(stdout, "Disconnecting from %s\n", host ? host : "localhost");
484
static void safe_exit(int error, MYSQL *mysql)
486
if (error && ignore_errors)
492
my_free(shared_memory_base_name);
494
free_defaults(argv_to_free);
496
my_free(opt_password);
503
static void db_error_with_table(MYSQL *mysql, char *table)
505
my_printf_error(0,"Error: %d, %s, when using table: %s",
506
MYF(0), mysql_errno(mysql), mysql_error(mysql), table);
512
static void db_error(MYSQL *mysql)
514
my_printf_error(0,"Error: %d %s", MYF(0), mysql_errno(mysql), mysql_error(mysql));
519
static char *add_load_option(char *ptr, const char *object,
520
const char *statement)
524
/* Don't escape hex constants */
525
if (object[0] == '0' && (object[1] == 'x' || object[1] == 'X'))
526
ptr= strxmov(ptr," ",statement," ",object,NullS);
529
/* char constant; escape */
530
ptr= strxmov(ptr," ",statement," '",NullS);
531
ptr= field_escape(ptr,object,(uint) strlen(object));
539
** Allow the user to specify field terminator strings like:
540
** "'", "\", "\\" (escaped backslash), "\t" (tab), "\n" (newline)
541
** This is done by doubleing ' and add a end -\ if needed to avoid
542
** syntax errors from the SQL parser.
545
static char *field_escape(char *to,const char *from,uint length)
548
uint end_backslashes=0;
550
for (end= from+length; from != end; from++)
554
end_backslashes^=1; /* find odd number of backslashes */
557
if (*from == '\'' && !end_backslashes)
558
*to++= *from; /* We want a dublicate of "'" for MySQL */
562
/* Add missing backslashes if user has specified odd number of backs.*/
570
#ifdef HAVE_LIBPTHREAD
571
pthread_handler_t worker_thread(void *arg)
574
char *raw_table_name= (char *)arg;
577
if (mysql_thread_init())
580
if (!(mysql= db_connect(current_host,current_db,current_user,opt_password)))
585
if (mysql_query(mysql, "/*!40101 set @@character_set_database=binary */;"))
587
db_error(mysql); /* We shall countinue here, if --force was given */
592
We are not currently catching the error here.
594
if((error= write_to_table(raw_table_name, mysql)))
600
db_disconnect(current_host, mysql);
602
pthread_mutex_lock(&counter_mutex);
604
pthread_cond_signal(&count_threshhold);
605
pthread_mutex_unlock(&counter_mutex);
613
int main(int argc, char **argv)
617
sf_leaking_memory=1; /* don't report memory leaks on early exits */
619
if (load_defaults("my",load_default_groups,&argc,&argv))
621
/* argv is changed in the program */
623
if (get_options(&argc, &argv))
625
free_defaults(argv_to_free);
628
sf_leaking_memory=0; /* from now on we cleanup properly */
630
#ifdef HAVE_LIBPTHREAD
631
if (opt_use_threads && !lock_tables)
633
pthread_t mainthread; /* Thread descriptor */
634
pthread_attr_t attr; /* Thread attributes */
635
pthread_attr_init(&attr);
636
pthread_attr_setdetachstate(&attr,
637
PTHREAD_CREATE_DETACHED);
639
pthread_mutex_init(&counter_mutex, NULL);
640
pthread_cond_init(&count_threshhold, NULL);
642
for (counter= 0; *argv != NULL; argv++) /* Loop through tables */
644
pthread_mutex_lock(&counter_mutex);
645
while (counter == opt_use_threads)
647
struct timespec abstime;
649
set_timespec(abstime, 3);
650
pthread_cond_timedwait(&count_threshhold, &counter_mutex, &abstime);
652
/* Before exiting the lock we set ourselves up for the next thread */
654
pthread_mutex_unlock(&counter_mutex);
655
/* now create the thread */
656
if (pthread_create(&mainthread, &attr, worker_thread,
659
pthread_mutex_lock(&counter_mutex);
661
pthread_mutex_unlock(&counter_mutex);
662
fprintf(stderr,"%s: Could not create thread\n",
668
We loop until we know that all children have cleaned up.
670
pthread_mutex_lock(&counter_mutex);
673
struct timespec abstime;
675
set_timespec(abstime, 3);
676
pthread_cond_timedwait(&count_threshhold, &counter_mutex, &abstime);
678
pthread_mutex_unlock(&counter_mutex);
679
pthread_mutex_destroy(&counter_mutex);
680
pthread_cond_destroy(&count_threshhold);
681
pthread_attr_destroy(&attr);
687
if (!(mysql= db_connect(current_host,current_db,current_user,opt_password)))
689
free_defaults(argv_to_free);
690
return(1); /* purecov: deadcode */
693
if (mysql_query(mysql, "/*!40101 set @@character_set_database=binary */;"))
695
db_error(mysql); /* We shall countinue here, if --force was given */
700
lock_table(mysql, argc, argv);
701
for (; *argv != NULL; argv++)
702
if ((error= write_to_table(*argv, mysql)))
705
db_disconnect(current_host, mysql);