Re: Recommendations on new hardware

[Bruce Dembecki]

Yes... OS X 10.4 with a 32 but MySQL binary is stable... it is the  
combination of 64 bit OS (Tiger), and the 64 bit MySQL binary, and  
accessing more than 2Gbytes of memory within the mysqld process that  
blows up the machine. You can also run the 64 bit binary but keep the  
memory allocation below 2Gbytes (but thats pretty pointless).


64 bit is important if you have large data sets, the more you can  
keep in memory instead of relying on disk access, the better things  
run... if your regularly accessed data isn't so large, no issue  
anyway... if you don't have more than 2Gbytes of memory to get at,  
also no problem.


And of course until it is worked out, stay within the limis of 32 bit  
and you don't have a problem :-)


Best Regards, Bruce

On Sep 7, 2005, at 8:51 PM, Scott Haneda wrote:


on 9/7/05 8:42 PM, Bruce Dembecki at [EMAIL PROTECTED] wrote:



Yeah, 64 bit isn't working... we can set the memory partition for
InnoDB to some big number, like say 10G or more (on the 16G Xserves),
and it will launch, so it has 64 bit OS and 64 bit MySQL Binaries...
We get past the first hurdle, but in practise it just doesn't run...
It works fine until InnoDB gets to more than 2GByte of memory it's
actually truing to use (as opposed to reserving when it launches),
and then the server locks up (OS level.. not a mysqld crash) After 5
minutes the watchdog timer kicks in and reboots the machine.

So I don't know if it's an Apple issue or a MySQL issue... the 64 bit
MySQL binary does it, the 64 bit binary I made with mySQL source does
it, 10.4.0 does it, 10.4.1 does it, 10.4.2 does it... etc...

I'm sure it's something we will get resolved, just for now we can't
run 64 bit. So we'll keep working with both our Apple and MySQL
contacts to get each of them trying to find the problem... but (being
the open minded people we are) we'll also take a look at Yellow Dog
and see what's involved in getting Linux up and making a 64 bit MySQL
Binary to run under Yellow Dog.



Sorry if this is boneheaded here, but are there non g4 bit variants  
you can

run on a G5 if you just want the stability until this is worked out?
--
-
Scott HanedaTel: 415.898.2602
 Novato, CA U.S.A.



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






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

Re: Questions about last_insert_id() instead of @@IDENTITY

[Remo Tex]

http://dev.mysql.com/doc/mysql/en/insert-select.html
There are 2 forms of INSERT INTO statement
1. INSERT INTO tbl_name (...) VALUES(...);
2. INSERT INTO tbl_name (...) SELECT ...;
there's no : INSERT INTO tbl_name (...) VALUES(...) SELECT ...;
its either ... or in MySQL i.e. aither provide values or get then form 
SELECT ... but not both in same INSERT ...


so indeed:
'INSERT INTO rps_names (nameid, name) VALUES (NULL, "Ryan Smithland") 
SELECT LA'

 - is invalid query!

Ryan Stille wrote:

I am migrating from MS SQL Server to MySQL 4.1.

I have code in my ColdFusion/SQL Server application that went something
like this:

SET NOCOUNT ON
INSERT INTO TABLE ()
SELECT @@IDENTITY AS adid
SET NOCOUNT OFF


That whole block was processed in coldfusion as a single query.  I gave
it a name of "insertquery", and I can get the ID back as
insertquery.adid.

I am having trouble getting the same functionality with MySQL.

INSERT INTO TABLE (...)
SELECT LAST_INSERT_ID() as adid


I get this MySQL error:
You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'INSERT
INTO rps_names (nameid, name) VALUES (NULL, "Ryan Smithland") SELECT LA'
at line 2

With MSSQL the trick was the NO COUNT.  That told the database server
not to return a count of the rows affected.  Is there something similar
I need to do in MySQL to get this to work?

Thanks,
-Ryan



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

Re: Recommendations on new hardware

[Bruce Dembecki]

And one other thing... our smallest load database server was a pair  
of G4 XServes, running about 300 queries per second, taking 5%CPU on  
the top display (which on OS X is 5% of one CPU) We ran the same  
load on a pair of Sun V440 Quad processor with 16Gbytes of memory and  
it used 30 - 50% CPU (which on Solaris is the percentage of all the  
CPUs combined). Even switching back to a 32 bit Solaris binary we had  
major performance loss compared to the Macs... the bus speed is an  
issue on the Sun boxes... fast processors don't mean anything when  
they are tied to a sub 200MHz system bus.


Ignoring the time to connect issue in the Anandtech articles because  
it simply doesn't affect us, the Mac boxes really perform very very  
well for us (which is the only benchmark that means anything to us).


On the 64 bit side if you aren't planning on giving MySQL more than  
2GBytes of memory, it's not an issue to start with.


Best Regards, Bruce


On Sep 7, 2005, at 4:23 PM, Scott Haneda wrote:



on 9/7/05 2:40 PM, Bruce Dembecki at [EMAIL PROTECTED] wrote:




We're happy with our Mac based MySQL servers in many respects. We've
got some 64 bit issues that are causing a little grief, so we're
looking at our options... Obviously working with Apple and MySQL to
determine the real reason for the 64 bit failures will be high on  
the

list. At the same time we're going to take a look at Yellow Dog's
Linux for Power PC and then we can do a direct comparison and see
what differences a change in Operating System makes to us in real
life, not just what effect it has on Anandtech's "benchmarks", which
don't represent how we work at all. Once we've looked at Yellow Dog
we'll be happy to talk about our experiences there too.




Ok, you are almost selling me on getting an Xserve, can you tell  
me a bit
about the 64bit issues and how they affect me?  I have someone who  
may just
donate my a xserve, one of the older ones, but still, not a bad  
piece of

hardware at all.
--
-
Scott HanedaTel: 415.898.2602
 Novato, CA U.S.A.



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







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






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

Re: Recommendations on new hardware

[Scott Haneda]

on 9/7/05 8:42 PM, Bruce Dembecki at [EMAIL PROTECTED] wrote:

> Yeah, 64 bit isn't working... we can set the memory partition for
> InnoDB to some big number, like say 10G or more (on the 16G Xserves),
> and it will launch, so it has 64 bit OS and 64 bit MySQL Binaries...
> We get past the first hurdle, but in practise it just doesn't run...
> It works fine until InnoDB gets to more than 2GByte of memory it's
> actually truing to use (as opposed to reserving when it launches),
> and then the server locks up (OS level.. not a mysqld crash) After 5
> minutes the watchdog timer kicks in and reboots the machine.
> 
> So I don't know if it's an Apple issue or a MySQL issue... the 64 bit
> MySQL binary does it, the 64 bit binary I made with mySQL source does
> it, 10.4.0 does it, 10.4.1 does it, 10.4.2 does it... etc...
> 
> I'm sure it's something we will get resolved, just for now we can't
> run 64 bit. So we'll keep working with both our Apple and MySQL
> contacts to get each of them trying to find the problem... but (being
> the open minded people we are) we'll also take a look at Yellow Dog
> and see what's involved in getting Linux up and making a 64 bit MySQL
> Binary to run under Yellow Dog.

Sorry if this is boneheaded here, but are there non g4 bit variants you can
run on a G5 if you just want the stability until this is worked out?
-- 
-
Scott HanedaTel: 415.898.2602
 Novato, CA U.S.A.



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

Re: Recommendations on new hardware

[Bruce Dembecki]

Yeah, 64 bit isn't working... we can set the memory partition for  
InnoDB to some big number, like say 10G or more (on the 16G Xserves),  
and it will launch, so it has 64 bit OS and 64 bit MySQL Binaries...  
We get past the first hurdle, but in practise it just doesn't run...  
It works fine until InnoDB gets to more than 2GByte of memory it's  
actually truing to use (as opposed to reserving when it launches),  
and then the server locks up (OS level.. not a mysqld crash) After 5  
minutes the watchdog timer kicks in and reboots the machine.


So I don't know if it's an Apple issue or a MySQL issue... the 64 bit  
MySQL binary does it, the 64 bit binary I made with mySQL source does  
it, 10.4.0 does it, 10.4.1 does it, 10.4.2 does it... etc...


I'm sure it's something we will get resolved, just for now we can't  
run 64 bit. So we'll keep working with both our Apple and MySQL  
contacts to get each of them trying to find the problem... but (being  
the open minded people we are) we'll also take a look at Yellow Dog  
and see what's involved in getting Linux up and making a 64 bit MySQL  
Binary to run under Yellow Dog.


Best Regards, Bruce

On Sep 7, 2005, at 4:23 PM, Scott Haneda wrote:


on 9/7/05 2:40 PM, Bruce Dembecki at [EMAIL PROTECTED] wrote:



We're happy with our Mac based MySQL servers in many respects. We've
got some 64 bit issues that are causing a little grief, so we're
looking at our options... Obviously working with Apple and MySQL to
determine the real reason for the 64 bit failures will be high on the
list. At the same time we're going to take a look at Yellow Dog's
Linux for Power PC and then we can do a direct comparison and see
what differences a change in Operating System makes to us in real
life, not just what effect it has on Anandtech's "benchmarks", which
don't represent how we work at all. Once we've looked at Yellow Dog
we'll be happy to talk about our experiences there too.



Ok, you are almost selling me on getting an Xserve, can you tell me  
a bit
about the 64bit issues and how they affect me?  I have someone who  
may just
donate my a xserve, one of the older ones, but still, not a bad  
piece of

hardware at all.
--
-
Scott HanedaTel: 415.898.2602
 Novato, CA U.S.A.



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






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

Re: Recommendations on new hardware

[Larry Lowry]

For hardware we are just assembling generic Athlon 64 boxes.
I just put together two Dual core A64 4400+ boxes as web
servers, running them as a two node cluster.  


My new DB box is a Dual core 4400+ with 4gigs of memory
and 10k sata drives.  I know some folks have had trouble with
the 10k drives but they have been working really well for us.

