csv import mysql error
Forum rules
Always add your Laravel, Aimeos and PHP version as well as your environment (Linux/Mac/Win)
Spam and unrelated posts will be removed immediately!
Always add your Laravel, Aimeos and PHP version as well as your environment (Linux/Mac/Win)
Spam and unrelated posts will be removed immediately!
-
- Posts: 10
- Joined: 12 Mar 2018, 14:00
csv import mysql error
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.
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.
-
- Posts: 10
- Joined: 12 Mar 2018, 14:00
Re: csv import mysql error
Aimeos Version from composer.json: "aimeos/aimeos-laravel": "~2017.10"
Re: csv import mysql error
You didn't configure the directory that contains the CSV files:
https://aimeos.org/docs/Configuration/C ... v/location
https://aimeos.org/docs/Configuration/C ... v/location
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, give us a star
If you like Aimeos, give us a star
-
- Posts: 10
- Joined: 12 Mar 2018, 14:00
Re: csv import mysql error
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.
#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
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
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
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, give us a star
If you like Aimeos, give us a star
Re: csv import mysql error
OK, the problem has been fixed and new minor releases are available. Please run "composer update".
Thank you for reporting the issue!
Thank you for reporting the issue!
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, give us a star
If you like Aimeos, give us a star
-
- Posts: 10
- Joined: 12 Mar 2018, 14:00
Re: csv import mysql error
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.
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
Cool!
You can create a new extension with your changes:
https://aimeos.org/developer/extensions ... _extension
You can create a new extension with your changes:
https://aimeos.org/developer/extensions ... _extension
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, give us a star
If you like Aimeos, give us a star