Hi,
is it possible to migrate a mailman3-mysql-database to a postgresql-database?
I tried to convert it with pgloader, but some identifiers had to be cut off: _address_id); 2018-03-17T21:33:39.087000+01:00 WARNING PostgreSQL warning: Bezeichner idx_680560_account_emailconfirm_email_address_id_5b7f8c58_fk_account_e wird auf idx_680560_account_emailconfirm_email_address_id_5b7f8c58_fk_ac gekrzt
Kind regards Bjoern
On 03/17/2018 01:44 PM, Bjoern Franke wrote:
Hi,
is it possible to migrate a mailman3-mysql-database to a postgresql-database?
I tried to convert it with pgloader, but some identifiers had to be cut off: _address_id); 2018-03-17T21:33:39.087000+01:00 WARNING PostgreSQL warning: Bezeichner idx_680560_account_emailconfirm_email_address_id_5b7f8c58_fk_account_e wird auf idx_680560_account_emailconfirm_email_address_id_5b7f8c58_fk_ac gekrzt
I suspect the issue is due to the way Mailman supports different databases via SQLAlchemy. The bottom line is the table and column specifications are based on the Mailman database models, but are database specific.
If I were doing this, I would dump the data from MySQL, but rather than using the CREATE TABLE queries from the dump, I would let Mailman create a new PostgreSQL database and then just load the existing tables from the MySQL dump.
However, that may all be irrelevant. I don't know which table/column that specific item comes from, but it appears just visually that
idx_680560_account_emailconfirm_email_address_id_5b7f8c58_fk_account_e
is already truncated so further truncating it to
idx_680560_account_emailconfirm_email_address_id_5b7f8c58_fk_ac
may not be a problem.
-- Mark Sapiro <mark@msapiro.net> The highway is for gamblers, San Francisco Bay Area, California better use your sense - B. Dylan
Hi,
I tried to convert it with pgloader, but some identifiers had to be cut off: _address_id); 2018-03-17T21:33:39.087000+01:00 WARNING PostgreSQL warning: Bezeichner idx_680560_account_emailconfirm_email_address_id_5b7f8c58_fk_account_e wird auf idx_680560_account_emailconfirm_email_address_id_5b7f8c58_fk_ac gekrzt
I suspect the issue is due to the way Mailman supports different databases via SQLAlchemy. The bottom line is the table and column specifications are based on the Mailman database models, but are database specific.
Now with the via pgloader converted db I get:
File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context context) File "/usr/lib/python3/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) FEHLER: Operator existiert nicht: character = uuid LINE 4: WHERE "user"._user_id = 'd254e5dc-188a-48de-8b99-3cbe81155c1... ^ HINT: Kein Operator stimmt mit dem angegebenen Namen und den Argumenttypen überein. Sie müssen möglicherweise ausdrückliche Typumwandlungen hinzufügen. [SQL: 'SELECT count(*) AS count_1 \nFROM (SELECT "user".password AS user_password, "user".id AS user_id, "user".display_name AS user_display_name, "user"._user_id AS user__user_id, "user"._created_on AS user__created_on, "user".is_server_owner AS user_is_server_owner, "user"._preferred_address_id AS user__preferred_address_id, "user".preferences_id AS user_preferences_id \nFROM "user" \nWHERE "user"._user_id = %(_user_id_1)s) AS anon_1'] [parameters: {'_user_id_1': UUID('d254e5dc-188a-48de-8b99-3cbe81155c11')}] Jun 14 12:30:43 2018 (22147) 127.0.0.1 - - "GET /3.0/users/279578694160539064315719588335836290065/addresses HTTP/1.1" 500 59 root@srv20 /etc/mailman3 #
If I were doing this, I would dump the data from MySQL, but rather than using the CREATE TABLE queries from the dump, I would let Mailman create a new PostgreSQL database and then just load the existing tables from the MySQL dump.
Unfortunately that did not work due to some missing indexes.
Regards Bjoern
participants (2)
-
Bjoern Franke -
Mark Sapiro