Mysql Timeouts when making big querries from a remote host
Posted by: Esmeralda (201.186.108.---)
Date: February 17, 2019 07:24PM

So recently I installed the latest version of WampServer in a new computer to replace an old one that I'm using mainly to host a Mysql server. I have a handful of databases that I access with a few basic programs, after importing everything to the new server and changing very little of the default configurations my programs ran fine and much faster than before when tested in the same machine, but when I try to use them from another computer in the same network I keep getting Timeout errors with the 2 applications that have to load the biggest amount of data through Queries.

In the one application that I'm using now to troubleshot this I noticed that it always timed out in the middle of a Select Query that delivered 4.000+ rows when the program is loading the initial data, but adding a Limit 100 at the end of that particular request made the program fully load but timeout later if I kept operating it to make it make more Select queries for smaller amount of data at short intervals.

I have no idea what can be a possible cause of this problem, the programs still run fine if I connect them to the old server which I think it's pretty much running with the default Mysql configuration, they also ran well when I tried Mamp in this same computer but I ended up having problems with its services so I discarded that option.

Since yesterday I have tried everything that found on the Internet that may have been slightly related to my problem with no luck, as a consequence my my.ini in a mess with some extra things and a lot of bigger numbers than the default ones, probably a lot of unnecesary stuff. Here it is:

[pastebin.com]

Thanks in advance for your time.

Options: ReplyQuote
Re: Mysql Timeouts when making big querries from a remote host
Posted by: Otomatic (Moderator)
Date: February 18, 2019 09:16AM

Hi,

Are the versions of MySQL between the old server (Database export) and the new one (Database import) the same?

Have you compared the my.ini and PHP configurations between the old and new servers?

And most importantly, have you seen the errors in the mysql and php log files?

------------------------------------------------------------------------------------------------------------
Wampserver 3.1.9 32 bit - Apache 2.4.39 - PHP 7.3.5/7.2.18/7.1.29/7.0.33/5.6.40 - MySQL 5.7.26 - MariaDB 10.3.15
Wampserver 3.1.9 64 bit - Apache 2.4.39 - PHP 7.3.5/7.2.18/7.1.29/7.0.33/5.6.40 - MySQL 5.7.26/8.0.16 - MariaDB 10.3.15
PhPMyadmin 4.8.5 - MysqlDumper 1.24.5
on W10 and W7 Pro 64 bit
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons
« Ce n'est pas parce qu'ils sont nombreux à avoir tort, qu'ils ont forcément raison. Coluche »
« It's not because they are many to be wrong, they are necessarily right. Coluche »

Options: ReplyQuote
Re: Mysql Timeouts when making big querries from a remote host
Posted by: Esmeralda (201.186.108.---)
Date: February 18, 2019 04:07PM

Otomatic Wrote:
-------------------------------------------------------
> Are the versions of MySQL between the old server
> (Database export) and the new one (Database
> import) the same?
They are not, the new server is on 5.7.24, and the old one should be running a 7 years old or so version I think, I can check the specific version later if needed, I installed it from an old EasyPHP pack.

> Have you compared the my.ini and PHP
> configurations between the old and new servers?
I checked the my.ini configuration before, I rechecked it again and I think the only possibly related changes are "skip-external-locking" "myisam_sort_buffer_size = 32M" which were in my old configuration file, I pasted them in the new one with no change. The other differences are mostly less stuff in the older file or lower numbers.
Here is the old one just in case: [pastebin.com]
I would expect my problem to be related to a wrong configuration in this file but I can't see anything wrong after spending hours and hours looking on this issue. Perhaps I'm overlooking something.

I looked over my old php.ini just in case and I had a [MySQL] and a [MSSQL] section with some configuration parameters there and not in the new one, I copied them over to the new server just in case but nothing changed. I may be wrong but I believe php is not related the direct connections of my programs to the MySQL databases, but I checked the files either way to discard this possibility.

> And most importantly, have you seen the errors in
> the mysql and php log files?
All the timeouts don't seem to be generating errors that get recorded in MySQL logs unfortunately, I don't know if it's possible to change something to get that kind of error logged, I just enabled log_error_verbosity = 3 to see if that helped but nothing was added when my program crashed. Here's my log just in case, I think everything recorded is unrelated to my issue.
[pastebin.com]

Options: ReplyQuote
Re: Mysql Timeouts when making big querries from a remote host
Posted by: Otomatic (Moderator)
Date: February 18, 2019 04:34PM

Hi,

2019-02-17T01:35:10.211630Z 0 [ERROR] Can't start server: Bind on TCP/IP port: No such file or directory
2019-02-17T01:35:10.213704Z 0 [ERROR] Do you already have another mysqld server running on port: 3306 ?

Right-Click -> Tools -> Test port 3306

------------------------------------------------------------------------------------------------------------
Wampserver 3.1.9 32 bit - Apache 2.4.39 - PHP 7.3.5/7.2.18/7.1.29/7.0.33/5.6.40 - MySQL 5.7.26 - MariaDB 10.3.15
Wampserver 3.1.9 64 bit - Apache 2.4.39 - PHP 7.3.5/7.2.18/7.1.29/7.0.33/5.6.40 - MySQL 5.7.26/8.0.16 - MariaDB 10.3.15
PhPMyadmin 4.8.5 - MysqlDumper 1.24.5
on W10 and W7 Pro 64 bit
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons
« Ce n'est pas parce qu'ils sont nombreux à avoir tort, qu'ils ont forcément raison. Coluche »
« It's not because they are many to be wrong, they are necessarily right. Coluche »

