|Previous 30 Newer Entries||Showing entries 31 to 60 of 60|
I discovered a feature (or bug) of MySQL timestamp fields. Maybe is documented in some place what I not read yet:
When I add a new timestamp field to a table, MySQL magically adds some features to new timestamp field like a “trigger” and a default value to CURRENT_TIMESTAMP.
There is test-case script:
-- CREATING TABLE AND INSERT SOME DUMMY DATA mysql> CREATE TABLE t( -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -> val VARCHAR(50) -> ); Query OK, 0 ROWS affected (0.15 sec) mysql> INSERT INTO t (val) VALUES ("foo") ,("var"); Query OK, 2 ROWS affected (0.08 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t; +----+------+ | id | val | +----+------+ | 1 | foo | | 2 | var | +----+------+ 2[Read more...]
A couple weeks ago I submitted Bug #67315: Crashing server by stored function referencing user defined variable in query. If you press that link, you can't see the bug (though I can as I submitted it).
This is due to Oracle's policy for security-related bugs. Tomas Ulin, Vice President MySQL Development at Oracle , was kind enough to discuss Oracle's policy with me, and these are the key points as I understand them:
Oracle's basic approach is to protect its customers. By publicizing security-bugs, Oracle's customers are vulnerable to black hatters attacks. Therefore Oracle takes measures and privatizes security bugs (crashing bugs can be treated as security bugs since a crash is a form of Denial of Service).
But what of a bug reported in a RC version, as was in my case? There is no strict policy[Read more...]
I pledged, in my first post last month, that SkySQL will do its part to promote the best of MySQL and its community. Given the recent discovery that Oracle is no longer publishing test cases for bug fixes, and the dialogue surrounding it, it feels like the right time to share my thoughts on what this means to the open source collective, and what we can do – and are doing – about it.
There has been a lot of talk about MySQL 5.6 replication improvements. With few exceptions, what I have seen was either marketing messages or hearsay. This means that few people have really tried out the new features to see whether they meet the users needs.
As usual, I did try the new version in my environment. I like to form my own opinion based on experiments, and so I have been trying out these features since they have appeared in early milestones.
What follows is a list of (potentially) surprising results that you may get when using MySQL 5.6.
All the examples are made using MySQL 5.6.6.
I have already mentioned that MySQL 5.6 is too verbose when creating data directory. This also means that your error[Read more...]
This is the third time MySQL has made an entry into the Oracle Critical Patch Update Advisory service. The first time, we at Team MariaDB came up with an analysis: Oracle’s 27 MySQL security fixes and MariaDB.
Security is important to a DBA. Having vague explanations does no one any good. Even Oracle ACE Director Ronald Bradford chooses to ask some tough questions on this issue. Recently we found a bug in MySQL & MariaDB and did some[Read more...]
There are mistakes that drive you crazy when you try to understand what went wrong.
One of the most annoying and hard to catch was this, apparently harmless line:
tungsten-sandbox -m 5.5.24 --topology all-masters -n 2 -p 7300 -l 12300 -r 10300 –t $HOME/mm -d tsb-mm
The person reporting the error told me that the installation directory (indicated by "-t") was not taken into account.
I usually debug by examples, so I copied the line, and pasted it into one of my servers. Sure enough, the application did not take trat option into account. The installation kept happening in the default directory.
I knew that I had done a good job at making the application configurable, but I checked the code nonetheless. The only place where the default directory is mentioned is when the related variable is initialized. Throughout the code, there are no[Read more...]
Do you rely on pt-diskstats from Percona Toolkit instead of the standard iostat a lot? There appears to be a nasty bug in pt-diskstats 2.1, which makes it produce bad results.
I noticed some of the numbers I was getting weren’t right, so I tried running iostat and two different releases of pt-diskstat side by side. Here’s what I got:
I can understand the slight differences between the lines in iostat and pt-diskstats 2.0 as they probably weren’t reading /proc/diskstats contents in the same moments, so the values they were seeing could be a bit different. However both lines practically show the same thing.
On the other hand, the line based on the pt-diskstats[Read more...]
I just finished a database modification, a new foreign key creation shouldn’t be take more than 5 mins, but I spent 2 hours because MySQL still have some useless error messages.
There is a way to create a new foreign key:
-- Create two tables foo and bar CREATE TABLE foo ( id INTEGER NOT NULL PRIMARY KEY, bar_id INT NOT NULL -- foreign key ); CREATE TABLE bar ( id INTEGER NOT NULL PRIMARY KEY ); -- Try to create a foreign key on `foo` ALTER TABLE foo ADD FOREIGN KEY(bar_id) REFERENCES bar(SOME_FIELD) ;
The last sentence returns a generic error message:
Error Code: 1005. Can't create table 'temp.#sql-4bd7_11' (errno: 150)
Everything would have been easier if I had noticed that wrong field name
bar(SOME_FIELD), sometimes happens, but if MySQL would have shown
Have you ever seen BIG weird numbers in mk-query-digest report that just seem wrong? I have! Here’s one report I got today:
... # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 5088s 1us 171s 2ms 467us 104ms 28us # Lock time 76s 0 3s 26us 69us 3ms 0 # Rows sent 9.80M 0 1.05M 3.50 0.99 642.32 0 # Rows examine 5.59G 0 82.56M 2.00k 0.99 97.41k 0 # Rows affecte 457.30k 0 2.62k 0.16 0.99 1.68 0 # Rows read 2.16G 0 82.56M 788.53 21.45 82.91k 0.99 # Bytes sent 2.14T 0 4.00G 781.27k 3.52k 47.84M 84.10 #[Read more...]
Working with customer, I faced pretty nasty bug, which is actually not rare situation , but in this particular there are some lessons I would like to share.
Everything below is related to InnoDB-plugin/XtraDB, but not to regular InnoDB ( i.e in MySQL 5.0)
In short, if you use big BLOBS ( TEXT, MEDIUMBLOB, etc) (that allocated in external segment in InnoDB), you can get your database in trash state just executing update on row with blob and rolling back transaction twice ( on the same row)
The keywords that diagnose you hit this bug is
InnoDB: Serious error! InnoDB is trying to free page N
InnoDB: though it is already marked as free in the
Yesterday, I reached a happy milestone in HailDB development. All compiler warnings left in the api/ directory (the public interface to the database engine) are now either probable/possible bugs (that we need to look at closely) or are warnings due to unfinished code (that we should finish).
There’s still a bunch of compiler warnings that we’ve inherited (HailDB compiles with lots of warnings enabled) that we have to get through, but a lot will wait until after we update the core to be based on InnoDB 1.1.
Recently came across this bug when trying out the federated storage engine for the first time in MySQL 5.1. Had a security table with user information on a remote server & database that needed to be joined to a local table housing site-specific permissions but only containing user IDs. I definitely wanted to use the “create server” method for the new table in case we later decided to link to a different table in the same remote database. A terrific little feature of the MySQL federated storage engine, to be sure.
The local server was the master in a replication pair. After executing the create server statement on the master, I proceeded to create the new federated table pointed to the new remote server. That’s when my mysql replication
OUTER JOIN queries in SQL are susceptible to two very subtle bugs that I’ve observed a number of times in the real world. Daniel and I have been hammering out ways to automatically detect queries that suffer from these bugs, in a relatively new Maatkit tool called mk-query-advisor. It’s part of our series of advisor tools for MySQL. I wrote a blog post about it a while ago. Automated analysis of bad query patterns is a good thing to write tools to do, because catching buggy queries is hard work if you do it manually.
Let’s dive right in and analyze these subtle bugs. Warning: if you don’t understand how SQL handles NULL, you’re not going to understand the following. Many[Read more...]
The problem I am going to describe is likely to be around since the very beginning of MySQL, however unless you carefully analyse and profile your queries, it might easily go unnoticed. I used it as one of the examples in our talk given at phpDay.it conference last week to demonstrate some pitfalls one may hit when designing schemas and queries, but then I thought it could be a good idea to publish this on the blog as well.
To demonstrate the issue let’s use a typical example – a sales query. Our data is a tiny store directory consisting of three very simple tables:PLAIN TEXT SQL:
Upgrading from MySQL 5.0 to MySQL 5.1 or Percona Server 5.1 you may run into issues with mysql_upgrade - it will identify some tables to be upgraded and will attempt to run REPAIR TABLE for them. This will fail with "The storage engine for the table doesn't support repair" error message. This seems to confuse a lot of people and I've seen people doing failsafe upgrade path of dumping and reloading complete database confused by this error message, which of course works, but can take quite a lot of time.
Another solution is to simply run ALTER TABLE tbl ENGINE=INNODB which will rebuild table with new MySQL version and normally will fix issues identified by mysql_upgrade.
You can use mysqlcheck -A --check-upgrade to identify tables which need to be fixed such a way.
With Oracle intentions to[Read more...]
Our Universe is full of ironies. But some ironies are just too hard to take.
As you may have guessed (!!!), I am an avid Linux developer and user. Though once upon a time I did develop under Windows. Yes, believe it. And on one particular case, I got to be on a first-name basis with some of the Microsoft Software Engineers to resolve issues we were having with their OLE crap — what the Holy Gods of Microsoft decided to redub as “Active-X”.
But I digress. For the past 10 years, I have been solid Linux and have defenestrated Windows for the most part. But as you know, you can never really completely eliminate Windows. Despite your best efforts, it will always be (for now, at least) the 500 pound gorilla in any room you care to be in. The installed software base there is just staggering, and most have no Linux options.
But then that’s why projects like Wine and the[Read more...]
TO_DAYS(). And depending on how you have partitioned your table, your queries are as fast as you expect them to be. However, there are cases where your query takes twice as long as it should, and of course this will not make you [Read more...]
Let me premise this post with the statement I think the MySQL documentation is an excellent and highly accurate resource. I think the MySQL docs team do a great job, however like software and people, documentation is not perfect.
As members of the MySQL community you can always contribute to improve the process by reading the documentation and logging any issues as Documentation Bugs.
Some time ago in a discussion with a friend and colleague, we were talking about changes in historical defaults that had been improved finally in MySQL 5.4 The specific discussion was on the new default innodb_buffer_pool_size and we both agreed it increased significantly. One said 1GB, the other said 128MB. Who was right? Well we both were, and we were both inaccurate depending on versions.
Referencing the 5.4 Manual in[Read more...]
I caught some of the keynotes and discussion at the Linux Foundation Collaboration Summit today, and was particularly interested in the panel discussion on open source and cloud computing. While we are used to hearing and talking about how important open source software is to cloud computing (open source giving to cloud computing), moderator John Mark Walker posed the question of whether cloud computing gives back? The discussion also rightfully focused on openness in cloud computing, how open source might or might not translate to cloud openness and the importance of data to be open as well.
The discussion also centered on some issues regarding open standards and how open is open[Read more...]
As I wrote about 2 years ago the feature of Innodb to store copy of master's position in Slave's Innodb tablespace got broken. There is a lot of discussions at the corresponding bug report while outcome of the fix remained uncertain for me (the bug is market duplicate while the bugs it seems to be duplicate for describe different issues).
Anyway. The customer came to me today having the following message in the error log after Slave crash while running MySQL 5.1.41 (extra virgin version without XtraDB or Percona Patches)
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 0 10000000, file name mysql-bin.005000
InnoDB: Last MySQL binlog
If you are one of the few unfortunate blokes that’s pulling your hair out over random File Menu popups on OpenOffice — Word or Spreadsheet — and have been hitting your head against a brick wall trying to find the solution, I hear you.
I intially thought there was some interaction with Skype, but now I have to retract that statement. In all honesty, I have no clue what the problem is.
But I decided to just watch the “random” File Menu problem and time it. And now I am more confused than ever.
On my computer running 64-bit Ubuntu 9.10 (Karmic), I noticed that the File Menu toggles twice a second when OpenOffice has the focus. One toggle event happens precisely on the 11th second; the other toggle event happens around 35th to the 45th second. The second toggle seems to be related to when I launch OpenOffice; the first is always precisely on the 11th second.[Read more...]
There was a discussion on LinkedIn one month ago that caught my eye:
Anyone out there created a zipcode database and created a "search within x numer of miles" function ?
Thankful for any tips you can throw my way..
A few people commented that some solutions wouldn't scale. To understand why these sorts of geographic search queries are problematic in MySQL, it's best to show some execution plans on dummy data:[Read more...]
While I worked on several web projects I collected a few wishes for MySQL (this collection is more user specific - a MySQL internal wishlist will come later) - which I use in most cases. I still fixed a few things, but don't know if they are good enough for production (because I just started hacking MySQL).
|Previous 30 Newer Entries||Showing entries 31 to 60 of 60|