Mysql connection

Elgueu · Post by **Elgueu** » 29 May 2006, 13:56

Hello,

In the above example there's also an example (between comment signs) on how to use the MySql driver. (not provided by Sun JRE, must be downloaded from www.mysql.com)

May we have a little tutorial about all this mysql stuff ?
I have a simple Linux online server, i installed SFS PRO 1.4, and i can't figure out how to make a good connection to a mysql database. The doc tells us that we have to install extra features to make mysql available through SFS, but is it really necessary ? And if yes, could someone help me on this point please ? ^^
Thanks

allexiel · Post by **allexiel** » 29 May 2006, 18:11

I think it's well explained in the docs

have a look here:

http://www.smartfoxserver.com/docs/docP ... ection.htm

Elgueu · Post by **Elgueu** » 29 May 2006, 18:25

No, if i ask here it's because i didn't find any solution in the doc ^^
I tried using the mysql code from the doc, but i got some errors telling me that the drivers are not available

allexiel · Post by **allexiel** » 29 May 2006, 18:31

Well, the docs says it's really necessary, so it must be

Install this JDCB drivers in the correct directory as stated in the docs.

Your error code: Driver not available,
is pretty self-explaining what the problem is.

Virusescu · Post by **Virusescu** » 29 May 2006, 18:52

Allexiel... I had the same problem as Elqueu. I for instance didn't had too much knowledge about how Java works, where I can install the aditional files and so on.

Elqueu. I can share my story with you, although I installed it on a Windows machine, but maybe it helps

.

First of all, you need to download the correct file from the www.mysql.com website. This 'correct' file is the java connector that helps your Java VM to connect to a mysql database.
Inside this section http://dev.mysql.com/downloads/ at the "Drivers and Connectors" you need to download the "MySQL Connector/J -- for connecting to MySQL from Java" corresponding to your installed Java version on the server.
Then (on a windows instalation of the Java environment at least) you need to copy that file to the Java path / lib / ext.
So find the folder where java is installed. Inside there should be a lib folder and inside that should be an ext folder. Copy the file "mysql-connector-java-3.1.12-bin.jar" (or whatever file you dowloaded for your version) in that folder.
After you copy the file you need to change the settings as in the doc files for your dbconnector. And I mean it like I say it. You don't need to change anything (apart from the ip, username and password

).
The driver is always named like this "org.gjt.mm.mysql.Driver"

Then, after succesfully configuring the xml file, just restart the server.

If you have problems configuring the xml file just "yell" here.

Elgueu · Post by **Elgueu** » 29 May 2006, 20:09

