Hi Sören
Can you try setting log_enable(3) before attempting the table creation to
enable row by row auto-commit of transactions preventing the after image size
limit from ever being exceeded, then set log_enable(1) when complete ..
Best Regards
Hugh Williams
Professional Services
OpenLink Software
Web: http://www.openlinksw.com
Support: http://support.openlinksw.com
Forums: http://boards.openlinksw.com/support
Twitter: http://twitter.com/OpenLink
On 22 Jun 2010, at 09:59, Sören Auer wrote:
> Hi all,
>
> When creating a table from a select query we got the following error
> message:
>
> Transaction aborted because it's log after image size went above the
> limit at line 18 of Top-Level:
>
> CREATE TABLE DB.DBA.ef_cache_gpv_d3455cb7d101000cacfe8a90d6b68970 AS
> SELECT __ro2sq ("s-6-0_rbc"."product") AS "product", __ro2sq
> ("s-6-0_rbc"."label") AS "label", __ro2sq ("s-6-0_rbc"."productType") AS
> "productType", __ro2sq ("s-6-0_rbc"."productFeature1") AS
> "productFeature1", __ro2sq ("s-6-0_rbc"."productFeature2") AS
> "productFeature2", __ro2sq ("s-6-0_rbc"."value1") AS "value1" FROM
> (SELECT "s-8-1"."product" AS "product", DB.DBA.RDF_DIST_DESER_LONG
> ("s-8-1"."label") AS "label", DB.DBA.RDF_DIST_DESER_LONG
> ("s-8-1"."productType") AS "productType", DB.DBA.RDF_DIST_DESER_LONG
> ("s-8-1"."productFeature1") AS "productFeature1",
> DB.DBA.RDF_DIST_DESER_LONG ("s-8-1"."productFeature2") AS
> "productFeature2", DB.DBA.RDF_DIST_DESER_LONG ("s-8-1"."value1") AS
> "value1" FROM ( SELECT DISTINCT __id2i ( "s-8-1-t0"."S" ) AS "product",
> __ro2sq ( DB.DBA.RDF_DIST_SER_LONG ( __ro2lo ( "s-8-1-t0"."O" ))) AS
> "label", __ro2sq ( DB.DBA.RDF_DIST_SER_LONG ( __ro2lo ( "s-8-1-t1"."O"
> ))) AS "productType", __ro2sq ( DB.DBA.RDF_DIST_SER_LONG ( __ro2lo (
> "s-8-1-t2"."O" ))) AS "productFeature1", __ro2sq (
> DB.DBA.RDF_DIST_SER_LONG ( __ro2lo ( "s-8-1-t3"."O" ))) AS
> "productFeature2", __ro2sq ( DB.DBA.RDF_DIST_SER_LONG ( __ro2lo (
> "s-8-1-t4"."O" ))) AS "value1" FROM DB.DBA.RDF_QUAD AS "s-8-1-t0" INNER
> JOIN DB.DBA.RDF_QUAD AS "s-8-1-t1" ON ( "s-8-1-t0"."S" = "s-8-1-t1"."S"
> ) INNER JOIN DB.DBA.RDF_QUAD AS "s-8-1-t2" ON ( "s-8-1-t0"."S" =
> "s-8-1-t2"."S" AND "s-8-1-t1"."S" = "s-8-1-t2"."S" ) INNER JOIN
> DB.DBA.RDF_QUAD AS "s-8-1-t3" ON ( "s-8-1-t0"."S" = "s-8-1-t3"."S" AND
> "s-8-1-t1"."S" = "s-8-1-t3"."S" AND "s-8-1-t2"."S" = "s-8-1-t3"."S" )
> INNER JOIN DB.DBA.RDF_QUAD AS "s-8-1-t4" ON ( "s-8-1-t0"."S" =
> "s-8-1-t4"."S" AND "s-8-1-t1"."S" = "s-8-1-t4"."S" AND "s-8-1-t2"."S" =
> "s-8-1-t4"."S" AND "s-8-1-t3"."S" = "s-8-1-t4"."S" ) WHERE
> "s-8-1-t0"."G" = __i2idn ( __bft( 'http://bsbm.aksw.org/2785' , 1)) AND
> "s-8-1-t0"."P" = __i2idn ( __bft(
> 'http://www.w3.org/2000/01/rdf-schema#label' , 1)) AND "s-8-1-t1"."G" =
> __i2idn ( __bft( 'http://bsbm.aksw.org/2785' , 1)) AND "s-8-1-t1"."P" =
> __i2idn ( __bft( 'http://www.w3.org/1999/02/22-rdf-syntax-ns#type' , 1))
> AND "s-8-1-t2"."G" = __i2idn ( __bft( 'http://bsbm.aksw.org/2785' , 1))
> AND "s-8-1-t2"."P" = __i2idn ( __bft(
> 'http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/vocabulary/productFeature'
> , 1)) AND "s-8-1-t3"."G" = __i2idn ( __bft( 'http://bsbm.aksw.org/2785'
> , 1)) AND "s-8-1-t3"."P" = __i2idn ( __bft(
> 'http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/vocabulary/productFeature'
> , 1)) AND "s-8-1-t4"."G" = __i2idn ( __bft( 'http://bsbm.aksw.org/2785'
> , 1)) AND "s-8-1-t4"."P" = __i2idn ( __bft(
> 'http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/vocabulary/productPropertyNumeric1'
>
> , 1)) OPTION (QUIETCAST) ) AS "s-8-1") AS "s-6-0_rbc" WITH DATA
>
>
>
> Following the error message we increased the following value:
> TransactionAfterImageLimit = 15000000000
>
>
> After that there is no error message anymore, but the table creation
> process also does not seem to stop correctly. The CPU load is only 2%
> but the table creation does not finish. Any ideas how to resolve this
> problem?
>
> Sören
>
>
>
> Here is an excerpt from our config.iniÖ
> [Parameters]
> ServerPort = 1111
> LiteMode = 0
> DisableUnixSocket = 1
> DisableTcpSocket = 0
> ;SSLServerPort = 2111
> ;SSLCertificate = cert.pem
> ;SSLPrivateKey = pk.pem
> ;X509ClientVerify = 0
> ;X509ClientVerifyDepth = 0
> ;X509ClientVerifyCAFile = ca.pem
> ServerThreads = 20
> CheckpointInterval = 60
> O_DIRECT = 0
> NumberOfBuffers = 2000
> MaxDirtyBuffers = 1200
> CaseMode = 2
> MaxStaticCursorRows = 5000
> CheckpointAuditTrail = 0
> AllowOSCalls = 0
> SchedulerInterval = 10
> DirsAllowed = .,
> /opt/virtuoso.5.0.12/share/virtuoso/vad, /tmp, /var/www,
> /home/michael/Workspace
> ThreadCleanupInterval = 0
> ThreadThreshold = 10
> ResourcesCleanupInterval = 0
> FreeTextBatchSize = 100000
> SingleCPU = 0
> VADInstallDir = /opt/virtuoso.5.0.12/share/virtuoso/vad/
> PrefixResultNames = 0
> RdfFreeTextRulesSize = 100
> IndexTreeMaps = 256
> TransactionAfterImageLimit = 15000000000
>
>
> ------------------------------------------------------------------------------
> ThinkGeek and WIRED's GeekDad team up for the Ultimate
> GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the
> lucky parental unit. See the prize list and enter to win:
> http://p.sf.net/sfu/thinkgeek-promo
> _______________________________________________
> Virtuoso-users mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/virtuoso-users
