Problem Database MySQL 5.6

[Karbust 3180]

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 139]

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

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

[Karbust 3180]

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

[iltizio 139]

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

[Karbust 3180]

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 139]

And it don't create this record?

What is the Storage Engine?

[Karbust 3180]

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

Engine: MyISAM

[ds_aim 206]

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 699]

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 64]

you should check error in php script
something like this

if (!execute_query)
    echo mysql_error;

 

[iltizio 139]

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 3180]

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 64]

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 3180]

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

[iltizio 139]

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

[Karbust 3180]

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 64]

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 3180]

Appears the same...

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

[ds_aim 206]

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 3180]

# 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 206]

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 3180]

Appears the same thing...

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

[Istny 64]

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 3180]

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

[Istny 64]

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

[Karbust 3180]

It ran...

 

I'm gonna need to change all mysql commands on my website? I'm using simple mysql on the normal website...