1
/* Copyright (C) 2000-2006 MySQL AB
3
This program is free software; you can redistribute it and/or modify
4
it under the terms of the GNU General Public License as published by
5
the Free Software Foundation; version 2 of the License.
7
This program is distributed in the hope that it will be useful,
8
but WITHOUT ANY WARRANTY; without even the implied warranty of
9
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
10
GNU General Public License for more details.
12
You should have received a copy of the GNU General Public License
13
along with this program; if not, write to the Free Software
14
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
17
** mysqlimport.c - Imports all given files
20
** *************************
22
** * AUTHOR: Monty & Jani *
23
** * DATE: June 24, 1997 *
25
** *************************
27
#define IMPORT_VERSION "3.7"
29
#include "client_priv.h"
30
#include "mysql_version.h"
31
#ifdef HAVE_LIBPTHREAD
32
#include <my_pthread.h>
36
/* Global Thread counter */
38
#ifdef HAVE_LIBPTHREAD
39
pthread_mutex_t counter_mutex;
40
pthread_cond_t count_threshhold;
43
static void db_error_with_table(MYSQL *mysql, char *table);
44
static void db_error(MYSQL *mysql);
45
static char *field_escape(char *to,const char *from,uint length);
46
static char *add_load_option(char *ptr,const char *object,
47
const char *statement);
49
static my_bool verbose=0,lock_tables=0,ignore_errors=0,opt_delete=0,
50
replace=0,silent=0,ignore=0,opt_compress=0,
51
opt_low_priority= 0, tty_password= 0;
52
static my_bool debug_info_flag= 0, debug_check_flag= 0;
53
static uint opt_use_threads=0, opt_local_file=0, my_end_arg= 0;
54
static char *opt_password=0, *current_user=0,
55
*current_host=0, *current_db=0, *fields_terminated=0,
56
*lines_terminated=0, *enclosed=0, *opt_enclosed=0,
57
*escaped=0, *opt_columns=0,
58
*default_charset= (char*) MYSQL_DEFAULT_CHARSET_NAME;
59
static uint opt_mysql_port= 0, opt_protocol= 0;
60
static char * opt_mysql_unix_port=0;
61
static longlong opt_ignore_lines= -1;
62
static CHARSET_INFO *charset_info= &my_charset_latin1;
65
static char *shared_memory_base_name=0;
68
static struct my_option my_long_options[] =
71
{"autoclose", OPT_AUTO_CLOSE, "Auto close the screen on exit for Netware.",
72
0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0},
74
{"character-sets-dir", OPT_CHARSETS_DIR,
75
"Directory where character sets are.", (uchar**) &charsets_dir,
76
(uchar**) &charsets_dir, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
77
{"default-character-set", OPT_DEFAULT_CHARSET,
78
"Set the default character set.", (uchar**) &default_charset,
79
(uchar**) &default_charset, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
81
"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.",
82
(uchar**) &opt_columns, (uchar**) &opt_columns, 0, GET_STR, REQUIRED_ARG, 0, 0, 0,
84
{"compress", 'C', "Use compression in server/client protocol.",
85
(uchar**) &opt_compress, (uchar**) &opt_compress, 0, GET_BOOL, NO_ARG, 0, 0, 0,
87
{"debug",'#', "Output debug log. Often this is 'd:t:o,filename'.", 0, 0, 0,
88
GET_STR, OPT_ARG, 0, 0, 0, 0, 0, 0},
89
{"debug-check", OPT_DEBUG_CHECK, "Check memory and open file usage at exit.",
90
(uchar**) &debug_check_flag, (uchar**) &debug_check_flag, 0,
91
GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
92
{"debug-info", OPT_DEBUG_INFO, "Print some debug info at exit.",
93
(uchar**) &debug_info_flag, (uchar**) &debug_info_flag,
94
0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
95
{"delete", 'd', "First delete all rows from table.", (uchar**) &opt_delete,
96
(uchar**) &opt_delete, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
97
{"fields-terminated-by", OPT_FTB,
98
"Fields in the textfile are terminated by ...", (uchar**) &fields_terminated,
99
(uchar**) &fields_terminated, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
100
{"fields-enclosed-by", OPT_ENC,
101
"Fields in the importfile are enclosed by ...", (uchar**) &enclosed,
102
(uchar**) &enclosed, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
103
{"fields-optionally-enclosed-by", OPT_O_ENC,
104
"Fields in the i.file are opt. enclosed by ...", (uchar**) &opt_enclosed,
105
(uchar**) &opt_enclosed, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
106
{"fields-escaped-by", OPT_ESC, "Fields in the i.file are escaped by ...",
107
(uchar**) &escaped, (uchar**) &escaped, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0,
109
{"force", 'f', "Continue even if we get an sql-error.",
110
(uchar**) &ignore_errors, (uchar**) &ignore_errors, 0, GET_BOOL, NO_ARG, 0, 0,
112
{"help", '?', "Displays this help and exits.", 0, 0, 0, GET_NO_ARG, NO_ARG,
114
{"host", 'h', "Connect to host.", (uchar**) ¤t_host,
115
(uchar**) ¤t_host, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
116
{"ignore", 'i', "If duplicate unique key was found, keep old row.",
117
(uchar**) &ignore, (uchar**) &ignore, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
118
{"ignore-lines", OPT_IGN_LINES, "Ignore first n lines of data infile.",
119
(uchar**) &opt_ignore_lines, (uchar**) &opt_ignore_lines, 0, GET_LL,
120
REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
121
{"lines-terminated-by", OPT_LTB, "Lines in the i.file are terminated by ...",
122
(uchar**) &lines_terminated, (uchar**) &lines_terminated, 0, GET_STR,
123
REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
124
{"local", 'L', "Read all files through the client.", (uchar**) &opt_local_file,
125
(uchar**) &opt_local_file, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
126
{"lock-tables", 'l', "Lock all tables for write (this disables threads).",
127
(uchar**) &lock_tables, (uchar**) &lock_tables, 0, GET_BOOL, NO_ARG,
129
{"low-priority", OPT_LOW_PRIORITY,
130
"Use LOW_PRIORITY when updating the table.", (uchar**) &opt_low_priority,
131
(uchar**) &opt_low_priority, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
133
"Password to use when connecting to server. If password is not given it's asked from the tty.",
134
0, 0, 0, GET_STR, OPT_ARG, 0, 0, 0, 0, 0, 0},
136
{"pipe", 'W', "Use named pipes to connect to server.", 0, 0, 0, GET_NO_ARG,
137
NO_ARG, 0, 0, 0, 0, 0, 0},
139
{"port", 'P', "Port number to use for connection or 0 for default to, in "
140
"order of preference, my.cnf, $MYSQL_TCP_PORT, "
141
#if MYSQL_PORT_DEFAULT == 0
144
"built-in default (" STRINGIFY_ARG(MYSQL_PORT) ").",
145
(uchar**) &opt_mysql_port,
146
(uchar**) &opt_mysql_port, 0, GET_UINT, REQUIRED_ARG, 0, 0, 0, 0, 0,
148
{"protocol", OPT_MYSQL_PROTOCOL, "The protocol of connection (tcp,socket,pipe,memory).",
149
0, 0, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
150
{"replace", 'r', "If duplicate unique key was found, replace old row.",
151
(uchar**) &replace, (uchar**) &replace, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
153
{"shared-memory-base-name", OPT_SHARED_MEMORY_BASE_NAME,
154
"Base name of shared memory.", (uchar**) &shared_memory_base_name, (uchar**) &shared_memory_base_name,
155
0, GET_STR_ALLOC, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
157
{"silent", 's', "Be more silent.", (uchar**) &silent, (uchar**) &silent, 0,
158
GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
159
{"socket", 'S', "Socket file to use for connection.",
160
(uchar**) &opt_mysql_unix_port, (uchar**) &opt_mysql_unix_port, 0, GET_STR,
161
REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
162
{"use-threads", OPT_USE_THREADS,
163
"Load files in parallel. The argument is the number "
164
"of threads to use for loading data.",
165
(uchar**) &opt_use_threads, (uchar**) &opt_use_threads, 0,
166
GET_UINT, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
167
#ifndef DONT_ALLOW_USER_CHANGE
168
{"user", 'u', "User for login if not current user.", (uchar**) ¤t_user,
169
(uchar**) ¤t_user, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
171
{"verbose", 'v', "Print info about the various stages.", (uchar**) &verbose,
172
(uchar**) &verbose, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
173
{"version", 'V', "Output version information and exit.", 0, 0, 0, GET_NO_ARG,
174
NO_ARG, 0, 0, 0, 0, 0, 0},
175
{ 0, 0, 0, 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0}
179
static const char *load_default_groups[]= { "mysqlimport","client",0 };
181
#include <help_start.h>
183
static void print_version(void)
185
printf("%s Ver %s Distrib %s, for %s (%s)\n" ,my_progname,
186
IMPORT_VERSION, MYSQL_SERVER_VERSION,SYSTEM_TYPE,MACHINE_TYPE);
187
NETWARE_SET_SCREEN_MODE(1);
191
static void usage(void)
194
puts("Copyright (C) 2000-2006 MySQL AB");
195
puts("This software comes with ABSOLUTELY NO WARRANTY. This is free software,\nand you are welcome to modify and redistribute it under the GPL license\n");
197
Loads tables from text files in various formats. The base name of the\n\
198
text file must be the name of the table that should be used.\n\
199
If one uses sockets to connect to the MySQL server, the server will open and\n\
200
read the text file directly. In other cases the client will open the text\n\
201
file. The SQL command 'LOAD DATA INFILE' is used to import the rows.\n");
203
printf("\nUsage: %s [OPTIONS] database textfile...",my_progname);
204
print_defaults("my",load_default_groups);
205
my_print_help(my_long_options);
206
my_print_variables(my_long_options);
209
#include <help_end.h>
212
get_one_option(int optid, const struct my_option *opt __attribute__((unused)),
218
setscreenmode(SCR_AUTOCLOSE_ON_EXIT);
224
char *start=argument;
225
my_free(opt_password,MYF(MY_ALLOW_ZERO_PTR));
226
opt_password=my_strdup(argument,MYF(MY_FAE));
227
while (*argument) *argument++= 'x'; /* Destroy argument */
229
start[1]=0; /* Cut length of argument */
237
opt_protocol = MYSQL_PROTOCOL_PIPE;
241
case OPT_MYSQL_PROTOCOL:
242
opt_protocol= find_type_or_exit(argument, &sql_protocol_typelib,
246
DBUG_PUSH(argument ? argument : "d:t:o");
249
case 'V': print_version(); exit(0);
259
static int get_options(int *argc, char ***argv)
263
if ((ho_error=handle_options(argc, argv, my_long_options, get_one_option)))
266
my_end_arg= MY_CHECK_ERROR | MY_GIVE_INFO;
267
if (debug_check_flag)
268
my_end_arg= MY_CHECK_ERROR;
270
if (enclosed && opt_enclosed)
272
fprintf(stderr, "You can't use ..enclosed.. and ..optionally-enclosed.. at the same time.\n");
275
if (replace && ignore)
277
fprintf(stderr, "You can't use --ignore (-i) and --replace (-r) at the same time.\n");
280
if (strcmp(default_charset, charset_info->csname) &&
281
!(charset_info= get_charset_by_csname(default_charset,
282
MY_CS_PRIMARY, MYF(MY_WME))))
289
current_db= *((*argv)++);
292
opt_password=get_tty_password(NullS);
298
static int write_to_table(char *filename, MYSQL *mysql)
300
char tablename[FN_REFLEN], hard_path[FN_REFLEN],
301
sql_statement[FN_REFLEN*16+256], *end;
302
DBUG_ENTER("write_to_table");
303
DBUG_PRINT("enter",("filename: %s",filename));
305
fn_format(tablename, filename, "", "", 1 | 2); /* removes path & ext. */
307
strmov(hard_path,filename);
309
my_load_path(hard_path, filename, NULL); /* filename includes the path */
314
fprintf(stdout, "Deleting the old data from table %s\n", tablename);
316
snprintf(sql_statement, FN_REFLEN*16+256, "DELETE FROM %s", tablename);
318
sprintf(sql_statement, "DELETE FROM %s", tablename);
320
if (mysql_query(mysql, sql_statement))
322
db_error_with_table(mysql, tablename);
326
to_unix_path(hard_path);
330
fprintf(stdout, "Loading data from LOCAL file: %s into %s\n",
331
hard_path, tablename);
333
fprintf(stdout, "Loading data from SERVER file: %s into %s\n",
334
hard_path, tablename);
336
sprintf(sql_statement, "LOAD DATA %s %s INFILE '%s'",
337
opt_low_priority ? "LOW_PRIORITY" : "",
338
opt_local_file ? "LOCAL" : "", hard_path);
339
end= strend(sql_statement);
341
end= strmov(end, " REPLACE");
343
end= strmov(end, " IGNORE");
344
end= strmov(strmov(end, " INTO TABLE "), tablename);
346
if (fields_terminated || enclosed || opt_enclosed || escaped)
347
end= strmov(end, " FIELDS");
348
end= add_load_option(end, fields_terminated, " TERMINATED BY");
349
end= add_load_option(end, enclosed, " ENCLOSED BY");
350
end= add_load_option(end, opt_enclosed,
351
" OPTIONALLY ENCLOSED BY");
352
end= add_load_option(end, escaped, " ESCAPED BY");
353
end= add_load_option(end, lines_terminated, " LINES TERMINATED BY");
354
if (opt_ignore_lines >= 0)
355
end= strmov(longlong10_to_str(opt_ignore_lines,
356
strmov(end, " IGNORE "),10), " LINES");
358
end= strmov(strmov(strmov(end, " ("), opt_columns), ")");
361
if (mysql_query(mysql, sql_statement))
363
db_error_with_table(mysql, tablename);
368
if (mysql_info(mysql)) /* If NULL-pointer, print nothing */
370
fprintf(stdout, "%s.%s: %s\n", current_db, tablename,
379
static void lock_table(MYSQL *mysql, int tablecount, char **raw_tablename)
381
DYNAMIC_STRING query;
383
char tablename[FN_REFLEN];
386
fprintf(stdout, "Locking tables for write\n");
387
init_dynamic_string(&query, "LOCK TABLES ", 256, 1024);
388
for (i=0 ; i < tablecount ; i++)
390
fn_format(tablename, raw_tablename[i], "", "", 1 | 2);
391
dynstr_append(&query, tablename);
392
dynstr_append(&query, " WRITE,");
394
if (mysql_real_query(mysql, query.str, query.length-1))
395
db_error(mysql); /* We shall countinue here, if --force was given */
401
static MYSQL *db_connect(char *host, char *database,
402
char *user, char *passwd)
406
fprintf(stdout, "Connecting to %s\n", host ? host : "localhost");
407
if (!(mysql= mysql_init(NULL)))
410
mysql_options(mysql,MYSQL_OPT_COMPRESS,NullS);
412
mysql_options(mysql,MYSQL_OPT_LOCAL_INFILE,
413
(char*) &opt_local_file);
416
mysql_ssl_set(mysql, opt_ssl_key, opt_ssl_cert, opt_ssl_ca,
417
opt_ssl_capath, opt_ssl_cipher);
418
mysql_options(mysql,MYSQL_OPT_SSL_VERIFY_SERVER_CERT,
419
(char*)&opt_ssl_verify_server_cert);
422
mysql_options(mysql,MYSQL_OPT_PROTOCOL,(char*)&opt_protocol);
424
if (shared_memory_base_name)
425
mysql_options(mysql,MYSQL_SHARED_MEMORY_BASE_NAME,shared_memory_base_name);
427
if (!(mysql_real_connect(mysql,host,user,passwd,
428
database,opt_mysql_port,opt_mysql_unix_port,
431
ignore_errors=0; /* NO RETURN FROM db_error */
436
fprintf(stdout, "Selecting database %s\n", database);
437
if (mysql_select_db(mysql, database))
447
static void db_disconnect(char *host, MYSQL *mysql)
450
fprintf(stdout, "Disconnecting from %s\n", host ? host : "localhost");
456
static void safe_exit(int error, MYSQL *mysql)
467
static void db_error_with_table(MYSQL *mysql, char *table)
469
my_printf_error(0,"Error: %d, %s, when using table: %s",
470
MYF(0), mysql_errno(mysql), mysql_error(mysql), table);
476
static void db_error(MYSQL *mysql)
478
my_printf_error(0,"Error: %d %s", MYF(0), mysql_errno(mysql), mysql_error(mysql));
483
static char *add_load_option(char *ptr, const char *object,
484
const char *statement)
488
/* Don't escape hex constants */
489
if (object[0] == '0' && (object[1] == 'x' || object[1] == 'X'))
490
ptr= strxmov(ptr," ",statement," ",object,NullS);
493
/* char constant; escape */
494
ptr= strxmov(ptr," ",statement," '",NullS);
495
ptr= field_escape(ptr,object,(uint) strlen(object));
503
** Allow the user to specify field terminator strings like:
504
** "'", "\", "\\" (escaped backslash), "\t" (tab), "\n" (newline)
505
** This is done by doubleing ' and add a end -\ if needed to avoid
506
** syntax errors from the SQL parser.
509
static char *field_escape(char *to,const char *from,uint length)
512
uint end_backslashes=0;
514
for (end= from+length; from != end; from++)
518
end_backslashes^=1; /* find odd number of backslashes */
521
if (*from == '\'' && !end_backslashes)
522
*to++= *from; /* We want a dublicate of "'" for MySQL */
526
/* Add missing backslashes if user has specified odd number of backs.*/
534
#ifdef HAVE_LIBPTHREAD
535
pthread_handler_t worker_thread(void *arg)
538
char *raw_table_name= (char *)arg;
541
if (mysql_thread_init())
544
if (!(mysql= db_connect(current_host,current_db,current_user,opt_password)))
549
if (mysql_query(mysql, "/*!40101 set @@character_set_database=binary */;"))
551
db_error(mysql); /* We shall countinue here, if --force was given */
556
We are not currently catching the error here.
558
if((error= write_to_table(raw_table_name, mysql)))
564
db_disconnect(current_host, mysql);
566
pthread_mutex_lock(&counter_mutex);
568
pthread_cond_signal(&count_threshhold);
569
pthread_mutex_unlock(&counter_mutex);
577
int main(int argc, char **argv)
583
load_defaults("my",load_default_groups,&argc,&argv);
584
/* argv is changed in the program */
586
if (get_options(&argc, &argv))
588
free_defaults(argv_to_free);
592
#ifdef HAVE_LIBPTHREAD
593
if (opt_use_threads && !lock_tables)
595
pthread_t mainthread; /* Thread descriptor */
596
pthread_attr_t attr; /* Thread attributes */
597
pthread_attr_init(&attr);
598
pthread_attr_setdetachstate(&attr,
599
PTHREAD_CREATE_DETACHED);
601
VOID(pthread_mutex_init(&counter_mutex, NULL));
602
VOID(pthread_cond_init(&count_threshhold, NULL));
604
for (counter= 0; *argv != NULL; argv++) /* Loop through tables */
606
pthread_mutex_lock(&counter_mutex);
607
while (counter == opt_use_threads)
609
struct timespec abstime;
611
set_timespec(abstime, 3);
612
pthread_cond_timedwait(&count_threshhold, &counter_mutex, &abstime);
614
/* Before exiting the lock we set ourselves up for the next thread */
616
pthread_mutex_unlock(&counter_mutex);
617
/* now create the thread */
618
if (pthread_create(&mainthread, &attr, worker_thread,
621
pthread_mutex_lock(&counter_mutex);
623
pthread_mutex_unlock(&counter_mutex);
624
fprintf(stderr,"%s: Could not create thread\n",
630
We loop until we know that all children have cleaned up.
632
pthread_mutex_lock(&counter_mutex);
635
struct timespec abstime;
637
set_timespec(abstime, 3);
638
pthread_cond_timedwait(&count_threshhold, &counter_mutex, &abstime);
640
pthread_mutex_unlock(&counter_mutex);
641
VOID(pthread_mutex_destroy(&counter_mutex));
642
VOID(pthread_cond_destroy(&count_threshhold));
643
pthread_attr_destroy(&attr);
649
if (!(mysql= db_connect(current_host,current_db,current_user,opt_password)))
651
free_defaults(argv_to_free);
652
return(1); /* purecov: deadcode */
655
if (mysql_query(mysql, "/*!40101 set @@character_set_database=binary */;"))
657
db_error(mysql); /* We shall countinue here, if --force was given */
662
lock_table(mysql, argc, argv);
663
for (; *argv != NULL; argv++)
664
if ((error= write_to_table(*argv, mysql)))
667
db_disconnect(current_host, mysql);
669
my_free(opt_password,MYF(MY_ALLOW_ZERO_PTR));
671
my_free(shared_memory_base_name,MYF(MY_ALLOW_ZERO_PTR));
673
free_defaults(argv_to_free);
added
removed
client/mysqlimport.c
