Posts in the “mysql” category
A MySQL “create table” syntax example
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 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.
MySQL backups: How to backup a MySQL database
MySQL database FAQ: How do I backup (back up) a MySQL database?
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:
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.
The PHP MySQL mysql_connect() can't connect to local MySQL server through socket error
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 examples (How to define foreign keys in MySQL)
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:
A MySQL stored procedure example
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 restore: How to restore a MySQL database from a backup
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 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.
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
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: