On Monday 17 September 2007 13:35, Dirk H Bartley wrote: > On Sun, 2007-09-16 at 19:23 +0200, Marc Cousin wrote: > > mmmkay ... > > > > I'm no mysql expert, but I think I've seen that before 4.0 or something, > > one can use LEFT JOIN xxx ON, but not JOIN xxx ON ... > > > > It's the sort of things that's only funny when you don't have to use > > mysql ... :) > > > > Anyone having mysql can confirm this ? > > > > On Sunday 16 September 2007 18:59:29 Scott Barninger wrote: > > > 3.23.58 > > > > > > On Sun, 2007-09-16 at 18:45 +0200, Marc Cousin wrote: > > > > what's the mysql version ? > > > > > > > > On Sunday 16 September 2007 18:19:50 Scott Barninger wrote: > > > > > Yes, for example this error when clicking on JobPlot. You get > > > > > similar errors for JobList and Media. > > Does changing "JOIN Status" to "INNER JOIN Status" fix it for that > version of mysql?? > > Is there some relatively easy change that can be made that would not use > ifdefs that would solve the issue?? > > The difficulty for me is that I don't have that version to test against. > I'm no sql expert, so I tend to go with what works for my environment.
It is probably the LEFT OUTER JOIN which is causing the problem. OUTER JOINs are *very* rare compared to INNER JOINs, and I'm not really sure why in this case that is what you chose. All LEFT INNER JOINs can be relatively easily eliminated by slightly transforming the SELECT into a form that is used in Bacula core, I can show you how, but it won't work for outer joins. The core code, except for the batch insert does not use LEFT JOIN directly. > > Dirk > > > > > > Query failed: .sql query="SELECT DISTINCT Job.Starttime AS > > > > > JobStart, Job.Jobfiles AS FileCount, Job.JobBytes AS Bytes, > > > > > Job.JobId AS JobId FROM Job JOIN Client ON > > > > > (Client.ClientId=Job.ClientId) JOIN Status ON > > > > > (Job.JobStatus=Status.JobStatus) LEFT OUTER JOIN FileSet ON > > > > > (FileSet.FileSetId=Job.FileSetId) ORDER BY Job.Starttime DESC, > > > > > Job.JobId DESC LIMIT 150". ERR=Query failed: SELECT DISTINCT > > > > > Job.Starttime AS JobStart, Job.Jobfiles AS FileCount, Job.JobBytes > > > > > AS Bytes, Job.JobId AS JobId FROM Job JOIN Client ON > > > > > (Client.ClientId=Job.ClientId) JOIN Status ON > > > > > (Job.JobStatus=Status.JobStatus) LEFT OUTER JOIN FileSet ON > > > > > (FileSet.FileSetId=Job.FileSetId) ORDER BY Job.Starttime DESC, > > > > > Job.JobId DESC LIMIT 150: ERR=You have an error in your SQL syntax > > > > > near 'ON (Client.ClientId=Job.ClientId) JOIN Status ON > > > > > (Job.JobStatus=Status.JobStatus' at line 1 > > > > > > > > > > On Sun, 2007-09-16 at 17:47 +0200, Marc Cousin wrote: > > > > > > what do you means by that ? you get a nested query error ? > > > > > > > > > > > > On Sunday 16 September 2007 15:47:34 Scott Barninger wrote: > > > > > > > FYI, > > > > > > > > > > > > > > The problem recently resolved in the director for disabling > > > > > > > batch insert is also present it seems in bat. When connected to > > > > > > > my 2.2.4 mysql server (rhel3) built with batch insert disabled, > > > > > > > I get the same sort of SQL error when clicking, for instance, > > > > > > > on JobList. > > ------------------------------------------------------------------------- > This SF.net email is sponsored by: Microsoft > Defy all challenges. Microsoft(R) Visual Studio 2005. > http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ > _______________________________________________ > Bacula-devel mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/bacula-devel ------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ Bacula-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/bacula-devel
