default-storage-engine not working?
Hi, we have just upgraded from mysql 4.1 to 5.0 and this far I only have one problem. I can't get default-storage-engine to work. I have default-storage-engine = INNODB under my [mysqld] in my.cnf which is loaded by the daemon (tested with password=xxx). I have also tried default-table-type, default_table_type and default_storage_engine since I found unofficial posts saying it with underscores. Please advice, I have spent 1-2 hours without any answer (probably me that misses something very easy). I test if it works with this: -- mysql create table g (i int); show table status; Query OK, 0 rows affected (0.06 sec) +--++-++--++-+--+--+---++-+-++---+--++-+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--++-++--++-+--+--+---++-+-++---+--++-+ | g| MyISAM | 10 | Fixed |0 | 0 | 0 | 1970324836974591 | 1024 | 0 | NULL | 2007-04-25 11:28:39 | 2007-04-25 11:28:39 | NULL | latin1_swedish_ci | NULL | | | +--++-++--++-+--+--+---++-+-++---+--++-+ 1 row in set (0.00 sec) -- My mysql.err looks like this: -- 070425 11:19:01 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown 070425 11:19:01 [Warning] Aborted connection 3 to db: 'mytest1' user: 'root' host: 'localhost' (Got timeout reading communication packets) 070425 11:19:03 InnoDB: Starting shutdown... 070425 11:19:05 InnoDB: Shutdown completed; log sequence number 0 74244313 070425 11:19:05 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 070425 11:19:05 mysqld ended 070425 11:19:06 mysqld started 070425 11:19:07 InnoDB: Started; log sequence number 0 74244313 070425 11:19:07 [Warning] 'user' entry '[EMAIL PROTECTED]' ignored in --skip-name-resolve mode. 070425 11:19:07 [Warning] 'user' entry '@mgdb01' ignored in --skip-name-resolve mode. 070425 11:19:07 [Note] /usr/local/mysql/bin/mysqld: ready for connections. Version: '5.0.37-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL) -- InnoDB is enabled: -- mysql show engines; ++--++ | Engine | Support | Comment | ++--++ | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | | BerkeleyDB | NO | Supports transactions and page-level locking | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | | EXAMPLE| YES | Example storage engine | | ARCHIVE| YES | Archive storage engine | | CSV| YES | CSV storage engine | | ndbcluster | DISABLED | Clustered, fault-tolerant, memory-based tables | | FEDERATED | YES | Federated MySQL storage engine | | MRG_MYISAM | YES | Collection of identical MyISAM tables | | ISAM | NO | Obsolete storage engine | ++--++ 12 rows in set (0.00 sec) -- I am using 5.0.37: -- mysql select version(); ++ | version() | ++ | 5.0.37-log | ++ 1 row in set (0.00 sec) -- -- Magnus Månsson Systems administrator Massive Entertainment Office: +46-40-6001000 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help in Procedure
Hi All, This is my procedure, i am using cursor to access data one row by one. The cursor is looping twice, though i have only one row data in the DB table(playersloginfo). Can any body tell me why cursor is looping twice and how can i control it, And please explain me what is this declare continue handler for sqlstate '02000' . I will be glad if anybody answer to me. create procedure activity1() begin declare done int default 0; declare pack text; declare name, logout, login varchar(50); set pack=''; declare cur1 cursor for select user_name, concat(logindate, ' ', logintime), concat(logoutdate, ' ',logouttime) from playersloginfo; declare continue handler for sqlstate '02000' set done = 1; open cur1; repeat fetch cur1 into name, login, logout; set pack = concat(pack, name, ',', login, ',', logout,';'); until done end repeat; select pack; close cur1; end;|
What is /*!40101 SET @...
I've been developing Perl/ PHP and MySQL applications on a small scale for years, reworking my small core of knowledge over and over again. I recently decided to hit the books and shoot for certification as a MySQL CMDEV. Most of my questions have answers in my books and on web sites, but I occassionally run into stuff so weird, I don't even know how to phrase a web query. For example, what is this? It appears at the top of a data dump. I recognize the Create and Insert sections, but this is Greek. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select first letters
Hello list. I want to select discinct the first letters of titles in a UTF8 table but only the greek ones. There are both english and greek charakter titles. How can I exclude the english from selection? My table is: CREATE TABLE `odigos_details` ( `id` int(11) NOT NULL auto_increment, `cat` tinyint(3) default NULL, `territory` tinyint(3) default NULL, `title` varchar(150) default NULL, `story` text, `link` varchar(100) default NULL, `address` varchar(150) default NULL, `tel` varchar(50) default NULL, `fax` varchar(11) default NULL, `photo` varchar(100) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 and I run: SELECT DISTINCT LEFT(title,1) FROM odigos_details ORDER BY title witch is working fine. Thank you. Nikos
Re: What is /*!40101 SET @...
At 8:44 AM -0400 4/25/07, Kebbel, John wrote: I've been developing Perl/ PHP and MySQL applications on a small scale for years, reworking my small core of knowledge over and over again. I recently decided to hit the books and shoot for certification as a MySQL CMDEV. Most of my questions have answers in my books and on web sites, but I occassionally run into stuff so weird, I don't even know how to phrase a web query. For example, what is this? It appears at the top of a data dump. I recognize the Create and Insert sections, but this is Greek. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; Those are code-containing comments, such that the code will be executed only if the server is as recent as the version number at the beginning of the comment. http://dev.mysql.com/doc/refman/5.0/en/comments.html Basically, it's version-specific code for features that are unavailable in older servers. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select first letters
I'm not sure, but I think that greek characters are sorted after English chars so try this: SELECT DISTINCT LEFT(title,1) FROM odigos_details WHERE LEFT(title,1) 'z' ORDER BY title HTH, Dusan nikos napsal(a): Hello list. I want to select discinct the first letters of titles in a UTF8 table but only the greek ones. There are both english and greek charakter titles. How can I exclude the english from selection? My table is: CREATE TABLE `odigos_details` ( `id` int(11) NOT NULL auto_increment, `cat` tinyint(3) default NULL, `territory` tinyint(3) default NULL, `title` varchar(150) default NULL, `story` text, `link` varchar(100) default NULL, `address` varchar(150) default NULL, `tel` varchar(50) default NULL, `fax` varchar(11) default NULL, `photo` varchar(100) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 and I run: SELECT DISTINCT LEFT(title,1) FROM odigos_details ORDER BY title witch is working fine. Thank you. Nikos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select first letters
Aren't all of the Greek characters distinct from all of the English characters? If so, you could test just the first letter of the field against an IN condition, where the IN (...) lists all of the Greek characters. If it's easier to type, you could use a NOT IN (...) test and list the English letters. The former would be safer, just to guard against the possibility that some non-Greek Latin characters have crept in. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: nikos [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 25, 2007 9:28 AM To: mysql@lists.mysql.com Subject: select first letters Hello list. I want to select discinct the first letters of titles in a UTF8 table but only the greek ones. There are both english and greek charakter titles. How can I exclude the english from selection? My table is: CREATE TABLE `odigos_details` ( `id` int(11) NOT NULL auto_increment, `cat` tinyint(3) default NULL, `territory` tinyint(3) default NULL, `title` varchar(150) default NULL, `story` text, `link` varchar(100) default NULL, `address` varchar(150) default NULL, `tel` varchar(50) default NULL, `fax` varchar(11) default NULL, `photo` varchar(100) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 and I run: SELECT DISTINCT LEFT(title,1) FROM odigos_details ORDER BY title witch is working fine. Thank you. Nikos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select first letters
Seems that work in v. 4.1.21 but not in 5.0.27 Thank you Dusan Dus(an Pavlica wrote: I'm not sure, but I think that greek characters are sorted after English chars so try this: SELECT DISTINCT LEFT(title,1) FROM odigos_details WHERE LEFT(title,1) 'z' ORDER BY title HTH, Dusan nikos napsal(a): Hello list. I want to select discinct the first letters of titles in a UTF8 table but only the greek ones. There are both english and greek charakter titles. How can I exclude the english from selection? My table is: CREATE TABLE `odigos_details` ( `id` int(11) NOT NULL auto_increment, `cat` tinyint(3) default NULL, `territory` tinyint(3) default NULL, `title` varchar(150) default NULL, `story` text, `link` varchar(100) default NULL, `address` varchar(150) default NULL, `tel` varchar(50) default NULL, `fax` varchar(11) default NULL, `photo` varchar(100) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 and I run: SELECT DISTINCT LEFT(title,1) FROM odigos_details ORDER BY title witch is working fine. Thank you. Nikos
Re: select first letters
It was my first thought Jerry, but it didn't work to me... Thanks anyway! Jerry Schwartz wrote: Aren't all of the Greek characters distinct from all of the English characters? If so, you could test just the first letter of the field against an IN condition, where the IN (...) lists all of the Greek characters. If it's easier to type, you could use a NOT IN (...) test and list the English letters. The former would be safer, just to guard against the possibility that some non-Greek Latin characters have crept in. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: nikos [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 25, 2007 9:28 AM To: mysql@lists.mysql.com Subject: select first letters Hello list. I want to select discinct the first letters of titles in a UTF8 table but only the greek ones. There are both english and greek charakter titles. How can I exclude the english from selection? My table is: CREATE TABLE `odigos_details` ( `id` int(11) NOT NULL auto_increment, `cat` tinyint(3) default NULL, `territory` tinyint(3) default NULL, `title` varchar(150) default NULL, `story` text, `link` varchar(100) default NULL, `address` varchar(150) default NULL, `tel` varchar(50) default NULL, `fax` varchar(11) default NULL, `photo` varchar(100) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 and I run: SELECT DISTINCT LEFT(title,1) FROM odigos_details ORDER BY title witch is working fine. Thank you. Nikos
RE: Stopping Replication
Re: webserver2-relay-bin.{some#}
Because you've deleted the master.info file found in the SLAVE server
therefore these files( relay logs) are no longer needed. Mind you, you
will not be able resume or restart replication. Replication at your site
will have to be setup from 'scratch'.
Thanks
-Original Message-
From: Jesse [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 24, 2007 4:26 PM
To: Brown, Charles; Michael Dykman; Gerald L. Clark
Cc: MySQL List
Subject: Re: Stopping Replication
Thanks,
I've removed these files. Also, there are a BUNCH of files named
webserver2-relay-bin.{some#} in the data folder. Can I delete all of
these (including the .index file)?
Thanks,
Jesse
- Original Message -
From: Brown, Charles [EMAIL PROTECTED]
To: Michael Dykman [EMAIL PROTECTED]; Gerald L. Clark
[EMAIL PROTECTED]
Cc: Jesse [EMAIL PROTECTED]; MySQL List mysql@lists.mysql.com
Sent: Tuesday, April 24, 2007 4:31 PM
Subject: RE: Stopping Replication
(Read ref manual: 6.4)
Here's the best kept secret:
The Slave has two files called master. info and relay-log.info. The
slave uses these two files to keep track of how much of the master's
binary log it has processed and all pertinent info about the master( i.e
userid, password, hostname) are all documented in the master.info file.
Did you say you would like to stop replication - PERIOD! Delete these
files or use CHANGE MASTER TO statement to change replication
parameters.
Why: The contents of master.info overrides some of the server options
specified on the command line or in my.cnf. (Read ref manual: 6.4)
-Original Message-
From: Michael Dykman [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 24, 2007 2:35 PM
To: Gerald L. Clark
Cc: Jesse; MySQL List
Subject: Re: Stopping Replication
you mill also do well to eliminate the master.nfo file from your data
directory.
On 4/24/07, Gerald L. Clark [EMAIL PROTECTED] wrote:
Jesse wrote:
I'm trying to stop replication between two servers that I've got
here.
I set it up originally, to play with it and see how it works.
However,
I now want to use these two servers separately.
I have edited the my.ini file on both the server and slave machines,
and
removed the server-id from them. I have also executed a stop slave
on
the slave server. However, it appears that when I stop and re-start
the
server that was the slave, it tries to re-start replication again,
because I get the error, Failed to create slave threads in the
event
log.
What else do I need to do to stop the replication process?
Thanks,
Jesse
You have to remove the master information from the slave's my.ini
file.
--
Gerald L. Clark
Supplier Systems Corporation
--
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
This message is intended only for the use of the Addressee and
may contain information that is PRIVILEGED and CONFIDENTIAL.
If you are not the intended recipient, you are hereby notified
that any dissemination of this communication is strictly prohibited.
If you have received this communication in error, please erase
all copies of the message and its attachments and notify us
immediately.
Thank you.
This message is intended only for the use of the Addressee and
may contain information that is PRIVILEGED and CONFIDENTIAL.
If you are not the intended recipient, you are hereby notified
that any dissemination of this communication is strictly prohibited.
If you have received this communication in error, please erase
all copies of the message and its attachments and notify us
immediately.
Thank you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
IBM, MySQL team up on database software
IBM, MySQL team up on database software BOSTON (Reuters) - International Business Machines Corp. IBM.N plans to start selling products from smaller rival MySQL AB, a developer of an open-source database that is used to help run Web sites, Executives at IBM, the world's No. 2 software maker, and MySQL told Reuters they will announce a technology and marketing partnership on Wednesday at a MySQL users' conference in Santa Clara, California. MySQL AB is a privately held company that develops standardized versions of the open-source MySQL database. It makes money by selling service agreements that cover upgrades, technical support and maintenance of those programs. MySQL is one of the most popular types of open source software after Linux, the popular computer operating system. It competes with products from Oracle Corp. The agreement calls for IBM and MySQL to develop software that will make MySQL compatible with programs that run IBM's System i line of business computers, including IBM's i5 operating system DB2 database. IBM will also sell MySQL's service products. This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: default-storage-engine not working?
Try just 'storage-engine' - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Sent: Wednesday, April 25, 2007 5:26:40 AM (GMT-0500) America/New_York Subject: default-storage-engine not working? Hi, we have just upgraded from mysql 4.1 to 5.0 and this far I only have one problem. I can't get default-storage-engine to work. I have default-storage-engine = INNODB under my [mysqld] in my.cnf which is loaded by the daemon (tested with password=xxx). I have also tried default-table-type, default_table_type and default_storage_engine since I found unofficial posts saying it with underscores. Please advice, I have spent 1-2 hours without any answer (probably me that misses something very easy). I test if it works with this: -- mysql create table g (i int); show table status; Query OK, 0 rows affected (0.06 sec) +--++-++--++-+--+--+---++-+-++---+--++-+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--++-++--++-+--+--+---++-+-++---+--++-+ | g| MyISAM | 10 | Fixed |0 | 0 | 0 | 1970324836974591 | 1024 | 0 | NULL | 2007-04-25 11:28:39 | 2007-04-25 11:28:39 | NULL | latin1_swedish_ci | NULL | | | +--++-++--++-+--+--+---++-+-++---+--++-+ 1 row in set (0.00 sec) -- My mysql.err looks like this: -- 070425 11:19:01 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown 070425 11:19:01 [Warning] Aborted connection 3 to db: 'mytest1' user: 'root' host: 'localhost' (Got timeout reading communication packets) 070425 11:19:03 InnoDB: Starting shutdown... 070425 11:19:05 InnoDB: Shutdown completed; log sequence number 0 74244313 070425 11:19:05 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 070425 11:19:05 mysqld ended 070425 11:19:06 mysqld started 070425 11:19:07 InnoDB: Started; log sequence number 0 74244313 070425 11:19:07 [Warning] 'user' entry '[EMAIL PROTECTED]' ignored in --skip-name-resolve mode. 070425 11:19:07 [Warning] 'user' entry '@mgdb01' ignored in --skip-name-resolve mode. 070425 11:19:07 [Note] /usr/local/mysql/bin/mysqld: ready for connections. Version: '5.0.37-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL) -- InnoDB is enabled: -- mysql show engines; ++--++ | Engine | Support | Comment | ++--++ | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | | BerkeleyDB | NO | Supports transactions and page-level locking | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | | EXAMPLE| YES | Example storage engine | | ARCHIVE| YES | Archive storage engine | | CSV| YES | CSV storage engine | | ndbcluster | DISABLED | Clustered, fault-tolerant, memory-based tables | | FEDERATED | YES | Federated MySQL storage engine | | MRG_MYISAM | YES | Collection of identical MyISAM tables | | ISAM | NO | Obsolete storage engine | ++--++ 12 rows in set (0.00 sec) -- I am using 5.0.37: -- mysql select version(); ++ | version() | ++ | 5.0.37-log | ++ 1 row in set (0.00 sec) -- -- Magnus Månsson Systems administrator Massive Entertainment Office: +46-40-6001000 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
RE: select first letters
It would depend upon the collating sequence for the field. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: nikos [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 25, 2007 10:07 AM To: Dus(an Pavlica; mysql@lists.mysql.com Subject: Re: select first letters Seems that work in v. 4.1.21 but not in 5.0.27 Thank you Dusan Dus(an Pavlica wrote: I'm not sure, but I think that greek characters are sorted after English chars so try this: SELECT DISTINCT LEFT(title,1) FROM odigos_details WHERE LEFT(title,1) 'z' ORDER BY title HTH, Dusan nikos napsal(a): Hello list. I want to select discinct the first letters of titles in a UTF8 table but only the greek ones. There are both english and greek charakter titles. How can I exclude the english from selection? My table is: CREATE TABLE `odigos_details` ( `id` int(11) NOT NULL auto_increment, `cat` tinyint(3) default NULL, `territory` tinyint(3) default NULL, `title` varchar(150) default NULL, `story` text, `link` varchar(100) default NULL, `address` varchar(150) default NULL, `tel` varchar(50) default NULL, `fax` varchar(11) default NULL, `photo` varchar(100) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 and I run: SELECT DISTINCT LEFT(title,1) FROM odigos_details ORDER BY title witch is working fine. Thank you. Nikos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select first letters
Jerry do you know if there is a php command that returns ascci number of a letter? I'll want to use chr() command because I want to transfer via link the letter to next page but greek characters transformed to something like %CE%9C. Thank you Jerry Schwartz wrote: It would depend upon the collating sequence for the field. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: nikos [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 25, 2007 10:07 AM To: Dus(an Pavlica; mysql@lists.mysql.com Subject: Re: select first letters Seems that work in v. 4.1.21 but not in 5.0.27 Thank you Dusan Dus(an Pavlica wrote: I'm not sure, but I think that greek characters are sorted after English chars so try this: SELECT DISTINCT LEFT(title,1) FROM odigos_details WHERE LEFT(title,1) 'z' ORDER BY title HTH, Dusan nikos napsal(a): Hello list. I want to select discinct the first letters of titles in a UTF8 table but only the greek ones. There are both english and greek charakter titles. How can I exclude the english from selection? My table is: CREATE TABLE `odigos_details` ( `id` int(11) NOT NULL auto_increment, `cat` tinyint(3) default NULL, `territory` tinyint(3) default NULL, `title` varchar(150) default NULL, `story` text, `link` varchar(100) default NULL, `address` varchar(150) default NULL, `tel` varchar(50) default NULL, `fax` varchar(11) default NULL, `photo` varchar(100) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 and I run: SELECT DISTINCT LEFT(title,1) FROM odigos_details ORDER BY title witch is working fine. Thank you. Nikos
RE: select first letters
The multi-byte extension doesn't seem to include one, but it appears that somebody put one together and posted it in the notes on chr(). http://us2.php.net/manual/en/function.chr.php#69082 Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 _ From: nikos [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 25, 2007 11:41 AM To: Jerry Schwartz; mysql@lists.mysql.com Subject: Re: select first letters Jerry do you know if there is a php command that returns ascci number of a letter? I'll want to use chr() command because I want to transfer via link the letter to next page but greek characters transformed to something like %CE%9C. Thank you Jerry Schwartz wrote: It would depend upon the collating sequence for the field. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: nikos [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 25, 2007 10:07 AM To: Dus(an Pavlica; mysql@lists.mysql.com Subject: Re: select first letters Seems that work in v. 4.1.21 but not in 5.0.27 Thank you Dusan Dus(an Pavlica wrote: I'm not sure, but I think that greek characters are sorted after English chars so try this: SELECT DISTINCT LEFT(title,1) FROM odigos_details WHERE LEFT(title,1) 'z' ORDER BY title HTH, Dusan nikos napsal(a): Hello list. I want to select discinct the first letters of titles in a UTF8 table but only the greek ones. There are both english and greek charakter titles. How can I exclude the english from selection? My table is: CREATE TABLE `odigos_details` ( `id` int(11) NOT NULL auto_increment, `cat` tinyint(3) default NULL, `territory` tinyint(3) default NULL, `title` varchar(150) default NULL, `story` text, `link` varchar(100) default NULL, `address` varchar(150) default NULL, `tel` varchar(50) default NULL, `fax` varchar(11) default NULL, `photo` varchar(100) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 and I run: SELECT DISTINCT LEFT(title,1) FROM odigos_details ORDER BY title witch is working fine. Thank you. Nikos
Re: select first letters
Finaly I use ord(). Thank you. Jerry Schwartz wrote: The multi-byte extension doesn't seem to include one, but it appears that somebody put one together and posted it in the notes on chr(). http://us2.php.net/manual/en/function.chr.php#69082 Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 *From:* nikos [mailto:[EMAIL PROTECTED] *Sent:* Wednesday, April 25, 2007 11:41 AM *To:* Jerry Schwartz; mysql@lists.mysql.com *Subject:* Re: select first letters Jerry do you know if there is a php command that returns ascci number of a letter? I'll want to use chr() command because I want to transfer via link the letter to next page but greek characters transformed to something like %CE%9C. Thank you Jerry Schwartz wrote: It would depend upon the collating sequence for the field. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: nikos [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 25, 2007 10:07 AM To: Dus(an Pavlica; mysql@lists.mysql.com Subject: Re: select first letters Seems that work in v. 4.1.21 but not in 5.0.27 Thank you Dusan Dus(an Pavlica wrote: I'm not sure, but I think that greek characters are sorted after English chars so try this: SELECT DISTINCT LEFT(title,1) FROM odigos_details WHERE LEFT(title,1) 'z' ORDER BY title HTH, Dusan nikos napsal(a): Hello list. I want to select discinct the first letters of titles in a UTF8 table but only the greek ones. There are both english and greek charakter titles. How can I exclude the english from selection? My table is: CREATE TABLE `odigos_details` ( `id` int(11) NOT NULL auto_increment, `cat` tinyint(3) default NULL, `territory` tinyint(3) default NULL, `title` varchar(150) default NULL, `story` text, `link` varchar(100) default NULL, `address` varchar(150) default NULL, `tel` varchar(50) default NULL, `fax` varchar(11) default NULL, `photo` varchar(100) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 and I run: SELECT DISTINCT LEFT(title,1) FROM odigos_details ORDER BY title witch is working fine. Thank you. Nikos
Re: what kind of indices to set up
Hi James, all, James Tu wrote: What do you guys think of this approach... Always query on all 5 columns...and then create a multicolumn index using all 5 columns? From a database point of view, that would be best - but I am not sure whether it fits your application and the users. Some points to note: 1) Currently, only one index is used. 2) It is the optimizer's task to decide which index to use. 3) The optimizer will not use an index whose selectivity is too low. Example for cars: If you have an index over the number of wheels and your statement contains WHERE wheels = 4, the index will not be used, because its entry for 4 contains almost all rows. There are too few tricycles in your data (I assume). 4) If an index is defined over multiple fields, then it can only be used if the statement provides values for the leading fields. To decide the best combination of indexes to create, you will need data (or good guesses) on the WHERE conditions which will be used. You want to support the most frequent statements, while you want to avoid creating indexes which will not be used (sufficiently often), because they need both performance (when you change data) and disk space. On Apr 24, 2007, at 11:42 AM, James Tu wrote: Thanks Mike. So let's say I have in index on each of the columns below...and I do a search for make=5 model=2 body_color=7 tire_type=11 hub_caps_type=1 MySQL will only pick one of them right? Let's say it picks make_index. Then what does it do? Does it scan the entire set of results returned by make=5 to match the other criteria? Effectively, yes. So you want to have an index that has a high selectivity - the better this result set corresponds to the data you want to see, the more it will improve your performance. Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Office: (+49 30) 417 01 487 VoIP: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB data log files
Hi Dan, Thanks for this, fixed the problem perfectly when we applied it. Andrew On Thu, 2007-04-12 at 10:45 -0500, Dan Nelson wrote: In the last episode (Apr 12), Andrew Simpson said: One server had a problem while creating a backup last week. The routine normally takes about 30 seconds, but in this case went on for over 30 minutes. During this, the application was responding correctly to other users. After a reboot, InnoDB has been disabled, which left the data inaccessible. The database was restored using the most recent backup, but all tables are now using the MyISAM engine. [...] The errors are due to InnoDB being disabled. As far as I can tell, this has happened as the InnoDB log file ib_logfile0 is the wrong size. This took me to the description for adding and removing log files at: http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html However, something has happened in this case to expand the file id_logfile0 and instructions are not given on how to clean it out and start again. [...] 070403 16:33:55 InnoDB: Starting shutdown... 070403 16:33:57 InnoDB: Shutdown completed; log sequence number 0 81792952 070403 16:33:57 [Note] C:\www\xampp\mysql\bin\mysqld-nt.exe: Shutdown complete InnoDB: Error: log file C:\www\xampp\mysql\data\ib_logfile0 is of different size 0 134217728 bytes InnoDB: than specified in the .cnf file 0 67108864 bytes! Looks like someone edited the my.cnf file and changed the innodb_log_file_size variable without restarting the server or deleting the old logfiles. Assuming the server had shut down cleanly, you should just have to delete the current logfiles and restart mysql. If the server had crashed, you would probably want to set innodb_log_file_size back to 128M, start*stop mysqld to flush any pending transactions, then lower it down to 64M, delete the logfiles, and start mysql. -- Dan Nelson [EMAIL PROTECTED] - This message has been scanned for viruses and dangerous content by the http://www.orbital.net 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]
FW: MySQL patches from Google
A co-worker sent this to me, thought I'd pass it along here. We do tons of failover/replication and would be eager to see mySQL implment the Google patches in the stock distribution. If anyone needs mission critical, scaleable, and failover clusters, it's Google -- so I have every confidence their patches are solid and worthy of inclusion... -Original Message- Very interesting! Google has been working on MySQL, and just released their patches. I'm going to dig into this stuff and see if it would be useful for us, it sure sounds like it could be. ... snip ... Callaghan said that while Google would like to see its changes become part of an official MySQL release, he and others wanted the public to have access to the modified code. The new features include support for for semi-synchronous replication, mirroring the binlog from a master to a slave, quickly promoting a slave to a master during failover, and keeping InnoDB and replication state on a slave consistent during crash recovery. There are also new manageability features such as new SQL statements for monitoring resource usage by table and account. The patch works with version 4 and Google expects version 5 support shortly. links: http://www.informationweek.com/news/showArticle.jhtml?articleID=199201237 http://news.com.com/8301-10784_3-9712307-7.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: MySQL patches from Google
On 4/25/07, Daevid Vincent [EMAIL PROTECTED] wrote: A co-worker sent this to me, thought I'd pass it along here. We do tons of failover/replication and would be eager to see mySQL implment the Google patches in the stock distribution. If anyone needs mission critical, scaleable, and failover clusters, it's Google -- so I have every confidence their patches are solid and worthy of inclusion... This isn't surprising for Google. They've done the same thing to Linux. I don't know much about Google's infrastructure these days, but several years ago they had a server farm of about 2,000 identical x86 Linux machines serving out search requests. Each machine had a local hard disk containing the most recent copy of the search database. Because of the volume of identical machines, reliability was critical, and Google had a certain flavor of the Linux kernel that they had tested and tuned. I wouldn't be surprised to see Google do the same thing with MySQL. For use internally, they would make some tweaks. What are they using MySQL for? Any massively parallel deployments?
Update question
Please, I nedd help!! I have two tabels: table1: id value table2: id value Both tables has a lot of records with identical IDs. I need to update the table1.value with the table2.value where the id are identical. But I cannot find any UPDATE query that can do this in a single operation. Anyone that can give me a suggestion? I'm using MySQL 4.1.8 -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update question
On Wed, April 25, 2007 23:10, Jørn Dahl-Stamnes wrote: Please, I nedd help!! I have two tabels: table1: id value table2: id value Both tables has a lot of records with identical IDs. I need to update the table1.value with the table2.value where the id are identical. update table1 t1, table2 t2 set t1.value=t2.value where t1.id=t2.id; But I cannot find any UPDATE query that can do this in a single operation. Anyone that can give me a suggestion? I'm using MySQL 4.1.8 -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), 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: FW: MySQL patches from Google
At 02:36 PM 4/25/2007, you wrote: On 4/25/07, Daevid Vincent [EMAIL PROTECTED] wrote: A co-worker sent this to me, thought I'd pass it along here. We do tons of failover/replication and would be eager to see mySQL implment the Google patches in the stock distribution. If anyone needs mission critical, scaleable, and failover clusters, it's Google -- so I have every confidence their patches are solid and worthy of inclusion... This isn't surprising for Google. They've done the same thing to Linux. I don't know much about Google's infrastructure these days, but several years ago they had a server farm of about 2,000 identical x86 Linux machines serving out search requests. Each machine had a local hard disk containing the most recent copy of the search database. So you're saying they had a MySQL database on the same machine as the webserver? Or maybe 1 webserver machine and one MySQL machine? I would have thought a single MySQL database could handle the requests from 25-50 webservers easily. Trying to maintain 2000 copies of the same database requires a lot of disk writes. I know Google today is rumored to have over 100,000 web servers and it would be impossible to have that many databases in sync at all times. Because of the volume of identical machines, reliability was critical, and Google had a certain flavor of the Linux kernel that they had tested and tuned. I wouldn't be surprised to see Google do the same thing with MySQL. For use internally, they would make some tweaks. What are they using MySQL for? Any massively parallel deployments? I believe Google is using MySQL for GMail. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: MySQL patches from Google
On 4/25/07, mos [EMAIL PROTECTED] wrote: At 02:36 PM 4/25/2007, you wrote: On 4/25/07, Daevid Vincent [EMAIL PROTECTED] wrote: A co-worker sent this to me, thought I'd pass it along here. We do tons of failover/replication and would be eager to see mySQL implment the Google patches in the stock distribution. If anyone needs mission critical, scaleable, and failover clusters, it's Google -- so I have every confidence their patches are solid and worthy of inclusion... This isn't surprising for Google. They've done the same thing to Linux. I don't know much about Google's infrastructure these days, but several years ago they had a server farm of about 2,000 identical x86 Linux machines serving out search requests. Each machine had a local hard disk containing the most recent copy of the search database. So you're saying they had a MySQL database on the same machine as the webserver? Or maybe 1 webserver machine and one MySQL machine? I would have thought a single MySQL database could handle the requests from 25-50 webservers easily. Trying to maintain 2000 copies of the same database requires a lot of disk writes. I know Google today is rumored to have over 100,000 web servers and it would be impossible to have that many databases in sync at all times. When I read the article some years ago, I got the impression that it was a custom database solution (i.e. nothing to do with MySQL). If you think about it, for a read-only database where the design was known in advance, nearly anybody on this list could write a database solution in 'C' that would outperform MySQL (generality always has a cost). Additionally, if you think about it, if you have some time to crunch on the data and the data set doesn't change until the next data set is released, you can probably optimize it in ways that are unavailable to MySQL because of the high INSERT cost. There might even be enough time to tune a hash function that won't collide much on the data set involved so that the query cost becomes O(1) rather than O(log N). You can't do that in real time on an INSERT. It may take days to crunch data in that way. My understanding was the Google's search servers had custom software operating on a custom database format. My understanding was also that each search server had a full copy of the database (i.e. no additional network traffic involved in providing search results). As far as keeping 100,000 servers in sync, my guess would be that most of the data is distilled for search by other machines and then it is rolled out automatically in a way to keep just a small fraction of the search servers offline at any one time.
problem restoring from binary log
I'm trying to set up MySQL backup restore using mysqldump and binary logs, so we can run mysqldump infrequently and use binary logs for the daily backups (because mysqldump makes the production database unusable for too long a time to run every night). - I can make full dumps using mysqldump - Binary logging works - I can restore full backups from mysqldump - Restoring binary logs gives me Duplicate entry errors Redhat Enterprise Linux 4, MySQL 5.0.24 installed from the Redhat RPMs. (since our production server runs 5.0.24, I'm playing with that version on my test setup, so that I can be sure whatever procedure I come up with will definitely work on the production server.) I can repeat the problem with this procedure on the test db: - Import a full mysqldump file from the prodution db - flush logs - run a full mysqldump with --flush-logs --master-data=2 - do a bunch of stuff that writes data - drop the database, and recreate it, flushing the log - make a copy of the binary log made since the mysqldump - import the mysqldump I made locally = now I have all data correctly restored up to that point Now, I attempt to restore the binary log and this happens: mysqlbinlog [binlogfile] | mysql -u root -p[db root password] [db name] ERROR 1062 (23000) at line 21: Duplicate entry '16021330' for key 1 I've googled around for similar errors, and found several conflicting reports about bugs related to inserting 0's into auto_increment columns. The insert statement at line 21 in the example above is indeed inserting into a table that has an auto_increment columnm, but it is not inserting a value into that column at all, nor are any earlier statements in the binary log. I've checked the date in the database at the time of the restore and there are no rows in the table where the value of that column is 0 or NULL. I believe what I'm trying to do is a pretty standard way to set up backup and restore for a production mysql database, so it should work. Any ideas? -- Cos (Ofer Inbar) -- [EMAIL PROTECTED] It's been said that if a sysadmin does his job perfectly, he's the fellow that people wonder what he does and why the company needs him, until he goes on vacation.-- comp.unix.admin FAQ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
