WAL read files

newer
sql query failure question

older
Multi-core join performance

Pierre-Adrien Coustillas

21 Jan 2016 21 Jan '16

9:21 p.m.

Hi,

is it possible to read WAL files ? sql/ log.<transaction id>

why ? We still have a problem of duplicate table . All our monetsdb are updated with the latest version of the repository debian8 , and that several server. We have learned to remove duplicate tables and a stable production. But I want to understand this bug Duplication of tables are always created after a restart monetdb

After reading this page https://www.monetdb.org/blog/monetdb-sql-transaction-management-scheme, and particularly this ""By default, persisted transactions are cleaned up from the WAL. During database startup, all transaction still left in the WAL are read and any that are not yet applied are written into the persistent BAT storage"" . I think that the drop or create commands are not cleaned up from the WAL

We used the library php _ map.inci and we run a lot of command drop / create ( ... I know this is not recommended ... ) We used autocommit.

If I can read and understand what is in the WAL file, can help me understand this bug

Thank

Pierre

-- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35 // 09 54 38 70 90

Attachments:

attachment.html (text/html — 5.6 KB)

Show replies by thread

Stefan Manegold

21 Jan 21 Jan

9:39 p.m.

Pierre,

about your duplicate table problem:

did you by any chance fill this/these table(s) using multiple COPY INTO bulk-load operation (per table)?

Thanks! Stefan

----- On Jan 21, 2016, at 10:21 PM, Pierre-Adrien Coustillas pcoustillas@1g6.biz wrote:

...

Hi,

is it possible to read WAL files ? sql/ log.<transaction id>

why ? We still have a problem of duplicate table . All our monetsdb are updated with the latest version of the repository debian8 , and that several server. We have learned to remove duplicate tables and a stable production. But I want to understand this bug Duplication of tables are always created after a restart monetdb

After reading this page https://www.monetdb.org/blog/monetdb-sql-transaction-management-scheme, and particularly this ""By default, persisted transactions are cleaned up from the WAL. During database startup, all transaction still left in the WAL are read and any that are not yet applied are written into the persistent BAT storage"" . I think that the drop or create commands are not cleaned up from the WAL

We used the library php _ map.inci and we run a lot of command drop / create ( ... I know this is not recommended ... ) We used autocommit.

If I can read and understand what is in the WAL file, can help me understand this bug

Thank

Pierre

-- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35 // 09 54 38 70 90

users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

Marcel Blok

22 Jan 22 Jan

8:28 a.m.

Hi Pierre,

We are experiencing a very similar situation. We are also experiencing duplication in tables (and also in columns) and we also use quite a lot of create and drop table statements, specifically after a shutdown (or crash...)

We use JDBC to connect and have experienced this behaviour on different Linux distributions as well as on Windows. So it seems not to be related to a specific build or environment.

I have a feeling you are on the right track here. I would also be very interested in being able to see into these WAL files, in order to detect some correlation...

Best regards, Marcel Blok

________________________________________ Van: users-list users-list-bounces+m.blok=chs.nl@monetdb.org namens Stefan Manegold Stefan.Manegold@cwi.nl Verzonden: donderdag 21 januari 2016 22:39 Aan: Communication channel for MonetDB users Onderwerp: Re: WAL read files

Pierre,

about your duplicate table problem:

did you by any chance fill this/these table(s) using multiple COPY INTO bulk-load operation (per table)?

Thanks! Stefan

----- On Jan 21, 2016, at 10:21 PM, Pierre-Adrien Coustillas pcoustillas@1g6.biz wrote:

...

Hi,

is it possible to read WAL files ? sql/ log.<transaction id>

why ? We still have a problem of duplicate table . All our monetsdb are updated with the latest version of the repository debian8 , and that several server. We have learned to remove duplicate tables and a stable production. But I want to understand this bug Duplication of tables are always created after a restart monetdb

After reading this page https://www.monetdb.org/blog/monetdb-sql-transaction-management-scheme, and particularly this ""By default, persisted transactions are cleaned up from the WAL. During database startup, all transaction still left in the WAL are read and any that are not yet applied are written into the persistent BAT storage"" . I think that the drop or create commands are not cleaned up from the WAL

We used the library php _ map.inci and we run a lot of command drop / create ( ... I know this is not recommended ... ) We used autocommit.

If I can read and understand what is in the WAL file, can help me understand this bug

