ports/64305: dbf2mysql -q option broken with mysql323-server and above
Travis Whitton
twhitton at ppb-envlabs.com
Mon Mar 15 17:40:17 UTC 2004
>Number: 64305
>Category: ports
>Synopsis: dbf2mysql -q option broken with mysql323-server and above
>Confidential: no
>Severity: non-critical
>Priority: low
>Responsible: freebsd-ports-bugs
>State: open
>Quarter:
>Keywords:
>Date-Required:
>Class: sw-bug
>Submitter-Id: current-users
>Arrival-Date: Mon Mar 15 09:40:16 PST 2004
>Closed-Date:
>Last-Modified:
>Originator: Travis Whitton
>Release: FreeBSD 4.9-RELEASE-p3 i386
>Organization:
PPB Environmental Labs
>Environment:
System: FreeBSD aop2.intranet 4.9-RELEASE-p3 FreeBSD 4.9-RELEASE-p3 #1: Wed Mar 10 19:50:55 EST 2004 root@:/usr/obj/usr/src/sys/MYKERNEL i386
>Description:
The existing dbf2mysql does not allow quick inserts(the -q) flag, which offers
up to a 2.5X speedup. This is because Versions of mysql greater than 3.23.49
and greater than 4.0.2 require the MYSQL_OPT_LOCAL_INFILE option in all clients
attempting to load data from a file. I'm attaching a new patch-ab, which allows
dbf2mysql to function properly with the mysql323-server port and any greater
version. The patch adds a -L option to tell dbf2mysql to specifically allow
local inserts. I have merged the new patch-ab with the existing patch-ab since
section 4.4 of the FreeBSD porters handbook states, "you should avoid having
more than one patch fix the same file". There aren't any conflicts with the
merge.
Note, I am not the author of this patch. I simply took the patch from the
Debian package of dbf2mysql and merged it with the FreeBSD patch-ab. The
original author is credited in the diff. I have tested the new -L feature
extensively, and it works.
>How-To-Repeat:
Attempt to use the -q flag with dbf2mysql and a mysql server with version
greater than 3.23.49.
>Fix:
Apply the following patch to fix the problem.
--- patch-ab begins here ---
--- dbf2mysql.c.orig Thu Mar 11 15:39:22 2004
+++ dbf2mysql.c Thu Mar 11 15:59:12 2004
@@ -9,6 +9,12 @@
Fixxed Quick mode insert for blank Numeric fields
Modified to use -x flag to add _rec and _timestamp fields to start of record.
( only those lines immediately affect by if(express) (and getopt) )
+
+ Bart Friederichs (bart at friesoft.nl) feb 2003
+ Added MYSQL_OPT_LOCAL_INFILE to options for compatibility with
+ MySQL >3.23.49 and >4.0.2, new option -L enables it
+ Replaced mysql_connect with mysql_real_connect, SQLsock isn't used anymore
+
*/
#include <stdio.h>
#include <fcntl.h>
@@ -25,7 +31,7 @@
#endif
int verbose=0, upper=0, lower=0, create=0, fieldlow=0, var_chars=1;
-int express=0;
+int express=0, enable_local=0;
int null_fields=0, trim=0, quick=0;
char primary[11];
char *host = NULL;
@@ -84,7 +90,7 @@
printf("dbf2mysql %s\n", VERSION);
printf("usage: dbf2mysql [-h hostname] [-d dbase] [-t table] [-p primary key]\n");
printf(" [-o field[,field]] [-s oldname=newname[,oldname=newname]]\n");
- printf(" [-i field[,field]] [-c] [-f] [-F] [-n] [-r] [-u|-l] \n");
+ printf(" [-i field[,field]] [-c] [-f] [-F] [-n] [-r] [-u|-l] [-L]\n");
printf(" [-v[v]] [-x] [-q] [-P password] [-U user] dbf-file\n");
}
@@ -588,7 +594,7 @@
}
if (mysql_query(SQLsock, query) == -1) {
fprintf(stderr,
- "Error sending LOAD DATA INFILE from file '%s'\n", datafile);
+ "Error sending LOAD DATA LOCAL INFILE from file '%s'\n", datafile);
fprintf(stderr,
"Detailed report: %s\n",
mysql_error(SQLsock));
@@ -604,7 +610,7 @@
int main(int argc, char **argv)
{
int i;
- MYSQL *SQLsock,mysql;
+ MYSQL mysql;
extern int optind;
extern char *optarg;
char *query;
@@ -612,7 +618,7 @@
primary[0] = '\0';
- while ((i = getopt(argc, argv, "xqfFrne:lucvi:h:p:d:t:s:o:U:P:")) != EOF) {
+ while ((i = getopt(argc, argv, "xqLfFrne:lucvi:h:p:d:t:s:o:U:P:")) != EOF) {
switch (i) {
case 'P':
pass = (char *)strdup(optarg);
@@ -679,6 +685,9 @@
case 'o':
flist = (char *)strdup(optarg);
break;
+ case 'L':
+ enable_local = 1;
+ break;
case ':':
usage();
printf("missing argument!\n");
@@ -717,6 +726,7 @@
table);
printf("Number of records: %ld\n", dbh->db_records);
}
+
if (verbose > 1) {
printf("Name\t\t Length\tDisplay\t Type\n");
printf("-------------------------------------\n");
@@ -733,7 +743,20 @@
printf("Making connection to MySQL-server\n");
}
- if (!(SQLsock = mysql_connect(&mysql,host,user,pass))) {
+ mysql_init(&mysql);
+
+ if (enable_local == 1) {
+ if (verbose) {
+ printf("Setting MySQL option MYSQL_OPT_LOCAL_INFILE\n");
+ }
+
+ if (mysql_options(&mysql, MYSQL_OPT_LOCAL_INFILE, 0)) {
+ printf("Setting options failed.");
+ exit(1);
+ }
+ }
+
+ if (!mysql_real_connect(&mysql,host,user,pass, dbase, 0, NULL,0)) {
fprintf(stderr, "Couldn't get a connection with the ");
fprintf(stderr, "designated host!\n");
fprintf(stderr, "Detailed report: %s\n", mysql_error(&mysql));
@@ -741,23 +764,12 @@
exit(1);
}
- if (verbose > 2) {
- printf("Selecting database '%s'\n", dbase);
- }
-
- if ((mysql_select_db(SQLsock, dbase)) == -1) {
- fprintf(stderr, "Couldn't select database %s.\n", dbase);
- fprintf(stderr, "Detailed report: %s\n", mysql_error(SQLsock));
- mysql_close(SQLsock);
- dbf_close(&dbh);
- exit(1);
- }
/* Substitute field names */
do_onlyfields(flist, dbh);
do_substitute(subarg, dbh);
if (!create) {
- if (!check_table(SQLsock, table)) {
+ if (!check_table(&mysql, table)) {
printf("Table does not exist!\n");
exit(1);
}
@@ -768,30 +780,30 @@
if (!(query = (char *)malloc(12 + strlen(table)))) {
printf("Memory-allocation error in main (drop)!\n");
- mysql_close(SQLsock);
+ mysql_close(&mysql);
dbf_close(&dbh);
exit(1);
}
sprintf(query, "DROP TABLE %s", table);
- mysql_query(SQLsock, query);
+ mysql_query(&mysql, query);
free(query);
/* Build a CREATE-clause
*/
- do_create(SQLsock, table, dbh);
+ do_create(&mysql, table, dbh);
}
/* Build an INSERT-clause
*/
if (create < 2)
- do_inserts(SQLsock, table, dbh);
+ do_inserts(&mysql, table, dbh);
if (verbose > 2) {
printf("Closing up....\n");
}
- mysql_close(SQLsock);
+ mysql_close(&mysql);
dbf_close(&dbh);
exit(0);
}
--- patch-ab ends here ---
>Release-Note:
>Audit-Trail:
>Unformatted:
More information about the freebsd-ports-bugs
mailing list