MySQL upgrade breaks search?

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

MySQL upgrade breaks search?

Marc Pinnell-3
I recently upgraded my servers and in the process changed from  
version 4.021 to version 4.1.12 of MySQL. The below search (which  
gets saved as a var and inserted into an inline) was working fine and  
I just noticed that it no longer works! Any ideas?

SELECT EmailAdd, Website, FirstName, LastName, BizName FROM Members  
WHERE BizName BETWEEN 'a' AND 'lz' AND Approved = 'Yes' ORDER BY  
BizName;

It doesn't throw an error, just doesn't find anything. I believe it  
has something to do with the "Between a and lz" part.

Thanx
Marc

--
Marc Pinnell
1027 Consulting
web << DESIGN >> print
PO Box 990872; Redding, CA 96099-0872
www.1027Consulting.com



============================================
Attend the Lasso Summit
February 17-19, 2006 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL upgrade breaks search?

Greg Willits-2
MySQL user passwords changed format in 4.1. If you just restored the  
old users, they may not work. Redefined them manually.

Also, LP6 doesn't work with MySQL 4.1 unless you use JDBC connector.

-- gw


On Feb 18, 2006, at 1:30 PM, Lasso List wrote:

> I recently upgraded my servers and in the process changed from  
> version 4.021 to version 4.1.12 of MySQL. The below search (which  
> gets saved as a var and inserted into an inline) was working fine  
> and I just noticed that it no longer works! Any ideas?
>
> SELECT EmailAdd, Website, FirstName, LastName, BizName FROM Members  
> WHERE BizName BETWEEN 'a' AND 'lz' AND Approved = 'Yes' ORDER BY  
> BizName;
>
> It doesn't throw an error, just doesn't find anything. I believe it  
> has something to do with the "Between a and lz" part.


============================================
Attend the Lasso Summit
February 17-19, 2006 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL upgrade breaks search?

Marc Pinnell-3
In reply to this post by Marc Pinnell-3
I'm running Lasso 8.1 and all my other sites and searches seem to be  
working fine. In fact another page that calls info from this same  
table works fine. Of course that one is calling a specific record.

Marc


On Feb 18, 2006, at 1:34 PM, Greg Willits wrote:

> MySQL user passwords changed format in 4.1. If you just restored  
> the old users, they may not work. Redefined them manually.
>
> Also, LP6 doesn't work with MySQL 4.1 unless you use JDBC connector.
>
> -- gw
>
>
> On Feb 18, 2006, at 1:30 PM, Lasso List wrote:
>
>> I recently upgraded my servers and in the process changed from  
>> version 4.021 to version 4.1.12 of MySQL. The below search (which  
>> gets saved as a var and inserted into an inline) was working fine  
>> and I just noticed that it no longer works! Any ideas?
>>
>> SELECT EmailAdd, Website, FirstName, LastName, BizName FROM  
>> Members WHERE BizName BETWEEN 'a' AND 'lz' AND Approved = 'Yes'  
>> ORDER BY BizName;
>>
>> It doesn't throw an error, just doesn't find anything. I believe  
>> it has something to do with the "Between a and lz" part.
>
>
> ============================================
> Attend the Lasso SummitFebruary 17-19, 2006 in Fort Lauderdale, FL
> http://www.LassoSummit.com/
> ============================================
>
> --
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription: http://www.listsearch.com/ 
> lassotalk.lasso?manage

--
Marc Pinnell
1027 Consulting
web << DESIGN >> print
PO Box 990872; Redding, CA 96099-0872
www.1027Consulting.com



============================================
Attend the Lasso Summit
February 17-19, 2006 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL upgrade breaks search?

Greg Willits-2
In reply to this post by Marc Pinnell-3
Does it use timestamp field? That changed format too.

There's a list of things that changed in 4.1 in the MySQL docs. You  
should probably have a look thru there for stuff that might apply.

-- gw




On Feb 18, 2006, at 1:40 PM, Lasso List wrote:

> I'm running Lasso 8.1 and all my other sites and searches seem to  
> be working fine. In fact another page that calls info from this  
> same table works fine. Of course that one is calling a specific  
> record.
>
> Marc
>
>
> On Feb 18, 2006, at 1:34 PM, Greg Willits wrote:
>
>> MySQL user passwords changed format in 4.1. If you just restored  
>> the old users, they may not work. Redefined them manually.
>>
>> Also, LP6 doesn't work with MySQL 4.1 unless you use JDBC connector.
>>
>> -- gw
>>
>>
>> On Feb 18, 2006, at 1:30 PM, Lasso List wrote:
>>
>>> I recently upgraded my servers and in the process changed from  
>>> version 4.021 to version 4.1.12 of MySQL. The below search (which  
>>> gets saved as a var and inserted into an inline) was working fine  
>>> and I just noticed that it no longer works! Any ideas?
>>>
>>> SELECT EmailAdd, Website, FirstName, LastName, BizName FROM  
>>> Members WHERE BizName BETWEEN 'a' AND 'lz' AND Approved = 'Yes'  
>>> ORDER BY BizName;
>>>
>>> It doesn't throw an error, just doesn't find anything. I believe  
>>> it has something to do with the "Between a and lz" part.
>>
>>
>> ============================================
>> Attend the Lasso SummitFebruary 17-19, 2006 in Fort Lauderdale, FL
>> http://www.LassoSummit.com/
>> ============================================
>>
>> --
>> ------------------------------
>> Lasso Support: http://support.omnipilot.com/
>> Search the list archives: http://www.listsearch.com/lassotalk.lasso
>> Manage your list subscription: http://www.listsearch.com/ 
>> lassotalk.lasso?manage
>
> --
> Marc Pinnell
> 1027 Consulting
> web << DESIGN >> print
> PO Box 990872; Redding, CA 96099-0872
> www.1027Consulting.com
>
>
>
> ============================================
> Attend the Lasso SummitFebruary 17-19, 2006 in Fort Lauderdale, FL
> http://www.LassoSummit.com/
> ============================================
>
> --
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription: http://www.listsearch.com/ 
> lassotalk.lasso?manage


============================================
Attend the Lasso Summit
February 17-19, 2006 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL upgrade breaks search?

Brian Loomis
In reply to this post by Marc Pinnell-3
and the query works from the command line or outside of lasso?

On Feb 18, 2006, at 4:40 PM, Lasso List wrote:

> I'm running Lasso 8.1 and all my other sites and searches seem to  
> be working fine. In fact another page that calls info from this  
> same table works fine. Of course that one is calling a specific  
> record.
>
> Marc
>
>
> On Feb 18, 2006, at 1:34 PM, Greg Willits wrote:
>
>> MySQL user passwords changed format in 4.1. If you just restored  
>> the old users, they may not work. Redefined them manually.
>>
>> Also, LP6 doesn't work with MySQL 4.1 unless you use JDBC connector.
>>
>> -- gw
>>
>>
>> On Feb 18, 2006, at 1:30 PM, Lasso List wrote:
>>
>>> I recently upgraded my servers and in the process changed from  
>>> version 4.021 to version 4.1.12 of MySQL. The below search (which  
>>> gets saved as a var and inserted into an inline) was working fine  
>>> and I just noticed that it no longer works! Any ideas?
>>>
>>> SELECT EmailAdd, Website, FirstName, LastName, BizName FROM  
>>> Members WHERE BizName BETWEEN 'a' AND 'lz' AND Approved = 'Yes'  
>>> ORDER BY BizName;
>>>
>>> It doesn't throw an error, just doesn't find anything. I believe  
>>> it has something to do with the "Between a and lz" part.
>>
>>
>> ============================================
>> Attend the Lasso SummitFebruary 17-19, 2006 in Fort Lauderdale, FL
>> http://www.LassoSummit.com/
>> ============================================
>>
>> --
>> ------------------------------
>> Lasso Support: http://support.omnipilot.com/
>> Search the list archives: http://www.listsearch.com/lassotalk.lasso
>> Manage your list subscription: http://www.listsearch.com/ 
>> lassotalk.lasso?manage
>
> --
> Marc Pinnell
> 1027 Consulting
> web << DESIGN >> print
> PO Box 990872; Redding, CA 96099-0872
> www.1027Consulting.com
>
>
>
> ============================================
> Attend the Lasso SummitFebruary 17-19, 2006 in Fort Lauderdale, FL
> http://www.LassoSummit.com/
> ============================================
>
> --
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription: http://www.listsearch.com/ 
> lassotalk.lasso?manage


============================================
Attend the Lasso Summit
February 17-19, 2006 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL upgrade breaks search?

Marc Pinnell-3
In reply to this post by Marc Pinnell-3
No. Returns nothing and no error.

On Feb 18, 2006, at 1:44 PM, Brian (Earthlink) wrote:

