Re: Retrieving partial field values

[Jigal van Hemert]

> Example a (GivenNames): Mary Elizabeth or Marg Elizabeth
> Example b (GivenNames): J. W. or I. W.
> Example c (Surname): Stotts, Statts or Stutts
>
> I need to be able to retrieve the following based upon the examples:
>
> For Example a:
> Return Mary Elizabeth where GivenNames begins with M;
> Return Marg Elizabeth where GivenNames begins with M;
> Return Mary Elizabeth where GivenNames contains the whole word Mary;
> Return Marg Elizabeth where GivenNames contains the whole word Marg;
> Return Mary Elizabeth where GivenNames=Mary Elizabeth
> Return Marg Elizabeth where GivenNames=Marg Elizabeth

I'm beginning to think that a form of FULL TEXT search is what you need;
take a look at these articles:
http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html
http://dev.mysql.com/doc/mysql/en/Fulltext_Boolean.html
and even query expansion might be usefull in your case (though it might be a
bit slow):
http://dev.mysql.com/doc/mysql/en/Fulltext_Query_Expansion.html

Regards, Jigal.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

redirectingerror

[N. Kavithashree]

hello,

the questn may be simple.but i m still in learning stage so please dont
get angry.

thank you

how to redirect every single error cmg in program with database connection
to other place than standard error log.

my error log is growing so fast so want to avoid error log for any type of
error.



kavi
===


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

in regards to merge table in MySQL 4.1.x

[JiSang YUN]

Hi, list

i'd like to know about that merge table type.

what i get to the best benefits, when i use this engine type?

thank you.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: traverse the recordset more than once

[Rudy Lippan]

On Thu, 6 Jan 2005, sam wun wrote:

> Steve Buehler wrote:
> 
> > At 08:11 PM 1/5/2005, you wrote:
> >
> >> Hi list,
> >>
> >> How can I traverse the recordset moret than once?
> >>
> >> For example, the recordset $sth is returned from executing the sql 
> >> statement.
> >> The following statement is writen with the "for" loop for retrieving 
> >> each record from the recordset.
> >>
> >> $aref = $sth->fetchrow_arrayref
> >>
> >> However when the "for" loop is finished, the pointer of the recordset 
> >> is point to the end of the recordset. How can I revise the point to 
> >> the beginning of the recordset ($sth)?
> >

If you hit the end of the result set, $sth->finish is automatically called for
you, and the result set is freed. If you want to go over it more than one time,
your best bet would be to use fetchall_arrayref(), and the loop over that as in:

my $data = $sth->fetchall_arrayref();

for my $row (@$data) {
  # do stuff with @$row;
}

Yes that will use more memory, but remember the entire data set is already
cahced client side, and that cached data set is free()d as soon as it is copied
into a perl data structure. But if you really don't wan't to have 2 copies
hanging around, you can always re-execute:

$sth->execute(@params);

while (my @array = $sth->fetchrow_array) {
   # do stuff
}

$sth->execute(); #execute again
#do more stuff.


Rudy


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MyODBC 3.5.9 and MySQL 4.1.8

[Prasad Mhatre]

I Agree I did downloaded from dev.x MyODBC 3.5.10-x86-win-32bit.exe and
it works with MySQL 4xx

Thanks
Love and regards
Prasad
- Original Message -
From: "Homam S.A." <[EMAIL PROTECTED]>
To: "Tom Crimmins" <[EMAIL PROTECTED]>; "nikos" <[EMAIL PROTECTED]>
Cc: 
Sent: Thursday, January 06, 2005 12:43 AM
Subject: RE: MyODBC 3.5.9 and MySQL 4.1.8


> Tom, this is awesome! It must have been fresh out of
> dev because I downloaded 3.51.09 a couple of days ago.
> I just downloaded it and it works fine with 4.1
> authentication.
>
> Thanks!
>
>
>
> --- Tom Crimmins <[EMAIL PROTECTED]> wrote:
>
> > Try MyODBC 3.51.10. It supports 4.1 auth. Here is a
> > link to a mirror that
> > has it:
> >
> >
> http://mysql.netvisao.pt/downloads/connector/odbc/3.51.html
> >
> >
> > ---
> > Tom Crimmins
> > Interface Specialist
> > Pottawattamie County, Iowa
> >
> >
> > -Original Message-
> > From: nikos
> > Sent: Wednesday, January 05, 2005 3:09 AM
> > To: mysql@lists.mysql.com
> > Subject: MyODBC 3.5.9 and MySQL 4.1.8
> >
> >
> > Hello list and happy new year.
> >
> > Recently I've install mysql 4.1 on win 2000 with IIS
> > 5 and works perfect. My
> > problem is that when i'm trying to make a connection
> > with myodbc (latest
> > release) as localhost I got the following message:
> > "Client does not support authentication protocol
> > requestet by server.
> > Consider upgrading mysql client".
> >
> > MyODBC whorks fine because I 've allready make
> > connection throw lan on a
> > Linux RH-9 with apache and mysql 4.0.22
> >
> > Any suggestions?
> > Thanky you
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> >
> http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
>
>
>
>
> __
> Do you Yahoo!?
> Meet the all-new My Yahoo! - Try it today!
> http://my.yahoo.com
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: traverse the recordset more than once

[Logan, David (SST - Adelaide)]

Hi Sam,

Remember you are only dealing with an array of references. Not the
actual record. This shouldn't take much time to do a copy and you will
only be keeping pointers in memory not much else.

Regards


