MariaDB – Add calculated field that shows day name

ALTER TABLE tablename 
ADD COLUMN dayname VARCHAR(20) GENERATED ALWAYS AS (DAYNAME(startdate)) 
STORED;

This query will add a new column called dayname to the tablename table. The column will be of type VARCHAR with a maximum length of 20 characters. The GENERATED ALWAYS clause tells MariaDB to calculate the value of the column each time a row is inserted or updated. The AS keyword specifies the expression used to calculate the value, in this case the DAYNAME function applied to the startdate column. Finally, the STORED keyword tells MariaDB to store the calculated value in the table so that it can be retrieved more efficiently.

Note that the GENERATED ALWAYS and STORED clauses require MariaDB version 5.7.6 or later. If you are using an earlier version of MariaDB, you can still add a calculated field using a trigger or a view.

MariaDB – Create query that matches value to a range in another table

For example lets say we were wanting to allocate transactions to a financial year but allow the users to adjust that financial year depending on their circumstances. How could we do that.

Here we have a table called
t0165financeyear
whose structure is

pkid
taxyear
startdate
enddate

And a table of transactions called t023finance

CREATE VIEW v0004financeyear
AS SELECT a.pkid pkidt0023,
a.banked,
a.transactiondate,
a.description,
a.category,
a.Direction,
a.hmrcint,
a.pkidt0001,
a.pkidt0007,
a.vamount,
a.invoiceno,
a.pkidt0011,
a.dupdated,
a.dcreated,
b.pkid pkidt00165,
b.taxyear from t0023finance a, t00165taxyears b
where
a.banked between b.startdate and b.enddate

Remarkably simple – I suspect that we might get cartesian join issues if your start date and end dates in the t00165 table overlap.

cPanel – take a manual backup of a MariaDB / MySQL database

There are no shortages of articles on how to take a backup of a MySQL or MariaDB database from cPanel however given how important it is I like to write these things down showing how I accomplished this for my own reference. Its extremely easy to do a download which means that you should not have any opportunity or reason not to do it regularly if you have an important database that for instance is part of a web application.

1. Enter your given cPanel management portal.

2.Find the database section and select the phpMyAdmin icon.

3.Select the database you are interested in from the lefthandside.

4.Click export in the menu section and then its just a case of clicking go. An SQL will be downloaded to the download directory (on a windows machine). And keep this and you can run this to create a new database.

And its very easy to go in and check if its ok you can use any good ide or you could change the suffix to TXT and then just look at it in word.
Happy backing up.!!!

Identify whether you are using MariaDB or MySQL using phpMyAdmin (through cPanel here)

When it comes to relational databases I used to think right there’s

    MySQL
    SQL Server
    PostGres
    Oracle

I know there is MariaDB as well and I know that is a direct substitute for MySQL I think I might be using it but you know what I’m not really sure. Whenever I look things up I quite often look for MySQL and to date any information obtained using those parameters have worked when executed against my databases through phpMyAdmin.

Should I be using MariaDB or hang on – am I???

Well turns out they are so similar that you might be using and not even know it. Turns out my hosting company uses MariaDB instead of MySQL but you might be under the impression you are still using MySQL. Why? Because if you are like me and you are using cPanel all the branding is still MySQL. I suspect this is actually very usual for anyone using cPanel.

Here’s my Database panel in cPanel

MySQL written all over the place even with a registered trademark symbol reinforced with the Dolphin MySQL logo.

Additionally phpMyAdmin never really mentions much about the database server you are using. And look it up and phpMyAdmin is often described as the MySQL client.

Am I using MySQL —— NOPE

Open up phpMyAdmin

Open the SQL panel and execute the following query.

SHOW VARIABLES LIKE "%Version%";

So despite all the logos and branding to the contrary I’m using MariaDB for this blog! Useful to know. I don’t know if it was MariaDB when I signed up in 2014 or not but this blog has been running continuously for 7 years and 7 months (at date of publishing this article) with no issues.

Kinsta on the differences between MariaDB and MySQL

I’m happy with being on MariaDB just because I am not mad about Oracle. Turns out I’m not alone in that position. Google / AliBaba and the European Investment Bank are all heavily invested in MariaDB which is probably why so many hosting companies actually run MariaDB in the background. There are big companies on MySQL but MariaDB is not going anywhere and many like me might have been running everything on MariaDB without even knowing it for years!

MariaDB on Wikipedia

I note in the Wiki article they state as a difference between MySQL and MariaDB Geographical Function support – not sure that part is true – I have asked Wikipedia to investigate , according to my research there is support via extensions although I have no personal experience of their use.

MYSQL / MariaDB – Useful SQL in both creating New Tables and Clarifying Default Parameters

Within an instance to identify the default character sets and collation in all databases.

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;

To alter the default character sets and collations for a database in a MySQL or MaraiDB instance.

ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

Creating Table – Template SQL

CREATE TABLE table1 
(col1 INT(10) NOT NULL AUTO_INCREMENT, 
col2 CHAR(30), 
PRIMARY KEY (col1))
ENGINE = INNODB;

And a good source of data types are here
Tech on the Net

There are unconfirmed reposts that queries against TEXT fields are generally 3 times slower than against VARCHAR fields.

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

VARCHAR(MAX)

Is a thing

And if you want to change the engine on a table retrospectively. See OPERATIONS.

And a short note on your choice of collation

From Stack overflow here

Actually, you probably want to use utf8_unicode_ci or utf8_general_ci.

utf8_general_ci sorts by stripping away all accents and sorting as if it were ASCII
utf8_unicode_ci uses the Unicode sort order, so it sorts correctly in more languages
However, if you are only using this to store English text, these shouldn’t differ.

Note further down there is a person that states “be very aware of this problem that can occur using utf8_general_ci

“MySQL will not distinguish between some characters in select statements, if the utf8_general_ci collation is used. This can lead to very nasty bugs – especially for example, where usernames are involved. Depending on the implementation that uses the database tables, this problem could allow malicious users to create a username matching an administrator account.

This problem exposes itself at the very least in early 5.x versions – I’m not sure if this behaviour as changed later.”

And here is some code that allowed me to reset the auto increment index on a table.

ALTER TABLE my_table MODIFY COLUMN pkid INT(10) UNSIGNED;
COMMIT;
ALTER TABLE my_table MODIFY COLUMN pkid INT(10) UNSIGNED AUTO_INCREMENT;
COMMIT;

I have tested it and works with INNODB and MySQL

Another way to do it might be to drop the pkid table and then recreate it again

Using

ALTER TABLE mytable DROP pkid;
ALTER TABLE mytable ADD pkid INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;