|Showing entries 1 to 30 of 1001||Next 30 Older Entries|
We're looking for an energetic and talented web developer to join our small but agile web team. This position is full-time and pay is negotiable. The hours are flexible and work can be done remotely.
Severalnines is a self-funded startup with a dozen employees; headquartered in Stockholm, Sweden and with a globally distributed, home-office based team. We provide automation and[Read more...]
To ensure that your MySQL Database works as efficiently as possible, it is important to know how to handle error messages and warnings.
Error messages have three components:
MySQL Server generates a warning when it is not fully able to comply with a request or when an action has possibly unintended side effects. You can display these warnings with the SHOW WARNINGS statement.
To learn about handling error messages and warnings along with other developer topics, consider taking the[Read more...]
In the article How MariaDB makes Stored Procedures usable I explained how to use the MariaDB CONNECT Storage Engine to workaround Stored Procedures limitations. Please read that article, if you didn’t, for details.
Since the technique to workaround such limitations is verbose and error-proof, I also proposed a procedure which makes it easy and convenient. Even thought this is a simple country blog, I have immediately received positive feedback after that article. But then, people started to write me: “Hey, doesn’t work!”. The reason is… MariaDB/MySQL bugs. While investigating these problems, I was able to report[Read more...]
I already wrote how MariaDB makes the debug of stored procedures much easier via the SQL Error Log. But I recently found out that MariaDB provides a usable workaround for some big limitations of their procedural SQL.
SELECT is not the only SQL statement which returns a resultset. Other examples are
EXPLAIN and administrative commands like
ANALYZE TABLE or
CHECK TABLE. But these commands cannot be used in place of
SELECT in the following contexts:
A very old version of the MySQL site contained a nice header: Speed, Power, Ease of Use. I don’t think that stored programs (routines, triggers, events) were supported, at that time. Now they are, and… developing them is amazingly hard.
There are many problems, for example the language is not flexible and the execution is sloooow. But the biggest problem is that there is no debug API.
In details, one of the biggest problems is that, if you have complex procedures (accessing multiple tables, having error handling, and/or calling other procedures) in practice you have no idea of what warnings occur within your procedures.
MariaDB 10.0 makes things much easier by adding the[Read more...]
“There are four things that motivate open source development teams:
1. The challenge/puzzle of programming, 2. Need for the software, 3. Personal advancement, 4. Belief in open source” — Bruce Momjian.
On PostgreSQL and the challenges of motivating and managing open source teams, I have interviewed Bruce Momjian, Senior Database Architect at EnterpriseDB, and Co-founder of the PostgreSQL Global Development Group and Core Contributor.
Q1. How did you manage to transform PostgreSQL from an abandoned academic project into a commercially viable, now enterprise relational database?[Read more...]
While working with an error that my students surfaced in the Oracle Database 12c, I blogged about the limit of using a subquery in an Oracle
INSERT statement, and I discovered something when retesting it in MySQL. It was a different limitation. I was also surprised when I didn’t find any mention of it through a Google search, but then I may just not have the right keywords.
The original test case uses a subquery to create a multiple row result set for an
INSERT statement. Unfortunately, I discovered that you can’t always embed a
UNION ALL inside a subquery. At least, you can’t when you call the subquery inside an
INSERT statement. For example, let’s create a
Of course, The postings on this site are my own and don’t necessarily represent IBM’s positions, strategies or opinions. Also, these numbers should be considered preliminary, but trust me – I did get them and it’s not April 1st.
From my[Read more...]
I got this idea from a Valerii Kravchuk’s MySQL bug report:
In theory, I completely agree that MySQL and forks should not allow us to set a default storage engine which cannot be used to create a table. You can see the same with MariaDB’s
SEQUENCE. The MySQL & forks philosophy seems to be: ignore your mistakes, so you can repeat them forever. Which can turn a mistype into a major data loss.
Unless you only use InnoDB and your magic powers tell you that this will never change, the
ENGINE clause should be mandatory in your MySQL installation. Since there is no clean way to make it mandatory, setting a “weird” storage engine as default seems to be a decent workaround. I don’t like
This is a list of MariaDB storage engines that are not distributed with MySQL. I think that most of them will work with MySQL, but not all – at least CassandraSE doesn’t.Engine Description Introduced XtraDB A fully-compatible fork of InnoDB, mantained by Percona Big Bang Aria A crash-safe MyISAM, also used for internal temptables Big Bang TokuDB A transactional engine with innovative buffers and high compression, by TokuTek 10.0 [Read more...]
The Oracle Database 12c documentation says you can set the maximum size of a
VARCHAR2 to 32,767 bytes. That’s true except when you’re trying to map a large Java string to a
VARCHAR2. It fails when the physical size of the Java string is greater than 4,000 bytes with an
ORA-01002 or fetch out of sequence error.
SELECT read_text_file('C:\Data\loader\Hobbit1.txt') * ERROR at line 1: ORA-24345: A Truncation or null fetch error occurred ERROR: ORA-01002: fetch out of sequence
You need to grant privileges before you can test this code. You can grant privileges by connecting as the
SYS user of a CDB (or non-multitenant database) or as the ADMIN user of a PDB with the
AS SYSDBA clause. Then, you run the following command to grant external file access to the JVM inside Oracle
Since the dawn of time, MySQL indexes have a limit: they cannot be descending. Yes,
ALTER TABLE and
CREATE INDEX can contain the
DESC keywords. It is perfectly legal in MySQL. But this does not mean that descending indexes are created. This is a well-known MySQL feature: when it cannot do something, it just pretends to. Well… someone thinks it is a feature. I think it is a bug (a bug is an unexpected behaviour), but what can we do.
The lack of support for descending indexes is only an issue when we need to create an index in which at least one column is ascending and at least one column is descending. For example, MySQL pretends to understand the following statement, but the resulting index won’t probably help us:
CREATE INDEX my_index ON my_table (my_column ASC, your_column
Getting started with MariaDB, by Daniel Bartholomew, is a good book for people who wants to approach MariaDB without knowing MySQL. While this book covers all basic topics, it provides a vast overview of what MariaDB is and can do. In other words: the text is not just about SQL queries.
The book also mentions topics that are not strictly related to MariaDB, but are important for MariaDB users; for[Read more...]
I want to teach you the difference between an inner and an outer join. We first need to think about what a join is. Simply, it’s when you combine two tables to make a new one. You’re not physically creating a new table when you join them together, but for the purposes of the query, you are creating a new virtual table. Every row now has the columns from both tables. So if TableA has columns Col1 and Col2 and TableB has columns Col3 and Col4, when you join these two tables, you’ll get Col1, Col2, Col3, and Col4. Just as with any query, you have the option of including all columns or excluding some, as well as filtering out rows.
Inner join. A join is combining the rows from two tables. An inner join[Read more...]
Over the past few years, we’ve seen MySQL technology advance in leaps and bounds, especially when it comes to scalability. But by focusing on the internals of the storage engine for so long, MySQL has fallen behind regarding support for advanced SQL features.
Why is this significant? It means that MySQL is now the only widely-used SQL implementation that does not support recursive queries. Fifteen years after it was defined in the SQL standard, almost every other SQL database of note has supported this feature:
As I already wrote,
COUNT(*) can be expensive in InnoDB, because this value is not stored in indexes. Some other storage engines may not store that value. Also,
MIN() are immediate only if executed on an indexed column. But we don’t want too many indexes, and some exotic storage engines do not support indexes – thus, we may need to execute an aggregate function on a non-indexed column.
Now, the question is: do you really need such values to be exact? If you need to report statistical values from your database, probably an error is acceptable. In this case, MariaDB 10 provides a solution:[Read more...]
Not too long ago I attended a talk about a database called Datomic. My overall impressions of Datomic were pretty negative, but this blog post isn’t about that. This is about one of the things the speaker referenced a lot: immutability and its benefits. I hope to illustrate, if only sketchily, why a lot of sophisticated databases are actually leaps and bounds beyond the simplistic design of such immutable databases. This is in direct contradiction to what proponents of Datomic-like systems would have you believe; they’d tell you that their immutable database implementations are advanced. Reality is not so clear-cut.
The Datomic-in-a-nutshell is that it (apparently) uses an append-only B-tree to record data, and never[Read more...]
I clicked around for a few moments but didn’t immediately see a license mentioned for the MariaDB knowledgebase. As far as I know, the MySQL documentation is not licensed in a way that would allow copying or derivative works, but at least some of the MariaDB Knowledge Base seems to be pretty similar to the corresponding MySQL documentation. See for example LOAD DATA LOCAL INFILE: MariaDB, MySQL.
Oracle’s MySQL documentation has a licensing notice that states:
You may create a printed copy of this documentation solely for your own personal
Enough negativity sometimes gets slung around that it’s easy to forget how much good is going on. I want to give a public thumbs-up to the great job the MySQL community team, especially Morgan Tocker, is doing. I don’t remember ever having so much good interaction with this team, not even in the “good old days”:
Here’s a trip down memory lane. I was just cleaning out some stuff and I found some notes I took from a hilarious MySQL seminar a few years back. I won’t say when or where, to protect the guilty.
I found it so absurd that I had to write down what I was witnessing. Enough time has passed that we can probably all laugh about this now. Times and people have changed.
The seminar was a sales pitch in disguise, of course. The speakers were singing Powerpoint Karaoke to slides real tech people had written. Every now and then, when they advanced a slide, they must have had a panicked moment. “I don’t remember this slide at all!” they must have been thinking. So they’d mumble something really funny and trying-too-hard-to-be-casual about “oh, yeah, [insert topic here][Read more...]
I just tried out EXPLAIN UPDATE in MySQL 5.6 and found unexpected results. This query has no usable index:
EXPLAIN UPDATE ... WHERE col1 = 9 AND col2 = 'something'\G
*************************** 1. row ***************************
Extra: Using where
The EXPLAIN output makes it seem like a perfectly fine query, but it’s a full table scan. If I do the old trick of rewriting it to a SELECT I see that:
*************************** 1. row ***************************
|Showing entries 1 to 30 of 1001||Next 30 Older Entries|