David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-Original Message-
From: sam wun [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 6 January 2005 1:53 PM
Cc: mysql
Subject: Re: traverse the recordset more than once

Steve Buehler wrote:

> At 08:11 PM 1/5/2005, you wrote:
>
>> Hi list,
>>
>> How can I traverse the recordset moret than once?
>>
>> For example, the recordset $sth is returned from executing the sql 
>> statement.
>> The following statement is writen with the "for" loop for retrieving 
>> each record from the recordset.
>>
>> $aref = $sth->fetchrow_arrayref
>>
>> However when the "for" loop is finished, the pointer of the recordset

>> is point to the end of the recordset. How can I revise the point to 
>> the beginning of the recordset ($sth)?
>
>
> Another thing you might try is to Set
> $arefgood = $sth->fetchrow_arrayref
> $arefusethisone = $arefgood
> Use the $arefusethisone in the for loop.  When you want to restart the

> for loop, just set "$arefusethisone = $arefgood" again.  Doing this, 
> you should always have your original.  This will probably work in any 
> language you are programming in, but since I don't program in to many 
> languages, I can't give you a definite on that one.
>
Thanks I hvae thought about this method, but conerning that it  will 
require extra memory to keep the orginal copy of the recordset, and also

whenever assign the original recordset to the temprecordset, it will 
need to take extra time to do the copies, I assumed this is not shadow 
copies.

Sam

> Steve
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: traverse the recordset more than once

[sam wun]

Steve Buehler wrote:
At 08:11 PM 1/5/2005, you wrote:
Hi list,
How can I traverse the recordset moret than once?
For example, the recordset $sth is returned from executing the sql 
statement.
The following statement is writen with the "for" loop for retrieving 
each record from the recordset.

$aref = $sth->fetchrow_arrayref
However when the "for" loop is finished, the pointer of the recordset 
is point to the end of the recordset. How can I revise the point to 
the beginning of the recordset ($sth)?

Another thing you might try is to Set
$arefgood = $sth->fetchrow_arrayref
$arefusethisone = $arefgood
Use the $arefusethisone in the for loop.  When you want to restart the 
for loop, just set "$arefusethisone = $arefgood" again.  Doing this, 
you should always have your original.  This will probably work in any 
language you are programming in, but since I don't program in to many 
languages, I can't give you a definite on that one.

Thanks I hvae thought about this method, but conerning that it  will 
require extra memory to keep the orginal copy of the recordset, and also 
whenever assign the original recordset to the temprecordset, it will 
need to take extra time to do the copies, I assumed this is not shadow 
copies.

Sam
Steve

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: traverse the recordset more than once

[sam wun]

Steve Buehler wrote:
At 08:11 PM 1/5/2005, you wrote:
Hi list,
How can I traverse the recordset moret than once?
For example, the recordset $sth is returned from executing the sql 
statement.
The following statement is writen with the "for" loop for retrieving 
each record from the recordset.

$aref = $sth->fetchrow_arrayref
However when the "for" loop is finished, the pointer of the recordset 
is point to the end of the recordset. How can I revise the point to 
the beginning of the recordset ($sth)?

Kind of depends on what programing language you are writing the script 
in.  If you are using PHP, you might look into the reset function.  I 
"think" that might do what you want.  At least it is a startmaybe
http://us3.php.net/manual/en/function.reset.php


I m using Perl DBI.
Thanks
Sam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: traverse the recordset more than once

[Steve Buehler]

At 08:11 PM 1/5/2005, you wrote:
Hi list,
How can I traverse the recordset moret than once?
For example, the recordset $sth is returned from executing the sql statement.
The following statement is writen with the "for" loop for retrieving each 
record from the recordset.

$aref = $sth->fetchrow_arrayref
However when the "for" loop is finished, the pointer of the recordset is 
point to the end of the recordset. How can I revise the point to the 
beginning of the recordset ($sth)?
Another thing you might try is to Set
$arefgood = $sth->fetchrow_arrayref
$arefusethisone = $arefgood
Use the $arefusethisone in the for loop.  When you want to restart the for 
loop, just set "$arefusethisone = $arefgood" again.  Doing this, you should 
always have your original.  This will probably work in any language you are 
programming in, but since I don't program in to many languages, I can't 
give you a definite on that one.

Steve 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: traverse the recordset more than once

[Steve Buehler]

At 08:11 PM 1/5/2005, you wrote:
Hi list,
How can I traverse the recordset moret than once?
For example, the recordset $sth is returned from executing the sql statement.
The following statement is writen with the "for" loop for retrieving each 
record from the recordset.

$aref = $sth->fetchrow_arrayref
However when the "for" loop is finished, the pointer of the recordset is 
point to the end of the recordset. How can I revise the point to the 
beginning of the recordset ($sth)?
Kind of depends on what programing language you are writing the script 
in.  If you are using PHP, you might look into the reset function.  I 
"think" that might do what you want.  At least it is a startmaybe
http://us3.php.net/manual/en/function.reset.php


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

traverse the recordset more than once

[sam wun]

Hi list,
How can I traverse the recordset moret than once?
For example, the recordset $sth is returned from executing the sql 
statement.
The following statement is writen with the "for" loop for retrieving 
each record from the recordset.

$aref = $sth->fetchrow_arrayref
However when the "for" loop is finished, the pointer of the recordset is 
point to the end of the recordset. How can I revise the point to the 
beginning of the recordset ($sth)?

Thanks
Sam.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Grant question

[Tom Crimmins]

[snip]
Is it possible to grant all these databases in just one GRANT instruction
such as:

GRANT SELECT,INSERT,UPDATE,DELETE ON dbexample*.* TO 'user'@'localhost' BY
'password';
[/snip]

GRANT [privs] ON `dbexample%`.* TO 'user'@'localhost' IDENTIFIED BY
'password';

---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa


-Original Message-
From: Andre Matos [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, January 04, 2005 4:26 PM
To: mysql@lists.mysql.com
Subject: Grant question

Hi list,

I have many databases on my MySQL server such as:

dbexample
dbexample_clients_abc
dbexample_clients_def
dbexample_clients_ghi
dbexample_local_abc
dbexample_local_def
dbexample_local_ghi

Is it possible to grant all these databases in just one GRANT instruction
such as:

GRANT SELECT,INSERT,UPDATE,DELETE ON dbexample*.* TO 'user'@'localhost' BY
'password';

I tried this instruction but did not work. Is someone knows how to do if it
is possible?

Thanks for any help.

Andre

--
Andre Matos
[EMAIL PROTECTED] 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Grant question

[Andre Matos]

Hi list,

I have many databases on my MySQL server such as:

dbexample
dbexample_clients_abc
dbexample_clients_def
dbexample_clients_ghi
dbexample_local_abc
dbexample_local_def
dbexample_local_ghi

Is it possible to grant all these databases in just one GRANT instruction
such as:

GRANT SELECT,INSERT,UPDATE,DELETE ON dbexample*.* TO 'user'@'localhost' BY
'password';

I tried this instruction but did not work. Is someone knows how to do if it
is possible?

Thanks for any help.

Andre

--
Andre Matos
[EMAIL PROTECTED] 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Ignore

[Scott Haneda]

Please ignore



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Ignore

[Scott Haneda]

Please ignore



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MYSQL4.0.23 upgrade

[Teresa A Narvaez]

Hello,
I am trying to upgrade from MYSQL 3.23.58 to MYSQL 4.0.23 on a Tru64 alpha
server.  However, I am getting the error below at compile time.  any ideas?
Thanks in advance -teresa.


Platform: Alpha Tru64 OSF 4.0F

Configuration parameters:
 CC="cc -pthread"  \
 CXX="cxx -pthread -O"  \
 export CC CFLAGS CXX CXXFLAGS\
 ./configure \
 --with-named-thread-libs="-lpthread -lmach -lexc -lc"\
 --prefix=/usr/local/mysql-4.0.23 \
 --enable-assembler \
 --with-docs\
 --enable-large-files \
 --with-mysqld-ldflags=-all-static

Error:

cations -o .libs/libmysqlclient.so.12.0.0
/bin/ld:
-pthread: Unknown flag
/bin/ld: Usage: /bin/ld [options] file [...]
gnumake[2]: *** [libmysqlclient.la] Error 1
gnumake[2]: Leaving directory `/data/users/builder/untar/mysql-4.0.23
a/libmysql'
gnumake[1]: *** [all-recursive] Error 1
gnumake[1]: Leaving directory `/data/users/builder/untar/mysql-4.0.23a'
gnumake: *** [all] Error 2



This is a PRIVATE message. If you are not the intended recipient, please
delete without copying and kindly advise us by e-mail of the mistake in
delivery. NOTE: Regardless of content, this e-mail shall not operate to
bind CSC to any order or other contract unless pursuant to explicit written
agreement or government initiative expressly permitting the use of e-mail
for such purpose.




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Pear abstraction class

[gunmuse]

We are using a pear 
abstraction class written by Mohammed J. Kabir  Problem is its out of date for 
error reporting.  Does anyone know where a current version of this 
is.  Hopefully one that would be a plug in exchange for the one we have so 
that we don't have to rewrite the software.

ThanksDonny LairsonPresident29 
GunMuse LaneP.O. box 166Lakewood NM 88254http://www.gunmuse.com469 228 2183 

Auto Table Name Completion has stopped working?

[Kieran Kelleher]

Normally, when you type the first few letters of a table name and press 
tab, the mysql client does not auto complete anymore.  Can this be 
enabled with a my.cnf setting or something since it is no longer the 
default behaviour on my machine?

Regards, Kieran

Dev Config = OS X 10.3.5 / Java 1.4.2_05 /  WO 5.2.3 / XCode v1.5 / 
MySQL 4.0.20 / Connector-J 3.0.11
Deploy Config = OS X 10.3.7 Server / Java 1.4.2_05 / WO 5.2.3 / MySQL 
4.0.20 / Connector-J 3.0.11
My Blog: http://webobjects.webhop.org/

RE: MyODBC 3.5.9 and MySQL 4.1.8

[Homam S.A.]

Tom, this is awesome! It must have been fresh out of
dev because I downloaded 3.51.09 a couple of days ago.
I just downloaded it and it works fine with 4.1
authentication.

Thanks!



--- Tom Crimmins <[EMAIL PROTECTED]> wrote:

> Try MyODBC 3.51.10. It supports 4.1 auth. Here is a
> link to a mirror that
> has it:
> 
>
http://mysql.netvisao.pt/downloads/connector/odbc/3.51.html
> 
> 
> ---
> Tom Crimmins
> Interface Specialist
> Pottawattamie County, Iowa
> 
> 
> -Original Message-
> From: nikos
> Sent: Wednesday, January 05, 2005 3:09 AM
> To: mysql@lists.mysql.com
> Subject: MyODBC 3.5.9 and MySQL 4.1.8
> 
> 
> Hello list and happy new year.
> 
> Recently I've install mysql 4.1 on win 2000 with IIS
> 5 and works perfect. My
> problem is that when i'm trying to make a connection
> with myodbc (latest
> release) as localhost I got the following message:
> "Client does not support authentication protocol
> requestet by server.
> Consider upgrading mysql client". 
> 
> MyODBC whorks fine because I 've allready make
> connection throw lan on a
> Linux RH-9 with apache and mysql 4.0.22
> 
> Any suggestions?
> Thanky you
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
>
http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 




__ 
Do you Yahoo!? 
Meet the all-new My Yahoo! - Try it today! 
http://my.yahoo.com 
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: MyODBC 3.5.9 and MySQL 4.1.8

[Tom Crimmins]

Try MyODBC 3.51.10. It supports 4.1 auth. Here is a link to a mirror that
has it:

http://mysql.netvisao.pt/downloads/connector/odbc/3.51.html


---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa


-Original Message-
From: nikos
Sent: Wednesday, January 05, 2005 3:09 AM
To: mysql@lists.mysql.com
Subject: MyODBC 3.5.9 and MySQL 4.1.8


Hello list and happy new year.

Recently I've install mysql 4.1 on win 2000 with IIS 5 and works perfect. My
problem is that when i'm trying to make a connection with myodbc (latest
release) as localhost I got the following message:
"Client does not support authentication protocol requestet by server.
Consider upgrading mysql client". 

MyODBC whorks fine because I 've allready make connection throw lan on a
Linux RH-9 with apache and mysql 4.0.22

Any suggestions?
Thanky you

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Cannot create Windows service for MySql. Error: 0

[Tom Crimmins]

I had this problem once. My fix was to delete the innodb files from the
mysql datadir (log and data files), then run mysqld-nt from a command
prompt. After it starts normally, you can stop it and then install it as a
service. I don't know if this will work for you, but it did for me. 

---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa


-Original Message-
From: Eugenia Mariani [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 05, 2005 11:57 AM
To: mysql@lists.mysql.com
Subject: Cannot create Windows service for MySql. Error: 0

My O.S in Win Xp Pro SP2.
My web server is Apache 5.0
While I am trying to install MySql Server 4.1, I have the following error
and I cannot install the Server:

Cannot create Windows service for MySql. Error: 0

Can someone help me to install without error?
Thanks
Eugenia Mariani

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: MyODBC 3.5.9 and MySQL 4.1.8

[Homam S.A.]

You could also leave the password blank if you don't
want to force MySQL to revert to the old password
encryption on start-up. This should be fine if your
server is in the DMZ and you control all the processes
that have access to the server.

Unfortunately, 4.2.x breaks backward compatibility
with ODBC and many other old clients, including major
open source PHP projects.



--- Mechain Marc <[EMAIL PROTECTED]> wrote:

> 
> Have a look at:
> 
> http://dev.mysql.com/doc/mysql/en/Old_client.html
> 
> Marc.
> 
> -Message d'origine-
> De : nikos [mailto:[EMAIL PROTECTED]
> Envoyé : mercredi 5 janvier 2005 10:09
> À : mysql@lists.mysql.com
> Objet : MyODBC 3.5.9 and MySQL 4.1.8
> 
> 
> 
> Hello list and happy new year.
> 
> Recently I've install mysql 4.1 on win 2000 with IIS
> 5 and works
> perfect. My problem is that when i'm trying to make
> a connection with
> myodbc (latest release) as localhost I got the
> following message:
> "Client does not support authentication protocol
> requestet by server.
> Consider upgrading mysql client". 
> 
> MyODBC whorks fine because I 've allready make
> connection throw lan on a
> Linux RH-9 with apache and mysql 4.0.22
> 
> Any suggestions?
> Thanky you
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
>
http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
>
http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 




__ 
Do you Yahoo!? 
The all-new My Yahoo! - Get yours free! 
http://my.yahoo.com 
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Cannot create Windows service for MySql. Error: 0

[Eugenia Mariani]

My O.S in Win Xp Pro SP2.
My web server is Apache 5.0
While I am trying to install MySql Server 4.1, I have the following error 
and I cannot install the Server:

Cannot create Windows service for MySql. Error: 0
Can someone help me to install without error?
Thanks
Eugenia Mariani
_
Ricerche online più semplici e veloci con MSN Toolbar! 
http://toolbar.msn.it/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Index fill factor

[Homam S.A.]

What's the default fill factor on MyISAM B-Tree
indexes? I know it's 15/16 by default for InnoDB, but
nothing is mentioned about MyISAM.

For example, if I'm going to create read-only tables
and index them, I'd like the fill factor to be 100%
since there won't be updates and therefore I'm not
worried about page splits.

Is there a way to control it, the same way MS SQL
Server allows you to specify the fillfactor for
indexes and primary keys?

Thanks!

Homam




__ 
Do you Yahoo!? 
Yahoo! Mail - Easier than ever with enhanced search. Learn more.
http://info.mail.yahoo.com/mail_250

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

prova1

[Eugenia Mariani]

prova1
_
Personalizza MSN Messenger con sfondi e fotografie! 
http://www.ilovemessenger.msn.it/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

prova

[Eugenia Mariani]

prova
_
Scarica gratuitamente MSN Toolbar! http://toolbar.msn.it/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

key_buffer variable in my.cnf causes error...

[BD]

Hi,

I'm attempting to improve MySQL speed/performance by adding key_buffer=50M 
to my my.cnf file for [mysqld].

Upon stopping and then restarting MySQL,
I'm getting a "0 DB_ERROR_NOT_CONNECTED" error when visiting
my php-MySQL web application via http.  When I delete the server variable
in my.cnf and then restart MySQL, the application works.

Can anyone tell me why I am getting this error?  

Thanks for any info,

DB


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysql view implementation

[Prashant Pai]

Hi there,
Does anyone know how views are implemented in MySQL? Is the view query 
executed each time the view is interacted with (ie. query modification) or 
is there some sort of caching involved (i.e. view materialization).

thanks
_
All the news that matters. Just the way you like it. 
http://www.msn.co.in/News/ Only at MSN News!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Retrieving partial field values

[Kentucky Families]

Thanks to everyone who has responded to this. I still don't have an answer that 
will work, so here is a bit more information:
 
1) The name fields are divided into 4 parts:
 Prefix, GivenNames, Surname, Suffix
 
3) The GivenNames field already has more than one word in many instances. 
Sample entries are: 
 Mary Elizabeth
 J. W.
 Marg. Ellen
 
4) I need a way to show alternate spellings when these very old documents are 
difficult to read. Examples:
 
Example a (GivenNames): Mary Elizabeth or Marg Elizabeth
Example b (GivenNames): J. W. or I. W.
Example c (Surname): Stotts, Statts or Stutts
 
I need to be able to retrieve the following based upon the examples:
 
For Example a:
Return Mary Elizabeth where GivenNames begins with M;
Return Marg Elizabeth where GivenNames begins with M;
Return Mary Elizabeth where GivenNames contains the whole word Mary;
Return Marg Elizabeth where GivenNames contains the whole word Marg;
Return Mary Elizabeth where GivenNames=Mary Elizabeth
Return Marg Elizabeth where GivenNames=Marg Elizabeth
 
Example b:
Return J. W. where GivenNames begins with J;
Return I. W. where GivenNames begins with I.;
Return J. W. whre GivenNames=J. W.;
Return I. W. where GivenNames=I. W.
 
Example c:
Return statts where Surame=statts;
Return stotts where Surame=stotts;
Return stutts where Surname=stutts
 
This is a huge database so the option of using LIKE to bring up everything 
beginning with the search term will result in too many hits. I need a way to 
isolate these entries and search them on whole words.
 
I had considered using SET and creating a set of all unique surnames, but the 
surname count for the set right now is over 120,000, so this exceeds the 
maximums allowed for SET.
 
