Did you lose your MariaDB root password? (GNU/Linux)

Don’t even think to drastical solutions. If you can log into GNU/Linux as root, you can always recover MariaDB root password.

Did you never know the password?

Maybe you installed MariaDB, or you bought a new server, but you don’t know the root password. Don’t panic! It’s ok!

Probably there is no password. Well, this is false; MariaDB asks for a password, and you won’t be able to logon if the password is incorrect; but the password is an empty string. On the CLI, just press enter to access.

Change it: it is insecure. If I had to break into a MariaDB/MySQL installation as root, I would first try an empty password. Don’t let me break into your system so easily!

Ok, you lost the password

Ok, you lost it. This is not the simplest case, but… it’s simple!

1) Log into your GNU/Linux system as the user used by MySQL (usually ‘mysql’) or root.

2) Restart MariaDB with the grant tables disabled:
mysqld_safe --skip-grant-tables --skip-networking

mysqld_safe will shut down mysqld for you.
With --skip-grant-tables, no password is needed to logon.
This is unsafe, so until the password is reset MariaDB should not accept network connections (--skip-networking).

3) Logon with no password:
mysql -u root

4) Set your new password.

Exec these 2 SQL statements and exit the client:

-- change pwd
UPDATE `mysql`.`user`
	SET `Password` = PASSWORD('new_password')
	WHERE `User` = 'root';
-- tell the server to read the grant tables
FLUSH PRIVILEGES;
\quit

(replace ‘new_password’ with the new password)

5) Stop mysqld_safe and restart mysqld:

mysqladmin shutdown
/etc/init.d/mysql start

(depending from your system, you may need to replace ‘/etc/init.d‘ with the correct MySQL path)

6) Logoff from you system (because you are now root or someone very powerful).

See also:

Enjoy!

Advertisements

MariaDB: How to create a Black Hole when BLACKHOLE is disabled

Versione italiana

The BLACKHOLE Storage Engine causes data to fall into a black hole. Not only the data you are INSERTing or LOADing, but also existing data. In other words, BLACKHOLE disables a table and erases its contents. It can be useful for debugging (don’t execute query, but verify if they cause an error) or other special purposes.

Thought, hosting providers generally have BLACKHOLE disabled. I’m not sure about how that simple Storage Engine can damage their service, but it’s usually you can’t use it, it’s a fact.

But then, how can you emulate a BLACKHOLE table? The answer is simple: just use a VIEW!

First, we need a table. The process is trivial and riskless, so I will not write a CREATE TABLE. Use one of your existing tables. The following example assumes that your table is called `t_user` – replace that name with the real one.

Then, we create a view. It is of the simplest type: it just mirrors an existing table. You can read from it and write in it. Here’s the code:

CREATE OR REPLACE VIEW `t_user`
	AS SELECT * FROM `user`;

You will use `t_user` instead of `user` for all your operations. At some point, you want to disable that view – ie, turn it into a Black Hole!

The process, as I said, is trivial. Just re-define `user`, but with 2 changes:
* Add a WHERE FALSE clause. Obviously the view will not show any data (because no row can possibly satisfy a WHERE FALSE).
* Add a WITH CHECK OPTION clause. It will make the view not-writable (because a row can only be INSERTed if it satisfies WHERE FALSE, which is impossible).

Here’s the code:

CREATE OR REPLACE VIEW `t_user`
	AS SELECT * FROM `user` WHERE FALSE
	WITH CHECK OPTION;

When you are ready to turn the Black Hole into a habitable planet, execute the first statement again.

The OR REPLACE clause is useful in this case, because you don’t need to DROP an re-CREATE the view – you replace it with a single atomic operation.

Enjoy!

Working with different tables using a view

The problem and the possible solutions

Most online stores have a common problem: they sell several families of products and each of them has different attributes. For example, shirts have a color and a size, while cell phones have a brand and a model. For this reason, a reasonable solution may be to store them in different tables. But they also have some important common attributes, like price or avaibility; and those attributes are read by many queries, so it would be comfortable to have those families in one table.

The worst solution is to create one table with a lots of columns which will be used only for few records. The historical solution is view-based. MariaDB introduces Virtual Columns, so there is now a Virtual Columns-based solution.

In this article we will discuss the view-based solution. Virtual Columns will be explained in a future article.

Ok, let’s CREATE what we need

Here’s the SQL code:

