The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

problem with LOAD DATA in phpmyadmin

Discussion in 'Database Discussions' started by olivier222333, Sep 14, 2004.

  1. olivier222333

    olivier222333 Well-Known Member
    PartnerNOC

    Joined:
    Jul 12, 2004
    Messages:
    55
    Likes Received:
    0
    Trophy Points:
    6
    Hi
    When I want to do a 'LOAD DATA ' in phpmyadmin

    I got this msg:

    #1148 - The used command is not allowed with this MySQL version

    how to allow this function?
    thx
     
  2. olivier222333

    olivier222333 Well-Known Member
    PartnerNOC

    Joined:
    Jul 12, 2004
    Messages:
    55
    Likes Received:
    0
    Trophy Points:
    6
    I try the fix to recompile php with =/usr but it didnt work .... I still goth this bug

    $MYSQL='--with-mysql=/usr';
    if (-e "/usr/local/bin/mysql") {
    $MYSQL='--with-mysql=/usr/local';
    }
    if (-e "/usr/bin/mysql") {
    $MYSQL='--with-mysql=/usr';
    }



    but phpmyadmin answered:



    SQL :

    LOAD DATA LOCAL INFILE '/tmp/phpa5Vgw5' INTO TABLE `Test` FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'

    MySQL a répondu:
    #1148 - The used command is not allowed with this MySQL version
     
    #2 olivier222333, Sep 14, 2004
    Last edited: Sep 14, 2004
  3. olivier222333

    olivier222333 Well-Known Member
    PartnerNOC

    Joined:
    Jul 12, 2004
    Messages:
    55
    Likes Received:
    0
    Trophy Points:
    6
    anyone has solved this issue?
     
  4. olivier222333

    olivier222333 Well-Known Member
    PartnerNOC

    Joined:
    Jul 12, 2004
    Messages:
    55
    Likes Received:
    0
    Trophy Points:
    6
    up

    please I dont find the problem
     
  5. Angel78

    Angel78 Well-Known Member

    Joined:
    May 9, 2002
    Messages:
    413
    Likes Received:
    1
    Trophy Points:
    16
    it's a security "feature" in the newere versions of mysql. I also have the problem and havent found a workaround.
     
  6. Myacen

    Myacen Well-Known Member

    Joined:
    Apr 6, 2002
    Messages:
    222
    Likes Received:
    0
    Trophy Points:
    16
  7. zentity

    zentity Well-Known Member

    Joined:
    Jun 16, 2004
    Messages:
    78
    Likes Received:
    0
    Trophy Points:
    6
    If you drop a phpinfo.php page
    Code:
    <?
    phpinfo();
    ?>
    
    into one of your cpanel themes ie. /usr/local/cpanel/base/frontend/x/phpinfo.php and access it http://cpanelurl.com:2082/x/phpinfo.php, does it show that php was compiled with --with-mysql=/usr ?

    Thats fixed every box I had this problem with so far.
     
    #7 zentity, Sep 15, 2004
    Last edited: Sep 15, 2004
  8. Angel78

    Angel78 Well-Known Member

    Joined:
    May 9, 2002
    Messages:
    413
    Likes Received:
    1
    Trophy Points:
    16

    Status: RESOLVED
    Resolution: INVALID


    what does this mean? That they have no idea how to fix it or?
     
  9. Bruce

    Bruce Well-Known Member

    Joined:
    Oct 4, 2001
    Messages:
    147
    Likes Received:
    0
    Trophy Points:
    16
    Has anyone found a fix for this yet ?
     
  10. Myacen

    Myacen Well-Known Member

    Joined:
    Apr 6, 2002
    Messages:
    222
    Likes Received:
    0
    Trophy Points:
    16
    Just update the bugzilla report I posted if your having the same problem.
     
  11. darkspider42

    darkspider42 Registered

    Joined:
    Jul 13, 2004
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    bump, any progress on this problem yet?
     
  12. Myacen

    Myacen Well-Known Member

    Joined:
    Apr 6, 2002
    Messages:
    222
    Likes Received:
    0
    Trophy Points:
    16
  13. Myacen

    Myacen Well-Known Member

    Joined:
    Apr 6, 2002
    Messages:
    222
    Likes Received:
    0
    Trophy Points:
    16
    Back to page 1 as this is not resolved.
     
  14. Angel78

    Angel78 Well-Known Member

    Joined:
    May 9, 2002
    Messages:
    413
    Likes Received:
    1
    Trophy Points:
    16
    will this ever be solved by Cpanel ? :(
     
  15. Myacen

    Myacen Well-Known Member

    Joined:
    Apr 6, 2002
    Messages:
    222
    Likes Received:
    0
    Trophy Points:
    16
    :confused: :( Back to the top.... who knows?
     
  16. smerkel

    smerkel Member

    Joined:
    Feb 19, 2002
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    1
    The problem is the phpMyAdmin that is built into the cPanel interface doesn't use the php built with apache. So you can rebuild apache all day long and it will not change how php is compiled.

    './configure' '--enable-static' '--enable-mbstring' '--enable-mbstr-enc-trans' '--enable-mbregex' '--with-zlib' '--with-imap=../../bsd/imap' '--enable-ftp' '--with-gettext' '--with-xml' '--enable-track-vars' '--enable-sockets' '--with-mysql' '--with-pgsql' '--with-config-file-path=/usr/local/cpanel/3rdparty/etc' '--prefix=/usr/local/cpanel/3rdparty'

    This is what phpinfo looks like (when placed into /usr/local/cpanel/base/3rdparty/), and here is what it looks like when run over the Apache instance (on my server for example)

    /configure' '--with-apxs=/usr/local/apache/bin/apxs' '--with-xml' '--enable-bcmath' '--enable-calendar' '--with-curl' '--enable-ftp' '--with-gd' '--with-jpeg-dir=/usr/local' '--with-png-dir=/usr' '--with-xpm-dir=/usr/X11R6' '--with-gettext' '--with-imap' '--with-imap-ssl' '--with-kerberos' '--enable-mbstring' '--enable-mbstr-enc-trans' '--enable-mbregex' '--with-mcrypt' '--with-mhash' '--with-ming=../ming-0.2a' '--enable-magic-quotes' '--with-mysql=/usr' '--enable-discard-path' '--with-pear' '--enable-safe-mode' '--enable-track-vars' '--with-ttf' '--with-freetype-dir=/usr' '--enable-gd-native-ttf' '--enable-versioning' '--with-zip' '--with-zlib'

    Until the php instance that cPanel uses is fixed, the error will persist.

    Regards,

    Steve
     
  17. easyhoster1

    easyhoster1 Well-Known Member

    Joined:
    Sep 25, 2003
    Messages:
    659
    Likes Received:
    0
    Trophy Points:
    16
    Also bug exist on;

    WHM 9.7.2 cPanel 9.7.7-R16
    FreeBSD 4.9-RELEASE i386 - WHM X v3.1.0
     
  18. jsteel

    jsteel Well-Known Member

    Joined:
    Jul 4, 2002
    Messages:
    646
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Atlanta, GA
    This problem has nothing to do with cPanel or phpMyAdmin. MySQL v4 changed it's secuirty model and the File_priv field for a user is set to 'N' by default. If you update the field to 'Y' for the user that is trying to access the DB in question, LOAD DATA INFILE will work just fine.
     
  19. smerkel

    smerkel Member

    Joined:
    Feb 19, 2002
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    1
    Greetings:

    Actually, no.

    From http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html

    “For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all. Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege. See section 5.5.3 Privileges Provided by MySQL.

    Using LOCAL is a bit slower than letting the server access the files directly, because the contents of the file must be sent over the connection by the client to the server. On the other hand, you do not need the FILE privilege to load local files.”

    As a result, changing the file_priv flag will not aid in this particular problem. It would, however, allow LOAD DATA INFILE to work. (By default, this flag is set to off - and most service providers - with good reason - will not enable it.)

    Best regards,

    Steve
     
  20. MattR9

    MattR9 Registered

    Joined:
    Jun 8, 2004
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    I've found a solution to this issue:

    - Modify /usr/local/cpanel/base/3rdparty/phpMyAdmin/libraries/common.lib.php
    - Update all instances of client_flags to be 128

    Basically this will result in a connection like:

    mysql_connect(hostname.com, username, password, false, 128);

    (note ", false, 128")

    CSV files now upload successfully.

    -Matt
    RackNine.com
     
Loading...

Share This Page