From owner-freebsd-ports-bugs@FreeBSD.ORG Mon Mar 15 09:40:17 2004 Return-Path: Delivered-To: freebsd-ports-bugs@hub.freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 75EB416A4D5 for ; Mon, 15 Mar 2004 09:40:17 -0800 (PST) Received: from freefall.freebsd.org (freefall.freebsd.org [216.136.204.21]) by mx1.FreeBSD.org (Postfix) with ESMTP id 39D0E43D41 for ; Mon, 15 Mar 2004 09:40:17 -0800 (PST) (envelope-from gnats@FreeBSD.org) Received: from freefall.freebsd.org (gnats@localhost [127.0.0.1]) i2FHeHbv046443 for ; Mon, 15 Mar 2004 09:40:17 -0800 (PST) (envelope-from gnats@freefall.freebsd.org) Received: (from gnats@localhost) by freefall.freebsd.org (8.12.10/8.12.10/Submit) id i2FHeHNa046442; Mon, 15 Mar 2004 09:40:17 -0800 (PST) (envelope-from gnats) Resent-Date: Mon, 15 Mar 2004 09:40:17 -0800 (PST) Resent-Message-Id: <200403151740.i2FHeHNa046442@freefall.freebsd.org> Resent-From: FreeBSD-gnats-submit@FreeBSD.org (GNATS Filer) Resent-To: freebsd-ports-bugs@FreeBSD.org Resent-Reply-To: FreeBSD-gnats-submit@FreeBSD.org, Travis Whitton Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 57F7716A4CE for ; Mon, 15 Mar 2004 09:35:24 -0800 (PST) Received: from ppb-envlabs.com (unknown [209.208.86.146]) by mx1.FreeBSD.org (Postfix) with SMTP id 8193C43D1F for ; Mon, 15 Mar 2004 09:35:23 -0800 (PST) (envelope-from twhitton@ppb-envlabs.com) Received: (qmail 28754 invoked from network); 15 Mar 2004 17:23:18 -0000 Received: from localhost (127.0.0.1) by ppb-envlabs.com with SMTP; 15 Mar 2004 17:23:18 -0000 Received: from aop2.intranet by dns.intranet (VaMailArmor-2.0.1.16) id 28748-433617E0; Mon, 15 Mar 2004 12:23:18 -0500 Received: (from root@localhost) by aop2.intranet (8.12.9p2/8.12.9/Submit) id i2FHZcEF075406; Mon, 15 Mar 2004 12:35:38 -0500 (EST) (envelope-from twhitton@ppb-envlabs.com) Message-Id: <200403151735.i2FHZcEF075406@aop2.intranet> Date: Mon, 15 Mar 2004 12:35:38 -0500 (EST) From: Travis Whitton To: FreeBSD-gnats-submit@FreeBSD.org X-Send-Pr-Version: 3.113 Subject: ports/64305: dbf2mysql -q option broken with mysql323-server and above X-BeenThere: freebsd-ports-bugs@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list Reply-To: Travis Whitton List-Id: Ports bug reports List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 15 Mar 2004 17:40:17 -0000 >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@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 #include @@ -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: