ProxySQL reporting warnings about duplicate keys.

ProxySQL is logging numerous entries like this:

2017-07-06 05:44:15 MySQL_Session.cpp:2667:handler(): [WARNING] Error during query on (0,,3306): 1062, Duplicate entry ‘0daea772-d11a-40a3-9dbc-4f8dafd5e77c’ for key ‘PRIMARY’
2017-07-06 06:03:14 MySQL_Session.cpp:2667:handler(): [WARNING] Error during query on (0,,3306): 1062, Duplicate entry ‘6b04d2ec-e9dd-4ddc-b985-10601747ecc3’ for key ‘PRIMARY’
2017-07-06 06:04:31 MySQL_Session.cpp:2667:handler(): [WARNING] Error during query on (0,,3306): 1062, Duplicate entry ‘38e6eb1e-9173-4b04-9dec-37319ee0ecfe’ for key ‘PRIMARY’

The problem is, it isn’t telling me what database or table (though I only have one database at the moment, so that’s not hard to deduce, but what table?), so I don’t know how to find these, to investigate them. These three lines, above, happen to be reported for the same backend server, but all three backends show up in the output.

Suggestions? Is there someway to search my entire database for these values? My apologies if I sound like a Noob - I am (at this).

As an enhancement request - having it note the database and table would be most useful …

Okay, I found a commercial tool that has, as one of its features, the ability to search my entire database. It found exactly one occurrence of the item I looked for.

Can someone explain to me why ProxySQL would be issuing warnings about duplicate key entries, when there aren’t?

The errors you see are the errors that are coming from MySQL Server when executing a query. That is, the error “Duplicate entry ‘0daea772-d11a-40a3-9dbc-4f8dafd5e77c’ for key ‘PRIMARY’” is exactly the error message that MySQL Server has sent to ProxySQL, and that ProxySQL has sent back to the application.
That also means that, if in the application you have some sort of logging and error handling, the application should know what query has triggered that error.

ProxySQL verbosity can be enhanced adding also the schemaname, but not the tablename: ProxySQL knows to which schema is connected, but doesn’t know against which table an error is generate. Also, performing the parsing of the query to get the tablename doesn’t make sense, because an error like “duplicate entry” could be generated by a simple insert/update, or triggers, stored procedure, foreign key constraints, etc.
There are two feature requests to enhance logging:

Finally, to answer “why ProxySQL would be issuing warnings about duplicate key entries, when there aren’t?”
Duplicate key entries do not exists because MySQL Server rejected the DML that was trying to to create a duplicate key entry.
For example, if there was already a primary key with value ‘0daea772-d11a-40a3-9dbc-4f8dafd5e77c’ and ProxySQL tried to execute an INSERT using the same PK, MySQL Server will reject the INSERT returning an error, error number 1062, error message “Duplicate entry ‘0daea772-d11a-40a3-9dbc-4f8dafd5e77c’ for key ‘PRIMARY’” . At this point, ProxySQL will generate a log entry reporting that it got an error from MySQL Server:
2017-07-06 05:44:15 MySQL_Session.cpp:2667:handler(): [WARNING] Error during query on (0,,3306): 1062, Duplicate entry ‘0daea772-d11a-40a3-9dbc-4f8dafd5e77c’ for key ‘PRIMARY’

I hope this answers your questions.

Almost. The one thing I don’t understand, however, is why my application only sees these (and many of them)

12:32:22 - [REGION DB]: MySQL error in ExecuteNonQuery: Duplicate entry ‘068de4b9-1cb3-4fe8-81b6-f660a6f09519’ for key ‘PRIMARY’

when connected to ProxySQL and never when connected directly to any one of the databases in PXC.

Additionally, my application only sees these (and many of them)

10:38:36 - [ASSET DB]: MySQL failure creating asset 85822972-5ef7-4ee5-8db1-2ab55e684bcb with name “terrainImage_7a1941d0-dbc6-11e3-9c1a-0800200c9a66”. Error: Packets larger than max_allowed_packet are not allowed.

when connected to ProxySQL and never when connected directly to any one of the databases in PXC. Regarding this last one, I have always had max_allowed_packet=16M when I only had my single MySQL 5.5 server. In trying to diagnose this, I have max_allowed_packet=1G set on all three PXC (5.7.18-29) nodes, and I have

| mysql-max_allowed_packet | 1073741824 |

set in ProxySQL. Is there some place else that I need to set this? Again, my application only sees these errors when connecting to the DB through ProxySQL.

I must say that I really like ProxySQL and look forward to resolving these issues (and a couple others - let’s take this in small bites), so that I can use it. Since I am new to both PXC and ProxySQL, I assume full responsibility for any errors, until proven they aren’t mine. :slight_smile: But, in this case, my application appears to be working perfectly with PXC, and only runs into trouble when ProxySQL is added to the mix. I do, in fact hope they are my errors, because they are the easiest to fix, once identified. :slight_smile:

