get rows that have a filed in common with another row
Hello,
I'm new to mysql (and databases, fo that matter) and have problems
finding a solution. The situation is as follows:
I have one table with filtered information about users, the columns
are, say, id, field and value. As you can see, any id can have more
rows assiciated with him, each containing one crucial piece of
information. Now I want to search the values for something and return
all rows with the same id -- in effect this means that I cannot use
select * from t where value like ... , which does, of course, return
all rows that match the expression rather than all rows with the same
id that happened to also contain a value with the expression.
If I'm not clear enough, here's an example:
id field value
--|-|---
a x string1
a y string2
b x string3
a z string4
select * from t where value like string2 returns the second row, but
I want to return all rows with the same id as in row number two.
I've tried this subquery, to no avail: select * from t where id =
(select id from t where value like '%string2%' limit 1)
Thanks,
andrej
--
echo ${girl_name} /etc/dumpdateshttp://sonet.homelinux.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hardware and Architectural Configuration
HI, We are working on a software tool for keyword analysis. Our proposed software will have Mysql as the backend database server. The proposed database will contain atleast 80 million rows and there will a lot of concurrent search (string search) and update operation by the software on the database. Can anybody help us by recomending a suitable hardware and architectural configuration for the the database. With Regards, Suryya
Number of connections to a database
Hello, is there any way to tell the number of simultaneous connections to a mysql database at a given time, using SQL or a scripting language such as php, perl, etc. ? Many thanks. Philippe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of connections to a database
Philippe de Rochambeau wrote: Hello, is there any way to tell the number of simultaneous connections to a mysql database at a given time, using SQL or a scripting language such as php, perl, etc. ? Many thanks. Philippe show status is your friend: show status like Connections HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: get rows that have a filed in common with another row
andrej h wrote: Hello, I'm new to mysql (and databases, fo that matter) and have problems finding a solution. The situation is as follows: I have one table with filtered information about users, the columns are, say, id, field and value. As you can see, any id can have more rows assiciated with him, each containing one crucial piece of information. Now I want to search the values for something and return all rows with the same id -- in effect this means that I cannot use select * from t where value like ... , which does, of course, return all rows that match the expression rather than all rows with the same id that happened to also contain a value with the expression. If I'm not clear enough, here's an example: id field value --|-|--- a x string1 a y string2 b x string3 a z string4 select * from t where value like string2 returns the second row, but I want to return all rows with the same id as in row number two. I've tried this subquery, to no avail: select * from t where id = (select id from t where value like '%string2%' limit 1) You would make it easier for us to help you if you would tell us what happened. Did you get an error message? If so, paste it in. Did you get unexpected results? If so, show us what you expected and what you got. Doesn't work, or its equivalent, tried ... to no avail, doesn't provide much for us to go on. Thanks, andrej Subqueries require mysql 4.1. Perhaps you have an older version. Try this: SELECT t2.* FROM t t1 JOIN t t2 ON t1.id = t2.id WHERE t1.value = 'string2'; +--+---+-+ | id | field | value | +--+---+-+ | a| x | string1 | | a| y | string2 | | a| z | string4 | +--+---+-+ 3 rows in set (0.00 sec) Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reversing DESC|ASC
You seem to be confused in your posting. Your first list is in ASC order and appears correct. Your second list seems to be in DESC order and also seems to be correct. Why is it your results need to have '2004-11-20' pretend to be AFTER '2004-11-24' so that it appears first in a descending order list? If I understood what you are trying to accomplish better, I could help you make your query appear as you like. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ashley M. Kirchner [EMAIL PROTECTED] wrote on 11/26/2004 03:10:41 AM: I'm currently running a query on a db that looks as follows: SELECT field1,field2,field3 FROM table ORDER BY field3 DESC LIMIT 5; This produces: ++-++ | field1 | field2 | field3 | ++-++ | 1 | Title 1 | 2004-09-08 | | 2 | Title 2 | 2004-10-23 | | 3 | Title 3 | 2004-11-11 | | 4 | Title 4 | 2004-11-20 | | 5 | Title 5 | 2004-11-24 | ++-++ My problem is, I need the last two in that list, in the order they're listed there. If I reverse the order (by using ASC), I will get: ++-++ | field1 | field2 | field3 | ++-++ | 5 | Title 5 | 2004-11-24 | | 4 | Title 4 | 2004-11-20 | | 3 | Title 3 | 2004-11-11 | | 2 | Title 2 | 2004-10-23 | | 1 | Title 1 | 2004-09-08 | ++-++ ...which puts the two that I need at the top, but not in the order I need them (I need 20th listed before the 24th). So, how do I reverse DESC sorting, to get the records in the order that I need? -- H | I haven't lost my mind; it's backed up on tape somewhere. + Ashley M. Kirchner mailto:[EMAIL PROTECTED] . 303.442.6410 x130 IT Director / SysAdmin / WebSmith . 800.441.3873 x130 Photo Craft Imaging . 3550 Arapahoe Ave. #6 http://www.pcraft.com . . .. Boulder, CO 80303, U.S.A. -- 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 Wed, 2004-11-24 at 09:15 -0600, Jeff Smelser wrote: On Wednesday 24 November 2004 07:32 am, Hristo Chernev wrote: How to upgrade from 3.23.58 to 4.1.7? Which is the easiest way with minimal risk and downtime? Yeah right.. ;) Background: Heavy loaded mysql server, only one database but it is huge - 5GB.The database is replicated to another server.Mysql 3.23.58.Linux OS. Here is the my plan, please correct me if there is a better way or if I am doing unnecessary actions: 1. Stop the site and mysql servers and backup database. 2. Upgrade mysql versions on the two machines. 3. Compiling apache and php with new mysql 4.1 client lib. 4. Recreate users and rights ( the provided script fix_privilege_table will not work correctly form 3.23. to 4.1. will it?). 5. Start main mysql server without replication. 6. Rename database to olddb. 7. Create new database named db. Do one of 8a or 8b: 8a.Dump olddb with mysqldump, then run the result file to fill the new db (in order to have native 4.1 database ). 8b.Using SQL queries (or phpmyadmin) copy all tables from olddb to new db (in order to have native 4.1 database ). 9. Assure that all is correct then delete olddb, copy database to the slave and start mysql servers. 10. Confirm replication is ok, then start the site. This is a good list.. My suggestion would be to do this on some sort of back/dev machine.. php doesnt just work with 4.1 automatically (depending on version). Not sure which distro u use, that could be a driving factor of problems you may see to. Jeff I am actually in the process of planning a similar upgrade for our database server. None of our databases are that big, but was wondering about what gotcha's I should be on the lookout for. I planned on walking through the upgrade pages from mysql.com for going from 3.23 to 4.0 and 4.0 to 4.1. I was hoping not to have to re-create the databases as the original poster mentioned, but I don't have a slave DB setup either. Will this be an issue? Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of connections to a database
Date: Mon, 29 Nov 2004 14:03:32 +0100 To: [EMAIL PROTECTED] From: Philippe de Rochambeau [EMAIL PROTECTED] Subject: Number of connections to a database Message-Id: [EMAIL PROTECTED] Hello, is there any way to tell the number of simultaneous connections to a mysql database at a given time, using SQL or a scripting language such as php, perl, etc. ? Many thanks. Philippe Perhaps this will help. mysql SHOW STATUS LIKE 'threads_connected'; +---+---+ | Variable_name | Value | +---+---+ | Threads_connected | 5 | +---+---+ 1 row in set (0.00 sec) mysql SHOW STATUS LIKE '%connect%'; +--+---+ | Variable_name| Value | +--+---+ | Aborted_connects | 2 | | Connections | 74| | Max_used_connections | 27| | Threads_connected| 5 | +--+---+ 4 rows in set (0.00 sec) Connections: Total number of connections made since the MySQL server was last restarted. Max_used_conections: Maximum number of simultaneous connections since the MySQL server was last restarted. Threads_connected: Current number of connections. -- Jon Stephens, Technical Writer MySQL AB www.mysql.com Office: +61 (7) 3388 2228 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: latin1/utf-8 problem
Hello. Looks like you've solved the problem? Remove quotes from show create... statements: show create table table_name; Steve Mansfield [EMAIL PROTECTED] wrote: --- On Saturday 27 November 2004 12:38, Steve Mansfield wrote: Seem to be answering my own questions here...:-) Looks like it's a MySQLcc problem. Dumped tables from the live server and then, rather than running them as a sql query via MySQLcc, I did it from the command line with: mysql -h host -D database -p filename.sql And that worked. The data stayed as latin1. For some reason, MySQLcc is messing with the data and turning it into utf-8. Go figure... -- 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: Fatal error: Can't open privilege tables: File '/usr/local/share/mysql/charsets/?.conf' not found (Errcode: 2)
Hello. Usually such error occurs on Windows boxes. If you can apply recomendations for Windows to FreeBSD :), look at: http://dev.mysql.com/doc/mysql/en/Windows_troubleshooting.html Can you upgrade to 4.0.22 or 4.1.7? Ricardo David Martins [EMAIL PROTECTED] wrote: Hi I am trying to run mysql 4.0.21 server on a freeBSD 5.3 box. I tried to run mysqld_safe to add a root password but i can't seem to run the safe command because it shuts down immediately. The following error is presented to me: Fatal error: Can't open privilege tables: File '/usr/local/share/mysql/charsets/?.conf' not found (Errcode: 2) I would like it to load the latin1.conf charset. I can't find any variable that influences this choice. Probably there is something not configured or the configuration isn't supported. Anyway I would like to know how to choose this. I installed the binary freebsd package which is suppose to be very reliable to a quick start. Thanks --=20 Ricardo David Martins ___= __ AVISO Esta mensagem (incluindo quaisquer anexos) pode conter informa=E7=E3o confidencial para uso exclusivo do destinat=E1rio. Se n=E3o for o destinat=E1rio pretendido, n=E3o dever=E1 usar, distribuir ou copiar este e-mail. Se recebeu esta mensagem por engano, por favor informe o emissor e elimine-a imediatamente. Obrigado. DISCLAIMER This e-mail (including any attachments) may contain confidential information for exclusive use of its recipient. If you are not the intended recipient you must not use, distribute or copy this e-mail. If you have received this e-mail in error please notify the sender and delete it immediately. Thank You. ___= __ -- 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: I want cluster, can I use MySQL?
Hello. From http://www.mysql.com/products/cluster/faq.html: Near-linear scalability shown on system where each storage node executed on the following hardware: CPU:2x Intel Xeon Processors at 2.8 GHz Memory: 16GB RAM HDD:4 x 73GB SCSI RAID 1 Controller Gigabit Ethernet I think, the best counsultation you can take from MySQL commercial support, see https://order.mysql.com/?ref=ensita one more thing, about MySQL cluster with a 6-8GB database. Which processor do I need in each storage node for a decent performance? I am thinking Xeon 2.8 or 3.0 GB, do P4 good enought to power such cluster? Assume I have enough main memory Thanks Koon Yue Lam [EMAIL PROTECTED] wrote: Hi list ! I want a high availability DB whcih means I need failover andload balancing. Can I use MySQL cluster to achieve this? becasue MySQL cluster is only in gramma, I wonder if I can use it in a commerical environment. Also if my website is commerical, do I need to have commerical license of MySQL? How about MaxDB? Regards -- 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: Bizarre table type switch
Hello. Usually you should follow instructions in chapters at: http://dev.mysql.com/doc/mysql/en/Debugging_server.html Stuart Felenstein [EMAIL PROTECTED] wrote: I'm not sure what happened but when I ran some test yesterday on a transaction it failed. Being puzzled I started digging around. I have come to find out that all the tables involved were now set to MyISAM. Obviously transactions wouldn't work. The odd thing that I'd like to figure out is how did they get switched. I know this is a vague and gray question , and if there is a way to trace it I'd like to know. Is there anything that would cause this type of occurance ? Anything I can do to find out why it happened? Stuart -- 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]
Upgrading bundled ver of mysql included with php 4.3.1
What steps are needed to upgrade (or remove) the builtin mysql [3.23.49] included with php 4.3.1 source? I have mysql 4.x installed and working by itself, but php continues to use 3.23.49. I have tried recompiling php 4.3.1 without --with-mysql on the configure line. Doing so did not remove the builtin 3.23.49 package. Any tips? Thanks, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: host blocked, but can't see errors
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 - From: Gleb Paharenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, November 25, 2004 12:06 AM Subject: Re: host blocked, but can't see errors Hello. In this context 'errors' means interrupted connections. Increase max_connect_errors. See: http://dev.mysql.com/doc/mysql/en/Server_system_variables.html Anne Ramey [EMAIL PROTECTED] wrote: Last week I got this message: Cannot connect DBI:mysql:formmail;myhost.net: Host 'myhost.net' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts' I couldn't find the errors anywhere. Not in any of the apache logs and not in the mysql error log: 041103 20:09:11 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 041103 20:09:11 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 041103 20:09:12 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 041103 20:09:12 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 041103 20:09:13 InnoDB: Started /usr/local/mysql/bin/mysqld: ready for connections. Version: '4.0.21-max' socket: '/tmp/mysql.sock' port: 3306 Official MySQL-max binary I've also checked my syslog, messages log, and formmail log, nothing. It's only happening to this one host. it keeps happening over and over. I have to flush my host every day or so. Any ideas where to look? -- 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] --- avast! Antivirus: Outbound message clean. Virus Database (VPS): 0448-1, 11/26/2004 Tested on: 11/29/2004 10:44:37 AM avast! - copyright (c) 2000-2004 ALWIL Software. http://www.avast.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading bundled ver of mysql included with php 4.3.1
Stembridge, Michael wrote: I have tried recompiling php 4.3.1 without --with-mysql on the configure line. Doing so did not remove the builtin 3.23.49 package. You need to run configure with the new MySQL directory explicitly specified, e.g. ./configure --with-mysql=/usr/local/mysql_4.1.7 HTH, -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Changing sort order of items.
I'm trying to create the most efficient way to allow a user to change the display order of a group of rows in a table. Lets say the basic table is: id group_id name sort_order The query to display it would be SELECT id, name FROM mytable WHERE group_id = $x ORDER BY sort_order Now when I display it they currenlty all have the same sort_order value so they come in the order however the db finds them. In my PHP app... I have a small arrow that allow them to move a row up or down changing the display order. Currently this is done by looping through the results of all the items in a group and reassigning a new sort_order value to each one. Meaning 1 SELECT and MANY updates (1 for each item in the group). I was wondering if anyone has come up with a better way to do this. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing sort order of items.
I wouldn't use a loop but an UPDATE statement instead. If I understand you correctly, all of your records are in the same group but you need them displayed in a user-defined order. It would be MUCH easier to manage that if the sort order values were already in sequential order. You may need to do a one-time loop-based query to individually reset each row to an appropriate value but after that these UPDATE statements should keep everything in order. Assume you want to move an item of group 6 from position 2 to position 6 SELECT @id := id from basictable where group_id = 6 and sort_order = 2; UPDATE basictable SET sort_order = if (id = @id, 6, sort_order -1) WHERE group_id = 6 AND sort_order BETWEEN 2 AND 6; and going the other way, from position 10 to position 2 SELECT @id := id from basictable where group_id = 6 and sort_order = 10; UPDATE basictable SET sort_order = if (id = @id, 2, sort_order +1) WHERE group_id = 6 AND sort_order BETWEEN 2 AND 10; make sense? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Michael J. Pawlowsky [EMAIL PROTECTED] wrote on 11/29/2004 11:17:01 AM: I'm trying to create the most efficient way to allow a user to change the display order of a group of rows in a table. Lets say the basic table is: id group_id name sort_order The query to display it would be SELECT id, name FROM mytable WHERE group_id = $x ORDER BY sort_order Now when I display it they currenlty all have the same sort_order value so they come in the order however the db finds them. In my PHP app... I have a small arrow that allow them to move a row up or down changing the display order. Currently this is done by looping through the results of all the items in a group and reassigning a new sort_order value to each one. Meaning 1 SELECT and MANY updates (1 for each item in the group). I was wondering if anyone has come up with a better way to do this. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing sort order of items.
Michael J. Pawlowsky wrote: I'm trying to create the most efficient way to allow a user to change the display order of a group of rows in a table. Lets say the basic table is: id group_id name sort_order The query to display it would be SELECT id, name FROM mytable WHERE group_id = $x ORDER BY sort_order Now when I display it they currenlty all have the same sort_order value so they come in the order however the db finds them. In my PHP app... I have a small arrow that allow them to move a row up or down changing the display order. Currently this is done by looping through the results of all the items in a group and reassigning a new sort_order value to each one. Meaning 1 SELECT and MANY updates (1 for each item in the group). I was wondering if anyone has come up with a better way to do this. Here is one idea: The sort_order column could be an integer, you could put values 1,2,3... for each group in this column. When a user move a row down, you issue statements similar to this: SELECT @so:=sort_order FROM mytable WHERE id = $moving_id; UPDATE mytable SET sort_order = sort_order + 1 WHERE id = $moving_id; UPDATE mytable SET sort_order = sort_order - 1 WHERE group_id=$x AND [EMAIL PROTECTED] + 1 AND id != $moving_id; This assumes you only know the group_id $x and the $moving_id. The @so is a user variable, it wont work well with replication. You can easily do the same in your application. If you also know the id of the row you are swapping with, its easier: UPDATE mytable SET sort_order = sort_order + 1 WHERE id = $moving_id; UPDATE mytable SET sort_order = sort_order - 1 WHERE id = $other_id; It would be equally easy to swap any two items in the list, not just two adjecent items. Just swap sort_order values. To move any item to the top, you would need something like this: SELECT @so:=sort_order FROM mytable WHERE id = $moving_id; UPDATE mytable SET sort_order = 1 WHERE id = $moving_id; UPDATE mytable SET sort_order = sort_order + 1 WHERE group_id=$x AND sort_order@so and id != $moving_id; In this case all rows between the moving row and the row it is moving to needs to be changed. This is heavier on the db, but it should not be an issue, unless your users do this all the time. You should in any case index the table non-uniquely on (group_id,sort_order), in addition to the primary key on id. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL question.... Trying to improve upon my PHP solution.
I have a table of members, about 13,000 rows.
Each night I need to shuffle the table. I have a small int column called
random_position. Currently I am creating a position list (based on the count
of the members), shuffle it, then while iterating through the members
assigning them a a position.
$time_start = microtime_float();
$member = new DataObjects_Member();
$number_of_rows = $member-find();
$positions = array();
for ($i = 1; $i = $number_of_rows; $i++) {
$positions[] = $i;
}
shuffle($positions);
while ($member-fetch()) {
// choose a position from the bottom
$position = array_pop($positions);
// set this member with that position
$member-setRandom_position($position);
$member-update();
}
Of course by doing it this way I'm running 13,000 SELECT calls and 13,000
UPDATE calls which can be processor intensive. As of now I'm timing it at
225 seconds but this machine is faster than server.
Is there a simpler / faster SQL query I could use?
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]
Re: SQL question.... Trying to improve upon my PHP solution.
At 11:08 AM 11/29/2004, you wrote:
I have a table of members, about 13,000 rows.
Each night I need to shuffle the table. I have a small int column called
random_position. Currently I am creating a position list (based on the count
of the members), shuffle it, then while iterating through the members
assigning them a a position.
$time_start = microtime_float();
$member = new DataObjects_Member();
$number_of_rows = $member-find();
$positions = array();
for ($i = 1; $i = $number_of_rows; $i++) {
$positions[] = $i;
}
shuffle($positions);
while ($member-fetch()) {
// choose a position from the bottom
$position = array_pop($positions);
// set this member with that position
$member-setRandom_position($position);
$member-update();
}
Of course by doing it this way I'm running 13,000 SELECT calls and 13,000
UPDATE calls which can be processor intensive. As of now I'm timing it at
225 seconds but this machine is faster than server.
Is there a simpler / faster SQL query I could use?
Thanks!
~ Mike
-
Mike Zornek
Web Designer, Media Developer, Programmer and Geek
Personal site: http://MikeZornek.com
Mike,
Your solution is way too complicated (it makes my head hurt).g
Try this:
set @n=0;
update tmp set rnd = @n := @n + 1 order by RAND()
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing sort order of items.
It would be equally easy to swap any two items in the list, not just two adjecent items. Just swap sort_order values. Yup... I think that is the key! Basically as they are inserted I will look up the max sort_order value for that group so far and increase that by one for the current insert. Then when it come to changing the sort order values I will simply swap it with the value of the previous or next one. So only 2 updates. The only thing I need to look out for is not to allow them to try and move the first row up... or the last row down. Thanks for the help, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question.... Trying to improve upon my PHP solution.
On 11/29/04 12:27 PM, mos [EMAIL PROTECTED] wrote: Mike, Your solution is way too complicated (it makes my head hurt).g Try this: set @n=0; update tmp set rnd = @n := @n + 1 order by RAND() Mike I'll give this a shot. Follow-up question: I've had a lot of trouble with RAND() on my MySQL 3 box not being all that random. This time I'm using 4.0.17-log. Is it going to be any better? ~ 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]
relay log file maintenance problem
I have a replication server that is running out of disk space because of old relay binary log files. Both it and the master server are version 4.0.20. I have a cron script that maintains a 3 day history of binary log files on the master server. Has someone figured out a way to manage the number relay log files so that someone doesn't have to check a replication server to see if the filesystem for the MySQL data directory hasn't been filled by old relay log files that haven't been accessed for some specified time? Thanks in advance, -bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL configuration file on Mac OS X
Hi List, I installed the MySQL 4.1.7 on my Windows XP and for that I have used the my.ini to set the startup configuration for using a different language and to use the old_password. Now I am installing on my Mac and I am wondering if there is any my.ini or my.cnf on Mac OS X. Does anyone knows about this? Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question.... Trying to improve upon my PHP solution.
- Original Message -
From: Mike Zornek [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, November 29, 2004 12:08 PM
Subject: SQL question Trying to improve upon my PHP solution.
I have a table of members, about 13,000 rows.
Each night I need to shuffle the table. I have a small int column called
random_position. Currently I am creating a position list (based on the
count
of the members), shuffle it, then while iterating through the members
assigning them a a position.
$time_start = microtime_float();
$member = new DataObjects_Member();
$number_of_rows = $member-find();
$positions = array();
for ($i = 1; $i = $number_of_rows; $i++) {
$positions[] = $i;
}
shuffle($positions);
while ($member-fetch()) {
// choose a position from the bottom
$position = array_pop($positions);
// set this member with that position
$member-setRandom_position($position);
$member-update();
}
Of course by doing it this way I'm running 13,000 SELECT calls and 13,000
UPDATE calls which can be processor intensive. As of now I'm timing it at
225 seconds but this machine is faster than server.
Is there a simpler / faster SQL query I could use?
I don't think this is a question about SQL at all; I think you already know
how to write the SQL to select, insert, update or delete rows.
I think that what you really want to know is if there is a more efficient
way to shuffle your rows and that is more a question of choosing a good
programming algorithm and plugging in the SQL you already know.
I have to admit I'm curious about why you need to do this. In many years of
database work, I've never seen the need to shuffle a table before. By the
way, could you clarify what you mean by shuffle? Do you mean that you want
to completely randomize every row and put it in some new, randomly-chosen
slot within the table? Or are you only moving the bottom row to the top
and pushing each of the others down one slot?
I suspect that you might be able to avoid this shuffle entirely and simply
choose rows at random based on their primary key without inserting,
updating, or deleting anything. You probably only have to show the rows in a
random sequence without actually physically moving them within the table. In
that case, simply get a list of the primary key values for every row of the
table and assign them to an array; then use a random number generator to
select keys from the array at random. Of course, this still gives you the
likelihood of choosing some records twice or multiple times and some not at
all; you'd need to make the algorithm recognize when it has grabbed a given
key already so that it ignores it if it is chosen a second time.
If PHP has a data structure analagous to Java's vector, it would be a lot
better choice: you simply put each primary key in its own slot of the
vector, select each key at random via the random number generator, and after
you've selected the row that corresponds to that primary key DELETE THE KEY
FROM THE VECTOR. That ensures that no row is selected twice and every row
gets selected at some point.
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: relay log file maintenance problem
Bill Thomason wrote: I have a replication server that is running out of disk space because of old relay binary log files. Both it and the master server are version 4.0.20. I have a cron script that maintains a 3 day history of binary log files on the master server. Has someone figured out a way to manage the number relay log files so that someone doesn't have to check a replication server to see if the filesystem for the MySQL data directory hasn't been filled by old relay log files that haven't been accessed for some specified time? Bill: Doublecheck to make sure your SQL thread is running ( SHOW SLAVE STATUS). Relay logs should get deleted as soon as they are processed by the SQL thread. Another potential problem - if you do not change the default config, and leave less than 1 GB for the relay log partition you will have a problem. Either put them on a bigger partition, or set max-relay-log-size to a lower value. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: host blocked, but can't see errors
Scott Tanner 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. Scott: I believe this is as much logging as you can get. However, this particular error happens when too many network reads are timing out. There are two most likely causes I can think of: * Network problems * The client CPU load is very high. So some client process starts authenticating, then gets rescheduled, and takes forever to get another turn to finish authenticating. As Gleb has pointed out, increasing max_connect_errors will help. Also, try increasing connect_timeout. And set up a cron job that runs FLUSH HOSTS every so often. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: glibc thread_stack
Wouter de Jong wrote: Hello, For a few servers with 1000 concurrent connections, I've compiled glibc 2.3.3 with a patched STACK_SIZE in linuxthreads/descr.h : #ifndef STACK_SIZE #define STACK_SIZE (128 * 1024) #endif instead of #define STACK_SIZE (2 * 1024 * 1024) This by the hints on the Documentation @ mysql.com But, now I notice: 041125 15:15:34 mysqld started 041125 15:15:34 Warning: Asked for 196608 thread stack, but got 126976 041125 15:15:35 InnoDB: Started /usr/local/mysql/libexec/mysqld: ready for connections. Is the 128K STACK_SIZE I've built glibc with too little, and should I update it to 256K ? Originally, MySQL team believed 128K was sufficient. Then some time later it was discovered that some DNS resolving routines required a 192K stack in some cases. So for safety reasons, mysqld was modified to request at least 192K stack. In your case, your modification to glibc makes it impossible for it to have a 192K stack, which is why you are getting the message. However, if you run mysqld with --skip-name-resolve (you will need to update your priv tables to use numeric addresses), glibc DNS routines are never called, so 128K should be sufficient. Also, with the disclaimer that I have not investigated this issue deeply, from a quick research + some memory about the incident, I believe the 192K stack requirement comes from a RedHat patch to libc, and if your version of libc does have this problem, mysqld will crash right way the moment you try to connect to it. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL configuration file on Mac OS X
Mac OS X is Unix, so you follow the directions for Unix, which means you use my.cnf. See the manual for details http://dev.mysql.com/doc/mysql/en/Option_files.html. Michael Andre Matos wrote: Hi List, I installed the MySQL 4.1.7 on my Windows XP and for that I have used the my.ini to set the startup configuration for using a different language and to use the old_password. Now I am installing on my Mac and I am wondering if there is any my.ini or my.cnf on Mac OS X. Does anyone knows about this? Thanks for any help. Andre -- 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 09:01 am, Greg Macek wrote: I am actually in the process of planning a similar upgrade for our database server. None of our databases are that big, but was wondering about what gotcha's I should be on the lookout for. I planned on walking through the upgrade pages from mysql.com for going from 3.23 to 4.0 and 4.0 to 4.1. I was hoping not to have to re-create the databases as the original poster mentioned, but I don't have a slave DB setup either. Will this be an issue? Well, in 4.0.22, you can do a new=1.. (something like that). and yer suppose to be able to test these new features in 4.1.. Might want to look for that in 3.23.. Jeff pgpomOHXppHgb.pgp Description: PGP signature
Re: Bizarre table type switch
Stuart, you probably have skip-innodb in my.cnf. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Stuart Felenstein [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, November 29, 2004 9:58 AM Subject: Re: Bizarre table type switch --- Stuart Felenstein [EMAIL PROTECTED] wrote: I'm not sure what happened but when I ran some test yesterday on a transaction it failed. Being puzzled I started digging around. I have come to find out that all the tables involved were now set to MyISAM. Obviously transactions wouldn't work. The odd thing that I'd like to figure out is how did they get switched. I know this is a vague and gray question , and if there is a way to trace it I'd like to know. Is there anything that would cause this type of occurance ? Anything I can do to find out why it happened? I forgot to mention this is 4.0.22-standard. I have requested my ISP check my.cnf to see if Innodb has been skipped for support. I am able to switch them back though. Stuart -- 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: Tunning Problem
Ronan Lucio wrote: Hi All, I´m having a trouble where my server is falling to it's knees when a certain number of connections are match. Well, in a short, I configured the my.cnf files to accept up to 120 connections. When the server receives about to 55 connections, it hangs the connections and I don´t get me even enter the MySQL interactive mode. I see in the list people configuring the MySQL tu accept up to 1000 connections and my server don´t get to hold 100... :-/ I think some queries of the applications was badly designed, but even thus I need to optimize it. 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. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- 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
At 12:57 -0600 11/29/04, Jeff Smelser wrote: On Monday 29 November 2004 09:01 am, Greg Macek wrote: I am actually in the process of planning a similar upgrade for our database server. None of our databases are that big, but was wondering about what gotcha's I should be on the lookout for. I planned on walking through the upgrade pages from mysql.com for going from 3.23 to 4.0 and 4.0 to 4.1. I was hoping not to have to re-create the databases as the original poster mentioned, but I don't have a slave DB setup either. Will this be an issue? Well, in 4.0.22, you can do a new=1.. (something like that). and yer suppose to be able to test these new features in 4.1.. Might want to look for that in 3.23.. No, there is no such option in 3.23. In general, it's better to upgrade 3.23 - 4.0 - 4.1 so that you can get used to differences from one version series to the next, rather than skipping version series. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question.... Trying to improve upon my PHP solution.
On 11/29/04 1:26 PM, Rhino [EMAIL PROTECTED] wrote: I don't think this is a question about SQL at all; I think you already know how to write the SQL to select, insert, update or delete rows. I think that what you really want to know is if there is a more efficient way to shuffle your rows and that is more a question of choosing a good programming algorithm and plugging in the SQL you already know. I have to admit I'm curious about why you need to do this. In many years of database work, I've never seen the need to shuffle a table before. By the way, could you clarify what you mean by shuffle? Do you mean that you want to completely randomize every row and put it in some new, randomly-chosen slot within the table? Or are you only moving the bottom row to the top and pushing each of the others down one slot? The basic problem is this. We have a search engine that lets photo buyers search our db for photographers. Rather than sort on name the results have always by default been randomized to help level the playing field. And yes this works but it causing frustration to users as when you bookmark the results, or refresh it for various reasons the order is always new. Our idea is to nightly order the members at random and when showing results. I call this shuffling only to make the metaphor. I really mean setting randomized values in a extra column which will be sorted when the user asks for random results. Thanks for you help. ~ 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]
Re: Bizarre table type switch
--- Heikki Tuuri [EMAIL PROTECTED] wrote: Stuart, you probably have skip-innodb in my.cnf. Best regards, Heikki Tuuri Heikki - Nope , doesn't seem so. My.cnf is below. Also, I'm guessing that if it was set to skip-innodb, I wouldn't not have had the ability to change them back. It's very puzzling. I'm not expecting to find an answer. The logs were checked and nothing found to inidicate anything. I'm on shared host. Supposedly my ISP did this for me. They are pretty reliable. But we are talking about 20+ tables. [mysqld] safe-show-database max_connections = 750 key_buffer = 136M myisam_sort_buffer_size = 72M join_buffer_size = 1M read_buffer_size = 1M sort_buffer_size = 2M table_cache = 1024 thread_cache_size = 128 wait_timeout = 5500 connect_timeout = 10 max_allowed_packet = 32M max_connect_errors = 10 query_cache_limit = 2M query_cache_size = 72M query_cache_type = 1 tmp_table_size = 72M read_rnd_buffer_size = 524288 bulk_insert_buffer_size = 72M [mysqld_safe] open_files_limit = 8192 [mysqldump] quick max_allowed_packet = 32M [myisamchk] key_buffer = 72M sort_buffer = 72M read_buffer = 24M write_buffer = 24M Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Heap Help
I want to put a table in Ram (HEAP) with a field of at least 500 characters. I do I do this if Blob and text are not allowed? ThanksDonny LairsonPresidenthttp://www.gunmuse.com469 228 2183
Re: SQL question.... Trying to improve upon my PHP solution.
At 11:53 AM 11/29/2004, you wrote: On 11/29/04 12:27 PM, mos [EMAIL PROTECTED] wrote: Mike, Your solution is way too complicated (it makes my head hurt).g Try this: set @n=0; update tmp set rnd = @n := @n + 1 order by RAND() Mike I'll give this a shot. Follow-up question: I've had a lot of trouble with RAND() on my MySQL 3 box not being all that random. This time I'm using 4.0.17-log. Is it going to be any better? ~ Mike Mike, According to http://dev.mysql.com/doc/mysql/en/News-4.0.1.html they've improved Rand in version 4.0.1 Changed RAND() initialization so that RAND(N) and RAND(N+1) are more distinct. and in 4.0.10 they Fixed initialization of the random seed for newly created threads to give a better rand() distribution from the first call. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL configuration file on Mac OS X
I just have finished to install the version 4.1.7 on my Mac and I looked at these directories below and I couldn't find the file my.cnf as described on the like that you gave me: /etc/my.cnf DATADIR/my.cnf ~/.my.cnf Do I need to create it or MySQL is suppose to do the job? Thanks. Andre On 11/29/04 1:55 PM, Michael Stassen [EMAIL PROTECTED] wrote: Mac OS X is Unix, so you follow the directions for Unix, which means you use my.cnf. See the manual for details http://dev.mysql.com/doc/mysql/en/Option_files.html. Michael Andre Matos wrote: Hi List, I installed the MySQL 4.1.7 on my Windows XP and for that I have used the my.ini to set the startup configuration for using a different language and to use the old_password. Now I am installing on my Mac and I am wondering if there is any my.ini or my.cnf on Mac OS X. Does anyone knows about this? Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL configuration file on Mac OS X
At 15:26 -0500 11/29/04, Andre Matos wrote: I just have finished to install the version 4.1.7 on my Mac and I looked at these directories below and I couldn't find the file my.cnf as described on the like that you gave me: /etc/my.cnf DATADIR/my.cnf ~/.my.cnf Do I need to create it or MySQL is suppose to do the job? You create it according to the configuration you want. Thanks. Andre On 11/29/04 1:55 PM, Michael Stassen [EMAIL PROTECTED] wrote: Mac OS X is Unix, so you follow the directions for Unix, which means you use my.cnf. See the manual for details http://dev.mysql.com/doc/mysql/en/Option_files.html. Michael Andre Matos wrote: Hi List, I installed the MySQL 4.1.7 on my Windows XP and for that I have used the my.ini to set the startup configuration for using a different language and to use the old_password. Now I am installing on my Mac and I am wondering if there is any my.ini or my.cnf on Mac OS X. Does anyone knows about this? Thanks for any help. Andre -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL configuration file on Mac OS X
Hello,
I installed a tar.gz version and I found it in a support directory:
there are some my.cnf file my{}.cnf where is large, huge,
...
Santino
At 15:26 -0500 29-11-2004, Andre Matos wrote:
I just have finished to install the version 4.1.7 on my Mac and I looked at
these directories below and I couldn't find the file my.cnf as described
on the like that you gave me:
/etc/my.cnf
DATADIR/my.cnf
~/.my.cnf
Do I need to create it or MySQL is suppose to do the job?
Thanks.
Andre
On 11/29/04 1:55 PM, Michael Stassen [EMAIL PROTECTED] wrote:
Mac OS X is Unix, so you follow the directions for Unix, which means you use
my.cnf. See the manual for details
http://dev.mysql.com/doc/mysql/en/Option_files.html.
Michael
Andre Matos wrote:
Hi List,
I installed the MySQL 4.1.7 on my Windows XP and for that I have used the
my.ini to set the startup configuration for using a different language and
to use the old_password.
Now I am installing on my Mac and I am wondering if there is any my.ini
or my.cnf on Mac OS X.
Does anyone knows about this?
Thanks for any help.
Andre
--
Andre Matos
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bizarre table type switch
Stuart, ok, then this is a complete mystery. I have not heard about this before. Regards, Heikki - Original Message - From: Stuart Felenstein [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, November 29, 2004 9:33 PM Subject: Re: Bizarre table type switch --- Heikki Tuuri [EMAIL PROTECTED] wrote: Stuart, you probably have skip-innodb in my.cnf. Best regards, Heikki Tuuri Heikki - Nope , doesn't seem so. My.cnf is below. Also, I'm guessing that if it was set to skip-innodb, I wouldn't not have had the ability to change them back. It's very puzzling. I'm not expecting to find an answer. The logs were checked and nothing found to inidicate anything. I'm on shared host. Supposedly my ISP did this for me. They are pretty reliable. But we are talking about 20+ tables. [mysqld] safe-show-database max_connections = 750 key_buffer = 136M myisam_sort_buffer_size = 72M join_buffer_size = 1M read_buffer_size = 1M sort_buffer_size = 2M table_cache = 1024 thread_cache_size = 128 wait_timeout = 5500 connect_timeout = 10 max_allowed_packet = 32M max_connect_errors = 10 query_cache_limit = 2M query_cache_size = 72M query_cache_type = 1 tmp_table_size = 72M read_rnd_buffer_size = 524288 bulk_insert_buffer_size = 72M [mysqld_safe] open_files_limit = 8192 [mysqldump] quick max_allowed_packet = 32M [myisamchk] key_buffer = 72M sort_buffer = 72M read_buffer = 24M write_buffer = 24M Stuart -- 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: Heap Help
At 1:09 PM -0700 11/29/04, [EMAIL PROTECTED] wrote: I want to put a table in Ram (HEAP) with a field of at least 500 characters. I do I do this if Blob and text are not allowed? The glib answer would be: you can't. Two alternatives would be: (1) split your text field up into as many char/varchar columns as necessary 9eg; text_part_1, text_part_2, ...), and then split/rejoin those columns programmatically; or, (2) you might be able to losslessly compress or otherwise encode your text such that it will fit into a char(255) or smaller column. Thanks Donny Lairson President http://www.gunmuse.com/http://www.gunmuse.com 469 228 2183 -- +--- my people are the people of the dessert, ---+ | Steve Edberghttp://pgfsun.ucdavis.edu/ | | UC Davis Genome Center[EMAIL PROTECTED] | | Bioinformatics programming/database/sysadmin (530)754-9127 | + said t e lawrence, picking up his fork +
RE: Heap Help
Create a myISAM table and put the table on a RAM DISK. It will support everything that heap does and more, like ranges ( 4.1 only allows Hash lookups). CREATE TABLE TABLE NAME ( .. ) INDEX DIRECTORY = /dev/shm DATA DIRECTORY = /dev/shm DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, November 29, 2004 12:10 PM To: Mysql Subject: Heap Help I want to put a table in Ram (HEAP) with a field of at least 500 characters. I do I do this if Blob and text are not allowed? Thanks Donny Lairson President http://www.gunmuse.com http://www.gunmuse.com/ 469 228 2183
C Interface on Windows XP
I have MySQL installed on my Windows XP machine. No problems. I have seen examples of MySQL interfacing with C in the UNIX environment. I can install Visual C++ on my XP machine. Is it possible to interface any such Windows-based C/C++ compiler with MySQL? Any examples? Thanks.
Re: timestamp and DST: impossible to backup database? *AND* bugs in TIMEDIFF, FROM_UNIXTIME, et.al.?
Thank you Michael for your very thoughtful reply. I know that it takes time and effort to answer at the level you did. Michael Stassen Michael.Stassen-at-verizon.net |Lists| wrote: You seem to have a fundamental misunderstanding of the TIMESTAMP type. No timezone or DST information is stored in a TIMESTAMP column. Yup. I thought it could be used to unambiguously represent any and all points in time. It can't. Thats it in a nutshell. I need to be able to sort, get and set the time unambiguously, also during the one problem hour in october. I need to know that if I put in a field with a time value I can reliably retrieve it again. And that if a record went in at time X and another in at time Y, Y-X is accurate for all values of Y and X, regardless of how we humans have decided to present X and Y to each other. (Standard computer stuff, no?) DATETIME is ambiguous, seconds since epoch UTC is not. Maybe my surprise is more: Hey, depending on now(), a UNIX_TIMESTAMP(2004-10-31 02:15:00) has two different interal values!!! (Why now() should have any effect on that is still weird to me... I realize *how* it ends up having an effect implementationally, but it *shouldn't*.) The other value is not representable at all by any DATETIME value. And sorting on a DATETIME gives one result now and another after a dump/restore cycle. And there is no way around that. We'll change our application to int(32) unsigned and handle presentation client-side. I don't think I'll ever use a DATETIME again... Maybe thats just me. Thanks again, Michael. 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]
Re: Pushing Files from Subversion
pete-at-holidian.com |Lists| wrote: Disclaimer: I'm new to source control in general and Subversion in specific ... I'd like to be able to make the change and then push those changes... I just wanted to make sure you are aware that the server doesn't know about / keep track of all the checked out working copies in the universe. The server doesn't know who to pus to. The client has to poll somehow or you need to do something specific for those clients in a post-commit hook script. I'll bet cron + svn update is your best friend right around now 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]
Re: Newbie: How to deal with multiple languages
thanks for all the help this will help as I think the client wants this project in 3 languages On Nov 28, 2004, at 7:25 AM, Rhino wrote: - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, November 27, 2004 5:36 AM Subject: Re: Newbie: How to deal with multiple languages Hello. You can find an answer here: http://dev.mysql.com/doc/mysql/en/Charset.html MySQL supports column character sets on columns of some types (char,varchar,text). Probably if I were you I would use Unicode in my application. Graham Anderson [EMAIL PROTECTED] wrote: I have a mysql db that contains tables with multiple language fields for example... Artist_id 'PK' Artist_name Artist_pictLink Artist_purchaseLink Artist_bio_Spanish Artist_bio_English Artist_bio_German I have other tables with a similar layout...Is this needlessly complicated ? track_id 'PK' Artist_id 'FK' track_name_Spanish track_name_English track_name_German track_path track_versionTotal track_purchaseLink track_pictLink Is there a better way to deal with tables that need multiple language fields...like creating another Db for that language ? trying to get the design down before I end up with a huge headache... You *could* alter your design to do something like this: create table artist (artist_id [column type] not null, artist_name [column type] not null, artist_pictlink [column type], artist_purchaseLink [column type], artist_bio_code int, primary key (artist_id) foreign key artist_bio_code references artist_bio on delete restrict) Type=InnoDB; create table artist_bio (artist_bio_code int not null, artist_bio_Spanish [column type], artist_bio_English [column type], artist_bio_German [column type], primary key(artist_bio_code)) Type=InnoDB; You would then have to join to get the artist_bio information in the desired language(s) but, of course, you wouldn't have to do the join unless you needed the bio. The dramatically smaller size of your artist table could help your performance for those queries where you don't need the bio. Naturally, queries that need the bio will have a bit more work to do to get the bio. Both designs lend themselves to supporting additional languages if that should become necessary. I think that is very important because I can easily imagine having to increase the number of languages. I haven't done any work with character sets in MySQL so I don't know if there would be any advantage to having the foreign character data separated into their own tables so that 'main' tables like 'Artist' would have only standard characters. You should probably read the chapter on character sets that Gleb cited to try to figure that out. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] graham anderson 310.402.3980 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored Procedure?
Ok, I'm new to MySql (or SQL in general). I'm curious what exactly a stored procedure in MySql is, and what the benefit would be? Steve -- 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: Steve Grosz [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, November 29, 2004 10:55 PM Subject: Stored Procedure? Ok, I'm new to MySql (or SQL in general). I'm curious what exactly a stored procedure in MySql is, and what the benefit would be? Stored procedures are new in MySQL 5.0 but very few people are running 5.0 yet. I think 5.0 is available in a pre-beta but I haven't heard anything about how stable it is. In other words, you may have to wait a while to use stored procedures unless you are really eager to be 'bleeding edge'. Stored procedures are very popular on databases that already have them, like DB2. Their main advantage is when they move a lot of the processing of a given task to the server from the client. The classic example goes something like this: 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. Let's say you moved the majority of the work to the server and simply invoked the program that did the work from the client. The client simply tells the program (which we call a stored procedure) at the server to read all the rows, sort them, read the first half of them and report on the median mark. In this scenario, the network traffic drops to almost nothing: there is the instruction that invokes the stored procedure and then the median mark returning from the stored procedure. Everything else takes place within the stored procedure ON THE SERVER. The server is often a particularly powerful computer with extra fast devices, more memory, etc. so it is able to do the work faster than the client would in many cases, even if network traffic wasn't an issue. The net result is that the same work gets done with far less network traffic. So, a stored procedure is, in essence, simply a program that runs on a server without a network between it and the database. The stored procedure is invoked by a client program and returns a result to the client. Rhino -- 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 Mon, 2004-11-29 at 13:11 -0600, Paul DuBois wrote: At 12:57 -0600 11/29/04, Jeff Smelser wrote: On Monday 29 November 2004 09:01 am, Greg Macek wrote: I am actually in the process of planning a similar upgrade for our database server. None of our databases are that big, but was wondering about what gotcha's I should be on the lookout for. I planned on walking through the upgrade pages from mysql.com for going from 3.23 to 4.0 and 4.0 to 4.1. I was hoping not to have to re-create the databases as the original poster mentioned, but I don't have a slave DB setup either. Will this be an issue? Well, in 4.0.22, you can do a new=1.. (something like that). and yer suppose to be able to test these new features in 4.1.. Might want to look for that in 3.23.. No, there is no such option in 3.23. In general, it's better to upgrade 3.23 - 4.0 - 4.1 so that you can get used to differences from one version series to the next, rather than skipping version series. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MIXING MYISAM AND INNODB
I'm using multiple dbs for my very high traffic multiple sites. One of my db (say parentDb) just maintains users who can login to all the sites. Others are sites specific dbs(say childDbs). I'm using INNODb tables for my parentDb while my childDbs tables are of MyISAM type. Some code does have direct joins with between parentDb and childDb. Is it good to use this kind of mixture? Can it create problems in future? __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
One question about Perl DBI interface.
Good morning! Important: SQL Server used: MySQL (version 4.0.17) on UNIX FreeBSD. Answer this question please: Can I make a request, consisting of several SQL commands at one step, like this: use DBI; my $db = DBI-new(); $db = DBI-connect(...) or die Can't connect to DataBase:, error code, \n; $db-do(CREATE TEMPORARY TABLE tmptable (field1 INT(4) UNSIGNED ZEROFILL DEFAULT '' NOT NULL,field2 DOUBLE(16,2) DEFAULT '0.00' NOT NULL); LOCK TABLES table1 read; INSERT INTO tmptable SELECT field1, MAX(field2) FROM table1 GROUP BY field1;); I can do it by sending these commands one by one in series, like this: use DBI; my $db = DBI-new(); $db = DBI-connect(...) or die Can't connect to DataBase:, error code, \n; $db-do(CREATE TEMPORARY TABLE tmptable (field1 INT(4) UNSIGNED ZEROFILL DEFAULT '' NOT NULL,field2 DOUBLE(16,2) DEFAULT '0.00' NOT NULL);); $db-do(LOCK TABLES table1 read;); $db-do(INSERT INTO tmptable SELECT field1, MAX(field2) FROM table1 GROUP BY field1;); I think last solution is more time expensive. # Truly yours Sviridov Vladislav. [EMAIL PROTECTED]
