Base crash query parameters

English
, , , ,
#1

I am having problems with base crashing very frequently.
It mostly happens when running a query which requires 2 date parameters and generally happens when the 1st digit of the 2nd date is entered. It doesn’t happen every time and generally the first time the query is run everything works OK.
This usually happens the 2nd or 3rd time the query is run and almost always happens with the 1st digit of the second date parameter.
Recovery always seems to work but heart drops everytime it crashes. I always make a backup copy of the database file before running as it virtually always crashes.
Any help would be really appreciated.
Is there a way to access any error report generated by the crash to find the cause?

Version: 6.4.6.2 Build ID: 1:6.4.6-0ubuntu0.20.04.1 CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3; Locale: en-GB (en_GB.UTF-8); UI-Language: en-GB Calc: threaded

Running embedded HSQLDB.
Tried with embedded firebird and same thing happens.

Removed all Libreoffice suite and re-installed with snap 7.0.2.2 and still happens with HSQLDB and firebird.
The problem area is probably associated in the WHERE section as this is where the date parameters are requested.

The full HSQLDB Query is :-

SELECT “Bookings”.“Booking_Ref” AS “Booking_Ref”, “Bookings”.“Agent” AS “Agent”, “Bookings”.“CustomerID” AS “CustomerID”, “Bookings”.“StartDate” AS “StartDate”, “Bookings”.“EndDate” AS “EndDate”, “Bookings”.“Comments” AS “Comments”, “Bookings”.“RecvdDate” AS “RecvdDate”, “Bookings”.“ElecDayStart” AS “ElecDayStart”, “Bookings”.“ElecNightStart” AS “ElecNightStart”, “Bookings”.“ElecDayEnd” AS “ElecDayEnd”, “Bookings”.“ElecNightEnd” AS “ElecNightEnd”, “Bookings”.“Status”, “Customers”.“Name” FROM “Bookings”, “Customers” WHERE “Bookings”.“CustomerID” = “Customers”.“CustomerID” AND ( “Bookings”.“StartDate” >= :Start_Date_From AND “Bookings”.“StartDate” <= :Start_Date_To OR “Bookings”.“StartDate” >= :Start_Date_From AND :Start_Date_To IS NULL OR “Bookings”.“StartDate” <= :Start_Date_To AND :Start_Date_From IS NULL OR :Start_Date_To IS NULL AND :Start_Date_From IS NULL ) ORDER BY “StartDate” ASC

This query wont work with firebird and had to change to use CAST format for NULL’s.
ie CAST( :Start_Date_From AS DATE ) IS NULL.

@ratslinger
This happens to be the query which I run inside another query which I reported as failing due to the ORDER BY a few days ago when I also mentioned the frequent crashing under “LIBRE BASE Query won’t run” where I mistakenly added as an answer rather than a comment.

#14

Whilst the original problem still exists (crashing using date parameter query), I have now managed to implement the “filter table search” method as suggested by @RobertG and this seems to be working fine.
It took me a few hours to modify all the relevant queries which used the date parameters, some of which were nested and also used in reports.
I can now confirm that this workaround is working fine with my database containing 4 years of records.
Many thanks @RobertG :+1: :+1:

#2

Hello,

When I set the second test, I got Base to crash. However, maybe did something incorrect. Have since tried dozens of times and no problems. Tried many different & same entries and no further crashes (at least a few dozen tests).

Here is my test file → ForumQuery.odb

#3

Hi @Ratslinger
I can’t believe you looked at this and put in effort today Christmas day. Thank you lots.

I tried with your odb file and yes. It crashed on entering the first digit of the 2nd date field for me on the first attempt, but was OK on the next 4 attempts.
I closed the database and re-opened it and the same thing happened.

At least you have been able to duplicate the crash I’m having, it’s always when entering the 1st digit of the second date parameter.

Is there a way to view the crash report to figure out what’s causing this? It’s happening on 2 versions of Base and seems to be independent of the embedded database engine.

There may be a better way to SQL the date checking I’m doing.
I want to enter date_from, date_to or leave either or both blank to return range, upto, from, or all records.

I’d like to convert to Firebird for future support but converting my database to Firebird is a nightmare, tables don’t convert well, query syntax is different and a macro fails!

#4

@BrianStew,

You’re welcome but I believe you may have not understood. The ONE and ONLY crash I got was on the second execution. Have done dozens of runs since without a crash. Even closed Base & re-opened multiple times without a problem. This on Ubuntu 20.04 Mate with LO 7.0.4.x from TDF.

