search

Part-DB / Part-DB-server

Part-DB is an Open source inventory management system for your electronic components
https://docs.part-db.de/
GNU Affero General Public License v3.0
902 stars 100 forks source link

Problem importing partkeepr data #473

Closed nilsht closed 9 months ago

nilsht commented 9 months ago

I setup a Part-DB docker instance, but have problems importing data from partkeepr. Here is the output from the attempt:

www-data@4cc4b269ea1d:~/html$ php bin/console partdb:migrations:import-partkeepr uploads/pk.xml 

 ! [NOTE] This command is still in development. If you encounter any problems, please report them to the issue tracker  
 !        on GitHub.                                                                                                    

 [WARNING] This command will delete all existing data in the database (except users). Make sure that you have no        
           important data in the database before you continue!                                                          

 Please type "DELETE ALL DATA" to continue. []:
 > DELETE ALL DATA

 [INFO] Importing distributors...                                                                                       

 [OK] Imported 20 distributors.                                                                                         

 [INFO] Importing part measurement units...                                                                             

 [OK] Imported 3 measurement units.                                                                                     

 [INFO] Importing manufacturers...                                                                                      

{"message":"User Deprecated: Since symfony/doctrine-bridge 6.3: Registering \"App\\EventSubscriber\\LogSystem\\EventLoggerSubscriber\" as a Doctrine subscriber is deprecated. Register it as a listener instead, using e.g. the #[AsDoctrineListener] or #[AsDocumentListener] attribute.","context":{"exception":{"class":"ErrorException","message":"User Deprecated: Since symfony/doctrine-bridge 6.3: Registering \"App\\EventSubscriber\\LogSystem\\EventLoggerSubscriber\" as a Doctrine subscriber is deprecated. Register it as a listener instead, using e.g. the #[AsDoctrineListener] or #[AsDocumentListener] attribute.","code":0,"file":"/var/www/html/vendor/symfony/doctrine-bridge/ContainerAwareEventManager.php:211","trace":["/var/www/html/vendor/symfony/doctrine-bridge/ContainerAwareEventManager.php:102","/var/www/html/vendor/doctrine/dbal/src/Connection.php:386","/var/www/html/vendor/doctrine/dbal/src/Connection.php:452","/var/www/html/vendor/doctrine/dbal/src/Connection.php:410","/var/www/html/vendor/doctrine/dbal/src/Connection.php:317","/var/www/html/vendor/doctrine/dbal/src/Connection.php:1315","/var/www/html/var/cache/docker/ContainerLiFNnQ3/App_KernelDockerContainer.php:278","/var/www/html/var/cache/docker/ContainerLiFNnQ3/App_KernelDockerContainer.php:348","/var/www/html/var/cache/docker/ContainerLiFNnQ3/App_KernelDockerContainer.php:312","/var/www/html/vendor/symfony/var-exporter/Internal/LazyObjectState.php:100","/var/www/html/vendor/symfony/var-exporter/LazyGhostTrait.php:176","/var/www/html/vendor/doctrine/orm/lib/Doctrine/ORM/EntityManager.php:207","/var/www/html/src/Doctrine/Purger/ResetAutoIncrementORMPurger.php:116","/var/www/html/src/Services/ImportExportSystem/PartKeeprImporter/PKImportHelper.php:46","/var/www/html/src/Command/Migrations/ImportPartKeeprCommand.php:82","/var/www/html/vendor/symfony/console/Command/Command.php:326","/var/www/html/vendor/symfony/console/Application.php:1096","/var/www/html/vendor/symfony/framework-bundle/Console/Application.php:126","/var/www/html/vendor/symfony/console/Application.php:324","/var/www/html/vendor/symfony/framework-bundle/Console/Application.php:80","/var/www/html/vendor/symfony/console/Application.php:175","/var/www/html/vendor/symfony/runtime/Runner/Symfony/ConsoleApplicationRunner.php:49","/var/www/html/vendor/autoload_runtime.php:29","/var/www/html/bin/console:18"]}},"level":200,"level_name":"INFO","channel":"deprecation","datetime":"2024-01-10T19:36:23.224779+00:00","extra":{"token":null,"command":{"name":"partdb:migrations:import-partkeepr","arguments":{"command":"partdb:migrations:import-partkeepr","file":"uploads/pk.xml"}}}}
{"message":"User Deprecated: Since symfony/doctrine-bridge 6.3: Registering \"App\\EventSubscriber\\LogSystem\\LogDBMigrationSubscriber\" as a Doctrine subscriber is deprecated. Register it as a listener instead, using e.g. the #[AsDoctrineListener] or #[AsDocumentListener] attribute.","context":{"exception":{"class":"ErrorException","message":"User Deprecated: Since symfony/doctrine-bridge 6.3: Registering \"App\\EventSubscriber\\LogSystem\\LogDBMigrationSubscriber\" as a Doctrine subscriber is deprecated. Register it as a listener instead, using e.g. the #[AsDoctrineListener] or #[AsDocumentListener] attribute.","code":0,"file":"/var/www/html/vendor/symfony/doctrine-bridge/ContainerAwareEventManager.php:211","trace":["/var/www/html/vendor/symfony/doctrine-bridge/ContainerAwareEventManager.php:102","/var/www/html/vendor/doctrine/dbal/src/Connection.php:386","/var/www/html/vendor/doctrine/dbal/src/Connection.php:452","/var/www/html/vendor/doctrine/dbal/src/Connection.php:410","/var/www/html/vendor/doctrine/dbal/src/Connection.php:317","/var/www/html/vendor/doctrine/dbal/src/Connection.php:1315","/var/www/html/var/cache/docker/ContainerLiFNnQ3/App_KernelDockerContainer.php:278","/var/www/html/var/cache/docker/ContainerLiFNnQ3/App_KernelDockerContainer.php:348","/var/www/html/var/cache/docker/ContainerLiFNnQ3/App_KernelDockerContainer.php:312","/var/www/html/vendor/symfony/var-exporter/Internal/LazyObjectState.php:100","/var/www/html/vendor/symfony/var-exporter/LazyGhostTrait.php:176","/var/www/html/vendor/doctrine/orm/lib/Doctrine/ORM/EntityManager.php:207","/var/www/html/src/Doctrine/Purger/ResetAutoIncrementORMPurger.php:116","/var/www/html/src/Services/ImportExportSystem/PartKeeprImporter/PKImportHelper.php:46","/var/www/html/src/Command/Migrations/ImportPartKeeprCommand.php:82","/var/www/html/vendor/symfony/console/Command/Command.php:326","/var/www/html/vendor/symfony/console/Application.php:1096","/var/www/html/vendor/symfony/framework-bundle/Console/Application.php:126","/var/www/html/vendor/symfony/console/Application.php:324","/var/www/html/vendor/symfony/framework-bundle/Console/Application.php:80","/var/www/html/vendor/symfony/console/Application.php:175","/var/www/html/vendor/symfony/runtime/Runner/Symfony/ConsoleApplicationRunner.php:49","/var/www/html/vendor/autoload_runtime.php:29","/var/www/html/bin/console:18"]}},"level":200,"level_name":"INFO","channel":"deprecation","datetime":"2024-01-10T19:36:23.226401+00:00","extra":{"token":null,"command":{"name":"partdb:migrations:import-partkeepr","arguments":{"command":"partdb:migrations:import-partkeepr","file":"uploads/pk.xml"}}}}
{"message":"User Deprecated: Support for MariaDB < 10.4 is deprecated and will be removed in DBAL 4. Consider upgrading to a more recent version of MariaDB. (AbstractMySQLDriver.php:54 called by AbstractDriverMiddleware.php:68, https://github.com/doctrine/dbal/pull/6110, package doctrine/dbal)","context":{"exception":{"class":"ErrorException","message":"User Deprecated: Support for MariaDB < 10.4 is deprecated and will be removed in DBAL 4. Consider upgrading to a more recent version of MariaDB. (AbstractMySQLDriver.php:54 called by AbstractDriverMiddleware.php:68, https://github.com/doctrine/dbal/pull/6110, package doctrine/dbal)","code":0,"file":"/var/www/html/vendor/doctrine/deprecations/lib/Doctrine/Deprecations/Deprecation.php:210","trace":["/var/www/html/vendor/doctrine/deprecations/lib/Doctrine/Deprecations/Deprecation.php:108","/var/www/html/vendor/doctrine/dbal/src/Driver/AbstractMySQLDriver.php:54","/var/www/html/vendor/doctrine/dbal/src/Driver/Middleware/AbstractDriverMiddleware.php:68","/var/www/html/vendor/doctrine/dbal/src/Driver/Middleware/AbstractDriverMiddleware.php:68","/var/www/html/vendor/doctrine/dbal/src/Driver/Middleware/AbstractDriverMiddleware.php:68","/var/www/html/vendor/doctrine/dbal/src/Connection.php:415","/var/www/html/vendor/doctrine/dbal/src/Connection.php:317","/var/www/html/vendor/doctrine/dbal/src/Connection.php:1315","/var/www/html/var/cache/docker/ContainerLiFNnQ3/App_KernelDockerContainer.php:278","/var/www/html/var/cache/docker/ContainerLiFNnQ3/App_KernelDockerContainer.php:348","/var/www/html/var/cache/docker/ContainerLiFNnQ3/App_KernelDockerContainer.php:312","/var/www/html/vendor/symfony/var-exporter/Internal/LazyObjectState.php:100","/var/www/html/vendor/symfony/var-exporter/LazyGhostTrait.php:176","/var/www/html/vendor/doctrine/orm/lib/Doctrine/ORM/EntityManager.php:207","/var/www/html/src/Doctrine/Purger/ResetAutoIncrementORMPurger.php:116","/var/www/html/src/Services/ImportExportSystem/PartKeeprImporter/PKImportHelper.php:46","/var/www/html/src/Command/Migrations/ImportPartKeeprCommand.php:82","/var/www/html/vendor/symfony/console/Command/Command.php:326","/var/www/html/vendor/symfony/console/Application.php:1096","/var/www/html/vendor/symfony/framework-bundle/Console/Application.php:126","/var/www/html/vendor/symfony/console/Application.php:324","/var/www/html/vendor/symfony/framework-bundle/Console/Application.php:80","/var/www/html/vendor/symfony/console/Application.php:175","/var/www/html/vendor/symfony/runtime/Runner/Symfony/ConsoleApplicationRunner.php:49","/var/www/html/vendor/autoload_runtime.php:29","/var/www/html/bin/console:18"]}},"level":200,"level_name":"INFO","channel":"deprecation","datetime":"2024-01-10T19:36:23.226627+00:00","extra":{"token":null,"command":{"name":"partdb:migrations:import-partkeepr","arguments":{"command":"partdb:migrations:import-partkeepr","file":"uploads/pk.xml"}}}}
{"message":"Error thrown while running command \"partdb:migrations:import-partkeepr 'uploads/pk.xml'\". Message: \"An exception occurred while executing a query: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '-0001-11-30 00:00:00' for column `PartDB`.`attachments`.`datetime_added` at row 1\"","context":{"exception":{"class":"Doctrine\\DBAL\\Exception\\DriverException","message":"An exception occurred while executing a query: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '-0001-11-30 00:00:00' for column `PartDB`.`attachments`.`datetime_added` at row 1","code":1292,"file":"/var/www/html/vendor/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php:117","trace":["/var/www/html/vendor/doctrine/dbal/src/Connection.php:1938","/var/www/html/vendor/doctrine/dbal/src/Connection.php:1880","/var/www/html/vendor/doctrine/dbal/src/Statement.php:194","/var/www/html/vendor/doctrine/dbal/src/Statement.php:249","/var/www/html/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/BasicEntityPersister.php:280","/var/www/html/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php:1182","/var/www/html/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php:446","/var/www/html/vendor/doctrine/orm/lib/Doctrine/ORM/EntityManager.php:403","/var/www/html/src/Services/ImportExportSystem/PartKeeprImporter/PKImportHelperTrait.php:155","/var/www/html/src/Services/ImportExportSystem/PartKeeprImporter/PKDatastructureImporter.php:133","/var/www/html/src/Command/Migrations/ImportPartKeeprCommand.php:126","/var/www/html/src/Command/Migrations/ImportPartKeeprCommand.php:95","/var/www/html/vendor/symfony/console/Command/Command.php:326","/var/www/html/vendor/symfony/console/Application.php:1096","/var/www/html/vendor/symfony/framework-bundle/Console/Application.php:126","/var/www/html/vendor/symfony/console/Application.php:324","/var/www/html/vendor/symfony/framework-bundle/Console/Application.php:80","/var/www/html/vendor/symfony/console/Application.php:175","/var/www/html/vendor/symfony/runtime/Runner/Symfony/ConsoleApplicationRunner.php:49","/var/www/html/vendor/autoload_runtime.php:29","/var/www/html/bin/console:18"],"previous":{"class":"Doctrine\\DBAL\\Driver\\PDO\\Exception","message":"SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '-0001-11-30 00:00:00' for column `PartDB`.`attachments`.`datetime_added` at row 1","code":1292,"file":"/var/www/html/vendor/doctrine/dbal/src/Driver/PDO/Exception.php:28","trace":["/var/www/html/vendor/doctrine/dbal/src/Driver/PDO/Statement.php:132","/var/www/html/vendor/doctrine/dbal/src/Statement.php:190","/var/www/html/vendor/doctrine/dbal/src/Statement.php:249","/var/www/html/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/BasicEntityPersister.php:280","/var/www/html/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php:1182","/var/www/html/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php:446","/var/www/html/vendor/doctrine/orm/lib/Doctrine/ORM/EntityManager.php:403","/var/www/html/src/Services/ImportExportSystem/PartKeeprImporter/PKImportHelperTrait.php:155","/var/www/html/src/Services/ImportExportSystem/PartKeeprImporter/PKDatastructureImporter.php:133","/var/www/html/src/Command/Migrations/ImportPartKeeprCommand.php:126","/var/www/html/src/Command/Migrations/ImportPartKeeprCommand.php:95","/var/www/html/vendor/symfony/console/Command/Command.php:326","/var/www/html/vendor/symfony/console/Application.php:1096","/var/www/html/vendor/symfony/framework-bundle/Console/Application.php:126","/var/www/html/vendor/symfony/console/Application.php:324","/var/www/html/vendor/symfony/framework-bundle/Console/Application.php:80","/var/www/html/vendor/symfony/console/Application.php:175","/var/www/html/vendor/symfony/runtime/Runner/Symfony/ConsoleApplicationRunner.php:49","/var/www/html/vendor/autoload_runtime.php:29","/var/www/html/bin/console:18"],"previous":{"class":"PDOException","message":"SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '-0001-11-30 00:00:00' for column `PartDB`.`attachments`.`datetime_added` at row 1","code":22007,"file":"/var/www/html/vendor/doctrine/dbal/src/Driver/PDO/Statement.php:130","trace":["/var/www/html/vendor/doctrine/dbal/src/Driver/PDO/Statement.php:130","/var/www/html/vendor/doctrine/dbal/src/Statement.php:190","/var/www/html/vendor/doctrine/dbal/src/Statement.php:249","/var/www/html/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/BasicEntityPersister.php:280","/var/www/html/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php:1182","/var/www/html/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php:446","/var/www/html/vendor/doctrine/orm/lib/Doctrine/ORM/EntityManager.php:403","/var/www/html/src/Services/ImportExportSystem/PartKeeprImporter/PKImportHelperTrait.php:155","/var/www/html/src/Services/ImportExportSystem/PartKeeprImporter/PKDatastructureImporter.php:133","/var/www/html/src/Command/Migrations/ImportPartKeeprCommand.php:126","/var/www/html/src/Command/Migrations/ImportPartKeeprCommand.php:95","/var/www/html/vendor/symfony/console/Command/Command.php:326","/var/www/html/vendor/symfony/console/Application.php:1096","/var/www/html/vendor/symfony/framework-bundle/Console/Application.php:126","/var/www/html/vendor/symfony/console/Application.php:324","/var/www/html/vendor/symfony/framework-bundle/Console/Application.php:80","/var/www/html/vendor/symfony/console/Application.php:175","/var/www/html/vendor/symfony/runtime/Runner/Symfony/ConsoleApplicationRunner.php:49","/var/www/html/vendor/autoload_runtime.php:29","/var/www/html/bin/console:18"]}}},"command":"partdb:migrations:import-partkeepr 'uploads/pk.xml'","message":"An exception occurred while executing a query: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '-0001-11-30 00:00:00' for column `PartDB`.`attachments`.`datetime_added` at row 1"},"level":500,"level_name":"CRITICAL","channel":"console","datetime":"2024-01-10T19:36:29.905710+00:00","extra":{"token":null,"command":{"name":"partdb:migrations:import-partkeepr","arguments":{"command":"partdb:migrations:import-partkeepr","file":"uploads/pk.xml"}}}}
19:36:29 CRITICAL  [console] Error thrown while running command "partdb:migrations:import-partkeepr 'uploads/pk.xml'". Message: "An exception occurred while executing a query: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '-0001-11-30 00:00:00' for column `PartDB`.`attachments`.`datetime_added` at row 1" ["exception" => Doctrine\DBAL\Exception\DriverException^ { …},"command" => "partdb:migrations:import-partkeepr 'uploads/pk.xml'","message" => "An exception occurred while executing a query: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '-0001-11-30 00:00:00' for column `PartDB`.`attachments`.`datetime_added` at row 1"] ["token" => null,"command" => ["name" => "partdb:migrations:import-partkeepr","arguments" => ["command" => "partdb:migrations:import-partkeepr","file" => "uploads/pk.xml"]]]
{"message":"Command \"partdb:migrations:import-partkeepr 'uploads/pk.xml'\" exited with code \"1292\"","context":{"command":"partdb:migrations:import-partkeepr 'uploads/pk.xml'","code":1292},"level":100,"level_name":"DEBUG","channel":"console","datetime":"2024-01-10T19:36:29.914417+00:00","extra":{"token":null,"command":{"name":"partdb:migrations:import-partkeepr","arguments":{"command":"partdb:migrations:import-partkeepr","file":"uploads/pk.xml"}}}}

In ExceptionConverter.php line 117:

  An exception occurred while executing a query: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '-0001-11-30 00:00:  
  00' for column `PartDB`.`attachments`.`datetime_added` at row 1                                                                              

In Exception.php line 28:

  SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '-0001-11-30 00:00:00' for column `PartDB`.`attachments`.`datetime  
  _added` at row 1                                                                                                                             

In Statement.php line 130:

  SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '-0001-11-30 00:00:00' for column `PartDB`.`attachments`.`datetime  
  _added` at row 1                                                                                                                             

partdb:migrations:import-partkeepr [--no-projects] [--import-users] [--] <file>

[www-data@4cc4b269ea1d:~/html]

I hope you can help!

jbtronics commented 9 months ago

I would suspect you either has some very odd dates in your database or the dates in the XML file are wrongly formatted? How have you generated the XML file?

It would be very helpful for debugging, if you could send me your database XML file (you can censor password hashes, etc.)

nilsht commented 9 months ago

pk.xml.txt

Attached (I hope), and thanks :-)

nilsht commented 9 months ago

And I forgot: It was created with mysqldump as in the docs

jbtronics commented 9 months ago

I fixed this problem and some other flaws i found in the importer. It is available in the master branch. In a direct installation do a pull and checkout the master branch, on docker, change your configuration to use the jbtronics/part-db1:master image and do a pull and restart. (It might take a while until the docker images with the changes are build).

However you will need to change something in your XML file, as two parts has the same internal part number 35, which is not allowed in Part-DB. You have to either change that in PartKeepr before or open the xml file in a text editor and search for internalPartNumber">35. You should find change two lines. Change one of them to a unique value, or just remove the value completly. Then import should work fine.

nilsht commented 9 months ago

Worked perfectly! Thank you.