I've also considered using separate fields for 'surname alternate spelling 1', 
'surname alternate spelling 2', etc. Is this the only way I can get the results 
I need?
 
Thanks!


Ian Grant <[EMAIL PROTECTED]> wrote:
On Wed, 05 Jan 2005 12:22:18 +
Stephen Moretti wrote:

> Kentucky Families wrote:
> 
> > ... If I use a VARCHAR or TINYEXT field to enter these values and
> >I want to be able to retrieve all records where the surname field
> >contains the whole word Stotts, how would I enter these values:
> > 
> >stotts or statts or stutts
> >stotts,statts,stutts
> >other?
>
> You need to do an IN query.
> 
> SELECT column,list,here
> FROM tblBMD
> WHERE Surname IN ('stotts','statts','stutts')

This will not match any of the records with multiple transcriptions. 
I think you have mis-understood the question. As I understand it she
asks how to enter multiple possible transcriptions into the field so
that they can be retrieved easily.

My answer is: since commas are unlikely in names, that is as good a
separator as any. To do the query use wildcard matching with LIKE e.g.

SELECT * FROM table WHERE surname LIKE '%stotts%';

will match a field 'Stotts,Statts,Stutts' or just 'Stotts' but note it
will also match e.g. 'Stottsford,Stattsford,Stuttsford'


-
Do you Yahoo!?
 Yahoo! Mail - Find what you need with new enhanced search. Learn more.

Re: Trouble w/ mysqldump

[Hurrican19]

Right, three is a true dump file, and I can restore the database, everything 
'seems' to be there, I can browse the table all day long, and I can see the 
binary files in the database, but the length of the tables are different.

mysql@lists.mysql.com
In a message dated 1/5/2005 3:50:39 AM Eastern Standard Time, Gleb Paharenko 
<[EMAIL PROTECTED]> writes:

>Hello.
>
>> The actual tables are there 
>
>I assume that 'there' is a dump file. One more question, can you
>select the Avatars data using SQL queries? For example:
>  SELECT 'Avatar_field' from 'avatar_table' limit 1;
>Does your application work after upgrade?
>
>
>[EMAIL PROTECTED] wrote:
>> Thanks for the reply.
>> My original install was w/ an RPM from the Redhat Network, I uninstalled all 
>> RPM's associated w/ MySQL, Apache, and PHP and downloaded the latest of 
>> everything and compiled it. After I did an updatedb on my box, I searched 
>> for "mysqldump" and "mysql" to confirm it was gone -- nothing was found.   
>> 
>> ./mysqldump Ver 10.9 Distrib 4.1.8a, for redhat-linux-gnu (i686)
>> 
>> The actual tables are there, but the images (or any attachments) are really 
>> messed up.  Looks like something from the 60's, lol.
>> 
>> In a message dated 1/4/2005 9:36:01 AM Eastern Standard Time, Gleb Paharenko 
>> <[EMAIL PROTECTED]> writes:
>> 
>>>Hello.
>>>
>>>In what way have you upgraded? How do you dump the data? Is everything fine,
>>>execept the database dump? As of MySQL 4.1, --opt command line option is on 
>>>by 
>>>default, so it can produce a smaller output. The definions of tables
>>>which stores Avatars are also absent in dumps? May be you use mysqldump
>>>from old 3.23 version?
>>>
>>>
>>>[EMAIL PROTECTED] wrote:
 Hi All,
 I run a website, and am having a problem w/ corrupt databases..
 
 I was runnig MySQL v3.23 previously, and my db dumps worked fine .. 
 However, I have since upgraded to v4.1.8a-log and I am having problems 
 getting completed (or non corrupt) backups.. The problem is pretty big..
 
 My Original Backup of the DB using 3.23 was 208MB --- When I upgraded to 
 4.18 the backup is only 138MB ... My users Avatar's, all attachments, etc 
 are totally whacked out. Does anyone have any insight on what is causing 
 this, and how to fix it? Any help is appreciated.. I need a new good 
 backup desperately 
 
>>>
>>>
>>>
>
>-- 
>For technical support contracts, goto https://order.mysql.com/?ref=ensita
>This email is sponsored by Ensita.NET http://www.ensita.net/
>   __  ___     ___   __
>  /  |/  /_ __/ __/ __ \/ /    Gleb Paharenko
> / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
>/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
>       <___/   www.mysql.com
>
>
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Retrieving partial field values

[SGreen]

Stephen Moretti <[EMAIL PROTECTED]> wrote on 01/05/2005 07:22:18 AM:

> Kentucky Families wrote:
> 
> >Very new to MySQL and this list. Thanks in advance for your 
> patience with elementary questions. I'm in process of converting a 
> current web site to a mysql database. As this database will 
> eventually contain millions of records, I want to set it up properly.
> > 
> >The database will contain transcriptions of birth, marriage and 
> death records. For surname fields, I would like for my transcribers 
> to be able to enter multiple values for records that are difficult 
> to read. For instance, a surname that could be read Stotts or Statts
> or Stutts. If I use a VARCHAR or TINYEXT field to enter these values
> and I want to be able to retrieve all records where the surname 
> field contains the whole word Stotts, how would I enter these values:
> > 
> >stotts or statts or stutts
> >stotts,statts,stutts
> >other?
> > 
> >
> You need to do an IN query.
> 
> SELECT column,list,here
> FROM tblBMD
> WHERE Surname IN ('stotts','statts','stutts')
> 
> http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html#IDX1235
> 
> Regards
> 
> Stephen
> 

Stephen, 

I don't think you understood the question.  She wants to allow for 
multiple values within a single field, not to check several rows of a 
single-value field.  She explained why very well.

I can't make a solid recommendation but I would assume that a full-text 
index on your surname field is what you are after. If you separated each 
name possibility with a simple space, the FT indexer would pick up on the 
word breaks and index each name individually (assuming that the name is 
not on the "stop words" list and that it is at least 4 characters long). 

An only slightly more complex (but more normalized) design would use a 
separate table of surnames. Each surname has its own ID. Actually, you 
would need 2 new tables: a surnames table and a table to associate a 
surname to a record. 

The surnames table could look like:

CREATE TABLE surname (
id int auto_increment
, name varchar(150) not null
, PRIMARY KEY (ID)
, UNIQUE(name)
)

and the table that would link your surnames list to the "record" table 
(birth record, death record, etc.):

CREATE TABLE record_surname (
record_id int not null
, surname_id int not null
, confidence float
, PRIMARY KEY(record_id, surname_id)
)

This not only gives you the ability to store each surname individually, it 
helps with _partial_ name matches(!!), and it gives you the ability to 
assign a confidence level to what the guess is. For your example if a name 
looks like stotts, statts, or stutts the transcriptionist could weigh one 
as being more likely than the others. What you use to represent those 
levels are up to you (percents, scale 1-to-10, simple order,... it doesn't 
matter how you rank them but this lets you do it.)

It would be very fast to do a surname search and if not found, stop there. 
If you do find a surname (or list of surnames that match a pattern) you 
could easily take those ids and query the other tables to retrieve 
whatever other details you may have.

You may consider doing the same thing with "given names" (first names) as 
they could be just as hard to decipher from the older records. That way if 
you have Jeb or Job as a first name and Statt or Stott as a last name you 
could store all 4 combinations in a fairly compact and very searchable 
way.

Just a suggestion,

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Retrieving partial field values

[Stephen Moretti]

Ian Grant wrote:
On Wed, 05 Jan 2005 12:22:18 +
Stephen Moretti <[EMAIL PROTECTED]> wrote:
 

Kentucky Families wrote:
   

... If I use a VARCHAR or TINYEXT field to enter these values and
I want to be able to retrieve all records where the surname field
contains the whole word Stotts, how would I enter these values:
stotts or statts or stutts
stotts,statts,stutts
other?
 

You need to do an IN query.
SELECT column,list,here
FROM tblBMD
WHERE Surname IN ('stotts','statts','stutts')
   

This will not match any of the records with multiple transcriptions. 
I think you have mis-understood the question. As I understand it she
asks how to enter multiple possible transcriptions into the field so
that they can be retrieved easily.

My answer is: since commas are unlikely in names, that is as good a
separator as any. To do the query use wildcard matching with LIKE e.g.
SELECT * FROM table WHERE surname LIKE '%stotts%';
will match a field 'Stotts,Statts,Stutts' or just 'Stotts' but note it
will also match e.g. 'Stottsford,Stattsford,Stuttsford'
 

Ah see what you mean.
I really would strongly recommend against storing more than one surname 
in a field, no matter how you decide to delimit them.

It should be noted that BMDs are generally consider legal notices. All 
efforts should be made to ensure that the data given to the transcribers 
is clear and correct, so that the correct data is entered first time.

If you want to do a "similar" look up, then you will need a couple of 
look up tables that allow a user to look up a surname and get back a 
list of similar surnames (this would be transparent to the user), which 
can then be used to query the BMDs.

Stephen

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB: Error: tablespace size stored in header is 17024 pages, but

[Ian Grant]

Dear Heikki

Many thanks for your reply.

On Mon, 3 Jan 2005 20:41:22 +0200
"Heikki Tuuri" <[EMAIL PROTECTED]> wrote:

> are you sure that you copied the complete ibdata1 file to the new
> place? It is strange how 7 MB can be missing from the file end.

I have just checked the tar file I used to do the transfer and sure
enough, the file length is right there, but not in my database
directory. Obviously I didn't notice any error when I unpacked it, but
it is definitely short now. So that (partly) explains what went wrong
... however we have new data in the database now so it is too late to go
back tho that copy.

> What does the old .err log contain? Any message about disk space
> running out?
> 
> The error below probably has not corrupted your tablespace. Best to
> run CHECK TABLE on some of your tables, though.

I did that on all tables and they all show they are OK.

> When ibdata files are created, they are initially written full of
> zeros. You can probably fix the error by using another mysqld
> installation (or any method that makes a 7 MB zero-filled file) to
> create a new ibdata2 file that is 7 MB in size, and add that to the
> tablespace. Look from
> http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html
> how to edit my.cnf then.

Thanks. This is what I did:

First I created ibdata2 with the command 

  dd if=/dev/zero bs=1M count=7 of=ibdata2

Then I added it to the start of the tablespace path in my.cnf by
changing

  innodb_data_file_path = ibdata1:10M:autoextend

to

  innodb_data_file_path = ibdata2:7M;ibdata1:10M:autoextend

Then I shut down:

050105 14:25:57 [Note] /local/own-1/rt/rt-3.2.2/libexec/mysqld: Normal
shutdown
050105 14:25:57  InnoDB: Starting shutdown...
050105 14:26:01  InnoDB: Shutdown completed; log sequence number 0
407778847
050105 14:26:01 [Note] /local/own-1/rt/rt-3.2.2/libexec/mysqld: Shutdown
complete

050105 14:26:01  mysqld ended

 ... and restarted MySQL but I got an error:

050105 14:26:04  mysqld started
050105 14:26:05  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
050105 14:26:05  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 407778847.
InnoDB: Doing recovery: scanned up to log sequence number 0 407778847
InnoDB: Page directory corruption: supremum not pointed to

followed by a page dump and a stack trace. I removed the entry for the
new file in the data file path and it has started as it did before. Have
I misunderstood your instructions?

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

FLUSH syntax query

[Karam Chand]

Hello,

>From the MySQL docs:

http://dev.mysql.com/doc/mysql/en/FLUSH.html