Thank

Pierre

-- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35 // 09 54 38 70 90

users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

Pierre-Adrien Coustillas

3:12 p.m.

Stefan,

it indifferently concerns the tables : - loaded with COPY INTO - created with CREATE TABLE table AS SELECT WITH DATA - CREATE AS SELECT WITH NO DATA follow INSERT INTO SELECT.

But all the duplicated tables are DROP and CREATE each day (or almost).

Pierre

----- Mail original ----- De: "Stefan Manegold" Stefan.Manegold@cwi.nl À: "Communication channel for MonetDB users" users-list@monetdb.org Envoyé: Jeudi 21 Janvier 2016 22:39:43 Objet: Re: WAL read files

Pierre,

about your duplicate table problem:

did you by any chance fill this/these table(s) using multiple COPY INTO bulk-load operation (per table)?

Thanks! Stefan

----- On Jan 21, 2016, at 10:21 PM, Pierre-Adrien Coustillas pcoustillas@1g6.biz wrote:

...

Hi,

is it possible to read WAL files ? sql/ log.<transaction id>

why ? We still have a problem of duplicate table . All our monetsdb are updated with the latest version of the repository debian8 , and that several server. We have learned to remove duplicate tables and a stable production. But I want to understand this bug Duplication of tables are always created after a restart monetdb

After reading this page https://www.monetdb.org/blog/monetdb-sql-transaction-management-scheme, and particularly this ""By default, persisted transactions are cleaned up from the WAL. During database startup, all transaction still left in the WAL are read and any that are not yet applied are written into the persistent BAT storage"" . I think that the drop or create commands are not cleaned up from the WAL

We used the library php _ map.inci and we run a lot of command drop / create ( ... I know this is not recommended ... ) We used autocommit.

If I can read and understand what is in the WAL file, can help me understand this bug

Thank

Pierre

-- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35 // 09 54 38 70 90

users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

Pierre-Adrien Coustillas

5 Feb 5 Feb

11:08 p.m.

New subject: WAL read files - duplicate table

Hi,

additional information in reference I think this bug : https://www.monetdb.org/bugzilla/show_bug.cgi?id=3912

I think there is à bug whit the character underscore .

I try to reproduce the bug, but not simple. I think the COPY INTO bulk-load operation with drop / create regulary is a good track.

I tried to study a little the Write-Ahead file with a hexdump -C and a "CREATE TABLE foo_bar (int bar);" I see "sys_foo_bar_bar" Undescrore to separate the table name of the field name? a bit risky, right?

Pierre

----- Mail original ----- De: "pcoustillas" pcoustillas@1g6.biz À: "Communication channel for MonetDB users" users-list@monetdb.org Envoyé: Vendredi 22 Janvier 2016 16:12:51 Objet: Re: WAL read files

Stefan,

it indifferently concerns the tables : - loaded with COPY INTO - created with CREATE TABLE table AS SELECT WITH DATA - CREATE AS SELECT WITH NO DATA follow INSERT INTO SELECT.

But all the duplicated tables are DROP and CREATE each day (or almost).

Pierre

Pierre,

about your duplicate table problem:

did you by any chance fill this/these table(s) using multiple COPY INTO bulk-load operation (per table)?

Thanks! Stefan

----- On Jan 21, 2016, at 10:21 PM, Pierre-Adrien Coustillas pcoustillas@1g6.biz wrote:

...

Hi,

is it possible to read WAL files ? sql/ log.<transaction id>

why ? We still have a problem of duplicate table . All our monetsdb are updated with the latest version of the repository debian8 , and that several server. We have learned to remove duplicate tables and a stable production. But I want to understand this bug Duplication of tables are always created after a restart monetdb

After reading this page https://www.monetdb.org/blog/monetdb-sql-transaction-management-scheme, and particularly this ""By default, persisted transactions are cleaned up from the WAL. During database startup, all transaction still left in the WAL are read and any that are not yet applied are written into the persistent BAT storage"" . I think that the drop or create commands are not cleaned up from the WAL

We used the library php _ map.inci and we run a lot of command drop / create ( ... I know this is not recommended ... ) We used autocommit.

If I can read and understand what is in the WAL file, can help me understand this bug

Thank

Pierre

-- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35 // 09 54 38 70 90

users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

Pierre-Adrien Coustillas

11:21 p.m.

New subject: WAL read files - duplicate table

I finally found a bug, but not the one I thought : https://www.monetdb.org/bugzilla/show_bug.cgi?id=3919

----- Mail original ----- De: "pcoustillas" pcoustillas@1g6.biz À: "Communication channel for MonetDB users" users-list@monetdb.org Envoyé: Samedi 6 Février 2016 00:08:44 Objet: Re: WAL read files - duplicate table

Hi,

additional information in reference I think this bug : https://www.monetdb.org/bugzilla/show_bug.cgi?id=3912

I think there is à bug whit the character underscore .

I try to reproduce the bug, but not simple. I think the COPY INTO bulk-load operation with drop / create regulary is a good track.

Pierre

Stefan,

it indifferently concerns the tables : - loaded with COPY INTO - created with CREATE TABLE table AS SELECT WITH DATA - CREATE AS SELECT WITH NO DATA follow INSERT INTO SELECT.

But all the duplicated tables are DROP and CREATE each day (or almost).

Pierre

Pierre,

about your duplicate table problem:

did you by any chance fill this/these table(s) using multiple COPY INTO bulk-load operation (per table)?

Thanks! Stefan

----- On Jan 21, 2016, at 10:21 PM, Pierre-Adrien Coustillas pcoustillas@1g6.biz wrote:

...

Hi,

is it possible to read WAL files ? sql/ log.<transaction id>

why ? We still have a problem of duplicate table . All our monetsdb are updated with the latest version of the repository debian8 , and that several server. We have learned to remove duplicate tables and a stable production. But I want to understand this bug Duplication of tables are always created after a restart monetdb

After reading this page https://www.monetdb.org/blog/monetdb-sql-transaction-management-scheme, and particularly this ""By default, persisted transactions are cleaned up from the WAL. During database startup, all transaction still left in the WAL are read and any that are not yet applied are written into the persistent BAT storage"" . I think that the drop or create commands are not cleaned up from the WAL

We used the library php _ map.inci and we run a lot of command drop / create ( ... I know this is not recommended ... ) We used autocommit.

If I can read and understand what is in the WAL file, can help me understand this bug

Thank

Pierre

-- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35 // 09 54 38 70 90

users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

Pierre-Adrien Coustillas

8 Feb 8 Feb

2:51 p.m.

Hi Marcel,

is that the underscore character is used in the tables and fields duplicated ?

Pierre

----- Mail original ----- De: "Marcel Blok" m.blok@chs.nl À: "Communication channel for MonetDB users" users-list@monetdb.org Envoyé: Vendredi 22 Janvier 2016 09:28:41 Objet: Re: WAL read files

Hi Pierre,

We use JDBC to connect and have experienced this behaviour on different Linux distributions as well as on Windows. So it seems not to be related to a specific build or environment.

I have a feeling you are on the right track here. I would also be very interested in being able to see into these WAL files, in order to detect some correlation...

Best regards, Marcel Blok

Pierre,

about your duplicate table problem:

did you by any chance fill this/these table(s) using multiple COPY INTO bulk-load operation (per table)?

Thanks! Stefan

----- On Jan 21, 2016, at 10:21 PM, Pierre-Adrien Coustillas pcoustillas@1g6.biz wrote:

...

Hi,

is it possible to read WAL files ? sql/ log.<transaction id>

why ? We still have a problem of duplicate table . All our monetsdb are updated with the latest version of the repository debian8 , and that several server. We have learned to remove duplicate tables and a stable production. But I want to understand this bug Duplication of tables are always created after a restart monetdb

After reading this page https://www.monetdb.org/blog/monetdb-sql-transaction-management-scheme, and particularly this ""By default, persisted transactions are cleaned up from the WAL. During database startup, all transaction still left in the WAL are read and any that are not yet applied are written into the persistent BAT storage"" . I think that the drop or create commands are not cleaned up from the WAL

We used the library php _ map.inci and we run a lot of command drop / create ( ... I know this is not recommended ... ) We used autocommit.

If I can read and understand what is in the WAL file, can help me understand this bug

Thank

Pierre

-- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35 // 09 54 38 70 90

users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

3024

Age (days ago)

3042

Last active (days ago)

List overview

Download

6 comments

3 participants

tags (0)

participants (3)

Marcel Blok
Pierre-Adrien Coustillas
Stefan Manegold