My Experience on DRBD for MySQL
Good day, I just want to share my experience in deploying DRBD for MySQL, I'm blogging it at http://blog.irwan.name/?p=118 I welcome any comments and critics, thanks. -- Regards, Mohd Irwan Jamaluddin Web: http://www.irwan.name/ Blog: http://blog.irwan.name/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: My Experience on DRBD for MySQL
Здравствуйте, Mohd. Вы писали 7 июня 2007 г., 12:46:32: Good day, I just want to share my experience in deploying DRBD for MySQL, I'm blogging it at http://blog.irwan.name/?p=118 I welcome any comments and critics, thanks. -- Regards, Mohd Irwan Jamaluddin Web: http://www.irwan.name/ Blog: http://blog.irwan.name/ Any perfomance tests? -- С уважением, Vitaliy mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Magazine - Issue 1 available NOW!!!!
On Wed, Jun 06, 2007 at 05:56:44PM -0700, Peter Rosenthal wrote: On 04/06/07, Jon Ribbens [EMAIL PROTECTED] wrote: Um, what? Both that and the methods described in the magazine are completely wrong. You use mysql_real_ecape_string(), that's it. I would disagree on the use of mysql_real_escape_string(). The use of placeholders is much safer from a maintenance and 'oops look I typoed it' perspective. That's not a disagreement - you're just talking about a different abstraction layer. Behind the scenes your 'placeholder' API will be using mysql_real_escape_string(). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: recurring table corruption problems (MyISAM)
I recompiled my sql with nocona instead of k8 and the problem persists. Now, I found this: http://bugs.mysql.com/bug.php?id=17329 which definitely sounds similar to my problems. I'm gonna give a shot to disabling delayed_key_write. On 6/6/07, Quentin Gouedard [EMAIL PROTECTED] wrote: Hello, So I have this somewhat busy mysql box (300qps - 1100 qps) holding a bunch (20) of myISAM tables. The Box is Core2Duo 2.4 / 4GB RAM / Gentoo64 / MySQL 5.0.38 There are a number of scripts accessing those tables, both located on the same machine and on others. Every day, 1 to 3 tables get corrupted. Some of these tables are accessed from the other servers, but some tables get corrupted which are only accessed locally too. Most of the time I do a REPAIR, I lose some rows in the battle. Imagine what the result is when some of those tables are like customers or contain other vital information. Sometimes, REPAIR returns that the table status is OK, when CHECK returns that the table needs to be repaired. In such cases, i need to shutdown the server, myisamchk -r the table, and restart mysql. Sometimes even that doesn't change the CHECK output, and i need somehow to re-run a REPAIR to see the table finally fixed. MYSQL docs state for possible reasons for corruption: [quote] 1- The mysqld process being killed in the middle of a write. 2- Unexpected shutdown of the computer (for example, if the computer is turned off). 3- A hardware error such as faulty memory/cpu/motherboard and or hard drive. 4- You are using an external program (like myisamchk) on a live table. 5- A software bug in the MySQL or MyISAM code[/quote] 1 - tables get corrupted without killed processes 2 - server never gets unexpectedly shutdown 3 - my hosts' monitoring system says ahrdware is fine (although i don't know what that means). 4 - never do that without shutting mysql down first 5 - I'm using a somewhat recent version so if it was this it'd be really curious. The software I use is a popular one for which no such problems have been reported before, so I doubt it's query/PHP related. The fact it occurs specifically on one out of 7 machines I have running similar configurations makes me think about an environmental problem rather than an application-layer one. Some more info if that may help : Typical errors showing up (error codes vary as you can see): 070502 3:27:45 [ERROR] /usr/sbin/mysqld: Table 'my_table' is marked as crashed and should be repaired 070502 4:23:30 [ERROR] Got error 127 when reading table './mysite/my_table' 070502 17:55:58 [ERROR] /usr/sbin/mysqld: Incorrect key file for table './mysite/my_table2.MYI'; try to repair it 070521 11:48:48 [ERROR] /usr/sbin/mysqld: Incorrect key file for table './mysite/my_table2.MYI'; try to repair it 070521 11:48:53 [ERROR] Got error 134 when reading table './mysite/my_table2' 070521 22:54:41 [ERROR] /usr/sbin/mysqld: Incorrect key file for table './mysite/my_table2.MYI'; try to repair it 070521 22:54:41 [ERROR] Got error 126 when reading table './mysite/my_table2' Another weird thing is that the errors do not always show up in the mysqld.err log file. Sometimes I get on-screen errors mentionning crashed tables, which are not reported in the log file, making me wonder how much more often it occurs. Would mysqladmin variables help helping me ? And now on to the questions : 1) Are there any known problems to running mysql 5 on gentoo 64 bits ? 2) Could delay_key_write ON be the reason for this corruption, or setting it off minimise the chances of it happenning ? 3) How can I debug / add logs to give me a clue about the corruption origin ? What would you do to find out the problem / solve it ? 4) Are there settings that minimize / maximize table corruption risks ? 5) I realized today that the march option in my /etc/make.conf file is set to k8 (AMD64 processors), when I use Core2Duo (for which recommended march is nocona). Could having compiled mysql with this parameter be the reason for the table corruptions ? Thanks !
How to parition a table with a VARCHAR Primary Key?
Hi,
I'm struggling with getting my tables partitioned. All the tables I want to
partition have a single primary key id which is a varchar. They also have a
created datetime field to let me know when each record was added to the db.
Ex:
CREATE TABLE `document` (
`document_id` varchar(50) NOT NULL DEFAULT '',
`filename` varchar(255) NOT NULL DEFAULT '',
`document` longblob,
`created` datetime DEFAULT NULL,
`last_modified` datetime DEFAULT NULL,
`size` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`document_id`),
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Ideally, I want to partition the table on a per month basis. Originally I
though I could do the following:
partition by range( Year( created) * 1000 + month(created) )(
partition 2004_01 values less than (200401),
partition 2004_02 values less than (200402) );
but that gave me an error 1491: A PRIMARY KEY must include all columns in
the table's partitioning function. Incidentally, I couldn't find any
reference to this in the documentation anywhere, that the partitioning field
must belong to the PK. In fact, the examples in the docs don't even use a
PK.
So that didn't work. And adding the created field to my PK may cause too
many problems that I don't want to deal with at the moment. So instead, I
looked at my id field. Lucikly, the first 16 characters are hex
representation of the timestamp. So a little quick script generated ranges
for me to parition against that as well.
partition by range( id )
(
partition 2002_01 values less than ('00ebc0102c7f'),
partition 2002_02 values less than ('00ec50423c7f'),
partition 2002_03 values less than ('00ece0744c7f')
);
However, that causes me an error as well:
Error Code : 1064
VALUES value must be of same type as partition function near '),
partition 2002_02 values less than
('00ec50423c7f'),' at line 7
Can anyone point me in the right direction please? Is it impossible to
partition a table this way? I've read (and reread) the docs multiple times,
but can't seem to find anything to help me out.
I'm running version 5.1.19-beta-community-nt-debug.
Thanks!
Eric
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql 5.0.* client connection to Mysql 4.0.* Database
Is it possible to connect to a 4.0.18 DB from the client of a 5.0.27 system? I know there is an old_passwords function. Can this be used somehow from a DOS command prompt? David
Re: Mysql 5.0.* client connection to Mysql 4.0.* Database
Have youtried this yet? While 4.0 client can't connect to a v.5 server unless the passwords have explicitly beewen stored as old_password, I have never run into an issue connecting a 5.x client to a 4.0 server. - michael On 6/7/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Is it possible to connect to a 4.0.18 DB from the client of a 5.0.27 system? I know there is an old_passwords function. Can this be used somehow from a DOS command prompt? David -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql 5.0.* client connection to Mysql 4.0.* Database
Yes, I did try. The issue was the security level setting on the new DB server, and not the password stuff. I've upgraded apache, mysql, and php in the last few weeks, and 3-4 issues became painfully apparent when going from php4 to php5, so I guess I was operating under similar assumptions and frustrations. My apologies to all. I'll try to keep from functioning in iamadummy mode. David ** Have you tried this yet? While 4.0 client can't connect to a v.5 server unless the passwords have explicitly been stored as old_password, I have never run into an issue connecting a 5.x client to a 4.0 server. - michael On 6/7/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Is it possible to connect to a 4.0.18 DB from the client of a 5.0.27 system? I know there is an old_passwords function. Can this be used somehow from a DOS command prompt? David -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful.
Rewriting PostgreSQL in another language - from C to SmartEiffel
Rewriting PostgreSQL in another language - from C to SmartEiffel PostgreSQL is very stable, mature and fully grown up and is in spectacular shape. But what will the developers do next ? Will they run out of work? No!! We can give them more work to do. We can keep them very busy for many more years!! Planning PostgreSQL development for the coming next 20 years: Rewrite the PostgreSQL in SmartEiffel. C is a very fast running language but developing in C and maintaining is a heck of work. How about SmartEiffel? SmartEiffel outputs C code which can be compiled. And SmartEiffel compiler is getting better everyday - it would be running as fast as C. Also SmartEiffel can output in Java classes. A very smart and intelligent compiler like SmartEiffel is like a code generator which can output C or 'java classes'. Because it outputs C, SmartEiffel programs run really fast. SmartEiffel http://smarteiffel.loria.fr/ http://en.wikipedia.org/wiki/SmartEiffel http://ese.sourceforge.net/wiki/index.php/Main_Page What is the diff between SmartEiffel and Java? Simple - SmartEiffel programs run much faster than Java. SmartEiffel is a 'Super power of computer langugages'. See http://www.geocities.com/alavoor for more details on benchmarks of computer languages. Expecting? Get great news right away with email Auto-Check. Try the Yahoo! Mail Beta. http://advision.webevents.yahoo.com/mailbeta/newmail_tools.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
String Locks
I saw mention of something called string locks in an earlier thread, yet I see nothing like it in the mysql documentation. Could somebody please point me at a reference? -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQL data replication
I have limited experience with MYSQL replication; which is why I am hoping others with more experience can answer a question or two. Let's say I have a MASTER MYSQL database. Let's say there are 50 - 60 other systems where I'd like to have MYSQL running on these slave systems. These slave systems need to be kept in synch with the Master, but it does NOT need to be anywhere near real time. The data in the slaves could lag as much a an hour or two. The amount of data in total in the MASTER is in the range of 100MB - 250MB The rate of changes to the data is in the range 2000 - 5000 DML per 24 hour day. We control the application so we can/will include date/time each record is created or modified. You can assume that no records ever get physically deleted; only INSERT UPDATE (no DELETE). What are some alternative ways to keep the slave systems current? TIA HAND!
Advanced Indexing
Is there a way to only include certain matching conditions in indexes? Example if I have a row I want to index that is mysql dates (2007-06-07) and I only want to include CURRENT and FUTURE dates in the index and ignore any past dates. Is that possible at all? The issue I have is that the ratio of queries on old vs. new data is like 1:10. And searches would be MUCH faster if I could force my queries that are looking at current or future data to use an index that ONLY had that information in them.. Thanks in advance for insight here. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
When will partitioning be included in the MySQl stable release ?
Dear All, I would like to know if there are short time plans (and eventually when approximately it will be) to include partitioning in the next MySQL stable release. Thanks in advance for your help. I look forward to hearing from you, Mariella - You snooze, you lose. Get messages ASAP with AutoCheck in the all-new Yahoo! Mail Beta.