What does FLUSH MASTER and FLUSH SLAVES does? Its not
documented?

Also, from which version of MySQL LOCAL and
NO_WRITE_TO_BINLOG options supported?

Regards,
Karam

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Retrieving partial field values

[Ian Grant]

On Wed, 05 Jan 2005 12:22:18 +
Stephen Moretti <[EMAIL PROTECTED]> wrote:

> Kentucky Families wrote:
> 
> > ... If I use a VARCHAR or TINYEXT field to enter these values and
> >I want to be able to retrieve all records where the surname field
> >contains the whole word Stotts, how would I enter these values:
> > 
> >stotts or statts or stutts
> >stotts,statts,stutts
> >other?
>
> You need to do an IN query.
> 
> SELECT column,list,here
> FROM tblBMD
> WHERE Surname IN ('stotts','statts','stutts')

This will not match any of the records with multiple transcriptions. 
I think you have mis-understood the question. As I understand it she
asks how to enter multiple possible transcriptions into the field so
that they can be retrieved easily.

My answer is: since commas are unlikely in names, that is as good a
separator as any. To do the query use wildcard matching with LIKE e.g.

SELECT * FROM table WHERE surname LIKE '%stotts%';

will match a field 'Stotts,Statts,Stutts' or just 'Stotts' but note it
will also match e.g. 'Stottsford,Stattsford,Stuttsford'

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Max connections being used every 10-12 day.

[Fredrik Carlsson]

Hi,
My original query was a UNION query :) and that one is really fast.  
The  problem i had was that  every  8-12 day  mysql  sad that all of my 
max_connections was in use. I think i will stick with my UNION query it 
seems faster.

// Fredrik.
Bill Easton wrote:
Fredrik,
I haven't read all of the earlier messages, but it looks like your problem
is that a query such as the following takes more than a minute to return 11
rows.
Select A.id, A.parent
 from art A inner join art B using (id)
 where A.id=560685 or B.parent=560685;
Why?  Well, your explain says that no key is used, but the entire table is
scanned (type='ALL').  In particular, MySQL is reading every record of A and
looking to see if either A.id=560685 or B.parent=560685.  Not good.
Your query is equivalent to the following:
Select id
 from art
 where id=560685 or parent=560685;
I'd be surprised if the simpler query weren't slightly faster than the
original--MySQL has more work to do on the original.  It appears that, with
the more complex query, you are trying to fool MySQL into using indexes for
both parts of the OR.  It didn't work.
In an earlier mail, your explain had type='index' and key='id_3'.  In that
case, you evidently had an index, id_3, that contained both id and parent.
So, MySQL could get all of the information form the id_3 index; therefore,
it read the index instead of the table.  It still read the entire index,
looking to see if either A.id=560685 or B.parent=560685.  Better than
reading the full table, but still not good.
What to do?
Well, you can get the information you want in TWO queries:
Select id
 from art
 where id=560685;
Select id
 from art
 where parent=560685;
In each of these, MySQL will surely use the appropriate index and return the
result in a few milliseconds.
You should be able to combine them into one query and get the same behavior:
Select id
 from art
 where id=560685
UNION ALL
Select id
 from art
 where parent=560685;
I'd be surprised if MySQL didn't do fine on this.  (You may have tried this
and failed--as I said, I didn't try to read all of the earlier mail.  But
I'd be astonished if it weren't fast, though I suppose MySQL might try to do
something fancy, knowing that the two queries are on the same table.  In any
event, check the two individual queries.  If necessary, use a temporary
table.)  Then, you get to add your ORDER BY clause; presumably, it will
still be fast.
There was some mention in earlier mail of joins being faster than unions.
That may be, but the difference should be too small to notice, unless
different indexes are used.  In your query above, with the inner join, MySQL
is going to first consider the join and then consider the use of indexes for
the where clause--so it ends up with the full table scan.
HTH,
Bill
 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Retrieving partial field values

[Stephen Moretti]

Kentucky Families wrote:
Very new to MySQL and this list. Thanks in advance for your patience with 
elementary questions. I'm in process of converting a current web site to a 
mysql database. As this database will eventually contain millions of records, I 
want to set it up properly.
The database will contain transcriptions of birth, marriage and death records. 
For surname fields, I would like for my transcribers to be able to enter 
multiple values for records that are difficult to read. For instance, a surname 
that could be read Stotts or Statts or Stutts. If I use a VARCHAR or TINYEXT 
field to enter these values and I want to be able to retrieve all records where 
the surname field contains the whole word Stotts, how would I enter these 
values:
stotts or statts or stutts
stotts,statts,stutts
other?
 

You need to do an IN query.
SELECT column,list,here
FROM tblBMD
WHERE Surname IN ('stotts','statts','stutts')
http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html#IDX1235
Regards
Stephen
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Retrieving partial field values

[Kentucky Families]

Very new to MySQL and this list. Thanks in advance for your patience with 
elementary questions. I'm in process of converting a current web site to a 
mysql database. As this database will eventually contain millions of records, I 
want to set it up properly.
 
The database will contain transcriptions of birth, marriage and death records. 
For surname fields, I would like for my transcribers to be able to enter 
multiple values for records that are difficult to read. For instance, a surname 
that could be read Stotts or Statts or Stutts. If I use a VARCHAR or TINYEXT 
field to enter these values and I want to be able to retrieve all records where 
the surname field contains the whole word Stotts, how would I enter these 
values:
 
stotts or statts or stutts
stotts,statts,stutts
other?
 
Thanks,
 
--Sheryl


-
Do you Yahoo!?
 Read only the mail you want - Yahoo! Mail SpamGuard.

Re: Max connections being used every 10-12 day.

[Bill Easton]

Fredrik,

I haven't read all of the earlier messages, but it looks like your problem
is that a query such as the following takes more than a minute to return 11
rows.

Select A.id, A.parent
  from art A inner join art B using (id)
  where A.id=560685 or B.parent=560685;

Why?  Well, your explain says that no key is used, but the entire table is
scanned (type='ALL').  In particular, MySQL is reading every record of A and
looking to see if either A.id=560685 or B.parent=560685.  Not good.

Your query is equivalent to the following:
Select id
  from art
  where id=560685 or parent=560685;

I'd be surprised if the simpler query weren't slightly faster than the
original--MySQL has more work to do on the original.  It appears that, with
the more complex query, you are trying to fool MySQL into using indexes for
both parts of the OR.  It didn't work.

In an earlier mail, your explain had type='index' and key='id_3'.  In that
case, you evidently had an index, id_3, that contained both id and parent.
So, MySQL could get all of the information form the id_3 index; therefore,
it read the index instead of the table.  It still read the entire index,
looking to see if either A.id=560685 or B.parent=560685.  Better than
reading the full table, but still not good.

What to do?

Well, you can get the information you want in TWO queries:

Select id
  from art
  where id=560685;

Select id
  from art
  where parent=560685;

In each of these, MySQL will surely use the appropriate index and return the
result in a few milliseconds.

You should be able to combine them into one query and get the same behavior:

Select id
  from art
  where id=560685
UNION ALL
Select id
  from art
  where parent=560685;

