RE: duplicate records check
Title: RE: duplicate records check Hi Neil, try something like this SELECT FROM GROUP BY HAVING COUNT(*) > 1 Mit freundlichen Grüssen Frank Kalis Asset Management ProACTIV___ CiV Versicherungen * PB Versicherungen * PB Pensionsfonds AG Neustraße 62, 40721 Hilden tel +49 (0) 21 03-34 - 7282 fax +49 (0) 21 03-34 - 7098 mailto:[EMAIL PROTECTED] internet: www.proactiv.de > -Original Message- > From: Neil Tompkins [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, July 09, 2003 8:42 AM > To: [EMAIL PROTECTED] > Subject: duplicate records check > > > Could any one advise what SQL statement I would need to use, > to check a > table for any duplicate records e.g that contain the same > data within a > field. Note that I haven't got the field as a unqiue field. > > Thanks > Neil > > _ > Stay in touch with absent friends - get MSN Messenger > http://www.msn.co.uk/messenger > > > -- > 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]
duplicate records check
Could any one advise what SQL statement I would need to use, to check a table for any duplicate records e.g that contain the same data within a field. Note that I haven't got the field as a unqiue field. Thanks Neil _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb table full
Hi Mixo, How about adding a second innodb file and set the first one to a fixed size ? "...If the disk becomes full you may want to add another data file to another disk, for example. Then you have to look the size of `ibdata1', round the size downward to the closest multiple of 1024 * 1024 bytes (= 1 MB), and specify the rounded size of `ibdata1' explicitly in innodb_data_file_path. After that you can add another datafile: innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend..." (taken from the manual http://www.mysql.com/doc/en/InnoDB_start.html ) Best regards Nils Valentin Tokyo/Japan 2003年 7月 9日 水曜日 15:19、mixo さんは書きました: > The size is already set to 2000M, and I may be wrong, but the autoextend > feature is not support > in mysql version earlier that 4. > > Nils Valentin wrote: > >Hi Mixo, > > > >Do you have the autoextend feature enabled for the innodb table ? > >It can be set f.e in my.cnf. > > > >Best regards > > > >Nils Valentin > >Tokyo/Japan > > > >2003年 7月 8日 火曜日 22:45、mixo さんは書きました: > >>How can I avoid this: > >> > >>DBD::mysql::st execute failed: The table 'Transactions' is full at > >>/usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm > >> > >>The table type is InnoDB. -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb table full
The size is already set to 2000M, and I may be wrong, but the autoextend feature is not support in mysql version earlier that 4. Nils Valentin wrote: Hi Mixo, Do you have the autoextend feature enabled for the innodb table ? It can be set f.e in my.cnf. Best regards Nils Valentin Tokyo/Japan 2003年 7月 8日 火曜日 22:45、mixo さんは書きました: How can I avoid this: DBD::mysql::st execute failed: The table 'Transactions' is full at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm The table type is InnoDB. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bulk loading data
Paul, I have used the ODBC interface as a rough test to experiment with application compatibility with MySQL. I would not consider using ODBC at all to load in a production environment. I want to develop a loading method that is as fast as possible. If I were to use LOAD FILE, my application 1. parses the binary file, 2. converts/outputs a text format, 3. MySQL parses this text format, and 4. converts to its own internal storage. It seems to me that if I could simply eliminate step 2 and 3 the load time would decrease dramatically, especially considering the volume of data. Disk I/O alone for writing and then reading such a large temp file seems to me to be a waste. I have converted these files to text before and they can inflate 10x.. from 10M to 100M! I know I am taking a more difficult path, but load time must be extremely fast for my application. How could I implement this functionality in my application, or extend MySQL to do it? thanks, casey > -Original Message- > From: Paul DuBois [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 08, 2003, 9:46 PM > To: [EMAIL PROTECTED], [EMAIL PROTECTED] > Subject: Re: Bulk loading data > > At 21:36 -0700 7/8/03, cmars wrote: > >Hi, > >I want to insert massive amounts of data in near real-time into a > >MySQL database. The data is in a structured binary format, and I > >have code that parses the data into logical data structures. > > > >Right now I can load into a MySQL database via ODBC, but I need to > >improve throughput. How can I bulk insert the data more efficiently? > > > >I would prefer not to use LOAD FILE because it involves converting > >the binary format to text, which will have a much larger footprint. > >To illustrate the pain of text files in my application, a single > >binary file might contain up to a million rows! And I might load > >hundreds, if not thousands of these files in a day! > > > >How can I load data directly into the database at a lower more > >direct level than LOAD FILE? What are my options to integrate my > >data parser with MySQL? > > I'm curious why you're concerned about the efficiency of LOAD FILE > when you're using ODBC, an API known to add a fair amount of overhead. > > Have you actually tried LOAD FILE and found it empirically to be slower than > ODBC? > > > > > > >thanks, > >casey > > > -- > Paul DuBois, Senior Technical Writer > Madison, Wisconsin, USA > MySQL AB, www.mysql.com > > Are you MySQL certified? http://www.mysql.com/certification/ > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bulk loading data
At 21:36 -0700 7/8/03, cmars wrote: Hi, I want to insert massive amounts of data in near real-time into a MySQL database. The data is in a structured binary format, and I have code that parses the data into logical data structures. Right now I can load into a MySQL database via ODBC, but I need to improve throughput. How can I bulk insert the data more efficiently? I would prefer not to use LOAD FILE because it involves converting the binary format to text, which will have a much larger footprint. To illustrate the pain of text files in my application, a single binary file might contain up to a million rows! And I might load hundreds, if not thousands of these files in a day! How can I load data directly into the database at a lower more direct level than LOAD FILE? What are my options to integrate my data parser with MySQL? I'm curious why you're concerned about the efficiency of LOAD FILE when you're using ODBC, an API known to add a fair amount of overhead. Have you actually tried LOAD FILE and found it empirically to be slower than ODBC? thanks, casey -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bulk loading data
Hi, I want to insert massive amounts of data in near real-time into a MySQL database. The data is in a structured binary format, and I have code that parses the data into logical data structures. Right now I can load into a MySQL database via ODBC, but I need to improve throughput. How can I bulk insert the data more efficiently? I would prefer not to use LOAD FILE because it involves converting the binary format to text, which will have a much larger footprint. To illustrate the pain of text files in my application, a single binary file might contain up to a million rows! And I might load hundreds, if not thousands of these files in a day! How can I load data directly into the database at a lower more direct level than LOAD FILE? What are my options to integrate my data parser with MySQL? thanks, casey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Faster reindexing
At 11:23 +0700 7/9/03, Dominicus Donny wrote: Try analyze your table(s). What information will this yield to make indexing faster? "Me fail English? That's unpossible" ###___Archon___### - Original Message - From: "electroteque" <[EMAIL PROTECTED]> To: "Paul DuBois" <[EMAIL PROTECTED]>; "Florian Weimer" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, July 09, 2003 10:23 AM Subject: RE: Faster reindexing when reimporting or reinserting or whatever from a huge db i usually drop all the indexes reimport then create them again much quicker -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 1:09 PM To: Florian Weimer; [EMAIL PROTECTED] Subject: Re: Faster reindexing At 9:39 +0200 7/7/03, Florian Weimer wrote: >I've got a table with 100 million rows and need some indexes on it >(one row is 126 bytes). > >I'm currently using MyISAM and the indexing proceeds at an >astonishingly low rate: about 200 MB per hour. This is rate is far >too low; if we had to recover the database for some reason, we'd have >to wait for days. > >The table looks like this: > >CREATE TABLE flows ( > versionCHAR NOT NULL, > router CHAR(15) NOT NULL, > src_ip CHAR(15) NOT NULL, > dst_ip CHAR(15) NOT NULL, > protocol TINYINT UNSIGNED NOT NULL, > src_port MEDIUMINT UNSIGNED NOT NULL, > dst_port MEDIUMINT UNSIGNED NOT NULL, > packetsINTEGER UNSIGNED NOT NULL, > bytes INTEGER UNSIGNED NOT NULL, > src_if MEDIUMINT UNSIGNED NOT NULL, > dst_if MEDIUMINT UNSIGNED NOT NULL, > src_as MEDIUMINT UNSIGNED NOT NULL, > dst_as MEDIUMINT UNSIGNED NOT NULL, > src_netCHAR(1) NOT NULL, > dst_netCHAR(1) NOT NULL, > direction CHAR(1) NOT NULL, > class CHAR(1) NOT NULL, > start_time CHAR(24), > end_time CHAR(24) >); > >Indexes are created using this statement: > >mysql> ALTER TABLE flows > -> ADD INDEX dst_ip (dst_ip, src_ip), > -> ADD INDEX dst_port (dst_port, start_time), > -> ADD INDEX src_ip (src_ip, start_time), > -> ADD INDEX time (start_time); > >In theory, we could represent the columns router, src_ip, dst_ip, >start_time, end_time using integers of the appropriate size, but this >would make ad-hoc queries harder to type (and porting our applications >would be even more difficult). Perhaps, but as a test, you might add a couple of extra columns to the table, then populate them like this after loading the table: UPDATE flows SET int_src_ip = INET_ATON(src_ip), int_dst_ip = INET_ATON(dst_ip); Then try creating the indexes using int_src_ip and int_dst_ip rather than src_ip and dst_ip. If it's significantly faster, you may want to reconsider whether it might not be worth using INET_ATON(X) in your queries rather than X. > >Should I switch to another table type? It's easy enough to convert the table to, e.g., InnoDB and then create the indexes, so an empirical test should not be difficult. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com > Are you MySQL certified? http://www.mysql.com/certification/ -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Faster reindexing
Try analyze your table(s). "Me fail English? That's unpossible" ###___Archon___### - Original Message - From: "electroteque" <[EMAIL PROTECTED]> To: "Paul DuBois" <[EMAIL PROTECTED]>; "Florian Weimer" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, July 09, 2003 10:23 AM Subject: RE: Faster reindexing > when reimporting or reinserting or whatever from a huge db i usually drop > all the indexes reimport then create them again much quicker > > -Original Message- > From: Paul DuBois [mailto:[EMAIL PROTECTED] > Sent: Wednesday, July 09, 2003 1:09 PM > To: Florian Weimer; [EMAIL PROTECTED] > Subject: Re: Faster reindexing > > > At 9:39 +0200 7/7/03, Florian Weimer wrote: > >I've got a table with 100 million rows and need some indexes on it > >(one row is 126 bytes). > > > >I'm currently using MyISAM and the indexing proceeds at an > >astonishingly low rate: about 200 MB per hour. This is rate is far > >too low; if we had to recover the database for some reason, we'd have > >to wait for days. > > > >The table looks like this: > > > >CREATE TABLE flows ( > > versionCHAR NOT NULL, > > router CHAR(15) NOT NULL, > > src_ip CHAR(15) NOT NULL, > > dst_ip CHAR(15) NOT NULL, > > protocol TINYINT UNSIGNED NOT NULL, > > src_port MEDIUMINT UNSIGNED NOT NULL, > > dst_port MEDIUMINT UNSIGNED NOT NULL, > > packetsINTEGER UNSIGNED NOT NULL, > > bytes INTEGER UNSIGNED NOT NULL, > > src_if MEDIUMINT UNSIGNED NOT NULL, > > dst_if MEDIUMINT UNSIGNED NOT NULL, > > src_as MEDIUMINT UNSIGNED NOT NULL, > > dst_as MEDIUMINT UNSIGNED NOT NULL, > > src_netCHAR(1) NOT NULL, > > dst_netCHAR(1) NOT NULL, > > direction CHAR(1) NOT NULL, > > class CHAR(1) NOT NULL, > > start_time CHAR(24), > > end_time CHAR(24) > >); > > > >Indexes are created using this statement: > > > >mysql> ALTER TABLE flows > > -> ADD INDEX dst_ip (dst_ip, src_ip), > > -> ADD INDEX dst_port (dst_port, start_time), > > -> ADD INDEX src_ip (src_ip, start_time), > > -> ADD INDEX time (start_time); > > > >In theory, we could represent the columns router, src_ip, dst_ip, > >start_time, end_time using integers of the appropriate size, but this > >would make ad-hoc queries harder to type (and porting our applications > >would be even more difficult). > > Perhaps, but as a test, you might add a couple of extra columns to > the table, then populate them like this after loading the table: > > UPDATE flows SET int_src_ip = INET_ATON(src_ip), int_dst_ip = > INET_ATON(dst_ip); > > Then try creating the indexes using int_src_ip and int_dst_ip rather > than src_ip and dst_ip. > > If it's significantly faster, you may want to reconsider whether it might > not be worth using INET_ATON(X) in your queries rather than X. > > > > >Should I switch to another table type? > > It's easy enough to convert the table to, e.g., InnoDB and then > create the indexes, so an empirical test should not be difficult. > > -- > Paul DuBois, Senior Technical Writer > Madison, Wisconsin, USA > MySQL AB, www.mysql.com > > Are you MySQL certified? http://www.mysql.com/certification/ > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Good Database Philosophy Book?
Hi, all Who can recommend me a good tools for testing performance under heavy use of mysql. With large corcurrent queries. -Ares -- Nuclear Science & Technology Virtual Research Center http://nst.pku.edu.cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: "select * from table where field=hex" doesnt work
Hi Paul, 2003年 7月 9日 水曜日 11:17、Paul DuBois さんは書きました: > At 12:31 +0900 7/8/03, Nils Valentin wrote: > >Hi Koh, > > > >Shouldn't the value in the first sample be used like this ? > > > >1) SELECT * FROM table1 WHERE field1 = '0x6100620063006400'; > > > >Correct me if I am wrong. > > You're wrong. :-) > > 0x6100620063006400 is a hex literal, which will be treated as > a string constant or an integer constant depending on context. > > '0x6100620063006400' is a string constant consisting of the > characters '0', 'x', '6', ... Thanks for pointig this out, the second lesson for me today to learn ;-) Best regards Nils Valentin Tokyo/Japan > > >Best regards > > > >Nils Valentin > >Tokyo/Japan > > > >2003îN 7åé 8ì™ â‘ójì™ 11:55ÅAKoh Swee Meng > >Ç„ÇÒÇÕèëÇ´ÇÐǵLJ: > >> i inserted a new record with this SQL > >> "INSERT INTO table1(field1) VALUES(0x6100620063006400)" > >> type of table1.field1 is tinyblob. > >> > >> to retrieve the record, i tried SQL belows, > >> 1) SELECT * FROM table1 WHERE field1 = 0x6100620063006400; > >> 2) SELECT * FROM table1 WHERE field1 LIKE 0x6100620063006400; > >> 1st SQL found nothing, but the 2nd SQL found the exact record. > >> > >> Is this a bug? I am using mysql 3.23.41 in Linux. > >> > >> > >> Regards, > >> > > > Koh Swee Meng > > -- > Paul DuBois, Senior Technical Writer > Madison, Wisconsin, USA > MySQL AB, www.mysql.com > > Are you MySQL certified? http://www.mysql.com/certification/ -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1044 & Error 2003 on 4.0.13 and RH9
Hi Paul, A good lesson to learn ;-). What you are saying actually means that Kevin didnt need to install it again. I must have missed this part in the manual. Thank you for pointing this out. Best regards Nils Valentin Tokyo/Japan 2003年 7月 9日 水曜日 11:14、Paul DuBois さんは書きました: > At 10:35 +0900 7/7/03, Nils Valentin wrote: > >2003îN 7åé 7ì™ åéójì™ 10:28ÅANils Valentin > >Ç„ÇÒÇÕèëÇ´ÇÐǵLJ: > >> Hi Kevin, > >> > >> try this: > >> > >> 1) stop the mysql server > >> 2) mysqld_safe --skip-grant-tables > >> 3) mysql -u root > >> 4) mysql> SHOW GRANTS FOR root; > >> 5) GRANT ALL ON *.* TO 'root'@'%'; > > > > GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'secret' WITH GRANT OPTION; > > > > > >sorry for the mistake. > > Actually, the mistake is a different one: > > When you start the server with --skip-grant-tables, it doesn't read > the grant tables *at all*. That means the GRANT and REVOKE statements > are disabled. (That's the reason for the "unknown command" error > reported in other messages in this thread.) > > However, once you connect to the server, you can issue FLUSH PRIVILEGES > to force the server to r-eread the grant tables into memory. That also > has the effect of re-enabling GRANT and REVOKE. (This is why Victoria > said that FLUSH PRIVILEGES should be used before the GRANT statement.) > > >Best regards > > > >Nils Valentin > >Tokyo/Japan -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Faster reindexing
when reimporting or reinserting or whatever from a huge db i usually drop all the indexes reimport then create them again much quicker -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 1:09 PM To: Florian Weimer; [EMAIL PROTECTED] Subject: Re: Faster reindexing At 9:39 +0200 7/7/03, Florian Weimer wrote: >I've got a table with 100 million rows and need some indexes on it >(one row is 126 bytes). > >I'm currently using MyISAM and the indexing proceeds at an >astonishingly low rate: about 200 MB per hour. This is rate is far >too low; if we had to recover the database for some reason, we'd have >to wait for days. > >The table looks like this: > >CREATE TABLE flows ( > versionCHAR NOT NULL, > router CHAR(15) NOT NULL, > src_ip CHAR(15) NOT NULL, > dst_ip CHAR(15) NOT NULL, > protocol TINYINT UNSIGNED NOT NULL, > src_port MEDIUMINT UNSIGNED NOT NULL, > dst_port MEDIUMINT UNSIGNED NOT NULL, > packetsINTEGER UNSIGNED NOT NULL, > bytes INTEGER UNSIGNED NOT NULL, > src_if MEDIUMINT UNSIGNED NOT NULL, > dst_if MEDIUMINT UNSIGNED NOT NULL, > src_as MEDIUMINT UNSIGNED NOT NULL, > dst_as MEDIUMINT UNSIGNED NOT NULL, > src_netCHAR(1) NOT NULL, > dst_netCHAR(1) NOT NULL, > direction CHAR(1) NOT NULL, > class CHAR(1) NOT NULL, > start_time CHAR(24), > end_time CHAR(24) >); > >Indexes are created using this statement: > >mysql> ALTER TABLE flows > -> ADD INDEX dst_ip (dst_ip, src_ip), > -> ADD INDEX dst_port (dst_port, start_time), > -> ADD INDEX src_ip (src_ip, start_time), > -> ADD INDEX time (start_time); > >In theory, we could represent the columns router, src_ip, dst_ip, >start_time, end_time using integers of the appropriate size, but this >would make ad-hoc queries harder to type (and porting our applications >would be even more difficult). Perhaps, but as a test, you might add a couple of extra columns to the table, then populate them like this after loading the table: UPDATE flows SET int_src_ip = INET_ATON(src_ip), int_dst_ip = INET_ATON(dst_ip); Then try creating the indexes using int_src_ip and int_dst_ip rather than src_ip and dst_ip. If it's significantly faster, you may want to reconsider whether it might not be worth using INET_ATON(X) in your queries rather than X. > >Should I switch to another table type? It's easy enough to convert the table to, e.g., InnoDB and then create the indexes, so an empirical test should not be difficult. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- 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: need help with select statement
At 4:20 -0500 7/7/03, Anthony Scism wrote: I am relatively new at this, but is there any way to perform the following: | obs_date | date | YES | | NULL | | | obs_time | time | YES | | NULL | | | object | varchar(40) | YES | | NULL | | | observing_site | varchar(40) | YES | | NULL | | | constellation | varchar(40) | YES | | NULL | | | seeing | varchar(80) | YES | | NULL | | | magnitude | decimal(6,2) | YES | | NULL | | | temperature| decimal(6,2) | YES | | NULL | | | size | decimal(6,2) | YES | | NULL | | | telescope | varchar(40) | YES | | NULL | | | r_a| varchar(20) | YES | | NULL | | | d_e_c | varchar(20) | YES | | NULL | | | eyepiece | varchar(20) | YES | | NULL | | | magnification | int(11) | YES | | NULL | | | filter | varchar(40) | YES | | NULL | | | observer | varchar(40) | YES | | NULL | | | notes | varchar(254) | YES | | | | | imgurl | varchar(254) | YES | | NULL | | | key| int(11) | | PRI | NULL | auto_increment | | key_pls_email | varchar(100) | | | key+observer | | ++--+--+-+--+--- -+ select * from table where key = 1; or select * from table where key = '1'; both of these return an error of: mysql> select * from observ_log where key=1; ERROR 1064: You have an error in your SQL syntax near 'key=1' at line 1 mysql> select * from observ_log where key='1'; ERROR 1064: You have an error in your SQL syntax near 'key='1'' at line 1 I can not find anything regarding using the primary key in the where expression. key is a reserved word, so write your WHERE clauses like this ... WHERE `key` = 1; ... WHERE `key` = '1'; any help would be greatly appreciated. A Scism [EMAIL PROTECTED] -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Faster reindexing
At 9:39 +0200 7/7/03, Florian Weimer wrote: I've got a table with 100 million rows and need some indexes on it (one row is 126 bytes). I'm currently using MyISAM and the indexing proceeds at an astonishingly low rate: about 200 MB per hour. This is rate is far too low; if we had to recover the database for some reason, we'd have to wait for days. The table looks like this: CREATE TABLE flows ( versionCHAR NOT NULL, router CHAR(15) NOT NULL, src_ip CHAR(15) NOT NULL, dst_ip CHAR(15) NOT NULL, protocol TINYINT UNSIGNED NOT NULL, src_port MEDIUMINT UNSIGNED NOT NULL, dst_port MEDIUMINT UNSIGNED NOT NULL, packetsINTEGER UNSIGNED NOT NULL, bytes INTEGER UNSIGNED NOT NULL, src_if MEDIUMINT UNSIGNED NOT NULL, dst_if MEDIUMINT UNSIGNED NOT NULL, src_as MEDIUMINT UNSIGNED NOT NULL, dst_as MEDIUMINT UNSIGNED NOT NULL, src_netCHAR(1) NOT NULL, dst_netCHAR(1) NOT NULL, direction CHAR(1) NOT NULL, class CHAR(1) NOT NULL, start_time CHAR(24), end_time CHAR(24) ); Indexes are created using this statement: mysql> ALTER TABLE flows -> ADD INDEX dst_ip (dst_ip, src_ip), -> ADD INDEX dst_port (dst_port, start_time), -> ADD INDEX src_ip (src_ip, start_time), -> ADD INDEX time (start_time); In theory, we could represent the columns router, src_ip, dst_ip, start_time, end_time using integers of the appropriate size, but this would make ad-hoc queries harder to type (and porting our applications would be even more difficult). Perhaps, but as a test, you might add a couple of extra columns to the table, then populate them like this after loading the table: UPDATE flows SET int_src_ip = INET_ATON(src_ip), int_dst_ip = INET_ATON(dst_ip); Then try creating the indexes using int_src_ip and int_dst_ip rather than src_ip and dst_ip. If it's significantly faster, you may want to reconsider whether it might not be worth using INET_ATON(X) in your queries rather than X. Should I switch to another table type? It's easy enough to convert the table to, e.g., InnoDB and then create the indexes, so an empirical test should not be difficult. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Disable InnoDB
Thanks for the Info Paul I guess since I'm a newbie and the server's not especially loaded or anything, I'll just leave it be.. (for the time being) Thank Cheers, Mun Heng, Ow H/M Engineering Western Digital M'sia DID : 03-7870 5168 -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 11:02 AM To: Ow Mun Heng; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Disable InnoDB At 9:46 +0800 7/7/03, Ow Mun Heng wrote: >Hi, > > This may be a stupid question but can I still just ask it? > > Why do you want to disable the use of InnoDB tables? If you don't >use it, couldn't the files still be there? It's not like it's a security >risk (or is it?) or that it takes up lots of space? (maybe this is true as >well?) It's not a security risk, but by starting the server with --skip-innodb, you disable the InnoDB storage engine. As a result, the server won't allocate any InnoDB-related buffers and will use less memory while it runs. > > I'm just starting out and my innoDB files are small. > >Cheers, >Mun Heng, Ow >H/M Engineering >Western Digital M'sia >DID : 03-7870 5168 > > >-Original Message- >From: Mike Blezien [mailto:[EMAIL PROTECTED] >Sent: Saturday, July 05, 2003 9:03 AM >To: [EMAIL PROTECTED] >Subject: Re: Disable InnoDB > > >Thanks, that did the trick :) > > >> Jeremy Zawodny wrote: >> On Fri, Jul 04, 2003 at 07:26:54PM -0500, Mike Blezien wrote: >> >>>Hello, >>> >>>Happy 4th to all :) >>> >>>Anyway, I am attempt to temporarily disable InnoDB tables. We just >>>setup on a new RH/Linux 7.3 server that had the MySQL version 4.0.13 >>>from RPM's, pre-installed... but we currently don't need the InnoDB >>>tables but may in the near future... I've commented out all the >>>relavent setting in the my.cnf file in the data >>>dir(/var/lib/mysql/my.cnf).. then restarted the MySQL server, but >>>it's still creates all the data file related to the InnoDB tables. >>> >>>is it not possible to disable the use of InnoDB tables using the >>>my.cnf file or not with RPM's ?? >> >> >> Put "skip-innodb" in your my.cnf file. > > > >-- >MikeBlezien >=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= >Thunder Rain Internet Publishing >Providing Internet Solutions that work! >http://www.thunder-rain.com >Web Hosting >http://www.justlightening.net >Tel: 1(985)902-8484 >MSN: [EMAIL PROTECTED] >=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Disable InnoDB
At 9:46 +0800 7/7/03, Ow Mun Heng wrote: Hi, This may be a stupid question but can I still just ask it? Why do you want to disable the use of InnoDB tables? If you don't use it, couldn't the files still be there? It's not like it's a security risk (or is it?) or that it takes up lots of space? (maybe this is true as well?) It's not a security risk, but by starting the server with --skip-innodb, you disable the InnoDB storage engine. As a result, the server won't allocate any InnoDB-related buffers and will use less memory while it runs. I'm just starting out and my innoDB files are small. Cheers, Mun Heng, Ow H/M Engineering Western Digital M'sia DID : 03-7870 5168 -Original Message- From: Mike Blezien [mailto:[EMAIL PROTECTED] Sent: Saturday, July 05, 2003 9:03 AM To: [EMAIL PROTECTED] Subject: Re: Disable InnoDB Thanks, that did the trick :) >> Jeremy Zawodny wrote: On Fri, Jul 04, 2003 at 07:26:54PM -0500, Mike Blezien wrote: Hello, Happy 4th to all :) Anyway, I am attempt to temporarily disable InnoDB tables. We just setup on a new RH/Linux 7.3 server that had the MySQL version 4.0.13 from RPM's, pre-installed... but we currently don't need the InnoDB tables but may in the near future... I've commented out all the relavent setting in the my.cnf file in the data dir(/var/lib/mysql/my.cnf).. then restarted the MySQL server, but it's still creates all the data file related to the InnoDB tables. is it not possible to disable the use of InnoDB tables using the my.cnf file or not with RPM's ?? Put "skip-innodb" in your my.cnf file. -- MikeBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Web Hosting http://www.justlightening.net Tel: 1(985)902-8484 MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help
At 17:42 -0500 7/6/03, Greg Donald wrote: > my coulmn (id) is an auto_increment coulmn and lets say that i have 100 entries, so the problem is that when i delete all entries on my table, the column (id) starts from 101 ,shouldn't is starts from 1 again! please i need help with this Use truncate table, it will reset the auto_incement. Always? :-) DROP TABLE IF EXISTS t; CREATE TABLE t (i INT NOT NULL AUTO_INCREMENT PRIMARY KEY) TYPE = INNODB; INSERT INTO t SET i = NULL; INSERT INTO t SET i = NULL; INSERT INTO t SET i = NULL; SELECT * FROM t; TRUNCATE TABLE t; INSERT INTO t SET i = NULL; SELECT * FROM t; Here's what I get: +---+ | i | +---+ | 1 | | 2 | | 3 | +---+ +---+ | i | +---+ | 4 | +---+ -- Greg Donald http://destiney.com/ -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: creating table type innodb
At 12:58 -0500 7/8/03, azamka wrote:
Hi everyone,
I am trying to create tables with type innodb. I created a database and all
the tables of type ISAM. Now I am at the point of making relations and
establish refrential integrity. For that ofcourse I have to change the tables
in the Innodb type. I did that with the commad "alter table tablename type =
innodb". Command runs fine but the table type doesnt change. I tried to create
a new table of type innodb but its not working either. I dont know what the
problem is. Do we have any command to enable the innodb property or what?? I
am stuck badly. Please help.
Your server may have no InnoDB capabilities. What do you see for
the output of this query:
SHOW VARIABLES LIKE 'have_innodb';
It should say "YES". If it's "NO", your server does not support InnoDB
at all. If it's "DISABLED", your server supports InnoDB but InnoDB was
disabled at server startup time; in this case, look for a skip-innodb
in your option files, remove it, and restart the server.
If your server doesn't support InnoDB ("NO"), you need to get one that
does. Prior to MySQL 3.23, you need MySQL Max. As of MySQL 4.0, InnoDB
support is standard.
Thank you in advance
Kamran
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: concat() differences between mssql and mysql
At 13:11 -0700 7/8/03, Daevid Vincent wrote: Hmmm. Well you might be able to work some magic if you know the length of the field it's supposed to be, then you could subtract the length of the Field and then pad with spaces. If this is to be displayed in a web page, and I assume you're trying to line things up pretty, just put them in table cells. If it's output to the terminal window, then try using a "\t" character to tab to the next spot for columnar output. Another option, depending on the application, might be to use RPAD(str,length-you-want,' ') Daevid Vincent http://daevid.com -Original Message- From: Ooks Server [mailto:[EMAIL PROTECTED] Sent: Monday, July 07, 2003 4:38 PM To: [EMAIL PROTECTED] Subject: concat() differences between mssql and mysql I've run into a problem with the behavior of concat(). If I have two fields, char(10), and I do this: concat(field1,fields) With MSSQL I get both fields including trailing spaces. With MYSql, I get the two fields with the trailing spaces trimmed. Example: Field1 = "abc " Field2 = "qwerty" MSSQL -> concat( field1, fields) -> "abc qwerty" MYSQL -> concat( field1, fields) -> "abcqwerty" How do I get Mysql to behave like MSSQL does? I need it to concatenate the > fields without stripping the trailing spaces. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cloning a Database
At 19:58 +0200 7/8/03, Boris Villazon wrote: Hi I need to know if is there anyway to clone a existing database (structure and data)? If yes, how can I do this? I looking for a SQL command. There is no single SQL statement to do this. You can use mysqldump to generate a file containing multiple SQL statements that you can load into mysql to create a copy of the database. Thanks in advance and best regards Boris -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Get equivalents via SQL?
I have a table like this (table_gender): gender_id gender_description --- 0 female 1 male I also have a table like this (table_post): post_id post_gender1post_gender2 --- --- 21 0 1 22 0 0 23 1 0 Is there any way that I can pull a singe row from table_post (for e.g. row 21) and have the results look like: post_id gender1 gender2 --- 21 female male Thank you very much! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Root@?
Paul, Your probably right about him running on windows, but I have also seen this in other situations running linux. In fact, and yes I am almost ashamed to say, I even set one up this way in my earlier days. It was on a local network with no internet, and the "users" were on a custom app connecting through libmysql.dll. I know, not the smartest thing to do but the "users" had no Idea about computers and probabaly didn't even know there was a DB server. So let the tongue lashing commence. Roger > -Original Message- > From: Paul DuBois [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 08, 2003 10:02 PM > To: Roger Davis; [EMAIL PROTECTED] > Subject: RE: [EMAIL PROTECTED] > > > At 21:17 -0400 7/8/03, Roger Davis wrote: > >localhost does not mean 127.0.0.1 Localhost is the socket to > connect to and > >has is separate from IP addresses. The % is for IP address > access. That is > >why there are 2 ways. I would hardly call that redundant. > > > >Roger > > That's true on Unix, but if Jim (the original poster) has entries > for [EMAIL PROTECTED] and [EMAIL PROTECTED], then he's probably running on Windows, > and Unix sockets are irrelevant. localhost can be used for named pipe > connections, though: the [EMAIL PROTECTED] entry will work for that, > whereas [EMAIL PROTECTED] will not. > > Note too that because [EMAIL PROTECTED] allows access to any remote host anywhere, > it's particularly important to make sure either that that account has > a password or that you remove it. Otherwise your server is wide open. > > > > > > >> -Original Message- > >> From: Joel Rees [mailto:[EMAIL PROTECTED] > >> Sent: Tuesday, July 08, 2003 8:30 PM > >> To: [EMAIL PROTECTED] > >> Subject: Re: [EMAIL PROTECTED] > >> > >> > >> > Not sure where these came from, but in my users I've got a > >> [EMAIL PROTECTED] and a > >> > [EMAIL PROTECTED] Is this redundant, or is there a reason for this? > >> > >> Well, some people might say it's redundant, but it _is_ by design -- > >> > >> http://www.mysql.com/doc/en/Privileges.html > >> > >> > ... > >> > >> -- > >> Joel Rees, programmer, Kansai Systems Group > >> Altech Corporation (Alpsgiken), Osaka, Japan > > > http://www.alpsgiken.co.jp > > > -- > Paul DuBois, Senior Technical Writer > Madison, Wisconsin, USA > MySQL AB, www.mysql.com > > Are you MySQL certified? http://www.mysql.com/certification/ > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.497 / Virus Database: 296 - Release Date: 7/4/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.497 / Virus Database: 296 - Release Date: 7/4/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create table...checksum = 1
At 9:35 -0400 7/8/03, DePhillips, Michael P wrote: Hey Folks I'm having a tough time finding any information in the available documentation about this feature. Does anyone out there have a bit of insight as to what exactly it does. Docs says MySQL will store a checksum for each row if this is 'on' - that's about all I can find. That's correct. It causes a slight slowdown for updates (the checksum must be recalculated) but improves table checking operations (e.g., CHECK TABLE). It's only for MyISAM tables. Some general questions are: Where is it stored? Is it assessable? No, which means the answer to the question before this one is "it doesn't matter because you can't get at it." :-) How is the checksum verified between two databases - e.g., in a master/slave environment? Which technology is used (MD5 etc.)? I'm not sure. I believe it may be a CRC algorithm. MD5 would be overkill for this kind of thing. An even more general is, what would be a scenario in which I would use this feature? I'm thinking it would be nice to verify data traveling across a client server environmentbut I may be wrong. It's for internal use only. If you want to verify data, you can institute your own application-level checksums. Any insight? Thanks Mike -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where is 'sqlplus.hh' ?
Peter hh is supposed to be a C++ header file "In some cases hh is a precompiled header" I would check out your make script or Ant build.xml to see how it's used Is there an accompanying .h file e.g. sqlplus.h file? Martin - Original Message - From: "Peter Moscatt" <[EMAIL PROTECTED]> To: "MySQL LIST" <[EMAIL PROTECTED]> Sent: Tuesday, July 08, 2003 10:10 PM Subject: Where is 'sqlplus.hh' ? > I have just installed MySQL++ 1.7.9 using the RPMs > from Rpmfind.com. > > It installed all Ok. The reason why I installed the > RPM over the source was that I can't get the source to > compile. > > When I go and search the drive after the installation > I don't see the 'sqlplus.hh' > > I am using Mandrake 9.1. > > Where have I gone wrong ? > > Pete > > > __ > Do you Yahoo!? > SBC Yahoo! DSL - Now only $29.95 per month! > http://sbc.yahoo.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: "select * from table where field=hex" doesnt work
At 12:31 +0900 7/8/03, Nils Valentin wrote: Hi Koh, Shouldn't the value in the first sample be used like this ? 1) SELECT * FROM table1 WHERE field1 = '0x6100620063006400'; Correct me if I am wrong. You're wrong. :-) 0x6100620063006400 is a hex literal, which will be treated as a string constant or an integer constant depending on context. '0x6100620063006400' is a string constant consisting of the characters '0', 'x', '6', ... Best regards Nils Valentin Tokyo/Japan 2003îN 7åé 8ì âójì 11:55ÅAKoh Swee Meng ÇÇÒÇÕèëÇ´ÇÐǵÇ: i inserted a new record with this SQL "INSERT INTO table1(field1) VALUES(0x6100620063006400)" type of table1.field1 is tinyblob. to retrieve the record, i tried SQL belows, 1) SELECT * FROM table1 WHERE field1 = 0x6100620063006400; 2) SELECT * FROM table1 WHERE field1 LIKE 0x6100620063006400; 1st SQL found nothing, but the 2nd SQL found the exact record. Is this a bug? I am using mysql 3.23.41 in Linux. Regards, > Koh Swee Meng -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User Permissions
I'm kinda new to administering MySQL. I have a remote db that I want to backup to my local MySQL server (windows). I can do a dump of the db from phpMyAdmin on the remote server. I try running it on my local server through the MySQL Control Center, and it says "Read Only". I don't have to enter password for [EMAIL PROTECTED] to login to the Control Center. I assume I don't have permissions to run DDL scripts as this user, and I can't figure out how to change this users permissions or create a new user with different privileges. Someone please help. I need to set this notebook up so I can work while out of town this week. :-) Thanks, Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1044 & Error 2003 on 4.0.13 and RH9
At 10:35 +0900 7/7/03, Nils Valentin wrote: 2003îN 7åé 7ì åéójì 10:28ÅANils Valentin ÇÇÒÇÕèëÇ´ÇÐǵÇ: Hi Kevin, try this: 1) stop the mysql server 2) mysqld_safe --skip-grant-tables 3) mysql -u root 4) mysql> SHOW GRANTS FOR root; 5) GRANT ALL ON *.* TO 'root'@'%'; GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'secret' WITH GRANT OPTION; sorry for the mistake. Actually, the mistake is a different one: When you start the server with --skip-grant-tables, it doesn't read the grant tables *at all*. That means the GRANT and REVOKE statements are disabled. (That's the reason for the "unknown command" error reported in other messages in this thread.) However, once you connect to the server, you can issue FLUSH PRIVILEGES to force the server to r-eread the grant tables into memory. That also has the effect of re-enabling GRANT and REVOKE. (This is why Victoria said that FLUSH PRIVILEGES should be used before the GRANT statement.) Best regards Nils Valentin Tokyo/Japan -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Where is 'sqlplus.hh' ?
I have just installed MySQL++ 1.7.9 using the RPMs from Rpmfind.com. It installed all Ok. The reason why I installed the RPM over the source was that I can't get the source to compile. When I go and search the drive after the installation I don't see the 'sqlplus.hh' I am using Mandrake 9.1. Where have I gone wrong ? Pete __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble creating users
At 8:55 -0700 7/8/03, Michael Iatauro wrote: I took once moe a sharp look now that you did a reply with another sample. Actually I was wrong the SQL statement was NOT correct - you forgot the semicolon in the first exapmple ;-) To my knowledge, the semicolon is unnecessary when using mysql --execute. That's true -- unless you're using that option to execute multiple queries. :-) Then you need semicolons as separators. For example: mysql --execute "SELECT NOW();SELECT USER()" ~Michael Iatauro -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL database gets slower over time
At 14:35 -0700 7/8/03, Steve Quezadas wrote: > have you done any "optimize" to your tables ? it should help improving the query performance. it's normal to have memory lower on each day, because your index files are growing, and takes memory. if you're not using innodb or bdb, you can try to run "flush threads" and "flush tables". it might release some memory. ;-) Yeah, that's the thing, the tables don't change. I add maybe 50,000 records in total every three months, but that's about it. Also, no one USES the databse. only like one a day for a few queries. I am thinking that redhat linux, and not mysql, is the culprit since the top reveals that the buffer size is increasing in memory. It's just weird that it gets slower over time, I suspect it's due to RAM. I am using myISAM tables. Is that innodb or bdb? Neither. MyISAM, InnoDB, and BDB are three different storage engines supported by MySQL. - Steve -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Root@?
At 21:17 -0400 7/8/03, Roger Davis wrote: localhost does not mean 127.0.0.1 Localhost is the socket to connect to and has is separate from IP addresses. The % is for IP address access. That is why there are 2 ways. I would hardly call that redundant. Roger That's true on Unix, but if Jim (the original poster) has entries for [EMAIL PROTECTED] and [EMAIL PROTECTED], then he's probably running on Windows, and Unix sockets are irrelevant. localhost can be used for named pipe connections, though: the [EMAIL PROTECTED] entry will work for that, whereas [EMAIL PROTECTED] will not. Note too that because [EMAIL PROTECTED] allows access to any remote host anywhere, it's particularly important to make sure either that that account has a password or that you remove it. Otherwise your server is wide open. -Original Message- From: Joel Rees [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 8:30 PM To: [EMAIL PROTECTED] Subject: Re: [EMAIL PROTECTED] > Not sure where these came from, but in my users I've got a [EMAIL PROTECTED] and a > [EMAIL PROTECTED] Is this redundant, or is there a reason for this? Well, some people might say it's redundant, but it _is_ by design -- http://www.mysql.com/doc/en/Privileges.html > ... -- Joel Rees, programmer, Kansai Systems Group Altech Corporation (Alpsgiken), Osaka, Japan > http://www.alpsgiken.co.jp -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Root@?
From: http://www.mysql.com/doc/en/Can_not_connect_to_server.html "A MySQL client on Unix can connect to the mysqld server in two different ways: Unix sockets, which connect through a file in the file system (default `/tmp/mysqld.sock') or TCP/IP, which connects through a port number. Unix sockets are faster than TCP/IP but can only be used when connecting to a server on the same computer. Unix sockets are used if you don't specify a hostname or if you specify the special hostname localhost." So it means, from the machine's console. 1. mysql -h localhost -p (means connect to the mysql server through the socket file) 2. mysql -p (means connect to the mysql server through the socket file) 3. mysql -h 127.0.0.1 -p (means connect to the mysql server through the IP Port) 4. mysql -h 192.168.0.1 -p (means connect to the mysql server through the IP Port) 1 & 2 you need privileges like [EMAIL PROTECTED] 3 you need privileges like [EMAIL PROTECTED] or [EMAIL PROTECTED] 4 you need privileges like [EMAIL PROTECTED] or [EMAIL PROTECTED] Hope this helps Roger > -Original Message- > From: Jim McAtee [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 08, 2003 9:27 PM > To: Roger Davis; [EMAIL PROTECTED] > Subject: Re: [EMAIL PROTECTED] > > > Roger Davis wrote: > > > localhost does not mean 127.0.0.1 Localhost is the socket to > connect to and > > has is separate from IP addresses. > > Does this mean from the machine's console, or something else? > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > --- > Incoming mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.497 / Virus Database: 296 - Release Date: 7/4/2003 > --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.497 / Virus Database: 296 - Release Date: 7/4/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Root@?
Roger Davis wrote: > localhost does not mean 127.0.0.1 Localhost is the socket to connect to and > has is separate from IP addresses. Does this mean from the machine's console, or something else? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Root@?
localhost does not mean 127.0.0.1 Localhost is the socket to connect to and has is separate from IP addresses. The % is for IP address access. That is why there are 2 ways. I would hardly call that redundant. Roger > -Original Message- > From: Joel Rees [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 08, 2003 8:30 PM > To: [EMAIL PROTECTED] > Subject: Re: [EMAIL PROTECTED] > > > > Not sure where these came from, but in my users I've got a > [EMAIL PROTECTED] and a > > [EMAIL PROTECTED] Is this redundant, or is there a reason for this? > > Well, some people might say it's redundant, but it _is_ by design -- > > http://www.mysql.com/doc/en/Privileges.html > > > ... > > -- > Joel Rees, programmer, Kansai Systems Group > Altech Corporation (Alpsgiken), Osaka, Japan > http://www.alpsgiken.co.jp > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > --- > Incoming mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.497 / Virus Database: 296 - Release Date: 7/4/2003 > --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.497 / Virus Database: 296 - Release Date: 7/4/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqldump
Hi Kalle, Try mysqldump databasename -uusername -ppassword > /to/a/directory/dump.sql NO SPACE between the -p and the password -Hassan -Original Message- From: Kalle Saarinen [mailto:[EMAIL PROTECTED] Sent: Monday, July 07, 2003 10:06 PM To: MySQL Subject: mysqldump Hello I'm trying to make mysqldump in a shell script but I can't deliver password to sql server. I have command in the script: mysqldump databasename -u username -p password > /to/a/directory/dump.sql When i run the script it asks for a password and takes the password given in the script for a table name. How can I give the password in the script??? mysqldump --help wasn't wery helpful... -Kalle Thanks -- 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: backup / superuser only
did you try the --skip-networking option? This option still allows local connections, so, not sure if it serves your purpose though... HTH, SB -Original Message- From: Rudy Metzger [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 10:08 AM To: [EMAIL PROTECTED] Subject: backup / superuser only Dear all, Is there a way to start mysqld in a way, that say only a superuser can connect. I would need this e.g. for restoring a backup, because I do not want users to connect to the server while I restore the binary log. I already check the options for mysqld but could not find anything (except changing the port and using this port for backup, hoping noone connects to the new port or to switch off tcp/ip at all and only connect through unix sockets). Thanx for any hint!!! Cheers /rudy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Root@?
> Not sure where these came from, but in my users I've got a [EMAIL PROTECTED] and a > [EMAIL PROTECTED] Is this redundant, or is there a reason for this? Well, some people might say it's redundant, but it _is_ by design -- http://www.mysql.com/doc/en/Privileges.html > ... -- Joel Rees, programmer, Kansai Systems Group Altech Corporation (Alpsgiken), Osaka, Japan http://www.alpsgiken.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Good Database Philosophy Book?
Joe Celko's SQL for Smarties: Advanced SQL Programming By Joe Celko -Original Message- From: David Thompson [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 4:21 AM To: 'MySQL Mailing List' Subject: Good Database Philosophy Book? Looking for a book to help me develop a philosophy for building databases (particularly on MySQL). And then taking them from concept to construction. Something like Start by asking which queries you will be performing, then define all data needed for each of those queries, then progress to normalize this data, etc. Any Ideasthanks... Regards, David Thompson President Provoix Corporation - Business Web Solutions www.provoix.com --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.497 / Virus Database: 296 - Release Date: 7/4/2003 -- 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: An irritation about win XP and mysql
At 18:36 08-07-2003 -0400, Larry R. Sieting wrote: Hi, When I delete both my.cnf from the root (C:\) directory and my.ini from windows (C:\WINDOWS), Mysql will restart as sson as I log in to WinMysqlAdmin. I dont want to use that program... Anyway... any suggestions? Go to start menu -> open and delete the short cut for that tool. -- Regards, For technical support contracts, visit https://order.mysql.com/ Are you MySQL certified?, http://www.mysql.com/certification/ Miguel Angel Solórzano <[EMAIL PROTECTED]> São Paulo - Brazil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Good Database Philosophy Book?
Looking for a book to help me develop a philosophy for building databases (particularly on MySQL). And then taking them from concept to construction. Something like Start by asking which queries you will be performing, then define all data needed for each of those queries, then progress to normalize this data, etc. Any Ideasthanks... Regards, David Thompson President Provoix Corporation - Business Web Solutions www.provoix.com --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.497 / Virus Database: 296 - Release Date: 7/4/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb table full
Hi Mixo, Do you have the autoextend feature enabled for the innodb table ? It can be set f.e in my.cnf. Best regards Nils Valentin Tokyo/Japan 2003年 7月 8日 火曜日 22:45、mixo さんは書きました: > How can I avoid this: > > DBD::mysql::st execute failed: The table 'Transactions' is full at > /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm > > The table type is InnoDB. -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble creating users
2003年 7月 9日 水曜日 00:55、Michael Iatauro さんは書きました: > > I took once moe a sharp look now that you did a reply with another > > sample. Actually I was wrong the SQL statement was NOT correct - you > > forgot the semicolon in the first exapmple ;-) > > To my knowledge, the semicolon is unnecessary when using mysql --execute. Hmmm, then I am lost as well. ;-) That was the only thing left really I could think of for now. Best regards > > ~Michael Iatauro -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Root@?
Not sure where these came from, but in my users I've got a [EMAIL PROTECTED] and a [EMAIL PROTECTED] Is this redundant, or is there a reason for this? The original MySQL install was done by an application we're running, but we've since added additional databases. thanks, Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
design question
This question came up at work today, would like to get some fellow developer's opinions: Scenario 1: 2 tables to track checks in terms of HD space a separate table would have been smaller since the checks table grows at 1000+ per week, but there would only be a handful of returned checks. In terms of cpu overhead, 90% of ops would have gotten a checkid from the checks table and then a search on the actions table indexed on checkid. Since there would be few actions per checks it shouldn't have been too bad. CREATE TABLE returned_checks ( returned_checksid int(11) NOT NULL auto_increment, webm_entityid int(11) NOT NULL default '0', checkno int(11) NOT NULL default '0', processed datetime NOT NULL default '-00-00 00:00:00', mirr_isdeleted tinyint(3) unsigned NOT NULL default '0', timestamp timestamp(14) NOT NULL, KEY wid (webm_entityid), KEY rid (returned_checksid) ) CREATE TABLE newpayday_checks ( type varchar(16) NOT NULL default '', amount float(9,2) unsigned NOT NULL default '0.00', textamount varchar(128) NOT NULL default '', name varchar(64) NOT NULL default '', webm_entityid int(11) NOT NULL default '0', addr1 varchar(60) NOT NULL default '', addr2 varchar(60) NOT NULL default '', citystatezip varchar(70) NOT NULL default '', country varchar(40) NOT NULL default '', socialsec varchar(15) NOT NULL default '', checkno int(11) NOT NULL default '0', date date NOT NULL default '-00-00', sent datetime NOT NULL default '-00-00 00:00:00', mirr_isdeleted tinyint(1) unsigned NOT NULL default '0', timestamp timestamp(14) NOT NULL, paydayfile datetime NOT NULL default '-00-00 00:00:00', stopped datetime NOT NULL default '-00-00 00:00:00', originalcheckid int(11) NOT NULL default '0', checkid int(11) NOT NULL auto_increment, checkdate date NOT NULL default '-00-00', KEY name (name,date), KEY checkid (checkid), KEY checkno (checkno), KEY mirr_isdeleted (mirr_isdeleted), KEY socialsec (socialsec), KEY webm_entityid (webm_entityid) ) TYPE=MyISAM; Scenario 2: one table to track checks (with a returned date in it) Reasons behind this are: * less duplication of data and indexes therefore less disk space which in turn means less disk IO * less chance of becoming out-of-sync, more data integrity * less joins (since we'll likely be joining this table) means less cpu overhead * less management, one table to maintain * one less entry in the mysql table cache (only holds so many) CREATE TABLE newpayday_checks ( type varchar(16) NOT NULL default '', amount float(9,2) unsigned NOT NULL default '0.00', textamount varchar(128) NOT NULL default '', name varchar(64) NOT NULL default '', webm_entityid int(11) NOT NULL default '0', addr1 varchar(60) NOT NULL default '', addr2 varchar(60) NOT NULL default '', citystatezip varchar(70) NOT NULL default '', country varchar(40) NOT NULL default '', socialsec varchar(15) NOT NULL default '', checkno int(11) NOT NULL default '0', date date NOT NULL default '-00-00', sent datetime NOT NULL default '-00-00 00:00:00', mirr_isdeleted tinyint(1) unsigned NOT NULL default '0', timestamp timestamp(14) NOT NULL, paydayfile datetime NOT NULL default '-00-00 00:00:00', stopped datetime NOT NULL default '-00-00 00:00:00', originalcheckid int(11) NOT NULL default '0', checkid int(11) NOT NULL auto_increment, checkdate date NOT NULL default '-00-00', returned datetime NOT NULL default '-00-00 00:00:00', KEY name (name,date), KEY checkid (checkid), KEY checkno (checkno), KEY mirr_isdeleted (mirr_isdeleted), KEY socialsec (socialsec), KEY webm_entityid (webm_entityid) ) TYPE=MyISAM; Which Scenario in your opinion, is better and why? -Ryan.
Re: backup / superuser only
Hi Rudy, How about changing the port number in my.cnf and restarting the server ? All clients will look on the wrong port number and not be able to connect. That should do it while you take your backup. When finished change the port number back to 3306. Best regards Nils Valentin Tokyo/Japan 2003年 7月 9日 水曜日 02:07、Rudy Metzger さんは書きました: > Dear all, > > > > Is there a way to start mysqld in a way, that say only a superuser can > connect. I would need this e.g. for restoring a backup, because I do not > want users to connect to the server while I restore the binary log. > > > > I already check the options for mysqld but could not find anything > (except changing the port and using this port for backup, hoping noone > connects to the new port or to switch off tcp/ip at all and only connect > through unix sockets). > > > > Thanx for any hint!!! > > > > Cheers > > /rudy -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
An irritation about win XP and mysql
Hello, I have been reading this list for about a month or so, and have picked up some good information. I have this little nagging problem with the install on my machine. I am running an XP box with Apache 1.3.27, PHP 4.3.2 and MySql 4.0.13. This is a dev machine I use all the time. Now, when I switched from console modes to services, Apache is fine, PHP is fine and Mysql works fine as long as I let WinMysqlAdmin run. If I try to use a my.cnf configuration file... mysql will not run. period. I have read thru the docs a few times on the installing mysql on windows... but I cant find anywhere to turn off WinMysqlAdmin. When I delete both my.cnf from the root (C:\) directory and my.ini from windows (C:\WINDOWS), Mysql will restart as sson as I log in to WinMysqlAdmin. I dont want to use that program... Anyway... any suggestions? Larry R. Sieting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQLDriverCS Data Typing problem in tables
> I don't know anything about MySQLDriverCS but if you continue to have > problems, you might try my provider at www.bytefx.com Reggie, You've ended a week's worth of frustration for me! Thanks a million! You've got a great product, even if it's not a release version yet! FYI, I only had to change about 4 lines of code to change over from MySQLDriverCS to your library. --K -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL database gets slower over time
> have you done any "optimize" to your tables ? > it should help improving the query performance. > > it's normal to have memory lower on each day, because your index files are growing, > and takes memory. > if you're not using innodb or bdb, you can try to run "flush threads" and "flush > tables". it might release some memory. ;-) Yeah, that's the thing, the tables don't change. I add maybe 50,000 records in total every three months, but that's about it. Also, no one USES the databse. only like one a day for a few queries. I am thinking that redhat linux, and not mysql, is the culprit since the top reveals that the buffer size is increasing in memory. It's just weird that it gets slower over time, I suspect it's due to RAM. I am using myISAM tables. Is that innodb or bdb? - Steve
RE: "select * from table where field=hex" doesnt work
Hi, Your solution converts the binary field to a string and then compares two strings. You should get rid of any conversion problems by comparing the values as binary data: SELECT * FROM table1 WHERE field1 = BINARY 0x6100620063006400; -- Mikko Noromaa ([EMAIL PROTECTED]) - SQL in Excel, check out ExcelSQL! - see http://www.excelsql.com - > -Original Message- > From: Koh Swee Meng [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 08, 2003 6:07 AM > To: [EMAIL PROTECTED] > Subject: Re: "select * from table where field=hex" doesnt work > > > oh, i found the solution for this, > by doing "SELECT * FROM table1 WHERE STRCMP(field1, > 0x6100620063006400)=0" will work as expected, so no need > to worry that > character %(ASCII 25) will cause any problem. > > > Regards, > Koh Swee Meng > > > - Original Message - > From: "Koh Swee Meng" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Tuesday, July 08, 2003 10:55 AM > Subject: "select * from table where field=hex" doesnt work > > > > i inserted a new record with this SQL > > "INSERT INTO table1(field1) VALUES(0x6100620063006400)" > > type of table1.field1 is tinyblob. > > > > to retrieve the record, i tried SQL belows, > > 1) SELECT * FROM table1 WHERE field1 = 0x6100620063006400; > > 2) SELECT * FROM table1 WHERE field1 LIKE 0x6100620063006400; > > 1st SQL found nothing, but the 2nd SQL found the exact record. > > > > Is this a bug? I am using mysql 3.23.41 in Linux. > > > > > > Regards, > > Koh Swee Meng > > > > > > -- > > 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: creating table type innodb
I am working on mysqld-max. I have created the my.cnf file yet. The manual does not have clear instructions on that. Can you help me in making mysqld-max to work?? I mean can u explain me the procedure if possible. Appreciate it thank U Kamran >= Original Message From "Subhakar Burri" <[EMAIL PROTECTED]> = >There'z no InnoDB mode for a database, it's only for tables and you can create InnoDB tables in any database as long as it supports InnoDB features. Do you have mysqld-max running? > >HTH, >SB > >-Original Message- >From: azamka [mailto:[EMAIL PROTECTED] >Sent: Tuesday, July 08, 2003 11:38 AM >To: Quinlan, Grant; mysql >Subject: RE: creating table type innodb > > >Yeah I though about that too. But i am not able to find the command for >creating a database in an innodb mode.?? DOu have an idea about it Grant??. > >thank u >kamran > > > > >>= Original Message From "Quinlan, Grant" <[EMAIL PROTECTED]> = >>I am a newbie myself, so please someone correct me if I am wrong, but here >>is how I understand it: >>A MySQL installation can support several databases. The system tables are >>always in a system database that uses tables of type ISAM. You create >>another database of type InnoDB, in which you keep your InnoDB tables. >> >>With the ISAM database every table is stored in a separate file. With InnoDB >>all tables in the database are stored in one or more database files, and >>data from single table may be stored within several of these database files. >> >>To use InnoDB tables and other InnoDB features you must first create an >>InnoDB database, then copy your tables into new tables in the InnoDB >>database. >> >> Grant Q >> >>-Original Message- >>From: azamka [mailto:[EMAIL PROTECTED] >>Sent: Tuesday, July 08, 2003 10:59 AM >>To: [EMAIL PROTECTED] >>Subject: creating table type innodb >> >> >>Hi everyone, >> >>I am trying to create tables with type innodb. I created a database and all >>the tables of type ISAM. Now I am at the point of making relations and >>establish refrential integrity. For that ofcourse I have to change the >>tables >>in the Innodb type. I did that with the commad "alter table tablename type = >> >>innodb". Command runs fine but the table type doesnt change. I tried to >>create >>a new table of type innodb but its not working either. I dont know what the >>problem is. Do we have any command to enable the innodb property or what?? I >> >>am stuck badly. Please help. >> >>Thank you in advance >> >>Kamran >> >> >> >>-- >>MySQL General Mailing List >>For list archives: http://lists.mysql.com/mysql >>To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >> >>-- >>MySQL General Mailing List >>For list archives: http://lists.mysql.com/mysql >>To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > >-- >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: Cloning a Database
> I need to know if is there anyway to clone a existing database (structure and > data)? I use mysqlhotcopy myself. http://www.mysql.com/doc/en/mysqlhotcopy.html -- Greg Donald http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.14 Date?
On Tue, Jul 08, 2003 at 12:49:54PM -0700, Christian Nelson wrote: > > Greetings... > > We're eagerly awaiting 4.0.14 since it fixes the SET FOREIGN_KEY_CHECKS > bug. > > http://www.mysql.com/doc/en/InnoDB_news-4.0.14.html says it should be > released in June... it's now July 8th. > > When can we expect 4.0.14 to be released? I spoke with Monty 40 minutes ago (he's sitting 10 feet away) at OSCON. He said it'lll be a day or two, since it's being built right now. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.13: up 35 days, processed 1,086,266,659 queries (353/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.14 Date?
Christian, - Original Message - From: "Christian Nelson" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Tuesday, July 08, 2003 10:51 PM Subject: 4.0.14 Date? > > Greetings... > > We're eagerly awaiting 4.0.14 since it fixes the SET FOREIGN_KEY_CHECKS > bug. > > http://www.mysql.com/doc/en/InnoDB_news-4.0.14.html says it should be > released in June... it's now July 8th. > > When can we expect 4.0.14 to be released? my guess is between July 15th and July 25th. > Thanks in advance... > Christian Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ > -- - > Christian 'xian' Nelson [EMAIL PROTECTED] > - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - > "Don't ask yourself what the world needs. Ask yourself what makes > you come alive, and go do that, because what the world needs is people > who have come alive." -- Howard Thurman > -- - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: creating table type innodb
There'z no InnoDB mode for a database, it's only for tables and you can create InnoDB tables in any database as long as it supports InnoDB features. Do you have mysqld-max running? HTH, SB -Original Message- From: azamka [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 11:38 AM To: Quinlan, Grant; mysql Subject: RE: creating table type innodb Yeah I though about that too. But i am not able to find the command for creating a database in an innodb mode.?? DOu have an idea about it Grant??. thank u kamran >= Original Message From "Quinlan, Grant" <[EMAIL PROTECTED]> = >I am a newbie myself, so please someone correct me if I am wrong, but here >is how I understand it: >A MySQL installation can support several databases. The system tables are >always in a system database that uses tables of type ISAM. You create >another database of type InnoDB, in which you keep your InnoDB tables. > >With the ISAM database every table is stored in a separate file. With InnoDB >all tables in the database are stored in one or more database files, and >data from single table may be stored within several of these database files. > >To use InnoDB tables and other InnoDB features you must first create an >InnoDB database, then copy your tables into new tables in the InnoDB >database. > > Grant Q > >-Original Message- >From: azamka [mailto:[EMAIL PROTECTED] >Sent: Tuesday, July 08, 2003 10:59 AM >To: [EMAIL PROTECTED] >Subject: creating table type innodb > > >Hi everyone, > >I am trying to create tables with type innodb. I created a database and all >the tables of type ISAM. Now I am at the point of making relations and >establish refrential integrity. For that ofcourse I have to change the >tables >in the Innodb type. I did that with the commad "alter table tablename type = > >innodb". Command runs fine but the table type doesnt change. I tried to >create >a new table of type innodb but its not working either. I dont know what the >problem is. Do we have any command to enable the innodb property or what?? I > >am stuck badly. Please help. > >Thank you in advance > >Kamran > > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dropping table using Select result
I have a shopping cart which creates temporary tables in the format of zorder_; These tables are used to hold a customers order while they shop and after checking out, it is dropped. The values of the shopping cart are put together in an HTML table and stored as their history. What I want to do is to run a cron job to delete the orders which were left undone once a day at about midnight. Here is my current query SELECT table_name FROM orders WHERE RIGHT(started,8) < RIGHT(NOW(),8) AND completed < 1 AND LEFT(started,10) = LEFT(NOW(),10); I was hoping to do something like this: DROP TABLE (SELECT table_name FROM orders WHERE RIGHT(started,8) < RIGHT(NOW(),8) AND completed < 1 AND LEFT(started,10) = LEFT(NOW(),10)); But from my understanding, MySQL does not support nested queries. Is there a query-based way to do this... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NAS vs SAN for data directory
Avoid NAS... that's like dealing with mysql via NFS ... Internal raid depending on controller type and disk configuration could be faster than an external SAN... But chances are external SAN has alot more scalability as far as adding more controller, cabinets and disks... If it was my choice and you've had great success with SAN's before as mentioned .. stay the route... On Tue, 8 Jul 2003, SAQIB wrote: > Hello All, > > I am trying to decide which storage I should use for the "Data Directory". > My application has lots of 'SELECT's (80%) and fewer > UPDATEs/INSERTs(20 %). > > I have the following choices of Data storage > > 1) Xiotech SAN (66 Mhz FCAL) > 2) NAS > 3) Internal SCSI 3 RAID 5 > > Will I achieve any better performance using one storage vs the other > storage? > > In the past (for about 2 years), I have I used SAN with 33Mhz FCAL, and > the performance has been quite exceptional. And I have encountered no > problems. > > Any suggestions? > Thanks > > Saqib Ali > http://www.xml-dev.com > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Typical newbie frustrations
You're using Windows, so I can't make any promises because this is what
I'd do under *nix, but when I first set up mySQL if I type:
# mysql -h localhost -p
The password will be denied, because no password is needed. So I'd
type:
# mysql -h localhost
And get in. This is under the /root/, and I can then:
mysql> USE mysql
mysql> UPDATE user SET password = password('pass') WHERE
username='root';
And, of course, on any created database I can
mysql> GRANT ALL PRIVILEGES ON some_db.* TO username;
mysql> FLUSH PRIVILEGES;
Disclaimer: I'm not looking at my reference card and typing rather
quickly so my syntax may be off a little here and there. Hope that
helps!
-Dan
On Tue, 2003-07-08 at 15:15, Kraig Olmstead wrote:
> If there's a FAQ I'm missing, please point me to it.
>
> v 4.0.13-NT
>
> I'm a C++ programmer trying to learn Java and JDBC (Java Database
> Connectivity). Obviously I need a database to bounce off in order to do
> that. Enter MySQL.
>
> I am going in through the command line shell. I am unable to do much
> because I don't seem to have privileges (e.g. create a database). The
> WinMySQLadmin 1.4 tool has my local user and host correct - i.e. they
> match what's in the "my ini Setup" tab of the same tool. I'm able to
> get in using that host and username, but whenever I try to use my
> password I am denied.
>
> I'm guessing that since I don't provide a password I'm some sort of a
> guest. The password I type in in the same as the one listed in the "my
> ini Setup" tab and the users match both that listed in the "Environment"
> tab and "my ini Setup" tab.
>
> My apologies in advance if I'm doing something stupid beyond words here
> or if this is something listed in a FAQ.
>
> I may simply uninstall/reinstall to verify that I have everything set up
> correctly.
>
> KO
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB: Operating system error number 13 in a file operation
Can not make mysql connection - error log: 030708 08:53:48 mysqld started 030708 8:53:49 InnoDB: Operating system error number 13 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 The http://www.innodb.com/ibman.html stated: If something goes wrong in an InnoDB database creation, you should delete all files created by InnoDB. This means all data files, all log files, the small archived log file, and in the case you already did create some InnoDB tables, delete also the corresponding .frm files for these tables from the MySQL database directories. Then you can try the InnoDB database creation again. It is best to start the MySQL server from a command prompt so that you see what is happening. Question: Should I delete these files? and if so, how do you delete files from the command line? Please help, Mark D
RE: concat() differences between mssql and mysql
Hmmm. Well you might be able to work some magic if you know the length of the field it's supposed to be, then you could subtract the length of the Field and then pad with spaces. If this is to be displayed in a web page, and I assume you're trying to line things up pretty, just put them in table cells. If it's output to the terminal window, then try using a "\t" character to tab to the next spot for columnar output. Daevid Vincent http://daevid.com > -Original Message- > From: Ooks Server [mailto:[EMAIL PROTECTED] > Sent: Monday, July 07, 2003 4:38 PM > To: [EMAIL PROTECTED] > Subject: concat() differences between mssql and mysql > > > I've run into a problem with the behavior of concat(). If I > have two fields, > char(10), and I do this: > > concat(field1,fields) > > With MSSQL I get both fields including trailing spaces. With > MYSql, I get > the two fields with the trailing spaces trimmed. Example: > > Field1 = "abc " > Field2 = "qwerty" > > MSSQL -> concat( field1, fields) -> "abc qwerty" > MYSQL -> concat( field1, fields) -> "abcqwerty" > > How do I get Mysql to behave like MSSQL does? I need it to > concatenate the > fields without stripping the trailing spaces. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.0.14 Date?
Greetings... We're eagerly awaiting 4.0.14 since it fixes the SET FOREIGN_KEY_CHECKS bug. http://www.mysql.com/doc/en/InnoDB_news-4.0.14.html says it should be released in June... it's now July 8th. When can we expect 4.0.14 to be released? Thanks in advance... Christian --- Christian 'xian' Nelson [EMAIL PROTECTED] - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - "Don't ask yourself what the world needs. Ask yourself what makes you come alive, and go do that, because what the world needs is people who have come alive." -- Howard Thurman --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Typical newbie frustrations
If there's a FAQ I'm missing, please point me to it. v 4.0.13-NT I'm a C++ programmer trying to learn Java and JDBC (Java Database Connectivity). Obviously I need a database to bounce off in order to do that. Enter MySQL. I am going in through the command line shell. I am unable to do much because I don't seem to have privileges (e.g. create a database). The WinMySQLadmin 1.4 tool has my local user and host correct - i.e. they match what's in the "my ini Setup" tab of the same tool. I'm able to get in using that host and username, but whenever I try to use my password I am denied. I'm guessing that since I don't provide a password I'm some sort of a guest. The password I type in in the same as the one listed in the "my ini Setup" tab and the users match both that listed in the "Environment" tab and "my ini Setup" tab. My apologies in advance if I'm doing something stupid beyond words here or if this is something listed in a FAQ. I may simply uninstall/reinstall to verify that I have everything set up correctly. KO -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: creating table type innodb
Yeah I though about that too. But i am not able to find the command for creating a database in an innodb mode.?? DOu have an idea about it Grant??. thank u kamran >= Original Message From "Quinlan, Grant" <[EMAIL PROTECTED]> = >I am a newbie myself, so please someone correct me if I am wrong, but here >is how I understand it: >A MySQL installation can support several databases. The system tables are >always in a system database that uses tables of type ISAM. You create >another database of type InnoDB, in which you keep your InnoDB tables. > >With the ISAM database every table is stored in a separate file. With InnoDB >all tables in the database are stored in one or more database files, and >data from single table may be stored within several of these database files. > >To use InnoDB tables and other InnoDB features you must first create an >InnoDB database, then copy your tables into new tables in the InnoDB >database. > > Grant Q > >-Original Message- >From: azamka [mailto:[EMAIL PROTECTED] >Sent: Tuesday, July 08, 2003 10:59 AM >To: [EMAIL PROTECTED] >Subject: creating table type innodb > > >Hi everyone, > >I am trying to create tables with type innodb. I created a database and all >the tables of type ISAM. Now I am at the point of making relations and >establish refrential integrity. For that ofcourse I have to change the >tables >in the Innodb type. I did that with the commad "alter table tablename type = > >innodb". Command runs fine but the table type doesnt change. I tried to >create >a new table of type innodb but its not working either. I dont know what the >problem is. Do we have any command to enable the innodb property or what?? I > >am stuck badly. Please help. > >Thank you in advance > >Kamran > > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cloning a Database
couldn't you dump the database and then use the backup to reload the database? -dan On Tue, 2003-07-08 at 13:58, Boris Villazon wrote: > Hi > > I need to know if is there anyway to clone a existing database (structure and data)? > > If yes, how can I do this? I looking for a SQL command. > > Thanks in advance and best regards > > Boris > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: creating table type innodb
I am a newbie myself, so please someone correct me if I am wrong, but here is how I understand it: A MySQL installation can support several databases. The system tables are always in a system database that uses tables of type ISAM. You create another database of type InnoDB, in which you keep your InnoDB tables. With the ISAM database every table is stored in a separate file. With InnoDB all tables in the database are stored in one or more database files, and data from single table may be stored within several of these database files. To use InnoDB tables and other InnoDB features you must first create an InnoDB database, then copy your tables into new tables in the InnoDB database. Grant Q -Original Message- From: azamka [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 10:59 AM To: [EMAIL PROTECTED] Subject: creating table type innodb Hi everyone, I am trying to create tables with type innodb. I created a database and all the tables of type ISAM. Now I am at the point of making relations and establish refrential integrity. For that ofcourse I have to change the tables in the Innodb type. I did that with the commad "alter table tablename type = innodb". Command runs fine but the table type doesnt change. I tried to create a new table of type innodb but its not working either. I dont know what the problem is. Do we have any command to enable the innodb property or what?? I am stuck badly. Please help. Thank you in advance Kamran -- 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: date and time
> Hi, > > can someone explain me the avantage of using date and time, and also can > i set time + XX minutes?? > > Thanx > Anthony Short answer is: You can use the date and time functions and formats. There is one or just use '+' or '-' See chapter 6 in the ref. manual. If you need both date and time use the datetime field type since you will simplify sorting and conditionals. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
date and time
Hi, can someone explain me the avantage of using date and time, and also can i set time + XX minutes?? Thanx Anthony
creating table type innodb
Hi everyone, I am trying to create tables with type innodb. I created a database and all the tables of type ISAM. Now I am at the point of making relations and establish refrential integrity. For that ofcourse I have to change the tables in the Innodb type. I did that with the commad "alter table tablename type = innodb". Command runs fine but the table type doesnt change. I tried to create a new table of type innodb but its not working either. I dont know what the problem is. Do we have any command to enable the innodb property or what?? I am stuck badly. Please help. Thank you in advance Kamran -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cloning a Database
Hi I need to know if is there anyway to clone a existing database (structure and data)? If yes, how can I do this? I looking for a SQL command. Thanks in advance and best regards Boris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: AUTOEXTEND
Subhakar, it is a bug which was fixed in 3.23.57: http://www.mysql.com/doc/en/News-3.23.57.html " D.4.2 Changes in release 3.23.57 (06 Jun 2003) ... SHOW VARIABLES LIKE 'innodb_data_file_path' displayed only the name of the first datafile (bug #468). ... " Regards, Heikki Subject: RE: AUTOEXTEND From: Subhakar Burri Date: Tue, 8 Jul 2003 10:10:26 -0700 Hi, sorry to keep this thread going ... I'm sending you the output of my results ... I don't see the autoextend string in my output. I'm using 3.23.56 I have the following entry in my .cnf file: innodb data file path=innodb data01:5M;innodb data02:5M:autoextend:max:10M And the output of my variables is as follows: | have innodb | YES | innodb additional mem pool size | 5242880 | innodb buffer pool size | 5242880 | innodb data file path | innodb data01 | innodb data home dir| /usr/local/mysql/data innodb | innodb file io threads | 4 | innodb force recovery | 0 | innodb thread concurrency | 8 | innodb flush log at trx commit | 1 | innodb fast shutdown| ON | innodb flush method | | innodb lock wait timeout| 50 | innodb log arch dir | /usr/local/mysql/data innodb/log | innodb log archive | ON | innodb log buffer size | 262144 | innodb log file size| 1048576 | innodb log files in group | 3 | innodb log group home dir | /usr/local/mysql/data innodb/log | innodb mirrored log groups | 1 I noticed Nils version is 4.1 alpha, Is this unavailable in 3.23.56? Moreover, why doesn't the second file (innodb data02)show up? Please advise. Thankx in advance, SB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup / superuser only
I wonder if you could make a backup of your original users table, then make one of JUST your super user... Then when you go to do your back up you would copy your table with JUST your super user in there and go to work... After you are done with the backup copy your old table back and restart mysql or flush the privilages. Not sure if that will even work but it might work for a temp fix. Ryan --- Rudy Metzger <[EMAIL PROTECTED]> wrote: > Dear all, > > Is there a way to start mysqld in a way, that say only a superuser can > connect. I would need this e.g. for restoring a backup, because I do not > want users to connect to the server while I restore the binary log. __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Query Syntax Error
Trevor Sather wrote: > Hello > > The following query used to work when I was using an Access database, > but now that I've moved to MySQL I get a syntax error when I try and > run it: > > SELECT *, (SELECT COUNT (*) > FROM Links > WHERE Links.CAT_ID = Categories.CAT_ID AND LINK_APPROVED = 'Yes') AS > LINK_COUNT FROM Categories ORDER BY CAT_NAME ASC > > The error message is this: > > SQLState: 42000 > Native Error Code: 1064 > [TCX][MyODBC]syntax error near 'SELECT COUNT (*) FROM Links WHERE > Links.CAT_ID = Categories.CAT_ID AND LINK_AP' at line 1 > > Any immediate reactions? Thanks in advance for any help you can give > me... What version are you using? Sub-selects only became available in MySQL 4.1 (I think). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
backup / superuser only
Dear all, Is there a way to start mysqld in a way, that say only a superuser can connect. I would need this e.g. for restoring a backup, because I do not want users to connect to the server while I restore the binary log. I already check the options for mysqld but could not find anything (except changing the port and using this port for backup, hoping noone connects to the new port or to switch off tcp/ip at all and only connect through unix sockets). Thanx for any hint!!! Cheers /rudy
RE: AUTOEXTEND
Hi, sorry to keep this thread going ... I'm sending you the output of my results ... I don't see the autoextend string in my output. I'm using 3.23.56 I have the following entry in my .cnf file: innodb_data_file_path=innodb_data01:5M;innodb_data02:5M:autoextend:max:10M And the output of my variables is as follows: | have_innodb | YES | innodb_additional_mem_pool_size | 5242880 | innodb_buffer_pool_size | 5242880 | innodb_data_file_path | innodb_data01 | innodb_data_home_dir| /usr/local/mysql/data_innodb | innodb_file_io_threads | 4 | innodb_force_recovery | 0 | innodb_thread_concurrency | 8 | innodb_flush_log_at_trx_commit | 1 | innodb_fast_shutdown| ON | innodb_flush_method | | innodb_lock_wait_timeout| 50 | innodb_log_arch_dir | /usr/local/mysql/data_innodb/log | innodb_log_archive | ON | innodb_log_buffer_size | 262144 | innodb_log_file_size| 1048576 | innodb_log_files_in_group | 3 | innodb_log_group_home_dir | /usr/local/mysql/data_innodb/log | innodb_mirrored_log_groups | 1 I noticed Nils version is 4.1 alpha, Is this unavailable in 3.23.56? Moreover, why doesn't the second file (innodb_data02)show up? Please advise. Thankx in advance, SB -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 1:19 AM To: [EMAIL PROTECTED] Subject: Re: AUTOEXTEND Subhakar, Nils already answered the first question. About the second question, there unfortunately is no way to extract only the table name and the type from SHOW TABLE STATUS. MySQL does not store table information into any table, but in .frm files in the database directories under the datadir. This is a known weakness, and should be fixed some time in the future. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: "Subhakar Burri" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, July 08, 2003 1:22 AM Subject: RE: AUTOEXTEND Hi, I tried SHOW VARIABLES LIKE '%innodb%'; but I couldn't find any variables that show information about auto extend property. Please clarify... For my second question, I need only the table_name and table_type information, and avoid the rest, for an instance. I tried the tables_priv table in mysql database, but of no use. Any idea where MYSQL stores the status information inside the database? Thankx in advance, SB -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Monday, July 07, 2003 2:24 PM To: [EMAIL PROTECTED] Subject: Re: AUTOEXTEND Subhakar, - Original Message - From: ""Subhakar Burri"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Monday, July 07, 2003 11:52 PM Subject: AUTOEXTEND > Sorry to post the same question again .. last time when I sent I = > received a couple of mails with some error messages .. so, not sure if = > my question was posted ... > Here we go again .. > > Hi,=20 > > I have a couple of questions, and really appreciate your help ... > > How can I find out if a data file is set to auto-extend without looking = > in my.cnf file? i.e... is there any variable that stores this = > information inside a database that I can query? please try SHOW VARIABLES LIKE '%innodb%'; > I need the table_name & table_type of all the tables in a instance (to = > run check table). Is there a way I can get that info from a single = > query? If not, what's the best way to get that info? Try SHOW TABLE STATUS FROM yourdatabasename; > Thankx in advance, > SB Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql error : 1036
Does the mysql user have write permissions on the files? HA. Mooduto wrote: dear all, what is the problem sql error : 1036 ...table is read only. please help me.. thank's... -- This mail sent through PSP: http://www.polinpdg.ac.id/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with Temporary Table
Hi, I am a newbie I want to create a temporary table and I get the following error message : mysql> CREATE TEMPORARY TABLE tempemp AS SELECT * FROM emp; ERROR 1044: Access denied for user: '@localhost' to database 'test1' Any help would be appreciated Didier [EMAIL PROTECTED] on 06/07/2003 09:29:01 Pour : [EMAIL PROTECTED] cc : [EMAIL PROTECTED], [EMAIL PROTECTED] (ccc : Didier ROS/A/EDFGDF/FR) Objet : Re: [PHP-DB] Create Temporary Table Does the ip address of where you are trying to access the mysql database from (client), change?If you have a dynamic ip address your ISP should have a grant something to the effect of: grant all on database.* to [EMAIL PROTECTED] identified by 'password'; This would allow your client to be any address on the 10.1.X.X network. You can just grant access to the database to [EMAIL PROTECTED] if you want that user to be able to access the database from any ip address. This might have some security implications for you, if it's wide open on the Internet. That's the only reason I can think for intermittent problems. Cheers, ~Steve Phil Dowson wrote: >Hi, > >PHP Version 4.3.1 >Mysql Version 4.0.13 > >When I try and run the following query > >CREATE TEMPORARY TABLE temptable AS SELECT * FROM permtable; > >on each database, the first one will run it with no problems at all. The >second returns the error: > >[server] ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database >'database' > >I have tried running the same query from MySQL at my site, from via the PHP >interface I am working with and also via PHPMyAdmin, each returns the same >error. > >One thing I should note. The CREATE TEMPORARY TABLE syntax does not work, >however if I were to use the CREATE TABLE syntax, it works fine. But I'd >rather use the TEMPORARY tables. > >This problem appears to be intermittent, it will work fine for a few days, >then fail for an undetermined amount of time. I have checked with my ISP, >they are sure that the rights haven't changed, and I have CREATE TEMPORARY >TABLE rights. > >Any ideas why this is intermittent? > >Thanks for your help! > >Phil Dowson > > > > > -- 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]
replication question
I work for a small (but very fast-growing) company, and we're about to start deploying our new website. Because it has a "live inventory" setup, and because our internal tools are so integrated with the ecommerce side of things, I figure that replication is the best tool for us - we'd have a fast database at our colo, and one in the office, and data would be mirrored as needed. I can work around issues that might arise with auto-increment fields when the servers can't talk, but I'm wondering if there is a way to do table-based replication instead of db-based. I'd prefer it if the master machine only paid attention to changes on certain tables from the slave (my code already handles this, but I'm still looking for that extra bit of security - plus as I understand it, if things are read-only they will be a bit faster). Also, is there a good link for how to set up two-way replication? As noted on the FAQ comments section, there is very little information in the documentation. Thanks, -Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sql error : 1036
dear all, what is the problem sql error : 1036 ...table is read only. please help me.. thank's... -- This mail sent through PSP: http://www.polinpdg.ac.id/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble creating users
I took once moe a sharp look now that you did a reply with another sample. Actually I was wrong the SQL statement was NOT correct - you forgot the semicolon in the first exapmple ;-) To my knowledge, the semicolon is unnecessary when using mysql --execute. ~Michael Iatauro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Encryption of user / pass in odbc.ini / alternatives
All; Does anyone know of a more secure way than storing the username and password to the database connection within the odbc.ini ? We are using PHP on one server connecting to a MySQL 4.0 database ? Thanks
RE: NAS vs SAN for data directory
Well, I would avoid NAS as it introduces unwanted overhead when accessing the data. As for SAN versus the internal RAID5, I am not sure, I would say benchmark the two and see which comes out better, or wait to see if there is anyone out there that has dealt with both. Regards, Mike Hillyer www.vbmysql.com > -Original Message- > From: SAQIB [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 08, 2003 8:45 AM > To: [EMAIL PROTECTED] > Subject: NAS vs SAN for data directory > > > Hello All, > > I am trying to decide which storage I should use for the > "Data Directory". > My application has lots of 'SELECT's (80%) and fewer > UPDATEs/INSERTs(20 %). > > I have the following choices of Data storage > > 1) Xiotech SAN (66 Mhz FCAL) > 2) NAS > 3) Internal SCSI 3 RAID 5 > > Will I achieve any better performance using one storage vs the other > storage? > > In the past (for about 2 years), I have I used SAN with 33Mhz > FCAL, and > the performance has been quite exceptional. And I have encountered no > problems. > > Any suggestions? > Thanks > > Saqib Ali > http://www.xml-dev.com > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Query Syntax Error
Hello The following query used to work when I was using an Access database, but now that I've moved to MySQL I get a syntax error when I try and run it: SELECT *, (SELECT COUNT (*) FROM Links WHERE Links.CAT_ID = Categories.CAT_ID AND LINK_APPROVED = 'Yes') AS LINK_COUNT FROM Categories ORDER BY CAT_NAME ASC The error message is this: SQLState: 42000 Native Error Code: 1064 [TCX][MyODBC]syntax error near 'SELECT COUNT (*) FROM Links WHERE Links.CAT_ID = Categories.CAT_ID AND LINK_AP' at line 1 Any immediate reactions? Thanks in advance for any help you can give me... Best wishes Trevor Sather
MySQL Problems Due to Stupid Newbieness
I've done a lot of stuff to try to get mySQL to work. I'm running Mac OS 10.2.6 and Apache webserver. First, I installed mySQL. No problems there. However, not knowing how everything worked, I used PHPmyAdmin to delete the root user. Yes, I know, stupid. Then, realizing my error with the help of a friend, I repeatedly tried to reinstall mySQL to get rid of those users. Didn't work. I tried uninstalling using an uninstall script from some site (entropy.ch or something) and it still didn't work when I reinstalled. I did not dump the database when I did it. I kept getting some socket error and mysqld would not start up. Finally, I deleted my mySQL user on my computer with hopes of replacing it... but I don't know how. If anyone can help me, I would GREATLY appreciate it. Here's the things I need in the order I need them: 1. How do I create the mySQL user on Mac OS X? 2. How can I delete all that stuff about root users and start anew? 3. What can I do about the socket error? #3 may not be an issue once I fix the others. Please consider helping a poor guy out. Thanks, Nick Boudreau -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
NAS vs SAN for data directory
Hello All, I am trying to decide which storage I should use for the "Data Directory". My application has lots of 'SELECT's (80%) and fewer UPDATEs/INSERTs(20 %). I have the following choices of Data storage 1) Xiotech SAN (66 Mhz FCAL) 2) NAS 3) Internal SCSI 3 RAID 5 Will I achieve any better performance using one storage vs the other storage? In the past (for about 2 years), I have I used SAN with 33Mhz FCAL, and the performance has been quite exceptional. And I have encountered no problems. Any suggestions? Thanks Saqib Ali http://www.xml-dev.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql-test-run failure
Hello. Thought I¹d pass this along to whoever cares. Issue: error running the mysql-test-run as logged > Installing Test Databases > Removing Stale Files > Installing Master Databases > mysqld is missing - looked in ../bin > Error: Could not install master test DBs Any suggestions? Details: I installed the package ³Complete MySQL² (MySQL dist.4.0.13) on a Mac OS X 10.2.6 box (PowerBookG3-400Mhz, 768MB) along with the ODBC and JDBC drivers. Initialized and set root password. I¹ve confirmed that MySQL is installed at /Library/MySQL and running, my ecommerce solution (oscommerce.org) works fine, and I can confirm the ../bin folder is located up one level at ³../bin² from the working directory ³/Library/MySQL/mysql-test². So everything works except the mysql-test-run.
Re: Error 1044 & Error 2003 on 4.0.13 and RH9
"Kevin H. Phillips" <[EMAIL PROTECTED]> wrote: > Hi, Nils > I was able to stop the mysqld daemon using the kill command. However, > when I tried to start it using mysqld_safe --skip-grant-tables, a line > would appear saying: > [EMAIL PROTECTED] root]# mysqld_safe --skip-grant-tables > Starting mysqld daemon with databases from /var/lib/mysql > > with the cursor blinking at the left-hand side of the screen with > apparently nothing going on. The daemon apparently *is* started because > I can ping it and get an "alive" signal. > > However, when I try granting options to root, I get an Error 1047: > unknown command. (in the screen where I start the server, while the > cursor is blinking, there will be no command line prompt so I have to > open another terminal screen. If I type anything where the cursor is > blinking in the first screen, nothing happens). > Execute FLUSH PRIVILEGES before using GRANT command. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: efficient table
"awarsd" <[EMAIL PROTECTED]> wrote: > > I tried to look everywhere @ mysql.com but i couldn't my question is > how many records can a table handle so it stays fairly fast? > What is the maximum records it can handle?? Up to 4 billion rows. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: Replication stops for no reason...
- Original Message - From: "Egor Egorov" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, July 08, 2003 7:58 PM Subject: Re: Replication stops for no reason... > "Jeff McKeon" <[EMAIL PROTECTED]> wrote: > > I've seen one other reference to this exact situation but it didn't have > > a resolution. What I've done is created a script that runs every 10 > > minutes via cron and checks a value from both databases and if it's not > > equal then it issues a "slave stop" and "slave start" command on the > > slave machine. > > > > Our DB's don't have a firewall or anything between them. Does anyone > > know if this is a known "bug" in 3.23.41 that is fixed in later > > versions? > > 3.23.41 is about 2 years old and upgrade is recommended in any case. Yep, i upgraded 2 dbservers from 3.23.x to 4.x with no problems... And 'helped' (more precisely, encourage and guide) another admin to upgrade 3 of their dbservers to 4.x As soon as all have the same version, we could set a nicely done daisy chain replication within our servers. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb table full
How can I avoid this: DBD::mysql::st execute failed: The table 'Transactions' is full at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm The table type is InnoDB. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
create table...checksum = 1
Hey Folks I'm having a tough time finding any information in the available documentation about this feature. Does anyone out there have a bit of insight as to what exactly it does. Docs says MySQL will store a checksum for each row if this is 'on' - that's about all I can find. Some general questions are: Where is it stored? Is it assessable? How is the checksum verified between two databases - e.g., in a master/slave environment? Which technology is used (MD5 etc.)? An even more general is, what would be a scenario in which I would use this feature? I'm thinking it would be nice to verify data traveling across a client server environmentbut I may be wrong. Any insight? Thanks Mike
Re: Replication stops for no reason...
"Jeff McKeon" <[EMAIL PROTECTED]> wrote: > I've seen one other reference to this exact situation but it didn't have > a resolution. What I've done is created a script that runs every 10 > minutes via cron and checks a value from both databases and if it's not > equal then it issues a "slave stop" and "slave start" command on the > slave machine. > > Our DB's don't have a firewall or anything between them. Does anyone > know if this is a known "bug" in 3.23.41 that is fixed in later > versions? 3.23.41 is about 2 years old and upgrade is recommended in any case. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Replication stops for no reason...
I've seen one other reference to this exact situation but it didn't have a resolution. What I've done is created a script that runs every 10 minutes via cron and checks a value from both databases and if it's not equal then it issues a "slave stop" and "slave start" command on the slave machine. Our DB's don't have a firewall or anything between them. Does anyone know if this is a known "bug" in 3.23.41 that is fixed in later versions? Jeff McKeon IT Manager Telaurus Communications LLC [EMAIL PROTECTED] (973) 889-8990 ex 209 ***The information contained in this communication is confidential. It is intended only for the sole use of the recipient named above and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication, or any of its contents or attachments, is expressly prohibited. If you have received this communication in error, please re-send it to the sender and delete the original message, and any copy of it, from your computer system. Thank You.*** -Original Message- From: Martin Waite [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 4:37 AM To: Jeff McKeon Cc: MySQL List Subject: RE: Replication stops for no reason... Hi Jeff, We had similar problems caused by replication crossing a firewall with a 5 minute timeout on its access control list. If the replication stream went idle for 5 minutes, the firewall would drop the connection and MySQL wouldn't notice. Our workaround was to run a daemon on the master to replace a row in a special table once per minute - hence keeping the connection open. I think there are some timeout settings in newer MySQL versions which get the slave to reconnect after a period of inactivity. regards, Martin On Mon, 2003-07-07 at 19:20, Jeff McKeon wrote: > UPDATED INFO > > mysql> show slave status \G; > *** 1. row *** > Master_Host: 10.32.1.10 > Master_User: repli > Master_Port: 3306 > Connect_retry: 60 >Log_File: db01tc0927-bin.034 > Pos: 468335571 > Slave_Running: Yes > Replicate_do_db: > Replicate_ignore_db: > Last_errno: 0 > Last_error: >Skip_counter: 0 > 1 row in set (0.00 sec) > > ERROR: > No query specified > > mysql> > > > > Jeff McKeon > IT Manager > Telaurus Communications LLC > [EMAIL PROTECTED] > (973) 889-8990 ex 209 > > ***The information contained in this communication is confidential. It > is intended only for the sole use of the recipient named above and may > be legally privileged. If the reader of this message is not the > intended recipient, you are hereby notified that any dissemination, > distribution or copying of this communication, or any of its contents > or attachments, is expressly prohibited. If you have received this > communication in error, please re-send it to the sender and delete the > original message, and any copy of it, from your computer system. Thank > You.*** > > > > -Original Message- > From: Jeff McKeon > Sent: Monday, July 07, 2003 1:47 PM > To: Mysql List > Subject: Replication stops for no reason... > > > mysql Ver 11.15 Distrib 3.23.41, for pc-linux-gnu (i686) > > I have the following replication setup... > > DB1 --> DB2 --> DB3 > > Every often, replication from DB1 to DB2 just stops. There is no > error messages that I can see. I know it's stopped because I have a > check that runs every 5 minutes to see a certain piece of data in DB1 > matches DB3. > If I issue slave stop and then slave start commnands, it's then fine for > another day or so... > > Any idea how I can track down the cause or where a log may be for > this? > > Thanks, > > Jeff > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Writing multi-threaded mysql application
Hi Loren, Thanks for quick response. The previous application is written by me in ANSI C using mysql API. I'd love to write it in Java if it was first time:) Do you have any suggestion related to MySQL C API? Ganbold At 02:28 AM 7/8/2003 -0700, you wrote: I'm not sure if there are formal resources on this topic, but here are some basic guidelines I have followed in the past: 1) You can imagine that each running thread will have its own MySQL connection. 2) The connection can get created and held for the lifetime of a thread, or, if you plan to have threads created/destroyed repeatedly, then the connection can come from a connection pool. 3) Make sure that MySQL is configured to handle the number of simultaneous open connections you are planning to create. If you are just trying to write an app that has multiple threads accessing MySQL (versus trying to use/learn C++), you may want to consider using Java and the Connector/J JDBC driver. The Jakarta DBCP project gets you no-hassle connection pooling. And if Posix threads are important, the latest 1.4.2 JVM can use the Native Posix Thread Library (NPTL) found on RH9.0. -Loren --- Ganbold <[EMAIL PROTECTED]> wrote: > Hi, > > I'm new to multi-threaded programming and reading the book "Programming > with POSIX Threads" and > trying to understand concepts and coding. > > What I'm trying to do is to rewrite mysql client application (which > calculates ISP dial-up customers' billing) > into multi-threaded version. > > I'm looking for some resources on multi-threaded mysql application > programming and > it will be great if somebody knows some tutorials, samples and howto > documentations on the web. > > I searched the web and didn't find anything related to mysql multi-threaded > programming. > > I tried to look at mysql.cc code but didn't find any mysql_thread_init() > calls in the source. > > thanks in advance, > > Ganbold > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Writing multi-threaded mysql application
I'm not sure if there are formal resources on this topic, but here are some basic guidelines I have followed in the past: 1) You can imagine that each running thread will have its own MySQL connection. 2) The connection can get created and held for the lifetime of a thread, or, if you plan to have threads created/destroyed repeatedly, then the connection can come from a connection pool. 3) Make sure that MySQL is configured to handle the number of simultaneous open connections you are planning to create. If you are just trying to write an app that has multiple threads accessing MySQL (versus trying to use/learn C++), you may want to consider using Java and the Connector/J JDBC driver. The Jakarta DBCP project gets you no-hassle connection pooling. And if Posix threads are important, the latest 1.4.2 JVM can use the Native Posix Thread Library (NPTL) found on RH9.0. -Loren --- Ganbold <[EMAIL PROTECTED]> wrote: > Hi, > > I'm new to multi-threaded programming and reading the book "Programming > with POSIX Threads" and > trying to understand concepts and coding. > > What I'm trying to do is to rewrite mysql client application (which > calculates ISP dial-up customers' billing) > into multi-threaded version. > > I'm looking for some resources on multi-threaded mysql application > programming and > it will be great if somebody knows some tutorials, samples and howto > documentations on the web. > > I searched the web and didn't find anything related to mysql multi-threaded > programming. > > I tried to look at mysql.cc code but didn't find any mysql_thread_init() > calls in the source. > > thanks in advance, > > Ganbold > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem Process table is empty ?
Hi, We use MySQL 4.0.13, Redhat 7.3(kernel 2.4.18.3). Apache 1.3.27 Mysql is alive and there are process but I don't see in ps (process table) and very slow web receive. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: concat() differences between mssql and mysql
Note that trailing spaces are not removed when you insert data into a TEXT (or BLOB for that matter) column. This may be of use to you, but TEXT does have limitations. Andy > -Original Message- > From: Egor Egorov [mailto:[EMAIL PROTECTED] > Sent: 08 July 2003 09:31 > To: [EMAIL PROTECTED] > Subject: Re: concat() differences between mssql and mysql > > > "Ooks Server" <[EMAIL PROTECTED]> wrote: > > I've run into a problem with the behavior of concat(). If I > have two fields, > > char(10), and I do this: > > > > concat(field1,fields) > > > > With MSSQL I get both fields including trailing spaces. With > MYSql, I get > > the two fields with the trailing spaces trimmed. Example: > > > > Field1 = "abc " > > Field2 = "qwerty" > > > > MSSQL -> concat( field1, fields) -> "abc qwerty" > > MYSQL -> concat( field1, fields) -> "abcqwerty" > > > > How do I get Mysql to behave like MSSQL does? I need it to > concatenate the > > fields without stripping the trailing spaces. > > It's a known behaviour of MySQL. MySQL removes trailing spaces at > the end of VARCHAR and CHAR columns: > http://www.mysql.com/doc/en/Open_bugs.html > > > > -- > For technical support contracts, goto https://order.mysql.com/?ref=ensita > This email is sponsored by Ensita.net http://www.ensita.net/ >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Egor Egorov > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ><___/ www.mysql.com > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie question, link tables
"Pedro X Gomes-Fabre" <[EMAIL PROTECTED]> wrote: > > Can I link two fields in two tables, in the way that if I add a new value > into a table in the other table the value is added automatically, No. > or Do I need to add the values one time for every table? Yes. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: ERROR 2002: Can't connect to local MySQL server through socket
Mark Depenbrock <[EMAIL PROTECTED]> wrote: > I have installed a Mac OS X 'PKG' binary package on my OS version > 10.2.6 Mac. > > I have been instructed that after the installation I can start up MySQL > by running the following command lines: > > [Computer:/usr/local/mysql] mark% sudo ./bin/mysqld_safe > Password: > Starting mysqld daemon with databases from /usr/local/mysql/data > 030707 12:31:53 mysqld ended mysqld isn't running. Look in the .err file in the MySQL data dir to see error message. > [Computer:/usr/local/mysql] mark% bg > bg: No current job. > > Instructions state that I should now be able to connect to MySQL server, > e.g. by running > > [Computer:/usr/local/mysql] mark% /usr/local/mysql/bin/mysql > ERROR 2002: Can't connect to local MySQL server through socket > '/tmp/mysql.sock' (61) > > It appears that the server is not running...any suggestions? > > I have checked to see if the mysql.sock exists and it appears to be > there: > [Computer:/usr/local/mysql] mark% ls /tmp > 501 mysql.sock printers printing.462 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: Replication stops for no reason...
Hi Jeff, We had similar problems caused by replication crossing a firewall with a 5 minute timeout on its access control list. If the replication stream went idle for 5 minutes, the firewall would drop the connection and MySQL wouldn't notice. Our workaround was to run a daemon on the master to replace a row in a special table once per minute - hence keeping the connection open. I think there are some timeout settings in newer MySQL versions which get the slave to reconnect after a period of inactivity. regards, Martin On Mon, 2003-07-07 at 19:20, Jeff McKeon wrote: > UPDATED INFO > > mysql> show slave status \G; > *** 1. row *** > Master_Host: 10.32.1.10 > Master_User: repli > Master_Port: 3306 > Connect_retry: 60 >Log_File: db01tc0927-bin.034 > Pos: 468335571 > Slave_Running: Yes > Replicate_do_db: > Replicate_ignore_db: > Last_errno: 0 > Last_error: >Skip_counter: 0 > 1 row in set (0.00 sec) > > ERROR: > No query specified > > mysql> > > > > Jeff McKeon > IT Manager > Telaurus Communications LLC > [EMAIL PROTECTED] > (973) 889-8990 ex 209 > > ***The information contained in this communication is confidential. It > is intended only for the sole use of the recipient named above and may > be legally privileged. If the reader of this message is not the intended > recipient, you are hereby notified that any dissemination, distribution > or copying of this communication, or any of its contents or attachments, > is expressly prohibited. If you have received this communication in > error, please re-send it to the sender and delete the original message, > and any copy of it, from your computer system. Thank You.*** > > > > -Original Message- > From: Jeff McKeon > Sent: Monday, July 07, 2003 1:47 PM > To: Mysql List > Subject: Replication stops for no reason... > > > mysql Ver 11.15 Distrib 3.23.41, for pc-linux-gnu (i686) > > I have the following replication setup... > > DB1 --> DB2 --> DB3 > > Every often, replication from DB1 to DB2 just stops. There is no error > messages that I can see. I know it's stopped because I have a check > that runs every 5 minutes to see a certain piece of data in DB1 matches > DB3. > If I issue slave stop and then slave start commnands, it's then fine for > another day or so... > > Any idea how I can track down the cause or where a log may be for this? > > Thanks, > > Jeff > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Benchmarking
Mixo, you have to add another InnoDB data file. Also adjust other InnoDB parameters in my.cnf to get a good performance. http://www.innodb.com/ibman.html#InnoDB_start " An advanced my.cnf example. Suppose you have a Linux computer with 2 GB RAM and three 60 GB hard disks (at directory paths `/', `/dr2' and `/dr3'). Below is an example of possible configuration parameters in my.cnf for InnoDB. Note that InnoDB does not create directories: you have to create them yourself. Use the Unix or MS-DOS mkdir command to create the data and log group home directories. [mysqld] # You can write your other MySQL server options here # ... innodb_data_home_dir = #Data files must be able to #hold your data and indexes innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend #Set buffer pool size to #50 - 80 % of your computer's #memory, but make sure on Linux #x86 total memory usage is #< 2 GB set-variable = innodb_buffer_pool_size=1G set-variable = innodb_additional_mem_pool_size=20M innodb_log_group_home_dir = /dr3/iblogs #.._log_arch_dir must be the same #as .._log_group_home_dir; starting #from 4.0.6, you can omit it #innodb_log_arch_dir = /dr3/iblogs set-variable = innodb_log_files_in_group=3 #Set the log file size to about #15 % of the buffer pool size set-variable = innodb_log_file_size=150M set-variable = innodb_log_buffer_size=8M #Set ..flush_log_at_trx_commit to #0 if you can afford losing #some last transactions innodb_flush_log_at_trx_commit=1 set-variable = innodb_lock_wait_timeout=50 #innodb_flush_method=fdatasync #set-variable = innodb_thread_concurrency=5 Note that we have placed the two data files on different disks. InnoDB will fill the tablespace formed by the data files from bottom up. In some cases it will improve the performance of the database if all data is not placed on the same physical disk. Putting log files on a different disk from data is very often beneficial for performance. You can also use raw disk partitions (raw devices) as data files. In some Unixes they speed up i/o. See section 12.1 about how to specify them in my.cnf. " http://www.innodb.com/ibman.html#Adding_and_removing " 5 Adding and removing InnoDB data and log files To add a new data file to the tablespace you have to shut down your MySQL database, edit the my.cnf file, adding a new file to innodb_data_file_path, and then start MySQL again. If your last data file was defined with the keyword autoextend, then the procedure to edit my.cnf is the following. You have to look the size of the last data file, round the size downward to the closest multiple of 1024 * 1024 bytes (= 1 MB), and specify the rounded size explicitly in innodb_data_file_path. Then you can add another data file. Remember that only the last data file in the innodb_data_file_path can be specified as auto-extending. An example: We assume you had just one auto-extending data file ibdata1 first, and that file grew to 988 MB. Below a possible line after adding another auto-extending data file. innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend Currently you cannot remove a data file from InnoDB. To decrease the size of your database you have to use mysqldump to dump all your tables, create a new database, and import your tables to the new database. If you want to change the number or the size of your InnoDB log files, you have to shut down MySQL and make sure that it shuts down without errors. Then copy the old log files into a safe place just in case something went wrong in the shutdown and you will need them to recover the database. Delete then the old log files from the log file directory, edit my.cnf, and start MySQL again. InnoDB will tell you at the startup that it is creating new log files. " Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: "mixo" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, July 08, 2003 11:21 AM Subject: Benchmarking > How can I benchmark the perfomance of Mysql with the following setup: > Perl 5.8.0 (perl-DBI, perl-DBI-Mysql) > mysql-3.23.54a-11 > apache-2.0.40-21 > mod_perl-1.99_07-5 > > I want to compare the perfomance of Mysql against that of Pg using
