Re: mysqldump specific tables from multiple databases?
Hello. is it possible to mysqldump specific tables from multiple databases in a single run? No. database. I can not (even off hours) lock the entire database (main one) long enough to do a full dump with locks so I see my options as: You may write your own sql file in which you're locking the tables which you need, selecting the data into outfiles and then unlocking the tables. Sid Lane [EMAIL PROTECTED] wrote: all, is it possible to mysqldump specific tables from multiple databases in a single run? what I am trying to do is get replication slaves to a starting point but am somewhat challenged by the nature of our architecture. specifically, we have a large number of relatively-static (updated only a few times/yr w/plenty of advance notice) tables which are relatively big (~50GB among them) and a small number of tables that are relatively small (~1GB among them) but are updated continuously (several million DMLs/day). what I had done w/a single DB was dump/load the static tables to a new slave then dump/load the dynamic ones w/a --master-data which took ~1 min (acceptable off hours). that got me to a point I could do a change master... then slave start and be in business. I am now trying to merge in a small, secondary application (only ~50K DMLs/day) which is currently in a separate database. I can not (even off hours) lock the entire database (main one) long enough to do a full dump with locks so I see my options as: 1. hope someone here knows how to do my original question 2. get architecture to sign off on consolidating tables into a single database (in progress). 3. hope someone here know an approach I hadn't even thought of (paradigm shifting w/o a clutch). thoughts? -- 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: field varchar and char truncate the ended blank characters
Hello. Use TEXT or BLOB columns. AESYS S.p.A. [Enzo Arlati] [EMAIL PROTECTED] wrote: I got this problem: I need to save in a varchar field a string wich can have the ended part filled with blank character, and I need to restore the string in the same way it was saved, ie. with the right number of ended blank characters. Mysql truncate tha last blank characaters of a field for example if I have a table like the one belowe and insert some field with blank characters at the end , like abc or middle , when I read thei field I get abc and middle. create table pippo ( codice int, messaggio varchar(20), a char(20)); insert into pippo values( 1, abc , 123 ); insert into pippo values( 2, middle , MIDDLE ); select codice, messaggio, length(messaggio), a, length(a) from pippo; 1 abc 3 123 3 2middle8 MIDDLE 7 There are some way to avoid the automatic deletion of the last blank characters ? Regards, Enzo Arlati [EMAIL PROTECTED] AESYS Via Artigiani, 41 24060 Brusaporto (BG) - Italy Tel. +39 (0)35.2924.182 Fax +39 (0)35 680030 -- 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: two-way replication
Hello. See: http://dev.mysql.com/doc/mysql/en/replication-faq.html Chris Knipe [EMAIL PROTECTED] wrote: Hi, Is two-way replication possible with MySQL 5.x? Any good sites / docs describing this type of setup? -- 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: Strange Issues
Hello. Use set NAMES 'utf8'; to set the connection character sets to the corresponding values. See: http://dev.mysql.com/doc/mysql/en/charset-connection.html Rob Cochrane [EMAIL PROTECTED] wrote: Hi Gleb, Ok here is the command line info which also answers the version question. Enter password: ** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.9-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show variables like '%char%'; +--++ | Variable_name| Value | +--++ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | utf8 | | character_set_results| latin1 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | C:\share\charsets/ | +--++ 7 rows in set (0.72 sec) mysql I am downloading v.10 right now. I believed we were using .10 but in verifying I have found not. I will re-test when the upgrade is installed. Many thanks Rob Gleb Paharenko wrote: At first, we should check that there is nothing wrong with the character_set_xxx variables. Please send us the output of the following statement: show variables like '%char%'; Does the problem remain if you are making the query using a mysql command line client? Do you use the latest release (4.1.10 now)? -- 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: copy DB data from FreeBSD to Redhat9.0
Hello. I used the following command backup database: mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST --quote-names --opt I don't think this command backup user account info. If you add --all-databases option it would also dump the mysql database. I've tried to import the mysql database from the 5.0.2 to 4.1.10. It works fine, You may try just to restore the dump file with mysql dump_file.sql command. Don't forget to execute FLUSH PRIVILEGES. sam wun [EMAIL PROTECTED] wrote: Gleb Paharenko wrote: Hello. I also want to copy the user accounts in MySQL5.0 db over to MySQL4.1 in Redhat as well. All user accounts and rights are stored in the tables of mysql database. And fields of grant tables in 4.1.x version just a subset of fields in 5.0.x. You should set the values of fields in 4.1.x version to values of corresponding fields in 5.0.x. For example you may store 5.0.x mysql database under different name in 4.1.x and then move accounts using SQL statements. Hi, thanks for your help. I just able to backup all the database (with their tables I believe) from mysql 5.0. And now I stuck at restoring the database and their tables as well as user accounts rights to mysql4.1. I used the following command backup database: mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST --quote-names --opt I don't think this command backup user account info. Thanks Sam -- 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]
innodb.status.#### files
Hello All I have an MySQL instance running that is generating the innodb.status. files. However, there is no innodb_monitor table in any of the databases. How is this happening? Thanks - Gabe 4.0.20-max-log SunOS 5.8 There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange Issues
Hello Gleb, Below is an extract of the SQLyog history on startup /*[02:26:25 PM][ 0 ms]*/ show variables like '%character%' /*[02:26:25 PM][ 0 ms]*/ Set character_set_connection=utf8 /*[02:26:25 PM][ 0 ms]*/ Set character_set_results=utf8 /*[02:26:25 PM][ 0 ms]*/ Set character_set_client=utf8 /*[02:26:25 PM][ 0 ms]*/ show databases I then tested again without any change this even shows up in a varchar field where I am trying to put in the Afrikaans word visuële (first e alt 0235) and it truncates before the e so I get visu and then nothing. show variables like '%char%'; reports as follows:- mysql show variables like '%char%'; +--++ | Variable_name| Value | +--++ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_results| utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | C:\share\charsets/ | +--++ 7 rows in set (0.01 sec) mysql Yet the data is still lost. I do appologise for taking so much of your time but this is becomming highly frustrating as the site I am building is English Afrikaans German and Spanish and I call all data for that language based on the language chosen by the user or default to the language I detect from a lookup of his IP address. Many thanks Rob Gleb Paharenko wrote: Hello. Use set NAMES 'utf8'; to set the connection character sets to the corresponding values. See: http://dev.mysql.com/doc/mysql/en/charset-connection.html Rob Cochrane [EMAIL PROTECTED] wrote: Hi Gleb, Ok here is the command line info which also answers the version question. Enter password: ** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.9-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show variables like '%char%'; +--++ | Variable_name| Value | +--++ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | utf8 | | character_set_results| latin1 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | C:\share\charsets/ | +--++ 7 rows in set (0.72 sec) mysql I am downloading v.10 right now. I believed we were using .10 but in verifying I have found not. I will re-test when the upgrade is installed. Many thanks Rob Gleb Paharenko wrote: At first, we should check that there is nothing wrong with the character_set_xxx variables. Please send us the output of the following statement: show variables like '%char%'; Does the problem remain if you are making the query using a mysql command line client? Do you use the latest release (4.1.10 now)? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
In article [EMAIL PROTECTED], Jim McAtee [EMAIL PROTECTED] writes: In another table like this with month/year fields I once created a dummy date field that I populated with the date of the first of the month (1/month/year), just to facilitate queries like this. Is there another approach? Can I somehow create that dummy field on the fly within the query itself? Sure, something like WHERE concat(yr, '-01-01') + INTERVAL (mn-1) MONTH BETWEEN '2002-01-01' AND '2002-03-01' but you would save yourself that trouble if you'd use DATE with some arbitrary day. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Version 5.0.3
Any idea 5.0.3 release date? There are many bugs fixed right now for 5.0.2! Alejandro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
R: field varchar and char truncate the ended blank characters
Using TEXT or BLOB prevent me to display the string in a dbgrid, the real data is replaced by a text like (MEMO). I can use somethinh like select substring( msg, 1,100 ) from mytable to bypass the problem but is no the clean solution I looking for. I thank for your hints but I'm looking, if possible, for a different solution. Other ideas ? -Messaggio originale- Da: Gleb Paharenko [mailto:[EMAIL PROTECTED] Inviato: lunedì 28 febbraio 2005 14.41 A: mysql@lists.mysql.com Oggetto: Re: field varchar and char truncate the ended blank characters Hello. Use TEXT or BLOB columns.AESYS S.p.A. [Enzo Arlati] [EMAIL PROTECTED] wrote:I got this problem: I need to save in a varchar field a string wich can have the ended part filled with blank character, and I need to restore the string in the same way it was saved, ie. with the right number of ended blank characters. Mysql truncate tha last blank characaters of a fieldfor example if I have a table like the one belowe and insert some field with blank characters at the end , like abc or middle , when I read thei field I get abc and middle.create table pippo ( codice int, messaggio varchar(20), a char(20));insert into pippo values( 1, abc , 123 ); insert into pippo values( 2, middle , MIDDLE ); select codice, messaggio, length(messaggio), a, length(a) from pippo; 1 abc 3 123 3 2middle8 MIDDLE 7 There are some way to avoid the automatic deletion of the last blank characters ?Regards, Enzo Arlati [EMAIL PROTECTED] AESYS Via Artigiani, 41 24060 Brusaporto (BG) - Italy Tel. +39 (0)35.2924.182 Fax +39 (0)35 680030 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: field varchar and char truncate the ended blank characters
Hello, Using TEXT or BLOB prevent me to display the string in a dbgrid, the real data is replaced by a text like (MEMO). I can use somethinh like select substring( msg, 1,100 ) from mytable to bypass the problem but is no the clean solution I looking for. I thank for your hints but I'm looking, if possible, for a different solution. Other ideas ? Is this using Delphi? If so - you might want to create an OnGetText event for the field and pass (part of the) TEXT as a simple string. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problems...
Hello all, I have I small problem I wonder if there is a easy solution for.. I have a SQL-query where I count out how many users per n_id I have. it is a little tricky because I had to do a left joint and compare two tables to get out thoose that are represented only in one of the tables. SELECT SUBS.NE_ID, count(*) from SUBS left join SUB on SUBS.SC_ID=SUB.SC_ID WHERE SUB.SC_ID is NULL group by SUBS.NE_ID; The result I get out looks like this NE_ID count 01 10 02 11 03 4 04 8 And now to my problem, I would like to count this together with another table I have instead of have a seperate query and count them together manually... SELECT count(*) from SUBS_DEAC where NE_ID=01 I want to have these extra query added in my first query and get out the result as above (but now are SUBS and SUBS_DEAC counted together) I can also mention that my MySQL db is version 3.23.55. Unfortunatly is no update to newer version at the moment planed... Would be greatfull to get some help! /Joppe -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problems...
Joppe A [EMAIL PROTECTED] wrote on 03/01/2005 02:21:31 PM: Hello all, I have I small problem I wonder if there is a easy solution for.. I have a SQL-query where I count out how many users per n_id I have. it is a little tricky because I had to do a left joint and compare two tables to get out thoose that are represented only in one of the tables. SELECT SUBS.NE_ID, count(*) from SUBS left join SUB on SUBS. SC_ID=SUB.SC_ID WHERE SUB.SC_ID is NULL group by SUBS.NE_ID; The result I get out looks like this NE_ID count 01 10 02 11 03 4 04 8 And now to my problem, I would like to count this together with another table I have instead of have a seperate query and count them together manually... SELECT count(*) from SUBS_DEAC where NE_ID=01 I want to have these extra query added in my first query and get out the result as above (but now are SUBS and SUBS_DEAC counted together) I can also mention that my MySQL db is version 3.23.55. Unfortunatly is no update to newer version at the moment planed... Would be greatfull to get some help! /Joppe You will have to count(distinct ) on your parent table in order to count parents and children in the same query. Without knowing your particular table structures I will make up an example to illustrate this type of query Here is one way to track users and messages in a chat room. One table keeps track of when a user logs in and logs out. The other table tracks messages (who to, who from, what time, and the message itself) CREATE TABLE logins ( id bigint unsigned auto_increment not null primary key , user_ID varchar(10) not null , logintime datetime not null , logouttime datetime null ); CREATE TABLE messages ( id bigint unsigned auto_increment not null primary key , login_id_from bigint unsigned not null , login_id_to bigint unsigned not null , msgcontent varchar(255) not null , msgtime datetime not null ); (note: creating a set of sample data is left as an exercise for the reader) To find out how many times a user has logged in, you could write a query like this: SELECT user_id, count(id) as login_count FROM logins GROUP BY user_id; Pretty straightforward, right? Now how about how many messages are sent from each login? That would also be a simple single-table count: SELECT login_id_from, count(id) as message_count FROM messages GROUP BY login_ID; Where things get tricky is if you want to know how many times someone has logged in AND how many messages they have sent in the same query. Let's start from a simpler query that returns just the user's activity. If I were just going to pull a complete log of a user's activity, I would write a query like this: SELECT l.user_id , l.logintime , l.logouttime , if(l.id=m.login_id_from,'S','R') as send_recv , m.msgtime , m.msgcontent FROM logins l INNER JOIN messages on l.id IN (m.login_id_from, m.login_id_to) WHERE user_id = 'jsmith' ORDER BY m.msgtime; I should get a list of every message the user 'jsmith' ever sent or received, right? Assuming jsmith has logged in 3 times and sent or received 300 messages total then the following statistics will apply to the data I get back from that query # of times the name 'jsmith' appears in the results = 300 # of times each logintime or logouttime is listed = once per message sent during that login. In numerical terms, if during login #1 he sent/received 50 messages, login #2 he sent/received 150 messages, and login #3 he sent/received 100 messages, then those login and logout times will be repeated that many times in our results. (50 copies of the logintime and logouttimes for login #1, etc) Because of those repeats we can't just write a query like the one below to get a count of both logins and messages by user. Because each login is listed right along with each message created during that login, the # of values counted for both logins and messages will be identical for each user. SELECT l.user_id , count(l.id) as login_count , count(m.id) as message_count FROM logins l INNER JOIN messages m ON l.id IN (m.login_id_from, m.login_id_to) GROUP BY l.user_id; What we want to do is count how many DIFFERENT logins have occurred not just how many login values we have: SELECT l.user_id , count(DISTINCT l.id) as login_count , count(m.id) as message_count FROM logins l INNER JOIN messages m ON l.id IN (m.login_id_from, m.login_id_to) GROUP BY l.user_id; Now if we look at the line where user_id = 'jsmith', we should see the statistics of 3 logins and 300 messages which is what we expect. Hopefully you will be able to extend my example to match your original problem. You will need to use COUNT(DISTINCT ) on one of your fields but since you didn't post your table structures, I can't tell you which one. Shawn Green
Innobase Oy declares MySQL/InnoDB-4.1 stable
Innobase Oy declares MySQL/InnoDB-4.1 stable Innobase Oy has decided to declare MySQL/InnoDB-4.1 stable and recommended for all production use, starting from version 4.1.10. MySQL/InnoDB-4.1 has been out for almost two years now, there have been millions of downloads of the software, and it is already in extensive production use at many MySQL sites. That gives us the confidence to recommend it for all production use from now on. The first alpha version of 4.1 was released on April 3, 2003, and the product entered the beta phase on June 28, 2004. The last known serious problem in MySQL/InnoDB-4.1 was the bug http://bugs.mysql.com/bug.php?id=7496 in the my.cnf option innodb_file_per_table. Best regards, Heikki Tuuri Innobase Oy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Could not parse relay log event entry. error on slave
We have a master-slave setup in production. The master is running on a dual-Opteron with SuSE 8 SLES. The slave is running on a dual Xeon with SuSE 9. Both run MySQL 4.0.20 We recently moved our traffic database to the machine and started writing additional traffic (perhaps as much as 600,000 inserts/updates plus at least as many selects per day). We use Nagios to monitor the machines, and have gotten alerts that the slave is not responding (this started yesterday, which is our busiest day). This morning, the alert appeared again, but this time, there was an error in show slave status Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. I am running a mysqlbinlog on the current binary log on the slave, but it's a large file, and is still going. On the master, the binary-log-pos is 929084940. On the slave, it's way back at 889778259 Other than rebuilding the slave from a backup of the master, is there any way to get the replication backup up? David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Useful Relevance with FullText Boolean Mode - help
Hi! On Feb 26, leegold wrote: Is there any way to make relevance when using boolean mode more useful? If not, are there plans in the future Fulltext development todo for making it useful? Current relevance formula is described in internals.texi (see mysqldoc repository on mysql.bkbits.net). Plans - yes, if the current one is bad. But I don't know what to put instead. Feel free to suggest a better weighting scheme :) 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]
Parallel Mysql Was: Re: Innodb and Linux 2.6 Async I/O ??
To me what is more important is where this is leading, http://www.distlab.dk/badger/Publications/exec_summary.pdf a true parallel database cluster, with the end goal of this work being a parallel MySQL. My cluster waits .. -pete On Mon, 2005-02-28 at 14:04, Greg Whalin wrote: Just found and read this study: http://www.distlab.dk/badger/Publications/report0403.ps and was curious to see if anyone has any additional thoughts as to the contents? Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
EXPLAIN does not explain the WHERE clause
When I use the statement: EXPLAIN SELECT * FROM workunits WHERE Sent0 It returns +---+--+---++-++-++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---++-++-++ | workunits | ALL | [NULL] | [NULL] | [NULL] | [NULL] | 2297055 | where used | +---+--+---++-++-++ Which is the same as performing: EXPLAIN SELECT * FROM workunits But If I SELECT * FROM `workunits` WHERE Sent0 I Get: ++---++---++---++--++++ | wuid | ETilt | ETilt2 | MTilt | MTilt2 | STilt | STilt2 | Sent | ProcessByHost1 | ProcessByHost2 | ProcessByHost3 | ++---++---++---++--++++ | .11.6267.1 | 22 | 22.1 | 1.6 | 2 | 6 | 7.1 | 2 | 2 | 1 | 0 | | .11.6267 | 22 | 22.1 | 1.6 | 2 | 6 | 7 | 2 | 2 | 1 | 0 | | .11.6266.9 | 22 | 22.1 | 1.6 | 2 | 6 | 6.9 | 2 | 2 | 1 | 0 | | .11.6266.8 | 22 | 22.1 | 1.6 | 2 | 6 | 6.8 | 2 | 2 | 1 | 0 | | .11.6266.7 | 22 | 22.1 | 1.6 | 2 | 6 | 6.7 | 1 | 2 | 0 | 0 | | .11.6266.6 | 22 | 22.1 | 1.6 | 2 | 6 | 6.6 | 1 | 2 | 0 | 0 | ++---++---++---++--++++ Only 6 rows in set I am using MySQLd 3.23.44 If I should upgrade which is probably a good idea, what version should I get? Chris No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.5.2 - Release Date: 28/02/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table status: Create_time
Hello all, Is there a way to change a table's Create_time that is displayed when doing a `SHOW TABLE STATUS FROM mydb LIKE 'foo'\G`? I've tried using touch to modify the file dates directly and searched the online docs with no luck. The reason I want to do this is because we had some tables that were dangerously close to running out of room. The tables are in excess of 5-8 million rows and it is not possible to lock them for the time it would take to alter the MAX_ROWS and AVG_ROW_LENGTH so I renamed them to a temporary name and created a new table to be used for the duration of the alter. Once the alter statement was complete I switched the names and INSERT...SELECT from the temporary table into the newly altered table. This changed the table's Create_time which breaks a program we have that estimates how many days, months, or years we have before a table is full based on the number of rows and the Create_time. We have far too many tables to just alter them all in one sitting so we do them as needed. Thanks. -- Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Nesting Query Calls
Hi,
I am wanting to write a nesting of queries without having to
retrieve all the records of the first query (because of the volume).
pseudo code, using MySQL 5. alpha/PHP mysqli C connector
res1 = query(db, sql1, USE_DATA);
while (row1 = fetch_row(res1))
{ res2 = query(db, sql2_function_of(row1);
row2 = fetch_row(res2, USE_DATA);
free_result(res2); }
free_result(res1);
From my reading this seem to violate two principal I have used elsewhere
1) query 1 is still busy, so I can't begin a query 2 -- can I open a
new connection to get around this?
2) I didn't read all the results of query 2, so they may show up
somewhere I don't want them?
Is there a way to do this, does it work and I am missing something?
Thanks,
Don.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: EXPLAIN does not explain the WHERE clause
Christopher Malton wrote: When I use the statement: EXPLAIN SELECT * FROM workunits WHERE Sent0 It returns +---+--+---++-++-++ | table | type | possible_keys | key| key_len | ref| rows| Extra | +---+--+---++-++-++ | workunits | ALL | [NULL]| [NULL] | [NULL] | [NULL] | 2297055 | where used | +---+--+---++-++-++ Which is the same as performing: EXPLAIN SELECT * FROM workunits You have no keys, so all 2297055 rows must be read in either query. If you have only a few possible values for sent, a key may not help. But If I SELECT * FROM `workunits` WHERE Sent0 I Get: ++---++---++---++--++++ | wuid | ETilt | ETilt2 | MTilt | MTilt2 | STilt | STilt2 | Sent | ProcessByHost1 | ProcessByHost2 | ProcessByHost3 | ++---++---++---++--++++ | .11.6267.1 |22 | 22.1 | 1.6 | 2 | 6 |7.1 |2 | 2 | 1 | 0 | | .11.6267 |22 | 22.1 | 1.6 | 2 | 6 | 7 |2 | 2 | 1 | 0 | | .11.6266.9 |22 | 22.1 | 1.6 | 2 | 6 |6.9 |2 | 2 | 1 | 0 | | .11.6266.8 |22 | 22.1 | 1.6 | 2 | 6 |6.8 |2 | 2 | 1 | 0 | | .11.6266.7 |22 | 22.1 | 1.6 | 2 | 6 |6.7 |1 | 2 | 0 | 0 | | .11.6266.6 |22 | 22.1 | 1.6 | 2 | 6 |6.6 |1 | 2 | 0 | 0 | ++---++---++---++--++++ Only 6 rows in set I am using MySQLd 3.23.44 If I should upgrade which is probably a good idea, what version should I get? Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Nesting Query Calls
Don Huff [EMAIL PROTECTED] wrote on 03/01/2005 02:33:34 PM:
Hi,
I am wanting to write a nesting of queries without having to
retrieve all the records of the first query (because of the volume).
pseudo code, using MySQL 5. alpha/PHP mysqli C connector
res1 = query(db, sql1, USE_DATA);
while (row1 = fetch_row(res1))
{ res2 = query(db, sql2_function_of(row1);
row2 = fetch_row(res2, USE_DATA);
free_result(res2); }
free_result(res1);
From my reading this seem to violate two principal I have used
elsewhere
1) query 1 is still busy, so I can't begin a query 2 -- can I open a
new connection to get around this?
2) I didn't read all the results of query 2, so they may show up
somewhere I don't want them?
Is there a way to do this, does it work and I am missing something?
Thanks,
Don.
The parent-child type of query you want to make is generally accomplished
through a JOIN. However, you say you have more data than you want to deal
with at one time. Can you be more specific? What is the actual problem you
are trying to solve? The more details you can give, the better the
combined response will be.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
php-mysql install on redhat
I am attempting to use Redhat's update to get the latest version of php-mysql. Howeever, each time I try it prompts: Unresolvable chain of dependencies: MySQL-bench 4.0.21-0 requires MySQL-client Running rpm -qa | grep MySQL gives me: MySQL-client-4.0.21-0 ... and a host of other things. mysqld is also running. Should I scrap this plan, and just install from source via AB Mysql? Or is there another way? I'm wondering if my versions are incompatible with one another. Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Hangs
Hi, I've got webserver with php and mysql-4.0.22 There I've got large phpbb2. Sometimes, server's system load rapidly grows, and mysql are locked. Normally, there are 7-10 mysql processes, at this strange situation, there are 30-40. I don't know what can make such big system load in time of 1 minute. Maybe this could help: http://saepia.net/temp/mysql.txt It's 'show processlist' when mysql is locked. Mysql load is about 50 queries per second. There are mostly myisam tables and they are stored on ide100 drive with dma enabled. Or maybe it's DoS? -- m. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Inserting Other Binary data into DB (NOT IMAGES)
Title: Inserting Other Binary data into DB (NOT IMAGES) Good afternoon all, I have a db (gasp!) that is holding images in a BLOB field already. I can upload and retrieve on the fly - no problems. Now I am looking at inserting binary data such as M$Word Documents (.doc and .rtf), XML (which I have already been exposed to on this list), and PDF files. I have read somewhere that it can be done, however I have not been able to make it work with MySQL. NOTES: -- I can make it work with Acce$$ if I use the OLE Object, but that is not my goal. -- I do not want to store the resulting FSO /path/to/file structure. -- My goal is to try to insert them into the database. DB NOTES: -- MySQL 4.1.10 -- Linux RHEL 3.2 (production) -- Slave on Win2k3 also running 4.1.10 (devel) -- MyODBC 3.51.10 on Win2k3 -- ASP 3.0 with IIS and Apache/Sun One ASP Any help or insight will be greatly appreciated. Thanks! J.R. smime.p7s Description: S/MIME cryptographic signature
Charsets, collation and connections.
After doing much research I am at a loss how really utf8_bin and utf8_general_ci differ, besides the obvious -- sort order. What I inferred from MySQL docs and some online articles is the following. utf8_bin is pretty much what it suggests, utf8 strings are stored as bytes and MySQL server compares/sorts these on single byte basis, where the value of each byte determines sort order. Make sense to me as utf8 is multi-byte encoding anyway. So what is utf8_general_ci collation all about, since it is DEFAULT collation for utf8 character set. But here is the kicker, it does not implement all utf8 characters, especially for languages like jp, ko, arabic, and other right to left languages. I know for fact ucs2_general_uca (default collation for ucs2 Unicode encoding) has only partial Unicode support, so my guess is, the same may be true for utf8_general_ci collation. The only way I can get full Unicode support is to use utf8_bin collation as utf8_general_ci messes some characters? (chars ?? are returned from the db) My question is, has anyone else run into similar issues with collations? Especially, how can utf8_general_ci not support all characters but be the default collation for utf8? The only way to get things to work is to set charset to utf8 for everything, and utf8_bin collation for everything as well. (connection, db, tables) Cheers, -Daniel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Complicated Subquery help
Hiya Folks! I'm struggling with a complicated subquery issue, and my SQL-fu isn't strong enough to make a solution apparent. Hopefully one of you experts can give me a nudge in the right direction :) The query utilizes data from just one table, which looks like: idX integer idY tinyint unsigned, idZ tinyint, c1 smallint, c2 smallint, c3 smallint, ... [and so on] ... What I'd like to do is select a set of the channel data (the c* fields) based on rows where the standard deviation on a given channel is below a certain threshold for the rows near that one, based on the values of the id fields. Getting the standard deviation on a single field for a given row isn't too bad: select std(c2330) from radiances where idZ between 44 and 46 and idX between 12 and 14, and idX = 7; That query selects the standard deviation for channel 2330, in the 9 footprints around the current point I'm looking at. Selecting the channels I need to analyze based on that should just using that select inside an IF statement. The trick is, how to automate this to iterate over all the id* fields? idX is an auto_increment counter based on observation time, idY and idZ are tied to specific observations within a given set, and are integer counters that vary from 0-150 or so. Is there a way to do this with one (or more) sql queries, or would I be better off writing a specific program for doing the selection? Thanks for the help! ken === Diplomacy is the weapon of the Civilized Warrior - Hun, A.T. Ken Gieselman [EMAIL PROTECTED] System Administrator http://www.endlessknot.com/~kgieselm === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inserting Other Binary data into DB (NOT IMAGES)
Inserting Other Binary data into DB (NOT IMAGES)Inserting images in no different compared to inserting PDFs. What is your problem? What have you tried so far? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com Good afternoon all, I have a db (gasp!) that is holding images in a BLOB field already. I can upload and retrieve on the fly - no problems. Now I am looking at inserting binary data such as M$Word Documents (.doc and .rtf), XML (which I have already been exposed to on this list), and PDF files. I have read somewhere that it can be done, however I have not been able to make it work with MySQL. NOTES: -- I can make it work with Acce$$ if I use the OLE Object, but that is not my goal. -- I do not want to store the resulting FSO /path/to/file structure. -- My goal is to try to insert them into the database. DB NOTES: -- MySQL 4.1.10 -- Linux RHEL 3.2 (production) -- Slave on Win2k3 also running 4.1.10 (devel) -- MyODBC 3.51.10 on Win2k3 -- ASP 3.0 with IIS and Apache/Sun One ASP Any help or insight will be greatly appreciated. Thanks! J.R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Inserting Other Binary data into DB (NOT IMAGES)
Since this post, I have been able to enter a PDF file into the DB using MySQLCC / MySQL CLI and INSERT / UPDATE statements. Now my issue is the ASP / aspUpload. That is not appropriate for this list. Thanks anyways! J.R. -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 01, 2005 5:09 PM To: [EMAIL PROTECTED]; mysql Subject: Re: Inserting Other Binary data into DB (NOT IMAGES) Inserting Other Binary data into DB (NOT IMAGES)Inserting images in no different compared to inserting PDFs. What is your problem? What have you tried so far? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com Good afternoon all, I have a db (gasp!) that is holding images in a BLOB field already. I can upload and retrieve on the fly - no problems. Now I am looking at inserting binary data such as M$Word Documents (.doc and .rtf), XML (which I have already been exposed to on this list), and PDF files. I have read somewhere that it can be done, however I have not been able to make it work with MySQL. NOTES: -- I can make it work with Acce$$ if I use the OLE Object, but that is not my goal. -- I do not want to store the resulting FSO /path/to/file structure. -- My goal is to try to insert them into the database. DB NOTES: -- MySQL 4.1.10 -- Linux RHEL 3.2 (production) -- Slave on Win2k3 also running 4.1.10 (devel) -- MyODBC 3.51.10 on Win2k3 -- ASP 3.0 with IIS and Apache/Sun One ASP Any help or insight will be greatly appreciated. Thanks! J.R. smime.p7s Description: S/MIME cryptographic signature
Re: Nesting Query Calls
Shawn, Yes, I know that is true. With a framework that is properly spread out however, often the inner query does not even know that the outer one is active. The situation: list a bunch of hits from the database to be displayed in a table. 1) the query is constructed and handed to the framework to execute, as rows are returned they are given to the main application to display, however, 2) the main application decides that to properly show this record it must determine a few other things, then create the table row and return control to the framework. It wants to do this by creating a new select statement and running it. It seems this is not possible if the framework tries to use a single global connection to the daemon, yes? no? Can another connection be made? thanks, Don. --- [EMAIL PROTECTED] wrote: The parent-child type of query you want to make is generally accomplished through a JOIN. However, you say you have more data than you want to deal with at one time. Can you be more specific? What is the actual problem you are trying to solve? The more details you can give, the better the combined response will be. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Switching to InnoDB turns out dissapointing
Hi: I have switched from MyISAM tables to InnoDB, using MySQL 4.1.10 under SuSE 8.2. My application, an ERP system developed in-house, uses 70 tables, the largest one holding a little over one million rows. To assist when changing table structures, we developed a software that creates a new table for each of the 70 tables, one at a time, using the new structure, copies all of the records from the old table to the new one, drops the old one and renames the new one. Using MyISAM tables, this process takes 10 minutes using a two Xeon 2.4 Ghz server, with 4 Gb RAM and SCSI RAID 5 disks. The same system takes 2 1/2 hours using InnoDB tables with the same configuration. We have followed the guidelines for tuning the server, and still, we find this to be excessive. Can somebody point to some docs, guidelines or web sites we can consult to improve InnoDB's performance? It seems inserting many rows decreases performance significantly. Thank you and regards. -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom - www.acycdomains.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching to InnoDB turns out dissapointing
What have you actually done to 'tune' the server? How are you doing the inserts? InnoDB uses transactions. If you are doing each row as a single transaction (the default), it would probably take a lot longer. I assume you're doing your copying as a INSERT INTO $new_table SELECT * FROM $old_table. Try wrapping that in a BEGIN; INSERT INTO $new_table SELECT * FROM $old_table; COMMIT; How do you have your table space configured? Just some random thoughts.. On Tue, 1 Mar 2005 17:24:32 -0600, Alfredo Cole [EMAIL PROTECTED] wrote: Hi: I have switched from MyISAM tables to InnoDB, using MySQL 4.1.10 under SuSE 8.2. My application, an ERP system developed in-house, uses 70 tables, the largest one holding a little over one million rows. To assist when changing table structures, we developed a software that creates a new table for each of the 70 tables, one at a time, using the new structure, copies all of the records from the old table to the new one, drops the old one and renames the new one. Using MyISAM tables, this process takes 10 minutes using a two Xeon 2.4 Ghz server, with 4 Gb RAM and SCSI RAID 5 disks. The same system takes 2 1/2 hours using InnoDB tables with the same configuration. We have followed the guidelines for tuning the server, and still, we find this to be excessive. Can somebody point to some docs, guidelines or web sites we can consult to improve InnoDB's performance? It seems inserting many rows decreases performance significantly. Thank you and regards. -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom - www.acycdomains.com -- 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: Switching to InnoDB turns out dissapointing
Gary's got another point about the transactions. I'd still look to using mysqldump first if possible, if they have the ability it will be remarkably faster. Otherwise, turning transactions off before the insert, and locking the table as well, (if you haven't already done that) could prove to save you a lot of time. Scott. On Tue, 1 Mar 2005 15:32:54 -0800, Gary Richardson [EMAIL PROTECTED] wrote: What have you actually done to 'tune' the server? How are you doing the inserts? InnoDB uses transactions. If you are doing each row as a single transaction (the default), it would probably take a lot longer. I assume you're doing your copying as a INSERT INTO $new_table SELECT * FROM $old_table. Try wrapping that in a BEGIN; INSERT INTO $new_table SELECT * FROM $old_table; COMMIT; How do you have your table space configured? Just some random thoughts.. On Tue, 1 Mar 2005 17:24:32 -0600, Alfredo Cole [EMAIL PROTECTED] wrote: Hi: I have switched from MyISAM tables to InnoDB, using MySQL 4.1.10 under SuSE 8.2. My application, an ERP system developed in-house, uses 70 tables, the largest one holding a little over one million rows. To assist when changing table structures, we developed a software that creates a new table for each of the 70 tables, one at a time, using the new structure, copies all of the records from the old table to the new one, drops the old one and renames the new one. Using MyISAM tables, this process takes 10 minutes using a two Xeon 2.4 Ghz server, with 4 Gb RAM and SCSI RAID 5 disks. The same system takes 2 1/2 hours using InnoDB tables with the same configuration. We have followed the guidelines for tuning the server, and still, we find this to be excessive. Can somebody point to some docs, guidelines or web sites we can consult to improve InnoDB's performance? It seems inserting many rows decreases performance significantly. Thank you and regards. -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom - www.acycdomains.com -- 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: Switching to InnoDB turns out dissapointing
El Mar 01 Mar 2005 17:32, Gary Richardson escribió: What have you actually done to 'tune' the server? How are you doing the inserts? InnoDB uses transactions. If you are doing each row as a single transaction (the default), it would probably take a lot longer. I assume you're doing your copying as a INSERT INTO $new_table SELECT * FROM $old_table. Try wrapping that in a BEGIN; INSERT INTO $new_table SELECT * FROM $old_table; COMMIT; How do you have your table space configured? Just some random thoughts.. This is the InnoDB related stuff from my.cnf: innodb_data_file_path = ibdata1:10M:autoextend set-variable = innodb_buffer_pool_size=192M set-variable = innodb_additional_mem_pool_size=32M set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=32M innodb_flush_log_at_trx_commit=0 set-variable = innodb_lock_wait_timeout=50 I am using the syntax as you describe it. In my notebook, with 512M RAM, it takes 4 hours to complete. The top command says mysqld is using about 8% of CPU, so it must be a disk problem. Funny thing is, it did not show when the tables were MyISAM. Thank you and regards. -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom - www.acycdomains.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching to InnoDB turns out dissapointing
InnoDB is a very different platform from MyISAM. innodb_data_file_path = ibdata1:10M:autoextend How big is your data? You need to set your innodb_data_file_path to have enough space for this. Right now, your file is autoextending constantly. I would either turn on per table table space, or pre-create your table space. My preference is to pre-create: innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G:autoextend That will create 3 gigs of table space. If you need more than 3 gigs of space, it will autogrow the last file. Note that restart the server after this change will probably nuke your existing files. It'll also probably take awhile to start as it has to create those files. set-variable = innodb_buffer_pool_size=192M If the server is only for MySQL and primarily InnoDB, you should set this way higher. On your 512MB laptop, You'd probably want to go to 384MB if it's only for Inno. Otherwise, I'd probably set 2G - 3G for your 4GB machine. Benchmarking will help determine the right number based on your number of concurrent connections. I am using the syntax as you describe it. In my notebook, with 512M RAM, it takes 4 hours to complete. InnoDB uses a lot more disc IO, for me anyway. Overall, I see a huge concurrency jump and the increased IO cost is well worth it. The top command says mysqld is using about 8% of CPU, so it must be a disk problem. Funny thing is, it did not show when the tables were MyISAM. Try the settings above -- it'll probably make a difference.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching to InnoDB turns out dissapointing
Alfredo, - Original Message - From: Alfredo Cole [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, March 02, 2005 2:09 AM Subject: Re: Switching to InnoDB turns out dissapointing El Mar 01 Mar 2005 17:32, Gary Richardson escribi=F3: What have you actually done to 'tune' the server? How are you doing the inserts? InnoDB uses transactions. If you are doing each row as a single transaction (the default), it would probably take a lot longer. I assume you're doing your copying as a INSERT INTO $new_table SELECT * FROM $old_table. Try wrapping that in a BEGIN; INSERT INTO $new_table SELECT * FROM $old_table; COMMIT; How do you have your table space configured? Just some random thoughts.. This is the InnoDB related stuff from my.cnf: innodb_data_file_path =ibdata1:10M:autoextend set-variable =innodb_buffer_pool_size=192M the buffer pool may be a bit too small for a database of this size. Remember that tables in the InnoDB format typically take 2 to 4 x the space of a MyISAM table. The zip-compressed InnoDB table format in MySQL-5.1 will squeeze InnoDB tables to about the same size as MyISAM. set-variable =innodb_additional_mem_pool_size=32M set-variable =innodb_log_file_size=5M The log file size is too small. set-variable =innodb_log_buffer_size=32M The log buffer is too big. innodb_flush_log_at_trx_commit=0 set-variable =innodb_lock_wait_timeout=50 I am using the syntax as you describe it. In my notebook, with 512M RAM, it= takes 4 hours to complete. The top command says mysqld is using about 8% of CPU, so it must be a disk= problem. Funny thing is, it did not show when the tables were MyISAM. Thank you and regards. Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom - www.acycdomains.com 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 -- Save the Pacific Northwest Tree Octopus! | http://zapatopi.net/treeoctopus.html | -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching to InnoDB turns out dissapointing
On Tue, 1 Mar 2005 18:09:37 -0600, Alfredo Cole wrote: El Mar 01 Mar 2005 17:32, Gary Richardson escribió: InnoDB uses transactions. If you are doing each row as a single transaction (the default), it would probably take a lot longer. I assume you're doing your copying as a INSERT INTO $new_table SELECT * FROM $old_table. Try wrapping that in a BEGIN; INSERT INTO $new_table SELECT * FROM $old_table; COMMIT; This is the InnoDB related stuff from my.cnf: innodb_data_file_path = ibdata1:10M:autoextend set-variable = innodb_buffer_pool_size=192M set-variable = innodb_additional_mem_pool_size=32M set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=32M innodb_flush_log_at_trx_commit=0 set-variable = innodb_lock_wait_timeout=50 I am using the syntax as you describe it. If the transactions you are using insert thousands of records (or more) it is probably faster to leave the default value for innodb_flush_log_at_trx_commit. It is recommended anyway for data security. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Does MyODBC support SQLDescribeParam ?
Hello: I need SQLDescribeParam to get parameters' type information. But with MyODBC3.51 and MYSQL 4.1.10max I got wrong type and wrong number of parameters. Does MyODBC support SQLDescribeParam ? When will MyODBC support SQLDescribeParam ? Thanks. Regards, Gu Lei -- inline: cover.jpg-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Switching to InnoDB turns out dissapointing
set-variable =innodb_log_buffer_size=32M The log buffer is too big. Is there a performance penalty associated with making the log buffer size too large, or is just not beneficial? -JF -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unicode characters are giving me question marks
Hey guys, I just upgraded to mysql 4.1 and I'm trying to get damn unicode to work in my database. I put default-character-set=utf8 in my my.cnf file and restarted the database. Then I created a brand new database new table, new field. I try hooking into the newly created database with Mysql Control Center or MS-Access using myODBC driver. When i try to type in an extended character set in a field (either in chinese or hebrew or whatever) and close and open the table, all I see are question marks! Does anyone know what's going wrong? I am able to see Hebrew and Chinese characters fine on my computer through my web browser, why isn't it storing in the database correctly? I even did a SELECT HEX(your_column) FROM your_table; and I see the hex codes of questions marks and not the unicode characters of what I typed. - Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyODBC SQLDescribeCol can't get ColumnSize of any String types column
Hello: MyODBC3.51 MYSQL4.1.10-max MyODBC SQLDescribeCol can't get ColumnSize of any String types column. SQLDescribeCol can get ColumnSize of numeric column. Why ? Regards, Gu Lei -- inline: cover.jpg-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy DB data from FreeBSD to Redhat9.0
Gleb Paharenko wrote: Hello. I used the following command backup database: mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST --quote-names --opt I don't think this command backup user account info. If you add --all-databases option it would also dump the mysql database. I've tried to import the mysql database from the 5.0.2 to 4.1.10. It works fine, You may try just to restore the dump file with mysql dump_file.sql command. Don't forget to execute FLUSH PRIVILEGES. Hi, I got the following error when execute mysql mydata.sql: # mysql mysql_2005-03-01.Tuesday.sql ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) I have looked thru the mysqld script in /etc/init.d/ directory, but didn't get a cule about start up mysqld with unix sock. You got any idea? Thanks Sam sam wun [EMAIL PROTECTED] wrote: Gleb Paharenko wrote: Hello. I also want to copy the user accounts in MySQL5.0 db over to MySQL4.1 in Redhat as well. All user accounts and rights are stored in the tables of mysql database. And fields of grant tables in 4.1.x version just a subset of fields in 5.0.x. You should set the values of fields in 4.1.x version to values of corresponding fields in 5.0.x. For example you may store 5.0.x mysql database under different name in 4.1.x and then move accounts using SQL statements. Hi, thanks for your help. I just able to backup all the database (with their tables I believe) from mysql 5.0. And now I stuck at restoring the database and their tables as well as user accounts rights to mysql4.1. I used the following command backup database: mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST --quote-names --opt I don't think this command backup user account info. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy DB data from FreeBSD to Redhat9.0
sam wun wrote: Gleb Paharenko wrote: Hello. I used the following command backup database: mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST --quote-names --opt I don't think this command backup user account info. If you add --all-databases option it would also dump the mysql database. I've tried to import the mysql database from the 5.0.2 to 4.1.10. It works fine, You may try just to restore the dump file with mysql dump_file.sql command. Don't forget to execute FLUSH PRIVILEGES. Hi, I got the following error when execute mysql mydata.sql: # mysql mysql_2005-03-01.Tuesday.sql ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) I have looked thru the mysqld script in /etc/init.d/ directory, but didn't get a cule about start up mysqld with unix sock. You got any idea? I also search for where mysql.sock is, but I couldn't find this file in the system. have I miss something in the mysqld start up script? Thanks Sam Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy DB data from FreeBSD to Redhat9.0
Hi, I have installed the server and started up successfully. mysql.sock file is written to /var/lib/mysql/ directory as well. Now I found that I also need to install mysql 4.10-1 client. However there is already mysql3 installed in the system. How can I remove the old version in Redhat? Here are all error I got: # rpm -i MySQL-client-4.1.10-0.i386.rpm warning: MySQL-client-4.1.10-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 file /usr/bin/mysql from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 file /usr/bin/mysql_find_rows from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 file /usr/bin/mysqlaccess from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 file /usr/bin/mysqlbinlog from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 file /usr/bin/mysqlcheck from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 file /usr/bin/mysqldump from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 file /usr/bin/mysqlimport from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 file /usr/bin/mysqlshow from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 file /usr/share/man/man1/mysql.1.gz from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 file /usr/share/man/man1/mysqlaccess.1.gz from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 file /usr/share/man/man1/mysqladmin.1.gz from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 file /usr/share/man/man1/mysqldump.1.gz from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 file /usr/share/man/man1/mysqlshow.1.gz from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 Thanks Sam sam wun wrote: sam wun wrote: Gleb Paharenko wrote: Hello. I used the following command backup database: mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST --quote-names --opt I don't think this command backup user account info. If you add --all-databases option it would also dump the mysql database. I've tried to import the mysql database from the 5.0.2 to 4.1.10. It works fine, You may try just to restore the dump file with mysql dump_file.sql command. Don't forget to execute FLUSH PRIVILEGES. Hi, I got the following error when execute mysql mydata.sql: # mysql mysql_2005-03-01.Tuesday.sql ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) I have looked thru the mysqld script in /etc/init.d/ directory, but didn't get a cule about start up mysqld with unix sock. You got any idea? I also search for where mysql.sock is, but I couldn't find this file in the system. have I miss something in the mysqld start up script? Thanks Sam Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connect to MYSQL server from Wi-Fi enabled Windows CE device
On Wednesday 23 February 2005 03:47, Hough Van Wyk said something like: I am developing a embedded VB application running on a hp ipaq running Windows CE 2003. This app has to connect to a MYSQL DB over a wireless network. I have surfed the internet for hours with no luck. Can anyone please help me with this problem. What exactly is your problem? Do you have a TCP/IP connection on the WinCE device? Will the MySQL ODBC drivers run on the device (or does your programming environment have its own drivers, such as the light-weight drivers for VB)? What have your tried? If it's a standard ethernet connection, there is no difference than if you were plugged into a wall. What exactly are you looking for in your search? j- k- -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb.status.#### files
Gabriel, - Original Message - From: Tucker, Gabriel [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, March 01, 2005 2:40 PM Subject: innodb.status. files Hello All I have an MySQL instance running that is generating the = innodb.status. files. However, there is no innodb_monitor table in = any of the databases. How is this happening? 4.0.20 writes them automatically. Later MySQL versions only when you request it to do that. Those files can be useful in debugging. Thanks - Gabe 4.0.20-max-log SunOS 5.8 There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F 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 -- Save the Pacific Northwest Tree Octopus! | http://zapatopi.net/treeoctopus.html | -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching to InnoDB turns out dissapointing
Jon, - Original Message - From: Jon Frisby [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, March 02, 2005 3:32 AM Subject: RE: Switching to InnoDB turns out dissapointing set-variable =3Dinnodb_log_buffer_size=3D32M =20 The log buffer is too big. Is there a performance penalty associated with making the log buffer size too large, or is just not beneficial? it is not beneficial beyond 8 MB. In this case, the big log buffer is eating valuable RAM from the buffer pool where the memory would be more useful. -JF 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 -- Save the Pacific Northwest Tree Octopus! | http://zapatopi.net/treeoctopus.html | -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL runing very slow after restore
Hi, I finally got the backup data ported from mysql 5.0 (from freebsd) to mysql 4.1.10 in Redhat. Webmin started up and I can browse the data in each table. After installed all prerequisit perl modules, and start the web gui and start to run some queries, I expeirence it runs very slow. A sql in mysql 5.0 finsihed in few seconds, but my mysql 4.1.10 takes forever to run. I created indexes in mysql 5.0. When doing backup, I use --all-databases and restore it use command mysql mysql.sql and mysql data.sql. I don't know whether all indexes are rebuilt correctly in the new database (mysql 4.1.10). How do I know all my previous indexes are in-placed with the restore? Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Silly mistake
Hi, I admit I m silly to shutdown mysqld with the killall command in the Redhat server, I can't restart mysql service now. Most of the reason is because the script mysql.server come with the mysql 4.1.10 does not like mysql.server start or mysql.server stop, so I need to start it up use and shut it down with killall. Anyway, after killall mysql, I got the following error when I tried to restart it. Here is the error: ./mysqld Starting mysqld daemon with databases from /usr/local/mysql/data STOPPING server from pid file /usr/local/mysql/data/vivaserver.pid 050302 15:52:05 mysqld ended How can I start mysqld now? Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