I'd be surprised if MySQL didn't do fine on this.  (You may have tried this
and failed--as I said, I didn't try to read all of the earlier mail.  But
I'd be astonished if it weren't fast, though I suppose MySQL might try to do
something fancy, knowing that the two queries are on the same table.  In any
event, check the two individual queries.  If necessary, use a temporary
table.)  Then, you get to add your ORDER BY clause; presumably, it will
still be fast.

There was some mention in earlier mail of joins being faster than unions.
That may be, but the difference should be too small to notice, unless
different indexes are used.  In your query above, with the inner join, MySQL
is going to first consider the join and then consider the use of indexes for
the where clause--so it ends up with the full table scan.

HTH,

Bill


= original message follows =

From: Fredrik Carlsson <[EMAIL PROTECTED]>
To: Donny Simonton <[EMAIL PROTECTED]>
CC:  mysql@lists.mysql.com
Subject: Re: Max connections being used every 10-12 day.

I really appreciate your help :)

I did some cleanup of my indexes(there are a couple of them left to
clean out but it takes so long time):

mysql> show index from
art;
+++--+--+-+-
--+-+--++--++-+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+++--+--+-+-
--+-+--++--++-+
| art |  0 | PRIMARY  |1 | id  | A
|  542437 | NULL | NULL   |  | BTREE  | |
| art |  1 | date |1 | date| A
|  542437 | NULL | NULL   | YES  | BTREE  | |
| art |  1 | lst |1 | lst| A
| 216 | NULL | NULL   | YES  | BTREE  | |
| art |  1 | batch|1 | batch   | A
| 183 | NULL | NULL   | YES  | BTREE  | |
| art |  1 | batch|2 | lst| A
|1802 | NULL | NULL   | YES  | BTREE  | |
| art |  1 | lst_3   |1 | lst| A
| 216 | NULL | NULL   | YES  | BTREE  | |
| art |  1 | lst_3   |2 | parent  | A
|   90406 | NULL | NULL   | YES  | BTREE  | |
| art |  1 | lst_3   |3 | batch   | A
|   90406 | NULL | NULL   | YES  | BTREE  | |
| art |  1 | parent   |1 | parent  | A
|   90406 | NULL | NULL   | YES  | BTREE  | |
| art |  1 | mid|1 | mid   | A
|  542437 | NULL | NULL   | YES  | BTREE  | |
| art |  1 | date_2   |1 | date| A
|  542437 | NULL | NULL   | YES  | BTREE  | |
| art |  1 | subc  |1 | subc | A |
54243 | NULL | NULL   | YES  | FULLTEXT   | |
| art |  1 | mf|1 | mf   | A |
54243 | NULL | NULL   | YES  | FULLTEXT   | 

Re: Load data infile...

[Gleb Paharenko]

Hello.



Similar problems are described at:

  http://dev.mysql.com/doc/mysql/en/Connection_access.html





Richard Whitney <[EMAIL PROTECTED]> wrote:

> I think I'm bringing this up again but with different errors.

> 

> This:   $sql = "LOAD DATA INFILE '$file' REPLACE INTO TABLE 
> `jobs` FIELDS

> TERMINATED BY '\\t' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '' LINES

> TERMINATED BY '\\r\\n'";

> 

> is getting the error "Access denied for user: '[EMAIL PROTECTED]' (Using 
> password: YES"

> 

> I have no idea where the % sign is coming from. shouldn't it say [EMAIL 
> PROTECTED]

> like it's setup in the db connection script?

> 

> Any clues?  Thanks!

> 

> R. Whitney

> Transcend Development

> "Producing the next phase of your internet presence"

> http://xend.net

> Premium Quality Web Hosting

> http://xendhosting.com

> rw AT xend.net

> Net Binder  http://netbinder.net

> 310-943-6498

> 602-288-5340

> 

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Cursors are not available in MySQL 5.0

[Gleb Paharenko]

Hello.



Basic support for read-only server side cursors was added in 5.x and released 
in alpha snapshot 5.0.2. See:

  http://dev.mysql.com/doc/mysql/en/News-5.0.2.html



  >I downloaded and installed MySQL 5.0.  But I could not run declare cursor 
features in 5.0.

  >Is the cursor implementation present in 5.0 ?



"Shyam Sarkar" <[EMAIL PROTECTED]> wrote:



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Embedded Quotes and Special Characters.

[Gleb Paharenko]

Hello.



See:

  http://dev.mysql.com/doc/mysql/en/Charset.html



Andrew Mull <[EMAIL PROTECTED]> wrote:

> That is what I was wondering, but not sure how to check/set the proper 

> character set.

> 

> Thanks.

> 

> -Andy

> 

> Quoting Gleb Paharenko <[EMAIL PROTECTED]>:

> 

>> Hello.

>> 

>> You can write a script which will walk through all tables and

>> update the rows using replace() function. But, I think, you should

>> find the source of the problem. What odd characters have appeared after

>> importing? If they are just unreadable, may be the clue is in wrong 

> character

>> set.

>> 

>> 

>> 

>> Andrew Mull <[EMAIL PROTECTED]> wrote:

>> > I'm moving a database from one server to another.  Both are unix based, 

>> > however the original database is on a Solaris box, and the new server is 

>> > RedHat.

>> > 

>> > The database has embedded "s and 's in some of the varchar fields.  When I

>> 

>> > moved the database, I did a mysqldump, copied the text file to the new

>> server, 

>> > and imported the data.  The database on the new server is now showing odd 

>> > characters in place of the original "s and 's.  

>> > 

>> > Is there a way to fix this problem?

>> > 

>> > Thanks!

>> > -Andy

>> > 

>> 

>> 

>> 

-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Trouble w/ mysqldump

[Gleb Paharenko]

Hello.



> The actual tables are there 



I assume that 'there' is a dump file. One more question, can you

select the Avatars data using SQL queries? For example:

  SELECT 'Avatar_field' from 'avatar_table' limit 1;

Does your application work after upgrade?





[EMAIL PROTECTED] wrote:

> Thanks for the reply.

> My original install was w/ an RPM from the Redhat Network, I uninstalled all 
> RPM's associated w/ MySQL, Apache, and PHP and downloaded the latest of 
> everything and compiled it. After I did an updatedb on my box, I searched for 
> "mysqldump" and "mysql" to confirm it was gone -- nothing was found.   

> 

> ./mysqldump Ver 10.9 Distrib 4.1.8a, for redhat-linux-gnu (i686)

> 

> The actual tables are there, but the images (or any attachments) are really 
> messed up.  Looks like something from the 60's, lol.

> 

> In a message dated 1/4/2005 9:36:01 AM Eastern Standard Time, Gleb Paharenko 
> <[EMAIL PROTECTED]> writes:

> 

>>Hello.

>>

>>In what way have you upgraded? How do you dump the data? Is everything fine,

>>execept the database dump? As of MySQL 4.1, --opt command line option is on 
>>by 

>>default, so it can produce a smaller output. The definions of tables

>>which stores Avatars are also absent in dumps? May be you use mysqldump

>>from old 3.23 version?

>>

>>

>>[EMAIL PROTECTED] wrote:

>>> Hi All,

>>> I run a website, and am having a problem w/ corrupt databases..

>>> 

