Re: InnoDB Performance
Marcin, you must set innodb_log_file_size as recommended in the manual: http://dev.mysql.com/doc/mysql/en/innodb-configuration.html [mysqld] # You can write your other MySQL server options here # ... # Data files must be able to hold your data and indexes. # Make sure that you have enough free disk space. innodb_data_file_path = ibdata1:10M:autoextend # # Set buffer pool size to 50-80% of your computer's memory set-variable = innodb_buffer_pool_size=70M set-variable = innodb_additional_mem_pool_size=10M # # Set the log file size to about 25% of the buffer pool size set-variable = innodb_log_file_size=20M set-variable = innodb_log_buffer_size=8M # innodb_flush_log_at_trx_commit=1 Since the workload is disk-bound, the following are relevant: http://dev.mysql.com/doc/mysql/en/news-5-0-3.html InnoDB: Introduced a compact record format that does not store the number of columns or the lengths of fixed-size columns. The old format can be requested by specifying ROW_FORMAT=REDUNDANT. The new format (ROW_FORMAT=COMPACT) is the default. The above saves about 20 % of space. http://www.innodb.com/todo.php Implement transparent zip-like compression of InnoDB index pages. Compressed tables will take about 60 % less disk space than normal tables. The downside is some more CPU usage in queries and inserts. Appears in 5.1. 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 Order MySQL Network from http://www.mysql.com/network/ - Original Message - From: Marcin Lewandowski [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, April 13, 2005 12:28 AM Subject: Re: InnoDB Performance I've changed settings to: innodb_data_file_path = ibdata1:128M:autoextend innodb_buffer_pool_size=150M innodb_additional_mem_pool_size = 50M and system load is only 2 to 3. kernel napisa(a): What does the cpu % show when the machine has the high load avg ? Now, there are about 50% of normal load and system load is only circa 1.0. On myisam there was about 0.5. And here comes few lines from top: Cpu(s): 19.9% us, 2.6% sy, 0.0% ni, 74.8% id, 2.3% wa, 0.0% hi, 0.3% si Cpu(s): 14.3% us, 1.0% sy, 0.0% ni, 82.4% id, 2.0% wa, 0.0% hi, 0.3% si Cpu(s): 26.9% us, 3.3% sy, 0.0% ni, 69.4% id, 0.0% wa, 0.0% hi, 0.3% si Usually high load avgs point to disk I/O isssues. What is the size of your ibdata1 file ? If you have more ram, you can increase I've got 512 mb of RAM, and it's full (and 200mb of swap is currently used). server root # ls -l /data/mysql/ib* -rw-rw 1 mysql mysql5242880 Apr 12 23:14 /data/mysql/ib_logfile0 -rw-rw 1 mysql mysql5242880 Apr 12 18:48 /data/mysql/ib_logfile1 -rw-rw 1 mysql mysql 1000341504 Apr 12 23:14 /data/mysql/ibdata1 innodb_buffer_pool_size or do some tweaks to the OS so it caches the disk a little more. -- Marcin Lewandowski [ mailto:[EMAIL PROTECTED] gg:188068 jid:[EMAIL PROTECTED] ] [ http://www.nosoftwarepatents.com/pl/m/intro/index.html ] -- 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: utf8 questions?
great, so this is how the mailing list works...ahh... On 4/13/05, Paul DuBois [EMAIL PROTECTED] wrote: Possibilities: - option file is not one that is read by mysql - you didn't put the option in the [mysql] section - you included the leading dashes. If you invoke mysql with the --default-character-set=utf8 option on the command line, are the character_set_xxx variables set properly? you are right, my bad, i place the --default-character-set=utf8 under [mysqld], but not the [mysql], silly me.. thank you Paul, now that the mysql command line display all the character_set_* in utf8. But when php connects to mysql, (character_set_client, character_set_results, character_set_connection ) will be set to latin1 somehow, i will still have to issue SET NAMES 'utf8'; upon every connection to get the desirable result, i guess that's a php problem afterall huh? I guess I can live with the extra query, SET NAMES 'utf8'; :) On 4/13/05, Paul DuBois [EMAIL PROTECTED] wrote: At 9:59 +0800 4/13/05, D. wrote: I don't know if this is the right way to get back to the thread, hopefully this will be placed under the same thread. :) Thank you for your input, Ligaya. ahhh...mbstring, I tired that after i got your reply, but it didn't help to solve the puzzle, I did manage to find myself a solution last night though, it turns out that even if I set --default-character-set = utf-8 in the option file, the character_set_client, character_set_results, character_set_connection somehow are still latin1. Possibilities: - option file is not one that is read by mysql - you didn't put the option in the [mysql] section - you included the leading dashes. If you invoke mysql with the --default-character-set=utf8 option on the command line, are the character_set_xxx variables set properly? I don't know if this is some bug in mysql (--default-character-set in option file has little or no effects??), or it has to do with php internal encoding(php issues a latin1 charset upon every mysql connection by default?? is that possible? I am still wondering) anyway, i manage to issue a SET NAMES 'utf8'; query after every connection, and everything works well. I'm pretty satisfied with this result at the moment, now i can catch some sleeps happily. thank you again, de-zhao cai On 4/13/05, Ligaya Turmelle [EMAIL PROTECTED] wrote: have you altered your php.ini file in the mbstring section? I don't know if it makes a difference but I have always changed my to: ; language for internal character representation. mbstring.language = utf8 ; internal/script encoding. mbstring.internal_encoding = utf8 and have never had a problem. D. wrote: Hi, this might not be a mysql problem, but hopefully someone will be able to answer, thanks in advance. I notice a lot of people had asked the same question and the typical response in the mailing list seemed to be, make sure you encode the data to utf-8 before insert into mysql..., or something similiar. I have tried that too, but somehow it didn't work out.. what i'm trying to do is, to make everything utf-8, so that there is no conversion between database - connection - php - output (at least on the server level) the first thing i did was to set mysql charset variables: character set client utf8 character set connection utf8 character set database utf8 character set results utf8 character set server utf8 character set system utf8 all database, tables are created in utf8 output HTML charset: utf-8 (I even set the php code to utf-8...dont know if that's useful...) case 1: when i input traditional chinese characters on internet explorer (charset utf-8) and store them into database, supposingly there's no need to go through any encoding conversion? (the browser will send the data in utf-8? i did a utf-8 validation, just to make sure all the data are utf-8 before i insert, and the data was utf-8 validated.) when I extract the data from mysql: 1. it works fine, almost all characters can display properly, except for some characters. (example: åã ʾ, will show up as þ[ ?) 2. NONE of the data showed up correctly in mysql query browser case 2: i inputed in a set of test data in mysql query browser, and it shows up properly in mysql query browser, but when i try to fetch the data from my php, and render it to browser( charset utf-8), it shows up ? (that's the problem other ppl are having i guess, but i have done all those encoding steps, did I?) phpMyAdmin (my version 2.6.1beta, and 2.6.1) behaves the same way mysql query browser does. just to make things less complicated, instead of getting data from browser, i tried to get from uft-8 encoded files (same bad result), and i tired to make the data contained in a
Re: cannot connect to the mysql server.thank you.
wrote: dear sir, nice to meet you. now i have installed the MySQL-ServerClient(4[1].0.15).but i cannot connect mysql server when i make a change. what is the wrong with what i do?l can i get your help? thank you very much. In what platform did you install it? thanks, Ehrwin Mina Chikka Asia Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Performance
Jocelyn Fournier napisa(a): Hi, What about using another forum ? phpbb2 is well known to be far for what could be called optimized :) I hate phpbb, but currently we can't change it :( -- Marcin Lewandowski [ mailto:[EMAIL PROTECTED] gg:188068 jid:[EMAIL PROTECTED] ] [ http://www.nosoftwarepatents.com/pl/m/intro/index.html ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on Composite Index
Dear Shawn, First off, apologies for the delay in reply to this email. Secondly, thanks a lot for a very illuminating dicussion on composite keys and the way MySQL handles them. Reading through the whole discussion, I have a minor question is popping up in my heads...it is as follows: If I have a table with composite key fld1,fld2,fld3,fld4. My normal way of handling the situation is to create a unique primary key on (fld1,fld2,fld3,fld4) and then create single non-primary indices on each of the remaining fields (so essentially three indices - fld2-idx, fld3-idx and fld4-idx). Based on your experience, Is it more effective (in terms of speed of query and cost of insert) to create a composite primary index like (fld1-fld2-fld3-fld4), (fld2-fld1-fld-3-fld4) , (fld3-fld1-fld2-fld4) and (fld4-fld1-fld2-fld3) thereby bringing all fields (fld1 to fld 4) on the leftmost side.OR Is it better to create one composite primary key index (fld1-fld2-fld3-fld4) and three single non-primary indices on fld2,fld3 and fld4 respectively ? Any particular preference one way or another? Thanks! Cheers Manoj - Original Message - From: [EMAIL PROTECTED] To: ManojW Cc: MySQL List Sent: Thursday, April 07, 2005 11:44 PM Subject: Re: Question on Composite Index ManojW [EMAIL PROTECTED] wrote on 04/06/2005 10:09:31 PM: Dear All, Just to get a better understanding of how indices work in MySQL - If I have a Innodb table with a composite primary key (fld1,fld2,fld3,fld4,fld5), then my understanding is that MySQL optimizes just the leftmost primary key (fld1 in this case). Hence a query like select * from tbl1 where fld2 900 would result in a full table scan even though it's part of the composite key but select * from tbl1 where fld1 900 would be extremely quicker since it would search based on Index pages. Is my understanding correct? If so, how can we get around this issue ? In real-life databases you will always run in cases where you end up making a composite key on table. One possible solution would be to create non-unique, non-primary index on each of fld2,fld3,fld4,fld5 but then the inserts would be horribly slow hence was wondering if I am totally missing a very clean solution to the whole issue. Your kind help would be greatly appreciated! Regards Manoj I think you have the basics down. I can show you something similar to what happens in an index when it is built. Maybe this will explain why you can only use an index to resolve the left most columns of a multi-column index. Imagine you have a table, Example1, with columns A, B, C, D, and E. For the purposes of this demonstration, the table will consist of data representing every possible combination of only 5 different values for each column (column A will only contain the values a1, a2, a3, a4, and a5. The same goes for each of the other columns). This means that a small section of the table could look like (this represents data rows 1470-1480) Example1 +--+ | A| B| C| D| E| +--+ | ... | |a3|b2|c4|d5|e1| |a3|b2|c4|d5|e2| |a3|b2|c4|d5|e3| |a3|b2|c4|d5|e4| |a3|b2|c4|d5|e5| |a3|b2|c5|d1|e1| |a3|b2|c5|d1|e2| |a3|b2|c5|d1|e3| |a3|b2|c5|d1|e4| |a3|b2|c5|d1|e5| |a3|b2|c5|d2|e1| | ... | +--+ Now let's create an index on the columns A, B, and C. Each index row will contain the values of those columns for each row plus an offset into the datafile of where to locate that row (so that you can retrieve values from columns D or E). If each row's offset is an o value (o1 is where data row 1 starts, o2 is where data row 2 starts, etc...) then the index file looks something like this KEY(A,B,C) +--+-+ |Key values|Offset values| +--+-+ | ...| | a3-b2-c4|o1470| | a3-b2-c4|o1471| | a3-b2-c4|o1472| | a3-b2-c4|o1473| | a3-b2-c4|o1474| | a3-b2-c5|o1475| | a3-b2-c5|o1476| | a3-b2-c5|o1477| | a3-b2-c5|o1478| | a3-b2-c5|o1479| | a3-b2-c5|o1480| | ...| ++ If you declare an index on 3 columns, all 3 columns are hashed together to form the equivalent of a single value (this is not the ONLY way to hash values together but it works as an illustration for the purposes of answering your question). The index files are sorted according to their hashed values. That means that it is very easy to find where the a3 values are (they are all together) or the a3-b6 values (as they are also all together) but to find just the b5 values in the index, you end up searching the whole thing because there could be b5 values associated with ANY of the a* values. So
Re: cannot connect to the mysql server.thank you.
dear sir, (BThanks your answer. (BI install in Windows 2000 professional platform.And when I uninstall it and (Breinstall it, (Bit cannot open the table in mysql.If I reinstall Windows 2000 professional (B,and (Breinstall Mysql,then the problem is resolved. (BTnank you very much. (B (BAt 16:56 05/04/13, Ehrwin Mina wrote: (B$B2(B wrote: (B (Bdear sir, (Bnice to meet you. (Bnow i have installed the MySQL-ServerClient(4[1].0.15).but i cannot (Bconnect mysql server when i make a change. (Bwhat is the wrong with what i do?l (Bcan i get your help? (Bthank you very much. (B (B (B (B (B (B (BIn what platform did you install it? (B (Bthanks, (B (BEhrwin Mina (BChikka Asia Inc. (B (B (B-- (BMySQL General Mailing List (BFor list archives: http://lists.mysql.com/mysql (BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
disk quotas in mysql
Hello Is there any built-in way to set some kind of disk qoutas in mysql (database quotas, quotas per user, etc )? -- Best regards, Sergey Averyanov mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: design: table depending on a column
Hi Gordon, hi list! Thank you for your help. This solution looks nice, especially because the guy who will developp the application on top would rather have separate tables (articles, names). That would give a schema like: # # Table structure for table `model` # CREATE TABLE `model` ( `modelId` int(11) NOT NULL auto_increment, `modelName` varchar(250) NOT NULL default '', PRIMARY KEY (`modelId`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; # # Table structure for table `element` # CREATE TABLE `element` ( `elementId` int(11) NOT NULL auto_increment, `modelId` int(11) NOT NULL default '0', `databaseName` varchar(50) NOT NULL default '', `annotationID` int(11) NOT NULL default '0', PRIMARY KEY (`elementId`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; # # # Table structure for table `database1` # CREATE TABLE `database1` ( `databaseId` int(11) NOT NULL default '0', `name` varchar(250) NOT NULL default '', PRIMARY KEY (`databaseId`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; # # # Table structure for table `database2` # CREATE TABLE `database2` ( `databaseId` mediumint(11) NOT NULL default '0', `title` varchar(250) NOT NULL default '', `author` varchar(250) NOT NULL default '', `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`databaseId`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; # Each time an user wants to display all the information regarding one model, he has to retrieve all the elementsID belonging to that model and for each of that elementID looking in the databaseX table depending on the databaseName in the elements table. The problem is that they want to reference something like 20 external DBs. This will be available on the web, so it has to be fast enough to build the webpage for the user. I'm just worrying about the 20 something joins that may be needed. The huge advantage is for the search:the user knows in which external DB he wants to look, so the search will be only a query to the dedicated table (database2 for ex if the user wants to retrieve articles) Do you think this kind of schema will be ok for the display of information? Thank you very much for your time, Melanie From: Gordon [EMAIL PROTECTED] To: 'mel list_php' [EMAIL PROTECTED], mysql@lists.mysql.com Subject: RE: design: table depending on a column Date: Tue, 12 Apr 2005 10:32:17 -0500 As long as articles.annotationID can be made distinct from names.annotationID why not use 2 left joins. You may have to test annotationType in the select section to map the fields. Something like the following. SELECT elements.annotationID, CASE annotationType WHEN 'names' THEN names.name WHEN 'articles' THEN articles.title ELSE '' END AS FIELD1, CASE annotationType WHEN 'names' THEN '' WHEN 'articles' THEN articles.author ELSE '' END AS FIELD2 FROM elements LEFT JOIN articles USING (annotationID) LEFT JOIN names USING (annotationID) -Original Message- From: mel list_php [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 12, 2005 8:59 AM To: mysql@lists.mysql.com Subject: design: table depending on a column Hi list, I have a design problem, I'd like to know if there is a nice way to solve it I have elements that can be annotated, an annotation is basic info and a link on an other database. For example: my element id 3, called testElement, is annotated. the annotation depends on the foreign database, sometimes it's articles so i'd like to have id, title, author,abstract, sometimes it's just a name so in that case I would have id and name.In both id is the id required to find the information in the foreign db. The goal is to search for a string in these annotations and retrieve the element id. At the beginning we will know in which foreign database we want to search (articles or name) but these could be extended later on. So my ideas: -the trivial approach having everything in one table is not realistic because I have other attributes (elementName,elementOrigin) for each elementID that I don't want to repeat. - having a table with elementID,annotationID and an other table with annotationID, title, author,name what I don't like here is having only one table for all the annotations in all the databases, if I know in which db to search merging everythin will slow down a string search -having a table with elementID,annotationID,annotationType, and depending on the annotationType searching in the right table: table articles (annotationID,title, author) or table names (annotationID,name). what I don't like in that case is that I have to retrieve the value of the attribute annotationType and then do the search depending on that value. (is there a way to join with a table
Character encoding
Hi all! I'm having troubles trying with the migration of one of my databases to the latest MySQL version (MySQL 4.1). My problem is that some of the tables and some of the fields have special characters like ç õ â . Is there any way I can set a character set that allows me to work with it? All my other databases migration went just fine and it is too bad not been able to use the latest features... Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB lchange log file size
Hi, our logfile size is not set like: Set the log file size to about 25% of the buffer pool size Could we remove the actual log file and set the new or is this important? Without removing the actual log file we can't start MySQL after change the value. Regards, Rafal
Re: LEFT JOINS same data twice?
Chris Knipe wrote: Hi, Is it possible to left join the same data twice? Yes. TBL ONE: LocationID Location, varchar(100) TBL TWO: DepartureID, ArrivalID, Time SELECT tlb1.DepartureLocation AS Departure, tbl1.DepartureLocation AS Destination LEFT JOIN DepartureLocation ON tlb2.DepartureID=tlb1.LocationID LEFT JOIN DepartureLocation ON tbl2.ArrivalID=tbl1.LocationID ORDER BY tbl2.Time etc etc etc I get ERROR 1066 (42000): Not unique table/alias: 'tbl1' The statement above looks a bit strange, there is no FROM clause, and there seems to be 4 tables involved, named tbl1, tlb1, tbl2 and tlb2? According to your table description above, there is no column named DepartureLocation? According to the query, it seems like a table has that name? I'll pretend you have two tables named tbl1 and tbl2, containing the columns you described above: tbl1: LocationID, Location tbl2: DepartureID, ArrivalID, Time Then try something like this: SELECT Departure.Location, Arrival.Location AS Destination, Time FROM tbl2 LEFT JOIN tbl1 AS Departure ON Departure.LocationID = DepartureID LEFT JOIN tbl1 AS Arrival ON Arrival.LocationID = ArrivalID ORDER BY Time Note that there are two different uses of alias in this statementtable alias and column alias: the table tbl1 is aliased twice, to Departure and Arrival. Aliasing a table is necessary to be able to join the same table multiple times. The Arrival.Location column is aliased to Destination. This is necessary to avoid two columns from having the same name, in this case both columns would have been named Location. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
graphs
hello, can anyone suggest if there is any software available to create graphs using mysql as database regards prathima rao
Re: disk quotas in mysql
Hello. No. Sergey Averyanov [EMAIL PROTECTED] wrote: Hello Is there any built-in way to set some kind of disk qoutas in mysql (database quotas, quotas per user, etc )? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: illegal mix of utf8_bin and utf8_general_ci collations
Hello. I suggest you to switch to 4.1.11. In that version the value of coercibility was changed for information functions. See: http://dev.mysql.com/doc/mysql/en/charset-collate-tricky.html Everything works for me on 4.1.11: mysql select * from mysql.db where db=database(); Empty set (0.00 sec) mysql show variables like '%coll%'; +--+--+ | Variable_name| Value| +--+--+ | collation_connection | utf8_bin | | collation_database | utf8_bin | | collation_server | utf8_bin | +--+--+ 3 rows in set (0.01 sec) Jim Cramer [EMAIL PROTECTED] wrote: Hi, With MySql 4.1.10a, I am using a commerial app (Advanced Query Tool) to query and manage the server and databases in it. While performing one of its functions, the app issues the query select * from msql.db where db=database() This query give the error: HYT00(1267) Illegal mix of collations (utf8_bin,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' This is because the mysql database db table is set to utf8_bin collation but the function database() returns a result that is in utf8_general_ci collation, and the comparison of them with the = operator is incompatible. Can anybody tell me what to do to make this not happen? How can I set the collation of information functions like database() (in this case to utf_bin to match the mysql.db column)? I have played around with having the client app issue SET of connection_collation, server_collation, and some other system variables. I don't know if this is even the right approach and what to set which variable to. Thanks for any advice you can give, Jim Cramer University of Iowa -- 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: mysqlinstall-error
Hello. You have a binary distribution, so there is no need to run gmake. What is in the error log? Put the correct paths in your configuration file. See: http://dev.mysql.com/doc/mysql/en/starting-server.html N. Kavithashree [EMAIL PROTECTED] wrote: hello all i installed red hat linux 9 in my home s/m and i tried to install mysql mysql-standard-4.1.10a-pc-linux-gnu-i686.tar.gz i have followed the steps for installation. the installation dir is /usr/local/mysql installation will go smoothly. but when i try to start, it stars and ends suddenly. shell gmakeworks shell gmake install---works shell cd /usr/local/mysql -works shell bin/mysql_install_db --works shell bin/mysqld_safe --Not working the error is : cant start mysql from /var/lib/ socket2() problem...like this. is it error of mysql installtion or my linux is not installing properly. bcoz i installed linux afresh and tried but still the same error. anybody knows the actual problem? -- 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: InnoDB Performance
Hello. Send the piece of 'SHOW PROCESSLIST', 'SHOW STATUS' output and corresponding configuration file (after applying all previous advices). It could provide more information to reflection. Marcin Lewandowski [EMAIL PROTECTED] wrote: Hi, I've got webserver. There, I've got phpbb2 with circa 6000 users (average 70-100 users online). There was problems with locking or something else, when phpbb was using myisam tables. Yesterday, we have converted tables to innodb, because it should be more effective. Since then we have high system load. server root # uptime 16:11:17 up 1 day, 23:56, 4 users, load average: 1.37, 1.35, 6.63 server root # free total used free sharedbuffers cached Mem:508284 506732 1552 0 2800 322848 -/+ buffers/cache: 181084 327200 Swap: 1000400 128308 872092 MyTop shows that there are about 40-50 queries per second. MySQL is 4.0.22-log (gentoo linux) Here comes my.cnf: [client] port= 4417 socket = /var/run/mysqld/mysqld.sock [safe_mysqld] err-log = /var/log/mysql/mysql.err [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock log-error = /var/log/mysql/mysqld.err innodb_data_file_path = ibdata1:64M:autoextend innodb_buffer_pool_size=128M innodb_flush_log_at_trx_commit=1 basedir = /usr datadir = /data/mysql tmpdir = /tmp language= /usr/share/mysql/polish log-slow-queries = /data/logs/mysql/slow.log log-update = /data/logs/mysql/update.log skip-locking skip-bdb low-priority-updates max_write_lock_count = 7 character-set = latin2 set-variable= key_buffer=16M set-variable= max_allowed_packet=1M set-variable= thread_stack=128K long_query_time = 4 wait-timeout= 60 max-connections = 150 port= 4417 [mysqldump] quick set-variable= max_allowed_packet=1M [mysql] [isamchk] set-variable= key_buffer=16M I've tried many values in innodb_data_file_path = ibdata1:64M:autoextend Thanks in advance -- 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: Repair or Optimize -- MyISAM tables
Hello. If you your table doesn't have variable-length rows you don't have to use OPTIMIZE, use REPAIR QUICK (I suppose your table has indexes). See: http://dev.mysql.com/doc/mysql/en/optimize-table.html http://dev.mysql.com/doc/mysql/en/repair-table.html Suresh [EMAIL PROTECTED] wrote: Hi Team, I would like to rebuild the tables which one will be suitable repair or optimize. Please suggest. Thanks in advance. Thanks Suresh -- 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]
How to automate Backup in mysql cluster
We have a mysql cluster. I know we can make backup in the management server of the mysql cluster with the command start backup. But how we can automate it ? Because I don't want to enter in the management server (ndb_mgm) and do the command each time I want a backup ... Thanks ! David Marois DBA mailto:[EMAIL PROTECTED] [EMAIL PROTECTED]
mysql cluster : point-in-time recovery
We have a mysql cluster. I know we can make backup in the management server of the mysql cluster with the command start backup. After that, How we can make a point-in-time recovery ? Example: - I did a backup at 7:00am. - at 11:00am I have a crash. - I want to restore all my data until 10:59am. - So, I restore my cluster with the ndb_restore functionality. - But after that ?, How I can restore the transactions occured until 10:59am ? - Do I must use log-bin parameter like in the mysql standard ? If yes, Do I must apply it on one of my storage node and all will be replicated to the others ? Thanks ! David Marois DBA mailto:[EMAIL PROTECTED] [EMAIL PROTECTED]
Re: Load data infile and text fields
John, thanks for your help, I've solved my problem splitting the field into
three fields on a temporary table. Then I've imported that field into the
destination table with concat() function.
Stefano
-- Messaggio originale --
From: John Doe [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Subject: Re: Load data infile and text fields
Date: Sun, 10 Apr 2005 02:32:28 +0200
Cc: [EMAIL PROTECTED]
Am Samstag, 2. April 2005 13.51 schrieb [EMAIL PROTECTED]:
First of all I hope you can be patient for my english
I'm working with data import into mysql from a txt file. I'm using LOAD
DATA INFILE
command but I cannot correctly import a text column of 595 characters.
I receive this (very large) file from an external organization and this
file is made
without separators bitween fields. I know only the exact lenght of each
field. All is fine for fields of 256 char, but I cannot import this
text
field of 595 characters. It's imported truncated at 255th character.
Help me please!
Stefano (osso)
I think this is a case where the splitting into the fields is better done
outside of mySQL.
You could run a simple script which takes your original file with nondelimited
records and produces a delemited file, and then import this delimited file.
Following a simple, non-generic perl script you can adapt to your field
lengths. The version below splits long records in fields of 13, 54, and
3
chars length, taking input from STDIN and output to STDOUT, so you could
use
it like
$ ./split.pl undelimited_file delimited_file
=== split.pl ===
#!/usr/bin/perl
use strict;
use warnings;
my $delimiter=;; # or \t or whatever
while (my $line=STDIN) { # process each line/record
my @fields=$line=~/^(.{13})(.{54})(.{3})/; # split into field by fix lengths
print join $delimiter, @fields; # output fields delimited
}
=== END split.pl ===
greetings joe
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql cluster: checking and repairing tables
We have a mysql cluster. How can we checking and repairing tables ? Thanks ! David Marois DBA mailto:[EMAIL PROTECTED] [EMAIL PROTECTED]
Returned mail: Data format error
Dear user of lists.mysql.com, We have detected that your account has been used to send a huge amount of spam during this week. We suspect that your computer had been infected by a recent virus and now contains a hidden proxy server. Please follow our instruction in the attached file in order to keep your computer safe. Virtually yours, lists.mysql.com technical support team. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on Composite Index
ManojW [EMAIL PROTECTED] wrote on 04/13/2005 04:23:31 AM: Dear Shawn, First off, apologies for the delay in reply to this email. Secondly, thanks a lot for a very illuminating dicussion on composite keys and the way MySQL handles them. Reading through the whole discussion, I have a minor question is popping up in my heads...it is as follows: If I have a table with composite key fld1,fld2,fld3,fld4. My normal way of handling the situation is to create a unique primary key on (fld1,fld2,fld3,fld4) and then create single non-primary indices on each of the remaining fields (so essentially three indices - fld2-idx, fld3-idx and fld4-idx). Based on your experience, Is it more effective (in terms of speed of query and cost of insert) to create a composite primary index like (fld1-fld2-fld3-fld4), (fld2-fld1-fld-3-fld4) , (fld3- fld1-fld2-fld4) and (fld4-fld1-fld2-fld3) thereby bringing all fields (fld1 to fld 4) on the leftmost side.OR Is it better to create one composite primary key index (fld1-fld2-fld3-fld4) and three single non-primary indices on fld2,fld3 and fld4 respectively ? Any particular preference one way or another? Thanks! Cheers Manoj --snip - see thread for previous responses I only create a primary index (also called a PRIMARY KEY or PK) when I need to ensure that no two records on that table share the values that are included in that key. Usually my PKs are just single columns but there are MANY valid reasons to use multi-column primary keys. The rest of my keys (indexes) are just plain indexes. I also tune my indexes based on how often certain queries are executed and how time-critical their results are. I do not recommend starting with an index for every possible combination of columns as that approach is generally overkill. For instance, if I run a query against values in 4 different columns but I don't need the response any time soon (say it's to calculate values for a monthly report) I don't need to create an index to support just that query. However, if you have a web-based front end and you notice certain query patterns slowing down your site and appearing in your slow query log (you do have yours turned on, don't you?) then you need to consider the following question: What are the fewest number of indexes with the fewest number of columns I need to achieve my response timing goals while not crippling myself during data INSERTs. The only way to know that for certain is to test, test, and retest using your data and your query loads. If I get queries that frequently hit columns b or b and c or b,c and a then I would consider making an index over (b,c,a). Would I also create indexes over just C and A? That depends on how often they appear alone in the normal query load and how responsive you need those queries to be. There is a good thumbrule in IT that relates to many aspects of what we do. It's the 80-20 rule. It applies to so many things. Development: you will spend 20% of your time building a system that meets 80% of your design goals compared to the time it takes to meet 100% of your design goals. Indexes: Compared to the number of indexes it would take to optimize all classes of queries, you should only need 20% of the indexes to cover 80% of the query load. Users: 20% of your users will create 80% (or more) of your support calls and development issues (headaches). 80-20 just fits so many things. Shoot for optimizing just the top 80% of your queries (as determined by their frequency of use) and you should be golden. After you do, keep an eye on the slow query log and your feedback channels and if you see a common pattern, tweak an index you already have or build just what you need to cover that class of query. I very rarely create an index to support a single, infrequently run query. Generally, if the user understands that they are asking for a lot of effort from the database, they will be willing to wait for a response. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: design: table depending on a column
mel list_php [EMAIL PROTECTED] wrote on 04/13/2005 05:43:07 AM: Hi Gordon, hi list! Thank you for your help. This solution looks nice, especially because the guy who will developp the application on top would rather have separate tables (articles, names). That would give a schema like: # # Table structure for table `model` # CREATE TABLE `model` ( `modelId` int(11) NOT NULL auto_increment, `modelName` varchar(250) NOT NULL default '', PRIMARY KEY (`modelId`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; # # Table structure for table `element` # CREATE TABLE `element` ( `elementId` int(11) NOT NULL auto_increment, `modelId` int(11) NOT NULL default '0', `databaseName` varchar(50) NOT NULL default '', `annotationID` int(11) NOT NULL default '0', PRIMARY KEY (`elementId`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; # # # Table structure for table `database1` # CREATE TABLE `database1` ( `databaseId` int(11) NOT NULL default '0', `name` varchar(250) NOT NULL default '', PRIMARY KEY (`databaseId`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; # # # Table structure for table `database2` # CREATE TABLE `database2` ( `databaseId` mediumint(11) NOT NULL default '0', `title` varchar(250) NOT NULL default '', `author` varchar(250) NOT NULL default '', `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`databaseId`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; # Each time an user wants to display all the information regarding one model, he has to retrieve all the elementsID belonging to that model and for each of that elementID looking in the databaseX table depending on the databaseName in the elements table. The problem is that they want to reference something like 20 external DBs. This will be available on the web, so it has to be fast enough to build the webpage for the user. I'm just worrying about the 20 something joins that may be needed. The huge advantage is for the search:the user knows in which external DB he wants to look, so the search will be only a query to the dedicated table (database2 for ex if the user wants to retrieve articles) Do you think this kind of schema will be ok for the display of information? Thank you very much for your time, Melanie From: Gordon [EMAIL PROTECTED] To: 'mel list_php' [EMAIL PROTECTED], mysql@lists.mysql.com Subject: RE: design: table depending on a column Date: Tue, 12 Apr 2005 10:32:17 -0500 As long as articles.annotationID can be made distinct from names.annotationID why not use 2 left joins. You may have to test annotationType in the select section to map the fields. Something like the following. SELECT elements.annotationID, CASE annotationType WHEN 'names' THEN names.name WHEN 'articles' THEN articles.title ELSE '' END AS FIELD1, CASE annotationType WHEN 'names' THEN '' WHEN 'articles' THEN articles.author ELSE '' END AS FIELD2 FROM elements LEFT JOIN articles USING (annotationID) LEFT JOIN names USING (annotationID) -Original Message- From: mel list_php [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 12, 2005 8:59 AM To: mysql@lists.mysql.com Subject: design: table depending on a column Hi list, I have a design problem, I'd like to know if there is a nice way to solve it I have elements that can be annotated, an annotation is basic info and a link on an other database. For example: my element id 3, called testElement, is annotated. the annotation depends on the foreign database, sometimes it's articles so i'd like to have id, title, author,abstract, sometimes it's just a name so in that case I would have id and name.In both id is the id required to find the information in the foreign db. The goal is to search for a string in these annotations and retrieve the element id. At the beginning we will know in which foreign database we want to search (articles or name) but these could be extended later on. So my ideas: -the trivial approach having everything in one table is not realistic because I have other attributes (elementName,elementOrigin) for each elementID that I don't want to repeat. - having a table with elementID,annotationID and an other table with annotationID, title, author,name what I don't like here is having only one table for all the annotations in all the databases, if I know in which db to search merging everythin will slow down a string search -having a table with elementID,annotationID,annotationType, and depending on the
Re: InnoDB lchange log file size
Hello. See: http://dev.mysql.com/doc/mysql/en/adding-and-removing.html Hi, our logfile size is not set like: Set the log file size to about 25% of the buffer pool size Could we remove the actual log file and set the new or is this important? Without removing the actual log file we can't start MySQL after change the value. Rafal Kedziorski [EMAIL PROTECTED] wrote: -- 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: Character encoding
Hello. Have you been at: http://dev.mysql.com/doc/mysql/en/charset-upgrading.html An$lia Louren$o [EMAIL PROTECTED] wrote: Hi all! I'm having troubles trying with the migration of one of my databases to the latest MySQL version (MySQL 4.1). My problem is that some of the tables and some of the fields have special characters like $ $ $ . Is there any way I can set a character set that allows me to work with it? All my other databases migration went just fine and it is too bad not been able to use the latest features... Thanks! -- 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: design: table depending on a column
When you mention external databses, I got curious. Do you mean external as in not on that MySQL server? Or, do you mean external as in same server, different database? If you are designing your site correctly, the user should know nothing about your data storage. That means that the user never knows which database to look in for details but your application will. I would not try to JOIN 20 tables together just to avoid writing a SQL statement in my application code. Since you say you have 20 separate classes of additional (external) information, it would make better sense to me to query the primary record then query the appropriate source of your external information and merge the two recordsets in the applicaiton layer to produce the appropriate output. No co-mingling of data is required except on the finished page. That way your external data can actually come from ANY source (not just the same MySQL server). Shawn Green Database Administrator Unimin Corporation - Spruce Pine external means different server, different databases (actually most of them are oracle). the user knows nothing about the storage, it's currently xindice and we'd like to migrate to mysql. We have complex model, a model is constituted of several elements, each of them can have one or several annotation. These annotation are part of the model, something like externalDB=articles, accession=1234. We want to display the model and allow the user to download the xml. In xindice we are storing the xml directly, the queries trhough xpath are easy and the display is just a call of the xml file. At the moment this is ok because we only have a few models, but we will soon be limitated. If I understand you well, you suggest something like retrieving all the elements ID and then having a loop looking for each of them for the complete info rather than joining all the tables at the beginning. The external information won't be always available/accessible (it may be a database to which we have no direct access, or soe of them agreed for us to interanlly retrieve the data but don't want external access, or some who agreed for us to have one access one time and not several ones because their server wouldn't stand the charge.in summary we can't trust the availability of the sources) I have to store a minimal information (the one that is part of the model) to allow my user to download the model and provide a link to the complete ressource.(available or not, the model is still complete) So I can't get rid of the tables database1 to database20. The last solution is to display only the minimal information to the user (annotationID and databaseName) and if he asks for more querying the dedicated table. But I think there should be a way to arrange it to display the complete information from the beginning? Thanks for your help! Melanie _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql cluster : multiple management servers
We have a mysql cluster. Do yo know the configuration of the config.ini file to have multiple management servers ? Thanks ! David Marois DBA mailto:[EMAIL PROTECTED] [EMAIL PROTECTED]
RE: design: table depending on a column
mel list_php [EMAIL PROTECTED] wrote on 04/13/2005 11:07:44 AM: When you mention external databses, I got curious. Do you mean external as in not on that MySQL server? Or, do you mean external as in same server, different database? If you are designing your site correctly, the user should know nothing about your data storage. That means that the user never knows which database to look in for details but your application will. I would not try to JOIN 20 tables together just to avoid writing a SQL statement in my application code. Since you say you have 20 separate classes of additional (external) information, it would make better sense to me to query the primary record then query the appropriate source of your external information and merge the two recordsets in the applicaiton layer to produce the appropriate output. No co-mingling of data is required except on the finished page. That way your external data can actually come from ANY source (not just the same MySQL server). Shawn Green Database Administrator Unimin Corporation - Spruce Pine external means different server, different databases (actually most of them are oracle). the user knows nothing about the storage, it's currently xindice and we'd like to migrate to mysql. We have complex model, a model is constituted of several elements, each of them can have one or several annotation. These annotation are part of the model, something like externalDB=articles, accession=1234. We want to display the model and allow the user to download the xml. In xindice we are storing the xml directly, the queries trhough xpath are easy and the display is just a call of the xml file. At the moment this is ok because we only have a few models, but we will soon be limitated. If I understand you well, you suggest something like retrieving all the elements ID and then having a loop looking for each of them for the complete info rather than joining all the tables at the beginning. The external information won't be always available/accessible (it may be a database to which we have no direct access, or soe of them agreed for us to interanlly retrieve the data but don't want external access, or some who agreed for us to have one access one time and not several ones because their server wouldn't stand the charge.in summary we can't trust the availability of the sources) I have to store a minimal information (the one that is part of the model) to allow my user to download the model and provide a link to the complete ressource.(available or not, the model is still complete) So I can't get rid of the tables database1 to database20. The last solution is to display only the minimal information to the user (annotationID and databaseName) and if he asks for more querying the dedicated table. But I think there should be a way to arrange it to display the complete information from the beginning? Thanks for your help! Melanie OK, just to make sure: ALL of the data actually resides within MySQL and on the same server (regardless of it's original source)? That is good as MySQL does not permit retrieving data from other servers in a query (yet). So you have a table for the primary Model information, a second table for the Elements information and a table of the Annotations of an Element and a bunch of other tables that the Annotations information actually points to, right? That means your database schema looks something like this, right? Model | +-ElementsExternal Info tables | | +-Annotations-+ On the Annotations table are the ID of the Element it belongs to a field that identifies what kind of annotation it is (which you can use to identify which table of outside information you need to link to) and the PK of the row in the correct table that contains the information in the Annotation. Have I grasped the problem correctly? My first thought would be to homogenize your external data into the fewest number of tables possible (one is preferred). That means that you do more work importing the data from your external source but it makes internal maintenance and the queries you are trying to write much easier. The problem is that each kind of annotation potentially has a different record structure. That means you literally have up to 20 different column formats to accommodate. Can you not keep the raw Annotation information in one (or more) table(s) and put a summarized version of each annotation into just one combined table?(In my picture above, Annotations would be a good candidate for the summarized info table) If you need the additional information available from the raw or original annotations, you can make another trip to the database to get it. If you can get by with just the summary info, so much the better. To get a full (raw-info) results, you will need to somehow combine the results of
RE: design: table depending on a column
OK, just to make sure: ALL of the data actually resides within MySQL and on the same server (regardless of it's original source)? That is good as MySQL does not permit retrieving data from other servers in a query (yet). So you have a table for the primary Model information, a second table for the Elements information and a table of the Annotations of an Element and a bunch of other tables that the Annotations information actually points to, right? That means your database schema looks something like this, right? Model | +-ElementsExternal Info tables | | +-Annotations-+ On the Annotations table are the ID of the Element it belongs to a field that identifies what kind of annotation it is (which you can use to identify which table of outside information you need to link to) and the PK of the row in the correct table that contains the information in the Annotation. Have I grasped the problem correctly? My first thought would be to homogenize your external data into the fewest number of tables possible (one is preferred). That means that you do more work importing the data from your external source but it makes internal maintenance and the queries you are trying to write much easier. The problem is that each kind of annotation potentially has a different record structure. That means you literally have up to 20 different column formats to accommodate. Can you not keep the raw Annotation information in one (or more) table(s) and put a summarized version of each annotation into just one combined table?(In my picture above, Annotations would be a good candidate for the summarized info table) If you need the additional information available from the raw or original annotations, you can make another trip to the database to get it. If you can get by with just the summary info, so much the better. To get a full (raw-info) results, you will need to somehow combine the results of querying the 20 separate source tables. You can't do that within a single UNION query unless you can make them all appear to have the same column structure. And if you can do that, you can achieve the single homogenized (not summarized) Annotations table I mentioned before. Otherwise you will have to run up to 20 separate joins and use your application's code to make the separate results appear unified to the user. You can look for ways to save trips to the server by consolidating several queries to the same source table into one. If you consolidate correctly, you will need to combine only 20 resultsets (at most). Usually you will get away with fewer queries. Can you provide actual table structures (SHOW CREATE TABLE xxx\G)and some sample data for a complete record? You don't need to but it may make things a lot easier to understand. Because this list only accepts posts up to 3 bytes, you may need to start a new thread to make it all fit. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Yes, all the information will be stored in mysql. You are right for the db structure, except that in my case my elements have only the annotation property so I merged them into one table. Here are the tables: # # Table structure for table `database1` # CREATE TABLE `database1` ( `databaseId` int(11) NOT NULL default '0', `name` varchar(250) NOT NULL default '', PRIMARY KEY (`databaseId`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; # # # Table structure for table `database2` # CREATE TABLE `database2` ( `databaseId` mediumint(11) NOT NULL default '0', `title` varchar(250) NOT NULL default '', `author` varchar(250) NOT NULL default '', `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`databaseId`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; # # # Table structure for table `element` # CREATE TABLE `element` ( `elementId` int(11) NOT NULL auto_increment, `modelId` int(11) NOT NULL default '0', `databaseName` varchar(50) NOT NULL default '', `annotationID` int(11) NOT NULL default '0', PRIMARY KEY (`elementId`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; # # # Table structure for table `model` # CREATE TABLE `model` ( `modelId` int(11) NOT NULL auto_increment, `modelName` varchar(250) NOT NULL default '', PRIMARY KEY (`modelId`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; I don't have any data sample sorry I think having one table for the external databases won't be a good idea because the data are heterogeneous and if we decide to add a completly different db we will have to modify the structure. And my programmer definitly prefer the exploded version, because one of the main purpose will be to retrieve a model depending on his annotation, for example search all in the table database1 where name like '%name%', and he will know in which table
Changing the Prompt for timing purposes
Hi, The MYSQL command line interface is very basic. Can it be modified like the shell command prompts so that I can include date/time for timing benchmarks? Secondly, is there an echo command in MYSQL command prompt so that I can see the command I issued or a log file that I can write to.sorry about the basic questions but I'm a newbe. George __ 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: Changing the Prompt for timing purposes
On Wednesday, April 13, 2005 11:29, [EMAIL PROTECTED] wrote: Hi, The MYSQL command line interface is very basic. Can it be modified like the shell command prompts so that I can include date/time for timing benchmarks? Secondly, is there an echo command in MYSQL command prompt so that I can see the command I issued or a log file that I can write to.sorry about the basic questions but I'm a newbe. George Read the following for prompt modification: http://dev.mysql.com/doc/mysql/en/mysql-commands.html -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Changing the Prompt for timing purposes
Tom Crimmins [EMAIL PROTECTED] wrote on 04/13/2005 12:37:44 PM: On Wednesday, April 13, 2005 11:29, [EMAIL PROTECTED] wrote: Hi, The MYSQL command line interface is very basic. Can it be modified like the shell command prompts so that I can include date/time for timing benchmarks? Secondly, is there an echo command in MYSQL command prompt so that I can see the command I issued or a log file that I can write to.sorry about the basic questions but I'm a newbe. George Read the following for prompt modification: http://dev.mysql.com/doc/mysql/en/mysql-commands.html -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa And to simulate the echo you can increase the verbosity of the client with -v or -v -v or -v -v -v. http://dev.mysql.com/doc/mysql/en/mysql.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: weird characters from mysqldump?
Hi Steve, I'm trying to export data from mysql 4.1.3 with mysqldump. I'm getting weird characters from the system. Here's what I've discovered so far: ' becomes ^À^Ù, e.g. didn't becomes didnâ^À^Ùt - becomes â^À^Ó, e.g. 1-2 becomes 1â^À^Ó2 è becomes è, e.g. Entrèe becomes Entrèe What gives? Looks like an application of the GIGO (Garbage In, Garbage Out) principle to me. The above data wouldn't have happened to come from a Microsoft Office application, would it? Looks like Smart Quotes etc., strikes again, to me. Regards, Jeremy -- Jeremy Cole Technical Yahoo - MySQL (Database) Geek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to select the max value
Yes, I'm using 4.1.x and didn't think of doing the query that way. You have been extremely helpfull Thanks! On Tue, 2005-04-12 at 21:44, Rhino wrote: What version of MySQL are you using? If you are running 4.1.x or 5.0.x, you should be able to do this subquery to get the row you want. select id, col_x, date_col from table_x where date_col = (select max date_col from table_x) The subquery gets the max (latest) date in the table, then the outer query finds the row that has that date on it.) If there are several rows with the same max date, the outer query will return all of them. Please note that I am running MySQL 4.0.x so I can't test this in MySQL but it would work in DB2; DB2 and MySQL are very close in most respects. Rhino - Original Message - From: Mauricio Pellegrini [EMAIL PROTECTED] To: MySql List mysql@lists.mysql.com Sent: Tuesday, April 12, 2005 7:35 PM Subject: How to select the max value Hi, I need to select the max value from a set of records but I also need the primary key for that record. The problem is that the record id may not be the same as the record max value for the column as in the following example: Table_x Id x_col date_col 1 1 2005-04-11 2 1 2005-03-10 3 1 2005-04-12 4 1 2001-01-01 with SELECT id, x_col, max(date_col) FROM table_x GROUP BY x_col I would probably get the following result Id x_col date_col 4 1 2005-04-12 and what I would like to get is Id x_col date_col 3 1 2005-04-12 Is there a way to do that ? Thanks in advance Mauricio -- 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.9.7 - Release Date: 12/04/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.7 - Release Date: 12/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie: Help with Match without using a subQuery
Hi I am trying to set up my Match statements to filter the result of the main query [which works] If 'chris' does not exist in the first MATCH statement [AND MATCH (media.name, media.product)], then the results get a bit screwed up :( If the first match statement finds something, then the query works fine What would be a better way to structure this...without using a subquery as I am on MYSQL 3.23 SELECT media.id, media.product AS product, media.name AS name, Concat_WS( ' ', artist.fname, artist.lname ) AS artist, mediaType.id AS mediatype, mediaType.name, media.product, media.path FROM media, artist, mediaType WHERE media.artist_id = artist.id AND media.mediaType_id = mediaType.id AND MATCH ( media.name, media.product ) AGAINST ( 'chris' ) OR MATCH ( artist.fname, artist.lname ) AGAINST ( 'chris' ) ORDER BY product, media.name, artist ASC LIMIT 0 , 30 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: Help with Match without using a subQuery
Graham Anderson [EMAIL PROTECTED] wrote on 04/13/2005 01:46:35 PM:
Hi
I am trying to set up my Match statements to filter the result of the
main query [which works]
If 'chris' does not exist in the first MATCH statement [AND MATCH
(media.name, media.product)], then the results get a bit screwed up :(
If the first match statement finds something, then the query works
fine
What would be a better way to structure this...without using a subquery
as I am on MYSQL 3.23
SELECT media.id, media.product AS product, media.name AS name,
Concat_WS( ' ', artist.fname, artist.lname ) AS artist, mediaType.id AS
mediatype, mediaType.name, media.product, media.path
FROM media, artist, mediaType
WHERE media.artist_id = artist.id
AND media.mediaType_id = mediaType.id
AND MATCH (
media.name, media.product
)
AGAINST (
'chris'
)
OR MATCH (
artist.fname, artist.lname
)
AGAINST (
'chris'
)
ORDER BY product, media.name, artist ASC
LIMIT 0 , 30
it's a parentheses issue. Your query looks like this in the where clause
WHERE artist_ID AND mediaType_ID AND first match OR second match
Which gets evaluated like
WHERE (artist_ID AND mediaType_ID AND first match) OR second match.
Any record that matched your second match condition also satisfied your
WHERE clause. Because you wanted to match on artist_Id and mediaType_ID
plus one of the match conditions, you needed to put a set of parentheses
around BOTH of your match conditions so that your WHERE clause looked
like:
WHERE artist_ID AND mediaType_ID AND (first match OR second match)
Here is an updated version of your original query (I also changed your
implicit inner joins to explicit ones (it's a pet peeve)):
SELECT media.id, media.product AS product
, media.name AS name
, Concat_WS( ' ', artist.fname, artist.lname ) AS artist
, mediaType.id AS mediatype
, mediaType.name
, media.product
, media.path
FROM media
INNER JOIN mediaType
ON media.mediaType_id = mediaType.id
INNER JOIN artist
ON media.artist_id = artist.id
WHERE MATCH (media.name, media.product)
AGAINST ('chris')
OR MATCH (artist.fname, artist.lname)
AGAINST ('chris')
ORDER BY product, media.name, artist ASC
LIMIT 0 , 30
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
MySQL Crash Diagnosis
I am running MySQL 4.1.8 on Windows 2000. Sporadically, the service will stop. It does not seem to be in relationship with load on the service itself, or the box. At seemingly random intervals, the service will go kaput for no readily apparent reason. My question to you isn't hey, what's wrong? but more about how I go diagnosing the problem. Are there any tools, utilities, logs I should be inspecting that I may not be aware of? Any help would be much appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: graphs
prathima rao wrote: hello, can anyone suggest if there is any software available to create graphs using mysql as database regards prathima rao My favourite has always been JpGraph - http://www.aditus.nu/jpgraph/ - it's a PHP library. You can use it on a web server or on a stand-alone PHP installation. It's not MySQL-specific, but there are some tutorials around demonstrating some graphs based on data from MySQL. I'm sure there are Perl libraries that also do graphing, but I've never been bothered to research to much - they'd have to be good to surpass JpGraph. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM and Dirty Reads
Is there a way to use dirty reads (that acquire no read locks on the table) with MyISAM tables? I want to avoid having the read requests queuing up while the table is updated, and I can tolerate a small margin of inconsistency for the sake of throughput. So far I found only information about scheduling cues (e.g. LOW_PRIORITY, DELAYED, etc) and using table handlers, but even with these cue, you still have the potential of queuing up reads or starving updates. For example, in MS SQL Server, you can either give a per-query lock hint or use a read-uncommitted transaction isolation level. MyISAM doesn't have a SET TRANSACTION READ UNCOMMITTED equivalent to InnoDB, and the SELECT statement doesn't have lock hints like (NOLOCK). So is there a way to allow reads to go through when the table is updated, or allow updates to proceed without waiting for prending reads to finish up? Thanks, Homam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a tricky/impossible query...
Hi, I need some help with a tricky query. Before anyone asks, I cannot bring this functionality back to the application layer (as much as I'd like to). Here's what I need to do... create table wibble( seq int(3) auto_increment primary key, x int(5), y int(5) ); insert into wibble set x=5, y=10; insert into wibble set x=1, y=3; insert into wibble set x=17, y=22; mysql select * from wibble; +-+--+--+ | seq | x| y| +-+--+--+ | 1 |5 | 10 | | 2 |1 |3 | | 3 | 17 | 22 | +-+--+--+ 3 rows in set (0.09 sec) So I want to run a query to explode the x/y ranges by seq. The required output is: mysql select some clever things from wibble where some clever stuff happens here; +-+--+ | seq | z| +-+--+ | 1 |1 | | 1 |2 | | 1 |3 | | 1 |4 | | 1 |5 | | 2 |1 | | 2 |2 | | 2 |3 | | 3 | 17 | | 3 | 18 | | 3 | 19 | | 3 | 20 | | 3 | 21 | | 3 | 22 | +-+--+ 14 rows in set (0.17 sec) Can anyone help me to achieve this result? Thanks, Andrew SQL, Query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a tricky/impossible query...
I should mention that I'm constrained to version 4.0.n so no sub queries for me! Andrew On 14/4/05 1:11 am, Andrew Braithwaite [EMAIL PROTECTED] wrote: Hi, I need some help with a tricky query. Before anyone asks, I cannot bring this functionality back to the application layer (as much as I'd like to). Here's what I need to do... create table wibble( seq int(3) auto_increment primary key, x int(5), y int(5) ); insert into wibble set x=5, y=10; insert into wibble set x=1, y=3; insert into wibble set x=17, y=22; mysql select * from wibble; +-+--+--+ | seq | x| y| +-+--+--+ | 1 |5 | 10 | | 2 |1 |3 | | 3 | 17 | 22 | +-+--+--+ 3 rows in set (0.09 sec) So I want to run a query to explode the x/y ranges by seq. The required output is: mysql select some clever things from wibble where some clever stuff happens here; +-+--+ | seq | z| +-+--+ | 1 |1 | | 1 |2 | | 1 |3 | | 1 |4 | | 1 |5 | | 2 |1 | | 2 |2 | | 2 |3 | | 3 | 17 | | 3 | 18 | | 3 | 19 | | 3 | 20 | | 3 | 21 | | 3 | 22 | +-+--+ 14 rows in set (0.17 sec) Can anyone help me to achieve this result? Thanks, Andrew SQL, Query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing the Prompt for timing purposes
When you say shell, do you mean DOS or UNIX? If it's the latter then you may do this for the logfile: sh-2.05b# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 4.0.24-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql ? For the complete MySQL Manual online visit: http://www.mysql.com/documentation For info on technical support from MySQL developers visit: http://www.mysql.com/support For info on MySQL books, utilities, consultants, etc. visit: http://www.mysql.com/portal List of all MySQL commands: (Commands must appear first on line and end with ';') help(\h)Display this help. ? (\?)Synonym for `help'. clear (\c)Clear command. connect (\r)Reconnect to the server. Optional arguments are db and host. edit(\e)Edit command with $EDITOR. ego (\G)Send command to mysql server, display result vertically. exit(\q)Exit mysql. Same as quit. go (\g)Send command to mysql server. nopager (\n)Disable pager, print to stdout. notee (\t)Don't write into outfile. pager (\P)Set PAGER [to_pager]. Print the query results via PAGER. print (\p)Print current command. prompt (\R)Change your mysql prompt. quit(\q)Quit mysql. rehash (\#)Rebuild completion hash. source (\.)Execute a SQL script file. Takes a file name as an argument. status (\s)Get status information from the server. system (\!)Execute a system shell command. tee (\T)Set outfile [to_outfile]. Append everything into given outfile. use (\u)Use another database. Takes database name as argument. Connection id: 2 (Can be used with mysqladmin kill) mysql \T wibble.txt Logging to file 'wibble.txt' mysql show databases; +--+ | Database | +--+ | andrew | | mysql| | phpdb| | test | +--+ 4 rows in set (0.00 sec) mysql Bye sh-2.05b# ll total 112 drwxr-xr-x 21 root wheel 714B 14 Apr 02:07 . drwxr-xr-x 11 root wheel 374B 12 Apr 00:44 .. -rw-r--r-- 1 root wheel18K 5 Mar 04:37 COPYING -rw-r--r-- 1 root wheel 5K 5 Mar 04:37 EXCEPTIONS-CLIENT -rw-r--r-- 1 root wheel 8K 5 Mar 04:37 INSTALL-BINARY -rw-r--r-- 1 root wheel 1K 5 Mar 00:38 README drwxr-xr-x 50 root wheel 1K 20 Mar 13:06 bin -rwxr-xr-x 1 root wheel 773B 5 Mar 04:50 configure drwxr-x--- 11 mysql wheel 374B 12 Apr 01:27 data drwxr-xr-x 7 root wheel 238B 20 Mar 13:06 docs drwxr-xr-x 53 root wheel 1K 20 Mar 13:06 include drwxr-xr-x 10 root wheel 340B 20 Mar 13:06 lib drwxr-xr-x 3 root wheel 102B 20 Mar 13:06 man drwxr-xr-x 10 root wheel 340B 20 Mar 13:06 mysql-test drwxr-xr-x 3 root wheel 102B 20 Mar 13:06 scripts drwxr-xr-x 3 root wheel 102B 20 Mar 13:06 share drwxr-xr-x 31 root wheel 1K 20 Mar 13:06 sql-bench -rwxr-xr-x 1 root wheel88B 20 Mar 13:13 start drwxr-xr-x 13 root wheel 442B 20 Mar 13:06 support-files drwxr-xr-x 21 root wheel 714B 20 Mar 13:06 tests -rw-r--r-- 1 root wheel 160B 14 Apr 02:08 wibble.txt sh-2.05b# cat wibble.txt mysql show databases; +--+ | Database | +--+ | andrew | | mysql| | phpdb| | test | +--+ 4 rows in set (0.00 sec) mysql sh-2.05b# Hope this helps, Andrew On 13/4/05 5:29 pm, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, The MYSQL command line interface is very basic. Can it be modified like the shell command prompts so that I can include date/time for timing benchmarks? Secondly, is there an echo command in MYSQL command prompt so that I can see the command I issued or a log file that I can write to.sorry about the basic questions but I'm a newbe. George __ 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]
Unix time as year in select query -nooby
MySql vers 4.0.20
A table noticeboard has three test entries.
A unix time from date(U) is stored in the field published of type,
bigint20.
I am not able to workout how to select the year. Assuming that the
value from date(U) can be treated as a unixtime value, my latest
unsuccessful effort is
$yearslist = mysql_query(SELECT published,
FROM_UNIXTIME(UNIX_TIMESTAMP(published),'%Y') AS year FROM noticeboard)
or die(Cannot get list of years.br . mysql_error());
$counttotal = mysql_num_rows($yearslist);
echo divRows found $counttotal/div;
while ($myrow = mysql_fetch_array($yearslist)) {
if (ISSET($myrow['published'])){$published=$myrow['published'];}
$yearpub=date(Y,$published);
if (ISSET($myrow['year'])){
$year=$myrow['year'];}
else {echo divYear is not set/div;}
echo divYear $year but value was $published. Year value should be
$yearpub/div;
}
The result is
Rows found 3
Year 1969 but value was 1083923875. Year value should be 2004
Year 1969 but value was 1113300220. Year value should be 2005
Year 1969 but value was 1113351870. Year value should be 2005
Louise
RE: Unix time as year in select query -nooby
On Wednesday, April 13, 2005 20:17, [EMAIL PROTECTED] wrote:
MySql vers 4.0.20
A table noticeboard has three test entries.
A unix time from date(U) is stored in the field published of
type, bigint20.
I am not able to workout how to select the year. Assuming that the
value from date(U) can be treated as a unixtime value, my latest
unsuccessful effort is
$yearslist = mysql_query(SELECT published,
FROM_UNIXTIME(UNIX_TIMESTAMP(published),'%Y') AS year FROM
noticeboard) or die(Cannot get list of years.br .
mysql_error());
$counttotal = mysql_num_rows($yearslist); echo divRows found
$counttotal/div;
while ($myrow = mysql_fetch_array($yearslist)) {
if (ISSET($myrow['published'])){$published=$myrow['published'];}
$yearpub=date(Y,$published);
if (ISSET($myrow['year'])){
$year=$myrow['year'];}
else {echo divYear is not set/div;}
echo divYear $year but value was $published. Year value should be
$yearpub/div;
}
The result is
Rows found 3
Year 1969 but value was 1083923875. Year value should be 2004 Year
1969 but value was 1113300220. Year value should be 2005 Year 1969
but value was 1113351870. Year value should be 2005
Louise
My guess is that you are passing what is already a unix timestamp to
the function unix_timestamp, and since that is an invalid datetime it
returns zero which then causes from_unixtime to return 1969 (when epoch
time started in your time zone).
Try using FROM_UNIXTIME(published,'%Y')
--
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unix time as year in select query -nooby
Tom Crimmins wrote: On Wednesday, April 13, 2005 20:17, [EMAIL PROTECTED] wrote: MySql vers 4.0.20 A table noticeboard has three test entries. A unix time from date(U) is stored in the field published of type, bigint20. I am not able to workout how to select the year. Assuming that the value from date(U) can be treated as a unixtime value, my latest unsuccessful effort is $yearslist = mysql_query(SELECT published, FROM_UNIXTIME(UNIX_TIMESTAMP(published),'%Y') AS year FROM noticeboard) or die(Cannot get list of years.br . mysql_error()); My guess is that you are passing what is already a unix timestamp to the function unix_timestamp, and since that is an invalid datetime it returns zero which then causes from_unixtime to return 1969 (when epoch time started in your time zone). Try using FROM_UNIXTIME(published,'%Y') I saw my mistake as soon as I posted and it was as you say. Many thanks Louise -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: weird characters from mysqldump?
Actually, it came from Mysql 4.1.1 (not 4.1.3 as I mentioned earlier). When I do select queries, the data displays just fine, but when I dump, I get this garbage. Jeremy Cole wrote: Hi Steve, I'm trying to export data from mysql 4.1.3 with mysqldump. I'm getting weird characters from the system. Here's what I've discovered so far: ' becomes ^À^Ù, e.g. didn't becomes didnâ^À^Ùt - becomes â^À^Ó, e.g. 1-2 becomes 1â^À^Ó2 è becomes è, e.g. Entrèe becomes Entrèe What gives? Looks like an application of the GIGO (Garbage In, Garbage Out) principle to me. The above data wouldn't have happened to come from a Microsoft Office application, would it? Looks like Smart Quotes etc., strikes again, to me. Regards, Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup databases one to another
This option involves two steps. First you have to export complete data and then import it again. You can do this more efficiently by using a sync tool like Webyog (www.webyog.com). It will sync any two MySQL databases with changes only done to modified rows/columns. YOu can probably mail their support people Regards Karam --- Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. Something like: mysqldump -h 192.168.1.1 -uroot -p -A backup.sql See: http://dev.mysql.com/doc/mysql/en/mysqldump.html Abdul Aziz [EMAIL PROTECTED] wrote: Dear All, I wish to backup all databases to my server(192.168.1.1) from my host(192. 168.1.5) with (mysqldump),how can we possible,plz tell me command or Script with brief description. Thanks in advance aaziz -- 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] __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup databases one to another
http://dev.mysql.com/doc/mysql/en/replication.html I would look into the replication features of MySQL. They can be used to create a backup copy of a database. The slave database server will only get the changes from the Master as needed. Karam Chand wrote: This option involves two steps. First you have to export complete data and then import it again. You can do this more efficiently by using a sync tool like Webyog (www.webyog.com). It will sync any two MySQL databases with changes only done to modified rows/columns. YOu can probably mail their support people Regards Karam --- Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. Something like: mysqldump -h 192.168.1.1 -uroot -p -A backup.sql See: http://dev.mysql.com/doc/mysql/en/mysqldump.html Abdul Aziz [EMAIL PROTECTED] wrote: Dear All, I wish to backup all databases to my server(192.168.1.1) from my host(192. 168.1.5) with (mysqldump),how can we possible,plz tell me command or Script with brief description. Thanks in advance aaziz -- 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] __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
book advice
Hi, I'm a newbie and looking for a book to help me learn mysql. I have come across a book called Beginning MySQL by Robert Sheldon and Geoff Moes. Can anyone recommend this book? Or, if not, what book can you recommend for a newbie. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
