[prev in list] [next in list] [prev in thread] [next in thread] 

List:       bacula-devel
Subject:    Re: [Bacula-devel] batch insert and bat
From:       Scott Barninger <scott.barninger () comcast ! net>
Date:       2007-09-17 20:31:45
Message-ID: 1190061105.8771.9.camel () scott ! localdomain
[Download RAW message or body]

On Mon, 2007-09-17 at 14:21 +0200, Kern Sibbald wrote:
> 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.

For what it's worth, here is what my 3.23 manual says:

MySQL supports the following JOIN types:

tbl_list:
	tbl_name
	tbl_list, tbl_name
	tbl_list [CROSS] JOIN tbl_name
	tbl_list INNER JOIN tbl_name
	tbl_list STRAIGHT_JOIN tbl_name
	tbl_list LEFT [OUTER] JOIN tbl_name ON conditional_exp
	tbl_list LEFT [OUTER] JOIN tbl_name USING (column_list)
	tbl_list NATURAL LEFT [OUTER] JOIN tbl_name

...Using JOIN, CROSS JOIN or INNER JOIN is equivalent to using commas.
STRAIGHT_JOIN is similar but forces the optimizer to join the tables in
the order that the tables are named...in a LEFT JOIN...when there is no
matching row in the right table, columns from the right table are
returned as NULL values...LEFT OUTER JOIN is equivalent to LEFT
JOIN...NATURAL LEFT JOIN is equivalent to LEFT JOIN USING
(column_list)...

> 
> >
> > 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
> > Bacula-devel@lists.sourceforge.net
> > 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
> Bacula-devel@lists.sourceforge.net
> 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
Bacula-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-devel
[prev in list] [next in list] [prev in thread] [next in thread]