How to convert this DELETE command from MySQL 4.0.25 to 3.23?
Hi, This query running fine on 4.0.25 but when trying on 3.23 an error occurs. can one help me to find correct command for 3.23? DELETE FROM A USING A RIGHT JOIN B ON B.id = A.sectionid WHERE B.id is null This task is deleted any row in A that have no items on B. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to convert this DELETE command from MySQL 4.0.25 to 3.23?
The Nice Spider schrieb: Hi, This query running fine on 4.0.25 but when trying on 3.23 an error occurs. can one help me to find correct command for 3.23? Probably if you post the error message you get. -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
difference between mysql max version and non-max version
Can anybdoy please tell me the difference between mysql max version andthe non-max version? Also lemme know how the max version is helpful if the installation of mysql on the clinet machine is to be done easily wihout any hassals and minimal human intervension regards, Ashwini - Yahoo! India Answers: Share what you know. Learn something new. Click here
Getting the previous months documents
I have a database that stores documents relating to meetings. They have all the
usual stuff agenda, minutes etc. There are 3 paper types for each date agenda
(1 only), minutes (1 only), and a bunch of general documents titled 'papers'.
I need to display all the documents for a specific date which is easy but I
also need to retrieve the minutes for the PREVIOUS meeting which is proving
more difficult. The documents are stored by date in the format dd/mm/yy. If
someone wants the documents from 02/05/06 how do I find the minutes for the
previous date when I do not know when it is?
Can I do this with mysql? Or will it be better with mktime and some php?
--
-- Table structure for table `board_papers`
--
CREATE TABLE `board_papers` (
`id` int(4) NOT NULL auto_increment,
`doc_date` varchar(10) NOT NULL default '-00-00',
`article_type` enum('agenda','minutes','paper') NOT NULL default 'agenda',
`fileName` varchar(50) NOT NULL default '',
`fileSize` int(4) NOT NULL default '0',
`fileType` varchar(50) NOT NULL default '',
`content` blob NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
RE: Getting the previous months documents
You should start by using MySQL date as the standard for date: -MM-DD
After that it is simple:
SELECT * FROM documents WHERE doc_date2006-05-02 ORDER BY doc_date DESC
LIMIT 1;
Othervise you could use MySQL function to take sub strings and create a
field in the query that extract it as 06-05-02 and order by that.
/Peter
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 02, 2006 3:04 PM
To: mysql@lists.mysql.com
Subject: Getting the previous months documents
I have a database that stores documents relating to meetings. They have all
the usual stuff agenda, minutes etc. There are 3 paper types for each date
agenda (1 only), minutes (1 only), and a bunch of general documents titled
'papers'.
I need to display all the documents for a specific date which is easy but I
also need to retrieve the minutes for the PREVIOUS meeting which is proving
more difficult. The documents are stored by date in the format dd/mm/yy. If
someone wants the documents from 02/05/06 how do I find the minutes for the
previous date when I do not know when it is?
Can I do this with mysql? Or will it be better with mktime and some php?
--
-- Table structure for table `board_papers`
--
CREATE TABLE `board_papers` (
`id` int(4) NOT NULL auto_increment,
`doc_date` varchar(10) NOT NULL default '-00-00',
`article_type` enum('agenda','minutes','paper') NOT NULL default 'agenda',
`fileName` varchar(50) NOT NULL default '',
`fileSize` int(4) NOT NULL default '0',
`fileType` varchar(50) NOT NULL default '',
`content` blob NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting the previous months documents
My sql is not so great. The dates are entered through a dropdown box in the
format dd/mm/ (uk date format) as a VARCHAR.
I could use strrev and str_replace (PHP) to get it in the correct format but
will the mysql query you send work on a VARCHAR which it is it present or
will I have to change the field to DATE?
Ross
- Original Message -
From: Peter Lauri [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, May 02, 2006 9:06 AM
Subject: RE: Getting the previous months documents
You should start by using MySQL date as the standard for date: -MM-DD
After that it is simple:
SELECT * FROM documents WHERE doc_date2006-05-02 ORDER BY doc_date DESC
LIMIT 1;
Othervise you could use MySQL function to take sub strings and create a
field in the query that extract it as 06-05-02 and order by that.
/Peter
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 02, 2006 3:04 PM
To: mysql@lists.mysql.com
Subject: Getting the previous months documents
I have a database that stores documents relating to meetings. They have
all
the usual stuff agenda, minutes etc. There are 3 paper types for each date
agenda (1 only), minutes (1 only), and a bunch of general documents titled
'papers'.
I need to display all the documents for a specific date which is easy but
I
also need to retrieve the minutes for the PREVIOUS meeting which is
proving
more difficult. The documents are stored by date in the format dd/mm/yy.
If
someone wants the documents from 02/05/06 how do I find the minutes for
the
previous date when I do not know when it is?
Can I do this with mysql? Or will it be better with mktime and some php?
--
-- Table structure for table `board_papers`
--
CREATE TABLE `board_papers` (
`id` int(4) NOT NULL auto_increment,
`doc_date` varchar(10) NOT NULL default '-00-00',
`article_type` enum('agenda','minutes','paper') NOT NULL default
'agenda',
`fileName` varchar(50) NOT NULL default '',
`fileSize` int(4) NOT NULL default '0',
`fileType` varchar(50) NOT NULL default '',
`content` blob NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
--
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: Killing MySQL connections on a specific interface from a specific host
I am not really sure why you would need this , but I am just throwing in a possible solution.. First I would say since you need to kill connections on a specific interface (eth0), It would be fair to assume that you have more than one interface, if thats the case and you don't have the --bind-address option set in my.cnf, then your mysqld daemon would listen on all the available IP's on all interfaces, ACK. And that's what I need. then it is vey difficult to know on what IP did mysql serve a specific connection, unless you would do a netstat, then correspond that IP to the clients IP in show processlist, etc etc , so pretty cumbersume... That's what I've done so far ... I can list and grep all connections on the interface and get the client IP. I can also get the corresponding MySQL Thread-IDs from the mysql-processlist. But I could not find a way to only select connections from a specific Client IP to a specific Server IP, as the Processlist only shows the Client-address. But unless there is a real need , you can just have the deamon to listen only on one specific IP residing on eth0, like this --bind-address= xxx.xxx.xx.x ( this IP resides on eth0) The Server has to listen on all (two) interfaces and clients can connect to both. If this is feasible in your setup, then killing threads from a specific IP should be easy, if you need to kill threads manually then use a toll like mytop (http://jeremy.zawodny.com/mysql/mytop/ http://jeremy.zawodny.com/mysql/mytop/), or if you want it automated then you could easily write a perl script which would parse the output of show full processlist, get all the connections from a specific client IP, and KILL them ... Well, that's what I've done ... But it also kills connections from the client to another interface. I know this is somewhat special and it would take quite a while to explain why exactly I need this. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Getting the previous months documents
I did this, sorry for bad format. This will do it for you.
SELECT CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, 4, 2),
SUBSTRING(temptext, 1, 2)) AS docdate
FROM `tempdate` WHERE CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext,
4, 2), SUBSTRING(temptext, 1, 2)) '060703' ORDER BY
CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, 4, 2),
SUBSTRING(temptext, 1, 2)) DESC LIMIT 1
/Peter
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 02, 2006 3:28 PM
To: Peter Lauri; mysql@lists.mysql.com
Subject: Re: Getting the previous months documents
My sql is not so great. The dates are entered through a dropdown box in the
format dd/mm/ (uk date format) as a VARCHAR.
I could use strrev and str_replace (PHP) to get it in the correct format but
will the mysql query you send work on a VARCHAR which it is it present or
will I have to change the field to DATE?
Ross
- Original Message -
From: Peter Lauri [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, May 02, 2006 9:06 AM
Subject: RE: Getting the previous months documents
You should start by using MySQL date as the standard for date: -MM-DD
After that it is simple:
SELECT * FROM documents WHERE doc_date2006-05-02 ORDER BY doc_date DESC
LIMIT 1;
Othervise you could use MySQL function to take sub strings and create a
field in the query that extract it as 06-05-02 and order by that.
/Peter
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 02, 2006 3:04 PM
To: mysql@lists.mysql.com
Subject: Getting the previous months documents
I have a database that stores documents relating to meetings. They have
all
the usual stuff agenda, minutes etc. There are 3 paper types for each date
agenda (1 only), minutes (1 only), and a bunch of general documents titled
'papers'.
I need to display all the documents for a specific date which is easy but
I
also need to retrieve the minutes for the PREVIOUS meeting which is
proving
more difficult. The documents are stored by date in the format dd/mm/yy.
If
someone wants the documents from 02/05/06 how do I find the minutes for
the
previous date when I do not know when it is?
Can I do this with mysql? Or will it be better with mktime and some php?
--
-- Table structure for table `board_papers`
--
CREATE TABLE `board_papers` (
`id` int(4) NOT NULL auto_increment,
`doc_date` varchar(10) NOT NULL default '-00-00',
`article_type` enum('agenda','minutes','paper') NOT NULL default
'agenda',
`fileName` varchar(50) NOT NULL default '',
`fileSize` int(4) NOT NULL default '0',
`fileType` varchar(50) NOT NULL default '',
`content` blob NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
--
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]
difference between mysql max version and non-max version
Note: forwarded message attached. Yahoo! India Answers: Share what you know. Learn something new. Click here---BeginMessage--- Can anybdoy please tell me the difference between mysql max version andthe non-max version? Also lemme know how the max version is helpful if the installation of mysql on the clinet machine is to be done easily wihout any hassals and minimal human intervensionregards,Ashwini Yahoo! India Answers: Share what you know. Learn something new. Click here---End Message--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: difference between mysql max version and non-max version
Hi! ashwini c.v wrote: Can anybdoy please tell me the difference between mysql max version andthe non-max version? I answered this here on April 26. Also, please use exact terminology: A version is a two- or three-level numeric designator, like 4.1 or 5.0.21. max is a package, or (coming with 5.1) a server variant. Also lemme know how the max version is helpful if the installation of mysql on the clinet machine is to be done easily wihout any hassals and minimal human intervension No difference between a max and a standard package in this respect. Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sorting with php/mysql
This is my database...I will use the item_id for the order but what if I
want to change item_id 3 to item id 1? How can I push all the items down one
place? How can I delete any gaps when items are deleted. Say I delete item 2
how can I delted the gap and 'promote item_id 3 to item_id 2
CREATE TABLE `board_papers` (
`id` int(4) NOT NULL auto_increment,
`doc_date` varchar(10) NOT NULL default '-00-00',
`article_type` enum('agenda','minutes','paper') NOT NULL default 'agenda',
`fileName` varchar(50) NOT NULL default '',
`fileSize` int(4) NOT NULL default '0',
`fileType` varchar(50) NOT NULL default '',
`content` blob NOT NULL,
`item_id` int(10) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can I get my disk space back?
I have a really simple (two tables, one relation) but big (~70 GB) innodb database containing rather dynamic data. After deleting lots of records from the tables, the innodb data files still take the same amount of disk space as before. What is the correct way of freeing the disk space? The general solution seems to be alter table tablename type=innodb which completely rebuilds the table, but this is not acceptable because: a) It locks the table during the operation making it unavailable to other clients. b) It involves copying the table, taking twice the disk space during the operation - disk space that I do not have. Is there another way of doing this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I get my disk space back?
David Israelsson schrieb: I have a really simple (two tables, one relation) but big (~70 GB) innodb database containing rather dynamic data. After deleting lots of records from the tables, the innodb data files still take the same amount of disk space as before. What is the correct way of freeing the disk space? The general solution seems to be alter table tablename type=innodb which completely rebuilds the table, but this is not acceptable because: a) It locks the table during the operation making it unavailable to other clients. b) It involves copying the table, taking twice the disk space during the operation - disk space that I do not have. Is there another way of doing this? Probably OPTIMIZE helps here -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I get my disk space back?
Barry [EMAIL PROTECTED] writes: David Israelsson schrieb: I have a really simple (two tables, one relation) but big (~70 GB) innodb database containing rather dynamic data. After deleting lots of records from the tables, the innodb data files still take the same amount of disk space as before. What is the correct way of freeing the disk space? The general solution seems to be alter table tablename type=innodb which completely rebuilds the table, but this is not acceptable because: a) It locks the table during the operation making it unavailable to other clients. b) It involves copying the table, taking twice the disk space during the operation - disk space that I do not have. Is there another way of doing this? Probably OPTIMIZE helps here I wish I was that lucky. According to the documentation, OPTIMIZE for InnoDB tables is mapped to ALTER TABLE. This is also exactly my experiences after actually trying it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I Have Problem with this Timestamp
Hi MySql Community, I put up a default null value TIMESTAMP on field type TIMESTAMP. Now I enter to the database as '-00-00 00:00:00' default for null value. From this on, I mapped it using hibernate with mysql-connector-java-3.1.6-bin.jar driver. I extract it but error occurred: Caused by: java.sql.SQLException: Cannot convert value '-00-00 00:00:00' from column 9 to TIMESTAMP. What did I done wrong? Is there a problem with the driver? Or should I use DATETIME instead? Some says the format is supposed to be '-00-00 00.00.00' instead, if so this would be a database problem, is this a some kind of unresovled bug? Lastly, any remedy for the meantime for this? Thanks a lot. Melvin R. Zamora - Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1cent;/min.
RE: innodb file per table
but you don't know __when__ innodb_file_per_table was set! So it's possible that many innodb tables actually reside in ibdata [1-4]. Check your data directory to see the individual innodb files/tables (*.ibd). This is true, and even on a fresh install that has always had innodb_file_per_table, InnoDB still needs the shared tablespace (though it probably doesn't need to be that large). Once you have an InnoDB tablespace the only way to reduce the size of the shared tablespace is to completely dump the data and recreate the tablespace. Roughly the sequence is: mysqldump to text...be very careful to keep a consistent snapshot, handle blobs, quoting names, etc Test this. Shut down mysql Rename/move old mysql data and log directories, create new, empty ones (copy over mysql database...it's not innodb and will keep the same users) Alter my.cnf, point to include new InnoDB shared table definition Start mysql, make sure InnoDB initializes correctly (check .err file) Read in dump file you took in step 1 Again, be careful with this. It essentially involves exporting and importing all your data, so make sure you have a valid export file. Good luck, Ware Thanks for the response. I routinely mysqldump --host=source | mysql --host=target for backup purposes, and then compare the row counts to see that the restore acutally completed, so I think I've got a good handle on making a clean copy of the data. In fact, I re-configured the my.cnf on the backup server, and I see that the directory size went from 58G to 45G, so there was a nice space savings. Thanks for the advice. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding duplicates, etc.
-Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 16:55 To: Dirk Bremer Cc: mysql@lists.mysql.com Subject: Re: Finding duplicates, etc. Dirk, I would like to know where there ar duplicate accounts that do not have duplicate addresses. I think that at this point in time that these are included in the first query. I would like to separate these out to report on them. How about ... select account,ident,address from group15034_i g1 inner join group15034_i g2 using (account) where g1.address g2.address; Peter, You got me started on the right track. Here is what I ended up with that seems to satisfy my requirements: select distinct g1.account,g1.sub_account,g1.address from group15034 as g1 inner join group15034 as g2 using (account) where (g1.status = 'single') and (g1.address g2.address) order by account,sub_account; The distinct clause had the most effect on limiting the results to a manageable set. Thanks for your help and if anyone has any suggestions to refine this query, please let me know. Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sorting with php/mysql
Ross,
This is my database.
Nope, it's a table, not a database.
..I will use the item_id for the order but what if I want to change
item_id 3 to item id 1?
A primary key shouldn't be edited, and the literal value of an
auto_increment column oughtn't to be relevant. If you really need a
settable ordering column, add it to the table, and if you really need
sequencing numbers to have no sequence breaks, consider disallowing
deletion in favour of carrying a column you can use for marking rows
'inactive' or whatever.
How can I push all the items down one place? How can I delete any gaps
when items are deleted. Say I delete item 2 how can I delted the gap
and 'promote item_id 3 to item_id 2
This question conflates data maintenance with reporting. If deletion of
a row required closing up the row-number sequence below it, such
deletion would require updating all rows with id values greater than the
id of the deleted row. That would be unmanageable. To report rows as
1,2,3 without breaks, you just need to write something like ...
SET @ord=0
SELECT @ord:[EMAIL PROTECTED] AS Item, doc_date AS Date,... c ...
PB
CREATE TABLE `board_papers` (
`id` int(4) NOT NULL auto_increment,
`doc_date` varchar(10) NOT NULL default '-00-00',
`article_type` enum('agenda','minutes','paper') NOT NULL default
'agenda',
`fileName` varchar(50) NOT NULL default '',
`fileSize` int(4) NOT NULL default '0',
`fileType` varchar(50) NOT NULL default '',
`content` blob NOT NULL,
`item_id` int(10) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 5/1/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fixing Databases When Replication Is Enabled?
My Sincere apologies, I intented to mean mysqlcheck but somehow came out as myisamchk :) Kishore Jalleda On 5/2/06, Marciano [EMAIL PROTECTED] wrote: How myisamchk can write to binlog if the server need to be down? - Mensagem Original De: Kishore Jalleda [EMAIL PROTECTED] Para: Robinson, Eric [EMAIL PROTECTED] Cópia: mysql@lists.mysql.com Assunto: Re: Fixing Databases When Replication Is Enabled? Data: 01/05/06 22:25 Yes On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck, not myisamchk or REPAIR. Is what you said still true? -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 1:56 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: Fixing Databases When Replication Is Enabled? On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck against a replication master database and it reported a problem with a table, which it corrected. Is the slave now out of sync? If so, how do I correct the problem without copying the whole database over a slow WAN link? --Eric By Default when you run REPAIR or myisamchk --recover , MySQL writes the changes made to the Binlog , and those will be obviously replicated to the slave, so if everything went fine with your myisamck on the master, then your slave is in sync and you don't have to do anyhthing special on the slave... Kishore Jalleda http://kjalleda.googlepages.com/projects -- 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] Yes On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck, not myisamchk or REPAIR. Is what you said still true? -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 1:56 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: Fixing Databases When Replication Is Enabled? On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck against a replication master database and it reported a problem with a table, which it corrected. Is the slave now out of sync? If so, how do I correct the problem without copying the whole database over a slow WAN link? --Eric By Default when you run REPAIR or myisamchk --recover , MySQL writes the changes made to the Binlog , and those will be obviously replicated to the slave, so if everything went fine with your myisamck on the master, then your slave is in sync and you don't have to do anyhthing special on the slave... Kishore Jalleda http://kjalleda.googlepages.com/projects -- 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] __ Webmail Intercol http://www.intercol.com.br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: difference between mysql max version and non-max version
On 5/2/06, ashwini c.v [EMAIL PROTECTED] wrote: Can anybdoy please tell me the difference between mysql max version andthe non-max version? Also lemme know how the max version is helpful if the installation of mysql on the clinet machine is to be done easily wihout any hassals and minimal human intervension This is well covered in the documentation: http://dev.mysql.com/doc/refman/5.0/en/mysqld-max.html Also, mysqld-max has the d on its name because its a daemon, a server, so, no use for it on a machine suppose to be a client. Its just a version of the server with different options (more features) at compile time. Joerg, accourding to the page above, he used the correct terminology, a version has different meanings, one for apps (as you stated) and another for executables (builds), that is the way Ashwini mentioned it. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Grant to some database question
I have a database 'DB1' and user 'user1'. How to allow this user to full access (read/write/create etc.) only to database 'DB1' and deny to other databases, including view its names? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I get my disk space back?
David, If you want to decrease the size of the ibdata files, you will have to restart the mysqld process. According to the manual (http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html) the only way is to: 1. Use *mysqldump* to dump all your |InnoDB| tables. 2. Stop the server. 3. Remove all the existing tablespace files. 4. Configure a new tablespace. 5. Restart the server. 6. Import the dump files. If you only want to free the space taken up by the now-deleted rows, and return this space to the innodb tablespace, then an ALTER TABLE / OPTIMIZE TABLE is a way to do it, but understand that this will not decrease the size of your ibdata files. If you can't afford the table to be locked during either of the above rebuild processes, the only other method I can suggest is to create a second server and perform the maintenance there, then switch your application over to that server and perform the maintenance on your initial server. I frequently do this, and while it works for the applications that I have, ymmv. I'd be glad to explain the process in more depth if needed. HTH! Regards, Devananda David Israelsson wrote: I have a really simple (two tables, one relation) but big (~70 GB) innodb database containing rather dynamic data. After deleting lots of records from the tables, the innodb data files still take the same amount of disk space as before. What is the correct way of freeing the disk space? The general solution seems to be alter table tablename type=innodb which completely rebuilds the table, but this is not acceptable because: a) It locks the table during the operation making it unavailable to other clients. b) It involves copying the table, taking twice the disk space during the operation - disk space that I do not have. Is there another way of doing this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to move the MySQL data directory?
Hello, I've just wanted to update MySQL 5.0 on my test machine that will be a productive server very soon. But upgrading MySQL like installing it after MySQL's guide brings a problem: I'd need to move the data directory to the new programme directory every time. So I want to move the datadir outside the application's directory, from /usr/local/mysql5/data (with mysql5 being a symlink to mysql-5.0.xx-...) to /var/mysql5/data. So I moved the entire data directory to the new location and tried to start the MySQL server. After a while printing out dots, it says ERROR! and that's it. When I have tried it with MySQL 4.0 and after adding some more of the suppressed output to the scripts, it seems like the startup script expects at least the mysql database to be located inside $basedir/data/mysql which of course is not what I meant to do. Is this true that MySQL wants all its databases to be inside the programme directory? Is there any other way to move the datadir out there without hacking all the scripts - over and over with each update? (Then I could just as well move the datadir each time...) -- Yves Goergen LonelyPixel [EMAIL PROTECTED] http://beta.unclassified.de – My web laboratory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to move the MySQL data directory?
Well, moving the datadir to the new MySQL 5.0.21 directory, messing around with all the stupid symlinks, it seems I have finally managed to delete that datadir... It was empty anyway, yet, but this is an extremely dangerous task. So I really need to move the datadir to a safe place, outside all that symlinked chaos. With that done, what is the proposed upgrade method anyway? 1. unpack the tarball to /usr/local/mysql-$version 2. chown root:mysql it all (what for, actually?) 3. stop the server 4. update the symlink /usr/local/mysql5 to the new directory 5. start the server Would that be okay? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] http://beta.unclassified.de – My web laboratory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL JDBC Problem
I have a problem using the latest version of Connector/J the DESC of table shows: mysql desc tiposelo; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | Tp_Id | int(4) unsigned | NO | PRI | NULL| auto_increment | | Tp_Ativo | int(1) unsigned | NO | MUL | 0 || | Tp_Descricao | varchar(200)| NO | UNI | || | Tp_Equivale| int(2) unsigned | NO | MUL | 0 || | Tp_Escopo | varchar(200)| NO | MUL | || | Tp_MascaraSelo | varchar(20) | NO | | || | Tp_Valor | double(16,2)| NO | | 0.00|| ++-+--+-+-++ 7 rows in set (0.01 sec) mysql The Key Tp_id is a PRIMARY KEY but with connector/J Call getPrimaryKeys() and result's in a null resultset The Connector/J is broken ? ps: the EXTRA clause auto_increment is not showed on .getIndexInfo() MySQL 5.0.20a Connector/J 3.1.12 Linux box Java 1.5.0_06 -- - ++ Dyego Souza Dantas Leal ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A ***http://javacoffe.blogspot.com*** - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 1647350 $ look into my eyes Phone : +55 041 2106-1212 look: cannot open my eyes Fax : +55 041 3296-6640 - Reply: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to find size of my database
Hi, is there any command to find the total size of mysql db. Regards, Shivaji.
How to find size of my database
Hi, is there any command to find the total size of mysql db. Regards, Shivaji.
Re: How to find size of my database
It would be easier to help if you specified what you meant by size. Do you mean the amount of space the database is using on your hard drive? Or the number of tables in the database? Or the number of rows in the tables? Or somethng else altogether? You should also mention which version of MySQL you are using; later versions have features and commands not found in earlier versions. Also, the size of a give database may differ from version to version of MySQL. -- Rhino - Original Message - From: Shivaji S [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, May 02, 2006 3:53 PM Subject: How to find size of my database Hi, is there any command to find the total size of mysql db. Regards, Shivaji. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL JDBC Problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Dyego Souza Dantas Leal said the following on 5/2/2006 2:58 PM: I have a problem using the latest version of Connector/J the DESC of table shows: mysql desc tiposelo; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | Tp_Id | int(4) unsigned | NO | PRI | NULL| auto_increment | | Tp_Ativo | int(1) unsigned | NO | MUL | 0 || | Tp_Descricao | varchar(200)| NO | UNI | || | Tp_Equivale| int(2) unsigned | NO | MUL | 0 || | Tp_Escopo | varchar(200)| NO | MUL | || | Tp_MascaraSelo | varchar(20) | NO | | || | Tp_Valor | double(16,2)| NO | | 0.00 || ++-+--+-+-++ 7 rows in set (0.01 sec) mysql The Key Tp_id is a PRIMARY KEY but with connector/J Call getPrimaryKeys() and result's in a null resultset The Connector/J is broken ? ps: the EXTRA clause auto_increment is not showed on .getIndexInfo() MySQL 5.0.20a Connector/J 3.1.12 Linux box Java 1.5.0_06 Dyego, Exactly how are you calling getPrimaryKeys(), i.e. the _exact_ arguments you're passing in. Do you actually get a NULL result set (shouldn't happen), or an empty one? This functionality is tested all over the place in the testsuite and the JDBC compliance test, so we're pretty darn sure it's solid, but we'd like to track down where it's going wrong for you. Unfortunately we can't do that without more information. -Mark -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2.2 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFEV70YtvXNTca6JD8RAhfwAJ9PD8m6zL+RShCgAujRVXV3Ong3KACgnAZJ Jz4y4vGlI8yrv4WH/9zEi7I= =Ik8X -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to move the MySQL data directory?
The easiest way might be to tell mysql in the config file where to look for the data directory. In your my.cnf file (typically /etc/my.cnf) under the [mysqld] section: datadir = /Volumes/mysql-data/data Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude)
2006-05-02
Thread
Adam i Agnieszka Gąsiorowski FNORD aka ALinkA ak a symbol '( { .A. } )'' ||~ { A.A }
On 2006-04-26, at 20:53, Nicolas Verhaeghe wrote:
Also, in order to do the Zip+4 translation, you need a good CASS
certification script. Most of these products are expensive. You could
program your own, but from talking to a programmer who created such a
product, it takes years to come with a good one, because people
misspell
their street addresses in so many ways, it's not even funny.
It *is* funny - in AI-related fields of CS,
that is - though, it will take a bit of time 'till
even lsd-25 inspired applications of algorithms
are as good at copyediting as a human editor :-} .A. {-:.
--
(defmethod node-to-dot ((node node)) ;; Viva Draconis!
(node-dot-using-attibute node (node-attribute node)))
(defmethod node-to-dot-using-attribute ((node node)
(attribute sc-element)) t) ;; https://hyperreal.info
smime.p7s
Description: S/MIME cryptographic signature
Mysql add multiple index
Mysql, Is there any difference between alter table test add index(id, lastname); alter table test add index(zip5, zip4); and alter table test add index(id, lastname), add index(zip5, zip4); The test table has 90 millions records. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: How to find size of my database
Hi Rhino, Thanks for the input. I mean to say the total Database Size ,that is to see my current Database Size.I am using 4.0.20 version Regards, Shivaji On Wed, 03 May 2006 Rhino wrote : It would be easier to help if you specified what you meant by size. Do you mean the amount of space the database is using on your hard drive? Or the number of tables in the database? Or the number of rows in the tables? Or somethng else altogether? You should also mention which version of MySQL you are using; later versions have features and commands not found in earlier versions. Also, the size of a give database may differ from version to version of MySQL. -- Rhino - Original Message - From: Shivaji S [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, May 02, 2006 3:53 PM Subject: How to find size of my database Hi, is there any command to find the total size of mysql db. Regards, Shivaji. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006
Re: Fixing Databases When Replication Is Enabled?
On Mon, 1 May 2006, Kishore Jalleda wrote: On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck against a replication master database and it reported a problem with a table, which it corrected. Is the slave now out of sync? If so, how do I correct the problem without copying the whole database over a slow WAN link? --Eric By Default when you run REPAIR or myisamchk --recover , MySQL writes the changes made to the Binlog , and those will be obviously replicated to the slave, so if everything went fine with your myisamck on the master, then your slave is in sync and you don't have to do anyhthing special on the slave... This needs a bit clarification. First off, to my knowledge MySQL does not write [data] changes due to a REPAIR statement. Their replication implementation doesn't work that way. Before MySQL 4.1, REPAIR (and for instance OPTIMIZE) statements are not replicated at all, but are done so by default in 4.1 and up. So an important question is what version your MySQL server is. To answer the original posters question: It's hard to say if your slave is out of sync. If your master got corrupted from say hardware failure or if your MySQL daemon died unexpectedly and a REPAIR resulted in modified data, then your replication slave will most likely be out of sync and you will need to give it a fresh data seed. In that case a REPAIR statement on the slave will not fix the data consistency issue. I can't say this for sure, but I would imagine there aren't many scenarios where a REPAIR on a slave would fix consistency issues. Atle - Flying Crocodile Inc, Unix Systems Administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: How to find size of my database
You still haven't said what you mean by size! Let me put it to you this way: what units would be used in the answer you want? In other words, would the answer look like: 1. 234,000 KB? 2. 2.75 million rows? 3. 321 tables? Or something else altogether? -- Rhino - Original Message - From: Shivaji S [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, May 02, 2006 4:33 PM Subject: Re: Re: How to find size of my database Hi Rhino, Thanks for the input. I mean to say the total Database Size ,that is to see my current Database Size.I am using 4.0.20 version Regards, Shivaji On Wed, 03 May 2006 Rhino wrote : It would be easier to help if you specified what you meant by size. Do you mean the amount of space the database is using on your hard drive? Or the number of tables in the database? Or the number of rows in the tables? Or somethng else altogether? You should also mention which version of MySQL you are using; later versions have features and commands not found in earlier versions. Also, the size of a give database may differ from version to version of MySQL. -- Rhino - Original Message - From: Shivaji S [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, May 02, 2006 3:53 PM Subject: How to find size of my database Hi, is there any command to find the total size of mysql db. Regards, Shivaji. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Re: How to find size of my database
Hi Rhino, sorry ,for my unclear reply. the size i meant is in KB or MB.. Regards, Shivaji On Wed, 03 May 2006 Rhino wrote : You still haven't said what you mean by size! Let me put it to you this way: what units would be used in the answer you want? In other words, would the answer look like: 1. 234,000 KB? 2. 2.75 million rows? 3. 321 tables? Or something else altogether? -- Rhino - Original Message - From: Shivaji S [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, May 02, 2006 4:33 PM Subject: Re: Re: How to find size of my database Hi Rhino, Thanks for the input. I mean to say the total Database Size ,that is to see my current Database Size.I am using 4.0.20 version Regards, Shivaji On Wed, 03 May 2006 Rhino wrote : It would be easier to help if you specified what you meant by size. Do you mean the amount of space the database is using on your hard drive? Or the number of tables in the database? Or the number of rows in the tables? Or somethng else altogether? You should also mention which version of MySQL you are using; later versions have features and commands not found in earlier versions. Also, the size of a give database may differ from version to version of MySQL. -- Rhino - Original Message - From: Shivaji S [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, May 02, 2006 3:53 PM Subject: How to find size of my database Hi, is there any command to find the total size of mysql db. Regards, Shivaji. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006
Re: Re: How to find size of my database
On 2 May 2006 20:33:56 -, Shivaji S [EMAIL PROTECTED] wrote: Hi Rhino, Thanks for the input. I mean to say the total Database Size ,that is to see my current Database Size.I am using 4.0.20 version Hmm, as generic as your first post. I'll just assume a lot of things in order to try and answer your question. Lets see. 1) I'll assume you use myisam tables, as your version is kinda outdated. 2) I'll assume you want the total DISK space required for your database. (could be rows, could be tables, could be space used by the rows and tables, all of those are different, different storage engines use space differently, a innodb table could use 1GB in your disk, and 800MB on rows) 3) I'll assume that you KNOW how to get the size of a given file/directory on your Operating System. If I'm wrong in any assumption, please let me know. Locate your data directory, if you don't know where it is, or don't have access to it, you just can't do this (you can always take a look at the manual, windows is usually the data directory, linux is somewhere in /var/lib, YMMV). Into this directory, you'll see a new directory for every database, getting the size of the directory you see how much disk space the database has taken. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fixing Databases When Replication Is Enabled?
On 5/2/06, Atle Veka [EMAIL PROTECTED] wrote: On Mon, 1 May 2006, Kishore Jalleda wrote: On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck against a replication master database and it reported a problem with a table, which it corrected. Is the slave now out of sync? If so, how do I correct the problem without copying the whole database over a slow WAN link? --Eric By Default when you run REPAIR or myisamchk --recover , MySQL writes the changes made to the Binlog , and those will be obviously replicated to the slave, so if everything went fine with your myisamck on the master, then your slave is in sync and you don't have to do anyhthing special on the slave... This needs a bit clarification. First off, to my knowledge MySQL does not write [data] changes due to a REPAIR statement. Their replication implementation doesn't work that way. Before MySQL 4.1, REPAIR (and for instance OPTIMIZE) statements are not replicated at all, but are done so by default in 4.1 and up. So an important question is what version your MySQL server is. To answer the original posters question: It's hard to say if your slave is out of sync. If your master got corrupted from say hardware failure or if your MySQL daemon died unexpectedly and a REPAIR resulted in modified data, then your replication slave will most likely be out of sync and you will need to give it a fresh data seed. In that case a REPAIR statement on the slave will not fix the data consistency issue. I can't say this for sure, but I would imagine there aren't many scenarios where a REPAIR on a slave would fix consistency issues. Atle - Flying Crocodile Inc, Unix Systems Administrator I am using 5.0, and yes REPAIR TABLES is written by default to the Binlog. http://dev.mysql.com/doc/refman/5.0/en/repair-table.html Kishore Jalleda http://kjalleda.googlepages.com/projects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Re: How to find size of my database
Daniel de Veiga has already answered you on how to determine the size of your database by using the file system and simply looking at the size of the physical files in your database. Another possibility is that you could use the SHOW TABLE STATUS command in MySQL. If you go to your MySQL prompt and select a database, then use the SHOW TABLE STATUS command, like this: set tmp; show table status like '%'; you'll find a column called Data_length which tells you the length of the data file for each table. If you simply add the size of each table in the database together, you should have the size of the whole database. Please note that I'm not sure how accurate my suggestion is; you might find that Daniel's approach gives you a better answer. I'm not sure if the Data_length column considers all the overhead that you might have with a table, such as index sizes, pointer sizes, unusable space, etc. Perhaps someone else can jump in with a better approach; I'm inclined to think it can't be quite as easy as I suggested. Also, even if the Data_length column gives an accurate answer for the size of a table, it is rather tedious to have to execute the SHOW TABLE STATUS command and then manually sum up the various sizes. I don't think you can simply execute an SQL query that does all the work for you, which is very unfortunate. It is entirely possible that there is a MySQL command that gives you the actual size of each database directly, although I didn't find it when I searched the manual. Again, perhaps someone with more administrative experience with MySQL can suggest a better approach. If not, perhaps we need to make a feature request of the MySQL people :-) This would appear to be a very useful command to create if it doesn't already exist! -- Rhino - Original Message - From: Shivaji S [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, May 02, 2006 5:12 PM Subject: Re: Re: Re: How to find size of my database Hi Rhino, sorry ,for my unclear reply. the size i meant is in KB or MB.. Regards, Shivaji On Wed, 03 May 2006 Rhino wrote : You still haven't said what you mean by size! Let me put it to you this way: what units would be used in the answer you want? In other words, would the answer look like: 1. 234,000 KB? 2. 2.75 million rows? 3. 321 tables? Or something else altogether? -- Rhino - Original Message - From: Shivaji S [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, May 02, 2006 4:33 PM Subject: Re: Re: How to find size of my database Hi Rhino, Thanks for the input. I mean to say the total Database Size ,that is to see my current Database Size.I am using 4.0.20 version Regards, Shivaji On Wed, 03 May 2006 Rhino wrote : It would be easier to help if you specified what you meant by size. Do you mean the amount of space the database is using on your hard drive? Or the number of tables in the database? Or the number of rows in the tables? Or somethng else altogether? You should also mention which version of MySQL you are using; later versions have features and commands not found in earlier versions. Also, the size of a give database may differ from version to version of MySQL. -- Rhino - Original Message - From: Shivaji S [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, May 02, 2006 3:53 PM Subject: How to find size of my database Hi, is there any command to find the total size of mysql db. Regards, Shivaji. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to move the MySQL data directory?
On 02.05.2006 22:24 (+0100), Dan Buettner wrote: The easiest way might be to tell mysql in the config file where to look for the data directory. In your my.cnf file (typically /etc/my.cnf) under the [mysqld] section: datadir = /Volumes/mysql-data/data One of the main problems with a global config file is that I am running two servers on the same machine (MySQL 4.0 and 5.0, each with separate data directories of course). Currently I am using config files (my.cnf) in each server's data directory, but telling the server about another datadir inside that other datadir is obiously not reasonable. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] http://beta.unclassified.de – My web laboratory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Streaming LOB Data
Anyone know for sure if the memory needed to insert a LOB is a percentage of the system's available memory or if it is allocated from the innodb_buffer_pool_size? IOW, how should my configuration settings be modified to allow the insertion of larger blobs? :) -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Saturday, April 29, 2006 9:49 AM To: Sergei Golubchik Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: RE: Streaming LOB Data Thanks Sergei, it's nice to know for sure. Do you know if there is any documentation on how memory is used to store LOB data? For example, is it a percentage of the total buffer pool size or is it allocated from available memory un-allocated to the buffer pool size? -Original Message- From: Sergei Golubchik [mailto:[EMAIL PROTECTED] Sent: Saturday, April 29, 2006 9:34 AM To: Robert DiFalco Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Streaming LOB Data Hi! On Apr 28, Robert DiFalco wrote: It appears (for InnoDB at least) that while INSERTing a LOB that all LOB data must be loaded into memory before it is written to disk. Or is it just the size of the combined log files? Looking at Task Manager, it looks like it DOES try to load the whole thing into memory before streaming it to disk; which just seems wacky. The error I get is this: Out of memory (Needed xxx ... If the server crashes I get this: InnoDB: which exceeds the log group capacity 18870682. InnoDB: If you are using big BLOB or TEXT rows, you must set the InnoDB: combined size of log files at least 10 times bigger than the InnoDB: largest such row. 060427 15:26:53 InnoDB: Error: cannot allocate 539001144 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 52823786 bytes. Operating system errno: 8 InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: Note that in most 32-bit computers the process InnoDB: memory space is limited to 2 GB or 4 GB. How can I make the size of LOBs I insert NOT be memory constrained? No way. Unfortunately, it's the way MySQL works at the moment. Which does not mean it's not going to be changed in the future. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Kerpen, Germany ___/ 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: How to convert this DELETE command from MySQL 4.0.25 to 3.23?
2006-05-02
Thread
Adam i Agnieszka Gąsiorowski FNORD aka ALinkA ak a symbol '( { .A. } )'' ||~ { A.A }
On 2006-05-02, at 08:51, The Nice Spider wrote:
Wow, impressive pseudonym...Are you
strong enough to be my man? ;-}
DELETE FROM A
USING A
RIGHT JOIN B ON B.id = A.sectionid
WHERE B.id is null
...AND the_reader IS a_fool. Hell and damnation!
SQL POETRY! :-}
This task is deleted any row in A that have no items
on B.
Sorry, I'm too sober to parse that :-}}}.{{{-:
Cheers!
--
she the month of june. A summers' tune
i am forever be, so proud and free and
in the heart of juliette, one love she
won't forget. she longs to be, forever
give. like a child, so pure and wild n
virtuous. she longs to be caused she's
smime.p7s
Description: S/MIME cryptographic signature
RE: How to move the MySQL data directory?
Specify the data dir in the local my.cnf and be sure your instance uses it by starting it with the --defaults-file parameter set to that instance's local copy. Regards, Paul -Original Message- From: Yves Goergen [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 02, 2006 3:11 PM To: Dan Buettner Cc: mysql@lists.mysql.com Subject: Re: How to move the MySQL data directory? On 02.05.2006 22:24 (+0100), Dan Buettner wrote: The easiest way might be to tell mysql in the config file where to look for the data directory. In your my.cnf file (typically /etc/my.cnf) under the [mysqld] section: datadir = /Volumes/mysql-data/data One of the main problems with a global config file is that I am running two servers on the same machine (MySQL 4.0 and 5.0, each with separate data directories of course). Currently I am using config files (my.cnf) in each server's data directory, but telling the server about another datadir inside that other datadir is obiously not reasonable. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] http://beta.unclassified.de - My web laboratory. -- 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]
Have You Seen My CV?
__ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Have You Seen My CV?
Anago Chima wrote: Tired of spam? Yahoo! Mail has the best spam protection around Oh the irony. -- Rob Munsch Solutions For Progress IT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Should I upgrade to 4.1 or 5.0 (Newbie level question)
MySQL Mailing List, Currently I'm running MySQL 4.1.15 on my home computer where I do web development, and MySQL 4.1.13-beta on my hosting service. Actually, I do work on web sites that are hosted on many hosting services, but on only one of them do I have enough access to decide for myself if I want to upgrade the server. It seems that MediaWiki, something I'm currently experimenting with, requires MySQL 4.1.14 or above. So at the very least I need to upgrade MySQL on the hosting service. But I'm a little confused by the presence of MySQL 5.0. On the mysql.com web site, it promotes version 5.0 and barely makes any mention of previous versions. (Of course, in the support documentation there is a lot about previous versions, but I'm just talking about the site's promotional text.) Ordinarily, a new version of any software wouldn't be confusing. I would assume that whatever the latest version that the developers are making available is the one that is supported and stable and preferable to use. But none of my hosting services anything higher than 4.1.15. And my home computer, which runs Ubuntu, defaulted to installing 4.1.15 and uthe application update manager doesn't update it to 5.0. So if 5.0 is the current version of MySQL, why does it seem to me that it's not widely adopted? Would it be problematic now or later if I upgraded to 5.0? Would I have to soon upgrade to 5.0 if I upgraded to 4.15 now? Thank you for any advice. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP --- Slow SP
Guys, So many thanks for you kind help. I was able to find the culprit, just adding 1 index i can get as fast as 30 thousands record per hour. So that I can proccess 2 million data in about 4 days only. But now, after my main tables loaded with more than 2 million data (2,9 million), i have another problem with the same SP. After some time (about each 25 execution loops), i always got this error message: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction How can this be happening since I am the only who is doing anything with the DB? Rgds/Hardi
Possible show databases bug with many databases
Hi, we've had a strange problem with mysql for quite some time which could not be solved so far. The problem is with servers with a lot of databases (10.000+) If one connects to mysql and issues a show database this will take ages and eat up 100% cpu. It does not matter if you connect as root or as a standard user that has only privileges for one database. If you set no-auto-rehash in the cnf then show databases will be fast again as root. It still takes ages as a normal user though. e.g. mysql -uroot -p mysql show databases; ... +--+ 55693 rows in set (0.21 sec) mysql mysql -uproftpd -p mysql show databases; +--+ | Database | +--+ | proftpd | +--+ 1 row in set (4.70 sec) If auto-rehash is not disabled then show databases / use database takes ages for both root / standard users (around 15 sek in the example). This is not really a hardware / filesystem problem I think. We have thrown any possible hardware at it. The examples above are on a Dual Opteron 280 16GB RAM 12x 18GB 15K RPM SCSI @ Mylex 600 U320 RAID 10 Filesystem XFS (also tested ReiserFS, other people with the same problem also tested ext3) 30-60k Databases (problem also exists a bit slighter on a small set of 7000 databases - hardware independend no matter what you throw at it) MySQL 4.0.26 AMD64 (problem also verified on i386 with different sets of databases + servers etc) Server is not in use, not other processes running etc. Almost no filesytem activity during show databases. Only 100% CPU. I have also found other references to this problem: http://lists.mysql.com/mysql/196396 http://lists.mysql.com/mysql/197140 http://www.rootforum.de/forum/viewtopic.php?t=39984 (german, examples + same problem) If you need any additional information I can provide you with whatever you need. Thank you for your time and efforts. Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude)
I haven't personally done a store locator, but... Store locators are relatively crude, yet useful. It is unlikely that exacting math will make one less crude in such a way as to make it significantly more useful. They usually just display a list of locations that *might* be convenient, and that's probably good enough. I'd guess that a majority or potential users wouldn't know their Z+4, wouldn't type it in if they did, and would be unwilling to give address/phone info, except when required for travel directions. Even if one were to know the user's exact coordinates, the distance between coordinates is crow-fly distance, not travel distance, and not travel time. Rivers, mountains, congested areas, and so on make exact distance a crude approximation of location convenience. As to the recommendations for (U.S.) databases -- I think they're all derived from the Census Bureau's Tiger database -- even the products from the Post Office that provide latitude/longitude. The PO version probably has good updates, but the Census Bureau releases theirs every couple of years, presumably with PO updates, and it's free (and large). I don't know if any of the commercial versions are improved in any way. As to the concerns over the varying coordinates between web sites, they're probably all correct (from a quick look at some of the differences posted, they appeared to be within 1/4 of a mile). Other than what appears in the Tiger files, there is no official coordinate for a zip code, because zips aren't points. They aren't even geographic areas (like a city or county with boundaries). They're just networks of delivery destinations. The Census Bureau developed Zip Code Tracking Areas (ZCTA) as approximations to the geographic areas covered by each zip. The listed coordinates are a calculated crude geographic center for each of the resulting irregularly shaped areas. Given all the approximations and that ZCTAs can be hundreds of square miles, it's obvious that any derived location information is crude at best. Zip+4 would indeed be less crude, but still problematic and likely no more useful. Probably less than $.02 worth. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Re: Re: How to find size of my database
Thanks Daniel,Rhino for the wounder full explanations given.I will work on finding the size of my databases with answers given by you. Regards, Shivaji. On Wed, 03 May 2006 Rhino wrote : Daniel de Veiga has already answered you on how to determine the size of your database by using the file system and simply looking at the size of the physical files in your database. Another possibility is that you could use the SHOW TABLE STATUS command in MySQL. If you go to your MySQL prompt and select a database, then use the SHOW TABLE STATUS command, like this: set tmp; show table status like '%'; you'll find a column called Data_length which tells you the length of the data file for each table. If you simply add the size of each table in the database together, you should have the size of the whole database. Please note that I'm not sure how accurate my suggestion is; you might find that Daniel's approach gives you a better answer. I'm not sure if the Data_length column considers all the overhead that you might have with a table, such as index sizes, pointer sizes, unusable space, etc. Perhaps someone else can jump in with a better approach; I'm inclined to think it can't be quite as easy as I suggested. Also, even if the Data_length column gives an accurate answer for the size of a table, it is rather tedious to have to execute the SHOW TABLE STATUS command and then manually sum up the various sizes. I don't think you can simply execute an SQL query that does all the work for you, which is very unfortunate. It is entirely possible that there is a MySQL command that gives you the actual size of each database directly, although I didn't find it when I searched the manual. Again, perhaps someone with more administrative experience with MySQL can suggest a better approach. If not, perhaps we need to make a feature request of the MySQL people :-) This would appear to be a very useful command to create if it doesn't already exist! -- Rhino - Original Message - From: Shivaji S [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, May 02, 2006 5:12 PM Subject: Re: Re: Re: How to find size of my database Hi Rhino, sorry ,for my unclear reply. the size i meant is in KB or MB.. Regards, Shivaji On Wed, 03 May 2006 Rhino wrote : You still haven't said what you mean by size! Let me put it to you this way: what units would be used in the answer you want? In other words, would the answer look like: 1. 234,000 KB? 2. 2.75 million rows? 3. 321 tables? Or something else altogether? -- Rhino - Original Message - From: Shivaji S [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, May 02, 2006 4:33 PM Subject: Re: Re: How to find size of my database Hi Rhino, Thanks for the input. I mean to say the total Database Size ,that is to see my current Database Size.I am using 4.0.20 version Regards, Shivaji On Wed, 03 May 2006 Rhino wrote : It would be easier to help if you specified what you meant by size. Do you mean the amount of space the database is using on your hard drive? Or the number of tables in the database? Or the number of rows in the tables? Or somethng else altogether? You should also mention which version of MySQL you are using; later versions have features and commands not found in earlier versions. Also, the size of a give database may differ from version to version of MySQL. -- Rhino - Original Message - From: Shivaji S [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, May 02, 2006 3:53 PM Subject: How to find size of my database Hi, is there any command to find the total size of mysql db. Regards, Shivaji. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006
RE: Getting the previous months documents
Ok, a little more clear:
$query = SELECT *
//Change tempdate to the table name of your board document table
FROM `tempdate`
//Change temptext to the field name of the date in your board doc table
WHERE CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, 4, 2),
SUBSTRING(temptext, 1, 2)) '060703'
//Same here
ORDER BY CONCAT(SUBSTRING(temptext, 7,
2),SUBSTRING(temptext, 4, 2), SUBSTRING(temptext, 1, 2)) DESC LIMIT 1;
If this does not help, give the table structure and I create the query for
you. My suggestion is to write this query in MySQL directly first, do not
use PHP to try to get a query to work.
Try to understand the SUBSTRING command and CONCAT command first. Read the
documentation on www.mysql.com.
/Peter
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 02, 2006 5:07 PM
To: Peter Lauri
Subject: Re: Getting the previous months documents
That looks very complicated but I suppose it converts the date and compares.
What does it return? I need the returned result to be an associative array
of the previous date. Should it be select * FROM board_papers concat..
This is my code with your query in it. But it doesn't return anything.
$query= SELECT CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, 4, 2),
SUBSTRING(temptext, 1, 2)) AS docdate FROM `tempdate` WHERE
CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, 4, 2),
SUBSTRING(temptext, 1, 2)) '060703' ORDER BY CONCAT(SUBSTRING(temptext, 7,
2),SUBSTRING(temptext, 4, 2), SUBSTRING(temptext, 1, 2)) DESC LIMIT 1;
$result= mysql_query($query);
while ($row = @mysql_fetch_array($result, MYSQL_ASSOC)){
echo $row['doc_date'];
$row['fileSize'] = $row['fileSize']/ 1024;
$row['fileSize']= number_format($row['fileSize'], 0);
$size= $row['fileSize'];
$name = str_replace(_, , $row['fileName']);
$name = str_replace(.pdf, , $name);
$link= $row['content'];
$id=$row['id'];
?
Thanks for your help.
- Original Message -
From: Peter Lauri [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Tuesday, May 02, 2006 10:20 AM
Subject: RE: Getting the previous months documents
I did this, sorry for bad format. This will do it for you.
SELECT CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, 4, 2),
SUBSTRING(temptext, 1, 2)) AS docdate
FROM `tempdate` WHERE CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext,
4, 2), SUBSTRING(temptext, 1, 2)) '060703' ORDER BY
CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, 4, 2),
SUBSTRING(temptext, 1, 2)) DESC LIMIT 1
/Peter
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 02, 2006 3:28 PM
To: Peter Lauri; mysql@lists.mysql.com
Subject: Re: Getting the previous months documents
My sql is not so great. The dates are entered through a dropdown box in
the
format dd/mm/ (uk date format) as a VARCHAR.
I could use strrev and str_replace (PHP) to get it in the correct format
but
will the mysql query you send work on a VARCHAR which it is it present or
will I have to change the field to DATE?
Ross
- Original Message -
From: Peter Lauri [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, May 02, 2006 9:06 AM
Subject: RE: Getting the previous months documents
You should start by using MySQL date as the standard for date: -MM-DD
After that it is simple:
SELECT * FROM documents WHERE doc_date2006-05-02 ORDER BY doc_date DESC
LIMIT 1;
Othervise you could use MySQL function to take sub strings and create a
field in the query that extract it as 06-05-02 and order by that.
/Peter
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 02, 2006 3:04 PM
To: mysql@lists.mysql.com
Subject: Getting the previous months documents
I have a database that stores documents relating to meetings. They have
all
the usual stuff agenda, minutes etc. There are 3 paper types for each
date
agenda (1 only), minutes (1 only), and a bunch of general documents
titled
'papers'.
I need to display all the documents for a specific date which is easy but
I
also need to retrieve the minutes for the PREVIOUS meeting which is
proving
more difficult. The documents are stored by date in the format dd/mm/yy.
If
someone wants the documents from 02/05/06 how do I find the minutes for
the
previous date when I do not know when it is?
Can I do this with mysql? Or will it be better with mktime and some php?
--
-- Table structure for table `board_papers`
--
CREATE TABLE `board_papers` (
`id` int(4) NOT NULL auto_increment,
`doc_date` varchar(10) NOT NULL default '-00-00',
`article_type` enum('agenda','minutes','paper') NOT NULL default
'agenda',
`fileName` varchar(50) NOT NULL default '',
`fileSize` int(4) NOT NULL default '0',
`fileType` varchar(50) NOT NULL default '',
`content` blob NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Re: Streaming LOB Data
Hi! On Apr 29, Robert DiFalco wrote: Thanks Sergei, it's nice to know for sure. Do you know if there is any documentation on how memory is used to store LOB data? For example, is it a percentage of the total buffer pool size or is it allocated from available memory un-allocated to the buffer pool size? No, I don't. Sorry :( Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Kerpen, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ndbd --initial startup issues in mysql cluster
hi, i am trying to install a cluster. i am facing issues in starting up the ndbd. My configuration is as follows. *Config.ini:* [NDBD DEFAULT] NoOfReplicas=2 [MYSQLD DEFAULT] [NDB_MGMD DEFAULT] [TCP DEFAULT] # Section for the cluster management node [NDB_MGMD] # IP address of the management node (this system) Id=1 HostName=192.168.0.103 # Section for the storage nodes [NDBD] # IP address of the first storage node Id=2 HostName=192.168.0.101 DataDir= /usr/local/mysql/data/ [NDBD] # IP address of the second storage node Id=3 HostName=192.168.0.102 DataDir/usr/local/mysql/data/ # one [MYSQLD] per storage node [MYSQLD] [MYSQLD] i added the following lines to the already existing *my.cnf* in both data nodes: [mysqld] ndbcluster # IP address of the cluster management node ndb-connectstring='host=192.168.0.103' [mysql_cluster] # IP address of the cluster management node ndb-connectstring='host=192.168.0.103' when i execute ndbd --initial in data node 1 i get no error when i execute ndbd --initial in data node 2 i am getting the following error: Date/Time: Tuesday 2 May 2006 - 14:17:06 Type of error: error Message: Invalid Configuration fetched from Management Server Fault ID: 2350 Problem data: Could not connect initialize handle to management server Object of reference: ProgramName: ndbd ProcessID: 22351 TraceFile: no tracefile ***EOM*** when i connect to the management node and give a show command i get the following output: show Cluster Configuration - [ndbd(NDB)] 2 node(s) id=2 (not connected, accepting connect from wci001.cricket.net) id=3 (not connected, accepting connect from wci002.cricket.net) [ndb_mgmd(MGM)] 1 node(s) id=1 @192.168.0.103 (Version: 5.0.20) [mysqld(API)] 2 node(s) id=4 (not connected, accepting connect from any host) id=5 (not connected, accepting connect from any host) As you can find here even the first data node is not connected. i have installed cluster from mysql-max-5.0.20-linux-i686-glibc23.tar.gz Can any one help me in solving this issue? Thanks in advance. -pradeep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimizing mysql client/server interactions
Hi All, Our current setup has the mysql server and the client app run on the same host. We are using mysql++ (ports/mysql++-1.7.9) for the database client api. The application returns huge data sets in the order of 50K. When we do a show processlist most of the threads spend their time in the state sending data. With the current setup data seems to go up and down the network stack for sending data to a client on the same host. Can this be short circuited somehow for better performance. Thanx Alex
RE: Re: How to find size of my database
1) I'll assume you use myisam tables, as your version is kinda outdated. 2) I'll assume you want the total DISK space required for your database. (could be rows, could be tables, could be space used by the rows and tables, all of those are different, different storage engines use space differently, a innodb table could use 1GB in your disk, and 800MB on rows) 3) I'll assume that you KNOW how to get the size of a given file/directory on your Operating System. If I'm wrong in any assumption, please let me know. Locate your data directory, if you don't know where it is, or don't have access to it, you just can't do this (you can always take a look at the manual, windows is usually the data directory, linux is somewhere in /var/lib, YMMV). Into this directory, you'll see a new directory for every database, getting the size of the directory you see how much disk space the database has taken. -- Daniel da Veiga Computer Operator - RS - Brazil Hence on linux, if your database folder is /var/lib/mysql/mydb, then execute : du -sh /var/lib/mysql/mydb Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lock Wait Timeout Problem
Hi, I have 1 SP that does INSERT and UPDATE query to 1-2 tables. It runs ok for sometime, but i sometime got this error message: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction The message would ussually dissapear and the SP runs well for sometime until the same error message comes again. How can this be happening since I am the only who is doing anything with the DB? Rgds/Hardi
