Page 1 of 1

csv import mysql error

Posted: 12 Mar 2018, 14:08
by timothy_truckle
PHP 7.0.25-0ubuntu0.16.04.1 (cli)

I tried to import a csv with products:

php artisan aimeos:jobs product/import/csv

Number of apostrophes don't match:
SELECT mpro."id" AS "product.id", mpro."siteid" AS "product.siteid",
mpro."typeid" AS "product.typeid", mpro."code" AS "product.code",
mpro."label" AS "product.label", mpro."config" AS "product.config",
mpro."start" AS "product.datestart", mpro."end" AS "product.dateend",
mpro."status" AS "product.status", mpro."ctime" AS "product.ctime",
mpro."mtime" AS "product.mtime", mpro."editor" AS "product.editor",
mpro."target" AS "product.target"
FROM "mshop_product" AS mpro

WHERE ( mpro."siteid" IN (1) AND mpro."code" IN ('{','name: \"laravel/laravel\"','description: \"The Laravel Framework.\"','keywords: [\"framework\"','license: \"MIT\"','type: \"project\"','require: {','php: \">=7.0.0\"','aimeos/aimeos-laravel: \"~2017.10\"','aimeoscom/omnipay-sofort: \"^2.3\"','fideloper/proxy: \"~3.3\"','guzzlehttp/guzzle: \"^6.3\"','laravel/framework: \"5.5.*\"','laravel/tinker: \"~1.0\"','omnipay/paypal: \"~2.0\"','symfony/event-dispatcher: \"^2.8\"',' }','require-dev: {','filp/whoops: \"~2.0\"','fzaninotto/faker: \"~1.4\"','mockery/mockery: \"~1.0\"','phpunit/phpunit: \"~6.0\"','symfony/thanks: \"^1.0\"','autoload: {','classmap: [','database/seeds','database/factories',' ]','psr-4: {','App\\\\: \"app/\"',' }','autoload-dev: {','Tests\\\\: \"tests/\"','extra: {','laravel: {','dont-discover: [',' ]','scripts: {','post-root-package-install: [','@php -r \\file_exists(\'.env\') || copy(\'.env.example\'','post-create-project-cmd: [','@php artisan key:generate','post-autoload-dump: [','Illuminate\\\\Foundation\\\\ComposerScripts::postAutoloadDump','@php artisan package:discover','post-update-cmd: [','php artisan vendor:publish --tag=public --force','php artisan migrate','config: {','preferred-install: \"dist\"','sort-packages: true','optimize-autoloader: true','}') )
GROUP BY mpro."id", mpro."siteid", mpro."typeid", mpro."code",
mpro."label", mpro."config", mpro."start", mpro."end",
mpro."status", mpro."ctime", mpro."mtime", mpro."editor",
mpro."target"


LIMIT 53 OFFSET 0

What could be the error here? if i unquote in heidisql the query works. Seems to be a problem with quoting or connection configuration.

Re: csv import mysql error

Posted: 12 Mar 2018, 14:30
by timothy_truckle
Aimeos Version from composer.json: "aimeos/aimeos-laravel": "~2017.10"

Re: csv import mysql error

Posted: 13 Mar 2018, 13:06
by aimeos
You didn't configure the directory that contains the CSV files:
https://aimeos.org/docs/Configuration/C ... v/location

Re: csv import mysql error

Posted: 13 Mar 2018, 14:19
by timothy_truckle
I think the issue is not with the path or csv, the query made by the importer is not properly quoted, a file import attempt is never made, if i remove the zip-file the output is the same:

#20 {main}Number of apostrophes don't match:

followed by the query:

SELECT mpro."id" AS "product.id", mpro."siteid" AS "product.siteid",
mpro."typeid" AS "product.typeid", mpro."code" AS "product.code",
mpro."label" AS "product.label", mpro."config" AS "product.config",
mpro."start" AS "product.datestart", mpro."end" AS "product.dateend",
mpro."status" AS "product.status", mpro."ctime" AS "product.ctime",
mpro."mtime" AS "product.mtime", mpro."editor" AS "product.editor",
mpro."target" AS "product.target"
FROM "mshop_product" AS mpro

