Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Converting a mysql db used through a dedicated server? (akonadi) #933

Closed
RJVB opened this issue Apr 8, 2019 · 13 comments
Closed

Converting a mysql db used through a dedicated server? (akonadi) #933

RJVB opened this issue Apr 8, 2019 · 13 comments
Labels
How To... Question

Comments

@RJVB
Copy link

RJVB commented Apr 8, 2019 •
edited
Loading

Hi,

All documentation I've found so far that relates to converting mysql databases assumes that the database is provided through a central server. What about applications (like KDE's akonadi) that fire up their own server?

I tried using the same base directory given to the mysql server but only got this far:

> pgloader -v mysql:///path/to/.local/share/akonadi postgresql:///akonadi-bertin
2019-04-08T09:27:24.075000Z NOTICE Starting pgloader, log system is ready.

Idem when I try via the server's socket:

> pgloader -v mysql://unix:/path/to/mysql.socket/akonadi postgresql:///akonadi-bertin
2019-04-08T09:42:14.087000Z NOTICE Starting pgloader, log system is ready.

FWIW this is the mysqld server command:

/usr/sbin/mysqld --defaults-file=/path/to/.local/share/akonadi/mysql.conf --datadir=/path/to/.local/share/akonadi/db_data/ --socket=/path/to/mysql.socket

This goes further but apparently fails:

> pgloader -v mysql://unix:/path/to/mysql.socket:3306/akonadi postgresql:///akonadi-bertin
2019-04-08T09:44:38.136000Z NOTICE Starting pgloader, log system is ready.
2019-04-08T09:44:38.417000Z LOG Migrating from #<MYSQL-CONNECTION mysql://bertin@unix:/path/to/mysql.socket:3306/akonadi {100A397BB3}>
2019-04-08T09:44:38.436000Z LOG Migrating into #<PGSQL-CONNECTION pgsql://bertin@UNIX:5432/akonadi-bertin {100A5BEFC3}>
2019-04-08T09:44:39.713000Z NOTICE Prepare PostgreSQL database.
KABOOM!
INFO: Control stack guard page unprotected
Control stack guard page temporarily disabled: proceed with caution

What I am doing here?

Control stack exhausted (no more space for function call frames).
This is probably due to heavily nested or infinitely recursive function
calls, or a tail call that SBCL cannot or has not optimized away.

PROCEED WITH CAUTION.

Exit 1

I saw that same error when trying to convert the KRecipes database, which is provided through the central mysqld .

Am I doing something wrong or "SooL"?

EDIT: I'm (blissfully?) unaware of almost all hairy details of database use, management etc; I just use them through applications which offer several backends and am trying to follow migration instructions.
@dimitri
Copy link
Owner

dimitri commented Apr 13, 2019

The latest error you had is promising: it seems like pgloader was able to connect to the source database and began working on it. Can you try again with --debug as a command line option, and maybe with different settings for the batch size: see prefetch rows = 1000 in the manual at https://pgloader.readthedocs.io/en/latest/pgloader.html#with.

@RJVB
Copy link
Author

RJVB commented Apr 14, 2019

