Apache OpenOffice (AOO) Bugzilla – Issue 21591
OOo/MySQL combo hangs (with high CPU loads) when defining (certain SQL) queries
Last modified: 2006-05-31 14:29:06 UTC
Background: Our department offers to beginning students of Business Administrations and Economics a course with the goal to get them aquainted with various (software) tools for solving various tasks they may face during their studies and (later) their professional lives. Regarding the tools used, we allow for choices, one of them (You guess it) is OpenOffice.org, augmented by MySQL when it comes to databases. For tutorial purposes, a (simplified) bookshop database is provided, consisting of six tables: there are books (table name Buch - regarding our target audience, table and field names are German), publishers (Verlag) and customers (Kunde), which place orders (Auftrag) containing various articles (Artikel) on them; the final table (PLZ) is used to complete addresses of customers and publishers (I use this sample database to demonstrate the problem; its definition is long, so I don't include it here, but it may be obtained at http://dl.idv. edu/sw_2003W/BuecherDB/BuecherDB.sql ). The Problem: OOo queries (I am using the German version of OOo, so I hope I am translating these terms back correctly ;-) are a wonderfull tool for combining various tables for further processing with various OOo components (OOo Calc, mass mailings and reports). However, saving or executing some queries cause OOo to hang infinitely (well, at least I didn't await an end so far ;-) with high CPU loads. One of these queries is select k.Nr, k.Nachname, sum(r.Menge*b.Preis) as Umsatz from Kunde as k, Auftrag as a, Artikel as r, Buch as b where k.Nr=a.Kundennummer and a.Nr=r.Auftragsnummer and r.Buchnummer=b.Nr group by k.Nr, k.Nachname (the SQL way of asking how much money each customer has spent on books so far - so this query is definitely not too far fetched). We tested this both on Windows and on Linux operating systems, both with OOo 1.0 and 1.1, and connecting OOo to MySQL by either ODBC and JDBC (at 1.1, we tried the "MySQL" way(s), too). Database performance definitely isn't the problem (the same queries immediately produces results when executed from the mysql CLI; and so it does when executed from "old" StarOffice 5); networking shoudn't be an issue, too, as OOo and MySQL database are running on the same computer. Anyone to shed more light on this problem? Many, many, many thanks in advance - Alfred Novacek
Alfred, thanks for reporting this. For tracking the issue down, can you please have a look at your ODBC logs when the query is executed? I strongly suppose that you will see something like ... HAVING 0 = 1 in the logs. If so, you're runnig into issues 18040/18039. The symptom is described in issue 18039, the reason in issue 18040. There's no workaround for this problem, yet. But since it hits everybody who uses a GROUP BY with a halfway larger MySQL table, we could probably fix this for 1.1.1.
Thank You very much, Frank, for Your fast reply. The ODBC Log showed clearly, the query got rewritten as described in issues 18040/18039 (at least on Windows; on Linux, the query texts got truncated before the interresting parts), and the description of issue 18039 already sounded very familiar to me. Let's hope we don't have to wait too long for 1.1.1 :-) (skimming IssueZilla, I just found other issues 21024/20040 describing another problem I just wanted to report, telling it probably will be fixed in 1.1.1 too). Thank You very much - Alfred P.S.: With this posting, I am closing this issue by marking this issue as a duplicate to 18039 (which most closely describes the problem I observed first). *** This issue has been marked as a duplicate of 18039 ***
change subcomponent to 'none'
close issue.