Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
10 Newer Entries Showing entries 91 to 100 of 104 4 Older Entries

Displaying posts with tag: Geek (reset)

MySQL Online Manual Search
+0 Vote Up -0Vote Down

Previously I wrote about the manual search and how it had been fixed. Now I think it’s time for polish. Most of the basic keyword searches work but not all of them. The syntax keyword searches should always be spot on.

mysql.com/select syntax

Works great but the less common syntax searches fail

mysql.com/grant syntax

There really is no excuse for the syntax searches failing. These are the sections that people need the most on quick reference. I noticed tha recently the keyword searches that do work put me …

  [Read more...]
The lost art of the join.
+0 Vote Up -0Vote Down

One of the things I always like about growing up with MySQL is that I had to learn joins. There was no sub select to fall back on if I couldn’t get a join to work correctly. With MySQL 4.1 the norm and 5.0 quickly on it’s heels I see more and more problems that people are solving with sub selects that could as easily be solved with joins if they knew how to write them. In my experience joins are faster and easier to optimize than sub selects but I seem to be in the minority now with that idea.

Don’t worry join syntax. I won’t forget you!

Pager
+0 Vote Up -0Vote Down

No, not the kind that wakes you up a 2am. The kind that lets you easily slice and present data from the mysql client in a more managable form. This little known feature of the mysql client is one of my favoriate and has helped me slice through large process lists and page through result sets for years.

Basically the pager command allows you to attach commands to the output of the mysql client to manipulate the output just like pipes in bash. Do you hate wading through all those sleeping connections to find the important ones? Yeah me too:

Normal Processlist:

mysql> show processlist;



  [Read more...]
Slow Query Log
+0 Vote Up -0Vote Down

The slow query log can be a great tool for diagnosing and fixing slow queries. It can also slow the server to a crawl if you are doing bulk inserting. MySQL isn’t afraid to write any query to the slow query log. Even ones that are hundreds of megs. If you are using the multiple-row insert syntax and a query takes longer than long_query_time when it completes mysql will write the entire query out to the slow query log effectively doubling the ammount of data written to the disk.

Fun With Triggers
+0 Vote Up -0Vote Down

I was trying to come up something funny|cute that used new features in 5.0 and settled on a quick little bit with a coffee pot and mug:

create table pot (coffee int unsigned);
create table mug (coffee int unsigned);

delimiter |

create trigger pour_cup after delete on pot
        for each row begin
                insert into mug (coffee) values (1);
        end
|

delimiter ;

Try it out:

mysql> insert into pot values (1), (1), (1), (1);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from pot;
+--------+
| coffee |
+--------+
|      1 |
|      1 |
|      1 |
|      1 |
+--------+
4 rows in set (0.00 …
  [Read more...]
MySQL, Innodb, and Oracle ? Looking through the FUD
+0 Vote Up -0Vote Down

There has been a lot of buzz going around lately about Oracle’s recent aquizition of Innobase Oy the company that produces the innodb storage engine for MySQL. Most of this buzz is centered around MySQL being doomed or at least being set back to the years before it was considered enterprise quality. None of this is true.

First a bit of background. MySQL is unique (as far as I know) in the relational database industry with the concept of storage engines. This gives users the choice of many different features for storage making MySQL the most flexible database in the world (opinion of course, rebut if you dare ) innodb is only one …

  [Read more...]
Bash Brace Expansion.
+0 Vote Up -0Vote Down

Most people who have had someone look over their shoulder while typing at a prompt have been hit with the question, “How did you do that?” Followed by a quick example of whatever nifty command they just executed. Of all the little bash tricks I know the one that I have explained to more people than any other is brace expansion. It’s a must have in the trick box of any bash user. Consider the following:
firewire@cartman:~> cp /etc/my.cnf /etc/my.cnf.bak
firewire@cartman:~> vim my.cnf
*edit edit edit*

Sometimes, before I knew about brace expanstion I would type my.cfn.bak then get slowed down by having to look for the right file. Other times …




  [Read more...]
Better Play It Safe
+0 Vote Up -0Vote Down

Copy and paste is normally a great idea. It saves time. Who wants to retype things over and over again? Not me. There are times when copy and paste can get you in trouble. This has bit me a few times in the past as well as other people. When this happens it usually causes and “oh shit! CTRL+c” to kill the query. The query gets cancelled on the master but still propagates to the slave. The master will happily continue executing while replication silently stays stopped on the slave.

The solution? Stop writes on the master. It really depends on the situation whether you want reads to continue or not. If not shutdown mysql (this will stop your clients from hanging) If reads can …

  [Read more...]
Beware of your error logs. A story of indexes and alter table.
+0 Vote Up -0Vote Down

A few days ago I was contacted by a user that was getting table full errors. I explained that by default MySQL uses 4 byte pointers for row data and if the data file grows larger than 4 bytes MySQL will run out of space for pointers. I also explained about max_rows and avg_row_length and how they can be used with alter or create table to give himself larger row pointers. He took this information and happily altered his table expecting all of his data to be there and MySQL to begin adding new rows. What he got was a new table with only a few hundred rows of data. He pinged me in a panick asking what he did wrong. Having never seen such a thing myself I logged into the box. I checked mysqld.err …

  [Read more...]
MySQL int(11) and int(3) store the same values.
+0 Vote Up -0Vote Down

MySQL has a little know feature for numerical types known as zerofill. This feature effects the display size of numerical types. Unlike the string types the number inside the parentheses is not the storage size in characters for the type. For numerical types the type name itself determines storage size.

Column Type Bytes On Disk Unsigned Storage Range Signed Storage Range
tinyint 1 bytes …
  [Read more...]
10 Newer Entries Showing entries 91 to 100 of 104 4 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.