Category: phpmyadmin

Reminder – its very easy to update your PHP Version in cpanel

After the recent news about the possible PHP exploit in existing versions remember if you are using CPanel to manage your estate it is very very easy to update the PHP version.

Check with your provider to ensure that they have made fixed PHP versions on your particular servers CPanel Account.
I would expect most have now.

Instructions from an old posting

CPanel Just gets better and better
If through cpanel you cannot see your exact PHP Version

Open cPanel → File Manager and go to your website’s document folder, usually public_html.

Create a file called php-version-check.php.

Edit it and paste:

<?php
header('Content-Type: text/plain');
echo 'PHP version: ' . PHP_VERSION;

Open in your browser
https://yourdomain.com/php-version-check.php

And you will be presented with the full PHP version number

Its not a problem if you leave that file there – there is a small chance someone could access and see the exact PHP version which can be used to target an attack to a particular version so it is good house keeping to delete it. But in of itself it is not anything but an information vulnerability.

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;