Re: bulk_insert_buffer_size and InnoDB
Hi! On May 10, Jan Pieter Kunst wrote: Dear all, I read the following on this page http://dev.mysql.com/doc/mysql/en/server-system-variables.html: bulk_insert_buffer_size MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. Note: This cache is used only when adding data to a non-empty table. The default value is 8MB. This variable was added in MySQL 4.0.3. This variable previously was named myisam_bulk_insert_tree_size. I take this to mean that bulk_insert_buffer_size has no bearing on InnoDB tables, only on MyISAM tables. Is this correct? Yes. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: missing file ( msyql.sock)
Hello. You may create my.cnf from the examples which're shipped with MySQL distribution. MySQL will create the socket file if it doesn't exists. It's location could be specified in configuration file or in command line options. See: http://dev.mysql.com/doc/mysql/en/program-options.html ganesan malairaja [EMAIL PROTECTED] wrote: now i am missing the file mysql.sock .. i could not find it anywhere on the computer i also could not find my.cnf .. i created one my.cnf pointing it o /tem/mysql.sock now i dont have the mysql.sock file where to get it -- 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: memory errors / crashes
Hello. See: http://dev.mysql.com/doc/mysql/en/crashing.html Chris Knipe [EMAIL PROTECTED] wrote: We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=536870912 read_buffer_size=2093056 max_used_connections=418 max_connections=2048 threads_connected=404 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 516080 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Righty. Now, the system has 4 x 3.2GHz Intel processors... It has 4GB of ram... systat shows that all 4 processors are definately used... NO swap space is used at all (4GB allocated), and yes, it uses the entire 4GB of ram under heavy load... I am aware of the POSSIBILITY of the 2GB limit... However, I highly doubt that this is related as the system has no problem to use the 4GB of RAM (According to top in any case). 1) Why does the system not swap, and 2) Why is Mysql complaining that it needs at least 512MB ram, whilst there are 4GB available to it?? I've been battling for over 3 weeks to get this right, I have made multiple posts on the list about this... can someone please just give me some answers We're running FBSD 5.4-STABLE. -- Chris. -- 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: SELECT Row Numbers?
In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: hi, your need is: select * from temp LIMIT 3,4; -- 3 because you have to take the fourth and 4 because dist=3+1 This does not make sense. A SELECT without an ORDER BY returns the rows in some undefined order. If you use LIMIT 3,4 without ORDER BY, you get four rows out of an unordered set, so it's virtually identical to LIMIT 4. As long as the original poster doesn't say what ordering he wants, there's no way to tell him a solution. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
inserting special characters
Hi, everybody! I have a problem working with MySQL and C++ Builder 6. I can´t find the solution and I hope that maybe somebody can help me. I use the ZEOS components (6.1.5) to connect the MySQL database (version 4.1.11) with C++ Builder. The fact is that everything goes rigth except when I try to write into the database. When I write special spanish characters (accents, ñ,...), this characters are changed in the database, so they´re wrong. I think that it occurs because the default character set of C++ Builder is not supported by the MySQL database (but I´m not sure about it). If anyone can explain me the reason, I´ll be exceedingly grateful. Thank you very much for your attention. Regards, __ Renovamos el Correo Yahoo! Nuevos servicios, más seguridad http://correo.yahoo.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Row Numbers?
oix ppl, now I have a question. I thougth that when you make a SELECT without an ORDER BY, the rows were returned in natural order, that would be some specific order (insertion order, presence in file, I don't know, but the order would be always the same). Is this uncorrect? Is the returning order variable? Thanks, mpneves On Tuesday 10 May 2005 10:37, Harald Fuchs wrote: In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: hi, your need is: select * from temp LIMIT 3,4; -- 3 because you have to take the fourth and 4 because dist=3+1 This does not make sense. A SELECT without an ORDER BY returns the rows in some undefined order. If you use LIMIT 3,4 without ORDER BY, you get four rows out of an unordered set, so it's virtually identical to LIMIT 4. As long as the original poster doesn't say what ordering he wants, there's no way to tell him a solution. -- Marco Paulo Neves MySQL Core Certified Linux Certified Professional http://themage.bliker.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DATA TYPE QUESTIONS?
[snip] IF I use the following statement: SELECT * FROM `items` WHERE `item` = 10902 HAVING `venturi_type` = 'universal' OR `venturi_type` = 'special'; I get a complete and full data dump. IF I change the statement by inserting a 'letter' in this case 'S' instead of a 'number' in this case '0' in the 'item', i.e. from 10902 to 109S2 I get the following error: #1054 - Unknown column '109S2' in 'where clause IF I change the statement by putting single quotes around '109S2' then I get an SQL execution completed result from phpMAdmin, but NO data dump! The column 'venturi_type' is the same NO MATTER what part number 'item' is and should display, yet it does not. Why should it make a difference wether I have a letter or a numeral in a part number? [/snip] Because the part number may be a string, not a number. Oh sure, it looks like a number, but it is really a string. Also, the venturi_type should be a conditional other than a HAVINGtry this... SELECT * FROM `items` WHERE `item` = '10902' --(note single quotes around string) AND (`venturi_type` = 'universal' OR `venturi_type` = 'special'); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inserting special characters
Hi, Javier! I don't know Zeos components but I use C++ Builder 6 and ODBC to connect to MySQL and I had similar problem. My databases had character set set to latin2 and problem was my client's (C++ Builder and ODBC) character set. Try to issue command SET NAMES 'your_WIN_codepage' (main was cp1250) as a first command after connecting to MySQL. It tells server that text from client is in 'your_WIN_codepage' character set and server can convert it and store everything in your database's (or table's) character set. Hope it make sense. Dusan - Original Message - From: Javier Ballesteros Correa [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, May 10, 2005 1:14 PM Subject: inserting special characters Hi, everybody! I have a problem working with MySQL and C++ Builder 6. I can´t find the solution and I hope that maybe somebody can help me. I use the ZEOS components (6.1.5) to connect the MySQL database (version 4.1.11) with C++ Builder. The fact is that everything goes rigth except when I try to write into the database. When I write special spanish characters (accents, ñ,...), this characters are changed in the database, so they´re wrong. I think that it occurs because the default character set of C++ Builder is not supported by the MySQL database (but I´m not sure about it). If anyone can explain me the reason, I´ll be exceedingly grateful. Thank you very much for your attention. Regards, __ Renovamos el Correo Yahoo! Nuevos servicios, más seguridad http://correo.yahoo.es -- 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: inserting special characters
Hello. What output do these statements produce: show variables like '%char%'; show variables like '%coll%'; Include the results of SHOW CREATE TABLE executed on your tables as well. Javier Ballesteros Correa [EMAIL PROTECTED] wrote: Hi, everybody! I have a problem working with MySQL and C++ Builder 6. I can$t find the solution and I hope that maybe somebody can help me. I use the ZEOS components (6.1.5) to connect the MySQL database (version 4.1.11) with C++ Builder. The fact is that everything goes rigth except when I try to write into the database. When I write special spanish characters (accents, $,...), this characters are changed in the database, so they$re wrong. I think that it occurs because the default character set of C++ Builder is not supported by the MySQL database (but I$m not sure about it). If anyone can explain me the reason, I$ll be exceedingly grateful. Thank you very much for your attention. Regards, __ Renovamos el Correo Yahoo! Nuevos servicios, m$s seguridad http://correo.yahoo.es -- 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: SELECT Row Numbers?
You should never assume that an SQL query will return data in any specific order *unless* you use an ORDER BY to force the sequence of rows in the result set. Data returned by a query will sometimes appear to come out in a particular order but you should always view this as a lucky coincidence, not something that is guaranteed to happen. Rhino - Original Message - From: Marco Neves [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, May 10, 2005 7:14 AM Subject: Re: SELECT Row Numbers? oix ppl, now I have a question. I thougth that when you make a SELECT without an ORDER BY, the rows were returned in natural order, that would be some specific order (insertion order, presence in file, I don't know, but the order would be always the same). Is this uncorrect? Is the returning order variable? Thanks, mpneves On Tuesday 10 May 2005 10:37, Harald Fuchs wrote: In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: hi, your need is: select * from temp LIMIT 3,4; -- 3 because you have to take the fourth and 4 because dist=3+1 This does not make sense. A SELECT without an ORDER BY returns the rows in some undefined order. If you use LIMIT 3,4 without ORDER BY, you get four rows out of an unordered set, so it's virtually identical to LIMIT 4. As long as the original poster doesn't say what ordering he wants, there's no way to tell him a solution. -- Marco Paulo Neves MySQL Core Certified Linux Certified Professional http://themage.bliker.com -- 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.308 / Virus Database: 266.11.6 - Release Date: 06/05/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.8 - Release Date: 10/05/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Site search using MySQL fulltext index
Hi, I've implemented my site search using MySQL db fulltext index. I understand that fulltext index currently doesn't support stemming of words. However I want to implement my search such that a query containing words in singular tense matches records of words in plural tense and vice versa. Is there an easy workaround / hack for the same ? I don't have much experience with mysql. Thanks. Zooman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Underline or minus sign ?
No one ? Please help me with this. Gabriel - Original Message - From: Gabriel PREDA [EMAIL PROTECTED] Sent: Wednesday, May 04, 2005 4:36 PM Subject: Underline or minus sign ? I'm going to start the InnoDB engine... and I want to know if the syntax for the CNF file unified at last ? Can I use: innodb-file-per-table instead of innodb_file_per_table ? [This is just an example] Can I use only minus sign in the whole CNF file instead of underline ? Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
backup a database
Hi, I 'm using mysqldump to backup my database, I wrote in my backup.sh the following string: $MYSQL/bin/mysqldump --opt databasename -user=root --password=rootpassword backup/mysql/databasename.sql I include backup.sh in my cron weekly (/etc/cron.weekly) but when it is executed appear databasename.sql empty, else if I run backup.sh in a shell konsole it is executed ok, do you know why can not execute the string in the cron ?? my mysql version is : 4.0.17 regards rafael
Re: Underline or minus sign ?
Gabriel PREDA wrote: I'm going to start the InnoDB engine... and I want to know if the syntax for the CNF file unified at last ? Can I use: innodb-file-per-table no instead of innodb_file_per_table ? this one (at least for 4.1.11) [This is just an example] Can I use only minus sign in the whole CNF file instead of underline ? no, i think it depends the option configured (..a bit confusing, I know) Gabriel -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange Error in MySQL
Hello. I am receiving the following error when trying to insert into a field of type text: #1030 - Got error 139 from storage engine The data to be inserted should fit easily into a text field. In fact, this error only occurs for a particular record and other records with much larger data sets are not throwing the same error. Has anyone experienced something similar? I checked via Google but did not find any resources that helped. Any help would be greatly appreciated. Thanks. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup a database
Rafael Diaz Valdes wrote: Hi, I 'm using mysqldump to backup my database, I wrote in my backup.sh the following string: $MYSQL/bin/mysqldump --opt databasename -user=root --password=rootpassword backup/mysql/databasename.sql I include backup.sh in my cron weekly (/etc/cron.weekly) but when it is executed appear databasename.sql empty, else if I run backup.sh in a shell konsole it is executed ok, do you know why can not execute the string in the cron ?? my mysql version is : 4.0.17 regards rafael Hi Rafael, Check the value of the variable $MYSQL. It is probably not being set by cron and should be set in your shell script. Cron starts with only a couple of variables set and you have to start everything else. Are you getting any output from the cron daemon? It should be in the logs somewhere or else emailed to root. Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup a database
Rafael Diaz Valdes wrote: Hi, I 'm using mysqldump to backup my database, I wrote in my backup.sh the following string: $MYSQL/bin/mysqldump --opt databasename -user=root --password=rootpassword backup/mysql/databasename.sql I include backup.sh in my cron weekly (/etc/cron.weekly) but when it is executed appear databasename.sql empty, else if I run backup.sh in a shell konsole it is executed ok, do you know why can not execute the string in the cron ?? my mysql version is : 4.0.17 regards rafae Do you have $MYSQL set? This really isn't a mysql question. It is a matter of elementary troubleshooting. Add to your script: echo $MYSQL/bin/mysqldump /tmp/junk and see what you have. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Strange Error in MySQL
[snip] Hello. I am receiving the following error when trying to insert into a field of type text: #1030 - Got error 139 from storage engine The data to be inserted should fit easily into a text field. In fact, this error only occurs for a particular record and other records with much larger data sets are not throwing the same error. Has anyone experienced something similar? I checked via Google but did not find any resources that helped. Any help would be greatly appreciated. Thanks. [/snip] The row is too big. Are there other columns into which a large amount of data are being placed? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup a database
Rafael Diaz Valdes wrote: Hi, I 'm using mysqldump to backup my database, I wrote in my backup.sh the following string: $MYSQL/bin/mysqldump --opt databasename -user=root --password=rootpassword backup/mysql/databasename.sql I include backup.sh in my cron weekly (/etc/cron.weekly) but when it is executed appear databasename.sql empty, else if I run backup.sh in a shell konsole it is executed ok, do you know why can not execute the string in the cron ?? maybe make backup/mysql/databasename.sql an absolute path, to avoid wrong permission or writing the file in a non-existing folder (due to a wrong working directory, try echo $PWD in your backup.sh). I had the same pb when we setup our daily backup. my mysql version is : 4.0.17 regards rafael -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange Error in MySQL
#1030 - Got error 139 from storage engine The data to be inserted should fit easily into a text field. In fact, this error only occurs for a particular record and other records with much larger data sets are not throwing the same error. Has anyone experienced something similar? I checked via Google but did not find any resources that helped. Any help would be greatly appreciated. Thanks. http://bugs.mysql.com/bug.php?id=10035 http://bugs.mysql.com/bug.php?id=5682 ... might provide a clue. These could be found in a simple Google search: http://www.google.com/search?hl=enq=%22Got+error+139+from+storage+engine%22btnG=Google+Search Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Row Numbers?
Hi, Have you forgotten what's a primary key ? Using order by will sort data, and if it's already sorted, it will be sorted again. Time, memory and maybe disk io. Using the marco example, i gaved a solution considering iy's what he wants. Till now i don't know if it's ok or not. if so, just add : select * from temp order by Id LIMIT 3,4; if no, the primary key index will give you the order. Mathias Selon Rhino [EMAIL PROTECTED]: You should never assume that an SQL query will return data in any specific order *unless* you use an ORDER BY to force the sequence of rows in the result set. Data returned by a query will sometimes appear to come out in a particular order but you should always view this as a lucky coincidence, not something that is guaranteed to happen. Rhino - Original Message - From: Marco Neves [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, May 10, 2005 7:14 AM Subject: Re: SELECT Row Numbers? oix ppl, now I have a question. I thougth that when you make a SELECT without an ORDER BY, the rows were returned in natural order, that would be some specific order (insertion order, presence in file, I don't know, but the order would be always the same). Is this uncorrect? Is the returning order variable? Thanks, mpneves On Tuesday 10 May 2005 10:37, Harald Fuchs wrote: In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: hi, your need is: select * from temp LIMIT 3,4; -- 3 because you have to take the fourth and 4 because dist=3+1 This does not make sense. A SELECT without an ORDER BY returns the rows in some undefined order. If you use LIMIT 3,4 without ORDER BY, you get four rows out of an unordered set, so it's virtually identical to LIMIT 4. As long as the original poster doesn't say what ordering he wants, there's no way to tell him a solution. -- Marco Paulo Neves MySQL Core Certified Linux Certified Professional http://themage.bliker.com -- 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.308 / Virus Database: 266.11.6 - Release Date: 06/05/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.8 - Release Date: 10/05/2005 -- 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: Strange Error in MySQL
Hello. Yes, there are. In fact, other records have more data for this field in them and inserts for those records never threw an error. - Asad On Tue, 10 May 2005, Jay Blanchard wrote: [snip] Hello. I am receiving the following error when trying to insert into a field of type text: #1030 - Got error 139 from storage engine The data to be inserted should fit easily into a text field. In fact, this error only occurs for a particular record and other records with much larger data sets are not throwing the same error. Has anyone experienced something similar? I checked via Google but did not find any resources that helped. Any help would be greatly appreciated. Thanks. [/snip] The row is too big. Are there other columns into which a large amount of data are being placed? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Strange Error in MySQL
[snip] Hello. Yes, there are. In fact, other records have more data for this field in them and inserts for those records never threw an error. [/snip] Is the data for the other fields larger than usual? This field may be smaller, but the cumulative row length (the combined size of every field) is what is causing the error. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Row Numbers?
In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: Hi, Have you forgotten what's a primary key ? Using order by will sort data, and if it's already sorted, it will be sorted again. Time, memory and maybe disk io. If MySQL really does that, I'd consider this a bug. Using the marco example, i gaved a solution considering iy's what he wants. Till now i don't know if it's ok or not. if so, just add : select * from temp order by Id LIMIT 3,4; if no, the primary key index will give you the order. I dunno what you're talking about, but definitely not MySQL 4.1.11: CREATE TEMPORARY TABLE tbl1 ( id INT UNSIGNED NOT NULL, val INT UNSIGNED, PRIMARY KEY (id), UNIQUE KEY (val) ); INSERT INTO tbl1 (id, val) VALUES (1, 1); INSERT INTO tbl1 (id, val) VALUES (2, 2); INSERT INTO tbl1 (id, val) VALUES (3, 3); INSERT INTO tbl1 (id, val) VALUES (4, 4); SELECT * FROM tbl1; DELETE FROM tbl1 WHERE id = 3; INSERT INTO tbl1 (id, val) VALUES (5, 5); SELECT * FROM tbl1; The first SELECT happens to return 1/2/3/4, but the second one returns for me 1/2/5/4. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Row Numbers?
news [EMAIL PROTECTED] wrote on 10/05/2005 15:13:49: In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: Hi, Have you forgotten what's a primary key ? Using order by will sort data, and if it's already sorted, it willbe sorted again. Time, memory and maybe disk io. If MySQL really does that, I'd consider this a bug. I agree. MySQL knows if the search order implicitly delivers the data in the requested order, and skips the sort phase if so. Using the marco example, i gaved a solution considering iy's what he wants. Till now i don't know if it's ok or not. if so, just add : select * from temp order by Id LIMIT 3,4; if no, the primary key index will give you the order. I dunno what you're talking about, but definitely not MySQL 4.1.11: CREATE TEMPORARY TABLE tbl1 ( id INT UNSIGNED NOT NULL, val INT UNSIGNED, PRIMARY KEY (id), UNIQUE KEY (val) ); INSERT INTO tbl1 (id, val) VALUES (1, 1); INSERT INTO tbl1 (id, val) VALUES (2, 2); INSERT INTO tbl1 (id, val) VALUES (3, 3); INSERT INTO tbl1 (id, val) VALUES (4, 4); SELECT * FROM tbl1; DELETE FROM tbl1 WHERE id = 3; INSERT INTO tbl1 (id, val) VALUES (5, 5); SELECT * FROM tbl1; The first SELECT happens to return 1/2/3/4, but the second one returns for me 1/2/5/4. InnoDB would probably do this, but MyISAM probably woudl not. If it chooses to do a fill table scan, it will deliver the results iht the essentially random order it stores them. If it uses and index, it is qitel likely to deliver them in the order of that index - which may not be the primary key. Indeed, the optimiser theoretically might use different indexes for the same query on different days, as the table cnages. It is therefore *never* safe to assume any sort of ordering unless you specify it. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Row Numbers?
In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: CREATE TEMPORARY TABLE tbl1 ( id INT UNSIGNED NOT NULL, val INT UNSIGNED, PRIMARY KEY (id), UNIQUE KEY (val) ); INSERT INTO tbl1 (id, val) VALUES (1, 1); INSERT INTO tbl1 (id, val) VALUES (2, 2); INSERT INTO tbl1 (id, val) VALUES (3, 3); INSERT INTO tbl1 (id, val) VALUES (4, 4); SELECT * FROM tbl1; DELETE FROM tbl1 WHERE id = 3; INSERT INTO tbl1 (id, val) VALUES (5, 5); SELECT * FROM tbl1; The first SELECT happens to return 1/2/3/4, but the second one returns for me 1/2/5/4. InnoDB would probably do this, but MyISAM probably woudl not. Incorrect, at least for the MySQL server I tested. If it chooses to do a fill table scan, it will deliver the results iht the essentially random order it stores them. If it uses and index, it is qitel likely to deliver them in the order of that index - which may not be the primary key. Indeed, the optimiser theoretically might use different indexes for the same query on different days, as the table cnages. Yes. I think the difference is not InnoDB vs MyISAM, but SELECT pkey vs SELECT pkey. someothercol. In the first case the result set can be built by just looking at the index (which is of course sorted), whereas the second case also needs to look at the table itself (which is unsorted). It is therefore *never* safe to assume any sort of ordering unless you specify it. That's what I wanted to emphasize. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inserting special characters
Hi, Mr. Paharenko,
Here are the results:
character_set_client | latin1
character_set_connection | latin1
character_set_database | latin1
character_set_results| latin1
character_set_server | latin1
character_set_system | utf8
character_sets_dir | C:\Archivos de
programa\MySQL\MySQL Server 4.1\share\charsets/
collation_connection | latin1_swedish_ci
collation_database | latin1_swedish_ci
collation_server | latin1_swedish_ci
Create Table: CREATE TABLE `datos` (
`id_user` tinyint(3) unsigned NOT NULL auto_increment,
`nombre` varchar(55) NOT NULL,
`dni` varchar(10) NOT NULL,
`nacimiento` date NOT NULL,
`direccion` varchar(255) NOT NULL,
`telefono` varchar(9) NOT NULL,
`desde` date NOT NULL,
`hasta` date NOT NULL,
`subcontrata` enum('No','Correos','Vanyera'),
PRIMARY KEY (`id_user`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
I have tried with the latin1_spanish_ci collation, but
the problem remains.
__
Renovamos el Correo Yahoo!: ¡250 MB GRATIS!
Nuevos servicios, más seguridad
http://correo.yahoo.es
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem compiling mysql 4.1.11 on AIX 5.1
Hi Jon, all! Jon Earle wrote: Joerg Bruehe said: Still, this seems to be a problem with the header files supplied / used by gcc. Are you sure you used the fixincludes script? Hi Joerg, I tried your suggestion as per: cd /opt/freeware/lib/gcc-lib/powerpc-ibm-aix5.1.0.0/3.3.2/ mv include inc cd install-tools export TARGET_MACHINE=AIX ./fixinc.sh /opt/freeware/lib/gcc-lib/powerpc-ibm-aix5.1.0.0/3.3.2/include As I wrote: I have not been using gcc on AIX for quite some time, I just remember that it need(s/ed) this step. From fixinc.sh, I was getting errors [[...]] When I ran MySQL's configure, modified as per a number of posts regarding openssl directives to be: ./configure \ --prefix=/usr2/tools/mysql \ --with-big-tables \ --with-low-memory \ --with-vio \ --with-openssl \ --with-openssl-includes=/usr2/tools/openssl/include \ --with-openssl-libs=/usr2/tools/openssl/lib \ --without-extra-tools \ --without-docs \ --without-bench \ --enable-local-infile I then received many errors of the form: ... checking dlfcn.h usability... no checking dlfcn.h presence... yes configure: WARNING: dlfcn.h: present but cannot be compiled configure: WARNING: dlfcn.h: check for missing prerequisite headers? configure: WARNING: dlfcn.h: see the Autoconf documentation configure: WARNING: dlfcn.h: section Present But Cannot Be Compiled configure: WARNING: dlfcn.h: proceeding with the preprocessor's result configure: WARNING: dlfcn.h: in the future, the compiler will take precedence configure: WARNING: ## -- ## configure: WARNING: ## Report this to the AC_PACKAGE_NAME lists. ## configure: WARNING: ## -- ## checking for dlfcn.h... yes ... The config.log showed [[...]] So, it appears I've made things worse. :( Any ideas where to go from here? Sorry, I have none but: Check with the AIX newsgroup, comp.unix.aix. Some years ago, when AIX was at the center of my activities, I found that to be a very helpful and active community. The error messages you publish IMO seem to be general GCC issues and not specifically related to the MySQL sources. I hope you get your issues solved, Jörg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: missing library
you need to install your distro's ncurses and ncurses-devel packages. -Eric ganesan malairaja wrote: ok i found the source file .. tarball mysql-4.1.11.tar.gz after executing the ./configure command i does some checking the it gives this error checking for termcap functions library ... configure : error :no curses / termcap library found htmlDIV DIVFONT color=#cc face=Lucida Handwriting, CursiveEMSTRONGIMG height=16 src=http://graphics.hotmail.com/emarrow_right.gif; width=16Ganesan_MalairajaIMG height=16 src=http://graphics.hotmail.com/emarrow_left.gif; width=16/STRONG/EM/FONT/DIV/DIV/html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: missing file ( msyql.sock)
Keep in mind that this file will only exist when mysqld is running. Gleb Paharenko wrote: Hello. You may create my.cnf from the examples which're shipped with MySQL distribution. MySQL will create the socket file if it doesn't exists. It's location could be specified in configuration file or in command line options. See: http://dev.mysql.com/doc/mysql/en/program-options.html ganesan malairaja [EMAIL PROTECTED] wrote: now i am missing the file mysql.sock .. i could not find it anywhere on the computer i also could not find my.cnf .. i created one my.cnf pointing it o /tem/mysql.sock now i dont have the mysql.sock file where to get it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL on AIX 5.1
Hi Jon (again), all! Jon Earle wrote: Hi folks, I need to get a version of MySQL running on AIX 5.1, but I see that the only versions available are for v5.2 and v4.3.3. Yes, currently we have build machines for these two only. There are proposals to upgrade them to run AIX 5.1 and 5.3, respectively, but this issue has not yet been decided. Also, the machine running 4.3 may not be able to generate 64 bit binaries. I tried both versions (32 and 64-bit) for 5.2 on my 5.1 box, but received this error when trying to install: To be expected: AIX provides upward compatibility only, but not downward. The C library contains version information to check this. You can run binaries generated on AIX 4.3 on 5.1, and this should work without problems. (I did not specifically check the differences between these two, but I did not get any info this causes problems. If an interface changes, IBM typically provides compatibility packages providing the old interface on the new OS.) [[...]] It is possible to get a current working version for 5.1? [[...]] Currently, we can not provide it. Regards, Jörg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Row Numbers?
Here, off the top of my head are situations in mysql where you can trust that the data is ordered in some fasion. 1. Using an order by clause on a query. 2. Using a group by the data will come out in ascending order of the column that was grouped on. 3. alter table order by has been performed and the table hasn't been modified . 4. select key from t; that uses the 'Using Index' in explain will return in the order of the key. Harald Fuchs wrote: In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: CREATE TEMPORARY TABLE tbl1 ( id INT UNSIGNED NOT NULL, val INT UNSIGNED, PRIMARY KEY (id), UNIQUE KEY (val) ); INSERT INTO tbl1 (id, val) VALUES (1, 1); INSERT INTO tbl1 (id, val) VALUES (2, 2); INSERT INTO tbl1 (id, val) VALUES (3, 3); INSERT INTO tbl1 (id, val) VALUES (4, 4); SELECT * FROM tbl1; DELETE FROM tbl1 WHERE id = 3; INSERT INTO tbl1 (id, val) VALUES (5, 5); SELECT * FROM tbl1; The first SELECT happens to return 1/2/3/4, but the second one returns for me 1/2/5/4. InnoDB would probably do this, but MyISAM probably woudl not. Incorrect, at least for the MySQL server I tested. If it chooses to do a fill table scan, it will deliver the results iht the essentially random order it stores them. If it uses and index, it is qitel likely to deliver them in the order of that index - which may not be the primary key. Indeed, the optimiser theoretically might use different indexes for the same query on different days, as the table cnages. Yes. I think the difference is not InnoDB vs MyISAM, but SELECT pkey vs SELECT pkey. someothercol. In the first case the result set can be built by just looking at the index (which is of course sorted), whereas the second case also needs to look at the table itself (which is unsorted). It is therefore *never* safe to assume any sort of ordering unless you specify it. That's what I wanted to emphasize. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Underline or minus sign ?
Back in the day '_' and '-' meant different things. In recent versions of mysql they are interchangable. Philippe Poelvoorde wrote: Gabriel PREDA wrote: I'm going to start the InnoDB engine... and I want to know if the syntax for the CNF file unified at last ? Can I use: innodb-file-per-table no instead of innodb_file_per_table ? this one (at least for 4.1.11) [This is just an example] Can I use only minus sign in the whole CNF file instead of underline ? no, i think it depends the option configured (..a bit confusing, I know) Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Row Numbers?
[EMAIL PROTECTED] wrote: hi, your need is: select * from temp LIMIT 3,4; -- 3 because you have to take the fourth and 4 because dist=3+1 That can't work as my initial query, because I don't know the location of iTempID: 4 in the query result to find the position 4, the query is : mysql select rk from(SELECT @row:[EMAIL PROTECTED] as rk,iTempID, sTemp - FROM dist) as A - WHERE iTempID=4; +--+ | rk | +--+ |4 | +--+ 1 row in set (0.00 sec) I can see how that gets me the position of my row, but I also need rows adjacent to it. I think I'll probably end up putting the results of my query into a temp table, complete with row numbers, then run my query for that. Or maybe a derived table. Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Field property question!
Can I setup a table so that no matter how data is entered into it (web form, command line) The data in one of the columns ALWAYS gets converted to uppercase? I remeber MSSQL had this feature of being able to apply a function to a field in its configuration. Thanks! Matt
Re: SELECT Row Numbers?
Harald Fuchs wrote: In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: hi, your need is: select * from temp LIMIT 3,4; -- 3 because you have to take the fourth and 4 because dist=3+1 This does not make sense. A SELECT without an ORDER BY returns the rows in some undefined order. If you use LIMIT 3,4 without ORDER BY, you get four rows out of an unordered set, so it's virtually identical to LIMIT 4. As long as the original poster doesn't say what ordering he wants, there's no way to tell him a solution. Actually I do have an ORDER BY column in my real query, I trimmed it out for the sake of brevity. This column is a unique integer, but there are gaps of between the numbers, so I can't do a purely numerical solution. It must be based on what order the rows were returned from the query as far as I can tell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Row Numbers?
Chris [EMAIL PROTECTED] wrote on 05/10/2005 12:20:57 PM: Harald Fuchs wrote: In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: hi, your need is: select * from temp LIMIT 3,4; -- 3 because you have to take the fourth and 4 because dist=3+1 This does not make sense. A SELECT without an ORDER BY returns the rows in some undefined order. If you use LIMIT 3,4 without ORDER BY, you get four rows out of an unordered set, so it's virtually identical to LIMIT 4. As long as the original poster doesn't say what ordering he wants, there's no way to tell him a solution. Actually I do have an ORDER BY column in my real query, I trimmed it out for the sake of brevity. This column is a unique integer, but there are gaps of between the numbers, so I can't do a purely numerical solution. It must be based on what order the rows were returned from the query as far as I can tell. So do you need just the 4 or 5 records _after_ a target ID or are you looking to bracket the target id (show me the record where ID=4 and the two records before and after it)? If you are only worried about ID+few following records you can say SELECT field list FROM table_name WHERE ID=target value ORDER BY ID LIMIT 5 That would give you your ID record and the 5 before immediately after. For your target ID + 2 records on either side you could say ( SELECT ID, field list FROM table_name WHERE ID=target value ORDER BY ID LIMIT 3 ) UNION ( SELECT ID, field list FROM table_name WHERE ID target value ORDER BY ID desc LIMIT 2 ) ORDER BY ID; Doing it this way, you don't need to know the position of a row because everything is based off of the row's id. It may not be as fast as some other ways but since your ID value is unique (I hope it's your primary key) then it should be indexed and these queries will be just about as fast as it gets. As everyone has stressed to the point of frustration, the concept of position only has meaning in an ordered set of results and only for the moment in time that the results were created. In the few tenths of a second it would take you to query a table, find a record, notice it's position, then requery a table based on that position, a few dozen records could have been added or deleted making your position-based query inaccurate. Trying to prevent that by locking the table would just make everything else come to a grinding halt until you had found the records you were looking for. Make your queries based on the PK value of the table you are dealing with. That way records can come and go as they please and your positional arithmetic will never be wrong. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Field property question!
[snip]
Can I setup a table so that no matter how data is entered into it (web
form,
command line) The data in one of the columns ALWAYS gets converted to
uppercase? I remeber MSSQL had this feature of being able to apply a
function to a field in its configuration.
[/snip]
The manual, it is amazing no?
http://dev.mysql.com/doc/mysql/en/string-functions.html
UPPER()
INSERT INTO `table` (`colFoo`)
VALUES (UPPER('myData'));
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Row Numbers?
[EMAIL PROTECTED] wrote: Chris [EMAIL PROTECTED] wrote on 05/10/2005 12:20:57 PM: Harald Fuchs wrote: In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: hi, your need is: select * from temp LIMIT 3,4; -- 3 because you have to take the fourth and 4 because dist=3+1 This does not make sense. A SELECT without an ORDER BY returns the rows in some undefined order. If you use LIMIT 3,4 without ORDER BY, you get four rows out of an unordered set, so it's virtually identical to LIMIT 4. As long as the original poster doesn't say what ordering he wants, there's no way to tell him a solution. Actually I do have an ORDER BY column in my real query, I trimmed it out for the sake of brevity. This column is a unique integer, but there are gaps of between the numbers, so I can't do a purely numerical solution. It must be based on what order the rows were returned from the query as far as I can tell. So do you need just the 4 or 5 records _after_ a target ID or are you looking to bracket the target id (show me the record where ID=4 and the two records before and after it)? In some instances I will need the X records *after* , and in others I'll need the X records *before*, but never both. The target row will always be in the result set, and either be first or last. If you are only worried about ID+few following records you can say SELECT field list FROM table_name WHERE ID=target value ORDER BY ID LIMIT 5 That would give you your ID record and the 5 before immediately after. For your target ID + 2 records on either side you could say ( SELECT ID, field list FROM table_name WHERE ID=target value ORDER BY ID LIMIT 3 ) UNION ( SELECT ID, field list FROM table_name WHERE ID target value ORDER BY ID desc LIMIT 2 ) ORDER BY ID; Doing it this way, you don't need to know the position of a row because everything is based off of the row's id. It may not be as fast as some other ways but since your ID value is unique (I hope it's your primary key) then it should be indexed and these queries will be just about as fast as it gets. I have a Primary Key (duh), but it's not the number I'm ordering by. I misspoke in my previous email. The order column is supposed to be unique, but due to my need to change the column numbers around at times I can't define it as unique. Despite that this query above definitely appears to be the sort of thing I need. Only difference is that , since I don't need both sides, I'll only need to run either the first of the two queries, or the last (while keeping the UNION ORDER BY to reorder them) As everyone has stressed to the point of frustration, the concept of position only has meaning in an ordered set of results and only for the moment in time that the results were created. In the few tenths of a second it would take you to query a table, find a record, notice it's position, then requery a table based on that position, a few dozen records could have been added or deleted making your position-based query inaccurate. Trying to prevent that by locking the table would just make everything else come to a grinding halt until you had found the records you were looking for. Yeah, I really don't want to do any table locking, I'm doing my best just to get it all in one query. Make your queries based on the PK value of the table you are dealing with. That way records can come and go as they please and your positional arithmetic will never be wrong. That's whjat I'm trying to do. At this moment I'm wishing 4.1.x had Stored procedures, they would make my life a bit easier I think. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Thanks, I appreciate it. Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Solution to slow queries
Hello, I am working on a database that deals with network statistics. I have a program that generates web reports based on this data every ten minutes. The table layout looks something like this: CREATE TABLE traffic ( unix_secs INT UNSIGNED NOT NULL, dpkts INT UNSIGNED NOT NULL DEFAULT 0, doctets INT UNSIGNED NOT NULL DEFAULT 0, first INT UNSIGNED, last INT UNSIGNED, srcaddr VARCHAR(15), dstaddr VARCHAR(15), srcport SMALLINT UNSIGNED, dstport SMALLINT UNSIGNED, prot TINYINT UNSIGNED NOT NULL DEFAULT 0, tos TINYINT UNSIGNED NOT NULL DEFAULT 0, tcp_flags TINYINT UNSIGNED NOT NULL DEFAULT 0, INDEX unix_secs (unix_secs), INDEX srcaddr (srcaddr), INDEX dstaddr (dstaddr), INDEX srcport (srcport), INDEX dstport (dstport) ); Now, as time progresses the queires are getting slower and slower. I know this is expected, so I am curious as to how I can have a main table that has all traffic, so that I can do monthly/yearly reports, and also have a daily table so that I can quickly do reports every minute or so on that data. I have read up a bit on merge tables (this is probably the answer) but I am unsure as to how you trigger the changes. ie, how do you do the rollover after every 24hours? Any thoughts, or a pointer in the right direction would be greatly appreciated. Thanks. -- _ Paul Halliday http://dp.penix.org Diplomacy is the art of saying Nice doggie! till you can find a rock. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Solution to slow queries
At 02:22 PM 5/10/05, Paul Halliday wrote: Now, as time progresses the queires are getting slower and slower. I know this is expected, I don't think so. I thought that if the number of rows returned does not change and an index is properly used, then query time should not change significantly as size of database grows. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Solution to slow queries
Don't forget to run an analyze to adjust the statistics for the optimizer/indexes. Also, after any updates (on dynamic tables which yours is) or any deletes run an optimize. Quoting Paul Halliday [EMAIL PROTECTED]: Hello, I am working on a database that deals with network statistics. I have a program that generates web reports based on this data every ten minutes. The table layout looks something like this: CREATE TABLE traffic ( unix_secs INT UNSIGNED NOT NULL, dpkts INT UNSIGNED NOT NULL DEFAULT 0, doctets INT UNSIGNED NOT NULL DEFAULT 0, first INT UNSIGNED, last INT UNSIGNED, srcaddr VARCHAR(15), dstaddr VARCHAR(15), srcport SMALLINT UNSIGNED, dstport SMALLINT UNSIGNED, prot TINYINT UNSIGNED NOT NULL DEFAULT 0, tos TINYINT UNSIGNED NOT NULL DEFAULT 0, tcp_flags TINYINT UNSIGNED NOT NULL DEFAULT 0, INDEX unix_secs (unix_secs), INDEX srcaddr (srcaddr), INDEX dstaddr (dstaddr), INDEX srcport (srcport), INDEX dstport (dstport) ); Now, as time progresses the queires are getting slower and slower. I know this is expected, so I am curious as to how I can have a main table that has all traffic, so that I can do monthly/yearly reports, and also have a daily table so that I can quickly do reports every minute or so on that data. I have read up a bit on merge tables (this is probably the answer) but I am unsure as to how you trigger the changes. ie, how do you do the rollover after every 24hours? Any thoughts, or a pointer in the right direction would be greatly appreciated. Thanks. -- _ Paul Halliday http://dp.penix.org Diplomacy is the art of saying Nice doggie! till you can find a rock. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] James Nobis Web Developer Academic Superstore 223 W. Anderson Ln. Suite A110, Austin, TX 78752 Voice: (512) 450-1199 x453 Fax: (512) 450-0263 http://www.academicsuperstore.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Solution to slow queries
On Tue, 2005-05-10 at 14:56 -0400, Frank Bax wrote: At 02:22 PM 5/10/05, Paul Halliday wrote: Now, as time progresses the queires are getting slower and slower. I know this is expected, I don't think so. I thought that if the number of rows returned does not change and an index is properly used, then query time should not change significantly as size of database grows. True, for the appropriate definition of 'significantly'. Also false, for the appropriate definition of 'significantly'. The index's are trees which must be searched, this is fairly fast and the time doesn't grow linearly or anything like that, but the time does of course grow with more rows. So if the number of rows increases greatly then a noticable increase in the time to search the index may occur. John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Solution to slow queries
I'm somewhat a newbee on this database but some observations: As your table grows (and indexes) INSERTS will definitly slow because of the indexes. Consider MySQL's version of Oracle's partitioning and using MERGE TABLES feature. Just remember that if you change 1 table, all of them have to be rebuilt the same way. Consider compressing the tables (Making them READ ONLY) with the MyISAM engine. The updating table (current one) would not be available since it could not be compressed. Paul Halliday [EMAIL PROTECTED] wrote: Hello, I am working on a database that deals with network statistics. I have a program that generates web reports based on this data every ten minutes. The table layout looks something like this: CREATE TABLE traffic ( unix_secs INT UNSIGNED NOT NULL, dpkts INT UNSIGNED NOT NULL DEFAULT 0, doctets INT UNSIGNED NOT NULL DEFAULT 0, first INT UNSIGNED, last INT UNSIGNED, srcaddr VARCHAR(15), dstaddr VARCHAR(15), srcport SMALLINT UNSIGNED, dstport SMALLINT UNSIGNED, prot TINYINT UNSIGNED NOT NULL DEFAULT 0, tos TINYINT UNSIGNED NOT NULL DEFAULT 0, tcp_flags TINYINT UNSIGNED NOT NULL DEFAULT 0, INDEX unix_secs (unix_secs), INDEX srcaddr (srcaddr), INDEX dstaddr (dstaddr), INDEX srcport (srcport), INDEX dstport (dstport) ); Now, as time progresses the queires are getting slower and slower. I know this is expected, so I am curious as to how I can have a main table that has all traffic, so that I can do monthly/yearly reports, and also have a daily table so that I can quickly do reports every minute or so on that data. I have read up a bit on merge tables (this is probably the answer) but I am unsure as to how you trigger the changes. ie, how do you do the rollover after every 24hours? Any thoughts, or a pointer in the right direction would be greatly appreciated. Thanks. -- _ Paul Halliday http://dp.penix.org Diplomacy is the art of saying Nice doggie! till you can find a rock. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Switch to Netscape Internet Service. As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register Netscape. Just the Net You Need. New! Netscape Toolbar for Internet Explorer Search from anywhere on the Web and block those annoying pop-ups. Download now at http://channels.netscape.com/ns/search/install.jsp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Solution to slow queries
Hi, you have to play with explain to see which index is used in your queries. Since you defined only mono-column indexes, i think they are not used in queries with multi-criteria search. Consider adding indexes with all used columns and eventually drop the not used ones to not slow updates and inserts. merge (Myisam) tables can help you to partition the data on relevant keys used in the queries. But i'm not sure it's certainly good because you then loose the innodb row locking which is better in your situation. you can also consider archiving of old (and not used) data. Finally, you can prepare agregation tables every day (or hour) for example if you can consider a gap of data in the results. Mathias Selon Paul Halliday [EMAIL PROTECTED]: Hello, I am working on a database that deals with network statistics. I have a program that generates web reports based on this data every ten minutes. The table layout looks something like this: CREATE TABLE traffic ( unix_secs INT UNSIGNED NOT NULL, dpkts INT UNSIGNED NOT NULL DEFAULT 0, doctets INT UNSIGNED NOT NULL DEFAULT 0, first INT UNSIGNED, last INT UNSIGNED, srcaddr VARCHAR(15), dstaddr VARCHAR(15), srcport SMALLINT UNSIGNED, dstport SMALLINT UNSIGNED, prot TINYINT UNSIGNED NOT NULL DEFAULT 0, tos TINYINT UNSIGNED NOT NULL DEFAULT 0, tcp_flags TINYINT UNSIGNED NOT NULL DEFAULT 0, INDEX unix_secs (unix_secs), INDEX srcaddr (srcaddr), INDEX dstaddr (dstaddr), INDEX srcport (srcport), INDEX dstport (dstport) ); Now, as time progresses the queires are getting slower and slower. I know this is expected, so I am curious as to how I can have a main table that has all traffic, so that I can do monthly/yearly reports, and also have a daily table so that I can quickly do reports every minute or so on that data. I have read up a bit on merge tables (this is probably the answer) but I am unsure as to how you trigger the changes. ie, how do you do the rollover after every 24hours? Any thoughts, or a pointer in the right direction would be greatly appreciated. Thanks. -- _ Paul Halliday http://dp.penix.org Diplomacy is the art of saying Nice doggie! till you can find a rock. -- 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: Opteron HOWTO?!
Excellent, I'll be waiting to see performance numbers, specifically for FreeBSD vs. Linux. Save for a few odd machines, we're pretty much pure FreeBSD and the last releases in the 4 branch are really impressive as far as speed and stability. That being said, the Opteron would have to offer a pretty decent performance gain for us to consider switching. At the moment, our software layout is such that we have not had a need to take advantage of more than 2G memory. Feel free to woo me with your performance results. ;) In the not so distant future I will have to get an Opteron box so I can see for myself.. Atle - Flying Crocodile Inc, Unix Systems Administrator On Mon, 9 May 2005, Kevin Burton wrote: Great! I created a wiki node for this issue. http://hashmysql.org/index.php?title=Opteron_HOWTO -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Solution to slow queries
We did something similar for our large statistic tables. The older data that no longer changes would get shipped off into a very fast read only table with a cron job and then that is the table we would generate the reports on. Even with millions of entries it is incredibly fast. Eric Jensen [EMAIL PROTECTED] wrote: Consider compressing the tables (Making them READ ONLY) with the MyISAM engine. The updating table (current one) would not be available since it could not be compressed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Opteron HOWTO?!
On 5/9/05, Kevin Burton wrote: So... it sounds like a lot of people here (Dathan and Greg) have had problems deploying MySQL on Opteron in a production environment. To me it sounds more like a lot of people have had problems running Linux on x86-64 systems. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Comparing to null queries
Hello, I have a query: select * from table where del != 1; Let's assume that I have a record where del is null (del is a single character field). In version 3.23.22-beta I get the record returned with the above query, in version 4.1.10a I get nothing returned. Did something change between these versions or is this a bug? I couldn't find anything specific to this on the archives. TIA -- Mike Rykowski NU-IT Telecommunications and Network Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing to null queries
Hi,
'l' is neither equal to null nor different from null.
you can try select ('l'!=NULL) or select ('l'=NULL).
in 4.1.x you should write :
select * from table where del != l' or del is null;
mysql select * from tbl;
+--+
| del |
+--+
| NULL |
| a|
| b|
| l|
| m|
| l|
+--+
6 rows in set (0.02 sec)
mysql
mysql
mysql select * from tbl where del !='l';
+--+
| del |
+--+
| a|
| b|
| m|
+--+
3 rows in set (0.00 sec)
mysql select * from tbl where del != 'l' or del is null;
+--+
| del |
+--+
| NULL |
| a|
| b|
| m|
+--+
4 rows in set (0.00 sec)
Mathias
Selon Mike Rykowski [EMAIL PROTECTED]:
Hello,
I have a query:
select * from table where del != 1;
Let's assume that I have a record where del is null (del is a single
character field).
In version 3.23.22-beta I get the record returned with the above query,
in version 4.1.10a I get nothing returned.
Did something change between these versions or is this a bug? I
couldn't find anything specific to this on the archives.
TIA
--
Mike Rykowski
NU-IT Telecommunications and Network Services
--
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: Comparing to null queries
Mike Rykowski wrote: Hello, I have a query: select * from table where del != 1; Let's assume that I have a record where del is null (del is a single character field). In version 3.23.22-beta I get the record returned with the above query, in version 4.1.10a I get nothing returned. Did something change between these versions or is this a bug? I couldn't find anything specific to this on the archives. TIA You should get nothing returned. NULL is not a value, so it cannot be equal, *or not equal*, to anything. The result of NULL != 1 is NULL. NULL is not TRUE, so no rows where del is NULL should be returned. If 3.23.222-beta returned such rows, it was a bug in that ancient version which has since been fixed. If you want rows where del is null, you need to SELECT * FROM table WHERE del IS NULL; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing to null queries
Mike, If 3.23.22 gave (NULL != 1) = TRUE, that was a bug, because in SQL, (NULL != 1) is NULL. This 3.23.26 change history item might be your culprit: "Fixed `' to work properly with `NULL'." PB - Mike Rykowski wrote: Hello, I have a query: select * from table where del != "1"; Let's assume that I have a record where del is null (del is a single character field). In version 3.23.22-beta I get the record returned with the above query, in version 4.1.10a I get nothing returned. Did something change between these versions or is this a bug? I couldn't find anything specific to this on the archives. TIA No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.8 - Release Date: 5/10/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Solution to slow queries
Paul Halliday wrote: srcaddr VARCHAR(15), dstaddr VARCHAR(15), Are these ip-adresses? If they are, consider using UNSIGNED INT columns and the INET_NTOA() and INET_ATON() funtions. It will save you a lot of space, thus increase the amount of data your hw can handle. I have read up a bit on merge tables (this is probably the answer) but I am unsure as to how you trigger the changes. ie, how do you do the rollover after every 24hours? You would have to program this yourself, there are no mechanisms for this in MySQL. It's pretty straight forward, though. Just use a shell script and cron or the equivalent if you are on a non-unix platform. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Field property question!
I think matt is thinking more automagically like having upper() called
on a column on insert for him instead of putting it into every query.
You can't do this yet. sorry.
-Eric
Jay Blanchard wrote:
[snip]
Can I setup a table so that no matter how data is entered into it (web
form,
command line) The data in one of the columns ALWAYS gets converted to
uppercase? I remeber MSSQL had this feature of being able to apply a
function to a field in its configuration.
[/snip]
The manual, it is amazing no?
http://dev.mysql.com/doc/mysql/en/string-functions.html
UPPER()
INSERT INTO `table` (`colFoo`)
VALUES (UPPER('myData'));
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Field property question!
[snip] I think matt is thinking more automagically like having upper() called on a column on insert for him instead of putting it into every query. You can't do this yet. sorry. [/snip] Aha, I see. Needs a trigger or stored procedure. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Opteron HOWTO?!
While you're at it, take a look at Gentoo Linux (http://www.gentoo.org). I've been having very good luck with it on everything from a Duron 1GHz to Opterons. Very responsive. It compiled a kernel on an opteron in about 5 minutes. Curtis Atle Veka wrote: Excellent, I'll be waiting to see performance numbers, specifically for FreeBSD vs. Linux. Save for a few odd machines, we're pretty much pure FreeBSD and the last releases in the 4 branch are really impressive as far as speed and stability. That being said, the Opteron would have to offer a pretty decent performance gain for us to consider switching. At the moment, our software layout is such that we have not had a need to take advantage of more than 2G memory. Feel free to woo me with your performance results. ;) In the not so distant future I will have to get an Opteron box so I can see for myself.. Atle - Flying Crocodile Inc, Unix Systems Administrator On Mon, 9 May 2005, Kevin Burton wrote: Great! I created a wiki node for this issue. http://hashmysql.org/index.php?title=Opteron_HOWTO
RE: Opteron HOWTO?!
Subject: Re: Opteron HOWTO?! On 5/9/05, Kevin Burton wrote: So... it sounds like a lot of people here (Dathan and Greg) have had problems deploying MySQL on Opteron in a production environment. To me it sounds more like a lot of people have had problems running Linux on x86-64 systems. We don't have any problems running Opterons at all. With all the tests me and my team have done, we know the ins and outs of getting Opterons up, running-stable, and blazing fast. Our entire datacenter are (about 200 servers) Dual Opterons with at least 4GB of memory running in 64-bit mode. All the databases (about 30) - Are dual opterons with 8 GB of memory connected to a Hitachi 9980 SAN-through a McData Switch. We do about 70K qps at peak for about 1 Billion Queries per day (only on 30 servers BOOYA). So, it's pretty stable. -- Dathan V Pattishall Sr. Database Engineer / Sr. Software Engineer Friendster Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to put tables on a different drive???
I have a big whopping problem.g I have a large database that generates 25gb tables (let's call them GenTable1 to GenTableN). I'd like to put these tables on another drive because I'm running out of disk space. If I need to create another database (let's call it dbGen), so be it. (But it would be nice if it could exist in the same MySQL database, but that doesn't look possible. It appears MySQL forces all the tables to be under the same directory.) How can I get MySQL 4.10 to create the generated tables on another hard drive? The largest hard drive I can get is 320-400gb and that may not be enough for both my normal tables and generated tables. (I'm using Windows XP - NTFS) I need to reference the generated tables and normal tables in a join so it has to be done using 1 MySQL server. So I'd like my normal tables to be on one drive, and my generated tables to be on another drive. Does anyone have any ideas on how to put the GenTables on another drive? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Solution to slow queries
On 5/10/05, Roger Baklund [EMAIL PROTECTED] wrote: Paul Halliday wrote: srcaddr VARCHAR(15), dstaddr VARCHAR(15), Are these ip-adresses? If they are, consider using UNSIGNED INT columns and the INET_NTOA() and INET_ATON() funtions. It will save you a lot of space, thus increase the amount of data your hw can handle. They are indeed ip addresses. This infomation is gathered and input into the db via a program called flow-export (export netflows). I intially had the column as UNSIGNED INT but it would only pick up the first octet, so I switched to VARCHAR. I have read up a bit on merge tables (this is probably the answer) but I am unsure as to how you trigger the changes. ie, how do you do the rollover after every 24hours? You would have to program this yourself, there are no mechanisms for this in MySQL. It's pretty straight forward, though. Just use a shell script and cron or the equivalent if you are on a non-unix platform. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- _ Paul Halliday http://dp.penix.org Diplomacy is the art of saying Nice doggie! till you can find a rock. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Opteron HOWTO?!
Care to share any secrets? You guys are running Suse w/ 2.4 kernel yes? Any specifics as far as kernel/glibc/gcc versions. Are you running mysql 4.1.*? Are you using NPTL? You using the binary from mysql, or building yourself? Are you running Innodb or Myisam. You mentioned reiserfs correct? Any problems w/ ext3? Sorry to bombard you w/ questions, but we have had nothing but horrible performance using Opterons, and any specifics you can give would help to clear up this mess. I know that I am not the only person who is seeing this flakyiness. Thanks, Greg Dathan Pattishall wrote: Subject: Re: Opteron HOWTO?! On 5/9/05, Kevin Burton wrote: So... it sounds like a lot of people here (Dathan and Greg) have had problems deploying MySQL on Opteron in a production environment. To me it sounds more like a lot of people have had problems running Linux on x86-64 systems. We don't have any problems running Opterons at all. With all the tests me and my team have done, we know the ins and outs of getting Opterons up, running-stable, and blazing fast. Our entire datacenter are (about 200 servers) Dual Opterons with at least 4GB of memory running in 64-bit mode. All the databases (about 30) - Are dual opterons with 8 GB of memory connected to a Hitachi 9980 SAN-through a McData Switch. We do about 70K qps at peak for about 1 Billion Queries per day (only on 30 servers BOOYA). So, it's pretty stable. -- Dathan V Pattishall Sr. Database Engineer / Sr. Software Engineer Friendster Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
repetition-operator operand invalid
I've upgraded from 5.0.2 to 5.0.3 Beta, and now there is a glitch in one of my regular expression queries. The expression works like this: a query for 'search' returns true for a matching 'search' field, but, querying 's$$#e%ar^c)(h' must also return true for a 'search' field. In other words, I need to pad every letter of the search string and tell it to allow any number of non-alphanumeric characters. Here is the expression I'm using below, for the term SEARCH: '[^a-zA-Z0-9]*S[^a-zA-Z0-9]*E[^a-zA-Z0-9]*A[^a-zA-Z0-9]*R[^a-zA-Z0-9]*C[^a-zA-Z0-9]*H[^a-zA-Z0-9]*' As I said, it worked fine until I upgraded. Is it to do with the double parsing MySQL does with REGEXP's? In some instances, I get emtpy result set where I used to get a match, and in other instances, I get a 'repetition-operator operand invalid'. Is there a simple way I can prevent errors from a search string that contains ^*$ or other sensitive expression characters? addslashes() in php maybe? Any help is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to put tables on a different drive???
When you create the tables, you can specify the DATA DIRECTORY and INDEX DIRECTORY clause while creating the table to specify different paths. Not sure if it works on Windows though. Should be a simple enough test -- Partha Dutta, Senior Consultant MySQL Inc, NY, USA, www.mysql.com Are you MySQL certified? www.mysql.com/certification -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 10, 2005 5:58 PM To: mySQL list Subject: How to put tables on a different drive??? I have a big whopping problem.g I have a large database that generates 25gb tables (let's call them GenTable1 to GenTableN). I'd like to put these tables on another drive because I'm running out of disk space. If I need to create another database (let's call it dbGen), so be it. (But it would be nice if it could exist in the same MySQL database, but that doesn't look possible. It appears MySQL forces all the tables to be under the same directory.) How can I get MySQL 4.10 to create the generated tables on another hard drive? The largest hard drive I can get is 320-400gb and that may not be enough for both my normal tables and generated tables. (I'm using Windows XP - NTFS) I need to reference the generated tables and normal tables in a join so it has to be done using 1 MySQL server. So I'd like my normal tables to be on one drive, and my generated tables to be on another drive. Does anyone have any ideas on how to put the GenTables on another drive? TIA Mike -- 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: Opteron HOWTO?!
-Original Message- From: Greg Whalin [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 10, 2005 3:12 PM To: Dathan Pattishall Cc: Jochem van Dieten; mysql@lists.mysql.com Subject: Re: Opteron HOWTO?! Care to share any secrets? You guys are running Suse w/ 2.4 kernel yes? Yes. We run RedHat with a Suse Kernel and pure Suse. Any specifics as far as kernel/glibc/gcc versions. Kernel - 2.4.21-215-default #5 SMP Glibc - rpm -qa |grep glib glibc-profile-2.3.2-95.6 glibc-2.3.2-95.20 glibc-headers-2.3.2-95.6 glib2-2.2.3-2.0 glibc-kernheaders-2.4-8.34 glibc-devel-2.3.2-95.6 glibc-common-2.3.2-95.20 glibc-devel-2.3.2-95.6 glib-1.2.10-11.1 glibc-utils-2.3.2-95.6 glib-1.2.10-11.1 glibc-2.3.2-95.6 Gcc - gcc3.3 Are you running mysql 4.1.*? Yes Are you using NPTL? No that sucks we use the other one. Can't make a static build with NPTL. You using the binary from mysql, or building yourself? I build it myself using gcc3.3 - 3.4 will crash mysql using -O of any level. Are you running Innodb or Myisam. Both You mentioned reiserfs correct? Any problems w/ ext3? You can't use O_DIRECT on ext3 and 2.4 there is a bug in EXT3 when used under heavy load the volume will lock. Thanks, Greg Dathan Pattishall wrote: Subject: Re: Opteron HOWTO?! On 5/9/05, Kevin Burton wrote: So... it sounds like a lot of people here (Dathan and Greg) have had problems deploying MySQL on Opteron in a production environment. To me it sounds more like a lot of people have had problems running Linux on x86-64 systems. We don't have any problems running Opterons at all. With all the tests me and my team have done, we know the ins and outs of getting Opterons up, running-stable, and blazing fast. Our entire datacenter are (about 200 servers) Dual Opterons with at least 4GB of memory running in 64-bit mode. All the databases (about 30) - Are dual opterons with 8 GB of memory connected to a Hitachi 9980 SAN-through a McData Switch. We do about 70K qps at peak for about 1 Billion Queries per day (only on 30 servers BOOYA). So, it's pretty stable. -- Dathan V Pattishall Sr. Database Engineer / Sr. Software Engineer Friendster Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to put tables on a different drive???
Hi, You can create a symbolic link on windows for one specific database. i sent a thread about this. the doc is here : http://dev.mysql.com/doc/mysql/en/windows-symbolic-links.html Mathias Selon Partha Dutta [EMAIL PROTECTED]: When you create the tables, you can specify the DATA DIRECTORY and INDEX DIRECTORY clause while creating the table to specify different paths. Not sure if it works on Windows though. Should be a simple enough test -- Partha Dutta, Senior Consultant MySQL Inc, NY, USA, www.mysql.com Are you MySQL certified? www.mysql.com/certification -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 10, 2005 5:58 PM To: mySQL list Subject: How to put tables on a different drive??? I have a big whopping problem.g I have a large database that generates 25gb tables (let's call them GenTable1 to GenTableN). I'd like to put these tables on another drive because I'm running out of disk space. If I need to create another database (let's call it dbGen), so be it. (But it would be nice if it could exist in the same MySQL database, but that doesn't look possible. It appears MySQL forces all the tables to be under the same directory.) How can I get MySQL 4.10 to create the generated tables on another hard drive? The largest hard drive I can get is 320-400gb and that may not be enough for both my normal tables and generated tables. (I'm using Windows XP - NTFS) I need to reference the generated tables and normal tables in a join so it has to be done using 1 MySQL server. So I'd like my normal tables to be on one drive, and my generated tables to be on another drive. Does anyone have any ideas on how to put the GenTables on another drive? TIA Mike -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Opteron HOWTO?!
Greg Whalin wrote: Care to share any secrets? You guys are running Suse w/ 2.4 kernel yes? Any specifics as far as kernel/glibc/gcc versions. Are you running mysql 4.1.*? Are you using NPTL? You using the binary from mysql, or building yourself? Are you running Innodb or Myisam. You mentioned reiserfs correct? Any problems w/ ext3? Sorry to bombard you w/ questions, but we have had nothing but horrible performance using Opterons, and any specifics you can give would help to clear up this mess. I know that I am not the only person who is seeing this flakyiness. Thanks, Greg # emerge --info Portage 2.0.51-r15 (default-linux/amd64/2004.3, gcc-3.4.3-hardenednossp, glibc-2.3.4.20050125-r0, 2.6.11-rc2-mm1 x86_64) = System uname: 2.6.11-rc2-mm1 x86_64 AMD Opteron(tm) Processor 246 Gentoo Base System version 1.6.9 ... sys-devel/autoconf: 2.59-r6, 2.13 sys-devel/automake: 1.7.9-r1, 1.8.5-r3, 1.5, 1.4_p6, 1.6.3, 1.9.4 sys-devel/binutils: 2.15.92.0.2-r4 sys-devel/libtool: 1.5.10-r5 virtual/os-headers: 2.6.8.1-r3 ... CFLAGS=-Os -march=opteron -mtune=opteron CHOST=x86_64-pc-linux-gnu ... CXXFLAGS=-Os -march=opteron -mtune=opteron ... USE=nptl nptlonly # mount | grep DB /dev/sda5 on /DB type reiserfs (rw,noatime,notail) # mysqld --version mysqld Ver 4.1.10-log for pc-linux-gnu on x86_64 (Still Not g.o Linux mysql-4.1.10) Regards, Francesco Riosa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
1 table or 2?
I am trying to set up a chem structures table. column 1 is the key, column 2 is the description, column 3 is the structure which is a blob. In PHP the first 2 columns comes out as text as expected, but the 3rd I am trying to tell PHP please ignore this initially since this is a blob. Should I be using 1 or 2 tables? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql random function strangeness
I have an ORDER statement like: ORDER BY playlist.order_id ASC,RAND(playlistItems.playlist_order) I am trying to get a random result very time the query is run ... Unfortunately, this statement gives the SAME random result each time how can I get mysql to randomly jumble the playlist items for a reasonably different result each time ? many thanks to a great list -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
missing file ( msyql.sock)
is it possible a firewall is denying mysql to create the mysql.sock file if not where i can get this file i cannot find it in my entire harddrive i am stuck at for days now a clear guideline will help i tried reading at the mysql.com but no help .. i am new to linux.. anyone who had this experience and solve it please reply i am running on suse 9.3 and using mysql-4.1.11 ( source file ) htmlDIV DIVFONT color=#cc face=Lucida Handwriting, CursiveEMSTRONGIMG height=16 src=http://graphics.hotmail.com/emarrow_right.gif; width=16Ganesan_MalairajaIMG height=16 src=http://graphics.hotmail.com/emarrow_left.gif; width=16/STRONG/EM/FONT/DIV/DIV/html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: missing file ( msyql.sock)
At 2:27 + 5/11/05, ganesan malairaja wrote: is it possible a firewall is denying mysql to create the mysql.sock file if not where i can get this file i cannot find it in my entire harddrive It's created by the server when you start the server. It won't exist until then. i am stuck at for days now a clear guideline will help i tried reading at the mysql.com but no help .. i am new to linux.. anyone who had this experience and solve it please reply i am running on suse 9.3 and using mysql-4.1.11 ( source file ) -- 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: missing file ( msyql.sock)
the socket file is created in the spot specified in /etc/mysql/my.cnf. In my case its: socket = /var/run/mysqld/mysqld.sock as always ymmv. Curtis ganesan malairaja wrote: is it possible a firewall is denying mysql to create the mysql.sock file if not where i can get this file i cannot find it in my entire harddrive i am stuck at for days now a clear guideline will help i tried reading at the mysql.com but no help .. i am new to linux.. anyone who had this experience and solve it please reply i am running on suse 9.3 and using mysql-4.1.11 ( source file ) htmlDIV DIVFONT color=#cc face=Lucida Handwriting, CursiveEMSTRONGIMG height=16 src=http://graphics.hotmail.com/emarrow_right.gif; width=16Ganesan_MalairajaIMG height=16 src=http://graphics.hotmail.com/emarrow_left.gif; width=16/STRONG/EM/FONT/DIV/DIV/html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
possible join
Getting a little stuck on this one: Table defs below: I have two tables, fedex_zones contains zip code to zone data, so for example, zip 94947 is in zone 8 select zone from fedex_zones where zip = '94947' 8 Now, in the defex_rates table is how, based on weight, I can look up how much it will cost to ship. Say the weight is 12. select z_8 from fedex_rates where weight = 8 In part, my trouble is that I need to take the resuling zone from the first select and use that to determine the field name. I can easily do this in my code in 2 selects, but was hoping to be able to get the price back in just one select, if possible. mysql describe fedex_rates; ++---+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | weight | int(11) | | | 0 || | z_2| decimal(10,2) | | | 0.00|| | z_3| decimal(10,2) | | | 0.00|| | z_4| decimal(10,2) | | | 0.00|| | z_5| decimal(10,2) | | | 0.00|| | z_6| decimal(10,2) | | | 0.00|| | z_7| decimal(10,2) | | | 0.00|| | z_8| decimal(10,2) | | | 0.00|| | z_9| decimal(10,2) | | | 0.00|| | z_10 | decimal(10,2) | | | 0.00|| | z_14 | decimal(10,2) | | | 0.00|| | z_17 | decimal(10,2) | | | 0.00|| | z_51 | decimal(10,2) | | | 0.00|| | z_54 | decimal(10,2) | | | 0.00|| | z_92 | decimal(10,2) | | | 0.00|| | z_96 | decimal(10,2) | | | 0.00|| | z_22 | decimal(10,2) | | | 0.00|| | z_23 | decimal(10,2) | | | 0.00|| | z_25 | decimal(10,2) | | | 0.00|| ++---+--+-+-++ mysql describe fedex_zones; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | id| int(11) | | PRI | NULL| auto_increment | | zip | char(5) | | UNI | || | zone | char(2) | | | || +---+-+--+-+-++ -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com 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: possible join
Scott, ...In part, my trouble is that I need to take the resuling zone from the first select and use that to determine the field name. I can easily do this in my code in 2 selects, but was hoping to be able to get the price back in just one select, if possible... If you have control over the data model, it would be good to change the structure of fedex_rates to (id int PK, zone int, weight int, price decimal(10,2)), getting rid of the denormalised z_* columns which are causing you problems. Then a one-stage query would just be SELECT price FROM fedex_rates WHERE zone=8 AND weight=12. If you're stuck with the table structure you show, you're stuck with two queries. If these lookup tables aren't large, there's probably not much performance to be gained from hiding the two stages inside a stored procedure, but if you want a one-step, IMO that's the way to go. PB - Scott Haneda wrote: Getting a little stuck on this one: Table defs below: I have two tables, fedex_zones contains zip code to zone data, so for example, zip 94947 is in zone 8 select zone from fedex_zones where zip = '94947' 8 Now, in the defex_rates table is how, based on weight, I can look up how much it will cost to ship. Say the weight is 12. select z_8 from fedex_rates where weight = 8 In part, my trouble is that I need to take the resuling zone from the first select and use that to determine the field name. I can easily do this in my code in 2 selects, but was hoping to be able to get the price back in just one select, if possible. mysql describe fedex_rates; ++---+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | weight | int(11) | | | 0 || | z_2| decimal(10,2) | | | 0.00|| | z_3| decimal(10,2) | | | 0.00|| | z_4| decimal(10,2) | | | 0.00|| | z_5| decimal(10,2) | | | 0.00|| | z_6| decimal(10,2) | | | 0.00|| | z_7| decimal(10,2) | | | 0.00|| | z_8| decimal(10,2) | | | 0.00|| | z_9| decimal(10,2) | | | 0.00|| | z_10 | decimal(10,2) | | | 0.00|| | z_14 | decimal(10,2) | | | 0.00|| | z_17 | decimal(10,2) | | | 0.00|| | z_51 | decimal(10,2) | | | 0.00|| | z_54 | decimal(10,2) | | | 0.00|| | z_92 | decimal(10,2) | | | 0.00|| | z_96 | decimal(10,2) | | | 0.00|| | z_22 | decimal(10,2) | | | 0.00|| | z_23 | decimal(10,2) | | | 0.00|| | z_25 | decimal(10,2) | | | 0.00|| ++---+--+-+-++ mysql describe fedex_zones; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | id| int(11) | | PRI | NULL| auto_increment | | zip | char(5) | | UNI | || | zone | char(2) | | | || +---+-+--+-+-++ No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.8 - Release Date: 5/10/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql random function strangeness
got it to work thanks On May 10, 2005, at 7:22 PM, Graham Anderson wrote: I have an ORDER statement like: ORDER BY playlist.order_id ASC,RAND(playlistItems.playlist_order) I am trying to get a random result very time the query is run ... Unfortunately, this statement gives the SAME random result each time how can I get mysql to randomly jumble the playlist items for a reasonably different result each time ? many thanks to a great list -- 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: possible join
on 5/10/05 8:29 PM, Peter Brawley at [EMAIL PROTECTED] wrote: Scott, ...In part, my trouble is that I need to take the resuling zone from the first select and use that to determine the field name. I can easily do this in my code in 2 selects, but was hoping to be able to get the price back in just one select, if possible... If you have control over the data model, it would be good to change the structure of fedex_rates to (id int PK, zone int, weight int, price decimal(10,2)), getting rid of the denormalised z_* columns which are causing you problems. Then a one-stage query would just be SELECT price FROM fedex_rates WHERE zone=8 AND weight=12. If you're stuck with the table structure you show, you're stuck with two queries. If these lookup tables aren't large, there's probably not much performance to be gained from hiding the two stages inside a stored procedure, but if you want a one-step, IMO that's the way to go. Ok, I changed the tables around a little, I can not really do this all in one table, since the data gets made new often by fedex, at any rate, (no pun intended :-))... mysql describe fedex_zones; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | id| int(11) | | PRI | NULL| auto_increment | | zip | char(5) | | UNI | || | zone | char(2) | | | || +---+-+--+-+-++ mysql describe fedex_rates; ++---+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | weight | int(11) | | | 0 || | zone | int(11) | | | 0 || | price | decimal(10,2) | | | 0.00|| ++---+--+-+-++ so first, I need to get the zone I am in, which is a: SELECT zone from fedex_zones where zip = 94947 8 If the result in that case is 8, then I can select price from fedex_rates where zone = '8' and weight = '25' For some reason, this join is still not screaming out at me, or maybe I have it right, and my data is in duplication, any help is appreciated. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com 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]