WHERE ( mpro."siteid" IN (1) AND mpro."code" IN ('{','name: \"laravel/laravel\"','description: \"The Laravel Framework.\"','keywords: [\"framework\"','license: \"MIT\"','type: \"project\"','require: {','php: \">=7.0.0\"','aimeos/aimeos-laravel: \"~2017.10\"','aimeoscom/omnipay-sofort: \"^2.3\"','fideloper/proxy: \"~3.3\"','guzzlehttp/guzzle: \"^6.3\"','laravel/framework: \"5.5.*\"','laravel/tinker: \"~1.0\"','omnipay/paypal: \"~2.0\"','symfony/event-dispatcher: \"^2.8\"',' }','require-dev: {','filp/whoops: \"~2.0\"','fzaninotto/faker: \"~1.4\"','mockery/mockery: \"~1.0\"','phpunit/phpunit: \"~6.0\"','symfony/thanks: \"^1.0\"','autoload: {','classmap: [','database/seeds','database/factories',' ]','psr-4: {','App\\\\: \"app/\"',' }','autoload-dev: {','Tests\\\\: \"tests/\"','extra: {','laravel: {','dont-discover: [',' ]','scripts: {','post-root-package-install: [','@php -r \\file_exists(\'.env\') || copy(\'.env.example\'','post-create-project-cmd: [','@php artisan key:generate','post-autoload-dump: [','Illuminate\\\\Foundation\\\\ComposerScripts::postAutoloadDump','@php artisan package:discover','post-update-cmd: [','php artisan vendor:publish --tag=public --force','php artisan migrate','config: {','preferred-install: \"dist\"','sort-packages: true','optimize-autoloader: true','}') )
GROUP BY mpro."id", mpro."siteid", mpro."typeid", mpro."code",
mpro."label", mpro."config", mpro."start", mpro."end",
mpro."status", mpro."ctime", mpro."mtime", mpro."editor",
mpro."target"


LIMIT 53 OFFSET 0

-------------------------------------------------------------------------------------------
copy the query AS IS to heidisql(or whatever mysql client): does not work bc wrong quoting!
-------------------------------------------------------------------------------------------

Anyway here is the config and csv, the path is readable by user and www-data(webserver), the file is named import.zip and contains a handcrafted csv (one line):

Config:

config/shop.php
...
'controller' => [
'jobs' => [
'import' => [
'csv' => [
'location' => '/home/user/projects/shop/storage/tmp'
],
],
'product' => [
'import' => [
'csv' => [
'mapping' => array(
'item' => array(
0 => 'product.code', // e.g. unique EAN code
1 => 'product.label', // UTF-8 encoded text, also used as product name
2 => 'product.type', // type of the product, e.g. "default" or "selection"
3 => 'product.status', // enabled (1) or disabled (0)
),
'text' => array(
4 => 'text.type', // e.g. "short" for short description
5 => 'text.content', // UTF-8 encoded text
6 => 'text.type', // e.g. "long" for long description
7 => 'text.content', // UTF-8 encoded text
),
'media' => array(
8 => 'media.url', // relative URL of the product image on the server
),
'price' => array(
9 => 'price.currencyid', // three letter ISO currency code
10 => 'price.quantity', // the quantity the price (for block pricing)
11 => 'price.value', // price with decimals separated by a dot
12 => 'price.taxrate', // tax rate with decimals separated by a dot
),
'attribute' => array(
13 => 'attribute.code', // code of an attribute, will be created if not exists
14 => 'attribute.type', // e.g. "size", "length", "width", "color", etc.
),
'product' => array(
15 => 'product.code', // e.g. EAN code of another product
16 => 'product.lists.type', // e.g. "suggestion" for suggested product
),
'property' => array(
17 => 'product.property.value', // arbitrary value for the corresponding type
18 => 'product.property.type', // e.g. "package-weight"
),
'catalog' => array(
19 => 'catalog.code', // e.g. Unique category code
20 => 'catalog.lists.type', // e.g. "promotion" for top seller products
),
),
],
],
],
...

CSV:

"1","30 Alte Hausschlachterezepte","default","1","short","30 leckere Hausschlachterrezepte","long","Inhaltsverzeichnis: 1. Delikateß - Leberwurst 2. Feine Leberwurst","/3/0/30-hausschlachte_1.jpg","EUR","1","3.7300","7.00","","","","","","","home",""

Any ideas? I am new to laravel and aimeos, please point me in the right direction.

Re: csv import mysql error

Posted: 14 Mar 2018, 10:47
by aimeos
We have been able to reproduce the problem but are still looking for the root cause of it. The good news: It's nothing that can lead to an SQL injection because the exeception is thrown when building the SQL locally.

Nevertheless, your error is still that the location of the directory containing the CSV is incorrect. It's "controller/jobs/product/import/csv/location" not "controller/jobs/import/csv/location":
https://aimeos.org/docs/Configuration/C ... v/location

Re: csv import mysql error

Posted: 14 Mar 2018, 13:56
by aimeos
OK, the problem has been fixed and new minor releases are available. Please run "composer update".
Thank you for reporting the issue!

Re: csv import mysql error

Posted: 12 Jul 2018, 09:20
by timothy_truckle
Thank you!
Shop is now up and running. You can see it in Action at https://www.wurstrezepte.org

I'd like to put the template (css/js etc) into a separate package, maybe someone else is interested in it. What would be the best way to do this? Thanks again and sorry for the late reply.

Re: csv import mysql error

Posted: 12 Jul 2018, 12:14
by aimeos
Cool! :-)

You can create a new extension with your changes:
https://aimeos.org/developer/extensions ... _extension