SVY 8 : SQL server connection issue

Questions and Answers on installation, deployment, management, locking, tranasactions of Servoy Application Server

SVY 8 : SQL server connection issue

Postby robrecht » Tue Jan 03, 2017 11:24 pm

Hi,

I'm trying to install a new application server for servoy 8.1.1
I keep getting error messages when connecting to the repository database

C:\Servoy_8_Demo\application_server>servoy_server.bat
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=128m; support was removed in 8.0
Log file: C:\Servoy_8_Demo\application_server\servoy_log.txt
Creating repository.
Error creating repository: com.servoy.j2db.persistence.RepositoryException: java.sql.SQLException: Cannot find the object "servoy_groups" because it does not exist or you do not have permissions.


In log file :

01-03 22:17:23,556 WARN [main] persistence.Server - Table name availability_group_listener_ip_addresses from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,556 WARN [main] persistence.Server - Table name availability_read_only_routing_lists from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,556 WARN [main] persistence.Server - Table name column_xml_schema_collection_usages from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,556 WARN [main] persistence.Server - Table name database_audit_specification_details from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,556 WARN [main] persistence.Server - Table name dm_cryptographic_provider_properties from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,556 WARN [main] persistence.Server - Table name dm_db_mirroring_auto_page_repair from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,556 WARN [main] persistence.Server - Table name dm_db_missing_index_group_stats from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,571 WARN [main] persistence.Server - Table name dm_db_xtp_nonclustered_index_stats from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,571 WARN [main] persistence.Server - Table name dm_exec_background_job_queue_stats from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,571 WARN [main] persistence.Server - Table name dm_exec_query_resource_semaphores from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,571 WARN [main] persistence.Server - Table name dm_exec_query_transformation_stats from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,571 WARN [main] persistence.Server - Table name dm_filestream_non_transacted_handles from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,571 WARN [main] persistence.Server - Table name dm_fts_semantic_similarity_population from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,571 WARN [main] persistence.Server - Table name dm_hadr_availability_group_states from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,571 WARN [main] persistence.Server - Table name dm_hadr_availability_replica_cluster_nodes from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,571 WARN [main] persistence.Server - Table name dm_hadr_availability_replica_cluster_states from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,571 WARN [main] persistence.Server - Table name dm_hadr_availability_replica_states from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,571 WARN [main] persistence.Server - Table name dm_hadr_database_replica_cluster_states from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,571 WARN [main] persistence.Server - Table name dm_hadr_database_replica_states from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,571 WARN [main] persistence.Server - Table name dm_os_buffer_pool_extension_configuration from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,571 WARN [main] persistence.Server - Table name dm_os_server_diagnostics_log_configurations from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,571 WARN [main] persistence.Server - Table name dm_resource_governor_configuration from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,571 WARN [main] persistence.Server - Table name dm_resource_governor_resource_pool_affinity from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,571 WARN [main] persistence.Server - Table name dm_resource_governor_resource_pool_volumes from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,571 WARN [main] persistence.Server - Table name dm_resource_governor_resource_pools from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,571 WARN [main] persistence.Server - Table name dm_resource_governor_workload_groups from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,571 WARN [main] persistence.Server - Table name dm_tran_active_snapshot_database_transactions from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,587 WARN [main] persistence.Server - Table name filetable_system_defined_objects from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,587 WARN [main] persistence.Server - Table name fulltext_semantic_language_statistics_database from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,587 WARN [main] persistence.Server - Table name message_type_xml_schema_collection_usages from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,587 WARN [main] persistence.Server - Table name parameter_xml_schema_collection_usages from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,587 WARN [main] persistence.Server - Table name registered_search_property_lists from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,587 WARN [main] persistence.Server - Table name resource_governor_configuration from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,587 WARN [main] persistence.Server - Table name resource_governor_resource_pool_affinity from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,587 WARN [main] persistence.Server - Table name resource_governor_resource_pools from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,587 WARN [main] persistence.Server - Table name resource_governor_workload_groups from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,587 WARN [main] persistence.Server - Table name server_audit_specification_details from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,587 WARN [main] persistence.Server - Table name service_contract_message_usages from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,587 WARN [main] persistence.Server - Table name system_components_surface_area_configuration from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,587 WARN [main] persistence.Server - Table name system_internals_allocation_units from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,587 WARN [main] persistence.Server - Table name system_internals_partition_columns from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:23,587 WARN [main] persistence.Server - Table name xml_schema_component_placements from server repository_server is too long (>30 chars) -- this is not supported by all databases [ ]
2017-01-03 22:17:24,587 ERROR [main] com.servoy.j2db.util.Debug - Throwable [ ]
com.servoy.j2db.persistence.RepositoryException: Error creating repository
at com.servoy.j2db.server.main.ApplicationServer.Za(ApplicationServer.java:305)
at com.servoy.j2db.server.main.Zqb.Za(Zqb.java:6)
at com.servoy.j2db.server.main.Zqb.start(Zqb.java:109)
at com.servoy.j2db.server.main.ApplicationServer.main(ApplicationServer.java:71)
Caused by: com.servoy.j2db.persistence.RepositoryException: java.sql.SQLException: Cannot find the object "servoy_groups" because it does not exist or you do not have permissions.
at com.servoy.j2db.server.Za.Zn.createRepositoryTables(Zn.java:1840)
at com.servoy.j2db.server.Za.Zo.Zc(Zo.java:1804)
at com.servoy.j2db.server.Za.Zo.Zb(Zo.java:1644)
at com.servoy.j2db.server.main.ApplicationServer.Za(ApplicationServer.java:307)
... 3 more
Caused by: java.sql.SQLException: Cannot find the object "servoy_groups" because it does not exist or you do not have permissions.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:723)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:1166)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:1119)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.servoy.j2db.datasource.Zg.invoke(Zg.java:8)
at com.sun.proxy.$Proxy4.executeUpdate(Unknown Source)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
at com.servoy.j2db.server.Za.Zn.createGroupsIndex(Zn.java:52)
at com.servoy.j2db.server.Za.Zn.recreateServoyGroups(Zn.java:1410)
at com.servoy.j2db.server.Za.Zn.createRepositoryTables(Zn.java:1879)
... 6 more



The credentials and URL are 100% correct, I copied them from a Servoy 7 installation and re-tested the connection string several times.

server.0.serverName=repository_server
server.0.URL=jdbc:jtds:sqlserver://localhost/servoy_repository
server.0.driver=net.sourceforge.jtds.jdbc.Driver
server.0.userName=sa
server.0.password=*********
server.0.schema=<none>
server.0.catalog=<none>


Is there something wrong with the shipped driver ?

Thanks
robrecht
 
Posts: 99
Joined: Wed Aug 01, 2012 4:30 pm

Re: SVY 8 : SQL server connection issue

Postby kwpsd » Wed Jan 04, 2017 12:47 am

...or you do not have permissions.


I received the same error message when installing 8.1.1 on a Windows 10 Pro system last week. If I recall, I was not running the installer with the requisite 'run as administrator' parameter. Perhaps, you are having permission problems as well?

I hope this helps!
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: SVY 8 : SQL server connection issue

Postby robrecht » Wed Jan 04, 2017 10:25 am

Hi,

Thanks for your advice, I ran the installer now as administrator but I keep getting the same error..
When changing the connection string I got a 'login failed' message, so it seems a connection is established
User is dbowner, SQL Server 2016
robrecht
 
Posts: 99
Joined: Wed Aug 01, 2012 4:30 pm

Re: SVY 8 : SQL server connection issue

Postby Jan Aleman » Wed Jan 04, 2017 6:09 pm

Have you tried using the Microsoft SQL driver as opposed to JTDS?
Jan Aleman
Servoy
Jan Aleman
 
Posts: 2083
Joined: Wed Apr 23, 2003 9:49 pm
Location: Planet Earth

Re: SVY 8 : SQL server connection issue

Postby jcompagner » Thu Jan 05, 2017 10:14 am

are you really connection to the same SQL Server instance that also Servoy 7 connects to?
Because there is really no change regarding the jdts driver or the way we connect compared to 7 that i know of.

The thing is it seems to be able to connect fine but doesn't find any tables (so it is not an existing repository database) and because it can't find the tables it tries to create them but is not allowed
This doesn't have anything to do with permission of the servoy install itself, but more about permissions inside the database for that users that you are using.
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8829
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: SVY 8 : SQL server connection issue

Postby robrecht » Thu Jan 05, 2017 9:33 pm

I tried this :

- Changed JTDS to SQL driver - same error
- Created a new database and ran a 'create table' statement in SSMS for testing (table successfully created) - same error
- Created a new user, new database - same error
- New installation on my VirtualBox Windows 10 environment with SQL 2014 - same error
- Connected to another SQL server instance (SQL 2012) on another machine with a new database, full user rights - same error
- Took a copy of an existing Servoy 7 installation, copied Servoy 8 property file with connection the newly created database in SQL 2012 instance - repository created instantly ...

So, all my tests with Servoy 8, with triple checking user rights, database connection, drivers, etc...They all failed, but connecting with the exact same property file with a Servoy 7 installation worked...

What else can I do / test ?
robrecht
 
Posts: 99
Joined: Wed Aug 01, 2012 4:30 pm

Re: SVY 8 : SQL server connection issue

Postby rgansevles » Fri Jan 06, 2017 11:37 am

Hi,

We are creating repository tables as part of our automatic test suite on the major databases, including sqlserver.
This runs as part of our continuous integration environment.

I have also tried this manually on our sqlserver db with the latest 8.1 code, all worked as expected.

I think something is different with your sa user setup.
Can you try with a default docker sqlserver image, I also tried this and it worked as expected.
See https://hub.docker.com/r/microsoft/mssql-server-linux/ or https://hub.docker.com/r/microsoft/mssq ... r-windows/

Rob
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: SVY 8 : SQL server connection issue

Postby robrecht » Fri Jan 06, 2017 2:53 pm

Hi,

We found what caused this error...
The repository database name was '_GEDE_servoy_repository_8'.
After changing the database name to 'repo', everything works fine.
Changing the database name to 're_po' causes the same error.
So an underscore is not allowed in the physical database name ?
Maybe only when altering objects ?

This is very familiar to the bug we recently logged - the case was rejected - in Servoy 7.4.9 : SVY-10704
I will make a new Jira case concerning the servoy 8 installation.

Robrecht
robrecht
 
Posts: 99
Joined: Wed Aug 01, 2012 4:30 pm

Re: SVY 8 : SQL server connection issue

Postby robrecht » Sat Jan 07, 2017 11:08 pm

FYI

Creating a new repository with Servoy 7.4.9 in combination with databases with underscores in its name doesn't work either...
robrecht
 
Posts: 99
Joined: Wed Aug 01, 2012 4:30 pm

Re: SVY 8 : SQL server connection issue

Postby rgansevles » Fri Jan 13, 2017 1:20 pm

We could finally reproduce this issue.
A fix has been made for release 7.4.10/8.1.2

Rob
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL


Return to Servoy Server

Who is online

Users browsing this forum: No registered users and 4 guests