Showing entries 61 to 70 of 1179
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: Databases (reset)
What is FTS_BEING_DELETED.ibd

I currently have on a MySQL 5.6 database using innodb_file_per_table the following individual tablespace file.

schema/FTS_00000000000001bb_BEING_DELETED.ibd

The schema is all InnoDB tables, and there ARE NO Full Text Indexes. I cannot comment on if a developer has tried to create one previously.
I am none the wiser in explaining the ongoing use of these files, or if it can be/should be deleted.

On closer inspection there are infact a number of FTS files.

$ ls -al FTS*
-rw-r----- 1 mysql mysql 98304 Jan 29 16:21 FTS_00000000000001bb_BEING_DELETED_CACHE.ibd
-rw-r----- 1 mysql mysql 98304 Jan 29 16:20 FTS_00000000000001bb_BEING_DELETED.ibd
-rw-r----- 1 mysql mysql 98304 Jan 29 16:26 FTS_00000000000001bb_CONFIG.ibd
-rw-r----- 1 mysql mysql 98304 Jan 29 16:21 FTS_00000000000001bb_DELETED_CACHE.ibd
-rw-r----- 1 mysql mysql 98304 Jan 29 16:00 FTS_00000000000001bb_DELETED.ibd
-rw-r----- 1 mysql mysql 98304 Jan 29 16:20 …
[Read more]
Good Test Data

Over the years you collect datasets you have created for various types of testing, seeding databases etc. I have always thought one needs to better manage this for future re-use. Recently I wanted to do some “Big Data” playing and again that question of what datasets can I use let me to review the past collated list at Seeking public data for benchmarks.

The types of things I was wanting to do lead me to realize a lot of content is “public domain” and Project Gutenberg is just one great source of text in multiple languages. This was just one aspect of my wish list but text based data is used from blogs, comments, articles, microblogs etc, and multiple languages was important from some text analysis.

With a bit of thinking about the building blocks, I created …

[Read more]
The GRANT/REVOKE dilemma

It is common practice to grant your application the privileges of “GRANT SELECT, INSERT, UPDATE, DELETE ON yourdb.* TO user@host”.

But what if you want to ensure you cannot DELETE data from just one table?

Ideally I want to be able to “REVOKE DELETE ON yourdb.important_table FROM user@host”. You cannot do currently this with the MySQL privilege system.

If your schema has 100 tables, and you want to remove DELETE from one, you have to define DELETE for the 99 others, and remember that for each new table, you need to remember to also modify user privileges.

MySQL Explain Explained

Whenever I have a problem query in MySQL, I say to myself, I know, I’ll use Explain Plan. Now I have two problems. I run the explain plan, but then I have to look up all the cryptic terms and read through the documentation to know what in the world is going on. Well, over the weekend, I decided to solve my own problem. I created a simple page that allows you to run an explain plan, and then each of those obscure, abbreviated terms is annotated with a tooltip that explains exactly what’s going on. I’m calling it MySQL Explain Explained.

I created a demo using the sample employees database (which you can download here). Here’s a sample query:

select gender, count(emp_no) as manager_cnt, sum(cnt) as sum, avg(cnt) as avg
from
(
    select m.emp_no, m.gender, count(de.emp_no) as cnt
    from …
[Read more]
Designing Multi-Tenanted Databases

Designing database tables for many customers is a surprisingly foreign concept for some programmers. I’ve been in interviews where a programmer will sketch out a normal object model, and then proceed to suggest that for the database, each customer has their own set of tables, each prefixed with the customer name. What I’d like to do in this post is introduce the concept of multi-tenancy and then show methods you can use to do it right, instead of hacky ways like the one I just mentioned. Multi-tenancy is when several customers (tenants) share the same database and codebase but can only see their own data.

The basic idea

The core method of adding multi-tenancy to your database is adding a column to every table you’d like to segment that indicates the owner of the data. I’ll call this column a tenant id. We’ll start with a basic example: a todo app. Let’s say you write a program that you’re going to use on your own to …

[Read more]
Null Columns in MySQL – Part II

I’ve covered some strange default behavior around nulls in MySQL. There’s another nuance to this issue: you can still insert rows without specifying values for not null columns. MySQL will helpfully give you a default value (this is not really helpful – true help would be a quick failure with a descriptive error message). Let’s walk through this example.

mysql> Create Table NullTable (a Int Not Null, b Int Not Null);
Query OK, 0 rows affected (0.01 sec)

Here’s a table with two not null columns.

mysql> Insert Into NullTable (a) Values (1);
Query OK, 1 row affected, 1 warning (0.01 sec)

We can totally insert, even though we didn’t specify a value for b and b is not null.

mysql> Show Warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message …
[Read more]
MySQL Simplified

MySQL is the little engine that could. It powers sites like Facebook, YouTube, Twitter, and thousands of blogs, CMSes, and e-commerce sites. Its value to the world and to the development community could be measured in the hundreds of billions, and yet it’s free, and you can use it just by downloading it. Almost every programming language has drivers for it and it can run on so many operating systems and architectures, there’s really no limit on it.

Yet there’s a dark side. MySQL is full of gotchas and bugs, and it lacks features that sometimes call into question its status as a real database. The documentation is often open-ended and confusing, with gaps in key parts. If you want to run it, you have the option of using it on Linux, Mac, Solaris, or Windows and every hosting company or provider like Amazon AWS has their own managed service, each with its own quirks and limitations. The user community has also produced thousands of …

[Read more]
MongoDB in 2013 -- A Year in Review

It's again that time of the year. Analysts are spending oceans of words to predict the future, companies are making plans for the next year and people are resting and enjoying the break with their families. To me, this is the perfect time to reflect on my choices, the direction I'm headed to and consider if I still love what I do.

At the beginning of the year I decided to join MongoDB (formerly 10gen). The more I think about it, the more I realize I've been wrong. Yes, it's been the worst decision in my life not to join MongoDB when I was first offered the opportunity years ago. At that time an insightful friend asked me to consider the opportunity. At that time I didn't see what I see today.

I didn't see the …

[Read more]
MongoDB in 2013 -- A Year in Review

It's again that time of the year. Analysts are spending oceans of words to predict the future, companies are making plans for the next year and people are resting and enjoying the break with their families. To me, this is the perfect time to reflect on my choices, the direction I'm headed to and consider if I still love what I do.

At the beginning of the year I decided to join MongoDB (formerly 10gen). The more I think about it, the more I realize I've been wrong. Yes, it's been the worst decision in my life not to join MongoDB when I was first offered the opportunity years ago. At that time an insightful friend asked me to consider the opportunity. At that time I didn't see what I see today.

I didn't see the …

[Read more]
Upgrading from MySQL 5.1 to MariaDB 5.5

In my last post, a tale of two MySQL upgrades, a few folks asked if I would outline the process we used to upgrade, and what kind of downtime we had.

Well, the processes were different for each upgrade, so I will tackle them in separate blog posts. The first step was to upgrade all our MySQL 5.1 machines to MariaDB 5.5. As mentioned in the previous post, MariaDB’s superior performance for subqueries is why we switched – and we switched back to MySQL for 5.6 to take full advantage of the performance_schema.

It is not difficult to blog about our procedure, as we have documentation on each process. My first tip would be to do that in your own environment. This also enables other folks to help, even if they are sysadmins and not normally DBAs. You may notice the steps contain items that might be “obvious” to someone who has done …

[Read more]
Showing entries 61 to 70 of 1179
« 10 Newer Entries | 10 Older Entries »