CREATE TABLE `shirt`
(
	`id`          TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
	`price`       DECIMAL(10, 2) NOT NULL,
	`avaibility`  SMALLINT NOT NULL,
	`size`        ENUM('xl','l','m','s','xs') NOT NULL,
	`color`       ENUM('yellow','white','black') NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE `cell`
(
	`id`          TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
	`price`       DECIMAL(10, 2) NOT NULL,
	`avaibility`  SMALLINT NOT NULL,
	`brand`       CHAR(50) NOT NULL,
	`model`       CHAR(50) NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE VIEW `product`
	AS
		(
		SELECT 
			`id` + 0 AS `id`, `id` AS `origin_id`, 'shirt' AS `family`, `price`, `avaibility`
			FROM `shirt`
		) UNION (
		SELECT 
			`id` + 1000 AS `id`, `id` AS `origin_id`, 'cell' AS `family`, `price`, `avaibility`
			FROM `cell`
		);

I think that the tables don’t need to be explain. There is a table for every product family, and they could be 100 or 1000; but for this tutorial, 2 are enough.

The view contains the common attributes of the different product families (price, avaibility). To gather them in one view, we use a UNION statement. Plus, the view contains some columns that need some to be explained.

Points of VIEW

`family`: This is a constant value, but rows located in different tables have a different value for `family`. That’s exactly what we want, so that we can distinguish shirts from phones.

`id`: We want to have a unique id for all products, disregarding their family. But differenct tables may contain many identical `id` values, so we can’t rely on their `id`. That’s why we need to create an artificial value inside the view.
Note that in this example shirts and phones have a TINYINT Primary Key. This is reasonable, because a store can hardly sell more than 100 different types of shirts or cell phones, and the maximum value for TINYINT UNSIGNED is 255. We need to find a comfortable value that is higher than 255: 1000 is a good candidate.
Now, we decide that id’s of product of different families are in different ranges. So the shirts id’s are 1..255. But id’s of cell phones are 1000..1255, so the value is unique. We do this by adding 1000 to the cell phones id’s.
This value can be used for SELECTs which read common values for specific rows in the view, not in the basetables.

`origin_id`: We can’t use the `id` value to UPDATE or DELETE individual rows, because the VIEWs based on a UNION are not updatable. To execute those operations, we must run our statements directly on the tables. But to do this, we need to know their original `id`. That’s why we have an `origin_id` field: it matches to the `id` columns in the basetables.

The distinction between `id` and `origin_id` is not very common. I like it, because it allows me to SELECT a signgle row from the view, when/if I need it; however, this rarely happens. So you may want to include the basetables `id` in your view without any alias, and forget the artificial value.

It’s important to remember that the view is not insertable or updatable: you will SELECT from the view, but insert, update and delete from the basetables.

Enjoy!

Emulating the CHECK clause in MariaDB

Versione italiana

MariaDB (like MySQL and its forks) does not have a CHECK clause in the CREATE TABLE statement. But you can emulate it using VIEWs.

Data Definition

First, let’s create a table:

CREATE TABLE `t_person`
(
	`id`       INTEGER UNSIGNED NOT NULL AUTO_INCREMENT ,
	`name`     CHAR(30) NOT NULL ,
	`surname`  CHAR(30) NOT NULL ,
	`email`    CHAR(50) NOT NULL ,
	PRIMARY KEY (`id`)
);

The table is ready to store our data, but we need to check that data is valid. When new row is inserted, we want to check that `surname` contains at least 2 characters and `email` contains ‘@’ and ‘.’ characters.

So, let’s create a view that would show only data that follow these simple rules:

CREATE VIEW `person`
	AS SELECT * FROM `t_person`
		WHERE LENGTH(`surname`) > 1
			AND `email` LIKE '%_@_%._%'
	WITH CHECK OPTION;

The view is INSERTable, because it is based on only one basetable and has no grouping functions/clauses. The WITH CHECK OPTION clause allow new rows to be inserted only if they satisfy the view’s WHERE condition.

Data Manipulation

Now, you can insert data through the view. Let’s start with an invalid row:

INSERT
	INTO `person`
		(`name`, `surname`, `email`)
	VALUES
		('Mario', 'Rossi', 'mario.rossi');

`email` does not contain a ‘@’, so the row is rejected with a good error message:
[Err] 1369 - CHECK OPTION failed 'test.person'

So, let’s try to insert a valid row:

INSERT
	INTO `person`
		(`name`, `surname`, `email`)
	VALUES
		('Mario', 'Rossi', 'mario.rossi@rossionline.it');

The row is successfully inserted!

You can SELECT your data from `t_person` or from `person`, as you prefer. If you use `person`, than you (or other developers) can pretend that the view is a table and `t_person` does not exists – it’s transparent. However, SELECTing rows from `person` adds a useless WHERE that is always true but affects the performances.

Enjoy!

See also:

Yet Another HELLO_WORLD()

As the title says, this is yet another blog about programming (YABAP). Because every BAP starts with a helloWorld(), here’s Yet Another Hello World (YAHW). And because all technical sites have a FAQ listing questions that no one ever asked (but the authors would really like to answer), let’s start with…

Yet Another FAQ (FAQ YA!)

Who are you?

My name is Federico Razzoli. I am a developer and a Free Software supporter. I live in Italy and my age is a secret, like many things in Italy.

Is the reason why you support Free Software technical, ethical or fanatical?

I don’t like intellectual property, because I don’t like property. If you think that my vision is stupid, you are free to stop using my software or reading my technical articles. Or, you can pay for them. Please, do it: I like sharing, but I also like to steal money from stupid people! ๐Ÿ™‚

Your blog has a strange title. Why?

Words are important, because they have a meaning. One of the reason why the people do not think very much is that they have been educated to use random words. If you want to think (and communicate), you must start thinking to the meaning of the words you use.

False is not null: it’s correct. False is something, and null is nothing. Nothing is not false, because what do not exist have no properties.

Also, FALSE IS NOT NULL is a valid (and TRUE) SQL expression.

Did the internet need yet another programming blog?

This question is very interesting. It can’t be answered now. I’ll try to write something interesting. Then, if I succed, the answer will be TRUE.

At least, I will write my contents. I will not do a blind copy/paste work like many blogs I’ve visited.

That’s all, for now. See ya!
Federico