I created a simple and empty mySQL database on a remote web server (10.1.35-MariaDB). Then I created a LibreOffice Base document linked to it by a direct connection (neither ODBC nor JBDC). The connection (password protected) works fine. By means of Base, I created a simple table (an automatic ID field and two VARCHAR fields) and populated it. Everything works fine. Then I created a view to sort the data according to the criteria I need. Everything works fine. But when I try to view the view (sorry: my English is basic), an error message appears saying that it was impossible to show the data: “Prepared statement needs to be re-prepared”. I am not an expert, so I can’t figure out what’s happening. Is it an issue? Or am I doing something wrong? I’m using Ubuntu Linux 20.04. LibreOffice version: 6.4.3.2 Build ID: 1:6.4.3-0ubuntu0.20.04.1
@Claudio : views are server-side stored objects with MariaDB/MySQL. They should be displayed in the Table list in LO. When you say you created a view, how did you go about this ? Does your view require any specific parameter input ?
@Alex > views are server-side stored objects with MariaDB/MySQL. They should be displayed in the Table list in LO. Yes, my view is correctly listed as a table. Only when I try to open it (double click or right click → open), the error occurs. Please note that the view can be properly accessed by phpMyAdmin. Also note that in LO I can modify the view. Just, I can't show it. > When you say you created a view, how did you go about this ? I simply used the LO Base procedure Create View (in Italian it's called Crea Vista, but I don't know the original English name) 1. Click the Create View command 2. Select the table 3. Drag and drop two of the three fields into the grid (I excluded the primary key field) 4. Set the order of one field as descending 5. Save > Does your view require any specific parameter input ? I don't think so. It only sorts the data according to the values of one column
Does the problem go away if you include the primary key?
Checked. The error is still present.
Thanks Claudio. I can't reproduce this with LO6432 on macOS Catalina (10.15..4), let me try with a 6.4 or 7alpha1.
I meant 6.3.4.2 not reproducible, sorry. Will try with a 6.4.x
OK. If you need, I will give you the credentials for accessing my database. It is not critical: it is just a test DB, so you can manage it without fear. But we need a private way for giving the credentials.
Could be an Ubuntu specific package problem (e.g. linked to mysql/maridab connector library version that they are using for their packages), wouldn't be the first time...
Where is your mariadb server running, on a LAN, or on the WAN/internet ? Wondering whether there is a timeout problem (which is a known issue in such cases, with currently no solution, as it partly depends on the server setttings, on the one hand, and the lack of an autoreconnect option for the native connector driver, on the other hand).
My tests were carried out on a LAN, and not over the internet (I see you mentioned remote webserver, but that doesn't say which kind of network).
No repro for me with : LO6442 macOS 10.15.4 MySQL server on LAN MySQL native connector connection in ODB file. The views that I have display without error, with or without a primary key.
@Claudio : https://stackoverflow.com/questions/4380813/how-to-get-rid-of-mysql-error-prepared-statement-needs-to-be-re-prepared would suggest that the problem is with your mariadb server configuration, and not LibreOffice. The bug, present in mysql server, also applied to mariadb. It can be worked around by editing my.cnf. If this is the case here, then this would not be a LO bug, and this report should be closed as INVALID.
@Alex Thank you. It's most likely possible the issue to be due to the server configuration. By the way, my DB relies on a web server on the internet. So I will ask my provider for supporting me on this issue. I don't know how to mark this report as INVALID, as you suggested (this status is not listed). Can you do it for me? If necessary, depending on the feed-back from my provider, I will reopen it. Thank you again.
I'm glad to make you informed that this issue has been fixed by my provider. He had just to update the system to the 5.5.5-10.1.44-MariaDB version. No further operations needed. At the end, it was not a LibreOffice Base bug, but in my opinion to communicate the solution may be useful. So, I'm going to change the status to Resolved - Fixed. I don't know if this applies: please, Alex, change the status if wrong.