I downloaded and uploaded the .jar file (i hope i took the good one, but i don't know anything about the version of java currently installed). Anyway the jar file has the same name as the one you just gave me.. must be a good sign =)

I don't have the same errors now, we're getting closer :

Code: Select all

<DatabaseManager active="true">

	<Driver>org.gjt.mm.mysql.Driver</Driver>
	<ConnectionString>jdbc:mysql://192.168.0.15:3306/***</ConnectionString>

	<UserName>***</UserName>
	<Password>***</Password>


	<TestSQL><![CDATA[SELECT map FROM world_map]]></TestSQL>


	<MaxActive>10</MaxActive>
	<MaxIdle>10</MaxIdle>


	<OnExhaustedPool>fail</OnExhaustedPool>
	<BlockTime>5000</BlockTime> 

</DatabaseManager>

and here is the console log (but i can't figure out where does the error come) :

Code: Select all

DB Manager Activated ( org.gjt.mm.mysql.Driver )
jvm 1    | 22:19:38.392 - [ SEVERE ] > DbManager could not retrive a connection. com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: 
jvm 1    | 
jvm 1    | ** BEGIN NESTED EXCEPTION ** 
jvm 1    | 
jvm 1    | java.net.SocketException
jvm 1    | MESSAGE: java.net.ConnectException: Connection timed out
jvm 1    | 
jvm 1    | STACKTRACE:
jvm 1    | 
jvm 1    | java.net.SocketException: java.net.ConnectException: Connection timed out
jvm 1    | 	at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:156)
jvm 1    | 	at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:284)
jvm 1    | 	at com.mysql.jdbc.Connection.createNewIO(Connection.java:2555)
jvm 1    | 	at com.mysql.jdbc.Connection.<init>(Connection.java:1485)
jvm 1    | 	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
jvm 1    | 	at java.sql.DriverManager.getConnection(Unknown Source)
jvm 1    | 	at java.sql.DriverManager.getConnection(Unknown Source)
jvm 1    | 	at org.apache.commons.dbcp.DriverManagerConnectionFactory.createConnection(DriverManagerConnectionFactory.java:48)
jvm 1    | 	at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:290)
jvm 1    | 	at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:771)
jvm 1    | 	at org.apache.commons.dbcp.PoolingDriver.connect(PoolingDriver.java:175)
jvm 1    | 	at java.sql.DriverManager.getConnection(Unknown Source)
jvm 1    | 	at java.sql.DriverManager.getConnection(Unknown Source)
jvm 1    | 	at it.gotoandplay.smartfoxserver.db.DbManager.executeQuery(DbManager.java:209)
jvm 1    | 	at it.gotoandplay.smartfoxserver.db.DbManager.executeQuery(DbManager.java:195)
jvm 1    | 	at it.gotoandplay.smartfoxserver.SmartFoxServer.setupZone(SmartFoxServer.java:1616)
jvm 1    | 	at it.gotoandplay.smartfoxserver.lib.ConfigReader.parse_Zones(ConfigReader.java:584)
jvm 1    | 	at it.gotoandplay.smartfoxserver.lib.ConfigReader.readZoneConfig(ConfigReader.java:284)
jvm 1    | 	at it.gotoandplay.smartfoxserver.SmartFoxServer.initServerSocket(SmartFoxServer.java:309)
jvm 1    | 	at it.gotoandplay.smartfoxserver.SmartFoxServer.run(SmartFoxServer.java:558)
jvm 1    | 
jvm 1    | 
jvm 1    | ** END NESTED EXCEPTION **
jvm 1    | 
jvm 1    | 
jvm 1    | 
jvm 1    | Last packet sent to the server was 287 ms ago.
jvm 1    | it.gotoandplay.smartfoxserver.exceptions.DbManagerException: The Test SQL statement failed! Please check your configuration.
jvm 1    | 	at it.gotoandplay.smartfoxserver.SmartFoxServer.setupZone(SmartFoxServer.java:1619)
jvm 1    | 	at it.gotoandplay.smartfoxserver.lib.ConfigReader.parse_Zones(ConfigReader.java:584)
jvm 1    | 	at it.gotoandplay.smartfoxserver.lib.ConfigReader.readZoneConfig(ConfigReader.java:284)
jvm 1    | 	at it.gotoandplay.smartfoxserver.SmartFoxServer.initServerSocket(SmartFoxServer.java:309)
jvm 1    | 	at it.gotoandplay.smartfoxserver.SmartFoxServer.run(SmartFoxServer.java:558)
jvm 1    | Problems in config file: The Test SQL statement failed! Please check your configuration.
jvm 1    | it.gotoandplay.smartfoxserver.exceptions.ConfigurationException: The Test SQL statement failed! Please check your configuration.
jvm 1    | 	at it.gotoandplay.smartfoxserver.lib.ConfigReader.readZoneConfig(ConfigReader.java:308)
jvm 1    | 	at it.gotoandplay.smartfoxserver.SmartFoxServer.initServerSocket(SmartFoxServer.java:309)
jvm 1    | 	at it.gotoandplay.smartfoxserver.SmartFoxServer.run(SmartFoxServer.java:558)
jvm 1    | 
jvm 1    | Errors found in configuration files.
jvm 1    | Unable to start the server.
jvm 1    | 
jvm 1    | 
jvm 1    | Shutting down server...

How can i be sure this line is correct in the config.xml :

Code: Select all

jdbc:mysql://192.168.0.15:3306/

Thank you for you help

Post by **Lapo** » 29 May 2006, 21:39

Is 192.168.0.15 the address of your mysql db?

Elgueu · Post by **Elgueu** » 29 May 2006, 21:57

No it's "localhost" as usual on a webserver, but i tried with that already without any results... And i don't have a clue for the port (it's the defaut one, but i don't know it).

Post by **Lapo** » 30 May 2006, 10:35

default port is 3306, so it's right.
Please try using the correct IP and port and if you still have problems, please report here the error messages

thanks

Elgueu · Post by **Elgueu** » 30 May 2006, 15:46

After some research i found that the problem comes from mysql privileges and distant connection...

I modified the my.cnf, by commenting this line :
#skip-networking

And i need to give permission to the user in the "mysql" db / "host" table. So, through webmin i modified the table like this :

Table host in database mysql :

Code: Select all

Host 	Db 	Select_priv 	Insert_priv 	Update_priv 	Delete_priv 	Create_priv 	Drop_priv 	Grant_priv 	References_priv 	Index_priv 	Alter_priv 	Create_tmp_table_priv 	Lock_tables_priv
_____________
% 	mybase 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y 	Y

(And here is the config.xml : )

Code: Select all

<DatabaseManager active="true">

	<Driver>org.gjt.mm.mysql.Driver</Driver>
	<ConnectionString>jdbc:mysql://127.0.0.1:3306/mybase</ConnectionString>

	<UserName>***</UserName>
	<Password>***</Password>


	<TestSQL><![CDATA[SELECT map FROM Sonic_world_map]]></TestSQL>


	<MaxActive>10</MaxActive>
	<MaxIdle>10</MaxIdle>


	<OnExhaustedPool>fail</OnExhaustedPool>
	<BlockTime>5000</BlockTime> 

</DatabaseManager>

But i'm now stuck with this error, i think there's still sthg wrong with the user permission but i can't find out where i did wrong

Code: Select all

DB Manager Activated ( org.gjt.mm.mysql.Driver )
jvm 1    | 18:00:38.238 - [ SEVERE ] > DbManager could not retrive a connection. java.sql.SQLException: null,  message from server: "Host 'localhost.localdomain' is not allowed to connect to this MySQL server"
jvm 1    | it.gotoandplay.smartfoxserver.exceptions.DbManagerException: The Test SQL statement failed! Please check your configuration.
jvm 1    | 	at it.gotoandplay.smartfoxserver.SmartFoxServer.setupZone(SmartFoxServer.java:1619)
jvm 1    | 	at it.gotoandplay.smartfoxserver.lib.ConfigReader.parse_Zones(ConfigReader.java:584)
jvm 1    | 	at it.gotoandplay.smartfoxserver.lib.ConfigReader.readZoneConfig(ConfigReader.java:284)
jvm 1    | 	at it.gotoandplay.smartfoxserver.SmartFoxServer.initServerSocket(SmartFoxServer.java:309)
jvm 1    | 	at it.gotoandplay.smartfoxserver.SmartFoxServer.run(SmartFoxServer.java:558)
jvm 1    | Problems in config file: The Test SQL statement failed! Please check your configuration.
jvm 1    | it.gotoandplay.smartfoxserver.exceptions.ConfigurationException: The Test SQL statement failed! Please check your configuration.
jvm 1    | 	at it.gotoandplay.smartfoxserver.lib.ConfigReader.readZoneConfig(ConfigReader.java:308)
jvm 1    | 	at it.gotoandplay.smartfoxserver.SmartFoxServer.initServerSocket(SmartFoxServer.java:309)
jvm 1    | 	at it.gotoandplay.smartfoxserver.SmartFoxServer.run(SmartFoxServer.java:558)
jvm 1    | 
jvm 1    | Errors found in configuration files.
jvm 1    | Unable to start the server.
jvm 1    | 
jvm 1    | 
jvm 1    | Shutting down server...

(Btw, here is the french explanation page for the distant mysql connection for my server : http://guides.ovh.com/ConnexionDistante ... enu.html#3 )

Post by **Lapo** » 30 May 2006, 16:23

By distant do you mean remote?
Another question: is your MySQL installed on the same machine where SFS is?

One more: after changing the privileges in MySQL did you restart it? It's usually necessary to activate changes

Elgueu · Post by **Elgueu** » 30 May 2006, 20:29

Well, MySQL is installed on the same machine, but obviously i need to configure it to allow "remote" connection, i don't know why... But the first errors disappeared.

And yes i restarted MySQL, and even the server after the changes.

Any idea ?

(There is the "hosts" config file in /etc/ that may be involved in this problem, don't you think ?

Code: Select all

# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1		localhost.localdomain localhost
213.251.**.**		ns**.ovh.net

)

Post by **Lapo** » 30 May 2006, 20:43

No, if SFS and MySQL are on the same machine... you're not connected remotely, you're on the same address.
Also the hosts file is unlikely to be of any help
What version of mysql do you use?

Elgueu · Post by **Elgueu** » 30 May 2006, 21:25

MySQL 4.1.14-Max-log running on localhost as pso@localhost

Elgueu · Post by **Elgueu** » 01 Jun 2006, 20:33

I'm still stuck =/
Is there any other way to store easily array on the server ? PHP ?