[OT] For those who love fur, please take a look

[Koon Yue Lam]

** Be prepared, rate R **
http://hk.geocities.com/blood1213/fur.html

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

RE: What is the max length of IN() function?

[Donny Simonton]

Actually, I've done a test with this in the past, we could not find a limit.
But there is a magic number where the optimizer stops doing a good job of
optimizing the query and it starts to get really slow.

In our case we were using words, and phrases, so we would have something
like:

IN ('a', 'apple', 'apple car', 'car', 'c')  etc...

We found that once it hits about 200 or so entries the query went from 0.00
seconds to about 2-3 seconds.  Sometimes much more.

Donny

> -Original Message-
> From: Tom Crimmins [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, February 16, 2005 9:07 PM
> To: Daevid Vincent
> Cc: mysql@lists.mysql.com
> Subject: RE: What is the max length of IN() function?
> 
> 
> > -Original Message-
> > From: Daevid Vincent
> > Sent: Wednesday, February 16, 2005 20:59
> > To: mysql@lists.mysql.com
> > Subject: What is the max length of IN() function?
> >
> > I tried to find this function on the dev.mysql.com site, but good luck
> > finding "in"... ;-)
> >
> > Can someone tell me what the maximum length is for this function?
> >
> > SELECT * FROM foo WHERE bar IN(1,2,3,4,. N);
> >
> > How many entries can there be in between 1 and N ? Hundreds?
> > Thousands?
> > Millions?
> 
> From http://dev.mysql.com/doc/mysql/en/comparison-operators.html, "The
> number of values in the IN list is only limited by the max_allowed_packet
> value."
> 
> ---
> Tom Crimmins
> Interface Specialist
> Pottawattamie County, Iowa
> 
> --
> 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: Using Different Database Groups On the Same Computer

[Hal Vaughan]

On Wednesday 16 February 2005 08:52 pm, Hal Vaughan wrote:
> I'm not quite sure what search terms to use, otherwise I'm sure I could
> find this on Google.
>
> I have a working install of MySQL on an older computer, running the current
> version of my program.  I am about to start developing a newer version of
> my program on a new computer.  I'd like to have both versions (current and
> new) on the same computer, however, that will cause a LOT of problems in
> interference between the current and development versions of my program.
>
> Is there any way to separate these, so I can run mysql one way to access
> the databases for the current version, and run it another way to access the
> newer databases for the development version (and yes, many databases in the
> development version will have the same name as those in the current
> version).
>
> For example, if I ran a command "mysql", I'd get these tables:
>
> CaseData
> Clients
> GeneralSetup
>
> And if I ran an alias, like, say, "new-mysql", I'd get these tables:
>
> CaseData  (same name, different table)
> Clients  (same name, different table)
> GeneralSetup  (same name, different table)
> DataFilters
>
> I am running MySQL on Linux, if that makes a difference.
>
> Hal

I've tried this by running 2 instances of mysqld, the first with no arguments, 
and the second like this:

mysqld --port=3307 --datadir=/dbtest/mysql

I have to run mysqld directly -- not through safe_mysqld 
(which /etc/init.d/mysql calls).  If I run it through safe_mysqld, I can run 
only one instance at a time, it will exit without running a new instance if 
it detects one already running.

In all test cases, I've always run the plain version first, and the one using 
the 2nd port and new data dir last.  No matter what I do, it always uses the 
new data dir, whether I try "mysql" or "mysql --port=3307".

Any ideas?

The goal is to have 2 instances of MySQL running on the same computer 
(preferably without needing 2 different versions or 2 different installs), 
with each instance using different data directories (and, of course, to reach 
the separate instances, each listens on a different port).

Thanks for any help.

Hal

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

Re: Using Different Database Groups On the Same Computer

[Hal Vaughan]

On Wednesday 16 February 2005 09:48 pm, Logan, David (SST - Adelaide) wrote:
> You could start a second copy of the same server the way that I
> described below. Just start the second copy listening on a different
> port. You could use command line overrides to start it up with a
> different my.cnf file with the changed parameters. eg. different
> datadir, different port etc. This way you don't have to install a second
> newer version but can run with the old one.
>
> instance 1 starts with /etc/my.cnf (default)
> instance 2 starts with /etc/my.cnf.dev and listens on port 3307 with a
> datadir of /some/where/else/on/the/disk
>
> See chapter 4 of the manual
> http://dev.mysql.com/doc/mysql/en/using-mysql-programs.html before I
> give you a bum steer 8-) that has all the docs on how to set up the
> correct option files you will require

Since you gave me this, I was able to find most of what I needed with mysqld 
--help.  I knew I could set the port, but I still wanted to be sure I could 
have two instances (that's the word I couldn't remember when I was trying to 
figure out what to Google for) running, using different data sources without 
them interfering with each other.

From what I see, I can take /etc/init.d/mysql and copy it 
to /etc/init.d/mysql2.  I can modify the 2nd version to specify a different 
port and a different config file (which may not be needed if I specify the 
different data directory) on the command line.

I'll try this tomorrow, when I'm more alert.  I just didn't want to poke 
around with something like that and find out later that I was doing something 
that had effects I wouldn't see until it was too late.

Thank you for all the info.  This is huge help to me (it lets me get rid of 2 
old boxen that'll go to my parents and another relative and move everything 
onto one system -- at long last!).

Hal


> 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: Hal Vaughan [mailto:[EMAIL PROTECTED]
> Sent: Thursday, 17 February 2005 1:10 PM
> To: mysql@lists.mysql.com
> Subject: Re: Using Different Database Groups On the Same Computer
>
> On Wednesday 16 February 2005 09:27 pm, Logan, David (SST - Adelaide)
>
> wrote:
> > Ooops, sorry for the second time, I forgot to include the list.
> >
> > Yes, you can install the "normal" version of mysql (in my case 4.0.22)
> > and leave everything default. You can then install a new version of
> > mysql (4.1.3 or something) and then set up the my.cnf so that it
>
> listens
>
> > on a different tcp port. MySQL uses port 3306 by default but you can
> > change this via the my.cnf. I also set up a different startup file in
> > the rc3.d directory that starts the second server using different
> > parameters passed via the command line.
>
> Okay -- I follow most of this.  Just one other question: Is there a way
> to set
> this up with only ONE install of MySQL?  (I'm running a Debian system
> and,
> for the sake of updates and stuff, I'm trying to put as little as
> possible on
> the system and keep it all so it can be easily updated through
> security.debian.org.)  In other words, could I make it so if I access it
>
> through 3306, it goes to the current version, but port 3307 makes it use
> the
> new databases?
>
> > The docs do quite a good job on describing this.
> >
> > You can then specify this in your DBI call to MySQL like
> >
> > my $dsn =
> > "DBI:mysql:host=$hostname;database=lcscreative;port=$newportnumber";
> > my $dbh = DBI->connect($dsn, 'username', 'password', \%err_handle);
> >
> > You could also use a unix socket rather than tcp by specifying the
> > socket name rather than a port number. This is documented in the DBI
> > docs at CPAN.
> >
> > You can do the same thing from PHP, eg. specifying a different port
> > number etc. You can do this from the command line by using mysql
> > --username username --password --port=3307
>
> --socket=somethingdifferent.
>
> > Just make sure that you specify 2 different data directories, 1 for
>
> the
>
> > production or normal version and 1 on a different filesystem or
> > whereever for the development version. It will not matter that the
> > databases have the same name as they are in different mysql instances.
>
> I figured I'd have to have different directories.  That also makes
> backing up
> data easier.  (I'm backing everything up with rsync, so if I specify
> directories that are in the same directory tree the rest of the programs
> and
> data are in, I can back up everything with one cron job instead of using
>
> mysqldump AND backing up the file I generate with it.)
>
> > I also use environment variables set in the apache config to ensure
> > separation. I have 2 servers that I use, one production and one
> > development. I have the following 2 statements in the virtual host
> > section for the website
>
> A

RE: What is the max length of IN() function?

[Tom Crimmins]

> -Original Message-
> From: Daevid Vincent
> Sent: Wednesday, February 16, 2005 20:59
> To: mysql@lists.mysql.com
> Subject: What is the max length of IN() function?
> 
> I tried to find this function on the dev.mysql.com site, but good luck
> finding "in"... ;-)
> 
> Can someone tell me what the maximum length is for this function?
> 
> SELECT * FROM foo WHERE bar IN(1,2,3,4,. N);
> 
> How many entries can there be in between 1 and N ? Hundreds? 
> Thousands?
> Millions?

>From http://dev.mysql.com/doc/mysql/en/comparison-operators.html, "The
number of values in the IN list is only limited by the max_allowed_packet
value."

---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa

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

What is the max length of IN() function?

[Daevid Vincent]

I tried to find this function on the dev.mysql.com site, but good luck
finding "in"... ;-)

Can someone tell me what the maximum length is for this function?

SELECT * FROM foo WHERE bar IN(1,2,3,4,. N);

How many entries can there be in between 1 and N ? Hundreds? Thousands?
Millions?


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

RE: Using Different Database Groups On the Same Computer

[Logan, David (SST - Adelaide)]

You could start a second copy of the same server the way that I
described below. Just start the second copy listening on a different
port. You could use command line overrides to start it up with a
different my.cnf file with the changed parameters. eg. different
datadir, different port etc. This way you don't have to install a second
newer version but can run with the old one.

instance 1 starts with /etc/my.cnf (default) 
instance 2 starts with /etc/my.cnf.dev and listens on port 3307 with a
datadir of /some/where/else/on/the/disk

