I have 4 LO databases from the old OO days. Each time I have upgraded, everything has worked well This time I have a number of issues. First, my auto increment columns (all are defined as integer) are not working properly with LO 5.1 on Ubuntu 16.04 x64. If I try to add a new record there is a zero in the field rather than the correct number. I can tell Base to add (save) the record, but it doesn’t, and it doesn’t tell me it hasn’t saved it. I have another table that uses that AI column in the first table as a foreign key. When I try to add that information, I get a MySQL error. because of the foreign key constraint and the fact that the first record (row) in the first table was not updated. Secondly, I have another table that is populated by a query. When I open the form, there is nothing in the table. If I go to the queries and execute it from there, it works properly. I am using MySQL 5.1.7 and everything works fine in both MySQL from the command line as well as from Workbench. Can anyone offer any insight? Thanks.
Question is a bit confusing. Are all these databases MySQL databases? If not, embedded or split (old or new split). Have similar set-up in Xubuntu and Mint 18 currently using LO v5.2.3.3 without problems. Have used v5.1.4.2 before and no problems. Is this a distro version? Any recent conversions of these “older” DB’s?
All DBs are MySQL access is direct.
What about LO version - distro or from LO?
As I said LO version is 5.1.4.2. It is a Ubuntu distro version.
Thank you. I knew the version just not if it was distro. I asked because this has been a problem source on occasion. However, as indicated in my answer I already loaded the distro version and can’t find any problem.
This is really funky. Only one of my forms malfunctions as described above. I have many that work properly. I can’t find anything different as far as design except the query that does not load the table is a Union ALL, but the 2 queries that make up the Union are of tables that are identical in all respects except values. And the query works if I go to the query itself within LO. Also, I have no idea why the auto increment field is resetting to zero when I try to add a new row.
If I am reading everything correctly, you can add a record in SQL Workbench but not in the form and the query works in the query section but not in the form. If that is the case, then it appears something is definitely wrong within the form.
You are correct as to the way things are working. What baffles me is that this form has worked for several versions of both OO and LO and many versions of Ubuntu. Perhaps it has to do with something unique to Ubuntu 16.04 and LO 5.1.4.2 (distro).
Here’s something else strange about the table not displaying anything on the form. If I execute the query from Base Queries, I get 4,260 rows. When I load the form, I get 4,260 rows with nothing being displayed. This tells me the query is being executed but not displayed. In the table Control, Visible is set to Yes. Very odd to me.
What you are saying is possible. Have seen in recent months where Ubuntu distro was producing a problem not present in regular LO version. Keep in mind this is rare. Only ran across 3 or 4 different occasions in all of 2016 (just one in Ubuntu). My reconstruction produced no problems and I do have multiple SQL (regular query & also macro) statements with joins and unions.
Okay, I found a solution to the problem of the query not loading the table. I created a View from the query and changed the Form to specify Table (with the name of the View) rather than Query. So half of this issue is resolved, but I still have no idea why the query stopped working. The problem of the auto increment field still eludes me.
Interesting. I changed the query so that it was no longer a UNION ALL; just a simple SELECT. Same problem. So I had to make a VIEW with the new query. That works. Hmm.
I investigated the auto increment issue a little more. The only difference I could find between this form and others is that I had Tab Stop = No. Changed it to Yes. Same problem. Still looking.
Okay, just got back from an appointment and found a little more. If I enter information into the form for the first record and exit, it is saved with the proper value in the AI column, even though the AI field shows zero. If I then re-enter the form, the proper value displays in the AI field, and I can add rows for the tables that have that AI column value as a foreign key. So, Even though the AI column seems to start with zero, it actually uses the correct value in the row when it is added.
However, I cannot add a row to a table that has that AI column as a fk, because either MySQL or LO thinks I am trying to add a row with a foreign key value of zero, which is an error. Is there somewhere in the form I can look to see if something is trying to set any of those fk’s to zero? I looked at the form properties for that table that expects the fk from the first table, and it has the same properties wrt its values, other than the specific name of the fk, as my other tables.
Working ‘in the blind’ is difficult. While you can examine various aspects of the form, I can only imagine and suppose. Here maybe you see a master & a sub-form. Now on the sub-form, if the info is obtained from a filter using a Master form “key” field this may be one way. It all depends upon how the entire form is set up. Again, working in this fashion is next to impossible.
Switched over to Ubuntu 16.04 which was installed about a month ago. Was going to review it again after abandoning it 18 mos. ago. I have LO v5.1.4.2 (distro) installed on it & MySQL v5.7.17 with MySQL Workbench v6.3.
Loaded about a dozen tables with a few thousand records over the tables from another system I use almost daily.
All works fine. Auto-increment working in all tables which have it. Queries work fine. Tables display data and work fine. Form all OK.
And as stated earlier, I’m using later versions from LO which also work without problem. Can’t imagine what is causing your problem.
Some of this stuff originated from back on LO v4.4.2 and possibly earlier.