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!"

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?

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?

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?

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!

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

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.

Lou,

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

Thanks,
Leonid

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

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

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?

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

Any ideas?  Do you need more information?

Geek,

could you please attach the following two files

• version.dat
• buildserver.script

from the <TeamCity_Home>/system directory?

Thanks,
Leonid

Attached

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

That sorted it - thanks for your help.

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.

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

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

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

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

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.

Hi Jack,

is the question still actual?

--
Leonid