When a database dump is not a complete dump ...
Fri, 10 Aug 2018 18:18:01 +0200
Serious? Are you fucking kidding me? #SQLite3 #Drupal #WTF
With a bit python it was possible to transform a Druapl 8's sqlite3 dump into a sql file that I could import into MariaDB 10.3. Surprisingly the whole multi domain and multi language website seemed to work quite fine in frontend and backend on a first and second look. Just the dblog is filling with unserialize errors from the key value store.
Looks like there are entries which do not get out of sqlite3 correctly. Neither through dump, nor cli.
How to get a complete dump from the sqlite3 database?
Where has my beloved TYPO3 gone?
With a bit python it was possible to transform a Druapl 8's sqlite3 dump into a sql file that I could import into MariaDB 10.3. Surprisingly the whole multi domain and multi language website seemed to work quite fine in frontend and backend on a first and second look. Just the dblog is filling with unserialize errors from the key value store.
Looks like there are entries which do not get out of sqlite3 correctly. Neither through dump, nor cli.
A sqlite3 database dump
$ sqlite3 sites/default/files/.ht.sqlite .dump | grep user.entity_type
INSERT INTO "key_value" VALUES('entity.definitions.installed','user.entity_type','O:36:"Drupal\Core\Entity\ContentEntityType":37:{s:15:"');
Query the database directly with the sqlite3 cli
$ sqlite3 sites/default/files/.ht.sqlite "SELECT * FROM key_value WHERE name = 'user.entity_type'"
entity.definitions.installed|user.entity_type|O:36:"Drupal\Core\Entity\ContentEntityType":37:{s:15:"
Actually this is the same like the sqlite3 cli
$ ../bin/drush sql-query "SELECT * FROM key_value WHERE name = 'user.entity_type'"
entity.definitions.installed|user.entity_type|O:36:"Drupal\Core\Entity\ContentEntityType":37:{s:15:"
Is this Drupal 8 Magic? :-!
$ ../bin/drush php-eval "\$r=db_query('SELECT * FROM key_value WHERE name = \"user.entity_type\"')->fetchAll(); print_r(\$r[0]->value);"
O:36:"Drupal\Core\Entity\ContentEntityType":37:{s:15:"*static_cache";b:1;s:15:"*render_cache";b:1;s:19:"*persistent_cache";b:1;s:14:"*entity_keys";a:6:{s:2:"id";s:3:"uid";s:8:"langcode";s:8:"langcode";s:4:"uuid";s:4:"uuid";s:8:"revision";s:0:"";s:6:"bundle";s:0:"";s:16:"default_langcode";s:16:"default_langcode";}s:5:"*id";s:4:"user";s:16:"*originalClass";s:23:"Drupal\user\Entity\User";s:11:"*handlers";a:9:{s:7:"storage";s:23:"Drupal\user\UserStorage";s:14:"storage_schema";s:29:"Drupal\user\UserStorageSchema";s:6:"access";s:36:"Drupal\user\UserAccessControlHandler";s:12:"list_builder";s:27:"Drupal\user\UserListBuilder";s:10:"views_data";s:25:"Drupal\user\UserViewsData";s:14:"route_provider";a:1:{s:4:"html";s:36:"Drupal\user\Entity\UserRouteProvider";}s:4:"form";a:3:{s:7:"default";s:23:"Drupal\user\ProfileForm";s:6:"cancel";s:31:"Drupal\user\Form\UserCancelForm";s:8:"register";s:24:"Drupal\user\RegisterForm";}s:11:"translation";s:37:"Drupal\user\ProfileTranslationHandler";s:12:"view_builder";s:36:"Drupal\Core\Entity\EntityViewBuilder";}s:19:"*admin_permission";s:16:"administer users";s:25:"*permission_granularity";s:11:"entity_type";s:8:"*links";a:4:{s:9:"canonical";s:12:"/user/{user}";s:9:"edit-form";s:17:"/user/{user}/edit";s:11:"cancel-form";s:19:"/user/{user}/cancel";s:10:"collection";s:13:"/admin/people";}s:17:"*label_callback";s:16:"user_format_name";s:21:"*bundle_entity_type";N;s:12:"*bundle_of";N;s:15:"*bundle_label";N;s:13:"*base_table";s:5:"users";s:22:"*revision_data_table";N;s:17:"*revision_table";N;s:13:"*data_table";s:16:"users_field_data";s:15:"*translatable";b:1;s:19:"*show_revision_ui";b:0;s:8:"*label";O:48:"Drupal\Core\StringTranslation\TranslatableMarkup":3:{s:9:"*string";s:4:"User";s:12:"*arguments";a:0:{}s:10:"*options";a:0:{}}s:19:"*label_collection";s:0:"";s:17:"*label_singular";s:0:"";s:15:"*label_plural";s:0:"";s:14:"*label_count";a:0:{}s:15:"*uri_callback";N;s:8:"*group";s:7:"content";s:14:"*group_label";O:48:"Drupal\Core\StringTranslation\TranslatableMarkup":3:{s:9:"*string";s:7:"Content";s:12:"*arguments";a:0:{}s:10:"*options";a:1:{s:7:"context";s:17:"Entity type group";}}s:22:"*field_ui_base_route";s:22:"entity.user.admin_form";s:26:"*common_reference_target";b:1;s:22:"*list_cache_contexts";a:0:{}s:18:"*list_cache_tags";a:1:{i:0;s:9:"user_list";}s:14:"*constraints";a:1:{s:13:"EntityChanged";N;}s:13:"*additional";a:0:{}s:8:"*class";s:23:"Drupal\user\Entity\User";s:11:"*provider";s:4:"user";s:20:"*stringTranslation";N;}How to get a complete dump from the sqlite3 database?
Where has my beloved TYPO3 gone?
Fri, 17 Aug 2018 18:17:43 +0200
TYPO3 is still very alive and made huge progress in the last years to renew itself. I really love TYPO3 and how it works and structures its data. Just the TYPO3 projects seem to vanish and have to maintain such stupid Wordpress and Drupal projects recently.
I have found a solution to the problem and have a successful Drupal8 migration from SQLite to MariaDB.
The problem seems to be related to SQLite's dynamic type system, where the value to store in a column determines its data type and not the column's data type. So Drupal stores a string with non UTF8 characters in a BLOB field and SQLite treats it as a TEXT data type. The sqlite3 CLI select and dump commands fail with the non UTF8 characters in the TEXT. I could not find a way how to dump or export the tables with sqlite3 itself.
Luckily there is a small tool which automatically exports the problematic fields as a BLOB instead of TEXT like the sqlite3 CLI. I have no idea how it does this and why sqlite3 does not provide such a feature, but with the export from DB Browser for SQLite I had a complete dump of the data and could import it into MariaDB.
#^DB Browser for SQLite
In the end it took 3 different tools to migrate the database, but now everything looks quite fine.
I have found a solution to the problem and have a successful Drupal8 migration from SQLite to MariaDB.
The problem seems to be related to SQLite's dynamic type system, where the value to store in a column determines its data type and not the column's data type. So Drupal stores a string with non UTF8 characters in a BLOB field and SQLite treats it as a TEXT data type. The sqlite3 CLI select and dump commands fail with the non UTF8 characters in the TEXT. I could not find a way how to dump or export the tables with sqlite3 itself.
Luckily there is a small tool which automatically exports the problematic fields as a BLOB instead of TEXT like the sqlite3 CLI. I have no idea how it does this and why sqlite3 does not provide such a feature, but with the export from DB Browser for SQLite I had a complete dump of the data and could import it into MariaDB.
#^DB Browser for SQLite
DB Browser for SQLite is a high quality, visual, open source tool to create, design, and edit database files compatible with SQLite.
It is for users and developers wanting to create databases, search, and edit data. It uses a familiar spreadsheet-like interface, and you don't need to learn complicated SQL commands.
In the end it took 3 different tools to migrate the database, but now everything looks quite fine.
Fri, 17 Aug 2018 23:45:48 +0200 last edited: Sat, 18 Aug 2018 02:24:58 +0200
sqlite 3 has 3 datatypes (well really 2 or 2.5 lol because of floats)
also has its own record id which may or may not be used by the application, but its there and is the index. if you think the index is that autoindex field you created, it's not. it's only there to appease you. :)
i'm not sure i would try a dump from sqlite to sql for import especially into another non standard sql server like mariahdb. but maybe it works? probably want to write a shim go between in php or something
also has its own record id which may or may not be used by the application, but its there and is the index. if you think the index is that autoindex field you created, it's not. it's only there to appease you. :)
i'm not sure i would try a dump from sqlite to sql for import especially into another non standard sql server like mariahdb. but maybe it works? probably want to write a shim go between in php or something
This website is tracked using the Piwik analytics tool. If you do not want that your visits are logged this way you can set a cookie to prevent Piwik from tracking further visits of the site (opt-out).