As far as web servers go (for example) we were able to replace
a dual xeon 3gig box and scsi drives with a $700 Athlon 64 3500+ 
using 7200rpm sata drives.  The Athlon system serves web pages

at least 1.5 times faster than the xeon box did. The IO is so much
better with the Athlon 64 boxes.  At this time we are getting 
enough performance per dollar that we are not going to go to

the Opterons, although I would like to try a few.  ;-))

Larry Lowry


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

Re: Recommendations on new hardware

[Daniel Kasak]

Scott Haneda wrote:


Hello, currently I run mysql 3.x on Mac OS X 10.3.

I have about as much ram in the machine as I can, and have tunes it the best
I can, however, I still see the performance drop pretty badly at times.

After reading this:
 I suspect OS X is just
not going to cut it.

I have a few years in a shell on OS X, but that's about it, I can get
around, compile stuff, etc, but I am in no way a master.

Part of me leans on getting into x86 BSD of some sort, so at least I am more
comfortable with it as it is going to be similar to OS X.  However, if
someone can tell me a good Linux distro to go with, I will use that.

Mainly, I would like to know what hardware to be looking at, something in
the rack mount style, a 1U would be nice.  Bigger if need be.

I just set up a LAMP system on a SunFire V20z dual 248 Opteron (2.2GHz), 
and the performance is amazing. I would have no problem with setting up 
a white-box Opteron system for an in-house server, but this customer 
wanted a big-name retailer behind the server, and Sun's hardware is very 
nice, while being fairly priced.


I would first try running Linux on your PPC system, and seeing if you 
get enough improvement out of it to keep things together for now - it 
will most likely give you some breathing space. Otherwise I'd go with a 
dual Opteron system like the above SunFire. I personally find people's 
decision to continue using Intel-based systems quite puzzling 
considering AMD are clearly the leaders in 64-bit computing now. The 
fact that Intel are moving to EMT-64 ( AMD's x86-64 spec, minus any 
mention of AMD of course ) drives this point home. AMD's superior bus & 
memory architecture also put it way above anything Intel have on offer - 
particularly in SMP systems, no matter what speed EMT-64 processor you 
compare against.


As for distros, I use Gentoo. I realise this will cause some to roll 
their eyes back and laugh. I stand by my recommendation anyway, for the 
following ( brief ) points:


- Managability
Portage gives you a very simple way of installing a multitude of 
packages, and keeping them up to date. I've managed 4 production Gentoo 
servers for the past 3 years now, and I've been very pleased with the 
ease of upgrades. Critics will say that source-only distros are not 
suitable for servers. My experience says the opposite. I've not had the 
same issues keeping my Gentoo systems current as I've had previously 
with Slackware, Suse and RedHat servers.


- Good x86-64 ( and other architecture ) support
Gentoo was among the 1st to support x86-64, and is now very stable on 
this architecture. As a side note, their PPC support is also very 
impressive. I'm running Gentoo Linux as my primary OS on my Apple 
Powerbook, and have had nothing but blissfull success setting everything up.


- Excellent online forum and community
The Gentoo forums ( forums.gentoo.org ) is the best Linux community I've 
encountered - for tech support and just for general friendliness.


The main hurdle for setting up a Gentoo server will be the installation 
process. It's not too difficult, but it's certainly not as fast as 
setting up other distros. If you're willing & able to put in a couple of 
hours for reading and configuring things, it will be well worth it.


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

Total newb at performance tuning mysql

[Scott Haneda]

Unless I am totally off base here, the mysql docs tell very little in
regards to how to performance tune mysql.

So far I have found this article to help:

But it still leaves me with a ton of questions.

For starters, and don't laugh, I just installed mysql and let it run,
started adding sites to it and such.  The machine has 1 gig of ram in in,
but at times, I could ask it were a little snappier.  It is a 1.2Ghz
machine.

First thing I did last night was to try to get a hnalde on this.  From what
I can tell, I have no my.cnf file in place, so there must be some default
settings that are compiled in.  I set up a cron job to run the following:

show variables like 'table_cache'
show status like 'open%_tables'

I have been running this once a minute for a day now, how long should I run
it to get an idea of what I need to change to better suit my servers load
usage?

First entry after a mysql restart:
Wed Sep 7 03:18:00 PDT 2005
table_cache 64
Open_tables 64 Opened_tables 1050
---

Last entry as of now:
---
Wed Sep 7 16:27:01 PDT 2005
table_cache 64
Open_tables 64 Opened_tables 4407

So it looks like Opened_tables is going to increase forever at the rate I
have it, which I am guessing is not a good thing, but not sure what I need
to do to fix this.

I am really looking for some pretty detailed docs on exactly what I can do
in my.cnf to make this work out better.

Thanks again, and if there are any questions that would help me get a better
answer, please let me know.
-- 
-
Scott HanedaTel: 415.898.2602
 Novato, CA U.S.A.



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

Re: Recommendations on new hardware

[Scott Haneda]

on 9/7/05 2:40 PM, Bruce Dembecki at [EMAIL PROTECTED] wrote:

> We're happy with our Mac based MySQL servers in many respects. We've
> got some 64 bit issues that are causing a little grief, so we're
> looking at our options... Obviously working with Apple and MySQL to
> determine the real reason for the 64 bit failures will be high on the
> list. At the same time we're going to take a look at Yellow Dog's
> Linux for Power PC and then we can do a direct comparison and see
> what differences a change in Operating System makes to us in real
> life, not just what effect it has on Anandtech's "benchmarks", which
> don't represent how we work at all. Once we've looked at Yellow Dog
> we'll be happy to talk about our experiences there too.

Ok, you are almost selling me on getting an Xserve, can you tell me a bit
about the 64bit issues and how they affect me?  I have someone who may just
donate my a xserve, one of the older ones, but still, not a bad piece of
hardware at all.
-- 
-
Scott HanedaTel: 415.898.2602
 Novato, CA U.S.A.



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

Re: Recommendations on new hardware

[Scott Haneda]

on 9/7/05 11:11 AM, Chris Martin at [EMAIL PROTECTED] wrote:

> I'd definitely try those first before forking out 2 grand.

Its not that I really have a choice, I do not have a spare mac around, so I
need new hardware no matter what.  To move OS's on a live mysql server and
then get the new one up, and not have downtime, would be a bear.

I am also running an older version of mysql, and the new version is going to
require a few hundres sites have the timestamp() functions updated, since I
am pretty sure there was a change in mysql that makes me need to do that.

I can up my budget to 3000.00 if it helps, that's pushing it though.  I
really want 1U or slightly larger, I don't want a tower as I have a rack and
they just waste space, I have 42U and a tower takes up 6 of those.  Rack
mounts are the way to go, however, apples xserve kit seems pricey compared
to the offerings from others.
-- 
-
Scott HanedaTel: 415.898.2602
 Novato, CA U.S.A.



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

Re: Locking Methods

[Dan Nelson]

In the last episode (Sep 07), Rich said:
> I started a different thread on this, but then I realized I might not
> have to use InnoDB for this.
> 
> Let me explain what I wish to achieve.
> 
> I want to create records in an established table.  I then want them
> locked (either by locking the whole table or by individual record) so
> that they cannot be updated or deleted.

Permanently?  Would revoking the UPDATE and DELETE privileges on the
table (but keeping INSERT and SELECT) suffice?  Locks are purely
session-based and disappear when the client that issued them exits.
 
> I am concerned that locking a table won't allow me to add new
> records.  I also need the records fully viewable.
> 
> Which table format should I choose, and how do I implement this? 
> I've reviewed some of the alternatives, and they got all confusing to
> me. 15.11.3 InnoDB and Transaction Isolation Level indicates that
> READ COMMITTED is what I should be looking for, but it refers to an
> index that I'm unaware of, as nothing is indexed.

-- 
Dan Nelson
[EMAIL PROTECTED]

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

Re: Recommendations on new hardware

[Bruce Dembecki]

On Sep 6, 2005, at 11:09 PM, Scott Haneda wrote:


After reading this:
 I suspect OS X  
is just

not going to cut it.



So while I think it is beneficial to be open to new things at all  
times, there are as always two sides to any story. The Anandtech  
articles stem back to a test they developed which essentially  
measures how long it takes to open and close a connection to MySQL...  
and then relating that and pretty much that alone to scalability.


Speaking as someone who knows about scale and deals in 100M+  
dynamically created page views month after month let me say that  
opening and closing a connection to MySQL is NOT a good measurement  
of this. That said we use MySQL on our Mac servers to serve each and  
every one of those dynamically created pages.


Anandtech says over several trials they have tried various MySQL  
config settings to fix or improve things, of course they list the  
settings they have tried. Guys... you are taking issue with time to  
establish a thread... try the thread cache (duh)!!


More importantly... If you are really focussed on scale you need to  
look at many parts of your technology, not just your database  
servers. One of the issues demonstrated by this review is that PHP  
which opens a connection each time you access the database may not be  
the best way to go if you are a high volume site, but moreover use  
something that utilizes a connection pool, such as JDBC (which of  
course would require you use Java for your application, rather than  
PHP). I'm not suggesting that there is something wrong with PHP or  
that everyone should code in Java... I'm just saying there are many  
technology choices that go into determining what will work and how  
well it will scale, and this Anandtech article focuses on one that  
doesn't apply to many many people.


Now IF you are relying on PHP and IF you are receiving high volume  
and IF your MySQL server isn't performing well enough, then you may  
be in the position that the Anandtech article applies. One solution  
(the one they are seemingly presenting) may be to change the database  
server's hardware platform. But it's not the only solution, and you  
should look beyond this one issue to make sure you are choosing  
something appropriate to your actual needs.


We're happy with our Mac based MySQL servers in many respects. We've  
got some 64 bit issues that are causing a little grief, so we're  
looking at our options... Obviously working with Apple and MySQL to  
determine the real reason for the 64 bit failures will be high on the  
list. At the same time we're going to take a look at Yellow Dog's  
Linux for Power PC and then we can do a direct comparison and see  
what differences a change in Operating System makes to us in real  
life, not just what effect it has on Anandtech's "benchmarks", which  
don't represent how we work at all. Once we've looked at Yellow Dog  
we'll be happy to talk about our experiences there too.


In general though, we will say that the XServe G5/OS X combo works  
better for us in most everything than our Sun Servers running  
Solaris our XServe's handle twice the load of Sun V240s on our  
Java based web applications, for half the price. But we're also  
willing to look at a Linux variant to see if it helps us in database  
land when teamed with 64 bit... I have a couple of 16Gbyte ram  
XServes I want to see how hard I can push in database land. In  
database land our G5 Xserves with 8Gbyte so far outperformed a Sun  
V440 with 4 processors and 16Gbytes of ram it wasn't funny... so OS X  
and Mac boxes isn't such a terrible thing


Give some thought to Yellow Dog... if you're going Linux anyway why  
not work with the hardware you have, $89 for the OS with installation  
support seems less dramatic than switching your whole hardware  
platform... but also give some thought to how you are using MySQL and  
where the performance issues really are, not just the single issue  
that one set of tests keeps focussing on (this is the third in a  
series of articles from Anandtech focussed on opening connections to  
a MySQL server on OS X - there is so much to performance than this  
one piece of the puzzle, and there are plenty of solutions which  
don't mean throwing out your hardware).


Best Regards, Bruce

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

RE: Questions about last_insert_id() instead of @@IDENTITY

[Ryan Stille]

> it might be best to ask this in the cf forums, however did
> you try to separate the statements with a ; ? I haven't tried
> 2 query statements for MySQL in cfquery...yet ;-) Also I
> believe MySQL 3.23+ supports @@identity

Yes I've tried semicolons after the statements, and I've tried setting
autocommit = 0 at the beginning also.

I will ask on a CF list, because they may have run into this issue.  But
really I think it's a MySQL question.

Thanks,
-Ryan


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

Re: Questions about last_insert_id() instead of @@IDENTITY

[Tim]

On 9/7/2005 1:17 PM Ryan Stille intelligently wrote:
> I am migrating from MS SQL Server to MySQL 4.1.
> 
> I have code in my ColdFusion/SQL Server application that went something
> like this:
> 
> SET NOCOUNT ON
> INSERT INTO TABLE ()
> SELECT @@IDENTITY AS adid
> SET NOCOUNT OFF
> 
> 
> That whole block was processed in coldfusion as a single query.  I gave
> it a name of "insertquery", and I can get the ID back as
> insertquery.adid.
> 
> I am having trouble getting the same functionality with MySQL.
> 
> INSERT INTO TABLE (...)
> SELECT LAST_INSERT_ID() as adid
> 
> 
> I get this MySQL error:
> You have an error in your SQL syntax; check the manual that corresponds
> to your MySQL server version for the right syntax to use near 'INSERT
> INTO rps_names (nameid, name) VALUES (NULL, "Ryan Smithland") SELECT LA'
> at line 2
> 
> With MSSQL the trick was the NO COUNT.  That told the database server
> not to return a count of the rows affected.  Is there something similar
> I need to do in MySQL to get this to work?
> 
> Thanks,
> -Ryan
> 

it might be best to ask this in the cf forums, however did you try to
separate the statements with a ; ? I haven't tried 2 query statements
for MySQL in cfquery...yet ;-) Also I believe MySQL 3.23+ supports
@@identity

HTH, Tim

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

Locking Methods

[Rich]

Hi there.

I started a different thread on this, but then I realized I might not have
to use InnoDB for this.

Let me explain what I wish to achieve.

I want to create records in an established table.  I then want them locked
(either by locking the whole table or by individual record) so that they
cannot be updated or deleted.

That's it.  

I am concerned that locking a table won't allow me to add new records.  I
also need the records fully viewable.

Which table format should I choose, and how do I implement this?  I've
reviewed some of the alternatives, and they got all confusing to me.
15.11.3 InnoDB and Transaction Isolation Level indicates that READ COMMITTED
is what I should be looking for, but it refers to an index that I'm unaware
of, as nothing is indexed.

Any leadership appreciated.

Cheers



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

Questions about last_insert_id() instead of @@IDENTITY

[Ryan Stille]

I am migrating from MS SQL Server to MySQL 4.1.

I have code in my ColdFusion/SQL Server application that went something
like this:

SET NOCOUNT ON
INSERT INTO TABLE ()
SELECT @@IDENTITY AS adid
SET NOCOUNT OFF


That whole block was processed in coldfusion as a single query.  I gave
it a name of "insertquery", and I can get the ID back as
insertquery.adid.

I am having trouble getting the same functionality with MySQL.

INSERT INTO TABLE (...)
SELECT LAST_INSERT_ID() as adid


I get this MySQL error:
You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'INSERT
INTO rps_names (nameid, name) VALUES (NULL, "Ryan Smithland") SELECT LA'
at line 2

With MSSQL the trick was the NO COUNT.  That told the database server
not to return a count of the rows affected.  Is there something similar
I need to do in MySQL to get this to work?

Thanks,
-Ryan


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

Re: Getting Record Count w/o doing 2 queries [sorry, left out one major piece of info]

[SGreen]

Then eliminate the subquery and SQL_CALC_FOUND_ROWS will be an option for 
you again. If you would like some help refactoring your query, I 
volunteer. If I can't help, I know there are several others on the list 
just as capable as I (or more so).

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


pow <[EMAIL PROTECTED]> wrote on 09/07/2005 02:33:09 PM:

> Very sorry for having troubled all regarding the seemingly stupid rtfm 
> garnering request for help.
> 
> But in my attempt to make the question as simple and succinct as 
> possible, i forgot to state one EXTREMELY important chunk of 
information.
> 
> I can't use SQLCALCFOUNDROWS because there is a subqry in my statement.
> SQLCALCFOUNDROWS does not like being in the subqry, always gives me an 
> error (it runs fine as a standalone query though)
> 
> The total row count has to be from the subqry, because thats where the 
> LIMIT is.
> 
> Many thanks and apologies.
> 
> [EMAIL PROTECTED] wrote:
> 
> >
> > RTFM - It is online, it has an index, and it is searchable: 
> > http://dev.mysql.com/doc/mysql/en/select.html (hint: look for the 
> > phrase "how many rows" on this page)
> >
> > If you looked in the index for "rows", you would find the function 
> > ROW_COUNT() (which is close but not exactly what you wanted). However, 

> > by clicking on the link next to that function, you wind up at another 
> > page that also has the answer you seek.
> >
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> >
> >
> >
> > pow <[EMAIL PROTECTED]> wrote on 09/07/2005 06:15:12 AM:
> >
> > > Hi everyone, Im executing the following query:
> > >
> > > SELECT *
> > > FROM
> > > table1
> > > WHERE
> > > table1.field1 = 'A' AND table1.field2 = 'B'
> > > LIMIT 0,10
> > >
> > > I also need to get the total record count for the above query, but
> > > without the limit clause (limit is for pagination purposes)
> > > Is there any way to extract this total record count without the need 
to
> > > do another query?
> > >
> > > Right now I am using another query to get the total record count:
> > >
> > > SELECT COUNT(*)
> > > FROM
> > > table1
> > > WHERE
> > > table1.field1 = 'A' AND table1.field2 = 'B'
> > >
> > >
> > > In reality, my tables are very large, and involve joins, so 
executing
> > > the query TWICE is taking its toll on the server.
> > > Thanks!
> > > Pow
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
> > >
> 
> 

Re: Getting Record Count w/o doing 2 queries [sorry, left out one major piece of info]

[pow]

Very sorry for having troubled all regarding the seemingly stupid rtfm 
garnering request for help.


But in my attempt to make the question as simple and succinct as 
possible, i forgot to state one EXTREMELY important chunk of information.


I can't use SQLCALCFOUNDROWS because there is a subqry in my statement.
SQLCALCFOUNDROWS does not like being in the subqry, always gives me an 
error (it runs fine as a standalone query though)


The total row count has to be from the subqry, because thats where the 
LIMIT is.


Many thanks and apologies.

[EMAIL PROTECTED] wrote:



RTFM - It is online, it has an index, and it is searchable: 
http://dev.mysql.com/doc/mysql/en/select.html (hint: look for the 
phrase "how many rows" on this page)


If you looked in the index for "rows", you would find the function 
ROW_COUNT() (which is close but not exactly what you wanted). However, 
by clicking on the link next to that function, you wind up at another 
page that also has the answer you seek.


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



pow <[EMAIL PROTECTED]> wrote on 09/07/2005 06:15:12 AM:

> Hi everyone, Im executing the following query:
>
> SELECT *
> FROM
> table1
> WHERE
> table1.field1 = 'A' AND table1.field2 = 'B'
> LIMIT 0,10
>
> I also need to get the total record count for the above query, but
> without the limit clause (limit is for pagination purposes)
> Is there any way to extract this total record count without the need to
> do another query?
>
> Right now I am using another query to get the total record count:
>
> SELECT COUNT(*)
> FROM
> table1
> WHERE
> table1.field1 = 'A' AND table1.field2 = 'B'
>
>
> In reality, my tables are very large, and involve joins, so executing
> the query TWICE is taking its toll on the server.
> Thanks!
> Pow
>
> --
> 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: Recommendations on new hardware

[Chris Martin]

On 9/7/05, Brent Baisley <[EMAIL PROTECTED]> wrote:
> As you've probably read in the article, the hardware isn't too bad,
> it's OS X that is slowing things down.

Interesting article. Helped me make my decision between OS X, and
Debian on our xServes. It appears bypassing the gui, and running a
straight console won't even help if the problem is with all the
wrappers.

> I would first go the free route. Download YellowDog Linux and install
> that on your current Mac hardware. That will give you a big boost
> when the load starts to climb. I've installed YellowDog on an old Mac
> (350Mhz) and it was pretty painless. If you can get around in the
> terminal on the Mac, you should do fine under Linux.

In addition to Yellow Dog. Lots of other distros offer ppc versions. I
use Debian, but Gentoo, Suse, and others offer them too.
I'd definitely try those first before forking out 2 grand. It's a good
time for Debian. Sarge was released not too long ago, so the stable
packages are no longer ancient, and installing mysql is as easy as
running
apt-get install mysql-server-4.1
Of course you might want to configure it some, but they provide decent
defaults. If you've ever used Fink on OS X, you might already be
familiar with apt-get.

-- 
Chris Martin
Web Developer
Open Source & Web Standards Advocate
http://www.chriscodes.com/

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

Re: Recommendations on new hardware

[Mark Addison]

On Wed, 2005-09-07 at 08:30 -0600, Cory Robin wrote:
> Brent Baisley wrote:
> 
> > If you do go the new hardware route, I wouldn't go with SCSI is you  
> > only have $2K to spend. S-ATA2 based drives would give you similar  
> > performance to SCSI, but at a big cost savings. SCSI's big  
> > performance advantage was in command queueing which SATA2 drives now  
> > have.
> >

> I agree with the S-ATA2 recommendation.

The SCSI choice is not purely about performance. In my experience SCSI
drives are much more reliable than IDE or SATA. You'll save money to
start with but may spend it later in replacement drives and downtime.
Its a tricky choice with only 2k to spend...

mark
--

This email (and any attachments) is intended solely for the individual(s) 
to whom addressed. 
It may contain confidential and/or legally privileged information. 
Any statement or opinions therein are not necessarily those of ITN unless 
specifically stated. 
Any unauthorised use, disclosure or copying is prohibited. 
If you have received this email in error, please notify the sender and delete 
it from your system. 
Security and reliability of the e-mail and attachments are not guaranteed. 
You must take full responsibility for virus checking.

Please note that to ensure regulatory compliance and for the protection of our 
clients and business, 
we may monitor and read e-mails sent to and from our server(s).


Independent Television News Limited,

Registered No. 548648 England,

VAT Reg. No: GB 756 2995 81,

200 Gray's Inn Road, London WC1X 8XZ,

Telephone: 020 7833 3000.

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

Re: trouble understanding why query is not using the index.

[Jason Pyeron]

Am I correct in the statment second query is faster? This query gets 
executed several million times.


mysql> explain select A.* from files as A where A.pathref=129286 and 
version=(select max(version) from files as B where A.pathref=B.pathref);
+++---+--+---+---+-+-+--+-+
| id | select_type| table | type | possible_keys | key   | 
key_len | ref | rows | Extra   |
+++---+--+---+---+-+-+--+-+
|  1 | PRIMARY| A | ref  | pathref_2,pathref | pathref_2 |  
 4 | const   |1 | Using where |
|  2 | DEPENDENT SUBQUERY | B | ref  | pathref_2,pathref | pathref_2 |  
 4 | crisfield.A.pathref |1 | Using index |
+++---+--+---+---+-+-+--+-+
2 rows in set (0.01 sec)

mysql> explain select A.* from files as A where A.pathref=129286 and 
version=(select max(version) from files as B where 129286=B.pathref);
++-+---+---+---+---+-+-+--+--+
| id | select_type | table | type  | possible_keys | key   | 
key_len | ref | rows | Extra|
++-+---+---+---+---+-+-+--+--+
|  1 | PRIMARY | A | const | pathref_2,pathref,version | pathref_2 |
   8 | const,const |1 |  |
|  2 | SUBQUERY| NULL  | NULL  | NULL  | NULL  |
NULL | NULL| NULL | Select tables optimized away |
++-+---+---+---+---+-+-+--+--+
2 rows in set (0.00 sec)

On Wed, 7 Sep 2005, Gleb Paharenko wrote:


Hello.



so why does 'explain select * from files where
(pathref,version)=(129286,0);' scan the whole table?


It is documented that this syntax can't be optimized yet. See:
 http://dev.mysql.com/doc/mysql/en/row-subqueries.html



Jason Pyeron <[EMAIL PROTECTED]> wrote:

On Tue, 6 Sep 2005, Dan Nelson wrote:


In the last episode (Sep 06), Jason Pyeron said:

there is an unique key index 'pathref_2 (pathref,version)' on this
table.

so why does 'explain select * from files where
(pathref,version)=(129286,0);' scan the whole table?


I have to admit I have never seen this syntax used in a where clause
before.


never thought not to use it, SQL servers are just big set processing
engines.


Does "where pathref=129286 and version=0" optimize any better?


yes, it scans only one row.


A quick test of mysql-5.0.11 indicates that it doesn't use indexes at
all with the (field1,field2,..)=(value1,value2,..) syntax.  Feel free
to file a bug :)


http://bugs.mysql.com/13024







--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
-   -
- Jason Pyeron  PD Inc. http://www.pdinc.us -
- Partner & Sr. Manager 7 West 24th Street #100 -
- +1 (443) 921-0381 Baltimore, Maryland 21218   -
-   -
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

This message is for the designated recipient only and may contain 
privileged, proprietary, or otherwise private information. If you 
have received it in error, purge the message from your system and 
notify the sender immediately.  Any other use of the email by you 
is prohibited.


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

Re: How concerned should I be about Collation?

[Alexandre Quessy]

Thank you guys so much !!!
I found the answer to all my problems regarding charsets with MySQL  
dumps.

Here are the code lines:


CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `foo` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

and then :

mysqldump -u {{username}} -p{{password}} {{database}} --default- 
character-set=latin1 > file.sql




Thanks !!!

Alexandre Quessy
http://www.sourcelibre.com/
http://www.flikstudio.com/folio/




Le 05-09-06 à 04:32, Gleb Paharenko a écrit :


Hello.

There is no statement which is able to change the collation
and character set information in all tables at once. For single
table use an ALTER TABLE statement. See:
  http://dev.mysql.com/doc/mysql/en/alter-table.html

Re: Getting Record Count w/o doing 2 queries.

[SGreen]

RTFM - It is online, it has an index, and it is searchable: 
http://dev.mysql.com/doc/mysql/en/select.html (hint: look for the phrase 
"how many rows" on this page)

If you looked in the index for "rows", you would find the function 
ROW_COUNT() (which is close but not exactly what you wanted). However, by 
clicking on the link next to that function, you wind up at another page 
that also has the answer you seek.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



pow <[EMAIL PROTECTED]> wrote on 09/07/2005 06:15:12 AM:

> Hi everyone, Im executing the following query:
> 
> SELECT *
> FROM
> table1
> WHERE
> table1.field1 = 'A' AND table1.field2 = 'B'
> LIMIT 0,10
> 
> I also need to get the total record count for the above query, but 
> without the limit clause (limit is for pagination purposes)
> Is there any way to extract this total record count without the need to 
> do another query?
> 
> Right now I am using another query to get the total record count:
> 
> SELECT COUNT(*)
> FROM
> table1
> WHERE
> table1.field1 = 'A' AND table1.field2 = 'B'
> 
> 
> In reality, my tables are very large, and involve joins, so executing 
> the query TWICE is taking its toll on the server.
> Thanks!
> Pow
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

Re: Getting Record Count w/o doing 2 queries.

[Gleb Paharenko]

Hello.



You want to check out the FOUND_ROWS() function: 

http://dev.mysql.com/doc/mysql/en/information-functions.html



BTW, similar question was asked recently.





pow <[EMAIL PROTECTED]> wrote:

> Hi everyone, Im executing the following query:

> 

> SELECT *

> FROM

> table1

> WHERE

> table1.field1 = 'A' AND table1.field2 = 'B'

> LIMIT 0,10

> 

> I also need to get the total record count for the above query, but 

> without the limit clause (limit is for pagination purposes)

> Is there any way to extract this total record count without the need to 

> do another query?

> 

> Right now I am using another query to get the total record count:

> 

> SELECT COUNT(*)

> FROM

> table1

> WHERE

> table1.field1 = 'A' AND table1.field2 = 'B'

> 

> 

> In reality, my tables are very large, and involve joins, so executing 

> the query TWICE is taking its toll on the server.

> Thanks!

> Pow

> 



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




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

MySQL Migration 1.14 rc not closing mdb

[Scott Hamm]

Is it normal for MySQL migration 1.14 rc to leave MS Access open when it is 
done with migration (leaving ldb file)?

Scott

-- 
Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html

Re: Recommendations on new hardware

[Cory Robin]

Brent Baisley wrote:

If you do go the new hardware route, I wouldn't go with SCSI is you  
only have $2K to spend. S-ATA2 based drives would give you similar  
performance to SCSI, but at a big cost savings. SCSI's big  
performance advantage was in command queueing which SATA2 drives now  
have.


Dell? No, I wouldn't buy Dell. Simply because they don't sell AMD  
based systems. The dual core AMD chips with the built-in memory  
controller are very nice. 


I agree with the S-ATA2 recommendation.  However, I have a large cluster 
of Dell Poweredge systems running Red Hat Linux, Apache, an application 
server and MySQL-Cluster and it performs flawlessly.   And the 
performance is fantastic.  We did demo several AMD-based systems and 
they just didn't seem to beat the Intel XEON's.   We run mission 
critical apps and we feel that Dell was a wise choice for overall ROI.  
And with your budget, I'd definately strongly consider Dell.


Cory
SkyVantage Corporation

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

Re: Recommendations on new hardware

[Brent Baisley]

As you've probably read in the article, the hardware isn't too bad,  
it's OS X that is slowing things down.


I would first go the free route. Download YellowDog Linux and install  
that on your current Mac hardware. That will give you a big boost  
when the load starts to climb. I've installed YellowDog on an old Mac  
(350Mhz) and it was pretty painless. If you can get around in the  
terminal on the Mac, you should do fine under Linux.


If you do go the new hardware route, I wouldn't go with SCSI is you  
only have $2K to spend. S-ATA2 based drives would give you similar  
performance to SCSI, but at a big cost savings. SCSI's big  
performance advantage was in command queueing which SATA2 drives now  
have.


Dell? No, I wouldn't buy Dell. Simply because they don't sell AMD  
based systems. The dual core AMD chips with the built-in memory  
controller are very nice.



On Sep 7, 2005, at 2:09 AM, Scott Haneda wrote:


Hello, currently I run mysql 3.x on Mac OS X 10.3.

I have about as much ram in the machine as I can, and have tunes it  
the best
I can, however, I still see the performance drop pretty badly at  
times.


After reading this:
 I suspect OS X  
is just

not going to cut it.

I have a few years in a shell on OS X, but that's about it, I can get
around, compile stuff, etc, but I am in no way a master.

Part of me leans on getting into x86 BSD of some sort, so at least  
I am more

comfortable with it as it is going to be similar to OS X.  However, if
someone can tell me a good Linux distro to go with, I will use that.

Mainly, I would like to know what hardware to be looking at,  
something in

the rack mount style, a 1U would be nice.  Bigger if need be.

I probably will have two hard drives, set as a mirror, or just use  
psync or
rsync to clone one to the other.  Loosing a few hours worth of data  
is not a

huge deal for the few hundred sites this machine will serve.

However, I do need the hardware to be able to handle something like  
getting
slashdotted.  I am on a 100Mbit pipe, its not a burstable one, full  
blown,
MRTG tells me it is loaded about 2% or so, so I am rarely heating  
it up.  I

don't think bandwidth is my bottleneck.

I may want to go SCSI, but I am not sure. I have about 2000.00 to  
drop into
this, and want a machine to to apache and mysql that will hold its  
own as

best as possible.

Do I just go to dell.com and get what they have, or do you all have  
better
stuff to recommend? If I have left out anything, please let me  
know, I will

be sure to reply.
--
-
Scott HanedaTel: 415.898.2602
 Novato, CA U.S.A.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql? 
[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]

Re: Getting Record Count w/o doing 2 queries.

[Brent Baisley]

Just add SQL_CALC_FOUND_ROWS to your select statement:
SELECT SQL_CALC_FOUND_ROWS COUNT(*)...;

The you can execute a "special" query to figure out how many rows  
would have been returned without the LIMIT clause.

SELECT FOUND_ROWS();

On Sep 7, 2005, at 6:15 AM, pow wrote:


Hi everyone, Im executing the following query:

SELECT *
FROM
table1
WHERE
table1.field1 = 'A' AND table1.field2 = 'B'
LIMIT 0,10

I also need to get the total record count for the above query, but  
without the limit clause (limit is for pagination purposes)
Is there any way to extract this total record count without the  
need to do another query?


Right now I am using another query to get the total record count:

SELECT COUNT(*)
FROM
table1
WHERE
table1.field1 = 'A' AND table1.field2 = 'B'


In reality, my tables are very large, and involve joins, so  
executing the query TWICE is taking its toll on the server.

Thanks!
Pow

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql? 
[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]

Re: Getting Record Count w/o doing 2 queries.

[Eric McGrane]

After you execute your select/limit statement you can execute 


select found_rows()

It returns the number of rows thbat the previous query would have returned 
if the limit wasn't applied.  See: http://dev.mysql.com/doc/mysql/en/information-functions.html.


Regards,
E


Hi everyone, Im executing the following query:

SELECT *
FROM
table1
WHERE
table1.field1 = 'A' AND table1.field2 = 'B'
LIMIT 0,10
I also need to get the total record count for the above query, but
without the limit clause (limit is for pagination purposes)
Is there any way to extract this total record count without the need
to
do another query?
Right now I am using another query to get the total record count:

SELECT COUNT(*)
FROM
table1
WHERE
table1.field1 = 'A' AND table1.field2 = 'B'
In reality, my tables are very large, and involve joins, so executing
the query TWICE is taking its toll on the server.
Thanks!
Pow





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

RE: please help .........very urgent

[Edwin Cruz]

Hi!!