See chapter 4 of the manual
http://dev.mysql.com/doc/mysql/en/using-mysql-programs.html before I
give you a bum steer 8-) that has all the docs on how to set up the
correct option files you will require

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: Hal Vaughan [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 17 February 2005 1:10 PM
To: mysql@lists.mysql.com
Subject: Re: Using Different Database Groups On the Same Computer

On Wednesday 16 February 2005 09:27 pm, Logan, David (SST - Adelaide)
wrote:
> Ooops, sorry for the second time, I forgot to include the list.
>
> Yes, you can install the "normal" version of mysql (in my case 4.0.22)
> and leave everything default. You can then install a new version of
> mysql (4.1.3 or something) and then set up the my.cnf so that it
listens
> on a different tcp port. MySQL uses port 3306 by default but you can
> change this via the my.cnf. I also set up a different startup file in
> the rc3.d directory that starts the second server using different
> parameters passed via the command line.

Okay -- I follow most of this.  Just one other question: Is there a way
to set 
this up with only ONE install of MySQL?  (I'm running a Debian system
and, 
for the sake of updates and stuff, I'm trying to put as little as
possible on 
the system and keep it all so it can be easily updated through 
security.debian.org.)  In other words, could I make it so if I access it

through 3306, it goes to the current version, but port 3307 makes it use
the 
new databases?

> The docs do quite a good job on describing this.
>
> You can then specify this in your DBI call to MySQL like
>
> my $dsn =
> "DBI:mysql:host=$hostname;database=lcscreative;port=$newportnumber";
> my $dbh = DBI->connect($dsn, 'username', 'password', \%err_handle);
>
> You could also use a unix socket rather than tcp by specifying the
> socket name rather than a port number. This is documented in the DBI
> docs at CPAN.
>
> You can do the same thing from PHP, eg. specifying a different port
> number etc. You can do this from the command line by using mysql
> --username username --password --port=3307
--socket=somethingdifferent.
>
> Just make sure that you specify 2 different data directories, 1 for
the
> production or normal version and 1 on a different filesystem or
> whereever for the development version. It will not matter that the
> databases have the same name as they are in different mysql instances.

I figured I'd have to have different directories.  That also makes
backing up 
data easier.  (I'm backing everything up with rsync, so if I specify 
directories that are in the same directory tree the rest of the programs
and 
data are in, I can back up everything with one cron job instead of using

mysqldump AND backing up the file I generate with it.)

> I also use environment variables set in the apache config to ensure
> separation. I have 2 servers that I use, one production and one
> development. I have the following 2 statements in the virtual host
> section for the website

At this point, fortunately, I won't have to worry about doing anything
through 
Apache.  I think I'll miss that entirely.

> httpd.config on development
>
> SetEnv DB_HOST devhost
> SetEnv DB_USER root
>
> httpd.config on production
>
> SetEnv DB_HOST prodhost
> SetEnv DB_USER prod_username
>
> and then access these through the %ENV hash in perl or getenv function
> in PHP. That makes it easy, all I have to do is transfer the scripts
> over and they pick up the correct names. You could do this on a single
> server by having virtual hosts and setting the variables to different
> values dependent upon the url called.

I'm using Perl for the web stuff (which is only for a setting editor).
I'm 
not a professional programmer.  I hadn't touched a line of code in over
a 
decade when I started this.  I discovered Perl quickly, and I've done as
much 
as possible in Perl so I can give myself a sense of unity.  (I had to do

another part in Java, so I'm trying to stick with only those two
languages 
for now.)

> HTH

Thank you for all the info.  It's a big help!

Hal

> 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
>

Re: Using Different Database Groups On the Same Computer

[Hal Vaughan]

On Wednesday 16 February 2005 09:27 pm, Logan, David (SST - Adelaide) wrote:
> Ooops, sorry for the second time, I forgot to include the list.
>
> Yes, you can install the "normal" version of mysql (in my case 4.0.22)
> and leave everything default. You can then install a new version of
> mysql (4.1.3 or something) and then set up the my.cnf so that it listens
> on a different tcp port. MySQL uses port 3306 by default but you can
> change this via the my.cnf. I also set up a different startup file in
> the rc3.d directory that starts the second server using different
> parameters passed via the command line.

Okay -- I follow most of this.  Just one other question: Is there a way to set 
this up with only ONE install of MySQL?  (I'm running a Debian system and, 
for the sake of updates and stuff, I'm trying to put as little as possible on 
the system and keep it all so it can be easily updated through 
security.debian.org.)  In other words, could I make it so if I access it 
through 3306, it goes to the current version, but port 3307 makes it use the 
new databases?

> The docs do quite a good job on describing this.
>
> You can then specify this in your DBI call to MySQL like
>
> my $dsn =
> "DBI:mysql:host=$hostname;database=lcscreative;port=$newportnumber";
> my $dbh = DBI->connect($dsn, 'username', 'password', \%err_handle);
>
> You could also use a unix socket rather than tcp by specifying the
> socket name rather than a port number. This is documented in the DBI
> docs at CPAN.
>
> You can do the same thing from PHP, eg. specifying a different port
> number etc. You can do this from the command line by using mysql
> --username username --password --port=3307 --socket=somethingdifferent.
>
> Just make sure that you specify 2 different data directories, 1 for the
> production or normal version and 1 on a different filesystem or
> whereever for the development version. It will not matter that the
> databases have the same name as they are in different mysql instances.

I figured I'd have to have different directories.  That also makes backing up 
data easier.  (I'm backing everything up with rsync, so if I specify 
directories that are in the same directory tree the rest of the programs and 
data are in, I can back up everything with one cron job instead of using 
mysqldump AND backing up the file I generate with it.)

> I also use environment variables set in the apache config to ensure
> separation. I have 2 servers that I use, one production and one
> development. I have the following 2 statements in the virtual host
> section for the website

At this point, fortunately, I won't have to worry about doing anything through 
Apache.  I think I'll miss that entirely.

> httpd.config on development
>
> SetEnv DB_HOST devhost
> SetEnv DB_USER root
>
> httpd.config on production
>
> SetEnv DB_HOST prodhost
> SetEnv DB_USER prod_username
>
> and then access these through the %ENV hash in perl or getenv function
> in PHP. That makes it easy, all I have to do is transfer the scripts
> over and they pick up the correct names. You could do this on a single
> server by having virtual hosts and setting the variables to different
> values dependent upon the url called.

I'm using Perl for the web stuff (which is only for a setting editor).  I'm 
not a professional programmer.  I hadn't touched a line of code in over a 
decade when I started this.  I discovered Perl quickly, and I've done as much 
as possible in Perl so I can give myself a sense of unity.  (I had to do 
another part in Java, so I'm trying to stick with only those two languages 
for now.)

> HTH

Thank you for all the info.  It's a big help!

Hal

> 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: Hal Vaughan [mailto:[EMAIL PROTECTED]
> Sent: Thursday, 17 February 2005 12:45 PM
> To: Logan, David (SST - Adelaide)
> Subject: Re: Using Different Database Groups On the Same Computer
>
> On Wednesday 16 February 2005 09:04 pm, you wrote:
> > Hi Hal,
> >
> > Do you mean using the same version but accessing 2 different sets of
> > tables? I'm a little confused as to what your requirements are.
>
> Or databases.  Basically, I want it all on one system, but I don't want
> the
> current databases, which are in use by the current and running version
> of the
> program, to be effected in any way by the new version of my program
> (actually
> a suite of programs), which will be using databases and tables with the
> same
> names as the current version.
>
> > You could install both versions and use aliases to ensure that you can
> > access only one instance at a time by the method you described. You
> > would have new-mysql aliases to a mysql command with either a
>
> different
>
> > port number or hostname to access the newer version whereas the mysql
> > would point to localho

RE: Using Different Database Groups On the Same Computer

[Logan, David (SST - Adelaide)]

Ooops, sorry for the second time, I forgot to include the list.

Yes, you can install the "normal" version of mysql (in my case 4.0.22)
and leave everything default. You can then install a new version of
mysql (4.1.3 or something) and then set up the my.cnf so that it listens
on a different tcp port. MySQL uses port 3306 by default but you can
change this via the my.cnf. I also set up a different startup file in
the rc3.d directory that starts the second server using different
parameters passed via the command line.

The docs do quite a good job on describing this.

You can then specify this in your DBI call to MySQL like

my $dsn =
"DBI:mysql:host=$hostname;database=lcscreative;port=$newportnumber";
my $dbh = DBI->connect($dsn, 'username', 'password', \%err_handle);

You could also use a unix socket rather than tcp by specifying the
socket name rather than a port number. This is documented in the DBI
docs at CPAN.

You can do the same thing from PHP, eg. specifying a different port
number etc. You can do this from the command line by using mysql
--username username --password --port=3307 --socket=somethingdifferent.

Just make sure that you specify 2 different data directories, 1 for the
production or normal version and 1 on a different filesystem or
whereever for the development version. It will not matter that the
databases have the same name as they are in different mysql instances.

I also use environment variables set in the apache config to ensure
separation. I have 2 servers that I use, one production and one
development. I have the following 2 statements in the virtual host
section for the website

httpd.config on development

SetEnv DB_HOST devhost
SetEnv DB_USER root

httpd.config on production

SetEnv DB_HOST prodhost
SetEnv DB_USER prod_username

and then access these through the %ENV hash in perl or getenv function
in PHP. That makes it easy, all I have to do is transfer the scripts
over and they pick up the correct names. You could do this on a single
server by having virtual hosts and setting the variables to different
values dependent upon the url called.

HTH

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: Hal Vaughan [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 17 February 2005 12:45 PM
To: Logan, David (SST - Adelaide)
Subject: Re: Using Different Database Groups On the Same Computer

On Wednesday 16 February 2005 09:04 pm, you wrote:
> Hi Hal,
>
> Do you mean using the same version but accessing 2 different sets of
> tables? I'm a little confused as to what your requirements are.

Or databases.  Basically, I want it all on one system, but I don't want
the 
current databases, which are in use by the current and running version
of the 
program, to be effected in any way by the new version of my program
(actually 
a suite of programs), which will be using databases and tables with the
same 
names as the current version.

> You could install both versions and use aliases to ensure that you can
> access only one instance at a time by the method you described. You
> would have new-mysql aliases to a mysql command with either a
different
> port number or hostname to access the newer version whereas the mysql
> would point to localhost on the standard port.

Let me put it in my words, so I can be sure I understand you.  So I
could 
install MySQL and leave everything "normal" for my current program to
access 
everything it can access now (I'm accessing them through Perl, using
DBI), 
AND I can also specify another group of databases that are accessed if I
try 
a different port from Perl, or perhaps, a different specification on the

command line.

Is that it?

If so, is there a technical term for what I'm trying to do, or what each
set 
of databases is called?  And what does this come under in the manuals
and 
info so I can find out how to configure my system to do this?

> I run 2 different versions on one of my smaller linux boxes for
> precisely the reason you are talking about. If you are accessing these
> via php for example, you can use an environment variable to set which
> port, host etc. you wish to talk to.

That's basically what I want to do, so I think we're talking about the
same 
thing.

Thanks!

Hal

> 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: Hal Vaughan [mailto:[EMAIL PROTECTED]
> Sent: Thursday, 17 February 2005 12:23 PM
> To: mysql@lists.mysql.com
> Subject: Using Different Database Groups On the Same Computer
>
> I'm not quite sure what search terms to use, otherwise I'm sure I
could
> find
> this on Google.
>
> I have a working install of MySQL on an older computer, running the
> current
> version of my program.  I am a

Re: Using Different Database Groups On the Same Computer

[Hal Vaughan]

On Wednesday 16 February 2005 09:04 pm, you wrote:
> Hi Hal,
>
> Do you mean using the same version but accessing 2 different sets of
> tables? I'm a little confused as to what your requirements are.

Or databases.  Basically, I want it all on one system, but I don't want the 
current databases, which are in use by the current and running version of the 
program, to be effected in any way by the new version of my program (actually 
a suite of programs), which will be using databases and tables with the same 
names as the current version.

> You could install both versions and use aliases to ensure that you can
> access only one instance at a time by the method you described. You
> would have new-mysql aliases to a mysql command with either a different
> port number or hostname to access the newer version whereas the mysql
> would point to localhost on the standard port.

Let me put it in my words, so I can be sure I understand you.  So I could 
install MySQL and leave everything "normal" for my current program to access 
everything it can access now (I'm accessing them through Perl, using DBI), 
AND I can also specify another group of databases that are accessed if I try 
a different port from Perl, or perhaps, a different specification on the 
command line.

Is that it?

If so, is there a technical term for what I'm trying to do, or what each set 
of databases is called?  And what does this come under in the manuals and 
info so I can find out how to configure my system to do this?

> I run 2 different versions on one of my smaller linux boxes for
> precisely the reason you are talking about. If you are accessing these
> via php for example, you can use an environment variable to set which
> port, host etc. you wish to talk to.

That's basically what I want to do, so I think we're talking about the same 
thing.

Thanks!

Hal

> 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: Hal Vaughan [mailto:[EMAIL PROTECTED]
> Sent: Thursday, 17 February 2005 12:23 PM
> To: mysql@lists.mysql.com
> Subject: Using Different Database Groups On the Same Computer
>
> I'm not quite sure what search terms to use, otherwise I'm sure I could
> find
> this on Google.
>
> I have a working install of MySQL on an older computer, running the
> current
> version of my program.  I am about to start developing a newer version
> of my
> program on a new computer.  I'd like to have both versions (current and
> new)
> on the same computer, however, that will cause a LOT of problems in
> interference between the current and development versions of my program.
>
> Is there any way to separate these, so I can run mysql one way to access
> the
> databases for the current version, and run it another way to access the
> newer
> databases for the development version (and yes, many databases in the
> development version will have the same name as those in the current
> version).
>
> For example, if I ran a command "mysql", I'd get these tables:
>
> CaseData
> Clients
> GeneralSetup
>
> And if I ran an alias, like, say, "new-mysql", I'd get these tables:
>
> CaseData  (same name, different table)
> Clients  (same name, different table)
> GeneralSetup  (same name, different table)
> DataFilters
>
> I am running MySQL on Linux, if that makes a difference.
>
> Hal

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

RE: Using Different Database Groups On the Same Computer

[Logan, David (SST - Adelaide)]

Hi Hal,

Do you mean using the same version but accessing 2 different sets of
tables? I'm a little confused as to what your requirements are.

You could install both versions and use aliases to ensure that you can
access only one instance at a time by the method you described. You
would have new-mysql aliases to a mysql command with either a different
port number or hostname to access the newer version whereas the mysql
would point to localhost on the standard port.

I run 2 different versions on one of my smaller linux boxes for
precisely the reason you are talking about. If you are accessing these
via php for example, you can use an environment variable to set which
port, host etc. you wish to talk to.

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: Hal Vaughan [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 17 February 2005 12:23 PM
To: mysql@lists.mysql.com
Subject: Using Different Database Groups On the Same Computer

I'm not quite sure what search terms to use, otherwise I'm sure I could
find 
this on Google.

I have a working install of MySQL on an older computer, running the
current 
version of my program.  I am about to start developing a newer version
of my 
program on a new computer.  I'd like to have both versions (current and
new) 
on the same computer, however, that will cause a LOT of problems in 
interference between the current and development versions of my program.

Is there any way to separate these, so I can run mysql one way to access
the 
databases for the current version, and run it another way to access the
newer 
databases for the development version (and yes, many databases in the 
development version will have the same name as those in the current
version).

For example, if I ran a command "mysql", I'd get these tables:

CaseData
Clients
GeneralSetup

And if I ran an alias, like, say, "new-mysql", I'd get these tables:

CaseData  (same name, different table)
Clients  (same name, different table)
GeneralSetup  (same name, different table)
DataFilters

I am running MySQL on Linux, if that makes a difference.

Hal

-- 
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]

Using Different Database Groups On the Same Computer

[Hal Vaughan]

I'm not quite sure what search terms to use, otherwise I'm sure I could find 
this on Google.

I have a working install of MySQL on an older computer, running the current 
version of my program.  I am about to start developing a newer version of my 
program on a new computer.  I'd like to have both versions (current and new) 
on the same computer, however, that will cause a LOT of problems in 
interference between the current and development versions of my program.

Is there any way to separate these, so I can run mysql one way to access the 
databases for the current version, and run it another way to access the newer 
databases for the development version (and yes, many databases in the 
development version will have the same name as those in the current version).

For example, if I ran a command "mysql", I'd get these tables:

CaseData
Clients
GeneralSetup

And if I ran an alias, like, say, "new-mysql", I'd get these tables:

CaseData  (same name, different table)
Clients  (same name, different table)
GeneralSetup  (same name, different table)
DataFilters

I am running MySQL on Linux, if that makes a difference.

Hal

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

query performance

[Ryan McCullough]

Can I post a query to this list and ask for help optimizing it?

-- 
Ryan McCullough
mailto:[EMAIL PROTECTED]

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

Re: Multiple record Insert limit

[Ed Reed]

That did it.
 
Thanks for the quick response!


>>> <[EMAIL PROTECTED]> 2/16/05 11:19 AM >>>
"Ed Reed" < [EMAIL PROTECTED] > wrote on 02/16/2005 02:13:40 PM:

> Can anyone tell me the limit for the number of records that can be
> inserted in a single call?
> 
> I'm trying to insert multiple records like this,
> 
> INSERT INTO t1 (f1, f2, f3, f4) VALUES
> ("test", 1, "some data", "Done"),
> ("testing", 21, "some more data", "Still Done"),("tested", 50, "no
more
> data", "Not Done"),("tester", 201, "Lots of data", "Finished");
> 
> This works when I've done 10 records but fails when I do 3. The
> error I get when it fails is 
> [MySQL][ODBC 3.51 Driver][mysqld-4.1.7-nt]MySQL server has gone
away.
> So what's the max number that I can insert in a single statement?
> 
> Thanks!
> 

In my experience, the max# of inserts depends on the setting of the 
MAX_ALLOWED_PACKET variable. If you create a packet that is too large 
(exceeds that value) you will run into problems like you are seeing.

To check the current value for your server, you can do

SHOW VARIABLES like 'max%';

Make sure when you are creating your INSERT statements that you do not

exceed that value for each statement. If these are generated by
mysqldump, 
it has an option where you can set the maximum length of a generated 
INSERT statement. Check the docs (
http://dev.mysql.com/doc/mysql/en/mysqldump.html ) or run: mysqldump
--help 
for specifics.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Generating ticket/file numbers

[daniel]

> Daniel,
>
> It's really not clear to me exactly what you want.  For example, what
> do you  mean by file numbers?  Perhaps if you could tell us a little
> more about your  table, your query, and the result you want, we could
> provide a solution.
>

File number = Job Number , or Ticket Number.



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

RE: mysql heartbeat

[Pete Moran]

A monitoring solution which can monitor mysql as well as pretty much any
service on nix and win platforms is zabbix (http://www.zabbix.com), its very
easy to setup and personally I think its excellent.  It may be overkill if
you just want to check a db is up though

-Original Message-
From: Mathew Ray [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 17 February 2005 2:35 AM
To: mysql@lists.mysql.com
Subject: mysql heartbeat

Has anyone used or built a db monitoring tool for MySQL?

I have come across various system-level heartbeat tools, but I am 
specifically looking for a solution that will work on Win2K Server and 
RedHat boxes, as we have several servers that each run different 
instances of mysql.

No replication or clustering here, just looking to get notification and 
execute some php if a specific db or table isn't accessible. I have a 
rough plan for making my own, but I thought I would check here to see if 
anyone had a suggestion...

Thanks,
~mathew



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



-- 
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.8 - Release Date: 14/02/2005
 

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.8 - Release Date: 14/02/2005
 


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

Re: Generating ticket/file numbers

[Michael Stassen]

Daniel,
It's really not clear to me exactly what you want.  For example, what do you 
mean by file numbers?  Perhaps if you could tell us a little more about your 
table, your query, and the result you want, we could provide a solution.

Michael
[EMAIL PROTECTED] wrote:
At 04:35 pm 2/16/2005, you wrote:
Hi there is there any way at all that with a special field type mysql
can generate file numbers like so AUD-01 then AUD-02 
AUD-00010 etc ... Let me know.
You can get the 01, etc via something like SeqItem int(6) unsigned
zerofill auto_increment. Then select "AUD_", SeqItem from the table.
However, only one field can be auto_increment.

maybe a table to autogenerate the file numbers ? maybe thats a waste of
time and resource ?

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

Re: Generating ticket/file numbers

[Eamon Daly]

You probably want a multiple column sequence. See pages 562
through 567 of the MySQL Cookbook by Paul DuBois, 1st. ed.
for an excellent and thorough description.
It's also briefly mentioned at:
http://dev.mysql.com/doc/mysql/en/example-auto-increment.html

Eamon Daly

- Original Message - 
From: <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, February 16, 2005 3:35 PM
Subject: Generating ticket/file numbers


Hi there is there any way at all that with a special field type mysql can
generate file numbers like so AUD-01 then AUD-02  AUD-00010
etc ... Let me know.

--
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: select where multiple joined records match

[AM Thomas]

Hi there,
I'll answer your questions below, but I should tell you that it looks like  
even three or four "joins" seem to make this too slow.  Theoretically, the  
user could select up to 15 or so GoalNumber values, 12 for Grade, and  
about 20 possibilities for Subject - clearly it wouldn't be useful to  
specify that many items in a search, but that's the theoretical maximum; 4  
or 5 values isn't unreasonable, though.

Four "AND"ed goal numbers plus a subject and a grade slowed the search (on  
the shared commercial web host I'm using) into the 3 minute range, and  
that's with a regular join, not a left join.  This is the SELECT that took  
about 3 minutes (3 trials, simplifying slightly each time, simplest given  
here):

select r.id from resources as r
  join resource_goals as g0 on (r.id=g0.ResourceID)
  join resource_goals as g1 on (r.id=g1.ResourceID and g1.GoalNumber=1)
  join resource_goals as g2 on (r.id=g2.ResourceID and g2.GoalNumber=2)
  join resource_goals as g3 on (r.id=g3.ResourceID and g3.GoalNumber=3)
  join resource_goals as g4 on (r.id=g4.ResourceID and g4.GoalNumber=4)
where ((g0.Subject='Social_Studies')
  and (g0.Grade='4th'))
group by r.id;
The fastest time was 2 min 48 sec.
Last time (simplest query) was 3 min 2 sec.
I'm really running out of time on this project, so I just went ahead and  
made the user interface such that users can only select one subject,  
grade, and/or goal number at a time.  It's probably a sound decision from  
a usability perspective, so I'm not too sad.  If I decide to make this  
work in the future, I'd probably have to just do a SELECT for each "AND"ed  
field, get the list of resource id's for each SELECT, then find the  
intersection of the lists in Perl.

If I could speed this up with some kind of indexing, I'd love to know  
about it.

The GROUP BY phrase is because I wanted just one row per resource.  It  
seemed like I'd get a row for each condition/resource (didn't test it with  
the final ).  I'm actually doing SELECT * FROM... in my code, and not  
using the resource_goals information in my output (that's a separate view  
at present, generated by different Perl code).

It seems to work fine without the NOT NULL parts, you're right.  I was  
wondering about that, but was sleepy enough at the time that I didn't  
trust my thinking.

Sorry about not including my table defs :-(  .  I guess I was just hoping  
for a general approach, and didn't realize that anyone would be interested  
enough to read all that detail and provide and exact solution for me.  Of  
course, now I realize that it would have simplified our discussion.   
Anyway, late but not never, and for help to whoever finds this in the list  
archives someday, here are my table defs (you're right - the goal number  
is a TINYINT):

(Below is an abridged version of the resources table ; it also contains  
about 60 more TINYINT fields which are essentially used as booleans, some  
of which I hope to eliminate.  Yes, I could have used SET or something,  
but I didn't for various reasons.)

CREATE TABLE resources (
  id INT UNSIGNED PRIMARY KEY,
  Title TEXT,
  ResourceType_THJHArticle TINYINT,
  ResourceType_NIEArticle TINYINT,
  DataEntryName TINYTEXT,
  Date DATETIME,
  Notes TEXT,
  Made_Keywords TEXT);
CREATE TABLE  resource_goals (
  goal_id INT UNSIGNED PRIMARY KEY,
  ResourceID INT,
  Grade TINYTEXT,
  Subject TINYTEXT,
  GoalNumber TINYINT,
  NumericGrade TINYINT);
Thanks a bunch for your help; I'm finding this more interesting than I  
thought I would.


On Wed, 16 Feb 2005 11:08:20 -0500, Michael Stassen  
<[EMAIL PROTECTED]> wrote:

AM Thomas wrote:
Guarded exclamations of success!
 This seems to be working for me so far - thank you!  Here's an actual   
example of a working query on my database; the field names are a  
little  different (sorry, I was trying to make my earlier example more  
readable).   The main change, though, is that I did plain 'join'  
instead of 'left  join', which seems to make it much faster.  It was  
pretty slow at first.
Yes, LEFT JOIN does extra work, and it wasn't needed here.
mysql> select r.id, ga.Grade, ga.GoalNumber, gb.GoalNumber from  
resources  as r
   join resource_goals as ga on r.id=ga.ResourceID
   and ga.Grade='4th' and ga.GoalNumber='1'
   join resource_goals as gb on r.id=gb.ResourceID
   and gb.Grade='4th' and gb.GoalNumber='2'
   where ga.goal_id IS NOT NULL
   and gb.goal_id IS NOT NULL
   group by r.id;
There are some strange things here, I think.
* You've never shown us your table definitions, but I would have  
expected GoalNumber to be an integer, not a string.  If so, you  
shouldn't quote the numbers you compare it to.

* I think it unlikely that you have rows with values in Grade,  
GoalNumber, and ResourceID which have NULL for goal_id.  Isn't goal_id  
the primary key?   If I'm right, you don't need your WHERE clause.

* Why have you added "GROUP BY r.id"?  Was that an attempt to fix  
somethi

Re: Generating ticket/file numbers

[daniel]

> At 04:35 pm 2/16/2005, you wrote:
>>Hi there is there any way at all that with a special field type mysql
>>can generate file numbers like so AUD-01 then AUD-02 
>>AUD-00010 etc ... Let me know.
> You can get the 01, etc via something like SeqItem int(6) unsigned
> zerofill auto_increment. Then select "AUD_", SeqItem from the table.
> However, only one field can be auto_increment.

maybe a table to autogenerate the file numbers ? maybe thats a waste of
time and resource ?



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

Re: Generating ticket/file numbers

[Eldon Ziegler]

At 04:35 pm 2/16/2005, you wrote:
Hi there is there any way at all that with a special field type mysql can
generate file numbers like so AUD-01 then AUD-02  AUD-00010
etc ... Let me know.
You can get the 01, etc via something like SeqItem int(6) unsigned 
zerofill auto_increment. Then select "AUD_", SeqItem from the table. 
However, only one field can be auto_increment.

Eldon Ziegler
MySQL Database Systems and Support
www.atlanticdb.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Generating ticket/file numbers

[daniel]

I already use the autoinc or sequence number as part of the number like

AUD-05--1 where 1 is the record id, just wanted to know if it could
happen on the mysql end.


> [EMAIL PROTECTED] wrote:
>
>>Hi there is there any way at all that with a special field type mysql
>>can generate file numbers like so AUD-01 then AUD-02 
>>AUD-00010 etc ... Let me know.
>>
>>
> Use an auto_increment field, say an 'unsigned integer auto_increment
> primary key'.
> Then do the formatting in code ( ie pad '1' to '01', '45' becomes
> '45', etc ). Then shove the 'AUD' bit at the start.
> Using an integer as the primary key you'll get faster joins, searches
> etc than using a string fields as the primary key.
>
> --
> Daniel Kasak
> IT Developer
> NUS Consulting Group
> Level 5, 77 Pacific Highway
> North Sydney, NSW, Australia 2060
> T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
> email: [EMAIL PROTECTED]
> website: http://www.nusconsulting.com.au
>
> --
> 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: Generating ticket/file numbers

[Daniel Kasak]

[EMAIL PROTECTED] wrote:
Hi there is there any way at all that with a special field type mysql can
generate file numbers like so AUD-01 then AUD-02  AUD-00010
etc ... Let me know.
 

Use an auto_increment field, say an 'unsigned integer auto_increment 
primary key'.
Then do the formatting in code ( ie pad '1' to '01', '45' becomes 
'45', etc ). Then shove the 'AUD' bit at the start.
Using an integer as the primary key you'll get faster joins, searches 
etc than using a string fields as the primary key.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Generating ticket/file numbers

[daniel]

Hi there is there any way at all that with a special field type mysql can
generate file numbers like so AUD-01 then AUD-02  AUD-00010
etc ... Let me know.



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

Re: Installation Instructions for MySQL for Mac OS X Panther

[daniel]

> Does anyone know where I can find in-depth, precise instructions to
> install MySQL on Mac OS X Panther client?
>
> - Asad
>

Just get the binary for OSX and follow the readme its simple.



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

Re: Update same table from subquery not allowed

[Michael Stassen]

matt_lists wrote:
matt_lists wrote:
"One restriction is that currently you cannot modify a table and 
select from the same table in a subquery. This applies to statements 
such as |DELETE|, |INSERT|, |REPLACE|, and |UPDATE|."

Anyboyd know if this will be fixed?
Working around it with a join on itself, is there some reason updating 
a table with a subquery on itself is not alowed?

Done it many times with other db's
Ok I was wrong, it wont let me do that
so how does everyone work around this "bug" (at least to me it's a bug)
senario is, id, audit, recno
I need to set audit = F on the record with the max(recno)
in most db's I would do update table set audit = f where recno = (select 
max(recno) from table)

but mysql does not allow updates on a table using a nested query on itself
so I figured a self join would work, but nope that does not work either
My first thought was
  SELECT @max_rec:= MAX(recno) FROM table;
  UPDATE table SET audit = 'F' WHERE recno = @max_rec;
but you'd probably need to lock the table to prevent someone doing an INSERT 
between the SELECT and UPDATE.

So this is probably better:
  UPDATE table SET audit = 'F' ORDER BY recno DESC LIMIT 1;
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Installation Instructions for MySQL for Mac OS X Panther

[Brent Baisley]

Just download the install package from the mysql website. Instructions 
for configuring it (i.e. setting root password) are the same for all 
platforms.

But, if you really need handholding, head over to entropy.ch.
On Feb 16, 2005, at 3:43 PM, Asad Habib wrote:
Does anyone know where I can find in-depth, precise instructions to
install MySQL on Mac OS X Panther client?
- Asad
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Installation Instructions for MySQL for Mac OS X Panther

[Asad Habib]

Does anyone know where I can find in-depth, precise instructions to
install MySQL on Mac OS X Panther client?

- Asad

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

Re: innodb error 995

[Heikki Tuuri]

Ben,
- Original Message - 
From: "Ben Kutsch" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Tuesday, February 15, 2005 1:55 AM
Subject: innodb error 995


Hi I'm running version MySQL 4.0.20a-debug
windows server 2003
on a dedicated database server with an external SCSI RAID array with a
1 TB of disk space  (over 400 GIG free)
1 gig of RAM
basic settings
key buffer 512 MB
sort Buffer 32 Mb
innof db
Buffer Pool Size 512 MB
my innodb datafile is about 200 gig is size and I have 6 innodb
databses. 3 are very large 40+ gigs with about 800 million records.
The other 3 are very small few MB is size. Every two weeks I rebuild
one of the large db and use the other 2 as backups.
This configuration as worked fine for the last 2 months.
I was in the process of rebuilding one of the large databases when I
got the following error.
050214 16:46:56  InnoDB: Operating system error number 995 in a file
operation.
InnoDB: See http://www.innodb.com/ibman.php for installation help.
InnoDB: See section 13.2 at http://www.innodb.com/ibman.php
InnoDB: about operating system error numbers.
InnoDB: File name .\ibdata1
InnoDB: File operation call: 'Windows aio'.
InnoDB: Cannot continue operation.
after restarting mysql, innodb rolled back my tables in the db that
crashed and the rest of my data seems fine.
I have googled around and there seems to be some info that this errors
is some kind windows driver. Is there any more info regarding this
error?
a few users have reported this Windows error. I believe it is a bug in 
Windows.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/debug/base/system_error_codes__500-999_.asp
"
ERROR_OPERATION_ABORTED
995 The I/O operation has been aborted because of either a thread exit or an 
application request.
"

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fileio/base/readfileex.asp
"
The ReadFileEx function may fail if there are too many outstanding 
asynchronous I/O requests. In the event of such a failure, GetLastError can 
return ERROR_INVALID_USER_BUFFER or ERROR_NOT_ENOUGH_MEMORY.

To cancel all pending asynchronous I/O operations, use the CancelIo 
function. This function only cancels operations issued by the calling thread 
for the specified file handle. I/O operations that are canceled complete 
with the error ERROR_OPERATION_ABORTED.
"

But InnoDB does not call CancelIo.
Do you happen to have a Microsoft support contract? You could report this to 
Microsoft.

Any help would be useful.
Thank you
-Ben
 
--
Ben Kutsch  Developer
Suite 304, 121 South 13th ST   Dept. of Computer Science and Eng.
phone: (402) 472-2340 University of Nebraska-Lincoln
fax: (402) 472-3135   Lincoln NE 68588-0115
email: [EMAIL PROTECTED]   http://nadss.unl.edu
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php

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

Re: Update same table from subquery not allowed

[matt_lists]

matt_lists wrote:
"One restriction is that currently you cannot modify a table and 
select from the same table in a subquery. This applies to statements 
such as |DELETE|, |INSERT|, |REPLACE|, and |UPDATE|."

Anyboyd know if this will be fixed?
Working around it with a join on itself, is there some reason updating 
a table with a subquery on itself is not alowed?

Done it many times with other db's
Ok I was wrong, it wont let me do that
so how does everyone work around this "bug" (at least to me it's a bug)
senario is, id, audit, recno
I need to set audit = F on the record with the max(recno)
in most db's I would do update table set audit = f where recno = (select 
max(recno) from table)

but mysql does not allow updates on a table using a nested query on itself
so I figured a self join would work, but nope that does not work either
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Update same table from subquery not allowed

[matt_lists]

"One restriction is that currently you cannot modify a table and select 
from the same table in a subquery. This applies to statements such as 
|DELETE|, |INSERT|, |REPLACE|, and |UPDATE|."

Anyboyd know if this will be fixed?
Working around it with a join on itself, is there some reason updating a 
table with a subquery on itself is not alowed?

Done it many times with other db's
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Multiple record Insert limit

[SGreen]

"Ed Reed" <[EMAIL PROTECTED]> wrote on 02/16/2005 02:13:40 PM:

> Can anyone tell me the limit for the number of records that can be
> inserted in a single call?
> 
> I'm trying to insert multiple records like this,
> 
> INSERT INTO t1 (f1, f2, f3, f4) VALUES
> ("test", 1, "some data", "Done"),
> ("testing", 21, "some more data", "Still Done"),("tested", 50, "no more
> data", "Not Done"),("tester", 201, "Lots of data", "Finished");
> 
> This works when I've done 10 records but fails when I do 3. The
> error I get when it fails is 
> [MySQL][ODBC 3.51 Driver][mysqld-4.1.7-nt]MySQL server has gone away.
> So what's the max number that I can insert in a single statement?
> 
> Thanks!
> 

In my experience, the max# of inserts depends on the setting of the 
MAX_ALLOWED_PACKET variable. If you create a packet that is too large 
(exceeds that value) you will run into problems like you are seeing.

To check the current value for your server, you can do

SHOW VARIABLES like 'max%';

Make sure when you are creating your INSERT statements that you do not 
exceed that value for each statement. If these are generated by mysqldump, 
it has an option where you can set the maximum length of a generated 
INSERT statement. Check the docs (
http://dev.mysql.com/doc/mysql/en/mysqldump.html) or run: mysqldump --help 
for specifics.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Multiple record Insert limit

[Ed Reed]

Can anyone tell me the limit for the number of records that can be
inserted in a single call?
 
I'm trying to insert multiple records like this,
 
INSERT INTO t1 (f1, f2, f3, f4) VALUES
("test", 1, "some data", "Done"),
("testing", 21, "some more data", "Still Done"),("tested", 50, "no more
data", "Not Done"),("tester", 201, "Lots of data", "Finished");
 
This works when I've done 10 records but fails when I do 3. The
error I get when it fails is 
[MySQL][ODBC 3.51 Driver][mysqld-4.1.7-nt]MySQL server has gone away.
So what's the max number that I can insert in a single statement?
 
Thanks!

Re: Table crash questions...

[Dan Tappin]

Good call... I should have figured that out my self.  The old version 
was 2.6:

/usr/bin/myisamchk (v2.6)
/usr/local/mysql-standard-4.1.8-apple-darwin7.6.0-powerpc/bin/myisamchk 
(v2.7)

This is likely why I lost my data in the first place.
Dan T
On Feb 16, 2005, at 10:57 AM, Michael Stassen wrote:
Dan Tappin wrote:
I have MySQL 4.1.8 installed on OS X 10.3.8 Server and I was in the 
process of building a PHP / MySQL website.  After numerous connection 
issues with the MySQL server I decided to take a look at the status 
of my tables to check for corruption.
After running myisamchk I managed to loose all my data records for 
one table.  Not a big deal As I was just in a testing phase an 
planned to purge the data any way.  The problem is now when I run 
myisamchk on this table I get:
Warning: table file /pathtomydatafolder/users.MYI was created in 
MySQL 4.1+, use REPAIR TABLE ... USE_FRM to recreate it as a valid 
MySQL 4.0 table
- recovering (with keycache) MyISAM-table 'users.MYI'
Data records: 0
You have installed mysql 4.1.8, but this error message appears to come 
from mysql 4.0.x.  Any possibility you started the wrong server?  
Which leads me to wonder which version of myisamchk you ran.  A PATH 
problem, perhaps?

I tried "REPAIR TABLE users USE_FRM" with no help in resolving this 
message.
Is this anything to even worry about??
Dan T

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

Re: How to plan the Tablespace in a huge mysql?

[Gary Richardson]

I've always figured that if your tablespaces are tight enough to
autoextend, you're going to take a performance hit. InnoDB uses the
tablespace for some transaction/rolebacks -- if you have a large
transaction going through it will be slowed down by an autoextend
operation. Plus, once you're there, you're probably going to be always
autoextending. I can't find anything 'official' that says this, so I
could be talking straight out of butt.

If you're talking about autoextending files on a 1.5TB database,
you're going to have 50GB files anyway. I'd sooner control it than let
it grow wildly.


On Wed, 16 Feb 2005 19:44:56 +0800, proace <[EMAIL PROTECTED]> wrote:
> I expect the data size is no more then 1.5TB.
> 
> Why don't you like to let tablespace auto grow?
> Is it performace issue or not?
> 
> If I create ten innodb_data_file and each size of innodb_data_file is 50G,
> dose some issues must be take care?
> Because the 50G is really very big for a file, I never do it.
> 
> Regards,
> proace
> 
> 
> On Tue, 15 Feb 2005 07:27:42 -0800, Gary Richardson
> <[EMAIL PROTECTED]> wrote:
> > My preference is to use innodb_data_file. If everything is InnoDB, I
> > would probably create 25G or 50G files until you've created enough to
> > hold all the data plus enough for growth. Do you know specifically how
> > big the data is?
> >
> > I don't like to let my table space autogrow, so I have monitors
> > watching the free innodb space. If it gets tight, I manually add more
> > space.
> >
> >
> > On Tue, 15 Feb 2005 20:25:36 +0800, proace Tsai <[EMAIL PROTECTED]> wrote:
> > > Hello:
> > >
> > > The mysql server is estimated to be as follows,
> > > 1. two servers, one is master and the other is slaves (replication)
> > > 2. two databases in mysql
> > > 3. 513 tables in each database
> > > 4. about 300 rows in each table
> > > 5. about 2T disk space for each server using SAN Storage
> > > 6. backup database periodically
> > >
> > > The running environment is follows,
> > > Server: Dual Intel Xeon 3.2G with 4G DDR2 Memory.
> > > OS: FreeBSD 5.3-RELEASE
> > > MySQL: 4.1 branch
> > > Operation: 70 ~ 80% operation is query (select statement)
> > >
> > > According to the above terms,
> > > how to plan the Tablespace in the mysql server?
> > > Using raw devices for the tablespace or innodb_data_file?
> > > ( How many Tablespace do I create? )
> > > or using innodb_data_file with innodb_file_per_table?
> > >
> > > Regards,
> > > proace.
> > >
>

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

Mysql running as root

[Russ]

I have two questions:
1) I'm still having trouble getting mysql to run as user mysql. All the file 
and folders have been changed to be owned by user mysql but phpadmin shows my 
sql running as root. When I log on as root and look at the users, I do not 
see a mysql user. Do I need to create this user and what permissions does it 
need? How can the files and folders be owned by a user if they do not exist?

2) When my windows environment died I was unable to dump mysql. I was able to 
recover the Data files for both tables I had (.myd, .myi and .frm) Also for 
the host database,etc. can these be copied to my new Linux enviroment or is 
it best to rebuild everything?

Thanks in advance for any help!!


-- 
Russ

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

Re: Table crash questions...

[Michael Stassen]

Dan Tappin wrote:
I have MySQL 4.1.8 installed on OS X 10.3.8 Server and I was in the 
process of building a PHP / MySQL website.  After numerous connection 
issues with the MySQL server I decided to take a look at the status of 
my tables to check for corruption.

After running myisamchk I managed to loose all my data records for one 
table.  Not a big deal As I was just in a testing phase an planned to 
purge the data any way.  The problem is now when I run myisamchk on this 
table I get:

Warning: table file /pathtomydatafolder/users.MYI was created in MySQL 
4.1+, use REPAIR TABLE ... USE_FRM to recreate it as a valid MySQL 4.0 
table
- recovering (with keycache) MyISAM-table 'users.MYI'
Data records: 0
You have installed mysql 4.1.8, but this error message appears to come from 
mysql 4.0.x.  Any possibility you started the wrong server?  Which leads me 
to wonder which version of myisamchk you ran.  A PATH problem, perhaps?

I tried "REPAIR TABLE users USE_FRM" with no help in resolving this 
message.

Is this anything to even worry about??
Dan T
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

is DELETE QUICK supported in 3.22.32?

[Jim Hoadley]

Is DELETE QUICK supported in MySQL version 3.22.32?

-- Jim







__ 
Do you Yahoo!? 
Yahoo! Mail - You care about security. So do we. 
http://promotions.yahoo.com/new_mail

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

Table crash questions...

[Dan Tappin]

I have MySQL 4.1.8 installed on OS X 10.3.8 Server and I was in the 
process of building a PHP / MySQL website.  After numerous connection 
issues with the MySQL server I decided to take a look at the status of 
my tables to check for corruption.

After running myisamchk I managed to loose all my data records for one 
table.  Not a big deal As I was just in a testing phase an planned to 
purge the data any way.  The problem is now when I run myisamchk on 
this table I get:

Warning: table file /pathtomydatafolder/users.MYI was created in MySQL 
4.1+, use REPAIR TABLE ... USE_FRM to recreate it as a valid MySQL 4.0 
table
- recovering (with keycache) MyISAM-table 'users.MYI'
Data records: 0

I tried "REPAIR TABLE users USE_FRM" with no help in resolving this 
message.

Is this anything to even worry about??
Dan T

Re: mysql heartbeat

[Mathew Ray]

Hi Michael, thanks for the info. I guess I will go ahead with my plan to 
make something a little bit more robust that will check the avialability 
of every table of every db on a box and send out notifications on an 
interval until they are corrected. Seems like a nice little sourceforge 
project though...

~mathew
Michael Dykman wrote:
On Wed, 2005-02-16 at 10:35, Mathew Ray wrote:
Has anyone used or built a db monitoring tool for MySQL?
I have come across various system-level heartbeat tools, but I am 
specifically looking for a solution that will work on Win2K Server and 
RedHat boxes, as we have several servers that each run different 
instances of mysql.

No replication or clustering here, just looking to get notification and 
execute some php if a specific db or table isn't accessible. I have a 
rough plan for making my own, but I thought I would check here to see if 
anyone had a suggestion...

Thanks,
~mathew

I don't know of anything in open source or shrink wrapped that does it,
least of all cross-platform but perl and Java each run with good
transparency on both *nix and Win32..  a script/program to perform basic
heartbeat check would take less than an hour and be completely portable.

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

Re: mysql heartbeat

[Michael Dykman]

On Wed, 2005-02-16 at 10:35, Mathew Ray wrote:
> Has anyone used or built a db monitoring tool for MySQL?
> 
> I have come across various system-level heartbeat tools, but I am 
> specifically looking for a solution that will work on Win2K Server and 
> RedHat boxes, as we have several servers that each run different 
> instances of mysql.
> 
> No replication or clustering here, just looking to get notification and 
> execute some php if a specific db or table isn't accessible. I have a 
> rough plan for making my own, but I thought I would check here to see if 
> anyone had a suggestion...
> 
> Thanks,
> ~mathew

I don't know of anything in open source or shrink wrapped that does it,
least of all cross-platform but perl and Java each run with good
transparency on both *nix and Win32..  a script/program to perform basic
heartbeat check would take less than an hour and be completely portable.

-- 
 - michael dykman
 - [EMAIL PROTECTED]


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

Re: Can I dissable transactions?

[Michael Stassen]

Ben Clewett wrote:
Jeff,
Thanks for your idea.  Deleting data x rows at a time would certainly 
help, if AUTOCOMMIT=TRUE.  But I have no idea how :)  I have tried:

DELETE FROM very_large_table WHERE delete_flag=1 LIMIT 10;
But the LIMIT is not understood (4.1.8).
What do you mean?  That appears to be valid syntax.  Do you get an error or 
unexpected results?  (You might also want to try a smaller limit).

Unfortunately my 'delete_flag' is not key.  Therefore repeated attempts 
at deleting small amounts of data is very slow.
Right, no index on delete_flag means a table scan.  The LIMIT N should cause 
mysql to stop as soon as it finds (and deletes) the Nth matching row, 
though.  The trick is to set N low enough to get the speed you need. 
Unfortunately, each run will probably take longer than the one before, as 
there will be farther to go to find the first match.

I expect delete_flag is either 0 or 1.  What percent of rows have 
delete_flag = 1?  If small enough, an index on delete_flag would be used for 
this statement to speed things up.  Of course, that's an extra index 
probably not used for anything else.

If you haven't read them already, you may find these pages from the manual 
helpful:



The fastest way seem to be to dump the data, edit the file, and 
re-insert the data.

But you have given my my solution:
If I cannot disable transactions, I'll have to work with one of the keys 
and iterate through that key bit bit.  So thanks, I'll go off and give 
it a go...

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

Re: How to do a simple fulltext match? Thanks!

[Michael Stassen]

healthserv wrote:
Hi!
I am having a problem searching a fulltext field.  I'm setting up a little code library program for 
a few of us who work together.  One field (TEXT) is "keywords" and it is indexed as well 
as fulltext.  I send a simple query via my form to the code below.  The connection is successfully 
made, and  I attempt to match my little search (for the word "short") contained in the 
variable $searchterms.  However, I get back the message that $peeky (below) is not a valid MySql 
resource.  Can someone tell me what I am doing wrong?   Thanks so much!
Cheers!
-Warren
=== Errant Code follows ===
$dbh=mysql_connect ("localhost", "something_here", "something_here") or die 
('Connection failed because: ' . mysql_error());
mysql_select_db ("thedatabase");
 $peeky=MYSQL_QUERY("select MATCH(keywords) AGAINST \"$searchterms\"  from 
CodeLib" );
 $peek=mysql_fetch_array($peeky);
 $howMany=$peek[0];
echo "Howmany=$howMany" ;
===
You checked for errors on mysql_connect, but not for mysql_query.  If you 
had, you would have learned that your query

  select MATCH(keywords) AGAINST "$searchterms"  from CodeLib
isn't valid SQL. You need to say which columns to select, then say which 
table, then restrict matches in a WHERE clause.  Something like

  SELECT id, some_col, some_other_col
  FROM CodeLib
  WHERE MATCH(keywords) AGAINST "$searchterms"
See the manual  for details.
Your code would then be something like
$dbh=mysql_connect("localhost", "something_here", "something_here")
  or die ('Connection failed because: ' . mysql_error());
mysql_select_db("thedatabase", $dbh);
$sql = "SELECT id, some_col, some_other_col
FROM CodeLib
WHERE MATCH(keywords) AGAINST \"$searchterms\"";
$peeky = mysql_query($sql, $dbh)
  or die ("Query:\n" . $sql . "\n"
  . "failed with error:\n" . mysql_error());
$peek = mysql_fetch_array($peeky);
$howMany = $peek[0];
echo "Howmany=$howMany" ;
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

auto_increment=0

[Philippe Rousselot]

hi,

I am migrating a DB having a table with a UID not_null autoincrement

the original table starts at UID=0

I cannot migrate this table autmaticaly as the line for UID=0 is
automatically transformed into UID=1 and therefore I get an error
message for the next line (UID=1) as being already into the table.

I thought to be smart first by creating the table with no autoincrement
and second altering the table to have UID unique and autoincremet but
MySQL does not seem to like that

I cannot modify the table to have it starting this UID=1

what can I do ?

Thanks in advance

Philippe


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

Re: select where multiple joined records match

[Michael Stassen]

AM Thomas wrote:
Guarded exclamations of success!
This seems to be working for me so far - thank you!  Here's an actual  
example of a working query on my database; the field names are a little  
different (sorry, I was trying to make my earlier example more 
readable).   The main change, though, is that I did plain 'join' instead 
of 'left  join', which seems to make it much faster.  It was pretty slow 
at first.
Yes, LEFT JOIN does extra work, and it wasn't needed here.
mysql> select r.id, ga.Grade, ga.GoalNumber, gb.GoalNumber from 
resources  as r
   join resource_goals as ga on r.id=ga.ResourceID
   and ga.Grade='4th' and ga.GoalNumber='1'
   join resource_goals as gb on r.id=gb.ResourceID
   and gb.Grade='4th' and gb.GoalNumber='2'
   where ga.goal_id IS NOT NULL
   and gb.goal_id IS NOT NULL
   group by r.id;
There are some strange things here, I think.
* You've never shown us your table definitions, but I would have expected 
GoalNumber to be an integer, not a string.  If so, you shouldn't quote the 
numbers you compare it to.

* I think it unlikely that you have rows with values in Grade, GoalNumber, 
and ResourceID which have NULL for goal_id.  Isn't goal_id the primary key? 
 If I'm right, you don't need your WHERE clause.

* Why have you added "GROUP BY r.id"?  Was that an attempt to fix something? 
 If we've got the query right, there should be no need for grouping.  If 
you don't get the result you want without the GROUP BY, then you should let 
us know, because that would mean we've missed something.

Putting those together, I'd expect
  SELECT r.id, ga.Grade, ga.GoalNumber, gb.GoalNumber
  FROM resources AS r
  JOIN resource_goals AS ga
 ON r.id = ga.ResourceID
AND ga.Grade = '4th'
AND ga.GoalNumber = 1
  JOIN resource_goals AS gb
 ON r.id = gb.ResourceID
AND gb.Grade = '4th'
AND gb.GoalNumber = 2
to do the job.  Does it?
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: select where multiple joined records match

[Michael Stassen]

Jeremy Cole wrote:
Hi,
Thanks, but unless I'm missing something, that will return the same 
as  SUBJECT="English" and (GRADE="1" or GRADE="2"), which is resource 
records  1,2,3, and 4 - too many matches.  Am I missing something?

How about this:
SELECT resources.id, resources.title
FROM resources
LEFT JOIN goals AS goal_a
  ON resources.id=goal_a.resource_id
  AND goal_a.subject="English" AND goal_a.grade=1
LEFT JOIN goals AS goal_b
  ON resources.id=goal_b.resource_id
  AND goal_b.subject="English" AND goal_b.grade=2
WHERE goal_a.id IS NOT NULL
  AND goal_b.id IS NOT NULL

There's no need for a LEFT JOIN here.  The difference between LEFT JOIN and 
JOIN is that LEFT JOIN creates extra NULL rows for the table on the right 
whenever it doesn't have a match for the table on the left.  We don't need 
that here.  Indeed, you throw those NULL rows away with your WHERE clause. 
It's more efficient not to create them in the first place if they're not 
needed.  In general, any time you write

 ...LEFT JOIN table_on_right ... WHERE table_on_right.some_col IS NOT NULL
you should probably just use a JOIN instead.
Hence, this query would be better as
  SELECT resources.id, resources.title
  FROM resources
  JOIN goals AS goal_a
ON resources.id = goal_a.resource_id
AND goal_a.subject = "English" AND goal_a.grade = 1
  JOIN goals AS goal_b
ON resources.id = goal_b.resource_id
AND goal_b.subject = "English" AND goal_b.grade = 2
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Problem with the stopword file in 4.1.9-Max

[N S]

Hi

I set ft_min_len_word = 3 and ft_stopword_file =
/etc/stopword.cnf in my.cnf file under the [mysqld]
section

I then restart the server and rebuild the indexes with
REPAIR TABLE ... QUICK

The ft_min_len_word=3 seems to work: I get the
expected results when quering for 3-letter words

Unfortunately, that is not the same with the stopwords
The default character set of the server is utf8. All
data in the tables are utf8 as well. So, I thought
that the stopword file should be in utf8 as well. If
that is wrong please say so. Now the syntax of that
file: I've tried one word each row, I've tried
double-quotes around words, I've tried comma-separated
as well as double-quotes and commas together, also
tried space-separated. I've tried anything I could
think of. None of those seem to work. When I query for
 one of those stopwords, I get results :(

After each change to the stopword file, I always
restart server, rebuild indexes etc.

I've run out of ideas. Any help would be appreciated.
Thx in advance, Nick




__ 
Do you Yahoo!? 
Yahoo! Mail - 250MB free storage. Do more. Manage less. 
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]

mysql heartbeat

[Mathew Ray]

Has anyone used or built a db monitoring tool for MySQL?
I have come across various system-level heartbeat tools, but I am 
specifically looking for a solution that will work on Win2K Server and 
RedHat boxes, as we have several servers that each run different 
instances of mysql.

No replication or clustering here, just looking to get notification and 
execute some php if a specific db or table isn't accessible. I have a 
rough plan for making my own, but I thought I would check here to see if 
anyone had a suggestion...

Thanks,
~mathew

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

Re: Server fails to start up after upgrade to 4.1.10

[Schneelocke]

Hi Heikki,

> try to find the real ibdata file and edit my.cnf or my.ini accordingly.

There is only one ibdata file, though - data/ibdata1, plus two log
files (data/ib_logfile0 and data/ib_logfile1).

Moving the files out of the data directory worked for now, and as far
as I know, none of my applications is using InnoDB, anyway, so it
shouldn't be a problem, but this still doesn't seem like a good
general solution, especially since 4.1.8 didn't have this problem (I'm
tempted to try downgrading again just to see if it goes away without
me having to fiddle with the InnoDB files).
 
-- 
schnee

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

INSERT queries hang on amd64

[Michel Buijsman]

Problem: INSERT queries hang on amd64.

This looks a lot like Don MacAskill's bugreport in 
http://bugs.mysql.com/bug.php?id=3483

I've just upgraded from 4.1.9 to 4.1.10 on a dual opteron with 8G ram,
running Debian 3.1. Also tried 4.0.23 last week, but that had the same 
problems. Kernels I've tried are 2.6.11-rc1-mm1 and 2.6.11-rc2.

I'm running 2 boxes in a replication setup, using the amd64 as the
master (or standalone) gave me pretty much what Don describes in bug 
3483, insert queries start hanging after a while with no way to kill 
them except kill -9 from the OS, which is a bit drastic. ;-)

The master or standalone setup required quite a bit of poking to get 
it to happen in a test environment, or just a couple of short hours 
running as a production server. I haven't been able to narrow it down
to a specific thing, but it appears to have something to do with indexes. 

I hacked up a test script that's throwing a random selection of selects 
and inserts at it at random intervals between 0 and 2 seconds, from 20 
concurrent threads.

I could trigger it by running a "repair table" on it while the test
script was running. The repair thread would wait for its turn, then lock 
everything else out, and when it was done the first insert after that 
would hang indefinitely. (Left it hanging over the weekend, nothing.)

Adding "skip-concurrent-insert" fixes the problem, but cripples the 
performance so that's not really an option.  

Then tried to run one of the amd64 boxes as a slave off of a xeon box:
Same problem, the replication thread hangs within seconds of starting 
the server, and again won't budge for anything except kill -9. This is
on a server without _any_ other connections except for a processlist.

I've run out of things to try, so I hope someone here can help...


my.cnf:

[mysqld]

user=mysql
socket=/tmp/mysql.sock
skip-locking
set-variable= key_buffer=2G
set-variable= table_cache=1024
set-variable= sort_buffer=16M
set-variable= read_buffer=16M
set-variable= max_allowed_packet=10M
set-variable= thread_cache=64
set-variable= tmp_table_size=16M
set-variable= interactive_timeout=600
set-variable= wait_timeout=600
set-variable= max_connections=1024
set-variable   = query_cache_type=2
set-variable   = query_cache_size=100M
set-variable= join_buffer_size=8M
set-variable   = thread_concurrency=4
set-variable= myisam_sort_buffer_size=64M
skip-innodb

-- 
 Michel Buijsmantty.nl -- 2dehands.nl


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

Re: Server fails to start up after upgrade to 4.1.10

[Heikki Tuuri]

Schnee,
- Alkuperäinen viesti - 
Lähettäjä: "Schneelocke" <[EMAIL PROTECTED]>
Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]>
Kopio: 
Lähetetty: Wednesday, February 16, 2005 5:07 PM
Aihe: Re: Server fails to start up after upgrade to 4.1.10


Hi Heikki,
you have probably mixed ibdata files. It is trying to apply a log record 
to
a page that is full of zeros.
*nods* What do I do about that?
try to find the real ibdata file and edit my.cnf or my.ini accordingly.
And, in an upgrade you should shut down mysqld gracefully. It is not a 
good
sign if InnoDB has to do a crash recovery after an upgrade.
I did that - i.e., I shut down the running instance from the System
Tray Monitor prior to upgrading, so unless something went wrong there,
the shutdown should have been graceful.
Hmm... then probably the mixup of ibdata files is what is causing InnoDB to 
think that it has to do a crash recovery.

--
schnee
Best regards,
Heikki
Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php

Order MySQL Network from http://www.mysql.com/support/index.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: INSERT queries hang on amd64

[Greg Whalin]

I have 2 dual proc opterons and also have not seen this using MyISAM 
(4.1.9), however, I have been less than impressed with mysql on opteron 
(using the x86-64 binary from their site).  Performance is generally 
less than that of our remaining old 32 bit dual proc xeon machines (all 
our db machines have 4 GB RAM and similar my.cnf settings giving 1.5GB 
to keybuffer, 768M to sort_buffer).  In some cases, we are seeing 
queries take twices as long on the opteron machines as on the xeons, and 
it is rare for the opterons to ever outperform our xeon cluster.  It is 
also much more likely for our tables to crash during big alters on the 
opteron machines than on the xeon, especially if the table has a full 
text on it (http://bugs.mysql.com/bug.php?id=7437).

We are planning a full switch to innodb, and I seriously hope that makes 
the mysql on opteron experience a more pleasurable one.

Greg
Donny Simonton wrote:
I know this may be strange, but have you turned on innodb on the box?  Even
if don't use it?  I have 8 amd64 boxes and have never experienced this
problem you are talking about.  They range from single proc to quad proc.
Never this problem but all of them have innodb turned on.  

Turn it on and see what happens, it could be a bug inside the mysql code.
Donny

-Original Message-
From: Michel Buijsman [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 16, 2005 5:41 AM
To: mysql@lists.mysql.com
Subject: INSERT queries hang on amd64
My problem: INSERT queries hang on amd64.
This looks a lot like Don MacAskill's bugreport in
http://bugs.mysql.com/bug.php?id=3483
Which is listed as closed, but the bug is apparently still there
so maybe it should be reopened...
I've just upgraded from 4.1.9 to 4.1.10 (binary) on a dual opteron
with 8G ram, running Debian 3.1 pure64 gcc-3.4. Also tried 4.0.23
last week, but that had the same problems. Kernels I've tried are
2.6.11-rc1-mm1 and 2.6.11-rc2.
I'm running 2 boxes in a replication setup, using the amd64 as the
master (or standalone) gave me pretty much what Don describes in bug
3483, insert queries start hanging after a while with no way to kill
them except kill -9 from the OS, which is a bit rough. ;-)
The master or standalone setup required quite a bit of poking to get
it to hang in a test environment, or just a few short hours running
as a production server. I haven't been able to narrow it down to one
specific thing, but it appears to have something to do with indexes,
because it ran fine after dropping all of them.
I hacked up a test script that's throwing a random selection of selects
and inserts at it at random intervals between 0 and 2 seconds, from 20
concurrent threads.
I could trigger it by running a "repair table" on it while the test
script was running. The repair thread would wait for its turn, then lock
everything else out and do its thing, and when it was done the first
insert after that would hang indefinitely. (Left it hanging over the
weekend, nothing.)
Adding "skip-concurrent-insert" fixes the problem, but cripples the
performance so that's not really an option.
Then tried to run one of the amd64 boxes as a slave off of a xeon box:
Same problem, the replication thread hangs within seconds of starting
the server, and again won't budge for anything except kill -9. This is
on a server without _any_ other connections except for a processlist.
I've run out of things to try, so I hope someone here can help...
my.cnf:
[mysqld]

user=mysql
socket=/tmp/mysql.sock
skip-locking
set-variable= key_buffer=2G
set-variable= table_cache=1024
set-variable= sort_buffer=16M
set-variable= read_buffer=16M
set-variable= max_allowed_packet=10M
set-variable= thread_cache=64
set-variable= thread_stack=512K
set-variable= tmp_table_size=16M
set-variable= interactive_timeout=600
set-variable= wait_timeout=600
set-variable= max_connections=1024
set-variable= query_cache_type=2
set-variable= query_cache_size=100M
set-variable= join_buffer_size=8M
set-variable= thread_concurrency=4
set-variable= myisam_sort_buffer_size=64M
skip-innodb
--
Michel Buijsmantty.nl -- 2dehands.nl
--
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: Server fails to start up after upgrade to 4.1.10

[Schneelocke]

Hi Heikki,

> you have probably mixed ibdata files. It is trying to apply a log record to
> a page that is full of zeros.

*nods* What do I do about that?

> And, in an upgrade you should shut down mysqld gracefully. It is not a good
> sign if InnoDB has to do a crash recovery after an upgrade.

I did that - i.e., I shut down the running instance from the System
Tray Monitor prior to upgrading, so unless something went wrong there,
the shutdown should have been graceful.

-- 
schnee

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

Re: how to make question that check the last hour

[Roger Baklund]

Jesper Andersson wrote:
[...]
created =VARCHAR(14)
[...]
select CREATED from SUBSCRIBER where date_sub(now(), interval 1 hour) <= 
created;
What do I wrong??
The 'created' column is a normal string, the date_sub() function returns 
a "time string", i.e. a string on the special format "-MM-DD 
HH:MM:SS". You must convert one to the format of the other, for instance:

select created
  from SUBSCRIBER
  where date_format(
date_sub(now(), interval 1 hour),
'%Y%m%d%H%i%s') <= created
This could be simplified like this with MySQL 3.23 or later:
select created
  from SUBSCRIBER
  where date_format(
now()-interval 1 hour),'%Y%m%d%H%i%s') <= created
You could also convert the other way:
select created
  from SUBSCRIBER
  where
date_sub(now(), interval 1 hour) <=
date_format(created,'%Y-%m-%d %H:%i:%s')
This is however not recommended in this case, because we get a function 
call on the created column which must be executed for each tested row, 
preventing the MySQL server from using an index on this column, if one 
is defined.

date_format(), date_sub(), the 'interval' operator and other date/time 
functions are documented here:

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html >
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: subquery help for an update

[mel list_php]

Found some help here:
http://forums.mysql.com/read.php?10,10572,11064#msg-11064
UPDATE Table1 SET Table1.Field1 = (
SELECT count(*) FROM Table2 WHERE Table2.Code2=Table1.Code1
)
and that query works for me, great!!
But I still don't understand why it updates properly without the WHERE 
clause.
So if anybody has a link to a doc for subqueries?

From: "mel list_php" <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Subject: subquery help for an update
Date: Wed, 16 Feb 2005 14:32:48 +
Hi,
I have one table tableNames ID,name, new_name. The columns ID and name are 
populated. I would like to update the field new_name from an other table 
tempName (ID,nameUpdated).
The ID between the 2 tables are the same, but I don't have the info for all 
the ID. (so 568 rows in my first table only 550 in my second one)
I saw in the MySQL cookbook different solutions, creating a new table from 
joining the 2 firsts, writing a script to do thisBut this book also 
says that with the subquery in MySQL 4.1 it should be possible through a 
direct query.
As I updated to 4.1, I would like to give it a try.

Here is what I want to do:
update tableName set new_name= (select nameUpdated from tableName,tempName 
where tableName.ID=tempName.ID)

First I have here the subquery error detailed in the manual:
"UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);
It's okay to use a subquery for assignment within an UPDATE statement, 
since subqueries are legal in UPDATE and DELETE statements as well as in 
SELECT statements. However, you cannot use the same table, in this case 
table t1, for both the subquery's FROM clause and the update target. "

and second how can I specify update for each ID?
I tried the same with insert into tableName select nameUpdated from 
tableName, tempName where tableName.ID=tempName.ID, but of course the 
records are inserted at the end of the first table.

I tried to find some help from the manual but saw nothing, and I think this 
is a common problem (as mentionned in the cookbook) but I can't find any 
solution and I'm becoming crazy trying to combine that 2 tables!!!

If somebody could help on this query, but also give me some pointer to a 
good doc/book regarding subqueries?

Thanks a lot for any help.
_
It's fast, it's easy and it's free. Get MSN Messenger today! 
http://www.msn.co.uk/messenger

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

_
Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Server fails to start up after upgrade to 4.1.10

[Heikki Tuuri]

Schnee,
you have probably mixed ibdata files. It is trying to apply a log record to 
a page that is full of zeros.

And, in an upgrade you should shut down mysqld gracefully. It is not a good 
sign if InnoDB has to do a crash recovery after an upgrade.

Regards,
Heikki
.
Hi,
I just upgraded my installation of MySQL 4.1.8 (on Windows XP) to
4.1.10, and the server fails to start up now. The following is a
relevant bit from the error logfile (I've removed the InnoDB page
dumps for readability - they're all 0's, FWIW):
050216 14:42:21  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...
050216 14:42:21  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 983155.
InnoDB: Doing recovery: scanned up to log sequence number 0 983155
InnoDB: Page directory corruption: supremum not pointed to
050216 14:42:21  InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex ... asc ... ;InnoDB: End of page dump
050216 14:42:21  InnoDB: Page checksum 1575996416,
prior-to-4.0.14-form checksum 1371122432
InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0
InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0
InnoDB: Page number (if stored to page already) 0,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page directory corruption: supremum not pointed to
050216 14:42:21  InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex ... asc ... ;InnoDB: End of page dump
050216 14:42:21  InnoDB: Page checksum 1575996416,
prior-to-4.0.14-form checksum 1371122432
InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0
InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0
InnoDB: Page number (if stored to page already) 0,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
050216 14:42:21 [ERROR] mysqld-nt: Got signal 11. Aborting!
050216 14:42:21 [ERROR] Aborting
050216 14:42:21 [Note] mysqld-nt: Shutdown complete
Any ideas why this might happen, or whether this (the segfault, in
particular) may be a bug in MySQL?
Thanks!
--
schnee 

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

Re: Tricky Join with Animal-DB

[Michael Dykman]

SELECT animal, pic from animal AS a, pic as p, animal_pic as ap
WHERE animal = 'penguin'
AND ap.animalid = a.id
and ap.picid = p.id

On Wed, 2005-02-16 at 03:32, Martin Rytz wrote:
> Hi SQL-Community
>  
> I need once more your help, I have a tricky problem with my animal-db.
>  
> I have a table with animals and a table with animal-pics. I have a third
> table. In this table you can see, which pic belongs to which animal:
>  
> CREATE TABLE animal (
>   id int(11) NOT NULL auto_increment,
>   animal varchar(250) NULL,
>   PRIMARY KEY  (id)
> );
> CREATE TABLE pic (
>   id int(11) NOT NULL auto_increment,
>   pic varchar(250) NULL,
>   PRIMARY KEY  (id)
> );
> CREATE TABLE animal_pic (
>   animalid int(11)  NULL ,
>   picid int(11) NULL
> );
>  
> The problem is now, that there can be n pics for an animal!
>  
> With which join can I show the animals with all pics for this animal (even
> if there are more than one pic for an animal)?
>  
> Thank you for you ideas!
>  
> Martin
-- 
 - michael dykman
 - [EMAIL PROTECTED]


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

Re: Mysql kill fails

[Michael Dykman]

On Wed, 2005-02-16 at 06:35, Gerd Pleyer wrote:
> Hi there!
> 
> I use MySQL 4.1.9 on Mac OS X Server 10.3.8
> 
> I use the mysql.server script (in support-files) to shutdown my MySQL Server 
> at system shutdown.
> But the command kill `cat data/my.server.de.pid` fails, so the DB is still 
> running and tables stay open at system shutdown.
> 
> With mysqladmin -uroot -p shutdown command the MySQL I can shutdown.
> 
> Perhaps somebody of you has an idea what the reason could be
> 
> Thanks in advantage
> 
I would first check which which user owns the file data/my.server.de.pid
and incidentally, check it's contents to make sure it actually does
contain the pid of active process.  The effective user at the time the
command "kill `cat data/my.server.de.pid`" is called needs to have
permission to read the file in order to fetch the pid and it needs to
have sufficient permissions to kill the process.  Typically, the
'mysql.server' script is run as root.

-- 
 - michael dykman
 - [EMAIL PROTECTED]


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

RE: INSERT queries hang on amd64

[Donny Simonton]

I know this may be strange, but have you turned on innodb on the box?  Even
if don't use it?  I have 8 amd64 boxes and have never experienced this
problem you are talking about.  They range from single proc to quad proc.
Never this problem but all of them have innodb turned on.  

Turn it on and see what happens, it could be a bug inside the mysql code.

Donny

> -Original Message-
> From: Michel Buijsman [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, February 16, 2005 5:41 AM
> To: mysql@lists.mysql.com
> Subject: INSERT queries hang on amd64
> 
> My problem: INSERT queries hang on amd64.
> 
> This looks a lot like Don MacAskill's bugreport in
> http://bugs.mysql.com/bug.php?id=3483
> Which is listed as closed, but the bug is apparently still there
> so maybe it should be reopened...
> 
> I've just upgraded from 4.1.9 to 4.1.10 (binary) on a dual opteron
> with 8G ram, running Debian 3.1 pure64 gcc-3.4. Also tried 4.0.23
> last week, but that had the same problems. Kernels I've tried are
> 2.6.11-rc1-mm1 and 2.6.11-rc2.
> 
> I'm running 2 boxes in a replication setup, using the amd64 as the
> master (or standalone) gave me pretty much what Don describes in bug
> 3483, insert queries start hanging after a while with no way to kill
> them except kill -9 from the OS, which is a bit rough. ;-)
> 
> The master or standalone setup required quite a bit of poking to get
> it to hang in a test environment, or just a few short hours running
> as a production server. I haven't been able to narrow it down to one
> specific thing, but it appears to have something to do with indexes,
> because it ran fine after dropping all of them.
> 
> I hacked up a test script that's throwing a random selection of selects
> and inserts at it at random intervals between 0 and 2 seconds, from 20
> concurrent threads.
> 
> I could trigger it by running a "repair table" on it while the test
> script was running. The repair thread would wait for its turn, then lock
> everything else out and do its thing, and when it was done the first
> insert after that would hang indefinitely. (Left it hanging over the
> weekend, nothing.)
> 
> Adding "skip-concurrent-insert" fixes the problem, but cripples the
> performance so that's not really an option.
> 
> Then tried to run one of the amd64 boxes as a slave off of a xeon box:
> Same problem, the replication thread hangs within seconds of starting
> the server, and again won't budge for anything except kill -9. This is
> on a server without _any_ other connections except for a processlist.
> 
> I've run out of things to try, so I hope someone here can help...
> 
> 
> my.cnf:
> 
> [mysqld]
> 
> user=mysql
> socket=/tmp/mysql.sock
> skip-locking
> set-variable= key_buffer=2G
> set-variable= table_cache=1024
> set-variable= sort_buffer=16M
> set-variable= read_buffer=16M
> set-variable= max_allowed_packet=10M
> set-variable= thread_cache=64
> set-variable= thread_stack=512K
> set-variable= tmp_table_size=16M
> set-variable= interactive_timeout=600
> set-variable= wait_timeout=600
> set-variable= max_connections=1024
> set-variable= query_cache_type=2
> set-variable= query_cache_size=100M
> set-variable= join_buffer_size=8M
> set-variable= thread_concurrency=4
> set-variable= myisam_sort_buffer_size=64M
> skip-innodb
> 
> --
>  Michel Buijsmantty.nl -- 2dehands.nl
> 
> 
> --
> 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]

subquery help for an update

[mel list_php]

Hi,
I have one table tableNames ID,name, new_name. The columns ID and name are 
populated. I would like to update the field new_name from an other table 
tempName (ID,nameUpdated).
The ID between the 2 tables are the same, but I don't have the info for all 
the ID. (so 568 rows in my first table only 550 in my second one)
I saw in the MySQL cookbook different solutions, creating a new table from 
joining the 2 firsts, writing a script to do thisBut this book also says 
that with the subquery in MySQL 4.1 it should be possible through a direct 
query.
As I updated to 4.1, I would like to give it a try.

Here is what I want to do:
update tableName set new_name= (select nameUpdated from tableName,tempName 
where tableName.ID=tempName.ID)

First I have here the subquery error detailed in the manual:
"UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);
It's okay to use a subquery for assignment within an UPDATE statement, since 
subqueries are legal in UPDATE and DELETE statements as well as in SELECT 
statements. However, you cannot use the same table, in this case table t1, 
for both the subquery's FROM clause and the update target. "

and second how can I specify update for each ID?
I tried the same with insert into tableName select nameUpdated from 
tableName, tempName where tableName.ID=tempName.ID, but of course the 
records are inserted at the end of the first table.

I tried to find some help from the manual but saw nothing, and I think this 
is a common problem (as mentionned in the cookbook) but I can't find any 
solution and I'm becoming crazy trying to combine that 2 tables!!!

If somebody could help on this query, but also give me some pointer to a 
good doc/book regarding subqueries?

Thanks a lot for any help.
_
It's fast, it's easy and it's free. Get MSN Messenger today! 
http://www.msn.co.uk/messenger

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

Re: [MySQL General] INSERT queries hang on amd64

[Michel Buijsman]

On Wed, Feb 16, 2005 at 12:41:04PM +0100, Michel Buijsman wrote:
> My problem: INSERT queries hang on amd64.

Sorry about the multiple mails, mysql.com's mailinglist software
does annoying things with the wrong adresses...

-- 
 Michel Buijsmantty.nl -- 2dehands.nl

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

Re: how to make question that check the last hour

[Michael Dykman]

On Wed, 2005-02-16 at 03:45, Jesper Andersson wrote:
> Hello again,
> 
> I relly new with databases and writing sql-questions.
> 
> But in my db want I to check what new rows have come the last hour.
> 
> the db-table (table name is SUBSCRIBER) have the following columns as follows:
> 
> ID  email created   updated
> 001 [EMAIL PROTECTED]  20050215131034   20050215133401
> 063 [EMAIL PROTECTED]  20050215141034   20050215141201
> 076  [EMAIL PROTECTED]  20050215134500   20050215134556
> 
> The data type of the columns are:
> ID  =VARCHAR(14)
> email   =VARCHAR(255)
> created =VARCHAR(14)
> updated =VARCHAR(14)
> 
> Now I would like to make a sql-question that show which new users have come 
> the last hour, without that I need to edit the question each time I want to 
> ask.
> (please, donr't blame me that the "date columns" (created & updated) not are 
> in date format, I have not done the DB from the beginning, I am only tries to 
> solve some problem in it), I don't  think it is possible to change these 2 
> columns to "DATETIME" format because then will the web program stop working, 
> but I would be greatfull for a work around solution that solve my problem.
> 
> Thanks Alec, for your quick answer but it dosn't helped me much, it list 
> every row any way. I have done my question like this (after your suggestion 
> (or you have to blame me that I'm stupid if it is not correct ;-))).
> 
> select CREATED from SUBSCRIBER where date_sub(now(), interval 1 hour) <= 
> created;
> 
> What do I wrong??
> 
> /J.
> 

The first thing I note is that your 'timestamp' columns are VARCHAR(14)
rather than the TIMESTAMP type, which is designed for exactly this sort
of thing.  To do any type of date/time math on them, they must be
converted to some sort of date time type.  The value '20050215134500'
may look intuitive, but it is quite awkward from a mathematical
perspective. In order to do what you are proposing, you would need to
convert a value from every single row for your query which gets more and
more expensive as the table grows, and an index is of next-to no value. 

If it was a 'TIMESTAMP' this same value could be indexed easily and
compared via the function TIMESTAMPDIFF() 

See http://dev.mysql.com/doc/mysql/en/datetime.html
and http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

 - michael dykman
> > 
> > "Jesper"  wrote on 15/02/2005 13:15:43:
> > 
> > > Hello,
> > >
> > > I relly new with databases and writing sql-questions. But in my 
> > > db want I to check what have new rows have come the last hour.
> > >
> > > the db have I as follow:
> > >
> > > ID  email created   updated 001 
> > > [EMAIL PROTECTED]  20050215131034   20050215133401
> > > 063 [EMAIL PROTECTED]  20050215141034   20050215141201
> > > 76  [EMAIL PROTECTED]  20050215134500   20050215134556
> > >
> > > Now I would like to make a sql-question that show which new users 
> > > have come the last hour, without that I need to edit the question 
> > > each time I want to ask.
> > 
> > select  from  where date_sub(now(), interval 1 hour) <=
> > created ;
> > 
> >  Alec
> 
> 
> 
> -- 
> ___
> Sign-up for Ads Free at Mail.com
> http://promo.mail.com/adsfreejump.htm
-- 
 - michael dykman
 - [EMAIL PROTECTED]


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

Re: Tricky Join with Animal-DB

[Roger Baklund]

Martin Rytz wrote:
[...]
With which join can I show the animals with all pics for this animal (even
if there are more than one pic for an animal)?
SELECT animal,pic
  FROM animal_pic,animal,pic
  WHERE
animal.id = animalid AND
pic.id = picid
This will list all animals which have pictures, with all their pictures. 
If you wanted a special animal, you could just add "AND animal = 'Turtle'".

The above "shorthand" way of writing a join is the same as the following 
more explicit join, note that the WHERE clause is used only for result 
conditions, the join conditions are moved to the ON parts of the joins:

SELECT animal.animal,pic.pic
  FROM animal_pic
  INNER JOIN animal ON
animal.id = animal_pic.animalid
  INNER JOIN pic ON
pic.id = animal_pic.picid
  WHERE
animal = 'Turtle'
If you also wanted animals _without_ pictures in the list, you could use 
a LEFT JOIN:

SELECT animal.animal,pic.pic
  FROM animal_pic
  INNER JOIN animal ON
animal.id = animal_pic.animalid
  LEFT JOIN pic ON
pic.id = animal_pic.picid
Finally, if you also wanted pictures without animals, you could LEFT 
JOIN both tables:

SELECT animal.animal,pic.pic
  FROM animal_pic
  LEFT JOIN animal ON
animal.id = animal_pic.animalid
  LEFT JOIN pic ON
pic.id = animal_pic.picid
http://dev.mysql.com/doc/mysql/en/join.html >
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

INSERT queries hang on amd64

[Michel Buijsman]

Problem: INSERT queries hang on amd64.

This looks a lot like Don MacAskill's bugreport in 
http://bugs.mysql.com/bug.php?id=3483

I've just upgraded from 4.1.9 to 4.1.10 on a dual opteron with 8G ram,
running Debian 3.1. Also tried 4.0.23 last week, but that had the same 
problems. Kernels I've tried are 2.6.11-rc1-mm1 and 2.6.11-rc2.

I'm running 2 boxes in a replication setup, using the amd64 as the
master (or standalone) gave me pretty much what Don describes in bug 
3483, insert queries start hanging after a while with no way to kill 
them except kill -9 from the OS, which is a bit drastic. ;-)

The master or standalone setup required quite a bit of poking to get 
it to happen in a test environment, or just a couple of short hours 
running as a production server. I haven't been able to narrow it down
to a specific thing, but it appears to have something to do with indexes. 

I hacked up a test script that's throwing a random selection of selects 
and inserts at it at random intervals between 0 and 2 seconds, from 20 
concurrent threads.

I could trigger it by running a "repair table" on it while the test
script was running. The repair thread would wait for its turn, then lock 
everything else out, and when it was done the first insert after that 
would hang indefinitely. (Left it hanging over the weekend, nothing.)

Adding "skip-concurrent-insert" fixes the problem, but cripples the 
performance so that's not really an option.  

Then tried to run one of the amd64 boxes as a slave off of a xeon box:
Same problem, the replication thread hangs within seconds of starting 
the server, and again won't budge for anything except kill -9. This is
on a server without _any_ other connections except for a processlist.

I've run out of things to try, so I hope someone here can help...


my.cnf:

[mysqld]

user=mysql
socket=/tmp/mysql.sock
skip-locking
set-variable= key_buffer=2G
set-variable= table_cache=1024
set-variable= sort_buffer=16M
set-variable= read_buffer=16M
set-variable= max_allowed_packet=10M
set-variable= thread_cache=64
set-variable= tmp_table_size=16M
set-variable= interactive_timeout=600
set-variable= wait_timeout=600
set-variable= max_connections=1024
set-variable   = query_cache_type=2
set-variable   = query_cache_size=100M
set-variable= join_buffer_size=8M
set-variable   = thread_concurrency=4
set-variable= myisam_sort_buffer_size=64M
skip-innodb

-- 
 Michel Buijsmantty.nl -- 2dehands.nl

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

INSERT queries hang on amd64

[Michel Buijsman]

My problem: INSERT queries hang on amd64.

This looks a lot like Don MacAskill's bugreport in 
http://bugs.mysql.com/bug.php?id=3483
Which is listed as closed, but the bug is apparently still there 
so maybe it should be reopened...

I've just upgraded from 4.1.9 to 4.1.10 (binary) on a dual opteron 
with 8G ram, running Debian 3.1 pure64 gcc-3.4. Also tried 4.0.23 
last week, but that had the same problems. Kernels I've tried are 
2.6.11-rc1-mm1 and 2.6.11-rc2.

I'm running 2 boxes in a replication setup, using the amd64 as the
master (or standalone) gave me pretty much what Don describes in bug 
3483, insert queries start hanging after a while with no way to kill 
them except kill -9 from the OS, which is a bit rough. ;-)

The master or standalone setup required quite a bit of poking to get 
it to hang in a test environment, or just a few short hours running 
as a production server. I haven't been able to narrow it down to one
specific thing, but it appears to have something to do with indexes,
because it ran fine after dropping all of them.

I hacked up a test script that's throwing a random selection of selects 
and inserts at it at random intervals between 0 and 2 seconds, from 20 
concurrent threads.

I could trigger it by running a "repair table" on it while the test
script was running. The repair thread would wait for its turn, then lock 
everything else out and do its thing, and when it was done the first 
insert after that would hang indefinitely. (Left it hanging over the 
weekend, nothing.)

Adding "skip-concurrent-insert" fixes the problem, but cripples the 
performance so that's not really an option.  

Then tried to run one of the amd64 boxes as a slave off of a xeon box:
Same problem, the replication thread hangs within seconds of starting 
the server, and again won't budge for anything except kill -9. This is
on a server without _any_ other connections except for a processlist.

I've run out of things to try, so I hope someone here can help...


my.cnf:

[mysqld]

user=mysql
socket=/tmp/mysql.sock
skip-locking
set-variable= key_buffer=2G
set-variable= table_cache=1024
set-variable= sort_buffer=16M
set-variable= read_buffer=16M
set-variable= max_allowed_packet=10M
set-variable= thread_cache=64
set-variable= thread_stack=512K
set-variable= tmp_table_size=16M
set-variable= interactive_timeout=600
set-variable= wait_timeout=600
set-variable= max_connections=1024
set-variable= query_cache_type=2
set-variable= query_cache_size=100M
set-variable= join_buffer_size=8M
set-variable= thread_concurrency=4
set-variable= myisam_sort_buffer_size=64M
skip-innodb

-- 
 Michel Buijsmantty.nl -- 2dehands.nl


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

Server fails to start up after upgrade to 4.1.10

[Schneelocke]

Hi,

I just upgraded my installation of MySQL 4.1.8 (on Windows XP) to
4.1.10, and the server fails to start up now. The following is a
relevant bit from the error logfile (I've removed the InnoDB page
dumps for readability - they're all 0's, FWIW):

050216 14:42:21  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...
050216 14:42:21  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 983155.
InnoDB: Doing recovery: scanned up to log sequence number 0 983155
InnoDB: Page directory corruption: supremum not pointed to
050216 14:42:21  InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex ... asc ... ;InnoDB: End of page dump
050216 14:42:21  InnoDB: Page checksum 1575996416,
prior-to-4.0.14-form checksum 1371122432
InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0
InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0
InnoDB: Page number (if stored to page already) 0,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page directory corruption: supremum not pointed to
050216 14:42:21  InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex ... asc ... ;InnoDB: End of page dump
050216 14:42:21  InnoDB: Page checksum 1575996416,
prior-to-4.0.14-form checksum 1371122432
InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0
InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0
InnoDB: Page number (if stored to page already) 0,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
050216 14:42:21 [ERROR] mysqld-nt: Got signal 11. Aborting!

050216 14:42:21 [ERROR] Aborting

050216 14:42:21 [Note] mysqld-nt: Shutdown complete

Any ideas why this might happen, or whether this (the segfault, in
particular) may be a bug in MySQL?

Thanks! 

-- 
schnee

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

Re: How to plan the Tablespace in a huge mysql?

[proace]

I expect the data size is no more then 1.5TB.

Why don't you like to let tablespace auto grow?
Is it performace issue or not?

If I create ten innodb_data_file and each size of innodb_data_file is 50G, 
dose some issues must be take care?
Because the 50G is really very big for a file, I never do it.

Regards,
proace 


On Tue, 15 Feb 2005 07:27:42 -0800, Gary Richardson
<[EMAIL PROTECTED]> wrote:
> My preference is to use innodb_data_file. If everything is InnoDB, I
> would probably create 25G or 50G files until you've created enough to
> hold all the data plus enough for growth. Do you know specifically how
> big the data is?
> 
> I don't like to let my table space autogrow, so I have monitors
> watching the free innodb space. If it gets tight, I manually add more
> space.
> 
> 
> On Tue, 15 Feb 2005 20:25:36 +0800, proace Tsai <[EMAIL PROTECTED]> wrote:
> > Hello:
> >
> > The mysql server is estimated to be as follows,
> > 1. two servers, one is master and the other is slaves (replication)
> > 2. two databases in mysql
> > 3. 513 tables in each database
> > 4. about 300 rows in each table
> > 5. about 2T disk space for each server using SAN Storage
> > 6. backup database periodically
> >
> > The running environment is follows,
> > Server: Dual Intel Xeon 3.2G with 4G DDR2 Memory.
> > OS: FreeBSD 5.3-RELEASE
> > MySQL: 4.1 branch
> > Operation: 70 ~ 80% operation is query (select statement)
> >
> > According to the above terms,
> > how to plan the Tablespace in the mysql server?
> > Using raw devices for the tablespace or innodb_data_file?
> > ( How many Tablespace do I create? )
> > or using innodb_data_file with innodb_file_per_table?
> >
> > Regards,
> > proace.
> >

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

Mysql kill fails

[Gerd Pleyer]

Hi there!

I use MySQL 4.1.9 on Mac OS X Server 10.3.8

I use the mysql.server script (in support-files) to shutdown my MySQL Server at 
system shutdown.
But the command kill `cat data/my.server.de.pid` fails, so the DB is still 
running and tables stay open at system shutdown.

With mysqladmin -uroot -p shutdown command the MySQL I can shutdown.

Perhaps somebody of you has an idea what the reason could be

Thanks in advantage

Best regards
Gerd Pleyer

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

LOAD INDEX INTO CACHE problem

[HMax]

Hello there,

We have a problem with the LOAD INDEX INTO CACHE command which is
supposed to be fixed in version 4.1.10

It may be fixed, but then we don't get the way to make it work.

We want to load all the indexes of one of our big table into the main key cache.
This table is myISAM, and has all sort of indexes, including UNIQUE
AND FULLTEXT.

When we try to load the indexes into cache, we have the following error :
"Indexes use different block size" "Operation Failed"

Now we created a small test table with 2 rows :
A integer, primary key, and a varchar(100) filled 10 times with MD5
values of NOW().
When the varchar row is not indexed, the command works fine, but when
we index it, LOAD INDEX INTO CACHE returns the same error. This means
this is not the FULLTEXT which create the problem.

Key buffer block size is set to 1024.
Tried to change it to 2048 but won't do.

Any help would be greatly appreciated.
Otherwise, I'll post a bug report.

Thank you

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

Re: Time in VBA for Excel

[Dan Wareham]

Hi Dan,
Sorry for causing additional work to getting the test setup, I will take 
your advice should I need to post these type of details again

With regards to your solution, it works perfectly!!! Great thanks for 
putting the time and effort in to help sort this problem. Much appreciated.

I didn't even know you could connect without a DSN in VBA. I'm basically 
teaching myself VBA from doing tutorials and experimenting. I'll take a look 
on the net and see how the DSN-less connection is done. Thanks for the heads 
up.

Kind Regards
[EMAIL PROTECTED] 


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

Re: MySQL license and GPL

[valentin_nils]

Hi Lotte,
(B
(BMySQL's dual License policu is very simple. If you use the GPL license
(Bthan you have to show the source code (modified or not), and include the
(BCopyright info when passing the code on.
(B
(BIf you dont want to do this (or in other words if you want to keep your
(Bmodifications hidden) than you need a commercial license.
(B
(BSo if you use mysql within your commercial product and sell it as a
(Bpackage and want to hide how/if you modified or improved the mysql source
(Bcode than you will need the commercial license. Does that make sense ?
(B
(BBest regards
(B
(BNils Valentin
(BTokyo / Japan
(B
(B> Hello,
(B>
(B> My understanding of the GPL license is that if you link your own software
(B> with GPL-licensed software, then the GPL forces you to release the whole
(B> thing under GPL. I am a bit confused that MySQL seems to require that even
(B> stand-alone applications are released as open source.
(B>
(B> Does MySQL have a more strict interpretation of the GPL? Or is the license
(B> on the MySQL GPL + more terms? Or?
(B>
(B> BR Lotte
(B>
(B>
(B>
(B> --
(B> MySQL General Mailing List
(B> For list archives: http://lists.mysql.com/mysql
(B> To unsubscribe:
(B> http://lists.mysql.com/[EMAIL PROTECTED]
(B>
(B>
(B
(B
(B-- 
(BMySQL General Mailing List
(BFor list archives: http://lists.mysql.com/mysql
(BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySQL license and GPL

[Lotte Mygind \(AH/LMD\)]

Hello,

My understanding of the GPL license is that if you link your own software with 
GPL-licensed software, then the GPL forces you to release the whole thing under 
GPL. I am a bit confused that MySQL seems to require that even stand-alone 
applications are released as open source. 

Does MySQL have a more strict interpretation of the GPL? Or is the license on 
the MySQL GPL + more terms? Or?

BR Lotte



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

how to make question that check the last hour

[Jesper Andersson]

Hello again,

I relly new with databases and writing sql-questions.

But in my db want I to check what new rows have come the last hour.

the db-table (table name is SUBSCRIBER) have the following columns as follows:

ID  email created   updated
001 [EMAIL PROTECTED]  20050215131034   20050215133401
063 [EMAIL PROTECTED]  20050215141034   20050215141201
076  [EMAIL PROTECTED]  20050215134500   20050215134556

The data type of the columns are:
ID  =VARCHAR(14)
email   =VARCHAR(255)
created =VARCHAR(14)
updated =VARCHAR(14)

Now I would like to make a sql-question that show which new users have come the 
last hour, without that I need to edit the question each time I want to ask.
(please, donr't blame me that the "date columns" (created & updated) not are in 
date format, I have not done the DB from the beginning, I am only tries to 
solve some problem in it), I don't  think it is possible to change these 2 
columns to "DATETIME" format because then will the web program stop working, 
but I would be greatfull for a work around solution that solve my problem.

Thanks Alec, for your quick answer but it dosn't helped me much, it list every 
row any way. I have done my question like this (after your suggestion (or you 
have to blame me that I'm stupid if it is not correct ;-))).

select CREATED from SUBSCRIBER where date_sub(now(), interval 1 hour) <= 
created;

What do I wrong??

/J.

> 
> "Jesper"  wrote on 15/02/2005 13:15:43:
> 
> > Hello,
> >
> > I relly new with databases and writing sql-questions. But in my 
> > db want I to check what have new rows have come the last hour.
> >
> > the db have I as follow:
> >
> > ID  email created   updated 001 
> > [EMAIL PROTECTED]  20050215131034   20050215133401
> > 063 [EMAIL PROTECTED]  20050215141034   20050215141201
> > 76  [EMAIL PROTECTED]  20050215134500   20050215134556
> >
> > Now I would like to make a sql-question that show which new users 
> > have come the last hour, without that I need to edit the question 
> > each time I want to ask.
> 
> select  from  where date_sub(now(), interval 1 hour) <=
> created ;
> 
>  Alec



-- 
___
Sign-up for Ads Free at Mail.com
http://promo.mail.com/adsfreejump.htm


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

Tricky Join with Animal-DB

[Martin Rytz]

Hi SQL-Community
 
I need once more your help, I have a tricky problem with my animal-db.
 
I have a table with animals and a table with animal-pics. I have a third
table. In this table you can see, which pic belongs to which animal:
 
CREATE TABLE animal (
  id int(11) NOT NULL auto_increment,
  animal varchar(250) NULL,
  PRIMARY KEY  (id)
);
CREATE TABLE pic (
  id int(11) NOT NULL auto_increment,
  pic varchar(250) NULL,
  PRIMARY KEY  (id)
);
CREATE TABLE animal_pic (
  animalid int(11)  NULL ,
  picid int(11) NULL
);
 
The problem is now, that there can be n pics for an animal!
 
With which join can I show the animals with all pics for this animal (even
if there are more than one pic for an animal)?
 
Thank you for you ideas!
 
Martin