Professional Web Applications Themes

LOAD DATA doesn't seem to work with CHARACTER SET. Is that featurein released MySQL 5.0? - MySQL

Put in this MySQL command, via Python and MySQLdb: LOAD DATA LOCAL INFILE "c:\doe~1\nagle\locals~1\temp\tmpi16zz2.tx t" REPLACE INTO TABLE companyindex CHARACTER SET utf8 FIELDS ENCLOSED BY '"' ESCAPED BY '\' TERMINATED BY ',' (conformed_company_name, domain, location, state, postal_code, country_code, database_name, record_id) Received:   If I remove the "CHARACTER SET utf8", SQL accepts it, FIELDS and all, and loads ASCII data correctly. I get exactly the same error by pasting the command above into the MySQL query browser, so it's not a Python problem. That should be the right syntax, per http://dev.mysql.com/doc/refman/5.0/en/load-data.html Here's my MySQL installation: MySQL version: 5.0.27. character_set_client: utf8 character_set_connection: ...

  1. #1

    Default LOAD DATA doesn't seem to work with CHARACTER SET. Is that featurein released MySQL 5.0?

    Put in this MySQL command, via Python and MySQLdb:

    LOAD DATA LOCAL INFILE "c:\\doe~1\\nagle\\locals~1\\temp\\tmpi16zz2.tx t" REPLACE
    INTO TABLE companyindex CHARACTER SET utf8 FIELDS ENCLOSED BY '"' ESCAPED BY
    '\\' TERMINATED
    BY ',' (conformed_company_name, domain, location, state, postal_code,
    country_code, database_name, record_id)

    Received:
     

    If I remove the "CHARACTER SET utf8", SQL accepts it, FIELDS
    and all, and loads ASCII data correctly.

    I get exactly the same error by pasting the command above into the
    MySQL query browser, so it's not a Python problem.

    That should be the right syntax, per

    http://dev.mysql.com/doc/refman/5.0/en/load-data.html

    Here's my MySQL installation:

    MySQL version: 5.0.27.
    character_set_client: utf8
    character_set_connection: utf8
    character_set_database: utf8
    chacter_set_results: utf8
    character_set_server: utf8
    character_set_system: utf8

    so everything is Unicode-enabled.

    But, according to this bug report:

    http://bugs.mysql.com/bug.php?id=15126

    [16 Mar 11:29] Alexander Barkov

    Dear doc team,

    Can you please also doent that the CHARACTER SET
    clause was added into LOAD DATA syntax:

    LOAD DATA INFILE 'loaddata6.dat' INTO TABLE t1 CHARACTER SET koi8r;

    the MySQL 5.0 doentation was recently updated to show this feature.
    But it looks like that feature actually went into MySQL 5.1 in this patch

    http://lists.mysql.com/commits/16915

    on December 13, 2006, and isn't in MySQL 5.0.

    In fact, if I paste the LOAD DATA INFILE statement from the bug
    report into the MySQL query browser, I get a syntax error near
    "'CHARACTER SET koi8r' at line 1". So it really isn't implemented
    in MySQL 5.0.27.

    Is that correct?

    Given that, what's the correct way to tell MySQL that it
    is reading a UTF8 file? (And does the Python interface to
    the MySQL client need to do something about this?)

    John Nagle

    John Guest

  2. #2

    Default Re: LOAD DATA doesn't seem to work with CHARACTER SET. Is that feature in released MySQL 5.0?

    John Nagle <com> wrote: 
    >
    > If I remove the "CHARACTER SET utf8", SQL accepts it, FIELDS
    > and all, and loads ASCII data correctly.[/ref]
     

    Maybe this was changed recently, but the manual page you reference
    says:

    "Beginning with MySQL 5.0.38, if the contents of the input file use
    a character set that differs from the default, it is possible (and
    usually preferable) to use the CHARACTER SET clause to specify the
    character set of the file."

    So your MySQL is too old for that feature.

     

    From the same manual page:

    "The character set indicated by the character_set_database system
    variable is used to interpret the information in the file. SET NAMES
    and the setting of character_set_client do not affect interpretation
    of input."



    XL
    --
    Axel Schwenke, Support Engineer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  3. #3

    Default Re: LOAD DATA doesn't seem to work with CHARACTER SET. Is that featurein released MySQL 5.0?

    Axel Schwenke wrote: 
    Yes, the manual page changed since yesterday. Compare

    http://web.archive.org/web/20060428130509/http://dev.mysql.com/doc/refman/5.0/en/load-data.html
     

    "Too old?" 5.0.38 isn't even released yet. 5.0.37 is the latest download.

    John Nagle
    John Guest

  4. #4

    Default Re: LOAD DATA doesn't seem to work with CHARACTER SET. Is that feature in released MySQL 5.0?

    John Nagle <com> wrote: 
    > Yes, the manual page changed since yesterday. Compare
    >
    > http://web.archive.org/web/20060428130509/http://dev.mysql.com/doc/refman/5.0/en/load-data.html

    >
    > "Too old?" 5.0.38 isn't even released yet. 5.0.37 is the latest download.[/ref]

    Hehe. You're experiencing the time lag between updating the
    doentation repository and releasing a new version.
    To get this feature, you have to checkout MySQL source code
    from the public bk repository and compile yourself:

    http://dev.mysql.com/doc/refman/5.0/en/installing-source-tree.html

    Sorry for the inconvenience but apparently this is a "bleeding
    edge" feature...


    XL
    --
    Axel Schwenke, Support Engineer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  5. #5

    Default Re: LOAD DATA doesn't seem to work with CHARACTER SET. Is that featurein released MySQL 5.0?

    Axel Schwenke wrote: [/ref]
     

    I know. I spent most of Sunday trying to figure out why
    my LOAD DATA command wouldn't work. Actually, I didn't
    need a CHARACTER SET clause; everything (server, database, tables,
    connection, client) is configured for utf8, and the default
    for LOAD FILE should then be utf8 too.

    John Nagle
    John Guest

Similar Threads

  1. Why doesn't Load Vars work everytime?
    By MYSCREENNAMEISUNAVAILABLE in forum Macromedia Flash Data Integration
    Replies: 0
    Last Post: March 10th, 05:58 PM
  2. mysql query in php: doesn't work
    By fra in forum MySQL
    Replies: 3
    Last Post: October 6th, 04:10 PM
  3. #24742 [NEW]: mysql functions doesn't work.
    By webmaster at trip-fx dot com in forum PHP Development
    Replies: 0
    Last Post: July 21st, 08:57 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139