Learn about Centmin Mod LEMP Stack today
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:
    5:21 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

    55,801
    12,271
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,857
    Local Time:
    9:51 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    comment out in my.cnf
    Code:
    local-infile=0
     
  3. pritam sinha

    pritam sinha New Member

    12
    2
    3
    Oct 23, 2015
    Ratings:
    +2
    Local Time:
    5:21 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 Well-Known Member

    5,451
    1,412
    113
    May 25, 2014
    Ratings:
    +2,206
    Local Time:
    7:51 PM
    Mainline
    10.2
    and did mysqlrestart ?
     
  5. pritam sinha

    pritam sinha New Member

    12
    2
    3
    Oct 23, 2015
    Ratings:
    +2
    Local Time:
    5:21 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:
    5:21 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.