Does this have anything to do with my cluster having just 1 hostgroup (0), with all 3 nodes read/write? If so, how might I configure it so that I can take any single one of the three offline, without impacting my application? Just brainstorming …

I found something interesting. When I dropped mysql-max_allowed_packet to 768M (instead of 1G), I stopped getting the max_allowed_packet errors. However, when I then dropped max_allowed_packet to 768M on all three DB servers, the issue came back. This suggests to me that ProxySQL is mangling my packets somehow. Further evidence that ProxySQL is mangling my packets, is that I am seeing my application occasionally fail to properly get data from the DB, that it does get correctly, every time, when connected directly to one of the 3 cluster nodes. This is not looking good.

I’m currently using the latest ProxySQL from Percona’s yum repository. I think I might try the latest version from the ProxySQL site. If that doesn’t work, I’ll have to look for alternatives. :frowning:

tldr; Success! I got it working. :slight_smile:

I installed the latest debug version from the ProxySQL site, on a second server, and had pretty much the same dismal results as I did with Percona’s version of it, on my first server - my application was blowing up, left and right, with all the issues I noted earlier in this thread. It was pretty discouraging.

But I thought to myself, “It could be the software is crap, but there’s a hell of a lot of people using it, just fine, and I’m new to it, so what might I be over-looking?” I’m a SysAdmin by trade, and often, if something new doesn’t work, it turns out to be a PEBKAC issue.

So, I thought, “When does everything ‘just work’?” Well, that’s when all the multiple instances of my application (I say ‘my’, but - trust me - I didn’t write it) were connected to a SINGLE database for their reads and writes. That, in turn, lead me back to my earlier after-thought question, above:

Does this have anything to do with my cluster having just 1 hostgroup (0), with all 3 nodes read/write?

So, I decided to set things up as a read/write split, that I’d seen mentioned in numerous places, as something a lot of people did. I was concerned about only having one writer, because what would happen if it went offline - would I be left with no writers, only readers? That would kinda break stuff too. But then I figured someone must have thought of that, already, so I started googling around. I came across this article:…using-proxysql

It assuaged my concerns about being left with no writers (I wouldn’t be), and it showed me what to do. So, since I was mostly set up correctly already, I just used that article to see how to modify my mysql_servers and scheduler tables. I also modified mysql-query_retries_on_failure as it mentioned. I did not create any rules. I wanted it to act as if there was only one server, just like when ProxySQL wasn’t the middle-man.

I re-ran my test - essentially restarting all 51 instances of my application at once, so that it beat on the database - and it just worked. Not a single error anywhere. It was just as flawless as it was without ProxySQL - though perhaps a teeny bit slower than going directly to the server, but I’d read that the debug version was a tad slower, so that was OK.

So, it would seem that my application cannot handle multiple writers. Why, I do not know or understand, but having a single writer just works.

Next, I’ll set up some query rules, to redirect some traffic to the two reader (and, potentially, writer) nodes that are currently sitting unused. Rather than use the black & white method mentioned in the article above, I plan to do it the more “efficient” way spelled out in the “read/write split using regex and digest” section of this article:

If my application is still working OK, after adding the query-routing rules - and I’m betting it will - then I’ll go try re-configuring the other server (the one with Percona’s version on it) and making sure all is well there, too (I’m betting it will be). Then all I’ll have to do is decide which flavor I wish to run, going forward.

As Hannibal Smith would say, "I love it when a plan comes together!’ :slight_smile:

Hi sterickson , wow this was an extremely interesting post to read :slight_smile: You should publish this on a blog!!

I wanted to call your attention to the proxysql-admin tool which might help you in the future with your ProxySQL configuration. You can read about it further:
[/LIST] I hope this helps! Thanks again for the great read

Thanks, Michael Coburn - and I’ll take a look at those links.

Today, I configured the server with Percona’s copy of ProxySQL on it, the same as the server with the copy direct from the ProxySQL site. As expected, they both work the same. As to which I’ll use, going forward, I think it’ll be the one from Percona. Why? Well, first, it’s the one you tell folks to use, heh, and second (and perhaps unfairly), the one from ProxySQL coredumped on me once, and Percona’s hasn’t (yet). Also, it looks like you folks are tweaking it - they’re close, but not identical - to best work with PXC, in whatever way you determine ‘best’ to be.

Having moved from my single, old, MySQL 5.5 server, to a shiny new PXC, thus preventing my DB from being a single point of failure, I now have to figure out how to prevent ProxySQL from being the single point of failure, which it currently is. I see that there are a few different ways of doing it, but I’m not yet sure of which way to go. I have 16 application servers in play. One thought would be to simply put ProxySQL on each of them, and have them connect directly to the cluster (dropping my current two standalone test ProxySQL servers) but then I have 16 configurations to maintain, and 16 ProxySQLs constantly poking the database. Neither of those are show stoppers - I just haven’t decided what I want to do yet. As mentioned earlier, I’m new to this (but learning fast!). :slight_smile: