Installation problems with MySql 5.0.41 (source distribution)
Hi everybody, I am trying to compile/configure MySQl 5.0.41 on a Mandrake 10 linux box. In doing so, I am getting some errors with mysql_install_db (ERROR: 1049 Unknown database 'mysql', Installation of system tables failed!) Please let me know how to solve the problem. Thanks in advance for your time and help. Anand Here are the details pertaining to my problem: I used this as a guide: http://dev.mysql.com/doc/refman/5.0/en/quick-install.html: and followed all the steps. The following error(s) comes when I try using mysql_install_db /usr/local/mysql/bin/mysql_install_db --user=mysql Installing MySQL system tables... ERROR: 1049 Unknown database 'mysql' 070616 1:24:38 [ERROR] Aborting 070616 1:24:38 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete Installation of system tables failed! Examine the logs in /usr/local/mysql/var for more information. You can try to start the mysqld daemon with: /usr/local/mysql/libexec/mysqld --skip-grant & and use the command line tool /usr/local/mysql/bin/mysql to connect to the mysql database and look at the grant tables: shell> /usr/local/mysql/bin/mysql -u root mysql mysql> show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in /usr/local/mysql/var that may be helpful. --- And then, when I do /usr/local/mysql/libexec/mysqld --skip-grant & as suggested in the above error message, it just aborts. Here is the architecture info. from mysqlbug -- >Release: mysql-5.0.41 (Source distribution) >C compiler:gcc (GCC) 3.3.2 (Mandrake Linux 10.0 3.3.2-6mdk) >C++ compiler: g++ (GCC) 3.3.2 (Mandrake Linux 10.0 3.3.2-6mdk) >Environment: System: Linux 2.6.3-7mdk #1 Wed Mar 17 15:56:42 CET 2004 i686 unknown unknown GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i586-mandrake-linux-gnu/3.3.2/specs Configured with: ../configure --prefix=/usr --libdir=/usr/lib --with-slibdir=/lib --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --enable-long-long --enable-__cxa_atexit --enable-clocale=gnu --enable-languages=c,c++,ada,f77,objc,java,pascal --host=i586-mandrake-linux-gnu --with-system-zlib Thread model: posix gcc version 3.3.2 (Mandrake Linux 10.0 3.3.2-6mdk) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Sep 13 2004 /lib/libc.so.6 -> libc-2.3.3.so -rwxr-xr-x 1 root root 1281788 Feb 16 2004 /lib/libc-2.3.3.so -rw-r--r-- 1 root root 204 Feb 16 2004 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--with-unix\ -socket-path=/usr/local/mysql/tmp/mysql.sock' -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump for myisam tables.
Hi, To take consistent backup, it is enough if you go for "mysqldump". 1) $ mysqldump -u user -p [table1,table2] > -- this itself takes consistent backup. mysqldump utility by default locks the table. 2) From one terminal issue mysql> LOCK TABLES WRITE; From another table, issue $>mysqldump -u user -p [table1,table2] > and unlock the tables once dump completed. Thanks ViSolve DB Team - Original Message - From: "Ananda Kumar" <[EMAIL PROTECTED]> To: "ViSolve DB Team" <[EMAIL PROTECTED]> Cc: "MySQL" Sent: Friday, June 15, 2007 4:30 PM Subject: Re: mysqldump for myisam tables. Thanks all for this response. This mysqlhotcopy take backup of .frm,.myd and .myi files, but my boss wants what mysqldump does. So, is it possible to take a consistent backup of myisam tables using mysqldump. Regards anandkl On 6/15/07, ViSolve DB Team <[EMAIL PROTECTED]> wrote: Hi You can also use "mysqlhotcopy". like $ mysqlhotcopy the option --addtodest - does not delete/rename the directory if exists; instead it will append the data to the same. [$mysqlhotcopy --addtodest dbname ] --allowold - create a new ; if that already exists renames it to _old. [mysqlhotcopy --allowold ] mysqlhotcopy is only for myisam & archive tables. While restoring, simply place the dumped directory into to mysql data dir. You can use either mysqldump or mysqlhotcopy. Thanks ViSolve DB Team - Original Message - From: "Ananda Kumar" <[EMAIL PROTECTED]> To: "MySQL" Sent: Friday, June 15, 2007 10:05 AM Subject: Re: mysqldump for myisam tables. > Hi All, > What are the parameters that i need to use to take consistent backup of > myisam tables using MYSQLDUMP. > > regards > anandkl > > > On 6/15/07, Ananda Kumar <[EMAIL PROTECTED]> wrote: >> >> Hi All, >> I am taking mysqldump of myisam table for the first time on a production >> database. Can you please let me know what all necessary thing i need >> to >> take >> care before i start mysqldump. Its on a running database. Also please >> tell >> me what all important parameters i need to use in mysqldump. >> >> Thanks for your help >> >> regards >> anandkl >> > No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.472 / Virus Database: 269.8.16/849 - Release Date: 6/14/2007 12:44 PM No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.472 / Virus Database: 269.8.16/849 - Release Date: 6/14/2007 12:44 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hiding columns used in GROUP BY and HAVING clauses
In the last episode (Jun 15), Edward Kay said: > I have a table of addresses. Each address is associated with a primary > entity and a primary entity can have n different addresses. For each primary > entity, one address is marked as the main address. > > I need a query to return all addresses that are the only address associated > with the primary entity but aren't marked as the main address. > > At the moment, I have this and it works: > > select * from contact_address > group by primary_entity_id > having count(primary_entity_id) = 1 > and is_primary = 0; > > This is fine except I want to use the result in a sub-query. Since it > returns two columns this doesn't work: > > update contact_address set is_primary = 1 where address_id in ( > select * from contact_address > group by primary_entity_id > having count(primary_entity_id) = 1 > and is_primary = 0 > ); > > Normally, I'd only return the address_id in the sub-SELECT, but I > need the is_primary column for the HAVING clause. I did some tests, and it looks like you can use aggregate functions in your HAVING clause without actually selecting the column. So "HAVING COUNT(primary_entity_id) = 1" should work even if you only select address_id. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hiding columns used in GROUP BY and HAVING clauses
Hi Edward, Edward Kay wrote: I have a table of addresses. Each address is associated with a primary entity and a primary entity can have n different addresses. For each primary entity, one address is marked as the main address. I need a query to return all addresses that are the only address associated with the primary entity but aren't marked as the main address. At the moment, I have this and it works: select * from contact_address group by primary_entity_id having count(primary_entity_id) = 1 and is_primary = 0; This is fine except I want to use the result in a sub-query. Since it returns two columns this doesn't work: update contact_address set is_primary = 1 where address_id in ( select * from contact_address group by primary_entity_id having count(primary_entity_id) = 1 and is_primary = 0 ); Normally, I'd only return the address_id in the sub-SELECT, but I need the is_primary column for the HAVING clause. There are two issues. 1) MySQL optimizes IN() subqueries very badly, and 2) as you see a scalar subquery can only return one column here. I suggest you rewrite it as a join: update contact_address inner join ( select address_id ... ) as X using(address_id) set is_primary = 1; Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5 et les charset sur debian etch
Hello, Je peux lire des Français, mais l'écriture qu'il n'est pas comme facile. Ainsi j'emploie des poissons de Babel pour traduire ceci d'anglais-français. Ainsi, mes excuses s'il la grammaire est totalement erroné. Très intéressant. Je devinerais que le problème s'est produit dans le transfert des données à partir d'une base de données à l'autre. Comment avez-vous copié les données ? Vous pourriez examiner insérer de nouvelles données dans chaque base de données et voir si elle semble correcte. Bill Gilles MISSONNIER wrote: Hello, j'ai 2 machines Linux debian etch, avec MySQL 5.0.32 J'ai un problème d'affichage sur l'une des machines : j'ai créé la même table et chargé le même fichier data dans une base sur chaque machine, et l'affichage est différent. Je ne vois pas oú est la différence... my.cnf idem locale idem mysql> select @@character_set_server,@@collation_server,@@character_set_connection; +---+++ | @@character_set_server | @@collation_server | @@character_set_connection +++---+ | utf8 | utf8_general_ci| utf8 +++---+ mysql> select nom,id from t; +--++ | nom | id | +--++ | aàb | 1 | | été | 2 | | cçoôeêeèeëi | 3 | | EÉEÈEË | 4 | | c'est tout | 5 | +--++ sur l'autre machine, l'affichage n'est pas bon. mysql> select @@character_set_server,@@collation_server,@@character_set_connection; ++++ | @@character_set_server | @@collation_server | @@character_set_connection ++++ | utf8 | utf8_general_ci| utf8 ++++ mysql> select nom,id from t; +++ | nom| id | +++ | aà b | 1 | | été | 2 | | cçoôeêeèeëi | 3 | | EÉEÈEË | 4 | | c'est tout | 5 | +++ mais si je fais : mysql> charset latin1; les accents sont là oú il faut, mais pas l'alignement des colones... mysql> select nom,id from t; +--+--+ | nom | id | +--+--+ | aàb |1 | | été|2 | | cçoôeêeèeëi |3 | | EÉEÈEË|4 | | c'est tout |5 | +--+--+ si quelqu'un peut reproduire la chose et m'expliquer le problème... voici la commande de création de la table : mysql> CREATE TABLE `t` ( `id` int(10) unsigned NOT NULL auto_increment, `nom` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 ; ensuite on rentre les données : mysql> load data infile '/le_repertoire/t' into table t fields terminated by ':'; et le fichier t contient : 1:aàb 2:été 3:cçoôeêeèeëi 4:EÉEÈEË 5:c'est tout _-¯-_-¯-_-¯-_-¯-_ Gilles Missonnier IAP - [EMAIL PROTECTED] 01 44 32 81 36 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hiding columns used in GROUP BY and HAVING clauses
I have a table of addresses. Each address is associated with a primary entity and a primary entity can have n different addresses. For each primary entity, one address is marked as the main address. I need a query to return all addresses that are the only address associated with the primary entity but aren't marked as the main address. At the moment, I have this and it works: select * from contact_address group by primary_entity_id having count(primary_entity_id) = 1 and is_primary = 0; This is fine except I want to use the result in a sub-query. Since it returns two columns this doesn't work: update contact_address set is_primary = 1 where address_id in ( select * from contact_address group by primary_entity_id having count(primary_entity_id) = 1 and is_primary = 0 ); Normally, I'd only return the address_id in the sub-SELECT, but I need the is_primary column for the HAVING clause. Any ideas on how to achieve this? Thanks, Edward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5 et les charset sur debian etch
Hello, j'ai 2 machines Linux debian etch, avec MySQL 5.0.32 J'ai un problème d'affichage sur l'une des machines : j'ai créé la même table et chargé le même fichier data dans une base sur chaque machine, et l'affichage est différent. Je ne vois pas oú est la différence... my.cnf idem locale idem mysql> select @@character_set_server,@@collation_server,@@character_set_connection; +---+++ | @@character_set_server | @@collation_server | @@character_set_connection +++---+ | utf8 | utf8_general_ci| utf8 +++---+ mysql> select nom,id from t; +--++ | nom | id | +--++ | aàb | 1 | | été | 2 | | cçoôeêeèeëi | 3 | | EÉEÈEË | 4 | | c'est tout | 5 | +--++ sur l'autre machine, l'affichage n'est pas bon. mysql> select @@character_set_server,@@collation_server,@@character_set_connection; ++++ | @@character_set_server | @@collation_server | @@character_set_connection ++++ | utf8 | utf8_general_ci| utf8 ++++ mysql> select nom,id from t; +++ | nom| id | +++ | aà b | 1 | | été | 2 | | cçoôeêeèeëi | 3 | | EÉEÈEË | 4 | | c'est tout | 5 | +++ mais si je fais : mysql> charset latin1; les accents sont là oú il faut, mais pas l'alignement des colones... mysql> select nom,id from t; +--+--+ | nom | id | +--+--+ | aàb |1 | | été|2 | | cçoôeêeèeëi |3 | | EÉEÈEË|4 | | c'est tout |5 | +--+--+ si quelqu'un peut reproduire la chose et m'expliquer le problème... voici la commande de création de la table : mysql> CREATE TABLE `t` ( `id` int(10) unsigned NOT NULL auto_increment, `nom` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 ; ensuite on rentre les données : mysql> load data infile '/le_repertoire/t' into table t fields terminated by ':'; et le fichier t contient : 1:aàb 2:été 3:cçoôeêeèeëi 4:EÉEÈEË 5:c'est tout _-¯-_-¯-_-¯-_-¯-_ Gilles Missonnier IAP - [EMAIL PROTECTED] 01 44 32 81 36 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Change in behaviour in version 5.0.41
On Fri, June 15, 2007 16:29, Ben Clewett wrote: > Dear MySql, > > I have noticed a change in behaviour in MySql 5.0.41 from 5.0.26 with > date comparisons. > > In 5.0.26:'2007-06-15' = '2007-06-15 00:00:00' is True. > In 5.0.41:'2007-06-15' = '2007-06-15 00:00:00' is False. > In 5.1.6-alpha: '2007-06-15' = '2007-06-15 00:00:00' is True. On my servers: 5.0.21-log '2007-06-15' = '2007-06-15 00:00:00' is False. 5.1.17-beta-log '2007-06-15' = '2007-06-15 00:00:00' is False. But on 5.0.21 SELECT COUNT(*) FROM t WHERE d = '2007-06-15 00:00:00'; = 1 SELECT COUNT(*) FROM t WHERE d = '2007-06-15'; = 1 and on 5.1.17 SELECT COUNT(*) FROM t WHERE d = '2007-06-15 00:00:00'; = 0 SELECT COUNT(*) FROM t WHERE d = '2007-06-15'; = 1 How does that make sense ??? > This has caused us a few problems. Is this the way things should be, > because this change does not seem right? > > I am also very worried that this behaviour revert when we role out 5.1? > > Does any member know whether this is a bug, or just an anoying feature? > > Regards, > > Ben > > > To Replicate: > > CREATE TABLE t (d DATE); > INSERT INTO t VALUES ('2007-06-15'); > SELECT COUNT(*) FROM t WHERE d = '2007-06-15 00:00:00'; > +--+ > | COUNT(*) | > +--+ > |0 | > +--+ > SELECT COUNT(*) FROM t WHERE d = '2007-06-15'; > +--+ > | COUNT(*) | > +--+ > |1 | > +--+ > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Master-Slave System Using Different Versions of MySQL
Hello, Did you already check: http://dev.mysql.com/doc/refman/5.0/en/replication-compatibility.html "You cannot replicate from a master that uses a newer binary log format to a slave that uses an older format (for example, from MySQL 5.0 to MySQL 4.1.)" Thanks, Jimmy Guerrero Sr Product Manager MySQL, Inc Houston, TX [EMAIL PROTECTED] wrote: There was a comment this week about a v5 master having problems connecting to a v4 slave, or vice versa. Can someone shed some light on this issue. A manual reference is good, too. Thanks, David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb tablespace
In the last episode (Jun 15), Ben Clewett said: > > Are there any reasons why one would NOT use separate ibd files for > > each table > > Fragmentation for one. > > A single file can re-use empty space from deleted rows for any added > rows. A single file can only re-use space from that one file. > > Therefore the sum table size will be larger with many files. > Depending on how much data you regularly delete. I would claim that the file-per-table method reduces fragmentation and overall tablepsace size as compared to the tablespace method: OPTIMIZE'ing a file-per-table table recreates that .ibd file and removes all the empty space. To do that with the single tablespace setup, you would have to dump all tables, delete your tablespace file, and reload. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Change in behaviour in version 5.0.41
Ben Clewett wrote: Dear MySql, I have noticed a change in behaviour in MySql 5.0.41 from 5.0.26 with date comparisons. In 5.0.26:'2007-06-15' = '2007-06-15 00:00:00' is True. In 5.0.41:'2007-06-15' = '2007-06-15 00:00:00' is False. In 5.1.6-alpha: '2007-06-15' = '2007-06-15 00:00:00' is True. This has caused us a few problems. Is this the way things should be, because this change does not seem right? I'd report this as a bug, if it's not already reported. It definitely looks like a bug to me. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Master-Slave System Using Different Versions of MySQL
There was a comment this week about a v5 master having problems connecting to a v4 slave, or vice versa. Can someone shed some light on this issue. A manual reference is good, too. Thanks, David
Re: Innodb tablespace
Olaf Stein wrote: Hi all, Are there any reasons why one would NOT use separate ibd files for each table (--innodb_file_per_table). It seems logical to me to separate what does not belong together logically (different databases), but I as the shared tablespace is the default I wonder if it has nay advantages I am not aware of Fragmentation for one. A single file can re-use empty space from deleted data for any added tables and rows. A single file can only re-use space from that one file. Therefore the sum table size will be larger with many files. Depending on how much data you regularly delete. (Fragmentation also occurs when row sizes are increased.) There are file system problems as well. Many files rely on a good file system, like Reiserfs, and not, say, Fat32. Many files also result in a heavier hit on a journaling file system. Important if you are using a lot of files, like many hundreds. But I don't believe there is any IO difference. The same number of file handles are used, whether they all access one file or 1000 files... Also worth noting that an external single file is still used with file-per-table, which I suspect is used for referential constraint storage. Therefore a complete division by database is not possible, all databases still use this one file. Personally I like a single file, it's easier to administer and HotBackup works. But, if any IonnDB developers read this mailing list, we really need to be able to break this file using defined table space, as with Oracle. Then have as many/few files as we like. Ben Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb tablespace
> Hi all, > > Are there any reasons why one would NOT use separate ibd files for each > table Fragmentation for one. A single file can re-use empty space from deleted rows for any added rows. A single file can only re-use space from that one file. Therefore the sum table size will be larger with many files. Depending on how much data you regularly delete. (Fragmentation also occurs when row sizes are increased.) The file system is also an issue. Lots of files require a good file system, like Reiserfs, and not FAT32. A single file is just as efficient with any file system. But I don't believe there are any IO difference. The same number of file handles are used, whether they all access one file or 1000 files. Personally I like a single file, it's easier to administer and HotBackup works. But, if any IonnDB developers read this mailing list, we really need to be able to break this file using defined table space, as with Oracle. Then have as many/few files as we like Ben Olaf Stein wrote: Hi all, Are there any reasons why one would NOT use separate ibd files for each table (--innodb_file_per_table). It seems logical to me to separate what does not belong together logically (different databases), but I as the shared tablespace is the default I wonder if it has nay advantages I am not aware of Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Change in behaviour in version 5.0.41
Dear MySql, I have noticed a change in behaviour in MySql 5.0.41 from 5.0.26 with date comparisons. In 5.0.26:'2007-06-15' = '2007-06-15 00:00:00' is True. In 5.0.41:'2007-06-15' = '2007-06-15 00:00:00' is False. In 5.1.6-alpha: '2007-06-15' = '2007-06-15 00:00:00' is True. This has caused us a few problems. Is this the way things should be, because this change does not seem right? I am also very worried that this behaviour revert when we role out 5.1? Does any member know whether this is a bug, or just an anoying feature? Regards, Ben To Replicate: CREATE TABLE t (d DATE); INSERT INTO t VALUES ('2007-06-15'); SELECT COUNT(*) FROM t WHERE d = '2007-06-15 00:00:00'; +--+ | COUNT(*) | +--+ |0 | +--+ SELECT COUNT(*) FROM t WHERE d = '2007-06-15'; +--+ | COUNT(*) | +--+ |1 | +--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb tablespace
Hi All, If you specify one file per table, these files would be created under the database directory of that particular database . So, the benifit with respect to IO is negative. To have these files placed in different file system to get IO benifit, you need to use symbolic links. Please correct me if i am wrong. regards anandkl On 6/15/07, Olaf Stein <[EMAIL PROTECTED]> wrote: Hi all, Are there any reasons why one would NOT use separate ibd files for each table (--innodb_file_per_table). It seems logical to me to separate what does not belong together logically (different databases), but I as the shared tablespace is the default I wonder if it has nay advantages I am not aware of Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb tablespace
Hi all, Are there any reasons why one would NOT use separate ibd files for each table (--innodb_file_per_table). It seems logical to me to separate what does not belong together logically (different databases), but I as the shared tablespace is the default I wonder if it has nay advantages I am not aware of Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump for myisam tables.
Thanks all for this response. This mysqlhotcopy take backup of .frm,.myd and .myi files, but my boss wants what mysqldump does. So, is it possible to take a consistent backup of myisam tables using mysqldump. Regards anandkl On 6/15/07, ViSolve DB Team <[EMAIL PROTECTED]> wrote: Hi You can also use "mysqlhotcopy". like $ mysqlhotcopy the option --addtodest - does not delete/rename the directory if exists; instead it will append the data to the same. [$mysqlhotcopy --addtodest dbname ] --allowold - create a new ; if that already exists renames it to _old. [mysqlhotcopy --allowold ] mysqlhotcopy is only for myisam & archive tables. While restoring, simply place the dumped directory into to mysql data dir. You can use either mysqldump or mysqlhotcopy. Thanks ViSolve DB Team - Original Message - From: "Ananda Kumar" <[EMAIL PROTECTED]> To: "MySQL" Sent: Friday, June 15, 2007 10:05 AM Subject: Re: mysqldump for myisam tables. > Hi All, > What are the parameters that i need to use to take consistent backup of > myisam tables using MYSQLDUMP. > > regards > anandkl > > > On 6/15/07, Ananda Kumar <[EMAIL PROTECTED]> wrote: >> >> Hi All, >> I am taking mysqldump of myisam table for the first time on a production >> database. Can you please let me know what all necessary thing i need to >> take >> care before i start mysqldump. Its on a running database. Also please >> tell >> me what all important parameters i need to use in mysqldump. >> >> Thanks for your help >> >> regards >> anandkl >> > No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.472 / Virus Database: 269.8.16/849 - Release Date: 6/14/2007 12:44 PM
Re: mysqldump for myisam tables.
Hi You can also use "mysqlhotcopy". like $ mysqlhotcopy the option --addtodest - does not delete/rename the directory if directory> exists; instead it will append the data to the same. [$mysqlhotcopy --addtodest dbname ] --allowold - create a new ; if that already exists renames it to _old. [mysqlhotcopy --allowold ] mysqlhotcopy is only for myisam & archive tables. While restoring, simply place the dumped directory into to mysql data dir. You can use either mysqldump or mysqlhotcopy. Thanks ViSolve DB Team - Original Message - From: "Ananda Kumar" <[EMAIL PROTECTED]> To: "MySQL" Sent: Friday, June 15, 2007 10:05 AM Subject: Re: mysqldump for myisam tables. Hi All, What are the parameters that i need to use to take consistent backup of myisam tables using MYSQLDUMP. regards anandkl On 6/15/07, Ananda Kumar <[EMAIL PROTECTED]> wrote: Hi All, I am taking mysqldump of myisam table for the first time on a production database. Can you please let me know what all necessary thing i need to take care before i start mysqldump. Its on a running database. Also please tell me what all important parameters i need to use in mysqldump. Thanks for your help regards anandkl No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.472 / Virus Database: 269.8.16/849 - Release Date: 6/14/2007 12:44 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question
Is it just this line I need to change? INNER JOIN url_categories uc ON uc.ID=bt.category_ID; Would it change to something like: INNER JOIN url_categories uc ON CAST(uc.ID as CHAR)=delimit(bt.category_ID) Just guessing! Thanks - that's what I thought. I really don't have much experience with mySQL. If it's not too much trouble, could someone give me a bit more help on how to do that please? Ed. no, those won't match based on just the datatype change.. you will have to define a user defined function to do those comparisons. On 6/14/07, Edward Quick <[EMAIL PROTECTED]> wrote: Thanks, that's interesting. Actually the uc.ID column is still type tinyint as it holds only one number, but are you saying if I change this to varchar my query will work e.g. 15 = 15:17 would work? > >What is the type of the 'uc.ID' column? If it's varchar, your match >will work fine. If it's an integer type, you are going to have a >problem because you have bt.category_ID holding things which can't be >represented as integers and will therefore never match. IF both >column type are being changed here, your query will work fine as is. > >- michael dykman > >On 6/14/07, Edward Quick <[EMAIL PROTECTED]> wrote: >>Hi, >> >>I have the following mySQL query in my script which has been working fine >>but due to a recent change, I had to modify one of the columns, >>bt.category_ID. This used to be defined as tinyint(3) but I've changed >>that >>now to varchar(20) as it needs to hold values such as 15, or 74:79 or >>43:56:113 >> >>In light of that, could anyone tell me what I need to change in my SQL to >>get it working please? >>Presuambly uc.ID=bt.category_ID won't work anymore. >> >>INSERT IGNORE $visitstable (url_scheme_ID, url_server_ID, url_path_ID, >>url_query_ID, url_category_ID) >> SELECT DISTINCT usc.ID, us.ID, up.ID, uq.ID, >>uc.ID >> FROM bulk_table bt >> INNER JOIN url_servers us ON us.server=bt.server >> INNER JOIN $pathstable up ON up.path=bt.path >> INNER JOIN url_schemes usc ON >>usc.ID=bt.scheme_ID >> INNER JOIN $queriestable uq ON uq.query=bt.query >> INNER JOIN url_categories uc ON >>uc.ID=bt.category_ID; >> >>Many thanks, >> >>Ed. >> >>_ >>Win tickets to the sold out Live Earth concert! >>http://liveearth.uk.msn.com >> >> >>-- >>MySQL General Mailing List >>For list archives: http://lists.mysql.com/mysql >>To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >> >> > > >-- >- michael dykman >- [EMAIL PROTECTED] > >- All models are wrong. Some models are useful. _ Win tickets to the sold out Live Earth concert! http://liveearth.uk.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. _ Play your part in making history - Email Britain! http://www.emailbritain.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Play your part in making history - Email Britain! http://www.emailbritain.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump for myisam tables.
HI You can use $mysqldump dbname table1 [,table2,table3] >dumpfile[path] or $ mysqldump dbname > dumpfile [path] or $ mysqldump --all-databases >dumpfile [path] --opt will Add a DROP TABLE statement before each CREATE TABLE, Uses the multiline INSERT syntax,Locks all tables on the server before starting the dump and unlocks the same. --opt is default. Thanks ViSolve DB Team - Original Message - From: "Ananda Kumar" <[EMAIL PROTECTED]> To: "MySQL" Sent: Friday, June 15, 2007 10:05 AM Subject: Re: mysqldump for myisam tables. Hi All, What are the parameters that i need to use to take consistent backup of myisam tables using MYSQLDUMP. regards anandkl On 6/15/07, Ananda Kumar <[EMAIL PROTECTED]> wrote: Hi All, I am taking mysqldump of myisam table for the first time on a production database. Can you please let me know what all necessary thing i need to take care before i start mysqldump. Its on a running database. Also please tell me what all important parameters i need to use in mysqldump. Thanks for your help regards anandkl No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.472 / Virus Database: 269.8.16/849 - Release Date: 6/14/2007 12:44 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]