>>> I was runnig MySQL v3.23 previously, and my db dumps worked fine .. 
>>> However, I have since upgraded to v4.1.8a-log and I am having problems 
>>> getting completed (or non corrupt) backups.. The problem is pretty big..

>>> 

>>> My Original Backup of the DB using 3.23 was 208MB --- When I upgraded to 
>>> 4.18 the backup is only 138MB ... My users Avatar's, all attachments, etc 
>>> are totally whacked out. Does anyone have any insight on what is causing 
>>> this, and how to fix it? Any help is appreciated.. I need a new good backup 
>>> desperately 

>>> 

>>

>>

>>

-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

ANN: Advanced Data Generator 1.2.6 released

[Martijn Tonies]

Dear ladies and gentlemen,

Upscene Productions is happy to announce a new version of
the database developer tool:
"Advanced Data Generator" (version 1.2.6)

A fast test-data generator tool that comes with a library
of real-life data, many filling options, presets and more.

This new release consists of four versions:

- Pro: ADO and ODBC connectivity
- InterBase Edition
- Firebird Edition
- MySQL Edition


More info and a 30-day trial version on www.upscene.com

Pricing information available on www.upscene.com/purchase.htm#adg


Recent changes:

V1.2.6
---
- Fixed: MS SQL returned errors "application uses the wrong type
 for this parameter" when filling multiple tables with
 same column names, but different datatypes
- Fixed: Oracle could return error "Invalid descriptor index" when
 using "referential filling"
- Enhancement: misc small GUI improvements
- Enhancement: enter values dialog with load/save/sort for list-values
- Enhancement: (Pro) increased timeout for ADO commands
- Enhancement: (MySQL) set/enum recognized as "string" datatype





With regards,

Martijn Tonies
Upscene Productions - Database Tools for Developers
http://www.upscene.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: MyODBC 3.5.9 and MySQL 4.1.8

[Mechain Marc]

Have a look at:

http://dev.mysql.com/doc/mysql/en/Old_client.html

Marc.

-Message d'origine-
De : nikos [mailto:[EMAIL PROTECTED]
Envoyé : mercredi 5 janvier 2005 10:09
À : mysql@lists.mysql.com
Objet : MyODBC 3.5.9 and MySQL 4.1.8



Hello list and happy new year.

Recently I've install mysql 4.1 on win 2000 with IIS 5 and works
perfect. My problem is that when i'm trying to make a connection with
myodbc (latest release) as localhost I got the following message:
"Client does not support authentication protocol requestet by server.
Consider upgrading mysql client". 

MyODBC whorks fine because I 've allready make connection throw lan on a
Linux RH-9 with apache and mysql 4.0.22

Any suggestions?
Thanky you


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MyODBC 3.5.9 and MySQL 4.1.8

[nikos]

Hello list and happy new year.

Recently I've install mysql 4.1 on win 2000 with IIS 5 and works
perfect. My problem is that when i'm trying to make a connection with
myodbc (latest release) as localhost I got the following message:
"Client does not support authentication protocol requestet by server.
Consider upgrading mysql client". 

MyODBC whorks fine because I 've allready make connection throw lan on a
Linux RH-9 with apache and mysql 4.0.22

Any suggestions?
Thanky you


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Load data infile...

[Tom Molesworth]

Richard Whitney  wrote on Tuesday, January 04, 2005 
6:16
PM:

> I think I'm bringing this up again but with different errors.
> 
> This: $sql = "LOAD DATA INFILE '$file'
> REPLACE INTO TABLE `jobs` FIELDS
> TERMINATED BY '\\t' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY ''
> LINES TERMINATED BY '\\r\\n'";
> 
> is getting the error "Access denied for user: '[EMAIL PROTECTED]' (Using
> password: YES" 
> 
> I have no idea where the % sign is coming from. shouldn't it say
> [EMAIL PROTECTED] like it's setup in the db connection script?

MySQL treats connections to "localhost" differently to connections to the
real hostname or IP address - the problem is likely in the mysql_connect or
pconnect lines (if you're using php mysql module). Try adding a new user to
the grant tables with duplicate permissions, but with host set to "%" (or
localhost if the existing user has host set to % already).

cheers,

Tom

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

???: Symbol `sql_protocol_typelib' has different size in shared object, consider re-linking

[Jacob Friis]

What should I do about this?
/etc/cron.daily/logrotate:
/usr/bin/mysqladmin: Symbol `sql_protocol_typelib' has different size in 
shared object, consider re-linking
/etc/cron.daily/mysql-server:
mysqladmin: Symbol `sql_protocol_typelib' has different size in shared 
object, consider re-linking
mysql: Symbol `sql_protocol_typelib' has different size in shared 
object, consider re-linking

Thanks,
Jacob
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Slow ORDER BY in MySQL v4.1.8

[Jigal van Hemert]

From: "Kelvin Wu"
> 1, If I created table 'newtest' using InnoDB type, inserting 50k
> records took me 20min! If I use MyISAM type, inserting took me less
> then 1 min. I don't know much about InnoDB but how come the
> performance is dropped down so much?
Do read everything in the MySQL manual and on the InnoDB website about the
InnoDB engine. There are many differences between MyISAM and InnoDB; a few:
- MyISAM supports FULL TEXT indexes, InnoDB doesn't
- InnoDB supports transactions, MyISAM doesn't
- MyISAM excels in situations where there are either many updates/inserts
and few selects, or many selects and few inserts/updates ("low concurrency")
- InnoDB excels in situations where there are many updates/inserts and many
selects ("high concurrency")

Inserting 50k records is something MyISAM is very comfortable with. In some
cases it is faster to drop the indexes, insert the records and recreate the
indexes (especially with full text indexes).

We use InnoDB in a situation where there are lots of inserts, updates and
selects. Tests we've done show that MyISAM is a lot faster when there are
very few records (< 50k), but the average execution time for a query
increases almost lineary with the number of records, while InnoDB shows
almost constant query execution times for very small and large tables.

> 2, Even I choose MyISAM type, the same perl script has another huge
> performance difference on 'mytest' which is copied from MySQL 3 and
> 'newtest' which is created under MySQL 4.
>
> query is simple too, something like:
> SELECT id, sessionid, timestamp FROM newtest ORDER BY sessionid DESC
> LIMIT 0, 100;
>
> If I didn't use ORDER BY, or use ORDER BY primary key id, performance
> is same on these two tables, if I use ORDER BY (sessionid or
> timestamp), 'mytest' is 10 times faster than 'newtest'.
>
> Did I do anything wrong? Anyone can help on this?

A lot of things could have happened. For test purposes use SELECT
SQL_NO_CACHE id, ses. to disable the query cache in MySQL 4+. If you
altered the newtest table the query cache is empty, while mytest can get the
results from the cache straight away (hugh performance difference).

An order by on timestamp will use a full table scan and is thus rather slow
(all records must be read to get the result).

Is the output from EXPLAIN SELECT id.. different for both tables? It
should show you which decisions MySQL made while analysing the query.

10 times seems to be a lot, but what kind of execution times are we talking
about here? 0.0005 vs. 0.005 or 2 vs. 20 seconds?

Regards, Jigal.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]