Archive for the ‘ MySQL ’ Category

Find tables without primary keys

Some plugins create tables in MYSQL without primary keys which may break exporting and importing actions.

We can identify where exactly with this SQL script:

SELECT
t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES AS t
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k
ON (
t.TABLE_NAME = k.TABLE_NAME
AND k.CONSTRAINT_SCHEMA = t.TABLE_SCHEMA
AND k.constraint_name = 'PRIMARY'
)
WHERE
t.TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
AND t.TABLE_SCHEMA = 'database name' -- put database name here
AND k.constraint_name IS NULL;
2024
10/01
CATEGORY
MySQL
Plugins
Wordpress
Write comment

Missing primary keys in MySQL

Some hosting providers (like DigitalOcean’s DBaaS) are very strict about importing a database which contains missing primary keys.
So to locate where those tables are, we can run the following query:

SELECT
t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES AS t
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k
ON (
t.TABLE_NAME = k.TABLE_NAME
AND k.CONSTRAINT_SCHEMA = t.TABLE_SCHEMA
AND k.constraint_name = 'PRIMARY'
)
WHERE
t.TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
AND k.constraint_name IS NULL;

From here:
https://moiseevigor.github.io/programming/2015/02/17/find-all-tables-without-primary-key-in-mysql/

2024
07/05
CATEGORY
MySQL
Write comment

Change a WordPress user from editor to admin in PhpMyAdmin

A web consulting agency recently took over my admin account essentially locking me out of a website. Fortunately, if you have access to the database via a tool like PhpMyAdmin, you can easily get back in.

First open the wp_users table for the WordPress you wish to access. Look for your account and note down the user ID number:

user-ID

Note: If you don’t have an existing account, you will need to create a new one. (You can check this article on how to do it.)

Next, access the wp_usermeta table and look for your ID number.

In the meta_key field wp_capabilities,
add this meta_value: a:1:{s:13:"administrator";s:1:"1";}

Save and exit and you should now be an administrator again.

—

More info: https://themeisle.com/blog/new-wordpress-admin-user/

2022
11/22
CATEGORY
CMS
MySQL
Wordpress
Write comment

Assign MySQL Character Set for PHP output

For the old proceedings/programme pages of old events, we were seeing character errors like this:

Thomas L�we

So to fix this I introduced into the configuration.php file the following:

$db=mysql_connect('localhost','username','password') or mysql_die();
mysql_select_db('DBname',$db);
mysql_set_charset('utf8',$db);

—
PHP.net reference here.

2021
03/08
CATEGORY
Coding
MySQL
PHP
Write comment

Query another DB from within WordPress

Add this line to change the default DB to another one:

$wpdb = new wpdb('username','password','database','localhost');

2018
06/20
CATEGORY
CMS
Coding
MySQL
PHP
Wordpress
Write comment

MYSQL replication outbound links

https://www.digitalocean.com/community/tutorials/how-to-set-up-mysql-master-master-replication

https://www.rackspace.com/knowledge_center/article/set-up-mysql-master-slave-replication

https://blog.nexcess.net/2011/06/04/configuring-mysql-replication/

http://superuser.com/questions/820328/how-do-i-tell-if-mysql-replication-is-occurring-over-ssl

http://dev.mysql.com/doc/refman/5.6/en/replication-solutions-ssl.html

http://lowendbox.com/blog/getting-started-with-mysql-over-ssl/

http://fromdual.com/how_to_setup_mysql_master-slave_replication

http://ryanveach.com/?p=253

2016
06/01
CATEGORY
Linux
MySQL
TAGS



Write comment

WindEurope DB backup script

This is the WindEurope database backup script. It’s modified from the standard EWEA script because the DB server is a) not local and b) running on a non-standard port. Therefore we need to make a few changes to the mysql query. We run the script in the same way as usual.

#!/bin/bash
# db.sh
# Backup of database to offsite server
# Jason Bickley, Senior Web Manager
# Wind Europe
# 23 Jan 2016

#==== RECEIVE VARIABLES FROM COMMAND LINE ====#
FREQ=$1
SERVER=$2

#==== SCRIPT OPTIONS ====#
USER=backup
PASS="mwbubCEsxCU6XVsW"
DIR=/root/backup/mysql/
FILE=localhost.sql.gz
DEST=backup.ewea.org
DATE=$(date +"%Y%m%d %T")
LOG=/var/log/backupDB_log

#==== EXCUTION OF COMMANDS ====#
# Change operating directory
cd $DIR

#==== EXPORT DATABASE ====#
mysqldump -h192.168.0.113 --port=13306 -u$USER -p$PASS --all-databases --lock-all-tables | gzip > $SERVER.$FREQ.$FILE
chmod 600 $SERVER.$FREQ.$FILE

#==== RSYNC THE EXPORTED FILE TO OFFSITE SERVER ====#
rsync -aze "ssh -p 10022 -i /root/.ssh/"$SERVER"_prv_key" $SERVER.$FREQ.$FILE root@$DEST:/backups/$FREQ/$SERVER/db/

#==== DELETE THE LOCAL FILE ====#
rm -f $SERVER.$FREQ.$FILE

#==== CONFIRM SUCCESS IN LOG FILE ====#
echo $DATE Backup successful! $DEST:/backups/$FREQ/$SERVER/db/$SERVER.$FREQ.$FILE >> $LOG

To use it, you just have to run: db.sh {freq} {servername} {option}

cd /root/Scripts/backup/
./db.sh daily main
2016
05/26
CATEGORY
Bash
Commands
Linux
MySQL
TAGS





Write comment

Displaying content of a WordPress Form


