Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Previous 30 Newer Entries Showing entries 91 to 104

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 into the correct page and not just the search results with that page at the top. I love this! I just needs to work for every syntax search.

While I’m on the subject of manual search I would like to request a few features. Migration to

  [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;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 5 | root | localhost | NULL |





  [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 sec)

mysql> select * from mug;
Empty set (0.00 sec)

mysql> delete from pot limit 1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from mug;
+--------+
| coffee |
+--------+
|      1 |
+--------+
1
  [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 of several storage engines available to users. Contrary to popular belief it is not the only storage engine that supports transactions.

  [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 when copying the bak file over the original I would mis-type my.cnf.

Introducing brace expansion. Brace expansion allows you to create multiple modified command line arguments out of a single argument.




  [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 continue a simple flush tables with read lock; will be fine. Once the slave has caught up flush tables on the slave and copy the table to the master. Once the table is in place on the master start mysql or flush tables and

  [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 and found that for the previous 10 days or so mysqld had been writing corrupt index warnings to the log. Fortunately he had a slave that we could recover the data from.

After some research the best explanation I can

  [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 -128 to 127 0 to 255 smallint 2 bytes -32768 to 32767 0 to 65535 mediumint 3 bytes -8388608 to 8388607 0 to 16777215 int 4 bytes -2147483648 to 2147483647 0 to 4294967295 bigint 8 bytes -9223372036854775808 to 9223372036854775807 0 to 18446744073709551615

The confusion between types comes from the number inside parentheses for different types. The integer type it’s the padding size for zerofill. The following examples demonstrates zerofill. All of these

  [Read more...]
How to get your question answered on irc by example
+0 Vote Up -0Vote Down

n0other: Hello, I have a field publish_to, I want to select only those entries, where publish_to is not older than today, publish_to contains for example ‘2005-05-30′, I do: SELECT * FROM table WHERE (publish_to - “‘.date(’Y-m-d’) .’”) > 0; (php date function), but that doesnt work, how should I implement this, I cannot change date structure

Notice the detailed question with example. This shows people exactly what the problem is and that you have taken the time to try to solve it yourself.

firewire: so you want where it’s today and newer?
firewire: where publish_to >= curdate()

A quick response prior to testing

n0other: oh :}

Now after a quick test a final response letting everyone know that the problem was solved



  [Read more...]
Root, mysql, root - A simple explanation of users.
+0 Vote Up -0Vote Down

There seems to be a lot of confusion in the newbie crowd about the difference between MySQL and OS users. It works like this. Your operating system supports users. That’s who you log in as when prompted. When logged in you can start processes or programs. If I log into my linux box as user eric anything started by me is said to be running as user eric. There is a super user known as root that has total control over the system. It’s not a good idea to run processes as root because they have the power to change anything in the system both bad and good. When installing MySQL the install guide says to create a user known as ‘mysql’. This is a new user in the operating system just like user ‘eric’. One of the things root can do is start processes as other users. Mysqld will complain if you start it as root without telling it to change into another user after executing. This can be

  [Read more...]
When MySQL Will Order Data.
+0 Vote Up -0Vote Down

This is cut and paste from an email I’m sending to the general mailing list. I’m putting this here so people can add comments. If any of this is wrong or you can think of a situation I didn’t. Add a nice comment

Here, off the top of my head are situations in mysql where you can trust that the data is ordered in some fasion.
1. Using an order by clause on a query.
2. Using a group by the data will come out in ascending order of the column that was grouped on.
3. alter table order by has been performed and the table hasn’t been modified .
4. select key from t; that uses the ‘Using Index’ in explain will return in the order of the key.

Use order by anyway just to be safe.

Naming Conventions In MySQL Revised.
+0 Vote Up -0Vote Down
I decided to repost the rules with Rule 3 removed. After review and discussion with a few people I decided that not naming tables plural couldn’t be a rule since there are times when it’s ok to name tables plural. I downloaded MySQL 5.0 Alpha to check out information_schema views. After a few minutes of [...]
Previous 30 Newer Entries Showing entries 91 to 104

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.