Posts in the “mysql” category

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

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 "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.

MySQL/MariaDB FAQ: How do I show or list MySQL users, i.e., show the user accounts in a MySQL or MariaDB database?

Solution

To show the users in a MySQL or MariaDB database — i.e., the user accounts in the database — first log into your MySQL/MariaDB database as an administrative user using the mysql command line client (such as the root user), then run this MySQL query:

MySQL “users” FAQ: How do I add a new user to a MySQL or MariaDB database (i.e., how do I create a MySQL user account, and then grant database permissions to that new user account)?

MySQL database FAQ: How do I restore a MySQL backup? (Also written as, "How do I restore a mysqldump file?")

Assuming that you’ve created a MySQL backup file as shown in my “MySQL backup - How to Backup a MySQL database” tutorial, you can use the commands shown here to restore your MySQL database.

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

MySQL “create table” syntax/example

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 FAQ: How do I show (list) the tables in a MySQL (or MariaDB) database using the mysql command line client?

Solution: How to list MySQL/MariaDB tables

To list (or show) the tables in a MySQL database, follow these steps:

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.

MySQL DATE syntax FAQ: How do I set a MySQL DATE field to default to "now", i.e., the current time?

Setting the date to "now"

Unfortunately you can't default a MySQL DATE field to "now", but you can get the "now" behavior with a MySQL TIMESTAMP field. The syntax to create a MySQL TIMESTAMP "now" field is:

Here's a quick example of how to default a MySQL timestamp field to "now", i.e., the current date and time. The following MySQL table definition shows a field named date_created that uses the now function to record the timestamp for when the row was created:

MySQL login FAQ: How do I log into a MySQL database?

Solution

Assuming that you (a) have the root user password and (b) you are logging into a MySQL database on your local computer, this mysql command from your Unix/Linux command line is what you need:

mysql -u root -p

I may have shown this before in other ways, but I wanted to take a moment to show how to use a MySQL database from the MySQL command line client. The basic command to work with an existing database is the use command, where you say something like this:

mysql> use my_database

For instance, if you have a database named orders, you would declare that you want to start working with it (use it) like this:

MySQL database FAQ: How do I backup (back up) a MySQL database?

PHP MySQL socket error FAQ: I'm getting a MySQL error like "Warning: mysql_connect(): Can't connect to local MySQL server through socket '/home/lib/mysql/mysql.sock'" when trying to connect to my MySQL database from a PHP application, what can I do to fix this?

I just ran into this problem on a new server I'm using with A2 Hosting, where the exact error I got in my PHP MySQL program is this:

MySQL foreign key FAQ: How do I define a foreign key in MySQL?

Answer: Here's a quick example of how I typically define a foreign key in MySQL.

Diving right into an example, here's the definition for a MySQL database table named nodes that I will link to from a second table:

I'm not normally a big stored procedure user or developer, but since stored procedures are supported in MySQL 5.0 and beyond I thought I'd give them a spin. In this blog post I'll show you how to create a very simple MySQL stored procedure. It's not quite a "Hello, world" stored proc, but it's close.

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 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.

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

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.