473,320 Members | 1,831 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

upgrade probs to 5.0.18 - queries fail

Hi all,

I'm hoping someone on this forum can help me. I use hibernate with java
to access mysql. We are trying to upgrade from 4.1.15 non-inndb, to
5.0.18 with innodb. Hibernate makes a 'create tables' script. The
script installs, but the queries that hibernate creates now fails.
These same queries worked fine with 4.1 .

Since this is a hard problem to solve on a forum, if any kind fellow
would like to, you could install the db from this zip:

http://www.braziloutsource.com/random/hwtf.zip

The query failing is this:

select count(activity0_.activity_id) as col_0_0_ from activity
activity0_, appointment_scheduler appointmen1_ inner join
activity_velox_info activityve2_ on
activity0_.act_velox_info_id=activityve2_.act_velo x_info_id inner join
activity_schedule activitysc3_ on
activity0_.act_sch_id=activitysc3_.act_sch_id inner join micro_area
microarea4_ on activityve2_.micro_area_id=microarea4_.micro_area_ id
inner join micro_area_scheduler microareas5_ on
appointmen1_.appsch_id=microareas5_.appsch_id inner join micro_area
microarea6_ on microareas5_.micro_area_id=microarea6_.micro_area_ id,
periods_of_day periodsofd7_ where (appointmen1_.appsch_id=1
)AND(activity0_.state<>'TRIAGE' )AND((activity0_.type='INSTALL'
)OR(activity0_.TYPE='SERVICE'
))AND(activity0_.promise_date>='2006-2-10'
)AND(activity0_.promise_date<'2006-2-11'
)AND(periodsofd7_.code='FIRST_HOUR' and
activityve2_.period_of_day_id=periodsofd7_.period_ of_day_id)AND(microarea4_.micro_area_id
IN(microarea6_.micro_area_id))AND(activitysc3_.sch eduler_id=appointmen1_.appsch_id
)

Unknown column 'activity0_.ACT_VELOX_INFO_ID' in 'on clause'

So its complaining on ACT_VELOX_INFO_ID, yet I can do a simple query:
select act_velox_info_id from activity;
+-------------------+
| act_velox_info_id |
+-------------------+
| 1 |
| 2 |

I'm very stuck. Any help highly appreciated.

iksrazal

Feb 11 '06 #1
3 1176
<ik******@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
The query failing is this:

SELECT . . .
FROM activity AS activity0_,
appointment_scheduler AS appointmen1_
INNER JOIN activity_velox_info AS activityve2_
ON activity0_.act_velox_info_id = activityve2_.act_velox_info_id

.. . .

I ran into this recently. It took me a while to figure it out. It is
explained in the docs, though.

MySQL 5.0.12 and later enforces an evaluation order in JOINs that is more
compliant with SQL:2003. So your alias "activity0_" hasn't been defined
yet, at the point when it's evaluating the join condition.

For example:

FROM tableA AS A, tableB AS B INNER JOIN tableC AS C ON A.field1 =
C.field2

The above fails, because the B JOIN C is evaluated before the A alias has
been created.

The solution is to use parentheses to force an order of evaluation:

FROM (tableA AS A, tableB AS B) INNER JOIN tableC AS C ON A.field1 =
C.field2

See the bulleted paragraph in
http://dev.mysql.com/doc/refman/5.0/en/join.html beginning:

"Previously, the comma operator (,) and JOIN both had the same precedence,
so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3).
Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2
JOIN t3)). "

Also see the bug http://bugs.mysql.com/bug.php?id=15779, in which the report
of joins failing in MySQL 5.0 was closed as "not a bug".

Regards,
Bill K.
Feb 12 '06 #2

Bill Karwin escreveu:
<ik******@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
The query failing is this:

SELECT . . .
FROM activity AS activity0_,
appointment_scheduler AS appointmen1_
INNER JOIN activity_velox_info AS activityve2_
ON activity0_.act_velox_info_id = activityve2_.act_velox_info_id

. . .

I ran into this recently. It took me a while to figure it out. It is
explained in the docs, though.

MySQL 5.0.12 and later enforces an evaluation order in JOINs that is more
compliant with SQL:2003. So your alias "activity0_" hasn't been defined
yet, at the point when it's evaluating the join condition.


Thanks Bill, that's the problem. I'm scambling for a solution since
hibernate is generating my queries, and there seems to be no way to
turn off this new feature, short of using pre 5.0.12. I'm hoping to get
a fix from hibernate.

iksrazal

Feb 13 '06 #3
Hi,

I'm having this exact same problem in my application. Was there any
resolution from Hibernate?

Thanks,
Saran

Feb 23 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: john | last post by:
I'm having some Interop problems. I really need someone's help on this, i'm running out of ideas. I have upgraded my Data access controls to the latest ver. Now then i have a form with these...
9
by: Scott Ribe | last post by:
OK, I'm using a pool of worker threads that each have a connection (from my own software, no PHP or anything like that involved), and a connection is in a state where all queries fail. Looking back...
3
by: RayPower | last post by:
I'm having a system using Access 2000 as both front-end (queries, forms, reports & temp tables for reports) & back-end (data) with back-end running on the server. The application runs on the...
1
by: rdraider | last post by:
Hi, I am trying to use the Upgrade Wizard in SQL 2000 to upgrade a SQL 6.5 db to 2000. I am only choosing to update a single database, not system objects. The Upgrade wizard connects to SQL 6.5,...
8
by: sabby | last post by:
I want to use the getline() so that i can enter a entire name in on line. (with spaces) The prob is that i am initializing the variable as "N/A" and saving it to a text file. it is declared as a...
6
by: ARC | last post by:
I'm almost ready to build an upgrade routine for a commercial app that will need to import data from an existing Access 97 back-end database to an Access 2007 back-end blank database. Ideally, it...
5
by: Ben | last post by:
We recently upgraded our MS SQL Server 2000 to 2005. Here is what we did: 1. Perform backup of the database from the old server. 2. Created a blank database in the new server. 3. Restored the...
5
by: marshmallowww | last post by:
I have an Access 2000 mde application which uses ADO and pass through queries to communicate with SQL Server 7, 2000 or 2005. Some of my customers, especially those with SQL Server 2005, have had...
3
by: Mark D Powell | last post by:
I attempted to upgrade my SQL Server 2000 Enterprise Edition (32 bit) to SQL Severer 2005 EE 32 bit and while the prereqs passed the installer will not let me select the version 8.00.x (2000 SP4)...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.