Options: ReplyQuote
Re: Mysql Timeouts when making big querries from a remote host
Posted by: Esmeralda (201.186.108.---)
Date: February 18, 2019 04:58PM

***** Test which uses port 3306 *****

===== Tested by command netstat filtered on port 3306 =====


Test for TCP
Your port 3306 is used by a processus with PID = 11080
The processus of PID 11080 is 'mysqld.exe' Session: Services
The service of PID 11080 for 'mysqld.exe' is 'wampmysqld64'
This service is from Wampserver - It is correct

Test for TCPv6
Your port 3306 is used by a processus with PID = 11080
The processus of PID 11080 is 'mysqld.exe' Session: Services
The service of PID 11080 for 'mysqld.exe' is 'wampmysqld64'
This service is from Wampserver - It is correct

--- Do you want to copy the results into Clipboard?
--- Type 'y' to confirm - Press ENTER to continue…


Seems fine I think, I have no idea why that error was shown that time, I don't think that at this moment I have a second MySQL server taking over the port because if I stop the Wamp MySQL service my program won't connect anymore.

Options: ReplyQuote
Re: Mysql Timeouts when making big querries from a remote host
Posted by: RiggsFolly (Moderator)
Date: February 18, 2019 05:06PM

Did you do any Mods to your `my.ini` file?

If not then I would check that you dont have another `my.ini` or `my.cnf` file on your system, probably in the \Windows folder somewhere

The reason for this is the error

[ERROR] InnoDB: Unrecognized value O_DIRECT for innodb_flush_method

as innodb_flush_method=O_DIRECT is not a valid option on Windows so either you amended `my.ini` incorrectly or MySQL is finding a `my.ini` from a unix system configuration

---------------------------------------------------------------------------------------------
(Windows 10 Pro 64bit) (Wampserver 3.1.8 32bit & 64bit)
<Apache versions MULTIPE> <PHP versions MULTIPLE> <MySQL Versions MULTIPLE>
<MariaDB versions MULTIPLE> <phpMyAdmin versions MULTIPLE> <MySQL Workbench 6.3.10>

Read The Manuals Apache -- MySQL -- PHP -- phpMyAdmin
Get your Apache/MySQL/mariaDB/PHP ADDONs here from the WAMPServer alternate Repo
-X-X-X- Backup your MySQL databases regularly Here is How dont regret it later! Yes even when developing -X-X-X-

Options: ReplyQuote
Re: Mysql Timeouts when making big querries from a remote host
Posted by: Esmeralda (201.186.108.---)
Date: February 18, 2019 05:12PM

Thanks everyone for your help, the problem just got solved. I don't really know what exactly was the cause of the issue or if any of the changes I made had any effect, but I restarted the server just a moment ago and the problem fixed itself. Previously I only restarted it once after installing WampServer and had it suspended when not using it.

Options: ReplyQuote
Re: Mysql Timeouts when making big querries from a remote host
Posted by: Otomatic (Moderator)
Date: February 18, 2019 06:09PM

Hi,

> They are not, the new server is on 5.7.24
The imported databases did not come from the same version of MySQL and, even if it is an export/import in SQL file, it would be necessary to perform a mysql_upgrade to verify that the structure of your databases corresponds to this new version of MySQL.

The program mysql_upgrade.exe allows you to modify tables that require modifications after an update or evolution of MySQL.

This program should be run after each update or upgrade of the MySQL version.

As usual, before performing any manipulations that may modify databases, it is best to backup them first.

Perform mysql_upgrade.exe

First of all, you have to launch the MySql server, so launch Wampserver
Then, launch a command window as administrator open on the bin folder of the MySQL version.
And there, you don't need to look for how to do it, or how to go to the right folder, the tools integrated into Wampserver are there to make your job easier.
Right-Click Wampmanager tray icon -> Tools -> Command windows -> Command windows mysql/bin

And here it is, an open command window where it belongs.

Once there you need to know if you have put a password at root@localhost or not

if password, type
mysql_upgrade.exe --user=root --host=localhost --password=mypassword

if no password, type
mysql_upgrade.exe --user=root --host=localhost --password=

All tables in all databases must be OK

------------------------------------------------------------------------------------------------------------
Wampserver 3.1.9 32 bit - Apache 2.4.39 - PHP 7.3.5/7.2.18/7.1.29/7.0.33/5.6.40 - MySQL 5.7.26 - MariaDB 10.3.15
Wampserver 3.1.9 64 bit - Apache 2.4.39 - PHP 7.3.5/7.2.18/7.1.29/7.0.33/5.6.40 - MySQL 5.7.26/8.0.16 - MariaDB 10.3.15
PhPMyadmin 4.8.5 - MysqlDumper 1.24.5
on W10 and W7 Pro 64 bit
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons
« Ce n'est pas parce qu'ils sont nombreux à avoir tort, qu'ils ont forcément raison. Coluche »
« It's not because they are many to be wrong, they are necessarily right. Coluche »

Options: ReplyQuote


Sorry, only registered users may post in this forum.