> and the query works from the command line or outside of lasso?
>
> On Feb 18, 2006, at 4:40 PM, Lasso List wrote:
>
>> I'm running Lasso 8.1 and all my other sites and searches seem to  
>> be working fine. In fact another page that calls info from this  
>> same table works fine. Of course that one is calling a specific  
>> record.
>>
>> Marc
>>
>>
>> On Feb 18, 2006, at 1:34 PM, Greg Willits wrote:
>>
>>> MySQL user passwords changed format in 4.1. If you just restored  
>>> the old users, they may not work. Redefined them manually.
>>>
>>> Also, LP6 doesn't work with MySQL 4.1 unless you use JDBC connector.
>>>
>>> -- gw
>>>
>>>
>>> On Feb 18, 2006, at 1:30 PM, Lasso List wrote:
>>>
>>>> I recently upgraded my servers and in the process changed from  
>>>> version 4.021 to version 4.1.12 of MySQL. The below search  
>>>> (which gets saved as a var and inserted into an inline) was  
>>>> working fine and I just noticed that it no longer works! Any ideas?
>>>>
>>>> SELECT EmailAdd, Website, FirstName, LastName, BizName FROM  
>>>> Members WHERE BizName BETWEEN 'a' AND 'lz' AND Approved = 'Yes'  
>>>> ORDER BY BizName;
>>>>
>>>> It doesn't throw an error, just doesn't find anything. I believe  
>>>> it has something to do with the "Between a and lz" part.
>>>
>>>
>>> ============================================
>>> Attend the Lasso SummitFebruary 17-19, 2006 in Fort Lauderdale, FL
>>> http://www.LassoSummit.com/
>>> ============================================
>>>
>>> --
>>> ------------------------------
>>> Lasso Support: http://support.omnipilot.com/
>>> Search the list archives: http://www.listsearch.com/lassotalk.lasso
>>> Manage your list subscription: http://www.listsearch.com/ 
>>> lassotalk.lasso?manage
>>
>> --
>> Marc Pinnell
>> 1027 Consulting
>> web << DESIGN >> print
>> PO Box 990872; Redding, CA 96099-0872
>> www.1027Consulting.com
>>
>>
>>
>> ============================================
>> Attend the Lasso SummitFebruary 17-19, 2006 in Fort Lauderdale, FL
>> http://www.LassoSummit.com/
>> ============================================
>>
>> --
>> ------------------------------
>> Lasso Support: http://support.omnipilot.com/
>> Search the list archives: http://www.listsearch.com/lassotalk.lasso
>> Manage your list subscription: http://www.listsearch.com/ 
>> lassotalk.lasso?manage
>
>
> ============================================
> Attend the Lasso SummitFebruary 17-19, 2006 in Fort Lauderdale, FL
> http://www.LassoSummit.com/
> ============================================
>
> --
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription: http://www.listsearch.com/ 
> lassotalk.lasso?manage

--
Marc Pinnell
1027 Consulting
web << DESIGN >> print
PO Box 990872; Redding, CA 96099-0872
www.1027Consulting.com



============================================
Attend the Lasso Summit
February 17-19, 2006 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL upgrade breaks search?

Olivier Miossec
In reply to this post by Marc Pinnell-3
> I recently upgraded my servers and in the process changed from
> version 4.021 to version 4.1.12 of MySQL. The below search (which
> gets saved as a var and inserted into an inline) was working fine and
> I just noticed that it no longer works! Any ideas?
>
> SELECT EmailAdd, Website, FirstName, LastName, BizName FROM Members
> WHERE BizName BETWEEN 'a' AND 'lz' AND Approved = 'Yes' ORDER BY
> BizName;
>
> It doesn't throw an error, just doesn't find anything. I believe it
> has something to do with the "Between a and lz" part.
>
> Thanx
> Marc


It can be a problem with the BizName field type.
Before Mysql 4.0.x BETWEEN is evaluate using the field type
Now it use the entire expression and you have a CHAR type and I guess a
VARCHAR type so you may need to explicitly CAST the value.


---
Olivier Miossec
AIM/ichat omiossec
--
http://www.lasso-developpeur.net
--

Need a Lasso consultant ?
(Lasso, Java, Databases Consultant ...)
http://www.lasso-developpeur.net/oliviermiossec/

--




============================================
Attend the Lasso Summit
February 17-19, 2006 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL upgrade breaks search?

James Harvard
In reply to this post by Marc Pinnell-3
And the moral of this story is always check for incompatible changes before upgrading. :-)

For future reference: MySQL always has a list of changes you need to know about when upgrading between non-maintenance releases. The one you need is http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-4-0.html

HTH,
James H

At 2:34 pm -0800 18/2/06, Lasso List wrote:
>Apparently, from what I can find, MySQL 4.0x didn't have multiple character/collation sets, but 4.1 does. When upgrading it labeled all my columns as UTF8. I am guessing that UTF8 is case sensitive where MySQL searches were case insensitive before.

============================================
Attend the Lasso Summit
February 17-19, 2006 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage