Compiling 4.0.22 source including some 4.1.x functions
Hi all! I would like to know if it's possible to compile a custom 4.0.22 server including some functionality from 4.1.x source tree. The functionality I would like to include it's neither critical nor substantial. Basically, what I would like to include is the COMPRESS / UNCOMPRESS functions (which I suppose I can include them even as UDF functions). Please, anyone could give me advice on that? Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure?
[...] Suppose you have a table with a million records, test scores from a widely taken exam for example. You need to find the median mark - NOT the average! - so your algorithm needs to read all million records, sort them into ascending or descending sequence by the test score, then read exactly half way through the sequenced list to find the middle record; that's the one that contains the median score. If that work were to happen on the client, the client would have to fetch a million records, sort them all, then read through half of those records until it found the middle record; then it would report on the median mark. There would clearly be a lot of network traffic involved in getting all those records to the client, sorting them and then reading through the sorted records. [...] Pardon my ignorance, but why can't you do this (in MySQL) with a select count ... and afterwards a select ... order by... LIMIT? All the work is done on the server, too. No need for a SP here. Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Remote user problem
Hi, Am Mo, den 22.11.2004 schrieb Danesh Daroui um 16:29: ... /* mysql -h 127.0.0.1 -u root -p Error 1045 (28000): Access denied for user 'root'@'localhost.localdomain'. (using password YES) */ this might be a problem in your DNS. It resolves 127.0.0.1 to 'localhost.localdomain' instead of 'localhost'. Consequently, it does not match the entries in the user table (select host,user from mysql.user). mysql -h localhost -u root -p should work then. Regards, Ingo -- Ingo Strüwing, Senior Software Developer MySQL AB, www.mysql.com Office: +49 30 43672407 Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP-DB] Upgrading mySQL
Hi, your question suggests that you have installed MySQL already. Just install the upgrade in the same way. Start reading at chapter 2.2. If you got MySQL pre-installed with your operating system, you will need an upgrade from your distributor anyway. The paths built in the official MySQL binaries will most probable not match your distribution. Am Mo, den 22.11.2004 schrieb GH um 22:02: What I have been asking for... is that the upgrade does not tell me *HOW* to install this upgrade. WHich file I should download etc I do not see this under the UPGRADE section... That is what I have been asking Also, it says on the manual (section 2.10) It is a good idea to rebuild and reinstall the Perl DBD::mysql module whenever you install a new release of MySQL. The same applies to other MySQL interfaces as well, such as the PHP mysql extension and the Python MySQLdb module. However I do not know how to do this and was asking how to for PHP 4. On Mon, 22 Nov 2004 15:55:25 +0100, Ingo Strüwing [EMAIL PROTECTED] wrote: Hi GH, apart of the contents of the mentioned documents and hints, there is nothing else. Just start the new server in place of the old server with the same options unless told otherwise in the mentioned documents. Am Mo, den 15.11.2004 schrieb GH um 15:06: when I go to both 2.10 Upgrading MySQL (http://dev.mysql.com/doc/mysql/en/Upgrade.html) and 2.10.2 Upgrading from Version 4.0 to 4.1 (http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html) it does not tell me how to upgrade it only tells me about important changes and recomendation. I am looking for a guide of what commands I need to use and so forth. As I said in the inital mailing I made, I am a newbie to Linux. It also says that It is a good idea to rebuild and reinstall the Perl DBD::mysql module whenever you install a new release of MySQL. The same applies to other MySQL interfaces as well, such as the PHP mysql extension and the Python MySQLdb module. [But now how to] Regards, Ingo -- Ingo Strüwing, Senior Software Developer MySQL AB, www.mysql.com Office: +49 30 43672407 Are you MySQL certified? www.mysql.com/certification Regards, Ingo -- Ingo Strüwing, Senior Software Developer MySQL AB, www.mysql.com Office: +49 30 43672407 Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure?
Because you will be downloading 500,000 rows... And I don't really think that was the point. Who cares what the example is. Personally I was quite impressed with great explanation. I would have simply said a chunk of code that runs on the server that the client can call. And this guy took the time to put together a really good reply with an example. Mike Wolfram Kraus wrote: [...] Suppose you have a table with a million records, test scores from a widely taken exam for example. You need to find the median mark - NOT the average! - so your algorithm needs to read all million records, sort them into ascending or descending sequence by the test score, then read exactly half way through the sequenced list to find the middle record; that's the one that contains the median score. If that work were to happen on the client, the client would have to fetch a million records, sort them all, then read through half of those records until it found the middle record; then it would report on the median mark. There would clearly be a lot of network traffic involved in getting all those records to the client, sorting them and then reading through the sorted records. [...] Pardon my ignorance, but why can't you do this (in MySQL) with a select count ... and afterwards a select ... order by... LIMIT? All the work is done on the server, too. No need for a SP here. Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure?
Michael J. Pawlowsky wrote: Because you will be downloading 500,000 rows... And I don't really think that was the point. Who cares what the example is. Personally I was quite impressed with great explanation. I second that it was a great explanation! Please don't get me wrong! And furthermore I will only download one row two times: select count - one row select ... limit count/2, 1 - one row I would have simply said a chunk of code that runs on the server that the client can call. And this guy took the time to put together a really good reply with an example. I don't say that the example is bad, I only said that in MySQL you can do this without a SP. Mike Wolfram Wolfram Kraus wrote: [...] Suppose you have a table with a million records, test scores from a widely taken exam for example. You need to find the median mark - NOT the average! - so your algorithm needs to read all million records, sort them into ascending or descending sequence by the test score, then read exactly half way through the sequenced list to find the middle record; that's the one that contains the median score. If that work were to happen on the client, the client would have to fetch a million records, sort them all, then read through half of those records until it found the middle record; then it would report on the median mark. There would clearly be a lot of network traffic involved in getting all those records to the client, sorting them and then reading through the sorted records. [...] Pardon my ignorance, but why can't you do this (in MySQL) with a select count ... and afterwards a select ... order by... LIMIT? All the work is done on the server, too. No need for a SP here. Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
myisamchk sort buffer too small, check table has ran 1 week and no end in sight
Are these critical errors? It keeps on going, saying it's fixing records I tried doing a check table on the main server, it's been running a week and who knows how long that'll take myisamchk -o -p -f --sort_key_blocks=512M - -key_cache_block_size=512M --read_buffer_size=8M 321st_stat - parallel recovering (with sort) MyISAM-table '321st_stat' Data records: 0 - Fixing index 1 - Fixing index 2 - Fixing index 3 - Fixing index 4 - Fixing index 5 - Fixing index 6 - Fixing index 7 myisamchk: error: sort_buffer_size is to small MyISAM-table '321st_stat' is not fixed because of errors Try fixing it by using the --safe-recover (-o), the --force (-f) option or by no t using the --quick (-q) flag - recovering (with sort) MyISAM-table '321st_stat' Data records: 0 - Fixing index 1 3287000 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure?
news [EMAIL PROTECTED] wrote on 11/30/2004 07:58:18 AM: Michael J. Pawlowsky wrote: Because you will be downloading 500,000 rows... And I don't really think that was the point. Who cares what the example is. Personally I was quite impressed with great explanation. I second that it was a great explanation! Please don't get me wrong! And furthermore I will only download one row two times: select count - one row select ... limit count/2, 1 - one row Who's to say that his SP can't use your method and work just that much faster? The point was that stored procedures can automate complex processes and that the end user of the database doesn't necessarily need to know how the process works (or how to code that process in SQL) if they know how to call the procedure and interpret its results. Stored procedures provides a method for a DBA to efficiently provide DB users with results they would normally need to code by hand to achieve. How many database users do you know that understand how to correctly compute a median value or generate a cross tab query? If you, the DBA, write stored procedures or UDFs to perform these and other complex tasks (relatively speaking) then you have simplified the end user's data access in some significant ways. There are much more complex things you can do with SPs than just computing medians but it made an EXCELLENT example. I would have simply said a chunk of code that runs on the server that the client can call. And this guy took the time to put together a really good reply with an example. I don't say that the example is bad, I only said that in MySQL you can do this without a SP. Yes, but as I mentioned above, that would require some modest SQL skills from the user writing the query. Not all users are as comfortable with SQL as we are as administrators. Even if you give them some cut-and-paste code that did this function, they would still need use it properly. This is especially difficult for those users who rely on visual query builders (GUI interfaces) to automate their SQL generation. But, If I give them the name of a stored procedure that reliably computes what they need then the time I spend helping those who don't want to learn SQL to write useful queries goes down considerably. Mike Wolfram Shawn Green Database Administrator Unimin Corporation - Spruce Pine Wolfram Kraus wrote: [...] Suppose you have a table with a million records, test scores from a widely taken exam for example. You need to find the median mark - NOT the average! - so your algorithm needs to read all million records, sort them into ascending or descending sequence by the test score, then read exactly half way through the sequenced list to find the middle record; that's the one that contains the median score. If that work were to happen on the client, the client would have to fetch a million records, sort them all, then read through half of those records until it found the middle record; then it would report on the median mark. There would clearly be a lot of network traffic involved in getting all those records to the client, sorting them and then reading through the sorted records. [...] Pardon my ignorance, but why can't you do this (in MySQL) with a select count ... and afterwards a select ... order by... LIMIT? All the work is done on the server, too. No need for a SP here. Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure?
Heyho! [EMAIL PROTECTED] wrote: news [EMAIL PROTECTED] wrote on 11/30/2004 07:58:18 AM: Michael J. Pawlowsky wrote: Because you will be downloading 500,000 rows... And I don't really think that was the point. Who cares what the example is. Personally I was quite impressed with great explanation. I second that it was a great explanation! Please don't get me wrong! And furthermore I will only download one row two times: select count - one row select ... limit count/2, 1 - one row Who's to say that his SP can't use your method and work just that much faster? That would be the optimal solution for MySQL 5.x ;-) The point was that stored procedures can automate complex processes and that the end user of the database doesn't necessarily need to know how the process works (or how to code that process in SQL) if they know how to call the procedure and interpret its results. Stored procedures provides a method for a DBA to efficiently provide DB users with results they would normally need to code by hand to achieve. How many database users do you know that understand how to correctly compute a median value or generate a cross tab query? If you, the DBA, write stored procedures or UDFs to perform these and other complex tasks (relatively speaking) then you have simplified the end user's data access in some significant ways. There are much more complex things you can do with SPs than just computing medians but it made an EXCELLENT example. My original posting was a little bit short, sorry for that! I know what SPs are, I only wanted to point out that you don't need SPs to get the median without heavy calculations on the client. The definition of user levels/roles is another story. Btw: Rhino was missing/hiding the part with hiding complexity from users in his excellent explanation. I would have simply said a chunk of code that runs on the server that the client can call. And this guy took the time to put together a really good reply with an example. I don't say that the example is bad, I only said that in MySQL you can do this without a SP. Yes, but as I mentioned above, that would require some modest SQL skills from the user writing the query. Not all users are as comfortable with SQL as we are as administrators. Even if you give them some cut-and-paste code that did this function, they would still need use it properly. This is especially difficult for those users who rely on visual query builders (GUI interfaces) to automate their SQL generation. But, If I give them the name of a stored procedure that reliably computes what they need then the time I spend helping those who don't want to learn SQL to write useful queries goes down considerably. Point taken, nice example ;-) I am not really an DBA, I am more like a db-user (not in your way of definition) ;-) As I said above: definition of user levels/roles are a complete different thing. Mike Wolfram Shawn Green Database Administrator Unimin Corporation - Spruce Pine Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ibdata1 filesize.
Well i´m having another problem with ibdata1. It´s too big. I looked at mysql documentation and as it looks for i don´t have a way to reduce ibdata1´s size except following those passes from below: -Dump the content of databases -Stop the server -delete a ibdata file and iblogfiles -start the server -import the dumped file Is this the only way to reduce ibdata file? Thanks in advance. Sorry for bad english -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Performance impact -- multiple databases Vs multiple tables...
Hi All, I tried digging for this information in the archives but could not find anything. I am in to developing an app. that uses very high amount of data (Close to 80 GB per machine). It has 3-4 logical tables. But I have to partition them in to multiple tables because the mysql table size is limited by the system file size (even if I enable large file size support, I don't want to have 30 GB large tables). I see two options here. 1) Have a single database and create one table-set (set of 3-4 tables, each of them representing one partition of the logical table) every time the table size grows beyond a certain limit(say 100MB). But this way, I might end up having thousands of tables in a single database. 2) Create one mysql-database for each table-set.This way, I'll end up having hundreds of databases in the mysql data directory. Is any one of these two methods preferable over the other because of the way mysql caches the information ? In other words, which one of the above mentioned options exerts a heavier load on the mysql server ? One more parallel question is, because I have so many databases in my data directory, is it a good decision to run multiple mysql server instances (Divide the data space in to multiple partitions and have one mysql server instance handle one of those data partitions) ? I am thinking abt this because 1)As the number of tables/databases grows, mysql server will have to open more files in order to serve requests. And because of the limit on max number of open files by a process, it will be forced to close some tables to open other tables. 2) In general, the resources(like memory and CPU) allocated to a process are limited by the OS and it would reach the limit as the load grows. Having multiple mysql server instances could help in those cases, I guess. (Even though mysql is multi-threaded it's eventually one process running multiple threads) But I couldn't find any use cases where people run multiple mysql server instances for performance improvements. Am I missing something? Thanks in advance. -Alok. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Stored Procedure?
I think a much better example of store procedures is one showing actual
procedural processing. Whilst they also allow a lot less communication
over the network, and a lot less SQL knowledge for the user(!) - a
stored procedure is also a way of doing all the work that you currently
get the client to do (thinking things like PHP with IF, WHILE or LOOP
statements).
So, if you have a long transaction that does multiple round trips from
the client - which could be your web server), whilst looping through
the results in the client code and doing something else with mysql -
then a stored procedure is where it's at.
Here's a fairly easy example of one I use in Oracle, that allows an
admin user to reset the passwords for only a specific set of users:
CREATE PROCEDURE reset_password
(rp_username in varchar2,
rp_password in varchar2)
as
begin
if ( USER = 'ADMIN' and rp_username in
('GERRY', 'LYNNE', 'KRIS', 'STEPH') )
then
execute immediate
'alter user ' || rp_username ||
' identified by ' || rp_password;
else
send_admin_mail
('Admin trying to change password for ' || rp_username );
raise_application_error
( -20001, 'Change not Authorised' );
end if;
end;
The above will ensure that only the ADMIN user can execute the change
(enforcing extra security). It will then check that the user being
changed is within the given list, and if it is - it will run the
password change script (alter user LYNNE identified by 'password';).
However, if any of the aforementioned check fail, it will first of all
execute another procedure (send_admin_email), passing the message along
with the substitued username to be used as the message body. It will
then generate an application error to the user with another procedure.
Don't get me wrong Rhino, your example was great, and is still very
applicable.. I just thought this might be a slightly more insightful
example of what SP's can really handle.
Best regards
Mark
Mark Leith
Cool-Tools UK Limited
http://www.cool-tools.co.uk
-Original Message-
From: news [mailto:[EMAIL PROTECTED] On Behalf Of Wolfram Kraus
Sent: 30 November 2004 12:58
To: [EMAIL PROTECTED]
Subject: Re: Stored Procedure?
Michael J. Pawlowsky wrote:
Because you will be downloading 500,000 rows... And I don't really
think that was the point. Who cares what the example is. Personally I
was quite impressed with great explanation.
I second that it was a great explanation! Please don't get me wrong! And
furthermore I will only download one row two times: select count - one
row select ... limit count/2, 1 - one row
I would have simply said a chunk of code that runs on the server that
the client can call. And this guy took the time to put together a
really good reply with an example.
I don't say that the example is bad, I only said that in MySQL you can
do this without a SP.
Mike
Wolfram
Wolfram Kraus wrote:
[...]
Suppose you have a table with a million records, test scores from a
widely taken exam for example. You need to find the median mark
- NOT the average! - so your algorithm needs to read all million
records, sort them into ascending or descending sequence by the test
score, then read exactly half way through the sequenced list to find
the middle record; that's the one that contains the median score.
If that work were to happen on the client, the client would have to
fetch a million records, sort them all, then read through half of
those records until it found the middle record; then it would report
on the median mark. There would clearly be a lot of network traffic
involved in getting all those records to the client, sorting them
and then reading through the sorted records.
[...] Pardon my ignorance, but why can't you do this (in MySQL) with
a select count ... and afterwards a select ... order by... LIMIT?
All the work is done on the server, too. No need for a SP here.
Wolfram
--
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.289 / Virus Database: 265.4.3 - Release Date: 26/11/2004
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.289 / Virus Database: 265.4.3 - Release Date: 26/11/2004
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure?
- Original Message - From: Wolfram Kraus [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 30, 2004 10:04 AM Subject: Re: Stored Procedure? Heyho! [EMAIL PROTECTED] wrote: news [EMAIL PROTECTED] wrote on 11/30/2004 07:58:18 AM: Michael J. Pawlowsky wrote: Because you will be downloading 500,000 rows... And I don't really think that was the point. Who cares what the example is. Personally I was quite impressed with great explanation. I second that it was a great explanation! Please don't get me wrong! And furthermore I will only download one row two times: select count - one row select ... limit count/2, 1 - one row Who's to say that his SP can't use your method and work just that much faster? That would be the optimal solution for MySQL 5.x ;-) The point was that stored procedures can automate complex processes and that the end user of the database doesn't necessarily need to know how the process works (or how to code that process in SQL) if they know how to call the procedure and interpret its results. Stored procedures provides a method for a DBA to efficiently provide DB users with results they would normally need to code by hand to achieve. How many database users do you know that understand how to correctly compute a median value or generate a cross tab query? If you, the DBA, write stored procedures or UDFs to perform these and other complex tasks (relatively speaking) then you have simplified the end user's data access in some significant ways. There are much more complex things you can do with SPs than just computing medians but it made an EXCELLENT example. My original posting was a little bit short, sorry for that! I know what SPs are, I only wanted to point out that you don't need SPs to get the median without heavy calculations on the client. The definition of user levels/roles is another story. Btw: Rhino was missing/hiding the part with hiding complexity from users in his excellent explanation. You're absolutely right; I failed to mention the benefits of making the users lives easier by letting the administrators do the heavy lifting via stored procedures. In truth, I simply didn't think of that benefit at the time (it was late and I was overdue for bedtime ;-) but I probably would have omitted it any way simply because the original question didn't make me think of those issues. I was mostly just focusing on what a stored procedure was since that is what the questioner seemed to want. Shawn was absolutely right to add the benefits of hiding the complexity from users. I would have simply said a chunk of code that runs on the server that the client can call. And this guy took the time to put together a really good reply with an example. I don't say that the example is bad, I only said that in MySQL you can do this without a SP. Do you mean that you could run the same code that was in the stored procedure from the command line, therefore eliminating the need for an SP? Well, yes, that is true but how would you get the result to a client program? Or would you force users to sign on to the server to execute the code from the server's command line? Assuming stored procedures are implemented similarily in MySQL to the way they are in DB2, a stored procedure would work from both the server's command line *and* a client program. That means you simply build your stored procedure once and can handle both scenarios. If you want to invoke it from a client program, you simply call it, passing the necessary parameters and then handle the result within the client program. If you prefer to execute it right at the server, you can do that too with the same call statement you used from the client program, except that you hard code the values; then, the operating system displays the result of the stored procedure on the console. Yes, but as I mentioned above, that would require some modest SQL skills from the user writing the query. Not all users are as comfortable with SQL as we are as administrators. Even if you give them some cut-and-paste code that did this function, they would still need use it properly. This is especially difficult for those users who rely on visual query builders (GUI interfaces) to automate their SQL generation. But, If I give them the name of a stored procedure that reliably computes what they need then the time I spend helping those who don't want to learn SQL to write useful queries goes down considerably. Point taken, nice example ;-) I am not really an DBA, I am more like a db-user (not in your way of definition) ;-) As I said above: definition of user levels/roles are a complete different thing. Mike Wolfram Shawn Green Database Administrator Unimin Corporation - Spruce Pine Wolfram Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL
Re: Stored Procedure?
- Original Message -
From: Mark Leith [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, November 30, 2004 10:47 AM
Subject: RE: Stored Procedure?
I think a much better example of store procedures is one showing actual
procedural processing. Whilst they also allow a lot less communication
over the network, and a lot less SQL knowledge for the user(!) - a
stored procedure is also a way of doing all the work that you currently
get the client to do (thinking things like PHP with IF, WHILE or LOOP
statements).
So, if you have a long transaction that does multiple round trips from
the client - which could be your web server), whilst looping through
the results in the client code and doing something else with mysql -
then a stored procedure is where it's at.
Here's a fairly easy example of one I use in Oracle, that allows an
admin user to reset the passwords for only a specific set of users:
CREATE PROCEDURE reset_password
(rp_username in varchar2,
rp_password in varchar2)
as
begin
if ( USER = 'ADMIN' and rp_username in
('GERRY', 'LYNNE', 'KRIS', 'STEPH') )
then
execute immediate
'alter user ' || rp_username ||
' identified by ' || rp_password;
else
send_admin_mail
('Admin trying to change password for ' || rp_username );
raise_application_error
( -20001, 'Change not Authorised' );
end if;
end;
The above will ensure that only the ADMIN user can execute the change
(enforcing extra security). It will then check that the user being
changed is within the given list, and if it is - it will run the
password change script (alter user LYNNE identified by 'password';).
However, if any of the aforementioned check fail, it will first of all
execute another procedure (send_admin_email), passing the message along
with the substitued username to be used as the message body. It will
then generate an application error to the user with another procedure.
Don't get me wrong Rhino, your example was great, and is still very
applicable.. I just thought this might be a slightly more insightful
example of what SP's can really handle.
No offense taken, Mark. You've simply given a more advanced example that
illustrates even more capabilities of a stored procedure. It's a great
supplemental example.
I was simply citing the 'classic' example that I've taught in DB2 courses.
Those courses were for people who were new to stored procedures (and many
other aspects of DB2) and needed to know the basic concepts before trying to
write one. That seemed to be the level of the person who asked the original
question. However, your example illustrates how much more a stored procedure
can do so it should certainly be strongly considered by all developers,
particularly advanced ones.
I just wish that MySQL was a year or two further along than it is so that
5.x would be fairly mature and we could actually start coding stored
procedures, views, etc
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB Log and binlog files and Solid State Disk?
Hello, I happen to be in a position to utilize a 6GB FC solid state disk (RAM based with battery and disk backup) on our new storage area network. I haven't ever used an SSD for MySQL before, but I am aware of the potential performance benefits. My question is, does anyone have any suggestions on how to best deploy it? We have two machines, each dual opteron 250's with 4GB of ram ea. These are to be configured with steeleye's lifekeeper (not done yet) for active/passive failover. Currently we have 4 LUN's available via 2Gbps FC, each with 120 some odd gigs to be used for storage. Also the 6GB SSD. Our current InnoDB table space size is about 200GB. I planned on putting the innodb log log files on the SSD. These are not that large on the system we are gonig to replace, about 90 megs each for 3 of them. Does it make much sense to increase these significantly? Normally this would be bad in case of roll backs (we often do large transactions) that take a long time. Any opinions on how much might the fact that the innodb logs are on the SSD effect the rollback time? Further, I had been thinking to place the most recent mysql binlogs on the SSD as well. Is there anything else other than perhaps temp table space that I might consider placing on SSD? Any input is appreciated. Thanks, Richard signature.asc Description: This is a digitally signed message part
RE: Stored Procedure?
I just wish that MySQL was a year or two further along than it is so
that 5.x would be fairly mature and we could actually start coding
stored procedures, views, etc
Oh indeed, neither can I! Not just procedures and views either, but also
triggers and sequences! And a job scheduling system would be fantastic
as well!!
Mark dreaming away the day
-Original Message-
From: Rhino [mailto:[EMAIL PROTECTED]
Sent: 30 November 2004 16:04
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Stored Procedure?
- Original Message -
From: Mark Leith [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, November 30, 2004 10:47 AM
Subject: RE: Stored Procedure?
I think a much better example of store procedures is one showing
actual procedural processing. Whilst they also allow a lot less
communication over the network, and a lot less SQL knowledge for the
user(!) - a stored procedure is also a way of doing all the work
that you currently get the client to do (thinking things like PHP
with IF, WHILE or LOOP statements).
So, if you have a long transaction that does multiple round trips from
the client - which could be your web server), whilst looping through
the results in the client code and doing something else with mysql
- then a stored procedure is where it's at.
Here's a fairly easy example of one I use in Oracle, that allows an
admin user to reset the passwords for only a specific set of users:
CREATE PROCEDURE reset_password
(rp_username in varchar2,
rp_password in varchar2)
as
begin
if ( USER = 'ADMIN' and rp_username in
('GERRY', 'LYNNE', 'KRIS', 'STEPH') )
then
execute immediate
'alter user ' || rp_username ||
' identified by ' || rp_password;
else
send_admin_mail
('Admin trying to change password for ' || rp_username );
raise_application_error ( -20001, 'Change not Authorised' );
end if;
end;
The above will ensure that only the ADMIN user can execute the change
(enforcing extra security). It will then check that the user being
changed is within the given list, and if it is - it will run the
password change script (alter user LYNNE identified by 'password';).
However, if any of the aforementioned check fail, it will first of all
execute another procedure (send_admin_email), passing the message
along with the substitued username to be used as the message body. It
will then generate an application error to the user with another
procedure.
Don't get me wrong Rhino, your example was great, and is still very
applicable.. I just thought this might be a slightly more insightful
example of what SP's can really handle.
No offense taken, Mark. You've simply given a more advanced example that
illustrates even more capabilities of a stored procedure. It's a great
supplemental example.
I was simply citing the 'classic' example that I've taught in DB2
courses. Those courses were for people who were new to stored procedures
(and many other aspects of DB2) and needed to know the basic concepts
before trying to write one. That seemed to be the level of the person
who asked the original question. However, your example illustrates how
much more a stored procedure can do so it should certainly be strongly
considered by all developers, particularly advanced ones.
I just wish that MySQL was a year or two further along than it is so
that 5.x would be fairly mature and we could actually start coding
stored procedures, views, etc
Rhino
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.289 / Virus Database: 265.4.3 - Release Date: 26/11/2004
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.289 / Virus Database: 265.4.3 - Release Date: 26/11/2004
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure?
[EMAIL PROTECTED] wrote: news [EMAIL PROTECTED] wrote on 11/30/2004 07:58:18 AM: Michael J. Pawlowsky wrote: Because you will be downloading 500,000 rows... And I don't really think that was the point. Who cares what the example is. Personally I was quite impressed with great explanation. I second that it was a great explanation! Please don't get me wrong! And furthermore I will only download one row two times: select count - one row select ... limit count/2, 1 - one row Who's to say that his SP can't use your method and work just that much faster? The point was that stored procedures can automate complex processes and that the end user of the database doesn't necessarily need to know how the process works (or how to code that process in SQL) if they know how to call the procedure and interpret its results. Stored procedures provides a method for a DBA to efficiently provide DB users with results they would normally need to code by hand to achieve. How many database users do you know that understand how to correctly compute a median value or generate a cross tab query? If you, the DBA, write stored procedures or UDFs to perform these and other complex tasks (relatively speaking) then you have simplified the end user's data access in some significant ways. There are much more complex things you can do with SPs than just computing medians but it made an EXCELLENT example. As a mathematician, I'd like to point out that medians aren't quite that simple. select ... limit count/2, 1 will not work at least half the time. There are two possibilities: * count is odd - The median is the value in the middle, but count/2 is a decimal, so you have something like LIMIT 13.5, 1. Mysql (4.1.7, anyway) handles this by ignoring the decimal and gives the correct answer, but this is problematic. The manual http://dev.mysql.com/doc/mysql/en/SELECT.html clearly states LIMIT takes one or two numeric arguments, which must be integer constants. Hence we are relying on an undocumented feature which could easily disappear. * count is even - In this case, there is no middle value! The median is the average of the 2 values on either side of the middle. count/2 is a positive integer, however, so limit count/2, 1 will retrieve a row, but it is *not* the median. Hence, network traffic is not an issue, but there is still work to be done. You have to get the count, check if it is even or odd, then proceed accordingly. In the even case, you have to retrieve two rows, then average them. You can do all this in code on the client end, or do it on the server in a stored procedure, making the client's life easier (and improving his/her chances of getting it right). For completeness, here's a method to get the median in SQL: To get the median of the values in a column (val): CREATE TEMPORARY TABLE medians SELECT x.val medians FROM data x, data y GROUP BY x.val HAVING SUM(y.val = x.val) = COUNT(*)/2 AND SUM(y.val = x.val) = COUNT(*)/2; SELECT AVG(medians) AS median FROM medians; DROP TABLE medians; To get the groupwise median of the values in a column (val) for each value in another column (name): CREATE TEMPORARY TABLE medians SELECT x.name, x.val medians FROM data x, data y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val = x.val) = COUNT(*)/2 AND SUM(y.val = x.val) = COUNT(*)/2; SELECT name, AVG(medians) AS median FROM medians GROUP BY name; DROP TABLE medians; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Illegal mix of collations with 4.1.7
Ever since we upgraded to 4.1.7, we've been seeing a lot of errors similiar to this one: ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'locate' The query which generated this particular error is this: SELECT COUNT(*) FROM holdsplaced WHERE timestampDatePlaced = 2004113004 AND INSTR( sPatronName, 'bubba' ) != 0; But other queries have also been kicking this error out as well. The server is being started with the following options (some altered to protect the innocent): mysqld would have been started with the following arguments: --basedir=/dbs/tpp/mysql-4.1 --datadir=/dbs/tpp/mysql-4.1/data --port=portnum --socket=/dbs/tpp/mysql-4.1/mysql.sock --user=username --log-error=/dbs/tpp/mysql-4.1/logs/ping.err --log=/dbs/tpp/mysql-4.1/logs/ping.log --default-character-set=utf8 The problem, I'm sure, is that --default-character-set=utf8 option, but I don't know much beyond that. Google searches aren't helping much with this one. Something is out of alignment somewhere, I'm just not sure what it is. Any help? Thanks in advance, --V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance impact -- multiple databases Vs multiple tables...
If you are hitting file size limits, you probably want to look into using the InnoDB table type. That will allow you to work around file size limits and have a database of just about any size you need. You won't end up having a 30GB file, but multiple smaller files which will be transparent to your application. InnoDB tables are probably your best place to look for a solution. If you must use MyISAM table types, perhaps look into the RAID option for MyISAM tables. Although that will still have it's limits, mainly because of index size limits. If you are worried about load, look into replication. One machine will act as the master where all updates will occur, but then any of your replication machines can handle queries. You could setup a round robin DNS to automatically split the load or go with something even more sophisticated. You also might see what you can find on MySQL clusters, although this is a fairly new feature (this year) and I myself have not read up on it. On Nov 30, 2004, at 10:17 AM, Alok Gore wrote: Hi All, I tried digging for this information in the archives but could not find anything. I am in to developing an app. that uses very high amount of data (Close to 80 GB per machine). It has 3-4 logical tables. But I have to partition them in to multiple tables because the mysql table size is limited by the system file size (even if I enable large file size support, I don't want to have 30 GB large tables). I see two options here. 1) Have a single database and create one table-set (set of 3-4 tables, each of them representing one partition of the logical table) every time the table size grows beyond a certain limit(say 100MB). But this way, I might end up having thousands of tables in a single database. 2) Create one mysql-database for each table-set.This way, I'll end up having hundreds of databases in the mysql data directory. Is any one of these two methods preferable over the other because of the way mysql caches the information ? In other words, which one of the above mentioned options exerts a heavier load on the mysql server ? One more parallel question is, because I have so many databases in my data directory, is it a good decision to run multiple mysql server instances (Divide the data space in to multiple partitions and have one mysql server instance handle one of those data partitions) ? I am thinking abt this because 1)As the number of tables/databases grows, mysql server will have to open more files in order to serve requests. And because of the limit on max number of open files by a process, it will be forced to close some tables to open other tables. 2) In general, the resources(like memory and CPU) allocated to a process are limited by the OS and it would reach the limit as the load grows. Having multiple mysql server instances could help in those cases, I guess. (Even though mysql is multi-threaded it's eventually one process running multiple threads) But I couldn't find any use cases where people run multiple mysql server instances for performance improvements. Am I missing something? Thanks in advance. -Alok. -- 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]
Add
Re: host blocked, but can't see errors
Hello. See: http://dev.mysql.com/doc/mysql/en/Debugging_server.html Scott Tanner [EMAIL PROTECTED] wrote: I've been having this issue as well, happening more frequently to our production web /ejb servers. I've increased the logging to warning level, but my logs don't contain much (if any) information. Is there a way to increase logging to debug level, or get more information as to what is causing the problem? I want to make sure the application is not mishandling the connections before increasing the max_connection_errors. Thank you, Scott - Original Message --- -- 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: Performance impact -- multiple databases Vs multiple tables...
Hello. Think about merge storage. http://dev.mysql.com/doc/mysql/en/MERGE_storage_engine.html Alok Gore [EMAIL PROTECTED] wrote: Hi All, I tried digging for this information in the archives but could not find anything. I am in to developing an app. that uses very high amount of data (Close to 80 GB per machine). It has 3-4 logical tables. But I have to partition them in to multiple tables because the mysql table size is limited by the system file size (even if I enable large file size support, I don't want to have 30 GB large tables). I see two options here. 1) Have a single database and create one table-set (set of 3-4 tables, each of them representing one partition of the logical table) every time the table size grows beyond a certain limit(say 100MB). But this way, I might end up having thousands of tables in a single database. 2) Create one mysql-database for each table-set.This way, I'll end up having hundreds of databases in the mysql data directory. Is any one of these two methods preferable over the other because of the way mysql caches the information ? In other words, which one of the above mentioned options exerts a heavier load on the mysql server ? One more parallel question is, because I have so many databases in my data directory, is it a good decision to run multiple mysql server instances (Divide the data space in to multiple partitions and have one mysql server instance handle one of those data partitions) ? I am thinking abt this because 1)As the number of tables/databases grows, mysql server will have to open more files in order to serve requests. And because of the limit on max number of open files by a process, it will be forced to close some tables to open other tables. 2) In general, the resources(like memory and CPU) allocated to a process are limited by the OS and it would reach the limit as the load grows. Having multiple mysql server instances could help in those cases, I guess. (Even though mysql is multi-threaded it's eventually one process running multiple threads) But I couldn't find any use cases where people run multiple mysql server instances for performance improvements. Am I missing something? Thanks in advance. -Alok. -- 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: myisamchk sort buffer too small, check table has ran 1 week and no end in sight
Hello. There is a variable sort_buffer, which you can set with -O sort_buffer=xxxM or in my.cnf. matt_lists [EMAIL PROTECTED] wrote: Are these critical errors? It keeps on going, saying it's fixing records I tried doing a check table on the main server, it's been running a week and who knows how long that'll take myisamchk -o -p -f --sort_key_blocks=512M - -key_cache_block_size=512M --read_buffer_size=8M 321st_stat - parallel recovering (with sort) MyISAM-table '321st_stat' Data records: 0 - Fixing index 1 - Fixing index 2 - Fixing index 3 - Fixing index 4 - Fixing index 5 - Fixing index 6 - Fixing index 7 myisamchk: error: sort_buffer_size is to small MyISAM-table '321st_stat' is not fixed because of errors Try fixing it by using the --safe-recover (-o), the --force (-f) option or by no t using the --quick (-q) flag - recovering (with sort) MyISAM-table '321st_stat' Data records: 0 - Fixing index 1 3287000 -- 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: Illegal mix of collations with 4.1.7
Hello, I think your tables have a collation different from the connection collation. Open mysql client: mysql show variables like 'colla%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.04 sec) and if are different set the collation in your my.cnf file. Santino At 8:59 -0800 30-11-2004, V. M. Brasseur wrote: Ever since we upgraded to 4.1.7, we've been seeing a lot of errors similiar to this one: ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'locate' The query which generated this particular error is this: SELECT COUNT(*) FROM holdsplaced WHERE timestampDatePlaced = 2004113004 AND INSTR( sPatronName, 'bubba' ) != 0; But other queries have also been kicking this error out as well. The server is being started with the following options (some altered to protect the innocent): mysqld would have been started with the following arguments: --basedir=/dbs/tpp/mysql-4.1 --datadir=/dbs/tpp/mysql-4.1/data --port=portnum --socket=/dbs/tpp/mysql-4.1/mysql.sock --user=username --log-error=/dbs/tpp/mysql-4.1/logs/ping.err --log=/dbs/tpp/mysql-4.1/logs/ping.log --default-character-set=utf8 The problem, I'm sure, is that --default-character-set=utf8 option, but I don't know much beyond that. Google searches aren't helping much with this one. Something is out of alignment somewhere, I'm just not sure what it is. Any help? Thanks in advance, --V -- 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: Illegal mix of collations with 4.1.7
Ah! Many thanks. That appears to be our problem here: mysql show variables like 'colla%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | +--+---+ I'll work on getting all the connections onto the same collation page. Very many thanks again! --V Santino wrote: Hello, I think your tables have a collation different from the connection collation. Open mysql client: mysql show variables like 'colla%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.04 sec) and if are different set the collation in your my.cnf file. Santino At 8:59 -0800 30-11-2004, V. M. Brasseur wrote: Ever since we upgraded to 4.1.7, we've been seeing a lot of errors similiar to this one: ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'locate' The query which generated this particular error is this: SELECT COUNT(*) FROM holdsplaced WHERE timestampDatePlaced = 2004113004 AND INSTR( sPatronName, 'bubba' ) != 0; But other queries have also been kicking this error out as well. The server is being started with the following options (some altered to protect the innocent): mysqld would have been started with the following arguments: --basedir=/dbs/tpp/mysql-4.1 --datadir=/dbs/tpp/mysql-4.1/data --port=portnum --socket=/dbs/tpp/mysql-4.1/mysql.sock --user=username --log-error=/dbs/tpp/mysql-4.1/logs/ping.err --log=/dbs/tpp/mysql-4.1/logs/ping.log --default-character-set=utf8 The problem, I'm sure, is that --default-character-set=utf8 option, but I don't know much beyond that. Google searches aren't helping much with this one. Something is out of alignment somewhere, I'm just not sure what it is. Any help? Thanks in advance, --V -- 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]
SQL
Hi, My Table No | Type | ID 1v 3233 2h 5123 3v 1345 4v 5457 5h 8756 6h 8757 7v 8067 Select random No (i know rand()) Select two row (i know limit 0,2) Select Type v,h or h,v but dont want v,v or h,h Example Result i want 5 h 8756 7 v 8067 Or 1 v 3233 6 h 8757 But i dont want 1 v 3233 7 v 6067 or 5 h 8756 6 h 8757 Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tunning Problem
Sasha, Ronan: InnoDB complains it cannot allocate memory. With your configuration you are likely to run out of memory: You are telling InnoDB to allocate at least 256 MB + 20 MB for the buffer pool. On top of that, you are telling MyISAM to use 384 MB for the key buffer. So this is already over 700 MB. Then you start connecting. Each time you connect, you have some overhead on the order of a few megabytes. Times 55, and you can easily eat up the remaining 300 MB. Also, mysqld is probably not the only process on the system. Hmmm, you´re right. Thanks for clearify my thoughts. A good tunning seems to be a hard task. The MySQL manual page says: --- # Set buffer pool size to 50-80% of your computer's memory, # but make sure on Linux x86 total memory usage is 2GB --- (http://dev.mysql.com/doc/mysql/en/InnoDB_configuration.html) for a computer with 2 Gb of memory, and in some tunning docs that I´ve been looking I´ve found this: --- If you have much memory (=256M) and many tables and want maximum performance with a moderate number of clients, you should use something like this: shell safe_mysqld -O key_buffer=64M -O table_cache=256 \ -O sort_buffer=4M -O record_buffer=1M ---(http://www.tnt.uni-hannover.de/print/plain/soft/dat abase/MySQL/Docs/manual_Performance.html) If you know a good documentation about it or if you have suggestions how can I improve my configuration, please tell me. Thank you very much -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL
Diner Akay wrote: Hi, My Table No | Type | ID [snip] Select random No (i know rand()) Select two row (i know limit 0,2) Select Type v,h or h,v but dont want v,v or h,h How about selecting one random row where Type is v and one where Type is h and then UNIONing the two record sets? Stephen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrade from mysql 3.23 to 4.1
On Monday 29 November 2004 11:35 pm, Greg Macek wrote: I was thinking about this as well, but was hoping to minimize the amount of work I would have to do. However, if this makes the most sense for upgrading, perhaps this is what I'll do. Actually, I think you will be minimizing going the 4.0 route. These major upgrades are no small tasks anyway. Jeff pgpV2KwnnCgL1.pgp Description: PGP signature
Re: Stored Procedure?
Well, I think you've just succeeded in demolishing my wonderful example; it turns out that we don't need to use a stored procedure to find a median after all ;-) You're right that the algorithm I described in my original reply is a bit simplified and assumes an odd number of rows; it doesn't handle the case where the number of rows is even. I assume that was just for the convenience of the person who wrote the course materials I was teaching; they didn't want to get bogged down in the subtleties of the details of calculating a median. I have to admit I've never seen an SQL query that would compute a median before. I'm not sure I completely understand your query, particularly the GROUP BY and HAVING clauses - I know what GROUP BY and HAVING do in general, I'm just not sure what they are accomplishing in *this* case - but you're a mathematician so I'll assume that the query is accurate and will work for both odd and even numbered sets of rows ;-) It looks like I'll have to come up with a more bulletproof example of a stored procedure before I next teach the concepts. Rhino - Original Message - From: Michael Stassen [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Wolfram Kraus [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; news [EMAIL PROTECTED] Sent: Tuesday, November 30, 2004 11:49 AM Subject: Re: Stored Procedure? [EMAIL PROTECTED] wrote: news [EMAIL PROTECTED] wrote on 11/30/2004 07:58:18 AM: Michael J. Pawlowsky wrote: Because you will be downloading 500,000 rows... And I don't really think that was the point. Who cares what the example is. Personally I was quite impressed with great explanation. I second that it was a great explanation! Please don't get me wrong! And furthermore I will only download one row two times: select count - one row select ... limit count/2, 1 - one row Who's to say that his SP can't use your method and work just that much faster? The point was that stored procedures can automate complex processes and that the end user of the database doesn't necessarily need to know how the process works (or how to code that process in SQL) if they know how to call the procedure and interpret its results. Stored procedures provides a method for a DBA to efficiently provide DB users with results they would normally need to code by hand to achieve. How many database users do you know that understand how to correctly compute a median value or generate a cross tab query? If you, the DBA, write stored procedures or UDFs to perform these and other complex tasks (relatively speaking) then you have simplified the end user's data access in some significant ways. There are much more complex things you can do with SPs than just computing medians but it made an EXCELLENT example. As a mathematician, I'd like to point out that medians aren't quite that simple. select ... limit count/2, 1 will not work at least half the time. There are two possibilities: * count is odd - The median is the value in the middle, but count/2 is a decimal, so you have something like LIMIT 13.5, 1. Mysql (4.1.7, anyway) handles this by ignoring the decimal and gives the correct answer, but this is problematic. The manual http://dev.mysql.com/doc/mysql/en/SELECT.html clearly states LIMIT takes one or two numeric arguments, which must be integer constants. Hence we are relying on an undocumented feature which could easily disappear. * count is even - In this case, there is no middle value! The median is the average of the 2 values on either side of the middle. count/2 is a positive integer, however, so limit count/2, 1 will retrieve a row, but it is *not* the median. Hence, network traffic is not an issue, but there is still work to be done. You have to get the count, check if it is even or odd, then proceed accordingly. In the even case, you have to retrieve two rows, then average them. You can do all this in code on the client end, or do it on the server in a stored procedure, making the client's life easier (and improving his/her chances of getting it right). For completeness, here's a method to get the median in SQL: To get the median of the values in a column (val): CREATE TEMPORARY TABLE medians SELECT x.val medians FROM data x, data y GROUP BY x.val HAVING SUM(y.val = x.val) = COUNT(*)/2 AND SUM(y.val = x.val) = COUNT(*)/2; SELECT AVG(medians) AS median FROM medians; DROP TABLE medians; To get the groupwise median of the values in a column (val) for each value in another column (name): CREATE TEMPORARY TABLE medians SELECT x.name, x.val medians FROM data x, data y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val = x.val) = COUNT(*)/2 AND SUM(y.val = x.val) = COUNT(*)/2; SELECT name, AVG(medians) AS median FROM medians GROUP BY name; DROP TABLE medians; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
update and concat
I'm trying to set all the values of column 'map' to the value of column ML and '.png'. My intuition and an extensive reading of the manual and mail archives tell me to do it like this: mysql update listings set map= concat (ML, '.png') where ML''; but all I get is this: ERROR 1064 (42000): 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 '(ML, '.png') where ML''' at line 1 What am I doing wrong? Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: myisamchk sort buffer too small, check table has ran 1 week and no end in sight
Gleb Paharenko wrote: Hello. There is a variable sort_buffer, which you can set with -O sort_buffer=xxxM or in my.cnf. myisamchk -o -p -f --sort_key_blocks=512M - -key_cache_block_size=512M it's no longer -O sort_buffer=xxxM it's now --sort_key_blocks and I set it to 512M and it still said there was not enough -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update and concat
Thomas McDonough wrote: I'm trying to set all the values of column 'map' to the value of column ML and '.png'. My intuition and an extensive reading of the manual and mail archives tell me to do it like this: mysql update listings set map= concat (ML, '.png') where ML''; but all I get is this: ERROR 1064 (42000): 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 '(ML, '.png') where ML''' at line 1 What am I doing wrong? Don't use a space between 'concat' and the parantheses, it is a function call: update listings set map= concat(ML, '.png') where ML''; -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update and concat
Thomas McDonough wrote:
I'm trying to set all the values of column 'map' to the value of
column ML and '.png'. My intuition and an extensive reading of the
manual and mail archives tell me to do it like this:
mysql update listings set map= concat (ML, '.png') where ML'';
mysql update listings set map= concat(ML, '.png') where ML'';
Lose the space between 'concat' and '('
but all I get is this:
ERROR 1064 (42000): 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 '(ML, '.png') where ML''' at line 1
What am I doing wrong?
Tom
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update and concat
That did not do it. I'm still getting the same error message (?)
Tom
On Nov 30, 2004, at 2:09 PM, gerald_clark wrote:
Thomas McDonough wrote:
I'm trying to set all the values of column 'map' to the value of
column ML and '.png'. My intuition and an extensive reading of the
manual and mail archives tell me to do it like this:
mysql update listings set map= concat (ML, '.png') where ML'';
mysql update listings set map= concat(ML, '.png') where ML'';
Lose the space between 'concat' and '('
but all I get is this:
ERROR 1064 (42000): 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 '(ML, '.png') where ML''' at line 1
What am I doing wrong?
Tom
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL support for AMD64
I just received a box with the following specs: Dual AMD64 8G ram Two 3ware 2.4 terabyte RAID 5 arrays. My company has been using Redhat for most of its production machines. 1. Does anyone have any success/horror stories running MySQL 4.0.x on RHES 3/ AMD64? 2. Does anyone have alternate recommendations for running MySQL databases in the terabyte range on AMD64? Thanks Lynn Bender UnsubCentral Secure Email List Suppression Management Neutral. Bonded. Trusted. You are receiving this commercial email from a representative of UnsubCentral, Inc. 13171 Pond Springs Road, Austin, TX 78729 Toll Free: 800.589.0445 To cease all communication with UnsubCentral, visit http://www.unsubcentral.com/unsubscribe or send an email to [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select member when it meets two requirements
I'm very much a noob when it comes to MySQL .. Historically I've only used it for storage. I need help. I have a table: ++---+--+-+-+--- -+ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-+--- -+ | memberspecialty_id | int(10) unsigned | | PRI | NULL| auto_increment | | member_id | smallint(10) unsigned | | MUL | 0 | | | specialty_id | tinyint(3) unsigned | | MUL | 0 | | ++---+--+-+-+--- -+ How would I select all distinct member_id that have a specialty_id of 6 and 33? Thanks! ~ Mike - Mike Zornek Web Designer, Media Developer, Programmer and Geek Personal site: http://MikeZornek.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question after installing 4.1.7
I had installed MySql on Win2003, and when I check the 'server information' page, it shows a IP of 127.0.0.1. The IP of the server has a 192.x.x.x address. I'm asuming that this might have a problem why I can't telnet to it to make sure the DB is working?? How can I change this, or do I need to? Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select member when it meets two requirements
Hi Mike, you should be able to do: SELECT DISTINCT t.member_id FROM table t INNER JOIN table t2 ON t2.member_id = t.member_id AND t2.speciality_id = 2 WHERE t.speciality_id = 6 /Johan Mike Zornek wrote: I'm very much a noob when it comes to MySQL .. Historically I've only used it for storage. I need help. I have a table: ++---+--+-+-+--- -+ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-+--- -+ | memberspecialty_id | int(10) unsigned | | PRI | NULL| auto_increment | | member_id | smallint(10) unsigned | | MUL | 0 | | | specialty_id | tinyint(3) unsigned | | MUL | 0 | | ++---+--+-+-+--- -+ How would I select all distinct member_id that have a specialty_id of 6 and 33? Thanks! ~ Mike - Mike Zornek Web Designer, Media Developer, Programmer and Geek Personal site: http://MikeZornek.com -- Johan Höök, Pythagoras Engineering Group - MailTo:[EMAIL PROTECTED] - http://www.pythagoras.se Pythagoras AB, Stormbyv. 2-4, SE-163 55 SPÅNGA, Sweden Phone: +46 8 760 00 10 Fax: +46 8 761 22 77 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySql Hangs
Hi We have been having problems with a MySql database that runs on Linux.It just occurred suddenly and was working fine before Details: MySql Version:# mysql Ver 14.7 Distrib 4.1.6-gamma The filesystems which are accessed by MySql just hang.We cannot access any of the tables.All clients just hang.When we Shutdown the server, it hangs again.The Mysql deamons wont get killed even if I use the kill -9 command.After a reboot Everything works fine for sometime and then goes bad again.The problem seems to be that mysql seems to have a lock on all the tables and hence the use database,select,update hangs.All of the Mysql configuration is with default options. Has anyone encountered a similar problem with the 4.1.6-gamma version?? Thanks Ajay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql Hangs
Ajay, Could you send the error messages (.err file in the mysql dir) and your my.cnf file? Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql Hangs
Are you data files on a local filesystem? When this issue occurs can you access any regular files on the drive? Ajay Kalambur wrote: Hi We have been having problems with a MySql database that runs on Linux.It just occurred suddenly and was working fine before Details: MySql Version:# mysql Ver 14.7 Distrib 4.1.6-gamma The filesystems which are accessed by MySql just hang.We cannot access any of the tables.All clients just hang.When we Shutdown the server, it hangs again.The Mysql deamons wont get killed even if I use the kill -9 command.After a reboot Everything works fine for sometime and then goes bad again.The problem seems to be that mysql seems to have a lock on all the tables and hence the use database,select,update hangs.All of the Mysql configuration is with default options. Has anyone encountered a similar problem with the 4.1.6-gamma version?? Thanks Ajay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql Hangs
Ajay Kalambur wrote: Hi We have been having problems with a MySql database that runs on Linux.It just occurred suddenly and was working fine before Details: MySql Version:# mysql Ver 14.7 Distrib 4.1.6-gamma The filesystems which are accessed by MySql just hang.We cannot access any of the tables.All clients just hang.When we Shutdown the server, it hangs again.The Mysql deamons wont get killed even if I use the kill -9 command.After a reboot Everything works fine for sometime and then goes bad again.The problem seems to be that mysql seems to have a lock on all the tables and hence the use database,select,update hangs.All of the Mysql configuration is with default options. Has anyone encountered a similar problem with the 4.1.6-gamma version?? Thanks Ajay Next time it hangs, check your disk space, especially your tmp directory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question after installing 4.1.7
On Tuesday 30 November 2004 01:36 pm, Steve Grosz wrote: I had installed MySql on Win2003, and when I check the 'server information' page, it shows a IP of 127.0.0.1. The IP of the server has a 192.x.x.x address. Change the line below in my.cnf to what you need.. Its binding to 127 ip. # keep secure by default! bind-address= 127.0.0.1 port= 3306 pgpF6dwmc1dBY.pgp Description: PGP signature
Re: upgrade from mysql 3.23 to 4.1
On Tue, 2004-11-30 at 11:50 -0600, Jeff Smelser wrote: On Monday 29 November 2004 11:35 pm, Greg Macek wrote: I was thinking about this as well, but was hoping to minimize the amount of work I would have to do. However, if this makes the most sense for upgrading, perhaps this is what I'll do. Actually, I think you will be minimizing going the 4.0 route. These major upgrades are no small tasks anyway. Jeff OK, sounds like what I should do is the following: * Upgrade current mysql install (3.23.49) to latest stable 4.0 series (4.0.22 according to the website) * Test out all applications and make sure everything is working as expected. * Test new features in 4.0.x vs. 3.23.x * After sufficient time to test, upgrade to latest stable 4.1.x version. I think I can make this scenario work. :) Thanks all for the direction. Otherwise, I may have run into issues doing the huge jump. Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select member when it meets two requirements
Am I missing something? Will this not do the trick: SELECT DISTINCT member_id FROM table WHERE specialty_id IN(6,33); Graham -Original Message- From: Johan Höök [mailto:[EMAIL PROTECTED] Sent: 30 November 2004 19:56 To: Mike Zornek Cc: [EMAIL PROTECTED] Subject: Re: Select member when it meets two requirements Hi Mike, you should be able to do: SELECT DISTINCT t.member_id FROM table t INNER JOIN table t2 ON t2.member_id = t.member_id AND t2.speciality_id = 2 WHERE t.speciality_id = 6 /Johan Mike Zornek wrote: I'm very much a noob when it comes to MySQL .. Historically I've only used it for storage. I need help. I have a table: ++---+--+-+--- --+--- -+ | Field | Type | Null | Key | Default | Extra | ++---+--+-+--- --+--- -+ | memberspecialty_id | int(10) unsigned | | PRI | NULL| auto_increment | | member_id | smallint(10) unsigned | | MUL | 0 | | | specialty_id | tinyint(3) unsigned | | MUL | 0 | | ++---+--+-+--- --+--- -+ How would I select all distinct member_id that have a specialty_id of 6 and 33? Thanks! ~ Mike - Mike Zornek Web Designer, Media Developer, Programmer and Geek Personal site: http://MikeZornek.com -- Johan Höök, Pythagoras Engineering Group - MailTo:[EMAIL PROTECTED] - http://www.pythagoras.se Pythagoras AB, Stormbyv. 2-4, SE-163 55 SPÅNGA, Sweden Phone: +46 8 760 00 10 Fax: +46 8 761 22 77 -- 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 member when it meets two requirements
Graham Cossey wrote: Am I missing something? Will this not do the trick: SELECT DISTINCT member_id FROM table WHERE specialty_id IN(6,33); That would return any member_id with specialty_id=6 and any member_id with specialty_id=33, i.e. member_ids with specialty_id 6 OR 33. I think he wanted member_ids with specialty_id 6 AND 33. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select member when it meets two requirements
No, it won't. That will return every member_id that has either specialty_id=6, or specialty_id=33, or both. Mike only wants both. There are 2 solutions. One is the self-join proposed by Johan, although you usually wouldn't put one of the requirements into the join condition. That is, I would have expected: SELECT t1.member_id FROM your_table t1 JOIN your_table t2 ON t1.member_id = t2.member_id WHERE t1.specialty_id=6 AND t2.specialty_id=33; which should be equivalent. This query doesn't generalize so well, however, as you have to add a join for each specialty_id in your required list, but it can be done: SELECT t1.member_id FROM your_table t1 JOIN your_table t2 ON t1.member_id = t2.member_id JOIN your_table t3 ON t1.member_id = t3.member_id WHERE t1.specialty_id=6 AND t2.specialty_id=33 AND t3.specialty_id=49; The other option is this: SELECT member_id FROM your_table WHERE specialty_id IN (6, 33) GROUP BY member_id HAVING COUNT(*) = 2; This generalizes easily. Simply change the HAVING clause to COUNT(*) = N, where N is the number of required specialty_ids in the IN list. For example: SELECT member_id FROM your_table WHERE specialty_id IN (4, 13, 16, 42) GROUP BY member_id HAVING COUNT(*) = 5; DISTINCT will not help either way, unless you have duplicate rows. In that case DISTINCT fixes the self join method, but then the solution is to remove the duplicates, rather than using DISTINCT. Michael Graham Cossey wrote: Am I missing something? Will this not do the trick: SELECT DISTINCT member_id FROM table WHERE specialty_id IN(6,33); Graham -Original Message- From: Johan Höök [mailto:[EMAIL PROTECTED] Sent: 30 November 2004 19:56 To: Mike Zornek Cc: [EMAIL PROTECTED] Subject: Re: Select member when it meets two requirements Hi Mike, you should be able to do: SELECT DISTINCT t.member_id FROM table t INNER JOIN table t2 ON t2.member_id = t.member_id AND t2.speciality_id = 2 WHERE t.speciality_id = 6 /Johan Mike Zornek wrote: I'm very much a noob when it comes to MySQL .. Historically I've only used it for storage. I need help. I have a table: ++---+--+-+--- --+--- -+ | Field | Type | Null | Key | Default | Extra | ++---+--+-+--- --+--- -+ | memberspecialty_id | int(10) unsigned | | PRI | NULL| auto_increment | | member_id | smallint(10) unsigned | | MUL | 0 | | | specialty_id | tinyint(3) unsigned | | MUL | 0 | | ++---+--+-+--- --+--- -+ How would I select all distinct member_id that have a specialty_id of 6 and 33? Thanks! ~ Mike - Mike Zornek Web Designer, Media Developer, Programmer and Geek Personal site: http://MikeZornek.com -- Johan Höök, Pythagoras Engineering Group - MailTo:[EMAIL PROTECTED] - http://www.pythagoras.se Pythagoras AB, Stormbyv. 2-4, SE-163 55 SPÅNGA, Sweden Phone: +46 8 760 00 10 Fax: +46 8 761 22 77 -- 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: update and concat
At this point, what you say you are doing should work, but doesn't. We
cannot guess what's wrong. Please enter your command,
UPDATE listings SET map = CONCAT(ML, '.png') WHERE ML '';
get your error message, and then copy/paste the whole thing into your next
message. That way, someone should spot the problem.
Michael
Thomas McDonough wrote:
That did not do it. I'm still getting the same error message (?)
Tom
On Nov 30, 2004, at 2:09 PM, gerald_clark wrote:
Thomas McDonough wrote:
I'm trying to set all the values of column 'map' to the value of
column ML and '.png'. My intuition and an extensive reading of the
manual and mail archives tell me to do it like this:
mysql update listings set map= concat (ML, '.png') where ML'';
mysql update listings set map= concat(ML, '.png') where ML'';
Lose the space between 'concat' and '('
but all I get is this:
ERROR 1064 (42000): 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 '(ML, '.png') where ML''' at line 1
What am I doing wrong?
Tom
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help with SQL (join?) query
I am trying to join to tables: users: uid name 1 john 2 jim 3 mary groups: groupid groupname groupowner groupcreator 1 test1 1 1 2test2 1 2 3 test32 3 My desired output would look like: groupid groupname owner creator 1 test1john john 2 test2john jim 3 test3jim mary I was unable to find an example online.. Can anyone help? Thanks, Kris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL support for AMD64
Hi Lynn, (B (BIf you look in the archives you will find some strong recommendations (Bagianst RH and for Suse (performance reasons). (Look for "AMD 64 bit" or (B"perfomance") (B (BI am personally using Debian (sid) which you may not want to do for your (Bproduction ;-). Suse 9.2 unfortunately seems to be quite buggy (mostly (Bobvious things) That starts with the installation itself. (B (Bhttp://www.be-known-online.com/ (Bhttp://www.be-known-online.com/modules/newbb/viewforum.php?forum=46 (B (BShows some of my personal notes. Dont expect anything spectacular. Anyway (BI hope that you may find the info useful. (B (BBTW I am not running anything near Terabyte operations. (B (B (BBest regards (B (BNils Valentin (BTokyo/ Japan (B (B (B (B (B (B (B I just received a box with the following specs: (B (B Dual AMD64 (B 8G ram (B Two 3ware 2.4 terabyte RAID 5 arrays. (B (B My company has been using Redhat for most of its production machines. (B (B 1. Does anyone have any success/horror stories running MySQL 4.0.x (B on RHES 3/ AMD64? (B (B 2. Does anyone have alternate recommendations for running MySQL (B databases in the terabyte range on AMD64? (B (B Thanks (B Lynn Bender (B (B (B (B (B UnsubCentral (B Secure Email List Suppression Management (B Neutral. Bonded. Trusted. (B (B You are receiving this commercial email (B from a representative of UnsubCentral, Inc. (B 13171 Pond Springs Road, Austin, TX 78729 (B Toll Free: 800.589.0445 (B (B To cease all communication with UnsubCentral, visit (B http://www.unsubcentral.com/unsubscribe (B or send an email to [EMAIL PROTECTED] (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]
Re: help with SQL (join?) query
Kris zoob-at-doomstar.com |Lists| wrote: I am trying to join to tables: users: uid name 1 john 2 jim 3 mary groups: groupid groupname groupowner groupcreator 1 test1 1 1 2test2 1 2 3 test32 3 My desired output would look like: groupid groupname owner creator 1 test1john john 2 test2john jim 3 test3jim mary I was unable to find an example online.. Can anyone help? try something like : select groupid, groupname, owner.name as f_owner, creator.name as f_creator from groups, users as owner, users as creator where groups.groupowner = owner.uid and groups.groupcreator = creator.uid; (untested - but principle should be sound...) Peter -- Peter Valdemar Mørch http://www.morch.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie: making a proper subquery
I am trying to make a proper subqueryjust not sure bout the syntax
I use this this basic query to connect all of my tables:
# here is the basic query that connects all the tables..this works :)
$sql = 'SELECT playlist.name as playlist_name, artist.name as artist,'
. ' artist.'.$language.' as bio,artist.purchaseLink,'
. ' media.'.$language.' as trackName,
media.path,media.quality,mediaType.id as mediaType'
. ' FROM playlist, artist, media, playlistItems, mediaType'
. ' WHERE playlistItems.playlist_id = playlist.id'
. ' AND playlistItems.media_id = media.id'
. ' AND media.artist_id = artist.id'
. ' And media.mediaType_id = mediaType.id';
After I connect all my tables, I tried:
# if there is a query string, add it to the above $sql string
if(strcmp($query, )!= 0){
$sql = $sql. ' AND media.'.$language.' Like %'.$query.'%'
. ' OR artist.'.$language.' Like %'.$query.'%'
. ' OR artist.name Like %'.$query.'%'
. ' ORDER BY playlist.id ASC,playlistItems.order ASC LIMIT 0, 60';
well, for probably obvious reasons, this does not give the right result
Basically I am searching in every relevant field for the query string.
what would would be the correct syntax for this kind of thing ?
many thanks
g
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with SQL (join?) query
No offense but your response has created more confusion about this.. Here is a more simple diagram for what I'd like to get from an SQL query: Table users: uid username 1 john 2 jim 3 mary Table groups: id name creator owner 1 test 11 2 abc 12 3 test2 23 output from mysql: id(from groups) name(from groups) creatorname ownername 1 test john john 2 abc john jim 3 test2 jim mary I just want the output from the groups table but a name instead of the number where creator# and owner# in groups table is associated to the uid in users. Hope this helps understand my problem.. Thanks Kris Peter Valdemar Mørch wrote: Kris zoob-at-doomstar.com |Lists| wrote: I am trying to join to tables: users: uid name 1 john 2 jim 3 mary groups: groupid groupname groupowner groupcreator 1 test1 1 1 2test2 1 2 3 test32 3 My desired output would look like: groupid groupname owner creator 1 test1john john 2 test2john jim 3 test3jim mary I was unable to find an example online.. Can anyone help? try something like : select groupid, groupname, owner.name as f_owner, creator.name as f_creator from groups, users as owner, users as creator where groups.groupowner = owner.uid and groups.groupcreator = creator.uid; (untested - but principle should be sound...) Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A Select improvement
Hi, anyone has suggestions what changes to make to allow this query to run faster? SELECT domain FROM tbl_1 WHERE id 0 and id 2 domain = 12.221.190.111 AND score IS NOT Null AND data LIKE %param=search GROUP BY domain, data -- every one of those WHERE clauses makes the query very slow. for about 50 million records with 200-900 thousand matching records it takes about two minutes if I only have the straight domain = some string, then almost quadriples if I add the data Like pattern clause. It is very slow considering that the WHERE has to be ran many many times with different parameters here is table info : # Table: 'tbl_1' # CREATE TABLE `tbl_1` ( `id` int(10) unsigned NOT NULL auto_increment, `domain` varchar(50) NOT NULL default '', `data` varchar(200) default '', `score` int(11) default NULL, PRIMARY KEY (`id`), KEY `score` (`score`), KEY `domain` (`domain`), FULLTEXT KEY `data` (`data`) ) TYPE=MyISAM; heres query info (it remains the same if i remove some WHERE clauses except for rows count going up when i do: table| type | possible_keys | key| key_len | ref| rows | Extra | tbl_1|ref | score,domain | domain | 50 | const |1 | Using where; Using temporary; Using filesort | --- thank you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with SQL (join?) query
How so? Is there something you didn't understand? Peter's solution is the right idea. You need to join the groups table to the users table once to get the creatorname and again to get the ownername. Maybe it will be clearer if we rewrite the query to make the join conditions explicit: SELECT g.id, g.name, o.name AS 'owner', c.name AS 'creator' FROM groups g JOIN users o ON groups.owner = o.uid JOIN users c ON groups.creator = c.uid; Does that help? Kris wrote: No offense but your response has created more confusion about this.. Here is a more simple diagram for what I'd like to get from an SQL query: Table users: uid username 1 john 2 jim 3 mary Table groups: id name creator owner 1 test 11 2 abc 12 3 test2 23 output from mysql: id(from groups) name(from groups) creatorname ownername 1 test john john 2 abc john jim 3 test2 jim mary I just want the output from the groups table but a name instead of the number where creator# and owner# in groups table is associated to the uid in users. Hope this helps understand my problem.. Thanks Kris Peter Valdemar Mørch wrote: Kris zoob-at-doomstar.com |Lists| wrote: I am trying to join to tables: users: uid name 1 john 2 jim 3 mary groups: groupid groupname groupowner groupcreator 1 test1 1 1 2test2 1 2 3 test32 3 My desired output would look like: groupid groupname owner creator 1 test1john john 2 test2john jim 3 test3jim mary I was unable to find an example online.. Can anyone help? try something like : select groupid, groupname, owner.name as f_owner, creator.name as f_creator from groups, users as owner, users as creator where groups.groupowner = owner.uid and groups.groupcreator = creator.uid; (untested - but principle should be sound...) Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with SQL (join?) query
I think he gave you the right answer. you can also use joins instead of where, but it is the same idea, not sure which one of the two will be faster though: select groupid, groupname, owner.name as f_owner, creator.name as f_creator from groups left join users as owner on owner.uid = groups.groupowner left join users as creator on groups.groupcreator = creator.uid - Original Message - From: Kris [EMAIL PROTECTED] To: Peter Valdemar Mørch [EMAIL PROTECTED] Cc: Kris zoob-at-doomstar.com |Lists| [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, November 30, 2004 7:08 PM Subject: Re: help with SQL (join?) query No offense but your response has created more confusion about this.. Here is a more simple diagram for what I'd like to get from an SQL query: Table users: uid username 1 john 2 jim 3 mary Table groups: id name creator owner 1 test 11 2 abc 12 3 test2 23 output from mysql: id(from groups) name(from groups) creatorname ownername 1 test john john 2 abc john jim 3 test2 jim mary I just want the output from the groups table but a name instead of the number where creator# and owner# in groups table is associated to the uid in users. Hope this helps understand my problem.. Thanks Kris Peter Valdemar Mørch wrote: Kris zoob-at-doomstar.com |Lists| wrote: I am trying to join to tables: users: uid name 1 john 2 jim 3 mary groups: groupid groupname groupowner groupcreator 1 test1 1 1 2test2 1 2 3 test32 3 My desired output would look like: groupid groupname owner creator 1 test1john john 2 test2john jim 3 test3jim mary I was unable to find an example online.. Can anyone help? try something like : select groupid, groupname, owner.name as f_owner, creator.name as f_creator from groups, users as owner, users as creator where groups.groupowner = owner.uid and groups.groupcreator = creator.uid; (untested - but principle should be sound...) Peter -- 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]
Adding Foreign Key
mysql alter table t_quiz_trivia add foreign key (client_id) references t_client (client_id) on delete set default; ERROR 1005: Can't create table './smsserver/#sql-215d_11eff.frm' (errno: 150) May i know what makes error ? Here is the reference table CREATE TABLE `t_client` ( `client_id` int(11) NOT NULL default '0', `client_desc` varchar(255) NOT NULL default '', PRIMARY KEY (`client_id`) ) TYPE=InnoDB; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Securing mysql from remote access
I can do a telnet host.com 3386 and get a answer back from mysql. Is the only way to turn off remote access like this with a router and limit the ports, or is there some setting in mysql that will not allow outside connections other than from a IP range I tell it to? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED] 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: Securing mysql from remote access
If you plan on connecting only from the localhost host or via an ssh tunnel you block port 3306 at the firewall or router. What exactly are you looking to accomplish? Scott Haneda wrote: I can do a telnet host.com 3386 and get a answer back from mysql. Is the only way to turn off remote access like this with a router and limit the ports, or is there some setting in mysql that will not allow outside connections other than from a IP range I tell it to? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Securing mysql from remote access
on 11/30/04 8:53 PM, Victor Pendleton at [EMAIL PROTECTED] wrote: If you plan on connecting only from the localhost host or via an ssh tunnel you block port 3306 at the firewall or router. What exactly are you looking to accomplish? Well, some friend of a friend decided to scan me and found he could get myslq to report the version I am runing. He could not login or anything like that. He then pointed me to http://www.securityfocus.com/bid/11261/discussion/ http://www.securityfocus.com/bid/8590/discussion/ http://www.securityfocus.com/bid/10655/discussion/ http://www.securityfocus.com/bid/10654/discussion/ http://www.securityfocus.com/bid/975/discussion/ For which I just started wondering. I can easily block it out in the router, but it made me curious as to what other options there are. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED] 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: Securing mysql from remote access
Some of the vulnerabilities have been addressed in recent releases. I would check the bug list and change log to see if your versions have been patched. If you want to disallow all tcp/ip connections you can use the |--skip-networking parameter. Only Unix sockets or Windows named pipes connections will be allowed. |Scott Haneda wrote: on 11/30/04 8:53 PM, Victor Pendleton at [EMAIL PROTECTED] wrote: If you plan on connecting only from the localhost host or via an ssh tunnel you block port 3306 at the firewall or router. What exactly are you looking to accomplish? Well, some friend of a friend decided to scan me and found he could get myslq to report the version I am runing. He could not login or anything like that. He then pointed me to http://www.securityfocus.com/bid/11261/discussion/ http://www.securityfocus.com/bid/8590/discussion/ http://www.securityfocus.com/bid/10655/discussion/ http://www.securityfocus.com/bid/10654/discussion/ http://www.securityfocus.com/bid/975/discussion/ For which I just started wondering. I can easily block it out in the router, but it made me curious as to what other options there are. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding Foreign Key
Do you have an index on the client_id in the t_quiz table? Can you post the ddl for t_quiz? Ady Wicaksono wrote: mysql alter table t_quiz_trivia add foreign key (client_id) references t_client (client_id) on delete set default; ERROR 1005: Can't create table './smsserver/#sql-215d_11eff.frm' (errno: 150) May i know what makes error ? Here is the reference table CREATE TABLE `t_client` ( `client_id` int(11) NOT NULL default '0', `client_desc` varchar(255) NOT NULL default '', PRIMARY KEY (`client_id`) ) TYPE=InnoDB; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help me optimize this query
I am trying to execute this query and it is failing with Table is full error (I know I can make temp tables big). update t1, t2 set t1.XXX=1 where t1.YYY=t2. and t2. like '%X%'; My t1 has 10,00,000+ records and t2 has about 70,000 recorsds. I would like to know how can I optimize this query? What are the parmeters for this optimization? Can someone give me links where I can read up about such optimizations for update query. TIA, - Manish -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding Foreign Key
Below DDL for t_quiz_trivia CREATE TABLE `t_quiz_trivia` ( `quiz_id` int(11) NOT NULL auto_increment, `quiz_name` text NOT NULL, `quiz_keycode` varchar(255) NOT NULL default '', `quiz_pil_jwb` varchar(255) NOT NULL default '', `quiz_confirmation_msg` varchar(255) NOT NULL default '', `quiz_error_message` varchar(255) NOT NULL default '', `quiz_help_message` varchar(255) NOT NULL default '', `quiz_tarif_telkomsel` mediumint(9) NOT NULL default '2000', `quiz_tarif_satelindo` mediumint(9) NOT NULL default '2000', `quiz_tarif_im3` mediumint(9) NOT NULL default '2000', `quiz_tarif_proxl` mediumint(9) NOT NULL default '2000', `quiz_tarif_mobile8` mediumint(9) NOT NULL default '2000', `quiz_tarif_flexy` mediumint(9) NOT NULL default '2000', `quiz_tarif_lippo_telecom` mediumint(9) NOT NULL default '2000', `quiz_point_keycode` varchar(45) NOT NULL default 'point,poin', `quiz_point_answer` varchar(160) NOT NULL default 'Poin anda saat ini adalah: ___POIN___', `quiz_help_keycode` varchar(45) NOT NULL default 'help', `quiz_start_keycode` varchar(255) NOT NULL default 'start,ok', `quiz_tarif_esia` mediumint(9) NOT NULL default '2000', `quiz_tarif_starone` mediumint(9) NOT NULL default '2000', `client_id` int(11) NOT NULL default '0', PRIMARY KEY (`quiz_id`), KEY `t_idx01` (`client_id`) ) TYPE=InnoDB Victor Pendleton wrote: Do you have an index on the client_id in the t_quiz table? Can you post the ddl for t_quiz? Ady Wicaksono wrote: mysql alter table t_quiz_trivia add foreign key (client_id) references t_client (client_id) on delete set default; ERROR 1005: Can't create table './smsserver/#sql-215d_11eff.frm' (errno: 150) May i know what makes error ? Here is the reference table CREATE TABLE `t_client` ( `client_id` int(11) NOT NULL default '0', `client_desc` varchar(255) NOT NULL default '', PRIMARY KEY (`client_id`) ) TYPE=InnoDB; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance impact -- multiple databases Vs multiple tables...
Thanks a lot for the quick response :) We are not using MyISAM tables. All our tables are InnoDB tables. The rational behind this decision is that the database is expected to get hundreds of insert queries per second, so we want the row level locking of InnoDB to speed this up. (I should have mentioned this in the first mail. Sorry for that). Having one large InnoDB table is not a good option because it'll throttle the performance of selects and inserts and will be a hindrance when we want to replicate data. The option of mysql-replication has been looked in to.We are not relying on the mysql replication for load balancing or data backup.These things are done by the application layers. I had looked in to the mysql cluster. It does not cover all the use cases that we have in mind and it is not flexible enough to give us more control over how data is stored and restored in case of node failures. So it again boils down to same two questions: 1) What is a better option: (Having hundreds of databases or having a single database with thousands of tables). 2) Will it give any performance improvement if we run multiple mysql server instances (By partitioing the data set and having one mysql server handle one data set). This option is only applicable if we have multiple databases. Thanks in advance, -Alok. On Tue, 2004-11-30 at 22:38, Brent Baisley wrote: If you are hitting file size limits, you probably want to look into using the InnoDB table type. That will allow you to work around file size limits and have a database of just about any size you need. You won't end up having a 30GB file, but multiple smaller files which will be transparent to your application. InnoDB tables are probably your best place to look for a solution. If you must use MyISAM table types, perhaps look into the RAID option for MyISAM tables. Although that will still have it's limits, mainly because of index size limits. If you are worried about load, look into replication. One machine will act as the master where all updates will occur, but then any of your replication machines can handle queries. You could setup a round robin DNS to automatically split the load or go with something even more sophisticated. You also might see what you can find on MySQL clusters, although this is a fairly new feature (this year) and I myself have not read up on it. __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
writing subquries in 4.0.18 version
hello, i m working on mysql version 4.0.18 .the documentation given for this version will show subquries syntax which whill not work practically. i urgently want help in this regard. can anybody mail me how can we write the subquries in mysql version 4.0.18 ? Eg. select * from table as t1 where id IN (select id from table as T2 where one = 4 ); this syntax will not work.how to go ahead or which version documentation i can refer? N. Kavithashree === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reg SubQuery
Hi, I need to get all the details of an employee whose salary is the lowest. I can do like this in Oracle select * from emp where id = (select min(id) from emp). Can we have any alternative in MySQL for the above query, as sub queries are not supported in MySQL 4.0.21 Regards, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
Re: Question after installing 4.1.7
That seems to work fine, but now the problem is that am no longer able to log in to the Admin utility using root and the server name of db1. I can get in using a alternate account I created before I made the change just fine. Jeff Smelser wrote: On Tuesday 30 November 2004 01:36 pm, Steve Grosz wrote: I had installed MySql on Win2003, and when I check the 'server information' page, it shows a IP of 127.0.0.1. The IP of the server has a 192.x.x.x address. Change the line below in my.cnf to what you need.. Its binding to 127 ip. # keep secure by default! bind-address= 127.0.0.1 port= 3306 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
