Welcome to Centmin Mod Community
Register Now

MariaDB mysql Load data local problem

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by pritam sinha, Nov 15, 2015.

Tags:
  1. pritam sinha

    pritam sinha New Member

    12
    2
    3
    Oct 23, 2015
    Ratings:
    +2
    Local Time:
    3:16 PM
    1.9
    5.5
    I have custom ecommerce script where i am trying to import .sql file into DB but igives error.

    [LOAD DATA LOCAL INFILE '/home/nginx/domains/domain.com/public/upload/10/im_product.sql' INTO TABLE DB.PRODUCT FIELDS TERMINATED BY '|' ENCLOSED BY '\'' LINES TERMINATED BY ' ' (ID, CATEGORY_ID, TITLE, TITLE_CLEAN, FILENAME, MANUFACTURER, MANUFACTURER_CODE, EAN_UPC, MIN_PRICE, MAX_PRICE, STORE_COUNT, ISVALID, SPECIAL_OFFER, FIELD_1, FIELD_2, FIELD_3, FIELD_4, FIELD_5, FIELD_6, FIELD_7, FIELD_8, FIELD_9, DATE_ADDED)] - The used command is not allowed with this MariaDB version -

    When i try to run same command with mysql ssh it gives following error
    ERROR 2013 (HY000): Lost connection to MySQL server during query

    What is the problem? I think enclosed by '\'' is correct. i have also tried with '\' but query is not at all executed.
     
  2. eva2000

    eva2000 Administrator Staff Member

    34,574
    7,647
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +11,759
    Local Time:
    7:46 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    comment out in my.cnf
    Code:
    local-infile=0
     
    • Like Like x 1
    • Informative Informative x 1
  3. pritam sinha

    pritam sinha New Member

    12
    2
    3
    Oct 23, 2015
    Ratings:
    +2
    Local Time:
    3:16 PM
    1.9
    5.5
    I have done that. And added local-infile=1 in [mysql] section as well.
    I have even changed timeout parameters to 3000 but still error persists.
    Is there any security feature in centminmod causing problem?
     
  4. rdan

    rdan Premium Member Premium Member

    4,193
    1,014
    113
    May 25, 2014
    Ratings:
    +1,438
    Local Time:
    5:46 PM
    Mainline
    10.2
    and did mysqlrestart ?
     
    • Like Like x 1
  5. pritam sinha

    pritam sinha New Member

    12
    2
    3
    Oct 23, 2015
    Ratings:
    +2
    Local Time:
    3:16 PM
    1.9
    5.5
    yes,i did 'service mysql restart' too.
    interesting thing is that,this query executes:

    LOAD DATA LOCAL INFILE '/home/nginx/domains/domain.com/public/upload/tyy123.sql' REPLACE INTO TABLE `DB`.`allelectronics3` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (`title`, `description`, `brand`, `link`, `image_link`, `sub_category_name`, `category_name`, `offer_price`, `mrp`, `availability`);

    i have given same file permission to both files as well.


    Can any one help, what is the problem?
     
  6. pritam sinha

    pritam sinha New Member

    12
    2
    3
    Oct 23, 2015
    Ratings:
    +2
    Local Time:
    3:16 PM
    1.9
    5.5
    Here is a part of data in im_product.sql i wanted to import.

    '3'|'9'|'LG Optimus 3D P920'|'LG Optimus 3D P920'|''|'156'|''|''|'0.00'|'0.00'|'0'|'Y'|'N'|''|''|''|''|''|''|''|''|''|'2015-11-15 12:42:30'
    '4'|'9'|'Samsung Metro Duos'|'Samsung Metro Duos'|''|'64'|''|''|'0.00'|'0.00'|'0'|'Y'|'N'|''|''|''|''|''|''|''|''|''|'2015-11-15 12:42:30'
    '5'|'9'|'LG Optimus Black P970'|'LG Optimus Black P970'|''|'156'|''|''|'0.00'|'0.00'|'0'|'Y'|'N'|''|''|''|''|''|''|''|''|''|'2015-11-15 12:42:30'
    '7'|'9'|'Sony Ericsson Xperia SK17i'|'Sony Ericsson Xperia SK17i'|''|'1158'|''|''|'0.00'|'0.00'|'0'|'Y'|'N'|''|''|''|''|''|''|''|''|''|'2015-11-15
    12:42:30'

    Is it due to date- time format change in mariaDB 10.x?
    cause "tyy123.sql" dint had date-time column but im-product.sql.
     
..