Up to Discussions in General Topics

This Question is Assumed Answered

1 "correct" answer available (4 pts)
25 Replies Last post: Feb 4, 2010 4:42 PM by Leonid Bushuev  
Lou Syracuse Newbie
Lou Syracuse
9 posts since
Jul 20, 2009
Currently Being Moderated

Jul 27, 2009 6:33 PM

4.5 SQL Migration Failure

I am trying to migrate our system to a SQL Server backend but in the middle of the process I am receiving an error:

 

[2009-07-27 09:22:21,617]  DEBUG [           main] -     jetbrains.buildServer.dbmt - org.dbunit.dataset.NoSuchTableException: Did not find table 'AGENT' in schema 'null'
org.dbunit.dataset.NoSuchTableException: Did not find table 'AGENT' in schema 'null'
at org.dbunit.database.DatabaseTableMetaData.<init>(DatabaseTableMetaData.java:144)
at org.dbunit.database.DatabaseDataSet.getTableMetaData(DatabaseDataSet.java:292)
at org.dbunit.operation.AbstractOperation.getOperationMetaData(AbstractOperation.java:80)
at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:140)
at jetbrains.buildServer.serverSide.DBMTool.importToTargetDatabase(DBMTool.java:74)
at jetbrains.buildServer.serverSide.DBMTool.main(DBMTool.java:227)

 

The entire DBMT log file of the update is attached below.  This system started in 4.0 and was upgrated to 4.5 when we purchased the Enterprise licence.

 

Any assistance you can provide would be appreciated.  I've been at this for several dayssolving configuration and connectivity errors... I'm almost at the end.

 

Thanks,

Lou

Attachments:
Yegor Yarko JetBrains
Yegor Yarko
1,459 posts since
May 5, 2004
Currently Being Moderated
Jul 28, 2009 8:22 PM in response to: Lou Syracuse
Re: 4.5 SQL Migration Failure

Lou,

 

Can you please verify that the table "AGENT" exists in the database?

 

--
Best regards,

Yegor Yarko
QA Engineer (TeamCity)
JetBrains, Inc
http://www.jetbrains.com
"Develop with pleasure!"

Report Abuse
Lou Syracuse Newbie
Lou Syracuse
9 posts since
Jul 20, 2009
Currently Being Moderated
Jul 28, 2009 8:32 PM in response to: Yegor Yarko
Re: 4.5 SQL Migration Failure

The table AGENT is created in the SQL Database.  

 

How can I tell if it exists in the internal database - is there a tool I can use to examine the data, or a file I can browse?

Report Abuse
Yegor Yarko JetBrains
Yegor Yarko
1,459 posts since
May 5, 2004
Currently Being Moderated
Jul 28, 2009 8:53 PM in response to: Lou Syracuse
Re: 4.5 SQL Migration Failure

Lou,

 

Actually the log displays that table "agent" is present. You can run the script with "preserve" option and the data will be saved in CSV format in a directory under "bin". The cause may be in case differences. Is it possible to switch the database into cases-insensitive mode for table names?

Report Abuse
Lou Syracuse Newbie
Lou Syracuse
9 posts since
Jul 20, 2009
Currently Being Moderated
Jul 29, 2009 1:09 AM in response to: Yegor Yarko
Re: 4.5 SQL Migration Failure

We're giving up on migrating.    We were able to run the migrate program with no errors, but all of our builds and users were gone.  I was able to restore a copy of our data and get it running.  When I tried to migrate it again (after wiping out the tables in SQL Server) it errored out in a different place.

 

We don't care about the history anyway.  We just need to use SQL Server as a datastore going forward, and hopefully not have to re-build all of our existing builds.    What we want is what the documentation calls a 'switch': "..no data migration: build configurations settings will be preserved, but not the historical builds data or users.

 

But I can't get that to work either.

 

My database properties file reads:

driverName=net.sourceforge.jtds.jdbc.Driver
connectionUrl=jdbc:jtds:sqlserver://prsf:1433/buildserver/instance=webtest

connectionProperties.user=teamcity
connectionProperties.password=buildcode

maxConnections=50
poolPreparedStatements=true

 

Apparently it does not like the named instance parameter, because when I restart the server I get this in the teamcity-server.log:

 

[2009-07-28 15:35:32,215]   WARN - rverSide.impl.DatabaseSettings - Unknown database property: /instance. Known properties :
[maxConnections, poolPreparedStatements, maxIdleConnections, keepConnectionPool, testOnBorrow, driverName, validationQuery, connectionProperties, shutdownManagerFactory, connectionUrl]

I get more errors below that, they are kind of long so I didn't paste them here and it looks like the cause of the problem is the error above as it can't find my SQL server.

 

Any ideas?

Report Abuse
Leonid Bushuev JetBrains
Leonid Bushuev
149 posts since
Sep 30, 2002
Currently Being Moderated
Jul 29, 2009 3:45 PM in response to: Lou Syracuse
Re: 4.5 SQL Migration Failure

Lou,

 

could you please attach whole your database.properties file as is?

 

Thanks,

Leonid

Report Abuse
Leonid Bushuev JetBrains
Leonid Bushuev
149 posts since
Sep 30, 2002
Currently Being Moderated
Jul 29, 2009 4:13 PM in response to: Lou Syracuse
Re: 4.5 SQL Migration Failure

Lou,

 

the syntax of JTDS driver (that is used in this case) requires a semicolon before list of connection options in URL.

 

> connectionUrl=jdbc:jtds:sqlserver://prsf:1433/buildserver/instance=webtest

 

In the your excerpt from the "database properties" I see a slash instead of semicolon. Could you please replace the last slash with a semicolon to get

 

  connectionUrl=jdbc:jtds:sqlserver://prsf:1433/buildserver;instance=webtest

 

and try again?

 

Thanks,

Leonid

Report Abuse
Lou Syracuse Newbie
Lou Syracuse
9 posts since
Jul 20, 2009
Currently Being Moderated
Jul 29, 2009 6:04 PM in response to: Leonid Bushuev
Re: 4.5 SQL Migration Failure

Well, I think that helped... but I'm so lost in this after spending 4 days trying to set this progarm up to use SQL Server that I'm not sure. 

 

I REALLY appreciate your help!!!    You have no idea how many times I looked at that file and didn't see that.

 

I made the change, but it is still not working.   I have attached my database.properties file, as well as the server_log and Localhost logs that were generated after I restarted the TeamCity web server.    We're a .NET shop, nobody here knows beans about beans so we're having problems trying to decypher how to resolve what the logs are telling us.

 

The department head says if we have to reconstruct the build configurations we'll do that, although it is not our first choice.   But I can't even get the system to the point where it is using SQL Server.   Should I just uninstall, delete the directories and start again installing only version 4.5?

 

Thanks again for your help!!!!

Lou

Attachments:
Report Abuse
Leonid Bushuev JetBrains
Leonid Bushuev
149 posts since
Sep 30, 2002
Currently Being Moderated
Jul 29, 2009 6:34 PM in response to: Lou Syracuse
Re: 4.5 SQL Migration Failure

Lou,

 

I've found the "Network error IOException: Connection refused: connect" message in the logs you've attached. It looks like the TeamCity server could not connect to the MS SQL server (may be network troubles?) or like the "connectionUrl" property assigned incorrectly.

 

What I can suggest to do in this case:

 

  • check wether the MS SQL server can be accessed from the host where TeamCity server is installed;
  • specify the full name or even IP address in the "connectionUrl" property.

 

Did it help?

Report Abuse
Lou Syracuse Newbie
Lou Syracuse
9 posts since
Jul 20, 2009
Currently Being Moderated
Jul 29, 2009 7:54 PM in response to: Leonid Bushuev
Re: 4.5 SQL Migration Failure

I have it working!!!

 

After reading your last message I tried every combination I could think of with the connection information and it still woudln't work.  So I did what I should have done days ago:   I had the DBA move my database to a SQL Server without a named instance.  

 

No problems, fired up the first time.  Tables created, my builds are there.    I did a test build on one of the projects and it worked perfectly.

 

Now I just have to reconfigure the users; we just updated to 4.5 Enterprise and wanted to change to LDAP authentication anyway thats not a big deal.

 

VICTORY!!

 

Thanks again for all your help Leonid - I couldn't have done it without you!

 

Lou

Report Abuse
Scott Lawrence Newbie
Scott Lawrence
1 posts since
Aug 7, 2009
Currently Being Moderated
Aug 7, 2009 1:20 AM in response to: Leonid Bushuev
Re: 4.5 SQL Migration Failure

I'm having problems migrating from the internal HSQLDB to MS SQL Server Express 2005 for TeamCity 4.5.4.

 

I downloaded jtds-1.2.2.jar and placed it in C:\TeamCity\webapps\ROOT\WEB-INF\lib

 

I changed dbMigration.properties to what I've attached to this message.

 

But when I execute "migrateDB.bat migrate" from the command line, I get the following error message:

 

[2009-08-06 17:10:01,708]  ERROR -     jetbrains.buildServer.dbmt - Error connec

ting to the target database (URL:jdbc:jtds:sqlserver://smsbuild2.smsdev.local/Te

amCity;instance=SQLEXPRESS, user:TeamCityUser, password:asdfjkl;), make sure the

specified values are correct and modify them in the properties file if necessar

y

 

See error details in the: ../logs/teamcity-dbmt.log

Closing source database connection...

Done.

Critical error has occurred during migration.

Done.

I've attached the log file with the result.  The SQL Server Express instance is on the same machine as the TeamCity server.  Any help you could provide would be greatly appreciated.

 

Thanks!



Attachments:
Report Abuse
Leonid Bushuev JetBrains
Leonid Bushuev
149 posts since
Sep 30, 2002
Currently Being Moderated
Aug 7, 2009 3:46 PM in response to: Scott Lawrence
Re: 4.5 SQL Migration Failure

Scott,

 

The DB migration tool failed to connect to your MS SQL server in this case.

 

Please, try the following:

  1. check the targetURL for errata

  2. replace smsbuild2.smsdev.local with localhost - may be MS SQL Express Edition binds to localhost only by default

  3. remove extra parameters from targetURL (the semicolon and whole stuff after it)

  4. try to use any 3rd-party tool (that allows to connect to MS SQL) for ensuring that the connection url is correct and the MS SQL server accepts connections

 

Please, let me know did it help.

 

Best regards,

Leonid

Report Abuse
geek geek Newbie
geek geek
6 posts since
Jul 29, 2009
Currently Being Moderated
Jul 29, 2009 12:58 PM in response to: Lou Syracuse
Re: 4.5 SQL Migration Failure

I am having a similar issue.

teamcity_dbmt.log:

 

[2009-07-29 09:45:33,073]   INFO [           main] -     jetbrains.buildServer.dbmt - Using propeties file: dbMigration.properties
[2009-07-29 09:45:33,089]   INFO [           main] -     jetbrains.buildServer.dbmt - Connecting to the source database: jdbc:hsqldb:file:C:Documents and SettingsAdministrator.BuildServersystembuildserver
[2009-07-29 09:45:33,323]   INFO [           main] -     jetbrains.buildServer.dbmt - Connecting to the target database: jdbc:jtds:sqlserver://IP:PORT/DB
[2009-07-29 09:45:33,620]   INFO [           main] -     jetbrains.buildServer.dbmt - Initializing schema...
[2009-07-29 09:45:34,495]   INFO [           main] -     jetbrains.buildServer.dbmt - Saving C:\TeamCity\bin\export\targetSchema.sql
[2009-07-29 09:45:34,511]   INFO [           main] -     jetbrains.buildServer.dbmt - Tables to be converted (58): [[action_history, agent, agent_build_type_access, agent_sources_version, build_checkout_rules, build_data_storage, build_dependency, build_labels, build_queue, build_revisions, build_runtime_info, build_state, build_type_vcs_change, canceled_info, comments, compiler_output, default_build_parameters, deleted_build_types, downloaded_artifacts, failed_tests_output, history, ignored_tests, light_history, paused_build_types, personal_build_relative_path, personal_vcs_changes, personal_vcs_history, projects, remember_me, remote_run_build_types, run_configuration_policy, running, server, server_property, stats, tc_build_tags, test_failure_rate, test_info, test_info_trunk, test_names, user_blocks, user_build_parameters, user_notification_events, user_projects_order, user_projects_visibility, user_property, user_roles, user_watch_type, usergroup_notification_events, usergroup_roles, usergroup_subgroups, usergroup_users, usergroup_watch_type, usergroups, users, vcs_changes, vcs_history, vcs_root_versions]]
[2009-07-29 09:45:34,511]   INFO [           main] -     jetbrains.buildServer.dbmt - Exporting to C:\TeamCity\bin\export
[2009-07-29 09:45:34,589]   INFO [           main] -     jetbrains.buildServer.dbmt - Saving C:\TeamCity\bin\export\sourceSchema.sql
[2009-07-29 09:45:34,620]  ERROR [           main] -     jetbrains.buildServer.dbmt - Failed to export source database

[2009-07-29 09:45:34,620]  DEBUG [           main] -     jetbrains.buildServer.dbmt - java.sql.SQLException: Table not found in statement [SELECT COUNT(*) FROM action_history]
java.sql.SQLException: Table not found in statement [SELECT COUNT(*) FROM action_history]
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.executeQuery(Unknown Source)
at jetbrains.buildServer.serverSide.DBMTool.dumpSizes(DBMTool.java:3)
at jetbrains.buildServer.serverSide.DBMTool.exportSourceDatabase(DBMTool.java:121)
at jetbrains.buildServer.serverSide.DBMTool.main(DBMTool.java:154)
[2009-07-29 09:45:34,620]   INFO [           main] -     jetbrains.buildServer.dbmt - Closing source database connection...
[2009-07-29 09:45:34,620]   INFO [           main] -     jetbrains.buildServer.dbmt - Closing connection...
[2009-07-29 09:45:34,636]   INFO [           main] -     jetbrains.buildServer.dbmt - Closing target database connection...
[2009-07-29 09:45:34,636]   INFO [           main] -     jetbrains.buildServer.dbmt - Done.

 

The destination database is populated with tables (although there are no FKs and all columns seem to be nullable).  targetSchema.sql has various sql statements.  sourceSchema.sql is empty.

 

Please help!

Report Abuse
Leonid Bushuev JetBrains
Leonid Bushuev
149 posts since
Sep 30, 2002
Currently Being Moderated
Jul 29, 2009 2:17 PM in response to: geek geek
Re: 4.5 SQL Migration Failure

Geek,

 

which version of TeamCity did you use before migration? have you started the new version (4.5) on the internal database (HSQLDB) before migration?

 

 

Thanks,

Leonid

Report Abuse
geek geek Newbie
geek geek
6 posts since
Jul 29, 2009
Currently Being Moderated
Jul 29, 2009 2:19 PM in response to: Leonid Bushuev
Re: 4.5 SQL Migration Failure

Sorry forgot about that.  Yes we are using 4.5.  It was downloaded and installed just a few days ago.  We have already set up some builds so would like to preserve everything.

Report Abuse
geek geek Newbie
geek geek
6 posts since
Jul 29, 2009
Currently Being Moderated
Jul 30, 2009 10:51 AM in response to: geek geek
Re: 4.5 SQL Migration Failure

Any ideas?  Do you need more information?

Report Abuse
Leonid Bushuev JetBrains
Leonid Bushuev
149 posts since
Sep 30, 2002
Currently Being Moderated
Jul 30, 2009 7:11 PM in response to: geek geek
Re: 4.5 SQL Migration Failure

Geek,

 

could you please attach the following two files

  • version.dat
  • buildserver.script

from the <TeamCity_Home>/system directory?

 

Thanks,

Leonid

Report Abuse
geek geek Newbie
geek geek
6 posts since
Jul 29, 2009
Currently Being Moderated
Jul 30, 2009 7:39 PM in response to: Leonid Bushuev
Re: 4.5 SQL Migration Failure

Attached

Attachments:
Report Abuse
Leonid Bushuev JetBrains
Leonid Bushuev
149 posts since
Sep 30, 2002
Currently Being Moderated
Jul 30, 2009 8:37 PM in response to: geek geek
Re: 4.5 SQL Migration Failure

Both files are good, and all should work.

 

It seams a mistake with paths. Please check your dbMigration.properties file. It should contain absolute paths and must use only unix slashes (/) as folder separators, not ms-dos backslashes (\). Backslash is treated as an escape character.

 

Path to the database should include database file name but should not include file name extensions.

 

For example, the sourceURL on MS Windows host can look like

 

    sourceURL=jdbc:hsqldb:file:C:/Documents and Settings/MyName/.BuildServer/system/buildserver

 

Check paths and try again.

 

Please, let me know did it help.

 

 

With best regards,

Leonid

Report Abuse
geek geek Newbie
geek geek
6 posts since
Jul 29, 2009
Currently Being Moderated
Jul 30, 2009 11:50 PM in response to: Leonid Bushuev
Re: 4.5 SQL Migration Failure

Hi Leonid,

 

Thanks for your help - I am out of the office until Monday so I phoned a collegue (no external access to the server) and he confirmed that the slashes are wrong.  I will try again on Monday and let you know how I get on.

 

Thanks

Report Abuse
geek geek Newbie
geek geek
6 posts since
Jul 29, 2009
Currently Being Moderated
Aug 3, 2009 4:00 PM in response to: Leonid Bushuev
Re: 4.5 SQL Migration Failure

That sorted it - thanks for your help.

Report Abuse
jack jill Newbie
jack jill
2 posts since
Dec 2, 2009
Currently Being Moderated
Dec 2, 2009 7:25 PM in response to: Leonid Bushuev
Re: 4.5 SQL Migration Failure

Hi There,

 

I am getting exactly the same error on windows platform. I am also using latest version of TeamCity version which is 4.5. Please guide how to start server on internal HSQLDB databse?

 

Hope I am not missign anythign out.

 

Please look at the error for your kind ref.

 

[2009-12-02 16:08:22,241]  ERROR [           main] -     jetbrains.buildServer.dbmt - Failed to export source database

[2009-12-02 16:08:22,272]  DEBUG [           main] -     jetbrains.buildServer.dbmt - java.sql.SQLException: Table not found in statement [SELECT COUNT(*) FROM action_history]
java.sql.SQLException: Table not found in statement [SELECT COUNT(*) FROM action_history]
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.executeQuery(Unknown Source)
at jetbrains.buildServer.serverSide.DBMTool.dumpSizes(DBMTool.java:214)
at jetbrains.buildServer.serverSide.DBMTool.exportSourceDatabase(DBMTool.java:201)
at jetbrains.buildServer.serverSide.DBMTool.main(DBMTool.java:176)
[2009-12-02 16:08:22,272]   INFO [           main] -     jetbrains.buildServer.dbmt - Closing source database connection...
[2009-12-02 16:08:22,288]   INFO [           main] -     jetbrains.buildServer.dbmt - Closing connection...
[2009-12-02 16:08:22,303]   INFO [           main] -     jetbrains.buildServer.dbmt - Closing target database connection...
[2009-12-02 16:08:22,303]   INFO [           main] -     jetbrains.buildServer.dbmt - Done.

Please advice. Please have a look at the attached files for your kind ref. Your prompt response will help me a lot.

 

 

Many Many Thanks

Jackjill.

Attachments:
Report Abuse
Leonid Bushuev JetBrains
Leonid Bushuev
149 posts since
Sep 30, 2002
Currently Being Moderated
Feb 4, 2010 4:42 PM in response to: jack jill
Re: 4.5 SQL Migration Failure

Hi Jack,

 

is the question still actual?

 

--

Leonid

Report Abuse
Peter Niederwieser Novice
Peter Niederwieser
94 posts since
Sep 28, 2007
Currently Being Moderated
Aug 16, 2009 10:51 PM in response to: geek geek
Re: 4.5 SQL Migration Failure

I had the same problem when migrating from hsqldb to mysql (ubuntu). The cause was that tables in mysql were created with lower case names, but select statements used upper case names. To solve the problem, I had to add the following line to the [mysqld] section of /etc/mysql/my.cnf:

lower_case_table_names = 1

 

Cheers,

Peter

Report Abuse
jack jill Newbie
jack jill
2 posts since
Dec 2, 2009
Currently Being Moderated
Dec 2, 2009 7:19 PM in response to: geek geek
Re: 4.5 SQL Migration Failure

Hi There,

 

I am getting exactly the same error runing on windows platform. I tried to sort that using options given, but still not working.

 

Please help.

 

Thanks very much,

jackjill

Report Abuse
Ben MacLeod Newbie
Ben MacLeod
1 posts since
Aug 17, 2009
Currently Being Moderated
Aug 17, 2009 5:44 AM in response to: Lou Syracuse
Re: 4.5 SQL Migration Failure

Hi all,

FWIW, I also had the same problem migrating to from the default (HSQLDB) database to PostgreSQL DB on Windows 2003, and the problem was solved as per Leonid's advice on using the correct form of the connection string to connect to default database, i.e. slashes ("/") not backslashes ("\"), but otherwise as per the usual Windows pathnames, e.g.:

 

   sourceURL=jdbc:hsqldb:file:C:/Documents and Settings/MyName/.BuildServer/system/buildserver

 

Cheers,

Ben

Report Abuse

More Like This

  • Retrieving data ...