error 99(?) : can't connect to MySQL server
Hi,
I have an interesting (a.k.a. frustrating) problem on MySQL 4.1.11.
I try to connect to the database via DBD::mysql. Everything works - except in a
few cases (once in every 1 occasions, approximately) I get:
DBI connect('database=test:host=192.168.0.200','test',...) failed: Can't
connect to MySQL server on '192.168.0.200' (99) at con.pl line 14
perror 99 gives me:
OS error code 99: Cannot assign requested address
This doesn't make sense to me. I wrote a test script that looks like this:
for (1..5) {
$dbh = DBI-connect($dsn, $user, $password, {'RaiseError' = 1} );
my $sth = $dbh-prepare('SELECT * FROM users');
}
Periodically I get the above error.
I have tried to be more nice, after upgrading DBD and DBI, etc.:
for (1..5) {
$dbh = DBI-connect($dsn, $user, $password, {'RaiseError' = 1} );
my $sth = $dbh-prepare('SELECT * FROM users');
$sth = undef;
$dbh-disconnect;
$dbh = undef;
}
But the same happens.
Then I straced the program. This is what I got:
restart_syscall(... resuming interrupted call ...) = 0
time(NULL) = 1170758829
socket(PF_INET, SOCK_STREAM, IPPROTO_IP) = 3
fcntl64(3, F_SETFL, O_RDONLY) = 0
fcntl64(3, F_GETFL) = 0x2 (flags O_RDWR)
connect(3, {sa_family=AF_INET, sin_port=htons(3306),
sin_addr=inet_addr(192.168.0.200)}, 16) = -1 EADDRNOTAVAIL (Cannot assign
requested address)
shutdown(3, 2 /* send and receive */) = -1 ENOTCONN (Transport endpoint is
not connected)
close(3)= 0
write(1, Doesn\'t work. Reconnecting in 1 ..., 51) = 51
time(NULL) = 1170758829
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
rt_sigaction(SIGCHLD, NULL, {SIG_DFL}, 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
nanosleep({1, 0}, {1, 0}) = 0
time(NULL) = 1170758830
socket(PF_INET, SOCK_STREAM, IPPROTO_IP) = 3
fcntl64(3, F_SETFL, O_RDONLY) = 0
fcntl64(3, F_GETFL) = 0x2 (flags O_RDWR)
connect(3, {sa_family=AF_INET, sin_port=htons(3306),
sin_addr=inet_addr(192.168.0.200)}, 16) = -1 EADDRNOTAVAIL (Cannot assign
requested address)
shutdown(3, 2 /* send and receive */) = -1 ENOTCONN (Transport endpoint is
not connected)
Anybody seen anything like this?
- Fagzal
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error 99(?) : can't connect to MySQL server
Hi Faygal,
Fagyal Csongor wrote:
for (1..5) {
$dbh = DBI-connect($dsn, $user, $password, {'RaiseError' = 1} );
my $sth = $dbh-prepare('SELECT * FROM users');
}
I think you are simply running out of available outgoing ports with
that. Here is some more insight on that topic:
http://www.mysqlperformanceblog.com/2006/11/12/are-php-persistent-connections-evil/
Don't be confused with the PHP mentioned, the first paragraphs apply to
TCP/IP and mysql as whole.
regards
Nils
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error 99(?) : can't connect to MySQL server
Nils Meyer wrote:
Hi Faygal,
Fagyal Csongor wrote:
for (1..5) {
$dbh = DBI-connect($dsn, $user, $password, {'RaiseError' = 1} );
my $sth = $dbh-prepare('SELECT * FROM users');
}
I think you are simply running out of available outgoing ports with
that. Here is some more insight on that topic:
http://www.mysqlperformanceblog.com/2006/11/12/are-php-persistent-connections-evil/
Don't be confused with the PHP mentioned, the first paragraphs apply
to TCP/IP and mysql as whole.
regards
Nils
Thanks, Nils.
Actually I got to the same conclusion, too... when this error appears, there are something
like 15000 TCP connections around, mostly in TIME_WAIT. Too bad these
connections linger around even after an explicit -disconnect().
I should be using a persistent connection anyway :)
- Csongor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
running sum with a @variable
I want to calc a running sum with @variables. Using the command line client, I enter: SET @row := 0, @runsum := 0; followed by: SELECT @row := @row+1 AS `Row`, mycountries.Name AS `Country` , ROUND(SUM(db.Jan+db.Feb+db.Mar)) AS `Q1` , @runsum := @runsum + SUM(db.Jan+db.Feb+db.Mar) AS `RunSum` FROM data2006 AS db LEFT JOIN mycountries ON db.Country = mycountries.ID WHERE ... GROUP BY db.Country; Results are: Row Country Q1RunSum 1 Germany 9090 2 France 6060 3 Norway 2424 etc. I expect the RunSum for Germany 60, France 150, Norway 174 etc. Whay am I doing wrong ? Any help is appreciated ! Regards, Cor
Re: running sum with a @variable
i suppose this to be working when you leave the group by? On 2/6/07, C.R.Vegelin [EMAIL PROTECTED] wrote: I want to calc a running sum with @variables. Using the command line client, I enter: SET @row := 0, @runsum := 0; followed by: SELECT @row := @row+1 AS `Row`, mycountries.Name AS `Country` , ROUND(SUM(db.Jan+db.Feb+db.Mar)) AS `Q1` , @runsum := @runsum + SUM(db.Jan+db.Feb+db.Mar) AS `RunSum` FROM data2006 AS db LEFT JOIN mycountries ON db.Country = mycountries.ID WHERE ... GROUP BY db.Country; Results are: Row Country Q1RunSum 1 Germany 9090 2 France 6060 3 Norway 2424 etc. I expect the RunSum for Germany 60, France 150, Norway 174 etc. Whay am I doing wrong ? Any help is appreciated ! Regards, Cor -- Lars Schwarz Gottorpstrasse 20 26122 Oldenburg T 0441 2171 354 0 F 0441 2171 354 0 M 0179 512 4628 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Building MySql and Qt from source.
I'm trying out the MySql database connection example from C++ Gui programming with Qt. I'm using the binary build of Qt open-source edition from the books cd, and a binary build of MySql 5.0.27 downloaded from the official MySql website. The Qt libraries includes a MySql driver, which however uses a shared version of the MySql client library. However, there are no shared libraries installed by the mySql installer. I've concluded that to use MySql with Qt, I have to do source builds of both Qt and MySql. Now I wonder how to configure the packages for Mac OS X. I've already found out trying to build Qt for Mac OS X, that the default configure settings are unsuitable. Even if I choose a framework build, the frameworks ends up in a subdirectory of /usr/local, instead of properly inside /Library/Frameworks If you reply to this message please note that it's cross-posted. You might have to edit the adresses, if you are not subscribed to both the Qt and MySql lists. - This sig is dedicated to the advancement of Nuclear Power Tommy Nordgren [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: running sum with a @variable
try to put parenthesis around @runsum := @runsum + SUM(db.Jan+db.Feb+db.Mar) SELECT @row := @row+1 AS `Row`, mycountries.Name AS `Country` , ROUND(SUM(db.Jan+db.Feb+db.Mar)) AS `Q1` , (@runsum := @runsum + SUM(db.Jan+db.Feb+db.Mar)) AS `RunSum` FROM data2006 AS db LEFT JOIN mycountries ON db.Country = mycountries.ID WHERE ... GROUP BY db.Country; HTH, Dusan C.R.Vegelin napsal(a): I want to calc a running sum with @variables. Using the command line client, I enter: SET @row := 0, @runsum := 0; followed by: SELECT @row := @row+1 AS `Row`, mycountries.Name AS `Country` , ROUND(SUM(db.Jan+db.Feb+db.Mar)) AS `Q1` , @runsum := @runsum + SUM(db.Jan+db.Feb+db.Mar) AS `RunSum` FROM data2006 AS db LEFT JOIN mycountries ON db.Country = mycountries.ID WHERE ... GROUP BY db.Country; Results are: Row Country Q1RunSum 1 Germany 9090 2 France 6060 3 Norway 2424 etc. I expect the RunSum for Germany 60, France 150, Norway 174 etc. Whay am I doing wrong ? Any help is appreciated ! Regards, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: running sum with a @variable
oops, sorry, forget the last post i made. it's not related to the group by. it should work like that from my point of view. a stripped down version of this works for me (tried without the left join) On 2/6/07, Lars Schwarz [EMAIL PROTECTED] wrote: i suppose this to be working when you leave the group by? On 2/6/07, C.R.Vegelin [EMAIL PROTECTED] wrote: I want to calc a running sum with @variables. Using the command line client, I enter: SET @row := 0, @runsum := 0; followed by: SELECT @row := @row+1 AS `Row`, mycountries.Name AS `Country` , ROUND(SUM(db.Jan+db.Feb+db.Mar)) AS `Q1` , @runsum := @runsum + SUM(db.Jan+db.Feb+db.Mar) AS `RunSum` FROM data2006 AS db LEFT JOIN mycountries ON db.Country = mycountries.ID WHERE ... GROUP BY db.Country; Results are: Row Country Q1RunSum 1 Germany 9090 2 France 6060 3 Norway 2424 etc. I expect the RunSum for Germany 60, France 150, Norway 174 etc. Whay am I doing wrong ? Any help is appreciated ! Regards, Cor -- Lars Schwarz Gottorpstrasse 20 26122 Oldenburg T 0441 2171 354 0 F 0441 2171 354 0 M 0179 512 4628 -- Lars Schwarz Gottorpstrasse 20 26122 Oldenburg T 0441 2171 354 0 F 0441 2171 354 0 M 0179 512 4628 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: running sum with a @variable
Thanks Lars, Dusan, I found out that the problem is caused by an ORDER BY clause, left out in my example because I had no idea this would be the problem. It works fine with LEFT JOIN and GROUP BY. However, I need the ORDER BY ... Any more suggestions to work around ? Thanks, Cor - Original Message - From: Lars Schwarz [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, February 06, 2007 12:58 PM Subject: Re: running sum with a @variable oops, sorry, forget the last post i made. it's not related to the group by. it should work like that from my point of view. a stripped down version of this works for me (tried without the left join) On 2/6/07, Lars Schwarz [EMAIL PROTECTED] wrote: i suppose this to be working when you leave the group by? On 2/6/07, C.R.Vegelin [EMAIL PROTECTED] wrote: I want to calc a running sum with @variables. Using the command line client, I enter: SET @row := 0, @runsum := 0; followed by: SELECT @row := @row+1 AS `Row`, mycountries.Name AS `Country` , ROUND(SUM(db.Jan+db.Feb+db.Mar)) AS `Q1` , @runsum := @runsum + SUM(db.Jan+db.Feb+db.Mar) AS `RunSum` FROM data2006 AS db LEFT JOIN mycountries ON db.Country = mycountries.ID WHERE ... GROUP BY db.Country; Results are: Row Country Q1RunSum 1 Germany 9090 2 France 6060 3 Norway 2424 etc. I expect the RunSum for Germany 60, France 150, Norway 174 etc. Whay am I doing wrong ? Any help is appreciated ! Regards, Cor -- Lars Schwarz Gottorpstrasse 20 26122 Oldenburg T 0441 2171 354 0 F 0441 2171 354 0 M 0179 512 4628 -- Lars Schwarz Gottorpstrasse 20 26122 Oldenburg T 0441 2171 354 0 F 0441 2171 354 0 M 0179 512 4628 -- 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: running sum with a @variable
Have you tried using the WITH ROLLUP option after the group by clause? It seems to me that might give you something close to what you are looking for. - Original Message - From: C.R.Vegelin [EMAIL PROTECTED] To: Lars Schwarz [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, February 06, 2007 9:45 AM Subject: Re: running sum with a @variable Thanks Lars, Dusan, I found out that the problem is caused by an ORDER BY clause, left out in my example because I had no idea this would be the problem. It works fine with LEFT JOIN and GROUP BY. However, I need the ORDER BY ... Any more suggestions to work around ? Thanks, Cor - Original Message - From: Lars Schwarz [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, February 06, 2007 12:58 PM Subject: Re: running sum with a @variable oops, sorry, forget the last post i made. it's not related to the group by. it should work like that from my point of view. a stripped down version of this works for me (tried without the left join) On 2/6/07, Lars Schwarz [EMAIL PROTECTED] wrote: i suppose this to be working when you leave the group by? On 2/6/07, C.R.Vegelin [EMAIL PROTECTED] wrote: I want to calc a running sum with @variables. Using the command line client, I enter: SET @row := 0, @runsum := 0; followed by: SELECT @row := @row+1 AS `Row`, mycountries.Name AS `Country` , ROUND(SUM(db.Jan+db.Feb+db.Mar)) AS `Q1` , @runsum := @runsum + SUM(db.Jan+db.Feb+db.Mar) AS `RunSum` FROM data2006 AS db LEFT JOIN mycountries ON db.Country = mycountries.ID WHERE ... GROUP BY db.Country; Results are: Row Country Q1RunSum 1 Germany 9090 2 France 6060 3 Norway 2424 etc. I expect the RunSum for Germany 60, France 150, Norway 174 etc. Whay am I doing wrong ? Any help is appreciated ! Regards, Cor -- Lars Schwarz Gottorpstrasse 20 26122 Oldenburg T 0441 2171 354 0 F 0441 2171 354 0 M 0179 512 4628 -- Lars Schwarz Gottorpstrasse 20 26122 Oldenburg T 0441 2171 354 0 F 0441 2171 354 0 M 0179 512 4628 -- 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: MySQL to Postgres
ViSolve DB Team wrote: Hi, From MySQL 4.1, there is a support for mysqldump --compatible option. There is a safe/cool dump for your table: Try lik: shell mysqldump -u dev -p visolvetestdb credits --compatible=postgresql /home/test/ps.sql And also, By default tables are dumped in a format optimized for MySQL. Legal modes are: ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, no_field_options. One can use several modes separated by commas. Safe/cool? I don't understand all of the options you've used on this command but I can tell you that I did something similar to: mysqldump --compatible=postgres dumpfile.sql Unfortunately, this does not seem to result in a file that Postgres can use directly. I'm having to mess with every single line just to get my data in. This is a *real* pain since I'm not really very experienced with either Postgres or MySQL. It's cost me a weeks work already. It's fortunate that we planned for this kind of thing. Jim C. signature.asc Description: OpenPGP digital signature
bug #16979 auto-inc: question about the patch
I have run into that bug with mysql 5.0.24a: bug #16979 auto-inc My application was running OK on 4.1.7. But after migration to 5, the problem started. My questions are: 1-Can I run the patch on 5.0.24a even if it is for 5.0.21. 2-Which patch finally is the good one : the one from [13 May 2006 6:34] Vadim Tkachenko pa4.diff? 3-I have looked in the manual for how to apply a patch but could not find anything on that topic. But it seems that we can use the solaris patch program? Thank you in advance. list of patches: [4 Feb 2006 22:07] Vadim Tkachenko auto_inc patch Attachment: auto_inc.diff (text/plain), 9545 bytes. [8 May 2006 7:34] Vadim Tkachenko Patch for 5.0.21 tree Attachment: auto_inc_5021.diff (application/octet-stream), 10781 bytes. [8 May 2006 7:35] Vadim Tkachenko Hi, I added patch for 5.0.21 tree. To apply: patch -p1 auto_inc_5021.diff in 5.0.21 directory [13 May 2006 6:34] Vadim Tkachenko new version of patch Attachment: pa4.diff (text/plain), 11796 bytes. [2 Aug 2006 17:41] Vadim Tkachenko Patch for 5.1 Attachment: patch.51.autoinc.diff (text/plain), 10950 bytes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How find duplicate entries
SELECT foobar, COUNT(foobar) AS NumOfFoos FROM bar GROUP BY foobar HAVING ( COUNT(foobar) 1 ) On 2/6/07, Tomás Abad Fernández [EMAIL PROTECTED] wrote: Any can tell me a slq to find duplicate entries in a table? Thanks, Tomás -- Lars Schwarz Gottorpstrasse 20 26122 Oldenburg T 0441 2171 354 0 F 0441 2171 354 0 M 0179 512 4628 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How find duplicate entries
That actually should be HAVING ( NumOfFoos 1 ) - Original Message - From: Lars Schwarz [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, February 06, 2007 11:26 AM Subject: Re: How find duplicate entries SELECT foobar, COUNT(foobar) AS NumOfFoos FROM bar GROUP BY foobar HAVING ( COUNT(foobar) 1 ) On 2/6/07, Tomás Abad Fernández [EMAIL PROTECTED] wrote: Any can tell me a slq to find duplicate entries in a table? Thanks, Tomás -- Lars Schwarz Gottorpstrasse 20 26122 Oldenburg T 0441 2171 354 0 F 0441 2171 354 0 M 0179 512 4628 -- 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]
revoke SELECT on a column [ MySQL 4.1 ]
Hello, In a table [say t100], having 100 columns, I want to allow the select on all columns but 1. I tried to do this by granting all columns in the table t100, of the base, then revoke SELECT on the column hide_this, but this doesn't work. mysql GRANT SELECT ON the_base.t100 to 'a_user'@'localhost' identified by 'a_passwd'; mysql revoke SELECT (hide_this) on the_base.t100 from 'a_user'@'localhost'; ERROR 1147 (42000): There is no such grant defined for user 'a_user' on host 'localhost' on table 'current' Is there a turn around, or should grant the select on the 99 other columns ? regards, _-¯-_-¯-_-¯-_-¯-_ Gilles Missonnier IAP - [EMAIL PROTECTED] 01 44 32 81 36 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
detecting the table type by sql?
Hello, how can I get the type of a table (MyISAM, InnoDB, Berkeley DB) by a given table name with sql? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: detecting the table type by sql?
how can I get the type of a table (MyISAM, InnoDB, Berkeley DB) by a given table name with sql? select engine from information_schema.tables where table_schema='dbname' and table_name='tblname'; PB Marten Lehmann wrote: Hello, how can I get the type of a table (MyISAM, InnoDB, Berkeley DB) by a given table name with sql? Regards Marten -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.411 / Virus Database: 268.17.28/672 - Release Date: 2/6/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: detecting the table type by sql?
Marten
In more recent version you can do a simple...
SELECT ENGINE FROM TABLES WHERE TABLE_SCHEMA = {database name} AND
TABLE_NAME = {table name};
...dunno how you'd do it on older versions exactly, you can do...
SHOW TABLE STATUS WHERE Name = BID_UNIQUE_IDS;
...but you can't select individual fields from that.
Regards,
Phil
Hello,
how can I get the type of a table (MyISAM, InnoDB, Berkeley DB) by a
given table name with sql?
Regards
Marten
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: detecting the table type by sql?
Marten
In more recent version you can do a simple...
SELECT ENGINE FROM TABLES WHERE TABLE_SCHEMA = {database name} AND
TABLE_NAME = {table name};
...dunno how you'd do it on older versions exactly, you can do...
SHOW TABLE STATUS WHERE Name = BID_UNIQUE_IDS;
...but you can't select individual fields from that.
Regards,
Phil
Hello,
how can I get the type of a table (MyISAM, InnoDB, Berkeley DB) by a
given table name with sql?
Regards
Marten
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb madness
Marten Lehmann wrote: How can I check which tables are using innodb with sql? How can walk through the tables with show databases and show tables. Thanks. This somewhat depends on how the tables were declared. If you used ENGINE=InnoDb; in the CREATE TABLE sequence, you'd be able to loop through the results of SHOW TABLES and run a SHOW CREATE TABLE on each of the resulting tables to get that answer. While there is no REPAIR TABLE, a strange table locking issue we were having yesterday was apparently solved by a run of OPTIMIZE TABLE on the table at hand. Why we're not sure yet, but things are working so can't complain too much... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: detecting the table type by sql?
This is of course MySQL 5 SELECT engine FROM information_schema.tables WHERE table_schema='...' AND table_name='...'; I haven't used MySQL 4 Maybe this might help SHOW CREATE TABLE tbl-name; You should see TYPE=MyISAM or TYPE=BDB or some other engine in the string - Original Message - From: Marten Lehmann [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, February 6, 2007 12:54:45 PM (GMT-0500) US/Eastern Subject: detecting the table type by sql? Hello, how can I get the type of a table (MyISAM, InnoDB, Berkeley DB) by a given table name with sql? Regards Marten -- 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: innodb madness
There is a more robust way if you running MySQL 5
Export this query using mysql client to an SQL script like this
mysql -h... -u... -p... --skip-column-names -A -eSELECT CONCAT('OPTIMIZE
TABLE ',table_schema,'.',table_name,';') FROM information_schema.tables WHERE
ENGINE='InnoDB'
Then run the script using mysql client.
Please remember, OPTMIZE TABLE does absolutely nothing if all InnoDB data
resides in the shared space.
Your must create all InnoDB tables as separate entities.
To do this, mysqldump all tables to a dump file.
Shutdown MySQL
add 'innodb_file_per_table' to my.cnf
Delete the ibdata files and the logs
Startup MySQL
Reload dump file.
Each InnoDB will reside in .frm and .ibd files
OPTIMIZE will defragment each tablespace (.ibd) file
- Original Message -
From: Chris White [EMAIL PROTECTED]
To: Marten Lehmann [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Tuesday, February 6, 2007 1:24:46 PM (GMT-0500) US/Eastern
Subject: Re: innodb madness
Marten Lehmann wrote:
How can I check which tables are using innodb with sql? How can walk through
the tables with show databases and show tables. Thanks.
This somewhat depends on how the tables were declared. If you used
ENGINE=InnoDb; in the CREATE TABLE sequence, you'd be able to loop
through the results of SHOW TABLES and run a SHOW CREATE TABLE on
each of the resulting tables to get that answer.
While there is no REPAIR TABLE, a strange table locking issue we were
having yesterday was apparently solved by a run of OPTIMIZE TABLE on the
table at hand. Why we're not sure yet, but things are working so can't
complain too much...
--
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: automated character set conversions for tables
Hi You can 'attack' the problem from another perspective :-P I found out a tool (Linux) to convert all characters in a latin1 file to utf8. The command name is 'iconv' and in Debian it is shipped with the libc6 package. So basically dump you DB and convert the file then import it. See the article here: http://gentoo-wiki.com/TIP_Convert_latin1_to_UTF-8_in_MySQL Hope it helps. [EMAIL PROTECTED] wrote: Hello Jerry, Thanks for the reply. You are right. Thats why it finally took me 10-12 work hours to convert a single DB (split over two work days) to a UTF-8 compliant version. While it wasnt necessarily difficult to do (once you figured it out), it can put extra pressure on your eyes if you have to concentrate on the screen all the time ;-). If somebody knows of a smart tool that is doing the hart work feel free to speak ;-) Best regards Nils Valentin Quoting Jerry Schwartz [EMAIL PROTECTED]: Columns can have character set definitions, also. In this case, I hope not. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 10, 2007 10:59 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: automated character set conversions for tables I did a DB conversion before that with ALTER DATABASE db_name CHARACTER SET utf8 That worked wonderfully, except not as expected. ;-) It basically converted only the database itself. so I had to do a separate ALTER TABLE ... for each table. The database encoding more establishes the default to use when creating new tables. As far as adjusting every single table, you can work with your Favorite Scripting Program (tm) and run the query: `SHOW TABLES` to get a list of all tables for that database (the column you want is called Tables_in_[database name here]), which you can get the exact column by running it in console or your Favorite SQL Program (tm). Then simply loop over the result set and run the alter table command on each table. -- 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]
log table advice
Hi all, Just wondering how people are dealing with tables that are used for logging, ie: insert only tables supporting occasional queries used for audit or event logs. These tables will keep growing and there is no need to keep them that large so what is the best strategy in managing the data in them. I was thinking of going with MyIsam tables because I don't need transactions n the table data is self contained and portable. I would change my application to insert into tables which are named with a datestamp component (ie: have the apps determine the name of the table based on current time before doing an insert) and then have a cron job to create new tables as needed and to also backup and remove older tables as they are no longer being used. Any thoughts on this ? Thanks, Yong. Yong Lee Developer [EMAIL PROTECTED] http://www.eqo.com/ direct: +1.604.273.8173 x113 mobile:+1.604.418.4470 fax: +1.604.273.8172 web:www.EQO.com http://www.eqo.com/ EQO ID: yonglee
Re: log table advice
Hi all, Just wondering how people are dealing with tables that are used for logging, ie: insert only tables supporting occasional queries used for audit or event logs. These tables will keep growing and there is no need to keep them that large so what is the best strategy in managing the data in them. I was thinking of going with MyIsam tables because I don't need transactions n the table data is self contained and portable. I would change my application to insert into tables which are named with a datestamp component (ie: have the apps determine the name of the table based on current time before doing an insert) and then have a cron job to create new tables as needed and to also backup and remove older tables as they are no longer being used. Any thoughts on this ? Well, just a few thoughts... - with MyISAM, delayed insert and REPLACE proved to be very useful for me - for a very intensive logging application (1000 hits per second) I found it better to keep the hits in textfiles (on ramdisk), and periodically (in every minute or so) process them and feed them to MySQL, using a bunch of speedup techniques OTOH I am talking about preprocessed logfiles. Storing logs as-is in a database seems an overkill for me. I would just use standard textfiles with regular bzipping for that. - Fagzal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: log table advice
On 2/6/07, Yong Lee [EMAIL PROTECTED] wrote: Hi all, Just wondering how people are dealing with tables that are used for logging, ie: insert only tables supporting occasional queries used for audit or event logs. These tables will keep growing and there is no need to keep them that large so what is the best strategy in managing the data in them. I was thinking of going with MyIsam tables because I don't need transactions n the table data is self contained and portable. I would change my application to insert into tables which are named with a datestamp component (ie: have the apps determine the name of the table based on current time before doing an insert) and then have a cron job to create new tables as needed and to also backup and remove older tables as they are no longer being used. Any thoughts on this ? Use of the ARCHIVE engine in conjunction with a partitioning scheme works wonders for logging. -- -jp If at first you don't succeed, you are obviously not Chuck Norris.
Updating from 4.0.20 to 5.0.27
Hello: I'm currently using OS linux slackware 10.0 with MySQL version 4.0.20 I currently use python and rebol APIs to MySQL, not PHP. I've downloaded mysql-standard-5.0.27-linux-i686.tar.gz Questions: Should I first upgrade to to an intermediate version? IF so, which? URLs to relevant documentation? thanks tim -- Tim Johnson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Updating from 4.0.20 to 5.0.27
In the last episode (Feb 06), Tim Johnson said: I'm currently using OS linux slackware 10.0 with MySQL version 4.0.20 I currently use python and rebol APIs to MySQL, not PHP. I've downloaded mysql-standard-5.0.27-linux-i686.tar.gz Questions: Should I first upgrade to to an intermediate version? IF so, which? URLs to relevant documentation? thanks tim You can upgrade straight to 5.0. You'll want to read the following links. The last two detail changes between the versions. http://dev.mysql.com/doc/refman/5.0/en/upgrade.html http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-4-0.html http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How find duplicate entries
Hi , Try this query... SELECT COUNT(*), column1, column2 FROM tablename GROUP BY column1, column2 HAVING COUNT(*)1; Thanks, ViSolve DB Team - Original Message - From: Tomás Abad Fernández [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, February 06, 2007 9:57 PM Subject: How find duplicate entries Any can tell me a slq to find duplicate entries in a table? Thanks, Tomás No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.411 / Virus Database: 268.17.27/671 - Release Date: 2/5/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Spatial Select -- Finding the nearest points
Here's the table: CREATE TABLE `lsd` ( `id` mediumint(8) unsigned NOT NULL auto_increment, `latitude` decimal(10,8) NOT NULL, `longitude` decimal(12,8) NOT NULL, `coordinates` point NOT NULL, PRIMARY KEY (`id`), SPATIAL KEY `latlng` (`coordinates`(32)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=100534 ; I have been able to find any substantive articles on spatial SELECTs. Basically, I want to select the records with the coordinates (which are latitude/longitude points) closest to a given latitude/longitude. Simple, right? But I'm stuck here... None of the examples in the MySQL docs seem to do this precisely. Any ideas? (One lister showed me how to perform a SELECT on the latitude, longitude columns, but with hundreds of thousands of rows, the query takes several seconds. I figure with a spatial index on that coordinates POINT column, it could be much faster.) ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