If you continue with problems, first try resetting the user profile → LibreOffice user profile. If further problems, file a bug report → Bugzilla.

Firebird does take some adjusting to. There are many post here. Just search on Firebird. Once past initial hurdles it gets easier. Firebird should fix your Query calling Query situation.

#5

@BrianStew,

Was not fond of your Where portion of the SQL. Try this:

WHERE ("Bookings"."CustomerID" = "Customers"."CustomerID")
AND   (("Bookings"."StartDate" >= :Start_Date_From AND "Bookings"."StartDate" <= :Start_Date_To)
      OR ("Bookings"."StartDate" >= :Start_Date_From AND :Start_Date_To IS NULL)
      OR ("Bookings"."StartDate" <= :Start_Date_To AND :Start_Date_From IS NULL)
      OR (:Start_Date_To IS NULL AND :Start_Date_From IS NULL))
ORDER BY "StartDate" ASC
#6

@Ratslinger,
Sorry for any misunderstanding. I did understand that it crashed for you only once.
Initially when it crashed for me I thought i’d made an input mistake but i’ve discovered that it always happens at the same point and incorrect date entry is caught anyway.
Sadly this seems to be very intermittent and intermittent issues are always difficult.

I totally agree with you on not being fond of the Where portion of the SQL, adding parenthesis definitely makes it more readable. Sadly if you then enter graphical edit view (rather than SQL view) and save any changes, it removes them reverting to the original.

I still can’t find where any error logs are. Does Libreoffice create log files?

#7

@BrianStew,

Only know of crash reports and have not tried to do anything with them, so no help in that regard.

Still has not crashed again with that SQL. Runs each time I try it.

I also avoid the graphical view. It is limited, it has a number of problems and certainly does not work with many of the databases I use. Stick with SQL view as you can do more with this and is easier to transfer knowledge to other tools.

#8

Just an update,
Sadly nearly 3 years on I am still getting these crashes. I don’t run the query very often but it crashes 50% of the time and I have to recover the database, fortunately it has recovered every time but I’m always in fear of it not being recoverable and so always make a copy of the database before running it.
I did convert everything from HSQLDB to Firebird back in 2020 but exactly the same thing happens and am presently running 7.1.7.2.
I use the database to keep track of bookings for our holiday let and the date search function enables me to keep a track of how many nights we have booked in a tax year (6th April - 5th April, silly UK tax year!).

#9

Just tried updating to 7.6.2.1 and it still happens. I really do not know why this is happening but it seems very consistent, although it doesn’t happen every time it does fail around 50% of the time.
Also my reports no longer ran until I set a Java runtime environment.
I seem to remember a problem with my reports after upgrading a year or two ago and had to revert to 7.1.7.2 - hence running the older version.

#10

Have created many databases with LO Base. I never use a parameter in a query. If I want to filter the content I use a separate filter table, which only contains one row. With such a construction I never got a crash while running a query or a view, which looks for the values in filter table.

For your construction an example is needed to reproduce the behavior. If it could be reproduced by others it will be good to write a bug for this.

#11

I also use a filter with filter table for some data, unfortunately this is not possible (my assumption was wrong) when I need to display the data entries between two dates - eg over a financial year.

I have created a test database (very much cut down version of my full database to remove any personal data and data not relevant to this problem).
Run the query - it will prompt for a date range which will show how many guests stayed during that period.

  1. You can enter either no dates - all data is shown.
  2. Enter a start date and leave end date blank - all data from start date shown.
  3. Leave start date blank and enter an end date - all data to the end date shown.
  4. Enter both start and end dates - data between those dates is shown.

Entering no dates always works but is no real use for me.
Entering the second date often causes the crash but not always - about 50% of the time.

Test.odb (89.9 KB)

#12

StartDate_EndDate.odb (90.0 KB)
Can’t find a buggy behavior. Have set all you have included as parameters in the query in another query, which works with a filter table.
Have tested your query and mine. Both give the same result.

#13

Hi @RobertG
i’ve tried your filter table suggestion and it seems to work OK, :+1:
I’ve only ever used filter tables for a specific match and didn’t realise it could be used in a greater/less than situation - learning something every day (even in my senior years!!!).
My original query still crashes about 50% of the time, not always the first time, changing the dates usually causes it to crash (the date formats are valid).

I will try to implement your filter table solution into my database (contains 9 tables, 11 queries, 6 forms and 3 reports) but the date query is embedded deeply into it so it will take some time to work my way through it all.

Many thanks for your help which will hopefully be a workaround to what is clearly some intermittent bug in my installation which has persisted through quite a few versions of Libreoffice and Ubuntu.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.