Mysql server gone away ??

[vengo...@gmail.com]

Hi,

I found a strange behaviour, if I put maxscale between my webserver (PHP) and mysql server I have this response error "Mysql server gone away".
If I connect out application directly to the mysql server everything goes smooth...

This error comes up only on some query, the vast majority run ok.
I'm trying to filter them better.

Do you have any idea?
Is there some timeout or allowed packet size option in maxscale?


Thank you in advance, if someone can help me!

[Vilho Raatikka]

Hi, which version of MaxScale you are using?

--
You received this message because you are subscribed to the Google Groups "MaxScale" group.
To unsubscribe from this group and stop receiving emails from it, send an email to maxscale+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Vilho Raatikka]

Hi, previous message was unintentionally sent while I was still writing it :-)

Was there anything in the error log which could correspond to the unexpected behavior?

If the problem occurs more than once, you can enable trace logging by connecting to MaxScale with maxadmin and execute the following command 'enable log trace'. The trace log may give a hint what is happening.

Regards

Vilho Raatikka

[vengo...@gmail.com]

Hi!
I checked webserver and mysqlserver error log, they have not.
Also skygw_err1.log has only a couple of warnings, I enabled log trace...but I cannot see why some (always the same) queries produce "mysql server gone away".
I paste a piece of my skygw_trace4.log, it's one of the bad queries. Maybe I can have a little more help... :-)

2015-01-13 22:20:59 [20] > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_GSYSVAR_WRITE, stmt:
SELECT
@N := @N +1 AS rank,
player.name AS title,
IF(team.team_override!='',team.team_override,team.club) AS subtitle,
a.imgName as passportThumbImage,
goal,penalties,
appearances,
minutes_played,
player_career.playerId as objId,
ps2.passportAreaId,
YEAR(ps2.birthDate) as yDate
FROM player_career
LEFT JOIN player ON player_career.playerId=player.id
LEFT JOIN team ON player_career.teamId=team.id
INNER JOIN playersearch2_temp ps2 ON ps2.playerId = player_career.playerId
LEFT JOIN area a on a.id = player.birthAreaId
WHERE
player_career.seasonId='9815'
AND goal>0 ORDER BY goal DESC

2015-01-13 22:20:59 [20] Session write, routing to all servers.
2015-01-13 22:20:59 [20] Route query to slave 192.168.1.9:3306
2015-01-13 22:20:59 [20] Route query to master 192.168.1.8:3306 <

[vengo...@gmail.com]

Sorry, I want to add that I'm using the 1.04 stable version of maxscale (I had the same behaviour with 1.02rc), and I tried also that query from a mysql console with similar result, running on maxscale I get:

ERROR 2013 (HY000): Lost connection to MySQL server during query

running it directly on the mysqlserver I get correct results, as it should be.

[vengo...@gmail.com]

I'm very annoying today, I know, but I'm very excited to test maxscale, I need to make everything running ok and convince my boss..

I worked it out! I changed "use_sql_variables_in=master", thanks to your article:
https://mariadb.com/blog/maxscale-and-transparent-session-handling

I'll think about the consequences on performance and reliability, any advice will be well accepted!


Thank you very much for your time!!

Francesco

[Vilho Raatikka]

Hi! I was going to suggest that for you. Select updates session (SQL) variable inside the query and currently that is not supported like you already found out.
Is it ok for your application that the variable is incremented only in master? One potential workaround could be rewrite problematic query with regex filter so, that variable is incremented first (routed to all) and queried in separate query.

Regards Vilho Raatikka

[Vilho Raatikka]

Hi, I've filed the bug http://bugs.mariadb.com/show_bug.cgi?id=694 .

Thanks for letting us know.

Regards

Vilho

[Vilho Raatikka]

Hi, I've just pushed fix candidate to develop branch about http://bugs.mariadb.com/show_bug.cgi?id=694 .

Regards

Vilho Raatikka