Problem Database MySQL 5.6

Karbust · May 12, 2016

Hello guys

Since I moved to the server for a VPS bought in Eterhost that my database does not work well ...

On the site, when trying to register, you can not always say that the account already exists (error adding the query), the server itself has errors and sometimes lost items due to the server can not save the database, the site can not do the SELECT and this return correct without errors ...

This happens since I moved to the VPS, there everything worked well in Virtual Box ...

Users of the site and play, have the maximum permissions, has no N, have all selected the Y ...

Does anyone know what's going on? I need to solve this fast ...

Thank you

iltizio · May 13, 2016

You need to check mysql error log: /var/db/mysql/<hostname>.err

You can check your applicative logs (game syserr, webserver log)

Karbust · May 13, 2016

No errors are shown on /var/db/mysql/<hostname>.err... Neither on error_log from the webserver and syserr from the game... :S

iltizio · May 14, 2016

Did you tried to execute the website queries manually on a mysql client?

Karbust · May 14, 2016

I ran this query:

INSERT INTO account (login,password,real_name,email,social_id,question1,answer1,create_time,status,newsletter,coins,ip_registo,web_aktiviert) VALUES ('contateste',PASSWORD('testesenha'),'teste','[email protected]',1234567,1,'awnser','0000-00-00 00:00:00','BLOCK','1','0','127.0.0.1.','codigoativacao');

Is the website query to create accounts... I ran this query on navicat and no error...

Affected rows: 1

	Time: 0.077s

iltizio · May 14, 2016

And it don't create this record?

What is the Storage Engine?

Karbust · May 14, 2016

If I run directly on navicat the record is created...

Engine: MyISAM

ds_aim · May 14, 2016

Setting the SQL Mode

The default SQL mode in MySQL 5.6.6 and later is NO_ENGINE_SUBSTITUTION; in MySQL 5.6.5 and earlier, it was empty (no modes set).

To set the SQL mode at server startup, use the --sql-mode="modes" option on the command line, or sql-mode="modes" in an option file such as my.cnf(Unix operating systems) or my.ini (Windows). modes is a list of different modes separated by commas. To clear the SQL mode explicitly, set it to an empty string using --sql-mode="" on the command line, or sql-mode="" in an option file.

https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html

Exygo · May 14, 2016

14 minutes ago, ds_aim said:

Setting the SQL Mode

The default SQL mode in MySQL 5.6.6 and later is NO_ENGINE_SUBSTITUTION; in MySQL 5.6.5 and earlier, it was empty (no modes set).

To set the SQL mode at server startup, use the --sql-mode="modes" option on the command line, or sql-mode="modes" in an option file such as my.cnf(Unix operating systems) or my.ini (Windows). modes is a list of different modes separated by commas. To clear the SQL mode explicitly, set it to an empty string using --sql-mode="" on the command line, or sql-mode="" in an option file.

https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html

Nice copy-paste, but you don't actually help him

Istny · May 14, 2016

you should check error in php script
something like this

if (!execute_query)
echo mysql_error;

iltizio · May 14, 2016

If you don't find errors on logs I can't help you. Try to reinstall mysql and re upload your dbs.

Upload via dumps or transfer!

Karbust · May 14, 2016

3 hours ago, Istny said:

you should check error in php script
something like this

if (!execute_query)
echo mysql_error;

This is the code I have on the test page:

        $sql = "INSERT INTO account_pvp.account

	                (login, password, social_id, email, create_time, status, coins, ip_registo)

	                VALUES

	                ('" . $benutzername . "', PASSWORD('" . $passwort . "'), '" . $loeschcode . "', '" . $email . "', '" . $zeit . "', '" . $status . "', '" . $registercoins . "', '".$user_ip."')";

	        $qry = mysqli_query($sql, $con)or die('Erro: ' . mysqli_error());

But no error returns:

2 hours ago, iltizio said:

If you don't find errors on logs I can't help you. Try to reinstall mysql and re upload your dbs.

Upload via dumps or transfer!

I will try...

Edited August 20, 2022 by Metin2 Dev
Core X - External 2 Internal

Istny · May 14, 2016

50 minutes ago, charparodar said:

This is the code I have on the test page:


        $sql = "INSERT INTO account_pvp.account

	                (login, password, social_id, email, create_time, status, coins, ip_registo)

	                VALUES

	                ('" . $benutzername . "', PASSWORD('" . $passwort . "'), '" . $loeschcode . "', '" . $email . "', '" . $zeit . "', '" . $status . "', '" . $registercoins . "', '".$user_ip."')";

	        $qry = mysqli_query($sql, $con)or die('Erro: ' . mysqli_error());

But no error returns:

I will try...

when you run test page, data are inserted to database ? or the data are not inserted and also there is no error ?

Edited August 20, 2022 by Metin2 Dev
Core X - External 2 Internal

Karbust · May 14, 2016

The data isn't inserted but doesn't appear any error...

iltizio · May 14, 2016

But it die, so there are an error, but without output

Karbust · May 14, 2016

I use the same database, exactly the same tables, I just used the backup.sh that usually comes with the serverfiles and then transfered to the server...

If it was just the site... The game is with some log problems, don't log some levels, characters, coordinates, itens...

Istny · May 14, 2016

My bad, i'm not using mysqli from long time, you have to put mysql conection handler inside mysql_error() function like

Spoiler

mysqli_error($con)

Karbust · May 14, 2016

Appears the same...

Edited August 20, 2022 by Metin2 Dev
Core X - External 2 Internal

ds_aim · May 14, 2016

43 minutes ago, charparodar said:

Appears the same...

Show me usr/local/my.cnf

Edited August 20, 2022 by Metin2 Dev
Core X - External 2 Internal

Karbust · May 14, 2016

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

ds_aim · May 14, 2016

Just now, charparodar said:
 
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Delete this line restart mysql server and try again.

Karbust · May 14, 2016

Appears the same thing...

Edited August 20, 2022 by Metin2 Dev
Core X - External 2 Internal

Istny · May 14, 2016

11 minutes ago, charparodar said:

Appears the same thing...

replace in mysqli_query($sql, $con) variables order to
mysqli_query($con, $sql) and add ini_set('display_errors', 'On'); on top of the file

Edited August 20, 2022 by Metin2 Dev
Core X - External 2 Internal

Karbust · May 14, 2016

The same... I starting to freaking out...

Istny · May 14, 2016

try run this script http://pastebin.com/kiLECc97
replace in mysql_conn data with your mysql server address, user and password

Sign In

Problem Database MySQL 5.6

Recommended Posts

Karbust 4714

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Popular Posts

ds_aim

ds_aim

Istny

iltizio 212

Link to comment

Share on other sites

Karbust 4714

Link to comment

Share on other sites

iltizio 212

Link to comment

Share on other sites

Karbust 4714

Link to comment

Share on other sites

iltizio 212

Link to comment

Share on other sites

Karbust 4714

Link to comment

Share on other sites

ds_aim 239

Setting the SQL Mode

Link to comment

Share on other sites

Exygo 1045

Setting the SQL Mode

Link to comment

Share on other sites

Istny 75

Link to comment

Share on other sites

iltizio 212

Link to comment

Share on other sites

Karbust 4714

Link to comment

Share on other sites

Istny 75

Link to comment

Share on other sites

Karbust 4714

Link to comment

Share on other sites

iltizio 212

Link to comment

Share on other sites

Karbust 4714

Link to comment

Share on other sites

Istny 75

Link to comment

Share on other sites

Karbust 4714

Link to comment

Share on other sites

ds_aim 239

Link to comment

Share on other sites

Karbust 4714

Link to comment

Share on other sites

ds_aim 239

Link to comment

Share on other sites

Karbust 4714

Link to comment

Share on other sites

Istny 75