Page 1 of 2
Mysql connection
Posted: 29 May 2006, 13:56
by Elgueu
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
Posted: 29 May 2006, 18:11
by allexiel
I think it's well explained in the docs
have a look here:
http://www.smartfoxserver.com/docs/docP ... ection.htm
Posted: 29 May 2006, 18:25
by Elgueu
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
Posted: 29 May 2006, 18:31
by allexiel
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.
Posted: 29 May 2006, 18:52
by Virusescu
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.
Posted: 29 May 2006, 20:09
by Elgueu
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 :
Thank you for you help
Posted: 29 May 2006, 21:39
by Lapo
Is 192.168.0.15 the address of your mysql db?
Posted: 29 May 2006, 21:57
by Elgueu
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).
Posted: 30 May 2006, 10:35
by Lapo
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
Posted: 30 May 2006, 15:46
by Elgueu
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 )
Posted: 30 May 2006, 16:23
by Lapo
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
Posted: 30 May 2006, 20:29
by Elgueu
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
)
Posted: 30 May 2006, 20:43
by Lapo
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?
Posted: 30 May 2006, 21:25
by Elgueu
MySQL 4.1.14-Max-log running on localhost as pso@localhost
Posted: 01 Jun 2006, 20:33
by Elgueu
I'm still stuck =/
Is there any other way to store easily array on the server ? PHP ?