<html>
<head>
<meta charset="utf-8" />
<style type="text/css">
* {font-family: "franklin gothic book"; font-size: small;}
h2 {font-size: 150%;}
table,td,th {border-collapse: collapse; border: 1px dotted #afafff; padding: 3px 5px;}
th {background: #efefff;}
</style>
</head>
<body>


<?php


// STATE YOUR VARIABLES
////////////////////////////////////////////////////////////////////
	$user  = "annual2015";
	$pass  = "tuYPLUY44DxjRCNB";
	$db    = "annual2015";
	$table = "wp_a15_fm_data_22";
	$sql   = "SELECT * FROM $table ORDER BY `timestamp` DESC LIMIT 0, 500";


// ESTABLISH THE CONNECTION
////////////////////////////////////////////////////////////////////
	$cxn=mysqli_connect("localhost","$user","$pass","$db");
	$cxn->set_charset("utf8");
	if (mysqli_connect_errno())
	{
	echo "Failed to connect to MySQL: " . mysqli_connect_error();
	}
	$result = mysqli_query($cxn,"$sql");


// GET NUMBER OF ATTENDEES
////////////////////////////////////////////////////////////////////
	$rows = mysqli_num_rows($result);
	printf("<h2>Current number of registered attendees: <span style=\"background: #ff0; font-size: 20px;\">%d</span></h2>",$rows);


// CREATE THE TABLE
////////////////////////////////////////////////////////////////////
echo "<table>
	<tr>
	<th>First name</th>
	<th>Last name</th>
	<th>Job title</th>
	<th>Organisation</th>
	<th>Email address</th>
	<!--　<th>Food requirements</th>　-->
	<th>Date registered</th>
	</tr>\n";
while($row = mysqli_fetch_array($result))
	{
	echo "	<tr>\n";
	echo "	<td>" . "<b>" . $row['text-56275de811d4e'] . "</b>" . "</td>\n";
	echo "	<td>" . "<b>" . $row['text-56275dea2cd60'] . "</b>" . "</td>\n";
	echo "	<td>" . $row['text-56275dfd39f14'] . "</td>\n";
	echo "	<td>" . $row['text-56275dfb697d7'] . "</td>\n";
	echo "	<td>" . "<a href=\"mailto:" .$row['text-56275df9b2872'] . "\">" .$row['text-56275df9b2872'] . "</a>" . "</td>\n";
//	echo "	<td>" . $row[''] . "</td>\n";
	echo "	<td>" . $row['timestamp'] . "</td>\n";
	echo "	</tr>\n";
	}
echo "</table>";


// CLOSE THE CONNECTION
////////////////////////////////////////////////////////////////////
mysqli_close($cxn);
?>
</body>
</html>
2015
10/27
CATEGORY
HTML
MySQL
PHP
Templates
Wordpress
Write comment

Convert UNIX time into other formats on the fly with MySQL

Typo3 and other CMSs often save dates in UNIX time, which is quite indecipherable by humans. Luckily MySQL has a built in function to convert that number into a human-readable format on the fly.

See the following SQL sample for extracting creation date and last login dates of Members’ Area accounts:

SELECT 
`uid`,
FROM_UNIXTIME(crdate,GET_FORMAT(DATE,'INTERNAL')) AS `DATE CREATED`,
FROM_UNIXTIME(lastlogin,GET_FORMAT(DATE,'INTERNAL')) AS `LAST LOGIN`,
`first_name` AS `FIRST NAME`,
`last_name` AS `LAST NAME`,
`username` AS `EMAIL`,
`company` AS `COMPANY`,
`disable` AS `DISABLED?`
FROM `fe_users`
ORDER BY `fe_users`.`crdate`  DESC
LIMIT 10000

More info:
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime

2015
08/03
CATEGORY
MySQL
Typo3
Write comment

Install MySQL 5.5 on CentOS/RHEL 6.5 and 5.10 via Yum

Redhat Enterprise Linux (RHEL) 5.x and CentOS 5.x have been providing MySQL 5.0 in it’s distribution since that was the active stable branch when RHEL 5.0 was released on 14th March 2007 (CentOS 5.0 on 12th April 2007). Since then, Sun/Oracle have released MySQL 5.1 and MySQL 5.5, so the Webtatic Yum repository has added them to it’s web stack, which now provides updated versions of all the AMP in LAMP.

Update 2014-03-01 – mysql55 packages have been renamed to mysql55w packages (as well as mysql51 to mysql51w). This is to ensure it doesn’t clash with SCL packages.

First, to set up the yum repository, install the webtatic-release RPM based on your CentOS/RHEL release:

Webtatic EL6 for CentOS/RHEL 6.x

rpm -Uvh http://mirror.webtatic.com/yum/el6/latest.rpm

If you already have MySql client or server installed (rpm -q mysql mysql-server), then you can upgrade using the following method:

yum install mysql.`uname -i` yum-plugin-replace
 yum replace mysql --replace-with mysql55w

“yum install mysql” is only there to make sure yum-plugin-replace can resolve dependencies correctly if only mysql-server was installed.

Otherwise, to install MySql client and server, then run:

yum install mysql55w mysql55w-server

You should upgrade existing tables before setting the server to become a production machine, which can be done by starting the server and running the mysql_upgrade script (this may take time depending on the size of the database).

service mysqld start

# This will issue a password prompt for the user. If you don’t have a root user password, remove the “-p”
mysql_upgrade -u root -p

MySQL 5.1

The Webtatic 5 repository for CentOS/RHEL 5.x includes mysql51-* packages for installing MySQL 5.1. This can be installed by replacing in the above commands “mysql55w” with “mysql51w”.

As MySQL 5.1 is standard in CentOS/RHEL 6.x, it is not included in the Webtatic EL 6 repository.

2014
09/12
CATEGORY
Linux
MySQL
Write comment