Posts in the “mysql” category

MySQL example database design: A Nagios MySQL database design

MySQL database FAQ: Can you provide a large, complete, example MySQL database design?

I recently worked on a project where I wrote a web-based user interface that would let "everyday users" maintain their own Nagios configuration data. After logging in to the web interface, users could modify the Nagios configuration as desired, for instance, when they added new equipment to their network.

A MySQL “create table” syntax example

MySQL/MariaDB FAQ: What is the MySQL create table syntax?

Here’s a quick example of the MySQL create table syntax on a database table named users:

drop table if exists users;

create table users (
    id int auto_increment not null,
    username varchar(32) not null,
    password varchar(16) not null,
    primary key (id),
    constraint unique index idx_users_unique (username)
) ENGINE = InnoDB;

If you need more examples of the MySQL create table syntax, or examples of the MySQL unique constraint syntax, check out my MySQL Nagios database design. It includes many database tables, and as a result, it covers examples of many MySQL/MariaDB features.

MySQL create database example - How to create a MySQL database and user account for Drupal

MySQL “create database” FAQ: Can you share an example of how to create a MySQL database, including (a) creating the MySQL database and (b) a new MySQL database user, with all appropriate MySQL grant permissions for that user?

Sure. I've written about the process of How to add/create a MySQL user before, but I just ran across the Drupal installation documentation, which also has a nice description for how to create a MySQL database (for use with Drupal), so I thought I'd include some of their documentation here, along with some notes from my own recent Drupal and MySQL installation.

1) Create a new MySQL database

The first part of the Drupal installation document refers to creating a MySQL database:

MySQL show status: How to show open database connections

MySQL "show status" FAQ: Can you demonstrate how to use the MySQL show status command to show MySQL variables and status information, such as the number of open MySQL connections?

I don't have a whole lot of time today to give this a detailed discussion, but here is a quick look at some MySQL work that I did recently to show MySQL open database connections.

[toc hidden:1]

MySQL error: Expression of SELECT list is not in GROUP BY clause, nonaggregated column

Update: On September 21, 2021, using MySQL 8 and Ubuntu 20.04, the solution to this problem was to edit this file:

/etc/mysql/mysql.conf.d/mysqld.cnf

Then add this configuration line to the end of that file:

sql_mode = ""

Once I did that, the problem described below went away.

Note that you can use this command to show your MySQL version on Ubuntu Linux:

$ mysql -V
mysql  Ver 8.0.26-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

Background and older solution

NOTE: Everything below this point describes the background to this problem, and solutions I used with older versions of MySQL and Ubuntu Linux.

As a brief note to self, I ran into this MySQL error when running a SQL SELECT and GROUP BY query:

02:14:33.911 [main] ERROR scalikejdbc.StatementExecutor$$anon$1 - SQL execution failed
(Reason: Expression #3 of SELECT list is not in GROUP BY clause and contains
nonaggregated column 'aad8final.u.alias' which is not functionally dependent on columns
in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by):

select tn.nid, count(tn.nid) as countOfTnNid, u.alias, n.title 
from taxonomy_index tn, url_alias u, node_field_data n 
where tn.tid in (select tid from taxonomy_index where nid = 9423) 
and tn.nid = u.nid 
and tn.nid = n.nid 
and n.status = 1 
and n.nid != 9423 
group by tn.nid 
order by countOfTnNid 
desc limit 5

A MySQL database backup (mysqldump) shell script

MySQL database backup FAQ: Can you share a Linux shell script that I can use to make a MySQL backup (i.e., a shell script that wraps the mysqldump command)?

I currently have a collection of websites on several different servers (including GoDaddy and A2 Hosting web servers), so I was just spending some time trying to automate my MySQL database backups. To that end, I just created a MySQL shell script that I use on each Linux server to make my database backups, and I thought I'd share that script here.

How to find all MySQL database tables that have specific column names

MySQL FAQ: How can I find all MySQL database tables that have specific column names?

I found the solution on this SO page, and here’s my take on it. First, assuming that you want to copy and paste those column names after you get them, I recommend starting the MySQL command line client like this:

$ mysql -sN -u root -p

The -sN options get rid of all of the header and formatting information that MySQL typically puts around query results.

Next, this is the MySQL/MariaDB query you want to use:

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('column1', 'column2')
AND TABLE_SCHEMA='your_database_name';

Example results and output

For example, I was just looking for a column named langcode in all Drupal 8 database tables, so I used this query:

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('langcode')
AND TABLE_SCHEMA='drupal8';

This returned a very long list of tables. I’ll show only the first five tables, which are listed like this by the MySQL client:

block_content
block_content__body
block_content_field_data
block_content_field_revision
block_content_revision

As mentioned, the -sN options get rid of all the usual MySQL output formatting, so those are the entire results. If you don’t use those options, your output will look like this instead:

+-----------------------------------+
| TABLE_NAME                        |
+-----------------------------------+
| block_content                     |
| block_content__body               |
| block_content_field_data          |
| block_content_field_revision      |
| block_content_revision            |

Summary

In summary, if you want to know how to find one or more column names that are contained in all of your MySQL database tables, I hope you find this helpful.

How to perform a SQL query for fields that are null (or not null)

For some reason when I use SQL I can never remember how to search for database table fields that are either NULL or NOT NULL. I always try to use the = operator or something else.

So, as a note to myself, here’s an example of how to perform a SQL query and find all records where a field in a database table is NULL:

How to search for a string in all fields of every table in a MySQL database

Here’s a cool tip: if you want to search for a text string in all fields of all tables of a MySQL database, you can use phpMyAdmin to do this very easily. Here are the steps to search every MySQL/MariaDB database table.

1) Select the desired database

The first step is to select the database you want to search. Don’t select a table — just select the database you want to search. (If you select a table you’ll get a different search form in Step 2.)

MySQL FAQ: How do I show the fields or schema of a database table?

MySQL FAQ: How do I show the schema of a MySQL database table?

Answer: Use the desc command from the MySQL command line client.

For instance, in my current application I have a database table named orders, and when I need to see the schema for that table I show it with the desc command like this:

desc orders

The MySQL output looks like this:

A MySQL Vacuum database tables script (auto_increment reset)

MySQL vacuum FAQ: How do I vacuum a MySQL database table, or an entire MySQL database?

This may be an unusual situation, but as I'm creating a new application that uses a MySQL database, I've found that I want a "MySQL vacuum" operation; I want to reset all my MySQL auto_increment counters back to 1 without having to completely drop and then re-create my MySQL database.

Specifically what I'm doing is this:

A MySQL foreign keys “drop table” and re-create table example

MySQL FAQ: How can I drop a series of database tables that have foreign key relationships between them?

If you ever have a problem where you need to drop a series of MySQL database tables that have foreign key relationships between them, the key to doing this is setting the FOREIGN_KEY_CHECKS variable before and after your MySQL DROP TABLE queries.

For example, something like this should work:

MySQL drop table and foreign keys tip - the FOREIGN_KEY_CHECKS variable

MySQL “DROP TABLE” FAQ: Help, my MySQL database tables have a lot of foreign keys, and as a result it's a pain to use the MySQL DROP TABLE command in my scripts; they keep failing because of all the foreign keys. Is there something I can do to work around this DROP TABLE foreign keys problem?

A MySQL database table index example

MySQL FAQ: How do I create a MySQL database table index?

Here's an example of how to create an index on a MySQL database table. In this particular example my table uses the MySQL InnoDB database engine, but that doesn't matter too much -- it just happens to be a MySQL database engine that supports foreign key relationships.

To get started, here's the SQL definition of a MySQL table I've named ftp_files: