Database connection test in pro version

[ethereal]

Got a very basic setup with a strange behaviour.

when i connect with the test statement to a table with time values i got this..

Code: Select all

        DB Manager Activated ( com.mysql.jdbc.Driver )
22:45:14.390 - [ SEVERE ] > DbManager error during query/result creation: SELECT * FROM users

java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 10
 to TIMESTAMP.

it.gotoandplay.smartfoxserver.exceptions.DbManagerException: The Test SQL statem
ent failed! Please check your configuration.

--removed the rest of the crap--    
 

when i connect to the same table with :

SELECT id FROM users

its going fine. its a little confusing. or do i miss something?
it looks weird behaviour to me.
got the same connection with tomcat working.


grtz

Ethereal

[Lapo]

when i connect with the test statement to a table with time values i got this..

humm... You mean you have a field of type TIME ? or DATETIME?

Actually I've done a test right now, using a simple table with these fields:

Code: Select all

id - INTEGER
when - TIME
how - VARCHAR (50)

and using the same SQL test code:

Code: Select all

SELECT * FROM sfs_test

and it works without problems.

Questions:

- what MySQL version are you using?
- are you sure the jdbc connector is up to date?

Maybe could you post here the structure of your database table?

Thanks

[ethereal]

What a incredible fast respons Lapo, hope you can keep it this way.
Great products are mostly going down by lack off response.

//System config

dual P3 1000Mhz 1 gig memory, running on a RAID2 system.
OS, Debian stable version.
Webmin.
mysql. ver 4.17 distrib 4.1.11

minimum table config mysql

id int(11) No Primary auto_increment
Date1 datetime No None 0000-00-00 00:00:00
Date2 datetime Yes None 0000-00-00 00:00:00

smartfoxserver config

dual p3 1100 Mhz 2 gig memory. RAID3 system
windows2003 server,
apache webserver
java 1.5 sdk Java
tomcat.
smartfox
Ethereal.

searched for lost jars on the disks, not found any old versions,


tried it with a couple off other java applets, without any problems.
downloaded the latest version of the jdbc driver from mysql.com

mysql-connector-java-3.2.0-alpha-bin.jar,

Tried all version from 3.0 and up, they all have exactly the same error as respons.

Weird is that its functioning good after the test has passed.
What brings me to the bugs section.
when i select the same table exactly the same way (SELECT * FROM users) its not passing any error att all.

i'm pretty new to flash and smartfox so its possible that i miss something, but this is so basic that it can't go wrong, the documents that are in the smartfoxserver dir are a treasure for almost all the questions i see here on the board. Great job, and google will answer the other questions.

If you want it translated to dutch, just give a sign.

O found another small detail in the documents section, you refer to smartFox.joinRoom() Whats not working.
what does work smartfox.joinRoom()

grtz

Roel.

[Lapo]

At the moment I can't reproduce the problem...
I've created a table that should look similar to yours and tested the same SQL code but no errors appear.

This is the table structure:

Code: Select all

CREATE TABLE `sfs_test` (
  `id` int(11) NOT NULL auto_increment,
  `Date1` datetime NOT NULL default '0000-00-00 00:00:00',
  `Date2` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

Then I've added a few records:

Code: Select all

INSERT INTO `sfs_test` VALUES (4, '2005-11-10 00:00:00', '2005-11-04 00:00:00');
INSERT INTO `sfs_test` VALUES (3, '2005-11-09 00:00:00', '2005-11-10 00:00:00');
INSERT INTO `sfs_test` VALUES (5, '2005-11-26 00:00:00', '2005-11-29 00:00:00');
INSERT INTO `sfs_test` VALUES (6, '2005-11-03 00:00:00', '2005-11-04 00:00:00');
INSERT INTO `sfs_test` VALUES (7, '2005-11-05 00:00:00', '2005-11-06 00:00:00');

And the test SQL is:

Code: Select all

SELECT * FROM sfs_test

Maybe you could try the same on your side and see if you still get the problem.
Note: If you have a very large database, using the above test sql would waste time and resources as you would create a very long result set. I'd suggest using something simpler like this:
SELECT count(*) FROM sfs_test

I forgot to ask what version of SmartFoxServer PRO are you using? I use 1.3.0

O found another small detail in the documents section, you refer to smartFox.joinRoom() Whats not working.
what does work smartfox.joinRoom()

Well, that's not actually a mistake... smartFox just indicates any instance of the SmartFoxClient class. In our examples we used smartFox in almost all examples but it could be any variable name... hope it's clear

[Lapo]

The JDBC connector I am using is >> mysql-connector-java-3.1.10-bin.jar

I would reccomend to use the latest stable release. The mysql-connector-java-3.2.0-alpha-bin.jar that you mentioned is still alpha version

[ethereal]

I know the last jdbc connector is very alpha, but you got to figure out some things yourself before writing some problems.
as i wrote earlier, i tried all the jdbc connectors

i gonna reproduce your setup, even when i have a work around, i still want to figure out what is causing this behaviour.

i will use another clean machine for it and see what is happening then.

The query SELECT * FROM blabla should never be used in a good dbase structure. i only use this in a test environment.

its like writing a whole program with loops (yes its possible but ugly and slow)

i use pro (dehu dbase connection) 1.3.0.

The JDBC connector I am using is >> mysql-connector-java-3.1.10-bin.jar

I would reccomend to use the latest stable release. The mysql-connector-java-3.2.0-alpha-bin.jar that you mentioned is still alpha version

[ethereal]

Hi lapo,

I believe i figured it out, Loaded a new system to a machine.
got the exact same setup as you told me with as a result .

Code: Select all

+--------------------[ Licence Loaded ]----------------------+

Licence Type: PRO
Licenced to : --== Free Demo Licence ==--
Max. Clients: 20


+---------------------[ Zones & Rooms ]----------------------+

        DB Manager Activated ( com.mysql.jdbc.Driver )

and changed your SQL values fo the datetime fields a little bit. with the values you wrote its working perfectly, 'but change your datetime fields to zero's give the exact same error again.

no need to try another sql query here, its the values in the fields what is causing this behaviour, and thats exactly what the error message is telling.

This is also a problem on my site.
The database it should use should never accept a datetime value filled with zero's so i gonna break my own code too, grr
Still its pretty confusing if you get this error with the basic setup, a error capture would be nice anyway.
In the dbase i used i have around 1900 rows in my table with 2 datetime fields each, noway that i gonna check them all by hand
so now smartfox is also a debugging tool just capture the errors and find the rows with zero values

Code: Select all

19:52:32.421 - [ SEVERE ] > DbManager error during query/result creation: SELECT
 * FROM sfs_test
java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 2
to TIMESTAMP.
it.gotoandplay.smartfoxserver.exceptions.DbManagerException: The Test SQL statem
ent failed! Please check your configuration.
        at it.gotoandplay.smartfoxserver.SmartFoxServer.setupZone(SmartFoxServer
.java:1498)

Grtz

Roel

[Lapo]

I am glad you trapped the problem!

Still its pretty confusing if you get this error with the basic setup, a error capture would be nice anyway.

What do you mean, exactly?

[ethereal]

Well, i'm thinking off switching my systems to as far as i can see now its gonna be smartfoxserver.

I have already a database in use thats woking good even with the zero values.
Its not exactly a programing mistake on my side.
I can think off building a dbase system with zero daatetime values many times, what would cause the smartfoxserver to quit with a restart, doesnt sounds pretty to me, run a cronJob and your system is down.

i think that its not that bad to trap this before holding the startup procedure off smartfoxserver.


could you reproduce it by the way?


Grtz

Roel

[Lapo]

I can think off building a dbase system with zero daatetime values many times, what would cause the smartfoxserver to quit with a restart,

No, this won't happen. The server quits ONLY at boot-time if your test query is not working. This is because, if there's a problem with the database, it is important that you fix it before running your application.
If the error doesn't show up at boot time, the server will not stop if it will encounter this error while running. It will just log the error so that you will know that a problem was found with the DB.

The fact that the server quits (when it's being launched) on these types of errors will help you to immediately turn your attention to the problem before running the application. This is the typical scenario when deploying a multiuser application from a test environment to the production machines.

could you reproduce it by the way?

Not yet, I will try it as soon as possible

[ethereal]

Hmmz,

This sounds a little weird to me.
Its perfectly accepted in a database to have nul values in a field.
that this was happening on a database where i CHOOSE NOT TO ACCEPT 00:00:00 values is a choice, not a standard!!

I can think off building a dbase system with zero daatetime values many times, what would cause the smartfoxserver to quit with a restart

No, this won't happen. The server quits ONLY at boot-time if your test query is not working. This is because, if there's a problem with the database, it is important that you fix it before running your application.
If the error doesn't show up at boot time, the server will not stop if it will encounter this error while running. It will just log the error so that you will know that a problem was found with the DB.,

Sorry, its going down at boottime, at restart, and when reloading zone extensions!!

you are right that a running server without a LOAD action will keep running!!



And kick me if i'm wrong, but a server is going down when a database TEST is not able to convert a value? a normal value ??
sounds NOT good to me.
debugged a little further and its not MYSQL that is parsing the error its even not java, its really smartfox that fucksup here (forgive me the words)

I can reproduce ths with smartfox on any machine with any config.
access shows the same problem, a clean database shows same problem. i'm a zillion % sure that it has nothing to do with corrupted database, even a one fielder holds the server

So i will stuck with my solution, and go sit back waiting until someone notice the problem, i did my part


grtz,

Roel.

I can think off building a dbase system with zero daatetime values many times, what would cause the smartfoxserver to quit with a restart,

No, this won't happen. The server quits ONLY at boot-time if your test query is not working. This is because, if there's a problem with the database, it is important that you fix it before running your application.
If the error doesn't show up at boot time, the server will not stop if it will encounter this error while running. It will just log the error so that you will know that a problem was found with the DB.

The fact that the server quits (when it's being launched) on these types of errors will help you to immediately turn your attention to the problem before running the application. This is the typical scenario when deploying a multiuser application from a test environment to the production machines.

could you reproduce it by the way?

Not yet, I will try it as soon as possible

[Lapo]

This sounds a little weird to me.
Its perfectly accepted in a database to have nul values in a field.
that this was happening on a database where i CHOOSE NOT TO ACCEPT 00:00:00 values is a choice, not a standard!!

Yeah, I do agree. You can use whatever value. The problem is I can't reproduce your error.

Here follows a screenshot of the test database I use, as you can see I've filled the Date1 field with NULL values.



I use:

Code: Select all

SELECT * FROM sfs_test

as test SQL code.
And no error is shown when SmartFoxServer is launched.

So I guess the problem is somewhere else
Could you please post your database structure?

Sorry, its going down at boottime, at restart, and when reloading zone extensions!! you are right that a running server without a LOAD action will keep running!!

Boot-time, restart and zone reload all require integrity in the database connection. Also you shouldn't be able to restart or reload if the server halted at boot-time complaining for problems with the database ...

And kick me if i'm wrong, but a server is going down when a database TEST is not able to convert a value? a normal value ??
sounds NOT good to me.

Of course not. The server never crashes for a database problem but it will report it. If you run a query with wrong syntax or data-type conversion maybe your multiuser application will stop to work correctly but the server will keep doing its job without problems.

The server refuses to start only if there's a problem with the initial test SQL command. It's just like if your car refuses to start because you have a flat tire, and it says... "Hey mate, you've got a flat tire... please fix it before going".

debugged a little further and its not MYSQL that is parsing the error its even not java, its really smartfox that fucksup here (forgive me the words)

Actually the one who's complaining here is the JDBC database driver
SmartFox just traps the error and spits it out.

I will need a copy of your database to try to reproduce the error. Please contact us and send a mysql dump of your DB. We will try it locally and see what's going on

Thanks