$dbQuery = "select * from table where 1" ;
$result = mysql_query($dbQuery) or die("Error trying to execute the query:
".mysql_error());
If(mysql_num_rows($result)<1){ //also: "==0" works
echo "NULL";

-Original Message-
From: Kane Wilson [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, September 07, 2005 1:40 AM
To: mysql
Subject: please help .very urgent



hi , 

I wanted to check the following condition and if it is success i wanted to
display a massage.I tried as follows. but no luck. nothing displayed.

$dbQuery = results;
$result = mysql_query($dbQuery) or die("Couldn't get file list");

if (!isset($result))
   {echo "NULL";}


//if (mysql_result($result == 0))(

//echo "sorry";

?>


please , tell me how can i do this ?

Thanx
Kane.




__
Click here to donate to the Hurricane Katrina relief effort.
http://store.yahoo.com/redcross-donate3/

--
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: AES_DECRYPT output

[Nuno Pereira]

I suppose that you have to change the status to Open, or people suppose 
that there aren't any changes. It would be nice that it would be tested 
in ASP.NET because it can be simply a problem with of type conversion in 
.NET, I haven't see the problem (and I don't work with .NET).


M DR wrote:

Hi,

I have reported the bug a while ago, but it seems not all bugs are taken 
seriously. I reported it and one of the researchers couldn't replicate 
the problem, so I provided a sample project in ASP.NET where the problem 
can be seen very easily, but nobody reacted (probably because the status 
has been set to "Can't repeat").


See for the entry in the bugs database: 
http://bugs.mysql.com/bug.php?id=12872

Any idea how I can help fixing the problem nonetheless?


From: Gleb Paharenko <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Subject: Re: AES_DECRYPT output
Date: Mon, 29 Aug 2005 23:15:26 +0300

Hello.

> And I guess you also refer to the strange behavior that it's 
different when

> adding an ORDER BY or GROUP BY, right?

Yes, in my opinion, ORDER BY shouldn't change the column's type.


"M DR" <[EMAIL PROTECTED]> wrote:
> Hi,
>
> Thanks for your answers,
>
> What is according to you the bug? Should it always return a string 
format

> value? Or should it always return a system.byte[] format value?
>
> And I guess you also refer to the strange behavior that it's 
different when

> adding an ORDER BY or GROUP BY, right?
>
> Kind regards,
>
> Martin


--
Nuno Pereira

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

Locks acquired with get_lock() persisting, even after MySQL thread has exited

[Adam Newby]

Description:

We have distributed applications which make extensive use of the 
get_lock() function
to acquire a system-wide lock. Occasionally, all copies of a given 
application
block attempting to acquire a lock on the same lock string. Using 
is_used_lock() reports
that the lock is held by a particular thread ID.

In one instance, this thread ID did not exist.

In another instance, it did exist, but after killing that thread, 
is_used_lock()
still reported the same (now non-existent) thread as holding the lock.

Here is an example:

output from 'mysql newsnow -unewsnow -e "select is_used_lock('1 1 
11')"':

is_used_lock('1 1 11')
166

mysqladmin processlist output at same time - note that the above thread 
ID is not listed:

+-+--+--+-++---++--+
|   Id  |   User|   Host|   db  |   Command 
|   Time|   State   |   Info|
+-+--+--+-++---++--+
|   29  |   DELAYED |   |   newsnow |   Delayed insert  
|   0   |   Waiting for INSERT  |   |
|   36  |   DELAYED |   |   newsnow |   Delayed insert  
|   0   |   Waiting for INSERT  |   |
|   66  |   DELAYED |   |   newsnow |   Delayed insert  
|   0   |   Waiting for INSERT  |   |
|   152 |   DELAYED |   |   newsnow |   Delayed insert  
|   0   |   Waiting for INSERT  |   |
|   1270|   repl|   gilligan.webcluster.newsnow.co.uk:55619 
|   |   Binlog  Dump|   49399   |   Has sentall 
binlog  to  slave;  waiting for binlog  to  be  updated |   
|
|   1931|   newsnow |   fw-2.webcluster.newsnow.co.uk:34872 
|   newsnow |   Sleep   |   21  |   |   |
|   303482  |   newsnow |   king.webcluster.newsnow.co.uk:40739 
|   newsnow |   Query   |   0   |   Locked  |   /*  
SouAutFre   */  select  distinct(substring(FDate,   1,  10))
as  _Date,  dayofweek(FDate)as  _DayfromRef |
|   764780  |   champ   |   bush.newsnow.net:55504  |   newsnow 
|   Sleep   |   2   |   |   |
|   764781  |   champ   |   bush.newsnow.net:55505  |   newsnow 
|   Sleep   |   3   |   |   |
|   764782  |   champ   |   bush.newsnow.net:55506  |   newsnow 
|   Sleep   |   2   |   |   |
|   770544  |   newsnow |   fw-1.webcluster.newsnow.co.uk:46897 
|   newsnow |   Sleep   |   2   |   |   |
|   993861  |   newsnow |   ford.webcluster.newsnow.co.uk:42566 
|   newsnow |   Sleep   |   3   |   |   |
|   993865  |   newsnow |   ford.webcluster.newsnow.co.uk:42567 
|   newsnow |   Sleep   |   4   |   |   |
|   994002  |   newsnow |   simpson.webcluster.newsnow.co.uk:36279  
|   newsnow |   Sleep   |   5   |   |   |
|   994021  |   newsnow |   db-1.webcluster.newsnow.co.uk:46345 
|   newsnow |   Sleep   |   0   |   |   |
|   994023  |   newsnow |   db-1.webcluster.newsnow.co.uk:46346 
|   newsnow |   Sleep   |   3   |   |   |
|   994026  |   newsnow |   db-2.webcluster.newsnow.co.uk:45053 
|   newsnow |   Sleep   |   3   |   |   |
|   994092  |   newsnow |   fw-2.webcluster.newsnow.co.uk:42534 
|   newsnow |   Sleep   |   5   |   |   |
|   994116  |   newsnow |   db-1.webcluster.newsnow.co.uk:46347 
|   newsnow |   Sleep   |   4   |   |   |
|   994475  |   newsnow |   king.webcluster.newsnow.co.uk:40726 
|   newsnow |   Sleep   |   1   |   |   |
|   999805  |   newsnow |   www-2.webcluster.newsnow.co.uk:37533
|   newsnow |   Sleep   |   4   |   |   |
|   1000512 |   newsnow |   king.webcluster.newsnow.co.uk:41318 
|   newsnow |   Sleep   |   3   |   |   |
|   1055442 |   newsnow |   db-2.webcluster.newsnow.co.uk:48367 

Replication slave fails to start under OSF1 (Tru64)

[adh]

>Description:
I am running a MySQL 4.1.14-standard server on one HP Alpha machine. It is 
configured
to act as a replication master. When I start a replication slave on a different 
HP Alpha
machine, using the same binary distribution, the slave fails. This is part of 
the slave's
error log file:

050828 13:33:30  mysqld started
050828 13:33:30 [Warning] Can't open and lock time zone table: Table 
'mysql.time_zone_leap_second' doesn't exist trying to live without them
/nfs/pathsoft/external/mysql-standard-4.1.14/bin/mysqld: ready for connections.
Version: '4.1.14-standard'  socket: 
'/nfs/arcturus1/mysql/etc/mysql.pcs3-dev.sock'  port: 14644  MySQL Community 
Edition - Standard (GPL)
050828 13:33:30 [Note] Slave SQL thread initialized, starting replication in 
log 'FIRST' at position 0, relay log 
'/nfs/arcturus1/mysql/data/pcs3-dev/relay-bin.01' position: 4
050828 13:33:30 [ERROR] Slave I/O thread: error connecting to master '[EMAIL 
PROTECTED]:14642': Error: 'Unknown MySQL server host 'pcs3' (1)'  errno: 2005  
retry-time: 30  retries: 86400

Note the error message "Unknown MySQL server host 'pcs3' (1)". This suggests 
that the slave canor resolve the
hostname "pcs3" to an IP address, but from the same host that the slave is 
running on, I can connect to the
master with a command such as "mysql -h pcs3 -P 14642 ..."

I can successfully run a replication slave with an *identical* configuration 
file on a Linux/i686
machine, so I know that (a) my master server on host pcs3 is correctly 
configured to act as a
replication master, (b) my replication slave configuration file is correct, and 
(c) there is
nothing suspicious about my network environment such as bad a DNS or NIS lookup.

>How-To-Repeat:
This is a repeatable problem. Every time I try to start the replication slave 
on the HP Alpha,
it fails, reporting the same error message as above.
>Fix:
The error lies in the code which the server uses to perform host-to-IP lookups.
Specifically, the server calls gethostbyname_r to resolve the host name. The man
page for gethostbyname_r under OSF1 says:

NAME

  gethostbyname, gethostbyname_r - Get a network host entry by name

SYNOPSIS

  #include 


  struct hostent *gethostbyname(
  const char *name );

  [Tru64 UNIX]  The following function is supported in order to maintain
  backward compatibility with previous versions of the operating system.  You
  should not use it in new designs.

  int gethostbyname_r(
  const char *name,
  struct hostent *hptr,
  struct hostent_data *hdptr );

and later in the man page:

  [Tru64 UNIX]  The gethostbyname_r() function is an obsolete reentrant ver-
  sion of the gethostbyname() function.  It is supported in order to maintain
  backward compatibility with previous versions of the operating system and
  should not be used in new designs.  Note that you must zero-fill the hdptr
  structure before its first access by the gethostbyname_r() function.

The MySQL source code is *not* zero-filling the hdptr data structure, and as
a result, the function call is returning a non-zero value, which the MySQL
source code is interpreting incorrectly as a DNS lookup failure.

This error is closely related to a bug which I reported in 2002 in MySQL 
3.23.49:

  http://lists.mysql.com/bugs/11975

Monty Widenius identified the cause of the problem and fixed it in the next
release by forcing the use of gethostbyname, which is thread-safe under OSF1.
Unfortunately, the bug seems to have crept back in.

I'm afraid that I'm unable to re-build from source code to verify my hypothesis,
but I'll wager a bottle of beer that this is the problem.
>Submitter-Id:  
>Originator: David Harper
>Organization:
Wellcome Trust Sanger Institute,  Hinxton,  Cambridge CB10 1SA,  England
>
>MySQL support: none
>Synopsis: Replication slave fails to start under OSF1 (Tru64)
>Severity: serious
>Priority:  medium
>Category:  mysql
>Class: sw-bug
>Release:   mysql-4.1.14-standard (MySQL Community Edition - Standard (GPL))
>C compiler:
>C++ compiler:  
>Environment:
HP Alpha running OSF1 (Tru64)
System: OSF1 pcs3d V5.1 2650 alpha
Machine: alpha
Some paths:  /bin/perl /bin/make /usr/local/bin/gmake /usr/local/bin/gcc /bin/cc
GCC: Reading specs from /usr/local/lib/gcc-lib/alpha-dec-osf5.1/3.0.3/specs
Configured with: ../configure 
Thread model: single
gcc version 3.0.3
Compilation info: CC='cc'  CFLAGS='-O2 -pthread -ansi_alias -ansi_args -fast 
-inline speed -speculate all'  CXX='cxx'  CXXFLAGS='-O2 -pthread -ansi_alias 
-fast -inline speed -noexceptions -nortti'  LDFLAGS=''  ASFLAGS=''
LIBC: 
lrwxrwxrwx   1 root system17 Mar  6  2004 /lib/libc.a -> 
../ccs/lib/libc.a
lrwxrwxrwx   1 root system17 Mar  6  2004 /usr/lib/libc.a -> 
../ccs/lib/libc.a
Configure command: ./configure '--prefix=/usr/local/mysql' 
'--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin' 
'--with-comment=MySQL Community Edition - Standard (GPL)' 

Re: Sort aborted

[Bgs]

 Hi,

I've tried that there is no problem with that. Mysql creates bigger tmp 
tables from time to time. We had this problem with a specific query onyl


I sad had, as last night I tried again and it worked. Beats me...
Server was not rebooted, mysql was not restarted and the available space 
got a bit even smaller...


There must have been something in the query, but I cannot reproduce the 
error right now. I will look out for new occurences in the near future...


Bye
Bgs


Gleb Paharenko wrote:

Hello.


What operating system do you use? `su` to mysql user (I think
the less ugly method should exists) and check if you able to create
big files.


Bgs <[EMAIL PROTECTED]> wrote:


Hi,

There are no quotas at all. The server has been up since march. This is 
the first time we had this problem, but now it is consistent...


I run the query and after a few seconds, with about 8-10kB written on 
the disk, I get the abort.


Bye
Bgs








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

Getting Record Count w/o doing 2 queries.

[pow]

Hi everyone, Im executing the following query:

SELECT *
FROM
table1
WHERE
table1.field1 = 'A' AND table1.field2 = 'B'
LIMIT 0,10

I also need to get the total record count for the above query, but 
without the limit clause (limit is for pagination purposes)
Is there any way to extract this total record count without the need to 
do another query?


Right now I am using another query to get the total record count:

SELECT COUNT(*)
FROM
table1
WHERE
table1.field1 = 'A' AND table1.field2 = 'B'


In reality, my tables are very large, and involve joins, so executing 
the query TWICE is taking its toll on the server.

Thanks!
Pow

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

Re: AES_DECRYPT output

[M DR]

Hi,

I have reported the bug a while ago, but it seems not all bugs are taken 
seriously. I reported it and one of the researchers couldn't replicate the 
problem, so I provided a sample project in ASP.NET where the problem can be 
seen very easily, but nobody reacted (probably because the status has been 
set to "Can't repeat").


See for the entry in the bugs database: 
http://bugs.mysql.com/bug.php?id=12872

Any idea how I can help fixing the problem nonetheless?


From: Gleb Paharenko <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Subject: Re: AES_DECRYPT output
Date: Mon, 29 Aug 2005 23:15:26 +0300

Hello.

> And I guess you also refer to the strange behavior that it's different 
when

> adding an ORDER BY or GROUP BY, right?

Yes, in my opinion, ORDER BY shouldn't change the column's type.




"M DR" <[EMAIL PROTECTED]> wrote:
> Hi,
>
> Thanks for your answers,
>
> What is according to you the bug? Should it always return a string 
format

> value? Or should it always return a system.byte[] format value?
>
> And I guess you also refer to the strange behavior that it's different 
when

> adding an ORDER BY or GROUP BY, right?
>
> Kind regards,
>
> Martin
>


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




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




_
Direct antwoord op je vragen: gebruik MSN Messenger http://messenger.msn.nl/


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

RE: Date Ranges

[Mark Leith]

---snip--

> Given any two dates, MySQL can tell if a third date is within 
> that range. 
> That's easy.
> 
> To actually return a list of all dates between any arbitrary 
> pair of dates requires some form of loop (v5.0+) or a lookup 
> into a table populated with all possible dates (any version 
> that supports joins). It's possible to get MySQL to give you 
> a list of dates but not as a "native" function. There is just 
> no facility built into the system to return that list. Sorry! 
>  Have you seen such a function before? If so, where and what 
> was it called?
> 
> Most of the times when people ask this question, they have a 
> report they want to write and need to generate "blank" rows 
> for dates that aren't in the data. Is that what you need or 
> is there some other purpose to your question?
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine

Indeed, we were asked this question on the freenode IRC channel a few weeks
ago - somebody wanted to calculate how many days between two dates fell
within a weekend, where there were possibly gaps within the data that they
had. I came up with the following procedure which gives an example of how to
do it with a loop and a temporary table, then some other little date
statistics for the given date range. The main point of interest, as Shawn
noted, is the loop that creates the dates within the range, and inserts them
in to a temporary table: 

DROP PROCEDURE date_stats// 

CREATE PROCEDURE date_stats ( IN sdate DATE, IN edate DATE)
BEGIN
DECLARE dates_done INT DEFAULT 0;

CREATE TEMPORARY TABLE date_range ( tdate DATE );

dates: LOOP

  IF dates_done = 1 THEN
LEAVE dates;
  END IF;
  
  CASE WHEN sdate < edate 
THEN INSERT INTO date_range VALUES (sdate);
ELSE SET dates_done = 1;
  END CASE;
  
  SET sdate = sdate + INTERVAL 1 DAY;
END LOOP dates;

SELECT SUM(IF(WEEKDAY(tdate) IN (0,1,2,3,4),1,0)) as week_day_cnt,
SUM(IF(WEEKDAY(tdate) IN (5,6),1,0)) as weekend_day_cnt
  FROM date_range;
  
SELECT ROUND(COUNT(*)/7) as number_of_weeks,
COUNT(*) as number_of_days
  FROM date_range;
  
SELECT TIMESTAMPDIFF(SECOND,MIN(tdate),MAX(tdate)) as seconds_diff,
TIMESTAMPDIFF(MINUTE,MIN(tdate),MAX(tdate)) as minutes_diff,
TIMESTAMPDIFF(HOUR,MIN(tdate),MAX(tdate)) as hours_diff
  FROM date_range;
  
DROP TEMPORARY TABLE date_range;

END;
//

CALL date_stats('2005-01-01','2005-02-01')//

+--+-+
| week_day_cnt | weekend_day_cnt |
+--+-+
|   21 |  10 |
+--+-+
1 row in set (2.78 sec)

+-++
| number_of_weeks | number_of_days |
+-++
|   4 | 31 |
+-++
1 row in set (2.78 sec)

+--+--++
| seconds_diff | minutes_diff | hours_diff |
+--+--++
|  2592000 |43200 |720 |
+--+--++
1 row in set (2.78 sec)

Query OK, 0 rows affected (2.97 sec)

Hope this helps,

Mark

Mark Leith
Cool-Tools UK Limited
http://www.cool-tools.co.uk
http://leithal.cool-tools.co.uk 

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.10.18/91 - Release Date: 06/09/2005
 


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

`gmake test` probs Solaris9 for M. 4.1.4.

[Hugh Sasse]

Attempts to do make test prior to installation, to check that I'm
installing something workable, give:


neelix hgs 63 %> gmake test
cd mysql-test; perl mysql-test-run.pl && perl mysql-test-run.pl --ps-protocol
No ndbcluster support
Killing Possible Leftover Processes
Removing Stale Files
Installing Master Databases
Installing Master Databases
Installing Slave Databases
Installing Slave Databases
Installing Slave Databases
===
Finding  Tests in the 'main' suite
Starting Tests in the 'main' suite

TESTRESULT
---

alias   [ pass ]
alter_table [ pass ]
analyse [ pass ]
analyze [ pass ]
ansi[ pass ]
archive [ fail ]
Errors are (from /scratch/hgs/mysql-4.1.14/mysql-test/var/log/mysqltest-time) :
This test is not supported by this installation
mysqltest returned unexpected code 15872, it has probably crashed
(the last lines may be the most important ones)

Aborting: archive failed. To continue, re-run with '--force'.
Ending Tests
Shutting-down MySQL daemon

Master(s) shutdown finished
Slave(s) shutdown finished
gmake: *** [test] Error 1
neelix hgs 64 %>


So, firstly, invoke what with '--force'?  You don't get anything
useful if you pass that to make

Also, how can I tell if InnoDB built OK?  I'm doing this for use
with Ruby on Rails, and I need it to be in a separate directory from
the version we already have (3.x) used by others.  I modified
mysql-4.1.14/BUILD/compile-solaris-sparc thusly

neelix hgs 81 %> display_diffs.rb .
--- ./compile-solaris-sparc.orig2005-08-17 18:06:41.0 +0100
+++ ./compile-solaris-sparc 2005-09-06 18:36:25.386697000 +0100
@@ -11,6 +11,6 @@
(cd gemini && aclocal && autoheader && aclocal && automake && autoconf)
 fi

-CFLAGS="-g -Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts 
-Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused  -O3 -fno-omit-frame-pointer 
-mcpu=v8 -Wa,-xarch=v8plusa" CXX=gcc CXXFLAGS="-Wimplicit -Wreturn-type -Wswitch 
-Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings 
-Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor 
-felide-constructors -fno-exceptions -fno-rtti  -O3 -fno-omit-frame-pointer -mcpu=v8 
-Wa,-xarch=v8plusa -g" ./configure --prefix=/usr/local/mysql --enable-assembler 
--with-extra-charsets=complex --enable-thread-safe-client
+CFLAGS="-g -Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts 
-Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused  -O3 -fno-omit-frame-pointer 
-mcpu=v8 -Wa,-xarch=v8plusa" CXX=gcc CXXFLAGS="-Wimplicit -Wreturn-type -Wswitch 
-Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings 
-Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor 
-felide-constructors -fno-exceptions -fno-rtti  -O3 -fno-omit-frame-pointer -mcpu=v8 
-Wa,-xarch=v8plusa -g" ./configure --prefix=/usr/local/mysql --enable-assembler 
--with-extra-charsets=complex --enable-thread-safe-client --with-tcp-port=3308 
--with-unix-socket-path=/tmp/mysql4.sock --prefix=/usr/local/mysql-4.1.14

 gmake -j 4
neelix hgs 82 %>
and I invoked the script directly, and also tried with bash.

Not sure what else to pass on.

GNU Make version 3.79.1, by Richard Stallman and Roland McGrath.
Built for sparc-sun-solaris2.9

gcc (GCC) 3.4.3

Thank you
Hugh

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

Solution: old configuration

[Löffler Thomas J .]

Hi all, 

 

Thanks for your replies, 

 

Found the solution. I had the data directory on another harddisk + did not 
delete it after the deinstallation. 

 

 

cheers, Thomas 
 
 t.j. loeffler  
  swiss federal institute of technology 
  department of earth sciences 
  institute of geology 
  universitaetsstr. 6, CAB E10.4 phone: +41 (0)1 632 5696 
  ch-8092 zuerich   fax: +41 (0)1 632 1080 
__ mailto://[EMAIL PROTECTED]  
___http://www.erdw.ethz.ch/ _

 

Re: old settings

[Robert Graham]

Loffler Thomas J. wrote:

Hi all, 


I've installed MySQL db server 4.1.14 + Administrator + Control Center
for test purposes on Windows 2003 Server Standard. Then I deinstalled
everything, deleted all files which were not deleted by the uninstall
process (search for "mysql") + deleted also the entries in the registry
which were not deleted by the uninstall process (search "mysql"). 
Now, after installing again MySQL db server 4.1.14 etc. everything seems

to be ok, but after restarting the MySQL daemon, the old settings from
the installation before appear + cannot be changed. The error is mostly
"max_question resource exceeded". 
Does someone know which files or registry entry/entries must be deleted
or reset? 

TIA, 

cheers, Thomas 

 t.j. loeffler  
 swiss federal institute of technology 
 department of earth sciences 
 institute of geology 
 sonneggstr. 5, NO G35 phone: +41 (0)1 632 5696 
 ch-8092 zuerich fax: +41 (0)1 632 1080 
__ mailto://[EMAIL PROTECTED] 
___http://www.erdw.ethz.ch/_




 




No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.10.18/91 - Release Date: 2005/09/06
 


Hi there

Have you had a look at your my.ini file which is in your MySQL directory 
under Program Files on windows, or in your /etc directory on Linux


Hope this helps.

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

Re: re:mysqldump: Error 2013

[Gleb Paharenko]

Hello.



Have a look here:

  http://dev.mysql.com/doc/mysql/en/gone-away.html



christophe charron <[EMAIL PROTECTED]> wrote:

> Hi, 

> i've got the same kind of problem but not in a dump, in a restore. I

> tried to restore it in a MyIsam type table but i have the same error.

> Could other server variables interfer in this ? For example, i see

> that my long_query_time is set to '10' ...

> 



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




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

Re: Why does Mysql use a filesort ?

[Gleb Paharenko]

Hello.



> This index is not used at all for the select ?



substring(segment_0_sk,1,677) - a function is applied

to the indexed column. In my opinion, MySQL isn't so smart

to understand that this is a prefix. If you want to order only

on the prefix of your text column, think about max_sort_length

server system variable (it is a session variable as well). See:

  http://dev.mysql.com/doc/mysql/en/server-system-variables.html









Vincent De Groote <[EMAIL PROTECTED]> wrote:

> Hello,

> 

> I have the following table:

> 

> CREATE TABLE `list_datas_1` (

>  `list_id` bigint(20) NOT NULL,

>  `locale_id` bigint(20) NOT NULL,

>  `record_id` bigint(20) NOT NULL,

>  `segment_0` longtext,

>  `segment_1` longtext,

>  `segment_0_sk` longblob,

>  `segment_1_sk` longblob,

>  KEY `record_id` (`record_id`),

>  KEY `list_id` (`list_id`),

>  KEY `datas` (`locale_id`,`segment_0_sk`(677),`segment_1_sk`(338)),

>  CONSTRAINT `list_datas_1_ibfk_1` FOREIGN KEY (`list_id`) REFERENCES

> `list_definitions` (`list_definition_id`),

>  CONSTRAINT `list_datas_1_ibfk_2` FOREIGN KEY (`locale_id`) REFERENCES

> `locales` (`locale_id`),

>  CONSTRAINT `list_datas_1_ibfk_3` FOREIGN KEY (`record_id`) REFERENCES

> `records` (`record_id`)

> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

> 

> When I use the following statement:

> 

> select record_id, segment_0_sk, segment_1_sk from list_datas_1 order by

> locale_id, substring(segment_0_sk,1,677);

> 

> explain returns the following datas:

> 

> ++-+--+--+---+--

> +-+--+--++

> | id | select_type | table| type | possible_keys | key  |

> key_len | ref  | rows | Extra  |

> ++-+--+--+---+--

> +-+--+--++

> |  1 | SIMPLE  | list_datas_1 | ALL  | NULL  | NULL | NULL

> | NULL | 2697 | Using filesort |

> ++-+--+--+---+--

> +-+--+--++

> 

> The two columns in the order by clause is a prefix of the 'datas' index.

> This index is not used at all for the select ?

> 

> Thanks for you replies.

> 

> vdg

> 

> 



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




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

Re: old settings

[Gleb Paharenko]

Hello.





>"max_question resource exceeded". 



It is somehow related to the user privileges. See:

  http://dev.mysql.com/doc/mysql/en/user-resources.html



Start mysqld with --skip-grant-tables and fix the user's limits.





>Does someone know which files or registry entry/entries must be deleted



Settings are stored in the configuration file. See:

  http://dev.mysql.com/doc/mysql/en/option-files.html





Loffler Thomas J. <[EMAIL PROTECTED]> wrote:

>Hi all, 

>

>I've installed MySQL db server 4.1.14 + Administrator + Control Center

>for test purposes on Windows 2003 Server Standard. Then I deinstalled

>everything, deleted all files which were not deleted by the uninstall

>process (search for "mysql") + deleted also the entries in the registry

>which were not deleted by the uninstall process (search "mysql"). 

>Now, after installing again MySQL db server 4.1.14 etc. everything seems

>to be ok, but after restarting the MySQL daemon, the old settings from

>the installation before appear + cannot be changed. The error is mostly

>"max_question resource exceeded". 

>Does someone know which files or registry entry/entries must be deleted

>or reset? 



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




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

Re: trouble understanding why query is not using the index.

[Gleb Paharenko]

Hello.





>>> so why does 'explain select * from files where

>>> (pathref,version)=(129286,0);' scan the whole table?



It is documented that this syntax can't be optimized yet. See:

  http://dev.mysql.com/doc/mysql/en/row-subqueries.html







Jason Pyeron <[EMAIL PROTECTED]> wrote:

> On Tue, 6 Sep 2005, Dan Nelson wrote:

> 

>> In the last episode (Sep 06), Jason Pyeron said:

>>> there is an unique key index 'pathref_2 (pathref,version)' on this

>>> table.

>>>

>>> so why does 'explain select * from files where

>>> (pathref,version)=(129286,0);' scan the whole table?

>>

>> I have to admit I have never seen this syntax used in a where clause

>> before.

> 

> never thought not to use it, SQL servers are just big set processing 

> engines.

> 

>> Does "where pathref=129286 and version=0" optimize any better?

> 

> yes, it scans only one row.

> 

>> A quick test of mysql-5.0.11 indicates that it doesn't use indexes at

>> all with the (field1,field2,..)=(value1,value2,..) syntax.  Feel free

>> to file a bug :)

> 

> http://bugs.mysql.com/13024

> 



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




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

Re: please help .........very urgent

[Gleb Paharenko]

Hello.



> $dbQuery = results;



Have you forgotten to put '$' before the 'results'? Do you

have display_errors = On in your php.ini?





Kane Wilson <[EMAIL PROTECTED]> wrote:

> 

> 

> hi , 

> 

> I wanted to check the following condition and if it is

> success i wanted to display a massage.I tried as

> follows. but no luck. nothing displayed.

> 

> $dbQuery = results;

> $result = mysql_query($dbQuery) or die("Couldn't get

> file list");

> 

> if (!isset($result))

>   {echo "NULL";}

> 

> 

> //if (mysql_result($result == 0))(

> 

> //echo "sorry";

> 

> ?>

> 

> 

> please , tell me how can i do this ?

> 

> Thanx

> Kane.

> 

> 

>

>

> __

> Click here to donate to the Hurricane Katrina relief effort.

> http://store.yahoo.com/redcross-donate3/

> 



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




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

Re: a confusing error: 111

[Ben]

Try redoing from scratch but this time dont use a client
just use command line!

> yeah, i can access the database server normally with the client mysql.
> it works all right. the point is mysqladmin doesn't work.
>
> Thank u.
>
> On 9/7/05, 王静 <[EMAIL PROTECTED]> wrote:
> > On 9/7/05, Ben <[EMAIL PROTECTED]> wrote:
> > >  What linux where you using?
> >
> > RedHat linux 2.4.21
> >
> > > A blank password is not a good idea, I'm
> > > not sure if it is allowed anyway.
> >
> > yeah, i know. so what i'm trying to do is adding/changing password for
> > the only account root with the tool mysqladmin. but the error occured
> > as i've posted before
> >
> > > I had the same prob with SuSE 9.3
> > > and I updated MySQL and the problem
> > > left, But also you get the error when the account
> > > is not set properly.
> > > Can you log into # mysql -u root test
> > > ?
> >
> > yes, i can log into #mysql and get the prompt mysql>
> >
> > Thank u.
> >
> > > ---Original Message---
> > >
> > > From: 王静
> > > Date: 09/07/05 17:52:52
> > > To: mysql@lists.mysql.com
> > > Subject: Re: a confusing error: 111
> > >
> > > i recall i've managed to set up a connection to MySQL server using
> > > account root with the "password" item  blank in Kdevelop. when i
> > > invoke mysqlaccess -uroot -p, it returned a prompt for password, i
> > > just hit return, but failed this time. mysqladmin -uroot got the same
> > > suituation with the error 111.
> > > i'm sure the default port 3306 isn't blocked by firewall, since i can
> > > access the server with the tool mysql. any port else invovled?
> > > thank u.
> > >
> > > On 9/7/05, Ben <[EMAIL PROTECTED]> wrote:
> > > > yes,
> > > >  You dont have an open account with root permissions
> > > > mysqladmin -u root passwd password
> > > > replace password with a real password
> > > > also check to make sure your firewall isnt blocking
> > > > your query if on a seperate network!
> > > >
> > > > hope this helps
> > > >
> > > >
> > > > ---Original Message---
> > > >
> > > > From: 王静
> > > > Date: 09/07/05 17:04:31
> > > > To: mysql@lists.mysql.com
> > > > Subject: a confusing error: 111
> > > >
> > > > hi all.
> > > > i've installed MySQL database server 4.1.14 under linux platform
> > > > After setting up the server successfully, i envoked mysqladmin, but
> > > > what i got is an error: can't connect to local MySQL server through
> > > > socket '/tmp/mysql.sock'(111)
> > > > is there anyone who ever run into similar problem and how to solve
> > > > it? thank u.
> > > >
> > > > --
> > > > MySQL General Mailing List
> > > > For list archives: http://lists.mysql.com/mysql
> > > > To unsubscribe:
> > > > http://lists.mysql.com/[EMAIL PROTECTED]
> > > > nz
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > > http://lists.mysql.com/[EMAIL PROTECTED]
> > > nz

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

Why does Mysql use a filesort ?

[Vincent De Groote]

Hello,

I have the following table:

CREATE TABLE `list_datas_1` (
  `list_id` bigint(20) NOT NULL,
  `locale_id` bigint(20) NOT NULL,
  `record_id` bigint(20) NOT NULL,
  `segment_0` longtext,
  `segment_1` longtext,
  `segment_0_sk` longblob,
  `segment_1_sk` longblob,
  KEY `record_id` (`record_id`),
  KEY `list_id` (`list_id`),
  KEY `datas` (`locale_id`,`segment_0_sk`(677),`segment_1_sk`(338)),
  CONSTRAINT `list_datas_1_ibfk_1` FOREIGN KEY (`list_id`) REFERENCES
`list_definitions` (`list_definition_id`),
  CONSTRAINT `list_datas_1_ibfk_2` FOREIGN KEY (`locale_id`) REFERENCES
`locales` (`locale_id`),
  CONSTRAINT `list_datas_1_ibfk_3` FOREIGN KEY (`record_id`) REFERENCES
`records` (`record_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When I use the following statement:

select record_id, segment_0_sk, segment_1_sk from list_datas_1 order by
locale_id, substring(segment_0_sk,1,677);

explain returns the following datas:

++-+--+--+---+--
+-+--+--++
| id | select_type | table| type | possible_keys | key  |
key_len | ref  | rows | Extra  |
++-+--+--+---+--
+-+--+--++
|  1 | SIMPLE  | list_datas_1 | ALL  | NULL  | NULL | NULL
| NULL | 2697 | Using filesort |
++-+--+--+---+--
+-+--+--++

The two columns in the order by clause is a prefix of the 'datas' index.
This index is not used at all for the select ?

Thanks for you replies.

vdg


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

old settings

[Loffler Thomas J .]

Hi all, 

I've installed MySQL db server 4.1.14 + Administrator + Control Center
for test purposes on Windows 2003 Server Standard. Then I deinstalled
everything, deleted all files which were not deleted by the uninstall
process (search for "mysql") + deleted also the entries in the registry
which were not deleted by the uninstall process (search "mysql"). 
Now, after installing again MySQL db server 4.1.14 etc. everything seems
to be ok, but after restarting the MySQL daemon, the old settings from
the installation before appear + cannot be changed. The error is mostly
"max_question resource exceeded". 
Does someone know which files or registry entry/entries must be deleted
or reset? 

TIA, 

cheers, Thomas 

 t.j. loeffler  
  swiss federal institute of technology 
  department of earth sciences 
  institute of geology 
  sonneggstr. 5, NO G35 phone: +41 (0)1 632 5696 
  ch-8092 zuerich fax: +41 (0)1 632 1080 
__ mailto://[EMAIL PROTECTED] 
___http://www.erdw.ethz.ch/_