Exporting DB Data?

Hi,

Newbie to liquibase and liking it so far.  I was wondering how do you export the data from your database?  I’m using ant generateChangeLog and I’ve read about the diffTypes=“data” but that doesn’t work.  I’ve seen blogs on liqubase.org that say they just change the generateChangeLog to export the data but don’t say how they did this.  Can anyone show me how to do this?

Thanks.

Here’s the command line I used and the output.

liquidbase --url=jdbc:mysql://**** --username=root --password=* --changeLogFile=output2.xml --diffTypes=data generateChangeLog

java.lang.OutOfMemortyError: Java heap space
at java.util.Arrys.copyOfRange(Unknown Source)
at java.lang.String.(Unknown Source)
at java.lang.StringBuilder.toString(Unknown Source)

First Aid:
java.lang.OutOfMemortyError: Java heap space

Try to fix it by extend memory size of your JVM and setting JVM Options in liquibase_start_file [liquibase(.bat)] like this:

    -Xms32m -Xmx512m

Here is an option for configure it:

    # add any JVM options here JAVA_OPTS=

===
It’s interesting for me too - how to redefine diffTypes property when i’m using Ant? Is there some special attribute of generateChangeLog Ant task?

It looks like the Ant tasks didn’t include diffTypes support.  I added diffTypes and dataDir to the ant tasks in trunk.

Thnx again! It will really help me! :slight_smile:

It was dangerous for me to start ant task as executing of command-line, using ‘exec’ task :slight_smile:

Hi, newbie as well, I am trying to make liquibase work but I have the same kind of problem executing
java -classpath “liquibase-1.9.5.jar;./lib/mysql.jar” liquibase.commandline.Main --driver=com.mysql.jdbc.Driver --url=“jdbc:mysql://localhost/bugs” --username=XXX --password=XXX  --diffTypes=data --changeLogFile=changesesame.xml generateChangeLog
it works fine when there is no table.
here is the output
sh-3.2$ less changesesame.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<databaseChangeLog xmlns=“http://www.liquibase.org/xml/ns/dbchangelog/1.9” xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance” xsi:schemaLocation=“http://www.liqu
ibase.org/xml/ns/dbchangelog/1.9 http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd”/>

but as soon as a table is added the same function  spits out:
Migration Failed: Syntax error or access violation,  message from server: “Nom de base de donn�e ill�gal: ‘’”

I am surely missing something obvious in the configuration
the same applies to all databases with tables as well.

thanks for your help. I am trying to give liquibase a go. no luck with eclipse plugin, nor with 2.0RC1.0
I am stuck

It looks like a database error you are getting.  We don’t localize our error messages.  If you set logLevel=FINEST, what do you get for output?

Nathan

thanks for the quick reply.
seems that database name is missing!
it doesn’t it get from the url ?

java -classpath “liquibase-1.9.5.jar;./lib/mysql.jar” liquibase.commandline.Main --driver=com.mysql.jdbc.Driver --url=“jdbc:mysql://127.0.0.1/bugs” --username=XXXX --password=XXX  --diffTypes=data --changeLogFile=changesesame.xml --logLevel=FINEST generateChangeLog
Reading tables for sesame @ jdbc:mysql://127.0.0.1/bugs …
Reading foreign keys for sesame @ jdbc:mysql://127.0.0.1/bugs …
Reading primary keys for sesame @ jdbc:mysql://127.0.0.1/bugs …
Reading columns for sesame @ jdbc:mysql://127.0.0.1/bugs …
Migration Failed: Syntax error or access violation,  message from server: “Nom de base de donn�e ill�gal: ‘’”.  For more information, use the --logLevel flag)
21 avr. 2010 04:26:50 liquibase.commandline.Main main
GRAVE: Syntax error or access violation,  message from server: “Nom de base de donn�e ill�gal: ‘’”
liquibase.exception.JDBCException: java.sql.SQLException: Syntax error or access violation,  message from server: “Nom de base de donn�e ill�gal: ‘’”
        at liquibase.database.structure.SqlDatabaseSnapshot.(SqlDatabaseSnapshot.java:107)
        at liquibase.database.structure.MySqlDatabaseSnapshot.(MySqlDatabaseSnapshot.java:55)
        at liquibase.database.MySQLDatabase.createDatabaseSnapshot(MySQLDatabase.java:169)
        at liquibase.diff.Diff.compare(Diff.java:62)
        at liquibase.commandline.CommandLineUtils.doGenerateChangeLog(CommandLineUtils.java:135)
        at liquibase.commandline.Main.doMigration(Main.java:591)
        at liquibase.commandline.Main.main(Main.java:97)
Caused by: java.sql.SQLException: Syntax error or access violation,  message from server: “Nom de base de donn�e ill�gal: ‘’”
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1825)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1020)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1109)
        at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1070)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:2027)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:1984)
        at com.mysql.jdbc.Statement.executeQuery(Statement.java:1152)
        at liquibase.database.structure.MySqlDatabaseSnapshot.getColumnTypeAndDefValue(MySqlDatabaseSnapshot.java:73)
        at liquibase.database.structure.SqlDatabaseSnapshot.readColumns(SqlDatabaseSnapshot.java:294)
        at liquibase.database.structure.SqlDatabaseSnapshot.(SqlDatabaseSnapshot.java:95)
        … 6 more

I’m not sure why you are getting that error.  Liquibase has already ran a few statements against the database and they ran fine.  The command being ran is:

selectStatement.executeQuery("DESC "+database.escapeTableName(schemaName, tableName));

for each tableName found.

I wonder if for some reason you are getting an empty table name?

Nathan

ok, I debugged, found that bug in the mysql database
http://bugs.mysql.com/bug.php?id=9917

updated my mysql.jar to latest version of mysql-connector-java-5.1.12-bin
at now it works at last.

thanks for your help.

Glad you found the problem, thanks for letting me know

Nathan

Hi, newbie also. I am trying to make liquibase to export my database schema
output:
SEVERE 30.03.11 15:46:liquibase: FEHLER: Syntaxfehler bei ╗from½
liquibase.exception.DatabaseException: org.postgresql.util.PSQLException: FEHLER: Syntaxfehler bei ╗from½
        at liquibase.snapshot.jvm.JdbcDatabaseSnapshotGenerator.createSnapshot(JdbcDatabaseSnapshotGenerator.java:251)
        at liquibase.snapshot.DatabaseSnapshotGeneratorFactory.createSnapshot(DatabaseSnapshotGeneratorFactory.java:69)
        at liquibase.diff.Diff.compare(Diff.java:63)
        at liquibase.integration.commandline.CommandLineUtils.doGenerateChangeLog(CommandLineUtils.java:145)
        at liquibase.integration.commandline.Main.doMigration(Main.java:624)
        at liquibase.integration.commandline.Main.main(Main.java:116)
Caused by: org.postgresql.util.PSQLException: FEHLER: Syntaxfehler bei ╗from½
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:336)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:235)
        at liquibase.snapshot.jvm.JdbcDatabaseSnapshotGenerator.isColumnAutoIncrement(JdbcDatabaseSnapshotGenerator.java:842)
        at liquibase.snapshot.jvm.JdbcDatabaseSnapshotGenerator.readColumns(JdbcDatabaseSnapshotGenerator.java:369)
        at liquibase.snapshot.jvm.JdbcDatabaseSnapshotGenerator.createSnapshot(JdbcDatabaseSnapshotGenerator.java:244)
        … 5 more


For more information, use the --logLevel flag)

I am lost. Has anyone any propositions?
Thanks in advance
Roman

It looks like postgres is throwing an error, but I don’t know what "<span class=“Apple-style-span” is saying. Can you tell?

Nathan

Wow, thanks for answer! I allmost have the Hope gone.

"<span class="Apple-style-span"

I think a SQL-Query is corrupt or something, but why i know not:-(

I must say, we have Postgresql DB, with views, types, procedures (sql, plpgsql), triggers.

For present, I will only Database-Schema export to evaluate liquibase to our Problem.

Kind regards
Roman

Sorry for the delay in answering, it was in my queue…


The offending line in code is:


statement.executeQuery(“SELECT " + database.escapeColumnName(schemaName, tableName, columnName) + " FROM " + database.escapeTableName(schemaName, tableName) + " WHERE 1 = 0”


which is used to determine if a column is auto-increment or not. It looks sounds like there is a table or column name that is generating bad sql. I wonder if one of your table names is a reserved word that needs to be escaped. Liquibase is attempting to escape some reserved words, but may be missing some. We cannot blindly escape all table names because that affects case sensitivity in postgres.


Nathan

Many thank for spending your time an me.

Our purpose is to manage databases with Version Control tools. I was overhelmed with liquibase. It would fit exactly to our purpose.

You hit the point, lot of tables in our databases have columns with name “date”. We have not experencied any issues so far.

It would help me mach in my research, if you could answer following questions:
Escape you allready reserved word “date”?
If not: will you consider that in future releases?

Kind regards from Germany
Roman

we have also tables with column name: “name”

Yes, we can definitely add date and  name to the list of words to escape in postgres. I’ll add it to trunk  for the 2.0.2 release.


You can create a custom subclass of PostgresDatabase that adds them yourself using the extension system (liquibase.org/extensions) if you want something sooner.


Nathan

Great thanks for your help.

I will follow the extensions way. More columns must be escaped and the number can even grow. I would implement extension that accept list of words that must be escaped, or something like this.

Kind regards
Roman

PS: Will be glad to hear of you in http://liquibase.jira.com/browse/CONTRIB

Hello,

how can i tell liquibase to take my custom database.

I have created jar archive with

PostgresEscapeStrictDatabase.java.
The Archive is in the same directory as liquibase.jar.

My custom class escaped all reserved words from postgres

Thanks
Roman