Re: InnoDB and rsync
2011/1/25 Robinson, Eric eric.robin...@psmnv.com: your whole solution is crippled because why in the world are you killing your salves and reinit them without any reason daily? There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. ... sounds interesting; have you got any document explaining this phenomenon? AFAIK, the things that (silently) break replication are: - non-deterministic functions in statement-based replication - hand-made updates on the slave db is this enough to justify a *daily* resync?! However, this could be a solution for your problem (maybe) http://www.pythian.com/news/5113/video-building-a-mysql-slave-and-keeping-it-in-sync/ if you watch the movie, at ~40 minutes, you can see a slide What causes slave to get out of sync... Greetings, Mattia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Error 1064
Hi Brad, 2010/6/8 Brad Scott outlawsco...@hotmail.com: [...] Any line beginning with just a number (ie 9, 10, 16) causes a failure. What am I missing? you should add backticks ( ` ) at both ends of the column name, e.g. `7c` varchar(255) NOT NULL default '', I think your export should have been done with the option --quote-names enabled. Hope this helps, unfortunately I have no mysql instances available to test at the moment... Greetings, Mattia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Strange behavior by MySQL Stored Procedure
2010/5/28 Manasi Save manasi.s...@artificialmachines.com: [...] Or am I doing something wrong? probably; you better send us another e-mail writing at least: - mysql version you are using - mysql Connector/J version you are using - piece of java code you are using to call the stored procedure - source of the stored procedure (or part of it) ... probably, a subset of all of these infos will not be enough to understand the problem. In any case, if you have troubles using the mysql jdbc driver but no problem using the mysql CLI and you suspect a Connector/J bug, maybe you better write to the mysql java support mailing list: http://lists.mysql.com/java Greetings, Mattia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication : request DELETE is not executed on slave
AFAIR you can use LIMIT with replication only if you use row-based replication (or mixed), that means that you must use mysql 5.1. Greetings, Mattia. 2010/4/28 Tom Worster f...@thefsb.org: 16.3.1.9. Replication and LIMIT Replication of LIMIT clauses in DELETE, UPDATE, and INSERT ... SELECT statements is not guaranteed, since the order of the rows affected is not defined. Such statements can be replicated correctly only if they also contain an ORDER BY clause. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to modify the application to implement the separation of write/read
2010/3/11 Peter Chen peter.c...@aicent.com: [...] Does someone have met this problem? How to implement the separation of read and write? please, write us some more details about your application! For example, if you use java with the Connector/j jdbc driver, you can enjoy a really nice feature already implemented in the driver: http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-replication-connection.html Greetings, Mattia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to modify the application to implement the separation of write/read
2010/3/11 Peter Chen peter.c...@aicent.com: [...] I am not sure whether do I need to modify something else? Like my application code. as stated on the webpage you just posted here: --- An application signals that it wants a transaction to be read-only by calling Connection.setReadOnly(true), this replication-aware connection will use one of the slave connections, which are load-balanced per-vm using a round-robin scheme [...] --- So, I think you just need to set the readonly flag to true on the connection objects via the setReadOnly() method. I can't tell you how to handle this using the hibernate stuffs, you should probably: - post this question on an hibernate-related mailing list - post this question on the Connector/J mailing list (there should be one, I think! :) - wait until someone else answers to this mail - try it by yourself (my suggestion). Finally, if you look at the user comments on the very same page, you will see an entry explicitly dedicated to Hibernate, suggesting a way to configure the persistence.xml file. Regarding the automatic failover, I suggest you to investigate the autoReconnect and failOverReadOnly flags of the Connector/J: http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html Greetings, and have a nice coding! Mattia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: last_insert_id
2009/12/27 Victor Subervi victorsube...@gmail.com: mysql select * from products; [...] mysql select last_insert_id() from products; [...] Now, I was expecting 1, not 0! What up? [...] LAST_INSERT_ID() (no arguments) returns the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement. [...] If no rows were (successfully) inserted, LAST_INSERT_ID() returns 0. http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id http://dev.mysql.com/doc/refman/5.1/en/getting-unique-id.html Greetings, Mattia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Duplicate Entry, But Table Empty!
2009/12/13 Victor Subervi victorsube...@gmail.com: [...] Please advise. review your sql: you are inserting into tem126072414516 and selecting from tem126072385457 ( Asterisk in Pinter Tibor's mail means bold ) Greetings, Mattia Merzi. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: contributing to mysql ..
here you are: http://forge.mysql.com/wiki/Contributing Greetings, Mattia Merzi. 2009/5/27 mugisha moses mossp...@gmail.com: hi all am new to opensource. iam a finalist student doing computer science in East Africa. i would like to start contributing to the mysql project. can u guys give me some getting started tips or hacks... anything that can help me dive in straight.. thanx Mugisha Moses -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trigger working with server 5.0.51 but not 5.0.22
2009/5/13 Mauricio Tellez mauricio.tel...@gmail.com: Hi, I'm developed a trigger with mysql version 5.0.51(ubuntu), and when I tried to move this to a production server (version 5.0.22 fedora) I ran into [...] but mysql say there is an error near EXISTS cfe_tg_calculas_consumos. Of http://dev.mysql.com/doc/refman/5.0/en/drop-trigger.html The IF EXISTS clause was added in MySQL 5.0.32. Greetings, mattia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: avoiding use of Nulls (was: The = operator)
2009/3/14 mich...@j3ksolutions.com: [...] So instead of thinking that I am an idiot, try using your intelligence and try to understand what I am really talking about! ... instead, try to point us to a chapter of a book or take your time and write a short paper, 'cause my (stupid) intelligence suggests me that (maybe) you are the one that is not able to express his ideas *clearly* Greetings, Mattia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: avoiding use of Nulls (was: The = operator)
2009/3/13 mich...@j3ksolutions.com: I have a database logging weather data. When a station does not report a temperature, it is set to NULL. It would be a very bad idea to set it to 0 as this would ruin the whole statistics. NULL is a perfectly valid information in many cases. I'd use -9., I'd never allow a i don't know what it is value like Null in my database. what about a sensor that reports -9. when it is broken or the temperature is out of range for it? How could you express this information in this case? Just changing your fake-NULL value to -8. would be something *horrible*. I perfectly agree with Thomas, and I would expand his idea: - sensor working: integer value between -250~+32767 - sensor now working: integer value less than -250 - sensor unavailable: NULL I think that the advantages of this kind of architecture are easy to understand. More generally, NULL means (in this case): I cannot write a *value* because I have no informations, instead, -9. is a *value*! In a previous e-mail on this thread you wrote: NULL is garbage or something like this, but think: -9. is garbage. NULL is something we all agree, in general, it means I don't know. Going back to the fax-number example, you wrote (if I correctly remember) that you would use some blanks, instead ... well, I would use a single zero character, and another could use some other value it would be a disaster! How could you avoid this? Greetings, Mattia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB deadlocks
Hi there, well, thanks for the hints regarding transaction-serialization performance but, if you read my very first e-mail, I didn't mention any kind of performance trouble, I just sometimes (once a *month*) have to re-issue some db commands because of these deadlocks, but 99.9% of the time I have free cpu, free memory and free disk IO resources so, fortunately, performace is not a problem, just some deadlocks, and I just want to be sure that everything works as it should, even in that 0.1% of the time that the database is heavily used. Additionally, the database that causes me this kind of troubles is a database dedicated to batch jobs, so in any case nobody will be angry if the jobs finish couple of minutes later than usual ...:) It's something like: 23.5 hours a day data is loaded, 0.5 hours a day some clients run in parallel and call some stored procedures that can run in a serial fashion without causing any kind of trouble. Thank you anyway, any e-mail is really appreciated, even if performance is not a problem for me... and I hope that this sentence will not make this e-mail be considered as spam :D Greetings, and thanks again, Jerry! Thanks even to Paul for the really-tiny-but-really-appreciated answers to my questions! Mattia Merzi. 2009/3/10 Jerry Schwartz jschwa...@the-infoshop.com: [...] [JS] There is no free lunch, but sometimes you get a free appetizer. Within limits, you will get better throughput if you have multiple transactions running in parallel rather than running them serially. The problem is to determine those limits. If you have the luxury, you run stress tests and examine the queue lengths for the various bits: disk, memory, cpu, network. That will give you some idea of what your system can tolerate, as well as telling you where to put your money. In reality, most of us run stress tests during production. :( Of the various resources, memory is the one with the sharpest knee in the curve because either you have enough or you don't. If you have enough memory, then more will not help. Remember, there is always exactly one bottleneck in a system at any given moment. By the way, if transactions are constantly presented to a resource faster than the resource can service, the queue length will grow to infinity. That will cause performance problems. ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
InnoDB deadlocks
Hi everyone, I've got some problems with deadlocks on InnoDB tables. On paragraph 13.6.8.10. How to Cope with Deadlocks of the mysql 5.1 version, the last sentence states: -- Another way to serialize transactions is to create an auxiliary “semaphore” table that contains just a single row. Have each transaction update that row before accessing other tables. In that way, all transactions happen in a serial fashion. Note that the InnoDB instant deadlock detection algorithm also works in this case, because the serializing lock is a row-level lock. With MySQL table-level locks, the timeout method must be used to resolve deadlocks. -- Just two very simple questions: - using this method, transactions will be serialized so the deadlock problem will never come up again? This seems clear reading that sentence, the only thing that makes me humble is the statement: Note that the InnoDB instant deadlock detection algorithm also works in this case ... can someone briefly explain me this concept? - if I create a semaphore table and I start any deadlock-prone transaction issuing a lock table write on that table and an unlock tables immediately after the commit, will the effect be the same? 'Cause the last sentence of the manual says: With MySQL table-level locks, the timeout method must be used to resolve deadlocks will this mean that if I use this LOCK TABLE method I can get timeouts instead of deadlocks on the very same transactions? Thank you very much, Greetings, Mattia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Record IDs
2009/2/23 Hagen Finley finha...@comcast.net: I have a number of duplicate records in my table which are identical hence, I can't do a delete on the columns without deleting both records. One deletion strategy I have considered is identifying the records by their table record id - but I don't know for certain that such an identifier exists or how I would show it via a select statement. Any suggestions? DELETE FROM yourtable WHERE foo=bar LIMIT 1 should do the job. Greetings, Mattia Merzi. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org