Forum Stats

  • 3,750,404 Users
  • 2,250,174 Discussions
  • 7,866,968 Comments

Discussions

lb genschema error stack overflow

User_BMK8F
User_BMK8F Member Posts: 2 Green Ribbon
edited Aug 21, 2021 4:36PM in SQLcl

Hello! I'm new to Oracle databases and systems, so please correct me if I use any terminology incorrectly.

Using sqlcl connected to an existing Oracle 12c database I ran the command:

lb genschema

It ran for 6 hours before it returned an error stating that it had a stack overflow. It spent a majority of the time on "[Method processCaptureTable]:"


SQL> lb genschema

[Method loadCaptureTable]:                                                                                                

                 Executing                                                                                                

[Type - TYPE_SPEC]:                        11190 ms                                                                                    

[Type - TYPE_BODY]:                         1264 ms                                                                                    

[Type - SEQUENCE]:                           387 ms                                                                                    

[Type - DIRECTORY]:                           75 ms                                                                                    

[Type - CLUSTER]:                            962 ms                                                                                                 

[Type - TABLE]:                            91965 ms                                                                                                 

[Type - MATERIALIZED_VIEW_LOG]:             3320 ms                                                                                                 

[Type - MATERIALIZED_VIEW]:                26651 ms                                                                                                               

[Type - VIEW]:                            143307 ms                                                                                                               

[Type - REF_CONSTRAINT]:                     161 ms                                                                                                               

[Type - DIMENSION]:                          254 ms                                                                                                               

[Type - FUNCTION]:                          1176 ms                                                                                                                              

[Type - PROCEDURE]:                         1318 ms                                                                                                                              

[Type - PACKAGE_SPEC]:                    365993 ms                                                                                                                              

[Type - DB_LINK]:                            106 ms                                                                                                                                            

[Type - SYNONYM]:                         123790 ms                                                                                                                                            

[Type - INDEX]:                             7957 ms                                                                                                                                            

[Type - TRIGGER]:                          17493 ms                                                                                                                                            

[Type - JOB]:                               1890 ms

                 End

[Method loadCaptureTable]:               3760090 ms

[Method processCaptureTable]: Exception in thread "main" java.lang.StackOverflowError

	at java.base/java.lang.StringBuilder.<init>(StringBuilder.java:99)

	at oracle.dbtools.parser.Earley.tree(Earley.java:751)

	at oracle.dbtools.parser.Earley.tree(Earley.java:804)

	at oracle.dbtools.parser.Earley.tree(Earley.java:810)

	at oracle.dbtools.parser.Earley.tree(Earley.java:764)

	at oracle.dbtools.parser.Earley.tree(Earley.java:804)

	at oracle.dbtools.parser.Earley.tree(Earley.java:810)

	at oracle.dbtools.parser.Earley.tree(Earley.java:764)

	at oracle.dbtools.parser.Earley.tree(Earley.java:804)

	at oracle.dbtools.parser.Earley.tree(Earley.java:810)

	at oracle.dbtools.parser.Earley.tree(Earley.java:764)

	at oracle.dbtools.parser.Earley.tree(Earley.java:804)

	at oracle.dbtools.parser.Earley.tree(Earley.java:810)
... it repeats with lines 764, 804, 810 for hundreds of lines.


I am assuming that our database might be too big or cumbersome, but wanted to see if there were any suggestions for how to work around this. My goal is to implement devops best practices for this database. Most advice I've read online suggests that I get a baseline schema/ state of the database and then use a migration based approach to version control the changes applied after the baseline. Liquibase seems to be ideal for this, but the sqlcl vs native differences aren't very clear yet, so I'm testing them both out for now.


Any advice is welcome! Thank you in advance!

Tagged:

Best Answer

  • user9540031
    user9540031 Member Posts: 111 Silver Badge
    Accepted Answer

    Hello,

    It ran for 6 hours before it returned an error stating that it had a stack overflow. [...]

    [Method processCaptureTable]: Exception in thread "main" java.lang.StackOverflowError
            at java.base/java.lang.StringBuilder.<init>(StringBuilder.java:99)
    	at oracle.dbtools.parser.Earley.tree(Earley.java:751)
    	at oracle.dbtools.parser.Earley.tree(Earley.java:804)
            [...]
    

    If I understand well, the Earley parser is SQLcl's internal SQL parser... And it seems it might need a deep thread stack when parsing complex SQL queries. So you might try to set -Xss to a higher value, e.g. -Xss50m or -Xss100m in Java options—remark: assuming you're running a 64-bit JRE.

    How you do this exactly depends on how you start SQLcl, but setting the JAVA_TOOL_OPTIONS environment variable should do.

    I tried to run the native liquibase command and it spent 28 hours running [...]

    Apparently, the stand-alone Liquibase utility that you ran is liquibase.org's release 4.4.3. The built-in version in SQLcl is based on Liquibase.org's release 4.3.3. Besides SQLcl's version comes with many Oracle extensions which build on the DBMS_METADATA package and its internal SXML representation of objects metadata... So I would not expect lb genschema to do the same thing in SQLcl and in Liquibase.org's version.

    That said, I haven't done much testing of Liquibase in SQLcl, nor have I used Liquibase.org's version in recent years to help you any further beside the above remarks. I do remind, however, that at the time (2015) some Liquibase commands could easily trigger Liquibase.org's version into attempting to load the whole list of tables in the schema (+ column definitions) into in-memory data structures of the Liquibase client (in the Java heap), and that would fail pathetically—and agonizingly slowly—if done on a schema which contained dozens of thousands of tables...

    (I faced similar huge Java heap requirements in SQL Developer Data Modeler, when attempting to reverse-engineer a schema with hundreds of thousands of partitions and subpartitions. This is when monitoring the JVM, using Java VisualVM or similar tools, becomes very useful in order to figure out what's going on, along with watching from the database side which SQL queries are being run by the client session.)

    Good luck!

    Regards,

    P.S.: in fact running lb genschema in the first place, though nice to have, is not always absolutely necessary to get started deploying database refactorings using Liquibase.

Answers

  • User_BMK8F
    User_BMK8F Member Posts: 2 Green Ribbon

    I tried to run the native liquibase command and it spent 28 hours running before my internet went down and forced it to stop running. It just printed this at the start with the flashing cursor for the entire time without any logging. (name, password, and url are obscured for obvious reasons) I'll try the sqlcl command again this weekend.


    liquibase generate-changelog --username=name --password=fake --url=jdbc:oracle:thin:@obscuringtheurl.com:1524/serviceName
    ####################################################
    ##  _   _       _ _           ##
    ## | |  (_)      (_) |           ##
    ## | |  _ __ _ _  _ _| |__  __ _ ___ ___  ##
    ## | |  | |/ _` | | | | | '_ \ / _` / __|/ _ \ ##
    ## | |___| | (_| | |_| | | |_) | (_| \__ \ __/ ##
    ## \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___| ##
    ##       | |                ##
    ##       |_|                ##
    ##                        ## 
    ## Get documentation at docs.liquibase.com    ##
    ## Get certified courses at learn.liquibase.com ## 
    ## Free schema change activity reports at    ##
    ##      https://hub.liquibase.com                 ##
    ##                        ##
    ####################################################
    Starting Liquibase at 09:55:04 (version 4.4.3 #53 built at 2021-08-05 18:32+0000)
    Liquibase Version: 4.4.3
    Liquibase Community 4.4.3 by Datical
    BEST PRACTICE: The changelog generated by diffChangeLog/generateChangeLog should be inspected for correctness and completeness before being deployed.
    
    When generating formatted SQL changelogs, it is important to decide if batched statements
    should be split (splitStatements:true is the default behavior) or not (splitStatements:false).
    See http://liquibase.org for additional documentation.
    
  • user9540031
    user9540031 Member Posts: 111 Silver Badge
    Accepted Answer

    Hello,

    It ran for 6 hours before it returned an error stating that it had a stack overflow. [...]

    [Method processCaptureTable]: Exception in thread "main" java.lang.StackOverflowError
            at java.base/java.lang.StringBuilder.<init>(StringBuilder.java:99)
    	at oracle.dbtools.parser.Earley.tree(Earley.java:751)
    	at oracle.dbtools.parser.Earley.tree(Earley.java:804)
            [...]
    

    If I understand well, the Earley parser is SQLcl's internal SQL parser... And it seems it might need a deep thread stack when parsing complex SQL queries. So you might try to set -Xss to a higher value, e.g. -Xss50m or -Xss100m in Java options—remark: assuming you're running a 64-bit JRE.

    How you do this exactly depends on how you start SQLcl, but setting the JAVA_TOOL_OPTIONS environment variable should do.

    I tried to run the native liquibase command and it spent 28 hours running [...]

    Apparently, the stand-alone Liquibase utility that you ran is liquibase.org's release 4.4.3. The built-in version in SQLcl is based on Liquibase.org's release 4.3.3. Besides SQLcl's version comes with many Oracle extensions which build on the DBMS_METADATA package and its internal SXML representation of objects metadata... So I would not expect lb genschema to do the same thing in SQLcl and in Liquibase.org's version.

    That said, I haven't done much testing of Liquibase in SQLcl, nor have I used Liquibase.org's version in recent years to help you any further beside the above remarks. I do remind, however, that at the time (2015) some Liquibase commands could easily trigger Liquibase.org's version into attempting to load the whole list of tables in the schema (+ column definitions) into in-memory data structures of the Liquibase client (in the Java heap), and that would fail pathetically—and agonizingly slowly—if done on a schema which contained dozens of thousands of tables...

    (I faced similar huge Java heap requirements in SQL Developer Data Modeler, when attempting to reverse-engineer a schema with hundreds of thousands of partitions and subpartitions. This is when monitoring the JVM, using Java VisualVM or similar tools, becomes very useful in order to figure out what's going on, along with watching from the database side which SQL queries are being run by the client session.)

    Good luck!

    Regards,

    P.S.: in fact running lb genschema in the first place, though nice to have, is not always absolutely necessary to get started deploying database refactorings using Liquibase.