Mysqli statement execute error: Prepared statement needs to be re-prepared

[Mark E. Johnson]

Hello. I have this MySQL problem when I try to run a sql statement. I've looked through the forums and found several posts about this. The posts suggest I contact the host and have then restart MySQL. Their response was this:

"Sorry to inform you that we are not able to reset MySQL service on the Shared hosting environment. You will have to contact Xenforo support for further assistance."

So now what do I do? Do you have any suggestions for hosting? I tried A2 Hosting but apparently they cannot help with my issue.

Thank You in advance.
Mark

 

[Chris D]

What exactly is the SQL statement you're running? Is there a stack trace available when the error happens? (This might be logged in the Admin CP error log).

 

[Mark E. Johnson]

Here is the function with the Select that is failing. It is selecting from a View.

public function getAllSeriesInfo()
{
return $this->fetchAllKeyed('SELECT * FROM SRO_series_view ORDER BY SeriesName', 'Id');
}

Attached is an image of the XF error message.

 

[Chris D]

I certainly can't see anything wrong with that SQL so it is likely to be related to the threads you have seen elsewhere.

Do you have any details on the server such as which version of MySQL it is running, and which version of PHP you are running? Does your host give you the ability, such as in cPanel, to restart services yourself or switch to a higher PHP version?

 

[Mark E. Johnson]

My host provided the following link for the info you requested. Hope it helps.

http://simracingonline.us/info.php

 

[Mark E. Johnson]

So I assume you have reviewed the information and you are scratching your head. If so, that is ok. At this point, I would be more interested in your suggestions for a new host. Can you please advise me on a hosting company that you know will not cause impediments like I have just experienced?

 

[Chris D]

I think it's worth going back to the host in the first instance to tell them that, as far as we can see, the query being executed should work without issue, and therefore it points to an issue with MySQL or PHP.

Looking at the PHP version, I'd say that's probably unlikely to be the cause. But I'm still not clear what the actual MySQL version is, as the phpinfo doesn't indicate that.

I'm still somewhat hesitant, overall, because if this is the only query that is failing on an otherwise functioning board, that seems somewhat odd to me, but I assume you have tested this on another (maybe local) server and it works fine?

I can't, unfortunately, advise you on a host directly as it very much depends on your needs and what level of service you need. I use OVH and have an unmanaged VPS from them. If you need shared hosting or managed hosting, your mileage may vary. You should probably have a search around this forum or ask on a more general web hosting related forum for more detailed advice and testimonials.

 

[Mark E. Johnson]

Thank you Chris. I have forwarded your response to my host provider. And yes my site is running ok on development and staging servers. As for needs, the site is a low traffic, "hobby" class application. I will try to find a general web hosting related forum and post my question there.

 

[Mark E. Johnson]

I'm just curious. Why would XenForo work on my host, which is obviously making database call successfully, and my Select statements would fail? Are they not running under the same account and using the same database access methods? This is confusing.

 

[Chris D]

I agree that it's confusing.

XF uses prepared statements extensively, so there's no particular reason I can think of why the query in your code would fail, and all of the others would work. I also can't think why the query would work fine elsewhere but fail on your server. It makes little sense.

 

[Mark E. Johnson]

I think I have a clue. I changed the Select to select the data from the Table instead of from the View and the error went away. It doesn't like Views.

 

[Chris D]

Heh, I suppose the clue was in the view name, but I assumed it was a table.

It's still somewhat confusing, though. We don't use views anywhere in XF hence why it didn't crop up anywhere else. But there's no reason I can think of that would explain the error.

It's worth noting, however, that XF itself isn't directly involved in the processing of that query, so the error is still pointing to some sort of issue with MySQL or PHP's mysqli extension.

It might be worth seeing if your host can shed any more light on it. It might be worth increasing the MySQL table_definition_cache and/or table_open_cache values to help resolve the issue, but I can't necessarily correlate why it would only affect select from view statements, unless there's something specific to this view such as it involving a fairly huge amount of data.

 

[Mark E. Johnson]

A quick update on my issue and for your future reference. If someone comes to you with an issue like this and they say they developed on Windows, ask them if they have ever tried it on Linux? The problem was, my View calls a Function. In the Function I was incorrectly using a table alias, which worked ok on Windows but not on Linux. ugh. My site is working great and it's my first XF site. You guys don't charge enough for your product. Love it.

I feel bad for wasting your time. I owe you another 50 bucks Peace.