MySQL->Postgres database converter strange behaviour
8 views
Skip to first unread message
Spadajspadaj
Mar 11, 2020, 3:26:27 PM3/11/20
to bareos-users
Hello.
I've been trying to migrate my setup from MySQL to Postgres using the
bareos-dbcopy utility. It is almost working. Almost, because it copies
only one record from each table.
I ran it with strace and it seems that it's not me, it's him ;-)
Strace excerpt from the File table conversion:
write(1, "====== table File ======\n", 25====== table File ======
) = 25
write(1, "--> checking destination table...\n", 34--> checking
destination table...
) = 34
sendto(4, "Q\0\0\0\37SELECT * FROM File LIMIT 1\0", 32, MSG_NOSIGNAL,
NULL, 0) = 32
poll([{fd=4, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=4, revents=POLLIN}])
recvfrom(4,
"T\0\0\1\27\0\vfileid\0\0\0C\34\0\1\0\0\0\24\0\10\377\377\377\377\0\0fileindex\0\0\0C\34\0\2\
0\0\0\27\0\4\377\377\377\377\0\0jobid\0\0\0C\34\0\3\0\0\0\27\0\4\377\377\377\377\0\0pathid\0\0\0C\34\0\4\
0\0\0\27\0\4\377\377\377\377\0\0deltaseq\0\0\0C\34\0\5\0\0\0\25\0\2\377\377\377\377\0\0markid\0\0\0C\34\0
\6\0\0\0\27\0\4\377\377\377\377\0\0fhinfo\0\0\0C\34\0\7\0\0\6\244\377\377\0\24\0\4\0\0fhnode\0\0\0C\34\0\
10\0\0\6\244\377\377\0\24\0\4\0\0lstat\0\0\0C\34\0\t\0\0\0\31\377\377\377\377\377\377\0\0md5\0\0\0C\34\0\
n\0\0\0\31\377\377\377\377\377\377\0\0name\0\0\0C\34\0\v\0\0\0\31\377\377\377\377\377\377\0\0C\0\0\0\rSEL
ECT 0\0Z\0\0\0\5I", 16384, 0, NULL, NULL) = 300
sendto(4, "Q\0\0\0\nBEGIN\0", 11, MSG_NOSIGNAL, NULL, 0) = 11
poll([{fd=4, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=4, revents=POLLIN}])
recvfrom(4, "C\0\0\0\nBEGIN\0Z\0\0\0\5T", 16384, 0, NULL, NULL) = 17
write(1, "--> copying...\n", 15--> copying...
) = 15
poll([{fd=3, events=POLLIN|POLLPRI}], 1, 0) = 0 (Timeout)
write(3, "\204\0\0\0\3SELECT `DeltaSeq`, `Fhinfo`, `Fhnode`, `FileId`,
`FileIndex`, `JobId`, `LStat`, `MD
5`, `MarkId`, `Name`, `PathId` FROM File LIMIT 1", 136) = 136
read(3,
"\1\0\0\1\v4\0\0\2\3def\6bareos\4File\4File\10DeltaSeq\10DeltaSeq\f?\0\5\0\0\0\2
\0\0\0\0000\0\0\
3\3def\6bareos\4File\4File\6Fhinfo\6Fhinfo\f?\0\25\0\0\0\366\0\0\0\0\0000\0\0\4\3def\6bareos\4File\4File\
6Fhnode\6Fhnode\f?\0\25\0\0\0\366\0\0\0\0\0000\0\0\5\3def\6bareos\4File\4File\6FileId\6FileId\f?\0\24\0\0
\0\10#B\0\0\0006\0\0\6\3def\6bareos\4File\4File\tFileIndex\tFileIndex\f?\0\n\0\0\0\3
@\0\0\0.\0\0\7\3def\
6bareos\4File\4File\5JobId\5JobId\f?\0\n\0\0\0\3)P\0\0\0.\0\0\10\3def\6bareos\4File\4File\5LStat\5LStat\f
?\0\377\0\0\0\374\221\20\0\0\0*\0\0\t\3def\6bareos\4File\4File\3MD5\3MD5\f?\0\377\0\0\0\374\221\20\0\0\00
00\0\0\n\3def\6bareos\4File\4File\6MarkId\6MarkId\f?\0\n\0\0\0\3
\0\0\0\0,\0\0\v\3def\6bareos\4File\4File
\4Name\4Name\f?\0\377\377\0\0\374\221P\0\0\0000\0\0\f\3def\6bareos\4File\4File\6PathId\6PathId\f?\0\n\0\0
\0\3)P\0\0\0\5\0\0\r\376\0\0\"\0t\0\0\16\0010\0010\0010\01020815008\0011\00423492A
A IH/ B A A CA eK A A
BXgj8z Bx9k46 Bbgeq5 A A
L\26oFI6qIvho1W8LctK9rWsqQ\0010\fkarnawal.tex\0043097\5\0\0\17\376\0\0\"\0",
163
84) = 711
sendto(4, "Q\0\0\0\377INSERT INTO File (deltaseq, fhinfo, fhnode,
fileid, fileindex, jobid, lstat, md5, m
arkid, name, pathid) VALUES ('0','0','0','20815008','1','2349','A A IH/
B A A CA eK A A BXgj8z Bx9k46 Bbg
eq5 A A L','oFI6qIvho1W8LctK9rWsqQ','0','karnawal.tex','3097')\0", 256,
MSG_NOSIGNAL, NULL, 0) = 256
poll([{fd=4, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=4, revents=POLLIN}])
recvfrom(4, "C\0\0\0\17INSERT 0 1\0Z\0\0\0\5T", 16384, 0, NULL, NULL) = 22
write(1, "--> updating sequence\n", 22--> updating sequence
) = 22
sendto(4, "Q\0\0\0Fselect setval(' file_fileid_seq', (select max(fileid)
from file))\0", 71, MSG_NOSIGNAL
, NULL, 0) = 71
poll([{fd=4, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=4, revents=POLLIN}])
recvfrom(4,
"T\0\0\0\37\0\1setval\0\0\0\0\0\0\0\0\0\0\24\0\10\377\377\377\377\0\0D\0\0\0\22\0\1\0\0\0\010
20815008C\0\0\0\rSELECT 1\0Z\0\0\0\5T", 16384, 0, NULL, NULL) = 71
sendto(4, "Q\0\0\0\vCOMMIT\0", 12, MSG_NOSIGNAL, NULL, 0) = 12
poll([{fd=4, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=4, revents=POLLIN}])
recvfrom(4, "C\0\0\0\vCOMMIT\0Z\0\0\0\5I", 16384, 0, NULL, NULL) = 18
write(1, "--> success\n", 12--> success
) = 12
As you can see, it does SELECT with LIMIT 1 so there's no way it's gonna
migrate more entries that one. Or I'm missing something here.
Anyone encountered something similar?
I'm using the 19.2.6 release from Centos 7 RPM packets.
Best regards,
MK
I've been trying to migrate my setup from MySQL to Postgres using the
bareos-dbcopy utility. It is almost working. Almost, because it copies
only one record from each table.
I ran it with strace and it seems that it's not me, it's him ;-)
Strace excerpt from the File table conversion:
write(1, "====== table File ======\n", 25====== table File ======
) = 25
write(1, "--> checking destination table...\n", 34--> checking
destination table...
) = 34
sendto(4, "Q\0\0\0\37SELECT * FROM File LIMIT 1\0", 32, MSG_NOSIGNAL,
NULL, 0) = 32
poll([{fd=4, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=4, revents=POLLIN}])
recvfrom(4,
"T\0\0\1\27\0\vfileid\0\0\0C\34\0\1\0\0\0\24\0\10\377\377\377\377\0\0fileindex\0\0\0C\34\0\2\
0\0\0\27\0\4\377\377\377\377\0\0jobid\0\0\0C\34\0\3\0\0\0\27\0\4\377\377\377\377\0\0pathid\0\0\0C\34\0\4\
0\0\0\27\0\4\377\377\377\377\0\0deltaseq\0\0\0C\34\0\5\0\0\0\25\0\2\377\377\377\377\0\0markid\0\0\0C\34\0
\6\0\0\0\27\0\4\377\377\377\377\0\0fhinfo\0\0\0C\34\0\7\0\0\6\244\377\377\0\24\0\4\0\0fhnode\0\0\0C\34\0\
10\0\0\6\244\377\377\0\24\0\4\0\0lstat\0\0\0C\34\0\t\0\0\0\31\377\377\377\377\377\377\0\0md5\0\0\0C\34\0\
n\0\0\0\31\377\377\377\377\377\377\0\0name\0\0\0C\34\0\v\0\0\0\31\377\377\377\377\377\377\0\0C\0\0\0\rSEL
ECT 0\0Z\0\0\0\5I", 16384, 0, NULL, NULL) = 300
sendto(4, "Q\0\0\0\nBEGIN\0", 11, MSG_NOSIGNAL, NULL, 0) = 11
poll([{fd=4, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=4, revents=POLLIN}])
recvfrom(4, "C\0\0\0\nBEGIN\0Z\0\0\0\5T", 16384, 0, NULL, NULL) = 17
write(1, "--> copying...\n", 15--> copying...
) = 15
poll([{fd=3, events=POLLIN|POLLPRI}], 1, 0) = 0 (Timeout)
write(3, "\204\0\0\0\3SELECT `DeltaSeq`, `Fhinfo`, `Fhnode`, `FileId`,
`FileIndex`, `JobId`, `LStat`, `MD
5`, `MarkId`, `Name`, `PathId` FROM File LIMIT 1", 136) = 136
read(3,
"\1\0\0\1\v4\0\0\2\3def\6bareos\4File\4File\10DeltaSeq\10DeltaSeq\f?\0\5\0\0\0\2
\0\0\0\0000\0\0\
3\3def\6bareos\4File\4File\6Fhinfo\6Fhinfo\f?\0\25\0\0\0\366\0\0\0\0\0000\0\0\4\3def\6bareos\4File\4File\
6Fhnode\6Fhnode\f?\0\25\0\0\0\366\0\0\0\0\0000\0\0\5\3def\6bareos\4File\4File\6FileId\6FileId\f?\0\24\0\0
\0\10#B\0\0\0006\0\0\6\3def\6bareos\4File\4File\tFileIndex\tFileIndex\f?\0\n\0\0\0\3
@\0\0\0.\0\0\7\3def\
6bareos\4File\4File\5JobId\5JobId\f?\0\n\0\0\0\3)P\0\0\0.\0\0\10\3def\6bareos\4File\4File\5LStat\5LStat\f
?\0\377\0\0\0\374\221\20\0\0\0*\0\0\t\3def\6bareos\4File\4File\3MD5\3MD5\f?\0\377\0\0\0\374\221\20\0\0\00
00\0\0\n\3def\6bareos\4File\4File\6MarkId\6MarkId\f?\0\n\0\0\0\3
\0\0\0\0,\0\0\v\3def\6bareos\4File\4File
\4Name\4Name\f?\0\377\377\0\0\374\221P\0\0\0000\0\0\f\3def\6bareos\4File\4File\6PathId\6PathId\f?\0\n\0\0
\0\3)P\0\0\0\5\0\0\r\376\0\0\"\0t\0\0\16\0010\0010\0010\01020815008\0011\00423492A
A IH/ B A A CA eK A A
BXgj8z Bx9k46 Bbgeq5 A A
L\26oFI6qIvho1W8LctK9rWsqQ\0010\fkarnawal.tex\0043097\5\0\0\17\376\0\0\"\0",
163
84) = 711
sendto(4, "Q\0\0\0\377INSERT INTO File (deltaseq, fhinfo, fhnode,
fileid, fileindex, jobid, lstat, md5, m
arkid, name, pathid) VALUES ('0','0','0','20815008','1','2349','A A IH/
B A A CA eK A A BXgj8z Bx9k46 Bbg
eq5 A A L','oFI6qIvho1W8LctK9rWsqQ','0','karnawal.tex','3097')\0", 256,
MSG_NOSIGNAL, NULL, 0) = 256
poll([{fd=4, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=4, revents=POLLIN}])
recvfrom(4, "C\0\0\0\17INSERT 0 1\0Z\0\0\0\5T", 16384, 0, NULL, NULL) = 22
write(1, "--> updating sequence\n", 22--> updating sequence
) = 22
sendto(4, "Q\0\0\0Fselect setval(' file_fileid_seq', (select max(fileid)
from file))\0", 71, MSG_NOSIGNAL
, NULL, 0) = 71
poll([{fd=4, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=4, revents=POLLIN}])
recvfrom(4,
"T\0\0\0\37\0\1setval\0\0\0\0\0\0\0\0\0\0\24\0\10\377\377\377\377\0\0D\0\0\0\22\0\1\0\0\0\010
20815008C\0\0\0\rSELECT 1\0Z\0\0\0\5T", 16384, 0, NULL, NULL) = 71
sendto(4, "Q\0\0\0\vCOMMIT\0", 12, MSG_NOSIGNAL, NULL, 0) = 12
poll([{fd=4, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=4, revents=POLLIN}])
recvfrom(4, "C\0\0\0\vCOMMIT\0Z\0\0\0\5I", 16384, 0, NULL, NULL) = 18
write(1, "--> success\n", 12--> success
) = 12
As you can see, it does SELECT with LIMIT 1 so there's no way it's gonna
migrate more entries that one. Or I'm missing something here.
Anyone encountered something similar?
I'm using the 19.2.6 release from Centos 7 RPM packets.
Best regards,
MK
Spadajspadaj
Mar 12, 2020, 3:25:22 AM3/12/20
to bareos-users
BTW, If I'm seeing correctly, the dbcopy tool is inserting entries with
INSERT INTO even though the help and the docs say that unless I use -i
option, it should be using COPY FROM STDIN.
It seems that when I force some absurdly high limit on rows with -l
option (which is undocumented at the time and not shown in help; I found
it in sources) it tries to copy the rows.
We'll see how it goes.
INSERT INTO even though the help and the docs say that unless I use -i
option, it should be using COPY FROM STDIN.
It seems that when I force some absurdly high limit on rows with -l
option (which is undocumented at the time and not shown in help; I found
it in sources) it tries to copy the rows.
We'll see how it goes.
Spadajspadaj
Mar 12, 2020, 4:38:39 AM3/12/20
to bareos-users
It seems that with -l 100000000 (i have only some 5 mil entries in File
table) the migration completed and now my instance is running ok on
postgres.
table) the migration completed and now my instance is running ok on
postgres.
Frank Ueberschar
Mar 12, 2020, 5:44:53 AM3/12/20
to bareos...@googlegroups.com
Very good, finally you could do a migration to postgresql!
Well, bareos-dbcopy on the 19.2.6 release was experimental state, as
mentioned in the release notes. However, now it is available on the
nightly builds with the improved features mentioned in the documentation.
For interest, could you send us your original command line for
bareos-dbcopy (corresponding to the strace) so we can understand why it
comes to this output.
Thanks!
Am 11.03.20 um 20:26 schrieb Spadajspadaj:
--
Mit freundlichen Grüßen
Frank Ueberschar frank.ue...@bareos.com
Bareos GmbH & Co. KG Phone: +49 221 63 06 93-88
http://www.bareos.com Fax: +49 221 63 06 93-10
Sitz der Gesellschaft: Köln | Amtsgericht Köln: HRA 29646
Geschäftsführer: S. Dühr, M. Außendorf, J. Steffens, P. Storz
Well, bareos-dbcopy on the 19.2.6 release was experimental state, as
mentioned in the release notes. However, now it is available on the
nightly builds with the improved features mentioned in the documentation.
For interest, could you send us your original command line for
bareos-dbcopy (corresponding to the strace) so we can understand why it
comes to this output.
Thanks!
Am 11.03.20 um 20:26 schrieb Spadajspadaj:
Mit freundlichen Grüßen
Frank Ueberschar frank.ue...@bareos.com
Bareos GmbH & Co. KG Phone: +49 221 63 06 93-88
http://www.bareos.com Fax: +49 221 63 06 93-10
Sitz der Gesellschaft: Köln | Amtsgericht Köln: HRA 29646
Geschäftsführer: S. Dühr, M. Außendorf, J. Steffens, P. Storz
Spadajspadaj
Mar 12, 2020, 5:54:25 AM3/12/20
to bareos...@googlegroups.com
Sure.
The exact cmdline I did was
strace -ff bareos-dbcopy MyCatalog PgCatalog 2>&1 | tee
/tmp/bareos-dbcopy.trace
As you can see, the invocation of the bareos-dbcopy itself (minus whole
strace and redirection) was extremely simple.
The exact cmdline I did was
strace -ff bareos-dbcopy MyCatalog PgCatalog 2>&1 | tee
/tmp/bareos-dbcopy.trace
As you can see, the invocation of the bareos-dbcopy itself (minus whole
strace and redirection) was extremely simple.
Frank Ueberschar
Mar 12, 2020, 7:57:58 AM3/12/20
to bareos...@googlegroups.com
You met a bug that is already fixed in the nightly builds and -l
<rowcount> is a workaround for this. <rowcount> sets a member variable
that is otherwise unitialized.
Again, this issue is already fixed. Thank you for pointing this out.
Am 12.03.20 um 10:54 schrieb Spadajspadaj:
<rowcount> is a workaround for this. <rowcount> sets a member variable
that is otherwise unitialized.
Again, this issue is already fixed. Thank you for pointing this out.
Am 12.03.20 um 10:54 schrieb Spadajspadaj:
Reply all
Reply to author
Forward
0 new messages