Re: recurring corrupt table?
Jed Reynolds wrote: I'm seeing errors in my application where I'm getting from simple selects every few hours: I do a flush table activity_profiles; check table activity_profiles; and the table seems to have fixed itself. Any thots? Thanks for the thots, guys. It turns out one of my co-masters regressed it's my.cnf file...with the same auto_increment_offset value. I think replication was creating primary key collisions that were then re-written by concurrent activity. Lost data resulted, but the table didn't get trashed. Jed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to remove the duplicate values in my table!
I have always used this for de-duplicating... ALTER IGNORE TABLE mytbl ADD UNIQUE KEY ( myField1, myField1 ) ; It works a treat, hope it helps Roger -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: 20 November 2008 00:35 To: jean claude babin Cc: mysql@lists.mysql.com Subject: Re: How to remove the duplicate values in my table! On Nov 19, 2008, at 3:24 AM, jean claude babin wrote: Hi, I found the bug in my servlet ,when I run my application it enter one record to the database without duplicate values.Now I want to clean my table by removing all duplicate rows .Any thoughts? I assume you have a unique record identifier like and auto_increment field? If you not, add and auto_increment field, you have to have a unique ID. Assuming the deviceId field is what indicates a duplicate: SELECT max(uniqueId) maxUid, deviceId, count(*) c FROM table GROUP BY deviceId HAVING c1 That will give you the highest unique Id of each duplicate, which is what you want to delete assuming you want to keep the first record. If you want to keep the latest, change it to min. Then you want to join on that select so you can use it as your delete filter. DELETE table FROM table JOIN ( SELECT max(uniqueId) maxUid, deviceId, count(*) c FROM table GROUP BY deviceId HAVING c1 ) as dupSet ON dupSet.maxUid=table.uniqueId That will delete one duplicate record for each duplicate group at a time. So if you have 10 of the same duplicate, you need to run the query 9 times. It wouldn't be too hard to add another subquery (i.e. LEFT JOIN on the dup select WHERE table.uniqueId IS NULL) to that to filter so you can delete all duplicates in 1 shot. This has always been something I had to do very infrequently, so I never bothered taking it further. Hope that help! Brent Baisley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.175 / Virus Database: 270.9.2/1782 - Release Date: 11/19/2008 6:55 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding not quite duplicates
As a sidenote, your strings should be enclosed by single quotes, as per SQL standard, not double quotes, those are reserved for delimited identifiers. Hmmm. I've sort-of carried that over from the way I do PHP. I tend to use single quotes for strings that have no variables in them, so I use double-quotes around strings within the strings: $query = 'UPDATE foo SET field = always' That way I don't have to escape things, which I think makes them harder to read. I'll take your comment under advisement. Although that's true for string literals, you would still need to escape any user input since MySQL doesn't properly support parameters (or does it in 5.1 or so?) As I said, a complete side note :-) Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Sybase SQL Anywhere, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Replication in mySQL version 5
Tompkins Neil wrote: Hi We are looking to upgrade our version of mySQL to the latest version of mySQL 5. One of the main features we are going to think about using is replication for our website data. Basically we have 2 websites located in the UK and US which share similar information, and we are going to be using replication as a way of keeping the data up to date and in sync. Based on your experiences, is there anything we should be aware of before investigating this route and putting it into practice ? I've had to take servers out for bad raid-controllers, bad ram, bad mobos. Disks have been the least of my problems. So make sure your architecture tolerates the ability to take members of your pool out without load-spiking the remaining members. And if you're doing filesystem snapshots from a master to a replicant, you will have to either have policy or extra servers available to maintain your uptime when you interrupt the master to flush all the tables, sync the filesystem and do an LVM snapshot. Innodb would require a shutdown. Don't forget that LVM snapshots are copy-on-write, so when that master comes back up and starts processing modifying tables, you'll get amazing system load on a busy system as your file system starts madly copying extents into the snapshot volume. Define a procedure for junior staff how to properly down and up a pool member. Like, if you get a disk-full on one member, and it borks replication, what's the step-by-step for a) determining if replication can re-establish after you do a FLUSH LOGS, b) under what conditions do you have to re-copy all data from one master to another because your replication window has expired and your logs have gotten flushed. Your replication binlogs get really big if you're pushing large materialized views regularly via replication, or your servers have fast disks, not enough size to handle a more than a weekend or whole day (for example) of neglect. Define a procedure for checking your my.cnf files for correct auto-increment-* settings and server-id settings. Junior staff, and even senior staff rarely add more members to the pool, so these settings are often mistaken during a midnight maintenance hour. Procedure for adding members and changing master replication settings is very important. Often your DBA is not racking and changing the equipment. Make sure that you have a good understanding of what kind of capacity you're growing at. I started a project with two four-core boxes with plenty of 15krpm disk and when they got into production, they regularly spiked to load 20 and 30. Not pretty. Not only had my old architecture refused traffic to lighten the load, my new architecture didn't. My data set was growing so fast my sort-buffer settings for the old servers were too small for new servers. I ended up with four DL380s with 8 cores per box. I really had to scramble to get more servers in there. The addition of two more read-only members really helped, and backups handled by replication to an off-site replicant. Another load capacity warning: if your traffic is very spiky, and you get high-load conditions, I've seen reset/dropped connections and also plain old connection timeouts. So if you have RAM for 1024 connections, you prolly can't service 1024 connections when you've got table contention and connections from your web-nodes just start failing. If they fail for too long, then you have to do some FLUSH HOSTS to reset connection attempt counters. I don't know what your application does, but I certainly monitor replication lag. Load spikes can certainly increase lag. I've had to move from single instances of mysql to mysqld_multi and separate databases by replication rate. Your monitoring should also track sql threads. You might need to define procedure on how to deal with pooling-out members that fall too far behind in replication. I've written an iptables script to block webnode connections but allow sql pool member connections. I use this to take a member out to run table repairs or to lighten the load while it does replication catch-up. WAN connectivity for replication is interesting! I did site-to-site transfer using stunnel. I had to negotiate weird Cisco 5502 VPN behavior. Copying gigs of myisam files between sites would knock over my vpn so I had to rate-limit using rsync --bwlimit. Bursting bandwidth charges were still brutal, though. Later, we ended up configuring CBQ (search freshmeat.net for cbq-init) on my backup replicant to limit bandwidth so it wouldn't provoke bursting charges. Jed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MBRWithin bug?
Chris, I might be being an idiot. Yes, you are :-) -122.1529 is between -121.148 and -121.1575 This is not true! -122.something cannot be between -121.somethingother and -121.somethingelse -121.1529 is between -121.148 and -121.1575 Regards, -- Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slave content differ from master!!
Hi, Yesterday i encounter a very strange problem, i found some data on a replication db differ from that on the master. master: +-+++-+-++ | id | date | uid| type_id | report_type | amount | +-+++-+-++ | 2721193 | 2008-11-11 | 534581 | 15 | 1 |200 | +-+++-+-++ slave: +-+++-+-++ | id | date | uid| type_id | report_type | amount | +-+++-+-++ | 2721193 | 2008-11-11 | 534581 | 15 | 1 | -4800 | +-+++-+-++ the table: CREATE TABLE `report` ( `id` int(10) unsigned NOT NULL auto_increment, `date` date NOT NULL default '-00-00', `uid` int(10) unsigned NOT NULL default '0', `type_id` int(10) unsigned NOT NULL default '0', `report_type` tinyint(4) NOT NULL default '0', `amount` int(11) NOT NULL default '0', PRIMARY KEY (`id`), UNIQUE KEY `date` (`date`,`uid`,`type_id`,`report_type`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Then i go to the relay-log and found two transactions: # at 1002607297 #08 19:52:03 server id 1 log_pos 992050073 Intvar SET INSERT_ID=2721193; # at 1002607325 #08 19:52:03 server id 1 log_pos 992050073 Query thread_id=9490103 exec_time=0 error_code=0 SET TIMESTAMP=1226404323; INSERT INTO report (date, uid, type_id, report_type, amount) VALUES (DATE(NOW()), 534581, 15, 1, 200) ON DUPLICATE KEY UPDATE amount = amount + (200); # at 1002609187 #08 19:52:03 server id 1 log_pos 992050073 Intvar SET INSERT_ID=2721193; # at 1002609215 #08 19:52:03 server id 1 log_pos 992050073 Query thread_id=9478450 exec_time=0 error_code=0 SET TIMESTAMP=1226404323; INSERT INTO report (date, uid, type_id, report_type, amount) VALUES (DATE(NOW()), 548013, 17, 6, -5000) ON DUPLICATE KEY UPDATE amount = amount + (-5000); It seems that the two insert ... on duplicate got the same insert_id so they are 'duplicated' and mix together! Which is not the case happened in the master. I also checked the master bin log and are the same as relay log. Is it a bug of bin log? I try a whole day to reappear it, but failed.. Is there any hint about this problem? Thanks, jayven
Error on MySQL-5.0
Hi, I installed MySQL-5.0.67_1. When I execute CHECK TABLE information_schema.COLUMNS FOR UPGRADE I get the message: ++---+--++ | Table | Op| Msg_type | Msg_text | ++---+--++ | information_schema.COLUMNS | check | error| Table upgrade required. Please do REPAIR TABLE `/var/tmp/#sql_43b6_0` to fix it! | ++---+--++ The same occurs for tables ROUTINES, TRIGGERS and VIEWS. If I execute REPAIR TABLE COLUMNS I got: ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema' So I GRANT ALL ON information_schema.* TO 'root'@'localhost'; and got the same error: ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema' So I backed to command shell and mysqlcheck -u root -p --repair information_schema. It gives me no error, but the problem persists: When I execute CHECK TABLE information_schema.COLUMNS FOR UPGRADE I get the message: ++---+--++ | Table | Op| Msg_type | Msg_text | ++---+--++ | information_schema.COLUMNS | check | error| Table upgrade required. Please do REPAIR TABLE `/var/tmp/#sql_43b6_0` to fix it! | ++---+--++ Any help would be appreciate. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Virtualizing MySQL
Ok...based on the responses that I received so far...it seems like maybe I should be leaning toward a non virtualized solution. What I am wondering now is... 1)would it be better to have one MySQL instance running and have the developers each have their own DB inside that one instance? or 2) would it be better to have each developer have their own MySQL instance on the same machine? or 3) some combination of the above...maybe have the developers split between 2 or 3 MySQL instances on the same machine... Any thoughts? Thanks again, Shain Simon J Mudd wrote: [EMAIL PROTECTED] (Shain Miley) writes: I am looking into the idea of setting up 10 - 15 virtualized instances of MySQL. The reason for this is as follows...we are going to be setting up a 3 to 4 node MySQL replication cluster (1 master-rw and 2 slaves-ro)...each having 16 to 32 GB of RAM. In order for our development team to do their work...they must have access to some Mysql resources that are close to the production environment. I am not currently in a position to provide each developer two MySQL servers (one master and one slave with 16 to 32 GB of RAM) for testing...or obvious reasons...mainly cost ;-) So I have been thinking about how best to provide such resources, at this point I am thinking that I can use OpenVZ to help me out a bit. I was wondering if anyone had any thoughts on this issue...should I just run 10 instances of MySQL on the same server...are there other options? I am concerned with trying to ensure that the metrics, resources, workloads, etc from these development servers has some sort of relevance to our production environment...otherwise we are testing apples and oranges...which the dev team will clearly point out...and in a way I know we are...but I would like to minimize the effects My only concern would be that if you have busy mysql instances that they will interfere with each other. We used to have a couple of busy mysqld processes running on the same Linux server only to find that the performance characteristics were worse than 1/2 of the performance of having each instance on a separate server. Both mysqld instances were busy and so fought each other for I/O and for CPU often at the same time. If this might be an issue for your virtual servers may not be an ideal solution as most of the free virtualisation options don't control sufficiently the hardware resources distributed to each virtual machine. YMMV. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Virtualizing MySQL
Check out Giuseppe Maxia's MySQL Sandbox program. It is a very easy way to run however many MySQL servers you want with separate config files and such .. heck..even separate versions if you want (one 5.0, one 5.1, one 6.0). It is available here: https://launchpad.net/mysql-sandbox Will take you 10 minutes to set up if you have any perl experience whatsover..otherwise it might take a half hour. Worth the time. Google as there are several presentations on it available online. Keith Shain Miley wrote: Ok...based on the responses that I received so far...it seems like maybe I should be leaning toward a non virtualized solution. What I am wondering now is... 1)would it be better to have one MySQL instance running and have the developers each have their own DB inside that one instance? or 2) would it be better to have each developer have their own MySQL instance on the same machine? or 3) some combination of the above...maybe have the developers split between 2 or 3 MySQL instances on the same machine... Any thoughts? Thanks again, Shain Simon J Mudd wrote: [EMAIL PROTECTED] (Shain Miley) writes: I am looking into the idea of setting up 10 - 15 virtualized instances of MySQL. The reason for this is as follows...we are going to be setting up a 3 to 4 node MySQL replication cluster (1 master-rw and 2 slaves-ro)...each having 16 to 32 GB of RAM. In order for our development team to do their work...they must have access to some Mysql resources that are close to the production environment. I am not currently in a position to provide each developer two MySQL servers (one master and one slave with 16 to 32 GB of RAM) for testing...or obvious reasons...mainly cost ;-) So I have been thinking about how best to provide such resources, at this point I am thinking that I can use OpenVZ to help me out a bit. I was wondering if anyone had any thoughts on this issue...should I just run 10 instances of MySQL on the same server...are there other options? I am concerned with trying to ensure that the metrics, resources, workloads, etc from these development servers has some sort of relevance to our production environment...otherwise we are testing apples and oranges...which the dev team will clearly point out...and in a way I know we are...but I would like to minimize the effects My only concern would be that if you have busy mysql instances that they will interfere with each other. We used to have a couple of busy mysqld processes running on the same Linux server only to find that the performance characteristics were worse than 1/2 of the performance of having each instance on a separate server. Both mysqld instances were busy and so fought each other for I/O and for CPU often at the same time. If this might be an issue for your virtual servers may not be an ideal solution as most of the free virtualisation options don't control sufficiently the hardware resources distributed to each virtual machine. YMMV. Simon -- Editor MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Virtualizing MySQL
Hi Shain, all! Shain Miley wrote: Ok...based on the responses that I received so far...it seems like maybe I should be leaning toward a non virtualized solution. Virtualization includes overhead. It is fine as long as your application can tolerate that, but if your performance demands grow there will be a point where a DB server in a virtual machine will cause trouble but the same HW as a real machine would still suffice. What I am wondering now is... 1)would it be better to have one MySQL instance running and have the developers each have their own DB inside that one instance? or 2) would it be better to have each developer have their own MySQL instance on the same machine? or 3) some combination of the above...maybe have the developers split between 2 or 3 MySQL instances on the same machine... This depends on the number of developers. I would try to have separate instances, to isolate developers against each other (allow independent start/stop, separate configuration, isolate dumps, allow independent recovery, protect against crashes, ...) The obvious drawback is that this will take slightly more RAM, and that caches will not be shared. You need not fear multiple code pages in RAM as long as all instances use the same code files. If you can't have one per developer, at least try to have more than one in total. Several DB server instances in the same OS instance on a real machine will still need less RAM than equivalent DB instances in separate OS virtual machines. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Virtualizing MySQL
[snip] Virtualization includes overhead. It is fine as long as your application can tolerate that, but if your performance demands grow there will be a point where a DB server in a virtual machine will cause trouble but the same HW as a real machine would still suffice. [/snip] We run MySQL in virtualized environments processing millions of records a day (virtual servers interact with our SAN for storage) and have actually enjoyed performance increases. We are also able to take advantage of advanced disaster recovery/business continuity options available to us in this kind of environment. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Virtualizing MySQL
quote we are going to be setting up a 3 to 4 node MySQL replication cluster (1 master-rw and 2 slaves-ro)...each having 16 to 32 GB of RAM. quote If it is still true what you wrote you need different installations. Of course master and slave on the same host has the only use of an online backup solution, better if using different storage for data partitions, anyway adding not much to high availability. But if your only concern is to test a Master/Slave configuration I would go for multiple instances on same host. If you need a complete description on how to do it contact me. Sorry if I repeat myself, but for reliable test you should have the same architecture for both prod and preprod, Claudio Nanni Shain Miley wrote: Ok...based on the responses that I received so far...it seems like maybe I should be leaning toward a non virtualized solution. What I am wondering now is... 1)would it be better to have one MySQL instance running and have the developers each have their own DB inside that one instance? or 2) would it be better to have each developer have their own MySQL instance on the same machine? or 3) some combination of the above...maybe have the developers split between 2 or 3 MySQL instances on the same machine... Any thoughts? Thanks again, Shain Simon J Mudd wrote: [EMAIL PROTECTED] (Shain Miley) writes: I am looking into the idea of setting up 10 - 15 virtualized instances of MySQL. The reason for this is as follows...we are going to be setting up a 3 to 4 node MySQL replication cluster (1 master-rw and 2 slaves-ro)...each having 16 to 32 GB of RAM. In order for our development team to do their work...they must have access to some Mysql resources that are close to the production environment. I am not currently in a position to provide each developer two MySQL servers (one master and one slave with 16 to 32 GB of RAM) for testing...or obvious reasons...mainly cost ;-) So I have been thinking about how best to provide such resources, at this point I am thinking that I can use OpenVZ to help me out a bit. I was wondering if anyone had any thoughts on this issue...should I just run 10 instances of MySQL on the same server...are there other options? I am concerned with trying to ensure that the metrics, resources, workloads, etc from these development servers has some sort of relevance to our production environment...otherwise we are testing apples and oranges...which the dev team will clearly point out...and in a way I know we are...but I would like to minimize the effects My only concern would be that if you have busy mysql instances that they will interfere with each other. We used to have a couple of busy mysqld processes running on the same Linux server only to find that the performance characteristics were worse than 1/2 of the performance of having each instance on a separate server. Both mysqld instances were busy and so fought each other for I/O and for CPU often at the same time. If this might be an issue for your virtual servers may not be an ideal solution as most of the free virtualisation options don't control sufficiently the hardware resources distributed to each virtual machine. YMMV. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Cluster
Hi, Does anybody has a tip to install a MySQL Cluster in a Linux CentOS-5? Is it better from source or can it be from yum? I do prefer yum because it's easier for upgrades, but I don't know if the available package was compiled for that. Thank you, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
(Q) FullText (UTF8)
We are using MySQL 5.0.22 on CENTOS/redhat linux. The table and database
character-sets are all utf8.
We have a database supporting numerous languages. Of course, full-text works
beautifully with most of the languages.
But Chinese and Japanese are giving us problems, and there is NO reason why it
should be a problem since we are taking measures to help the database see
word-breaks.
When we insert the Chinese and Japanese passages, they have spaces (normal
ASCII $14-#32) between each word (verified). So basically if you have two
words like {APPLE}{DRUM} then we put {APPLE} then space then {DRUM}. If you
have UTF-8 then you can look at this sample, 三坐标测量机 固定架
When we try to match either {APPLE} or {DRUM} individually (or technically
三坐标测量机 or 固定架 ) then MySQL fails to find a match against anything. But
clearly it should find those.
MySQL is only finding matches for Japanese and Chinese on exact full-string
matches, which is clearly less than ideal.
I have already changed the ft min length setting to 1, to no avail.
What is going wrong, and how do I fix this?
Here is my sample query (selecting for ONE word
select *
from category_attributes
where match ( value ) against ( '三坐标测量机' ) 0
When I replace the word with固定架 then it still doesn't match anything. And
there is a row with merely
三坐标测量机 space固定架
Tim...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (Q) FullText (UTF8)
Have you tried in boolean mode?
Santino Cusimano
At 16:30 -0500 20-11-2008, Little, Timothy wrote:
We are using MySQL 5.0.22 on CENTOS/redhat
linux. The table and database character-sets
are all utf8.
We have a database supporting numerous
languages. Of course, full-text works
beautifully with most of the languages.
But Chinese and Japanese are giving us problems,
and there is NO reason why it should be a
problem since we are taking measures to help the
database see word-breaks.
When we insert the Chinese and Japanese
passages, they have spaces (normal ASCII
$14-#32) between each word (verified). So
basically if you have two words like
{APPLE}{DRUM} then we put {APPLE} then space
then {DRUM}. If you have UTF-8 then you can
look at this sample, éOçø±Í¾ó ä åíËâÀ
When we try to match either {APPLE} or {DRUM}
individually (or technically éOçø±Í¾ó ä or
åíËâÀ ) then MySQL fails to find a match
against anything. But clearly it should find
those.
MySQL is only finding matches for Japanese and
Chinese on exact full-string matches, which is
clearly less than ideal.
I have already changed the ft min length setting to 1, to no avail.
What is going wrong, and how do I fix this?
Here is my sample query (selecting for ONE word
select *
from category_attributes
where match ( value ) against ( 'éOçø±Í¾ó ä' ) 0
When I replace the word withåíËâÀ then it
still doesn't match anything. And there is a
row with merely
éOçø±Í¾ó ä spaceåíËâÀ
Tim...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
Re: MBRWithin bug?
Aha! I get it! I *was* being an idiot. The longitude of @g1 is 12*2*, not 121... Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Cluster
Hi. Here are some of my tests on Centos 5.0. http://blog.chinaunix.net/u/29134/article_71956.html On Fri, Nov 21, 2008 at 3:49 AM, Ronan Lucio [EMAIL PROTECTED] wrote: Hi, Does anybody has a tip to install a MySQL Cluster in a Linux CentOS-5? Is it better from source or can it be from yum? I do prefer yum because it's easier for upgrades, but I don't know if the available package was compiled for that. Thank you, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Error on MySQL-5.0
You may execute mysql_fix_privileges_table script to upgrade all of your mysqld. On Thu, Nov 20, 2008 at 7:54 PM, Ronan Lucio [EMAIL PROTECTED] wrote: Hi, I installed MySQL-5.0.67_1. When I execute CHECK TABLE information_schema.COLUMNS FOR UPGRADE I get the message: ++---+--++ | Table | Op| Msg_type | Msg_text | ++---+--++ | information_schema.COLUMNS | check | error| Table upgrade required. Please do REPAIR TABLE `/var/tmp/#sql_43b6_0` to fix it! | ++---+--++ The same occurs for tables ROUTINES, TRIGGERS and VIEWS. If I execute REPAIR TABLE COLUMNS I got: ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema' So I GRANT ALL ON information_schema.* TO 'root'@'localhost'; and got the same error: ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema' So I backed to command shell and mysqlcheck -u root -p --repair information_schema. It gives me no error, but the problem persists: When I execute CHECK TABLE information_schema.COLUMNS FOR UPGRADE I get the message: ++---+--++ | Table | Op| Msg_type | Msg_text | ++---+--++ | information_schema.COLUMNS | check | error| Table upgrade required. Please do REPAIR TABLE `/var/tmp/#sql_43b6_0` to fix it! | ++---+--++ Any help would be appreciate. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Virtualizing MySQL
I had done many instances on one machine before, the most important thing is about the my.cnf. And there are many individual my.cnf, which belonged to their own instance. Since your total memory is 32GB, you can assign them properly. On Fri, Nov 21, 2008 at 3:40 AM, Claudio Nanni [EMAIL PROTECTED]wrote: quote we are going to be setting up a 3 to 4 node MySQL replication cluster (1 master-rw and 2 slaves-ro)...each having 16 to 32 GB of RAM. quote If it is still true what you wrote you need different installations. Of course master and slave on the same host has the only use of an online backup solution, better if using different storage for data partitions, anyway adding not much to high availability. But if your only concern is to test a Master/Slave configuration I would go for multiple instances on same host. If you need a complete description on how to do it contact me. Sorry if I repeat myself, but for reliable test you should have the same architecture for both prod and preprod, Claudio Nanni Shain Miley wrote: Ok...based on the responses that I received so far...it seems like maybe I should be leaning toward a non virtualized solution. What I am wondering now is... 1)would it be better to have one MySQL instance running and have the developers each have their own DB inside that one instance? or 2) would it be better to have each developer have their own MySQL instance on the same machine? or 3) some combination of the above...maybe have the developers split between 2 or 3 MySQL instances on the same machine... Any thoughts? Thanks again, Shain Simon J Mudd wrote: [EMAIL PROTECTED] (Shain Miley) writes: I am looking into the idea of setting up 10 - 15 virtualized instances of MySQL. The reason for this is as follows...we are going to be setting up a 3 to 4 node MySQL replication cluster (1 master-rw and 2 slaves-ro)...each having 16 to 32 GB of RAM. In order for our development team to do their work...they must have access to some Mysql resources that are close to the production environment. I am not currently in a position to provide each developer two MySQL servers (one master and one slave with 16 to 32 GB of RAM) for testing...or obvious reasons...mainly cost ;-) So I have been thinking about how best to provide such resources, at this point I am thinking that I can use OpenVZ to help me out a bit. I was wondering if anyone had any thoughts on this issue...should I just run 10 instances of MySQL on the same server...are there other options? I am concerned with trying to ensure that the metrics, resources, workloads, etc from these development servers has some sort of relevance to our production environment...otherwise we are testing apples and oranges...which the dev team will clearly point out...and in a way I know we are...but I would like to minimize the effects My only concern would be that if you have busy mysql instances that they will interfere with each other. We used to have a couple of busy mysqld processes running on the same Linux server only to find that the performance characteristics were worse than 1/2 of the performance of having each instance on a separate server. Both mysqld instances were busy and so fought each other for I/O and for CPU often at the same time. If this might be an issue for your virtual servers may not be an ideal solution as most of the free virtualisation options don't control sufficiently the hardware resources distributed to each virtual machine. YMMV. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
mysqldump: Got error: 1030
Hello, what do these errors mean: mysqldump: Got error: 1030: Got error 1 from storage engine when using LOCK TABLES mysqldump: Couldn't execute 'show create table `Antrag`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Autor`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Bild`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Galerie`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Link`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Seite`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Termin`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Veroeffentlichung`': Got error 1 from storage engine (1030) Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to determine if temporary table exists
How can I determine if a temporary table exists? Normally I use something like: create temporary table Tablex like Table1; show tables like Tablex; but the Show Tables never displays any rows for a temporary table even though the temporary Tablex exists. (All in same thread). So is there a better way to determine if a temporary table exists? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to determine if temporary table exists
Try drop table if exists Tablex; On Fri, Nov 21, 2008 at 9:53 AM, mos [EMAIL PROTECTED] wrote: How can I determine if a temporary table exists? Normally I use something like: create temporary table Tablex like Table1; show tables like Tablex; but the Show Tables never displays any rows for a temporary table even though the temporary Tablex exists. (All in same thread). So is there a better way to determine if a temporary table exists? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: mysqldump: Got error: 1030
What is your storage engine used? On Fri, Nov 21, 2008 at 8:59 AM, Marten Lehmann [EMAIL PROTECTED] wrote: Hello, what do these errors mean: mysqldump: Got error: 1030: Got error 1 from storage engine when using LOCK TABLES mysqldump: Couldn't execute 'show create table `Antrag`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Autor`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Bild`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Galerie`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Link`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Seite`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Termin`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Veroeffentlichung`': Got error 1 from storage engine (1030) Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: mysqldump: Got error: 1030
What is your storage engine used? On Fri, Nov 21, 2008 at 8:59 AM, Marten Lehmann [EMAIL PROTECTED] wrote: Hello, what do these errors mean: mysqldump: Got error: 1030: Got error 1 from storage engine when using LOCK TABLES mysqldump: Couldn't execute 'show create table `Antrag`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Autor`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Bild`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Galerie`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Link`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Seite`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Termin`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Veroeffentlichung`': Got error 1 from storage engine (1030) Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: MySQL Cluster
Hello Moon's Father, That would be great..if it was in english ;) Hi. Here are some of my tests on Centos 5.0. http://blog.chinaunix.net/u/29134/article_71956.html On Fri, Nov 21, 2008 at 3:49 AM, Ronan Lucio [EMAIL PROTECTED] wrote: Hi, Does anybody has a tip to install a MySQL Cluster in a Linux CentOS-5? Is it better from source or can it be from yum? I do prefer yum because it's easier for upgrades, but I don't know if the available package was compiled for that. Thank you, Ronan -- 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 Cluster
Thanks for advice. There're no environment for me to test the cluster again right now. Hope the chance chooses me, then the english version will be done. :) On Fri, Nov 21, 2008 at 10:48 AM, steve grosz [EMAIL PROTECTED]wrote: Hello Moon's Father, That would be great..if it was in english ;) Hi. Here are some of my tests on Centos 5.0. http://blog.chinaunix.net/u/29134/article_71956.html On Fri, Nov 21, 2008 at 3:49 AM, Ronan Lucio [EMAIL PROTECTED] wrote: Hi, Does anybody has a tip to install a MySQL Cluster in a Linux CentOS-5? Is it better from source or can it be from yum? I do prefer yum because it's easier for upgrades, but I don't know if the available package was compiled for that. Thank you, Ronan -- 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] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: How to determine if temporary table exists
At 08:02 PM 11/20/2008, you wrote: Try drop table if exists Tablex; Ahhh, I don't necessarily want to drop the table if it already exists. :) If the table already exists then I'll add new rows to it (and keep the existing rows). If the table doesn't exist, then I'll create it. I suppose could count the rows in Tablex and it would throw an exception if the table did not exist . But I really didn't want to resort to trapping an exception in my program. I thought there should be an easy way using SQL to determine if a temporary table exists or not. Mike On Fri, Nov 21, 2008 at 9:53 AM, mos mailto:[EMAIL PROTECTED][EMAIL PROTECTED] wrote: How can I determine if a temporary table exists? Normally I use something like: create temporary table Tablex like Table1; show tables like Tablex; but the Show Tables never displays any rows for a temporary table even though the temporary Tablex exists. (All in same thread). So is there a better way to determine if a temporary table exists? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysqlhttp://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cnhttp://yueliangdao0608.cublog.cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to determine if temporary table exists
In the last episode (Nov 20), mos said: At 08:02 PM 11/20/2008, you wrote: Try drop table if exists Tablex; Ahhh, I don't necessarily want to drop the table if it already exists. :) If the table already exists then I'll add new rows to it (and keep the existing rows). If the table doesn't exist, then I'll create it. I suppose could count the rows in Tablex and it would throw an exception if the table did not exist . But I really didn't want to resort to trapping an exception in my program. I thought there should be an easy way using SQL to determine if a temporary table exists or not. Why not CREATE TEMPORARY TABLE IF NOT EXISTS ...? If you really need to know whether the table existed before or not, that command will return a warning if it was there already. http://dev.mysql.com/doc/refman/5.0/en/create-table.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]