Using a KRecipes db which is much smaller, served by the central mysql server but gives the same error (and which contains nothing I couldn't share). I tried varying the rows prefetch but it doesn't seem to have any effect (not even on the memory use reported by the shell if I dial it way back to 10 rows).

> pgloader --debug -v --with "prefetch rows = 1000000" mysql://bertin:XXXXXX@localhost/Krecipes postgresql:///Krecipes
sb-impl::*default-external-format* :UTF-8
tmpdir: #P"/tmp/pgloader/"
2019-04-14T08:53:29.147000Z NOTICE Starting pgloader, log system is ready.
2019-04-14T08:53:29.253000Z INFO Starting monitor
2019-04-14T08:53:29.298000Z INFO     SOURCE: "mysql://bertin:XXXXXX@localhost/Krecipes"
2019-04-14T08:53:29.300000Z INFO SOURCE URI: #<PGLOADER.SOURCE.MYSQL:MYSQL-CONNECTION mysql://bertin@localhost:3306/Krecipes {100A397EA3}>
2019-04-14T08:53:29.300000Z INFO     TARGET: "postgresql:///Krecipes"
2019-04-14T08:53:29.302000Z INFO TARGET URI: #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://bertin@UNIX:5432/Krecipes {100A5B7363}>
2019-04-14T08:53:29.302000Z DEBUG LOAD DATA FROM #<PGLOADER.SOURCE.MYSQL:MYSQL-CONNECTION mysql://bertin@localhost:3306/Krecipes {100A397EA3}>
2019-04-14T08:53:29.303000Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://bertin@UNIX:5432/Krecipes {100A5B7363}>
2019-04-14T08:53:29.303000Z DEBUG SET client_encoding TO 'utf8'
2019-04-14T08:53:29.304000Z DEBUG SET application_name TO 'pgloader'
2019-04-14T08:53:29.377000Z LOG Migrating from #<MYSQL-CONNECTION mysql://bertin@localhost:3306/Krecipes {100A397EA3}>
2019-04-14T08:53:29.378000Z LOG Migrating into #<PGSQL-CONNECTION pgsql://bertin@UNIX:5432/Krecipes {100A5B7363}>
2019-04-14T08:53:29.686000Z DEBUG CONNECTED TO #<MYSQL-CONNECTION mysql://bertin@localhost:3306/Krecipes {100A397EA3}>
2019-04-14T08:53:29.713000Z SQL MySQL: sending query: -- params: db-name
--         table-type-name
--         only-tables
--         only-tables
--         including
--         filter-list-to-where-clause incuding
--         excluding
--         filter-list-to-where-clause excluding
  select c.table_name, t.table_comment,
         c.column_name, c.column_comment,
         c.data_type, c.column_type, c.column_default,
         c.is_nullable, c.extra
    from information_schema.columns c
         join information_schema.tables t using(table_schema, table_name)
   where c.table_schema = 'Krecipes' and t.table_type = 'BASE TABLE'
         
         
         
order by table_name, ordinal_position;
2019-04-14T08:53:29.857000Z SQL MySQL: sending query: -- params: db-name
--         table-type-name
--         only-tables
--         only-tables
--         including
--         filter-list-to-where-clause incuding
--         excluding
--         filter-list-to-where-clause excluding
SELECT s.table_name, s.constraint_name, s.ft, s.cols, s.fcols,
       rc.update_rule, rc.delete_rule

FROM
 (
  SELECT tc.table_schema, tc.table_name,
         tc.constraint_name, k.referenced_table_name ft,

             group_concat(         k.column_name
                          order by k.ordinal_position) as cols,

             group_concat(         k.referenced_column_name
                          order by k.position_in_unique_constraint) as fcols

        FROM information_schema.table_constraints tc

        LEFT JOIN information_schema.key_column_usage k
               ON k.table_schema = tc.table_schema
              AND k.table_name = tc.table_name
              AND k.constraint_name = tc.constraint_name

      WHERE     tc.table_schema = 'Krecipes'
            AND k.referenced_table_schema = 'Krecipes'
            AND tc.constraint_type = 'FOREIGN KEY'
           
           
           

   GROUP BY tc.table_schema, tc.table_name, tc.constraint_name, ft
 ) s
             JOIN information_schema.referential_constraints rc
               ON rc.constraint_schema = s.table_schema
              AND rc.constraint_name = s.constraint_name
              AND rc.table_name = s.table_name;
2019-04-14T08:53:29.889000Z SQL MySQL: sending query: -- params: db-name
--         table-type-name
--         only-tables
--         only-tables
--         including
--         filter-list-to-where-clause incuding
--         excluding
--         filter-list-to-where-clause excluding
  SELECT table_name, index_name, index_type,
         sum(non_unique),
         cast(GROUP_CONCAT(column_name order by seq_in_index) as char)
    FROM information_schema.statistics
   WHERE table_schema = 'Krecipes'
         
         
         
GROUP BY table_name, index_name, index_type;
2019-04-14T08:53:31.258000Z NOTICE Prepare PostgreSQL database.
2019-04-14T08:53:31.289000Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://bertin@UNIX:5432/Krecipes {100A5B7363}>
2019-04-14T08:53:31.289000Z DEBUG SET client_encoding TO 'utf8'
2019-04-14T08:53:31.290000Z DEBUG SET application_name TO 'pgloader'
2019-04-14T08:53:31.313000Z DEBUG BEGIN
KABOOM!
INFO: Control stack guard page unprotected
Control stack guard page temporarily disabled: proceed with caution
2019-04-14T08:53:34.437000Z INFO Stopping monitor

What I am doing here?

Control stack exhausted (no more space for function call frames).
This is probably due to heavily nested or infinitely recursive function
calls, or a tail call that SBCL cannot or has not optimized away.

PROCEED WITH CAUTION.

@dimitri
Copy link
Owner

dimitri commented Apr 14, 2019

Which version of pgloader are you using? It might be old enough (pre 3.6.x times) that upgrading or using a fresh compile from source is going to make a difference here?

@RJVB
Copy link
Author

RJVB commented Apr 15, 2019 via email

@RJVB
Copy link
Author

RJVB commented Apr 15, 2019 via email

@dimitri
Copy link
Owner

dimitri commented Apr 15, 2019

Looks like you're trying to compile with a pretty-old SBCL version now. Which version is this? sbcl --version will tell you... and it's easy to install a newer one either from http://www.sbcl.org or from your distribution/OS packaging system.

@RJVB
Copy link
Author

RJVB commented Apr 16, 2019 via email

@RJVB
Copy link
Author

RJVB commented Apr 16, 2019 via email

@RJVB
Copy link
Author

RJVB commented Apr 16, 2019 via email

@dimitri
Copy link
Owner

dimitri commented Apr 17, 2019

That last error Symbol "CLOSE-MEMO" not found in the DB3 package is because I just made changes in cl-db3 (a companion library used by pgloader) that are not shipped yet in Quicklisp. If you're building from source, make clean then make again. If you're building from the release bundle please see #940.

@RJVB
Copy link
Author

RJVB commented Apr 17, 2019 via email

@RJVB
Copy link
Author

RJVB commented Apr 18, 2019 via email

@dimitri
Copy link
Owner

dimitri commented Apr 18, 2019

The following error is a case sensitive error. It happens because pgloader doesn't quote the database name, and I think it should. Now, the impact of failing this step is minimal to non-existent, it's a convenience that you might not need after all, depending on how the application is coded.

NOTICE ALTER DATABASE Krecipes SET search_path TO public, krecipes;

The last line of the summary shows no errors so you're good to go, everything went fine. Now I'll keep that issue open for me to remember about adding support for database level GUCs when migrating from PostgreSQL to PostgreSQL, sounds useful, and I should also fix the quoting here of course.

@JeffJankowski JeffJankowski mentioned this issue Apr 29, 2019
Closed
4 tasks
@cpwnd cpwnd mentioned this issue May 7, 2019
Closed
5 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
How To... Question
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@dimitri @RJVB