Fixing Auto Increment MySQL Errors (for WordPress)

The problem:

  • You can’t update your settings in WordPress.
  • You’re unable to add new posts or upload media.

Likely cause:

AUTO_INCREMENT is disabled in the MySQL table structure.

What is AUTO_INCREMENT and why is it important?

WordPress relies on numbers to save and retrieve settings from tables stored in the database. These are called option_id in the options table structure, and ID in the posts table structure. Without AUTO_INCREMENT, WordPress will not be able to determine the id number for new items sequentially.

How did AUTO_INCREMENT get disabled in the first place?

I have encountered this problem when changing hosting providers, and more specifically, when I export a database to another MySQL server. It also seems to happen when the domain name changes.

So what’s the fix?

You could try enabling auto increment in phpMyAdmin. Make sure you backup your database before implementing this solution.

Step 1:

Go to the options table of your WordPress install and click on the structure tab. Notice that AUTO_INCREMENT is not shown in the Extra column for the option_id row. Find “option_id” and click “Change.”

Tick the A_I checkbox and click “Save.” There’s nothing more you need to do if you did not see any errors. Otherwise, move on to step 2 if your screen looks like this:

Step 2:

Select all items listed in the table and click “Export.”

Leave the file format as SQL and click “Go.” Your web browser will download the SQL file to computer.

Step 3:

Open the SQL file in a plain text editor. Make sure you have something similar to the following code in the beginning of your script:

--
-- Database: `wordpress`
--

-- --------------------------------------------------------

--
-- Table structure for table `wp_options`
--

CREATE TABLE `wp_options` (
  `option_id` bigint(20) UNSIGNED NOT NULL,
  `option_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `option_value` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `autoload` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'yes'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

This code tells MySQL to create a table called “wp_options” in a database called “wordpress” at import. Chances are your database name and WordPress prefix are different from this example, so I do not recommend copying and pasting this as is.

Step 4:

Scroll to the very bottom of the script. You should see something similar to this:

(475, 'fv_player_popups', 'a:1:{i:1;a:4:{s:4:\"name\";s:0:\"\";s:4:\"html\";s:0:\"\";s:3:\"css\";s:0:\"\";s:8:\"disabled\";s:1:\"0\";}}', 'yes');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `wp_options`
--
ALTER TABLE `wp_options`
  ADD PRIMARY KEY (`option_id`),
  ADD UNIQUE KEY `option_name` (`option_name`),
  ADD KEY `autoload` (`autoload`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Notice the number in the first line. That number corresponds to the last option_id on the list. Your number will be different depending on the number of settings you have saved from your WordPress installation. Make note of it.

Step 5:

Copy and paste the following code at the absolute bottom of the SQL file:

--
-- AUTO_INCREMENT for table `wp_options`
--
ALTER TABLE `wp_options`
  MODIFY `option_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=480;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Including this code will tell MySQL to enable auto increment for the table called “wp_options.” Change the prefix so it matches with what’s already there.

Example from a different database repair.

Step 6:

Find…

AUTO_INCREMENT=480;

Remember the option_id number from the last item on your options table? Mine was 475, so I went with 480 to avoid problems and because it’s a nice round number. Pick a number that’s greater than the last option_id number on your list. Save and close.

Step 7:

Login to phpMyAdmin and drop the options table from your database.

Import the SQL file that you’ve just modified. You’ll know the import was a success if phpMyAdmin populates your screen with green confirmation boxes.

Step 8:

Check the options table to see if AUTO_INCREMENT has been enabled.

You should now be able to make changes to your WordPress installation. Happy blogging.

Converting MP4 to Encrypted HLS Video with OpenSSL and FFmpeg

HLS, which is a video streaming technology developed by Apple Inc., is not a video file in of itself. Rather, HLS consists of a single m3u8 file that lists video file chunks. My tutorial on using HLS for video on demand provides a more detailed explanation on how this technology works and how it can be used for digital rights management (DRM). I suggest you give it a read.

In that tutorial, I mentioned that the m3u8 file is a playlist. What I didn’t mention is that it’s possible to download this file and open it with a plain text editor. Doing so will reveal the location of the .ts video file chunks, which are usually found in the same path as the m3u8 playlist. A would-be pirate can use the information found in the m3u8 file to download each .ts file chunk; however that can be become tedious process assuming there are hundred of chunks. I have already thought of a ways to automate the download process and subsequently play the video in its entirety with VLC, but there are extra steps content creators and webmasters can take to deter unauthorized video downloads.

You will need terminal access to a machine with OpenSSL and FFmpeg already installed. You’ll also need a streaming server.

The first thing we need to do is generate a key file:

openssl rand 16 > keep_this_file_safe.key

You can name this file whatever you want, but I would keep the .key extension.

Now we need to create a .keyinfo file. FFmpeg will use this text file to convert the MP4 to encrypted HLS:

echo http://192.168.0.101/keys/keep_this_file_safe.key > my.keyinfo

This will create a new file called my.keyinfo with first line specifying the URL of your key file on the web server. You will have to upload the key file to that location so the encrypted video is playable. Change the URL according to your setup.

echo /path/to/keep_this_file_safe.key >> my.keyinfo

Running this command will add a second line to my.keyinfo that specifies where the key file is actually located in your Linux box. This is what FFmpeg will actually look for when encrypting the .ts file chunks.

Next, we’ll need a call back to the key file:

echo $(openssl rand -hex 16) >> my.keyinfo

Your .keyinfo file should look something like this:

http://192.168.0.101/keys/keep_this_file_safe.key
keep_this_file_safe.key
0123456789ABCDEF9876543210ABCDEF

Now it’s time to generate the encrypted HLS files with FFmpeg. Adjust the length of your .ts files and the video resolution accordingly before running this command:

ffmpeg -i my_video.mp4 -profile:v baseline -level 3.0 -s 1280x720 -start_number 0 -hls_time 10 -hls_list_size 0 -hls_key_info_file my.keyinfo -f hls my_video.m3u8

The time it takes to generate your HLS content will depend on the processing power of your machine. Upon completion, you will have HLS video content that cannot be played unless the key file is in the location specified in the first line of the keyinfo file.

Upload your .key file and encrypted HLS video content to a streaming server and you’re good to go.

Make sure you implement some form of security for the .key file so it is only accessible when needed for video playback. This can be accomplished with .htaccess, but that’s beyond the scope of this tutorial.

VOD (Video on Demand) using HLS

HLS is great for live streams, but can it be used for VOD? Yeah, I know RTMP (Real-Time Messaging Protocol) already serves this purpose. It can also handle live streams, but the problem with RTMP is that viewers must have an Adobe Flash plugin running on their web browsers in order to watch your video content. That’s a problem considering Adobe Flash will eventually become obsolete.

Why use HLS or RTMP for video on demand when it’s easier to upload video files onto any web server and point the media player to the URL?

For starters, I’m big on digital rights management (DRM). There are extensions you can add to Chrome or Firefox that will allow you to download audio and video content when the owner has not made direct links to the content available. Savvier users can take advantage of built-in inspector tools to pinpoint the exact URL of your media.

RTMP and HLS obfuscates the location of your video files, making it significantly more difficult for would-be pirates to rip your content. At the end of the day, a determined pirate will get a copy of the video content you post on the Internet. With that in mind, it’s better to not post a video online if you don’t want to share it with the world.

So, how can you play an mp4 video file through HLS? The easy answer is you don’t. You have to take that mp4, convert it into TS file chunks, and compile a playlist in m3u8 format using FFmpeg. This tutorial assumes that you have access to an NGINX web streaming server and that you’re running macOS. These steps can also be adapted in Windows and Linux.

  • Launch the Terminal app in macOS, which can be found under Applications > Utilities
  • macOS doesn’t come with a command-based package manager installed under the hood. For that, you’ll need Homebrew. Copy and paste the following command in Terminal, and enjoy a cup of coffee or a glass of your favorite beer while the script runs:
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"
  • Install FFmpeg:
brew install ffmpeg
  • You’ll need an mp4 to work with. Let’s create a folder called “hls” on your desktop where you’ll download an mp4 to convert.
cd Desktop
mkdir hls
brew install wget
wget http://vod.leasewebcdn.com/bbb.mp4

I know, you could have simply created a folder on your desktop with the same name and downloaded this mp4 to that folder. These commands are for those who are running headless servers, and had I not given this a go I wouldn’t have learned that Wget is also not installed out of the box in macOS. Most Linux distributions, such as Ubuntu or Debian, will come with Wget. The install command will vary depending on which Linux distro you’re running.

  • Now it’s time for the fun part. Let’s take that mp4 and convert it so it’s playable with HLS. Navigate to the hls folder in Terminal and run FFmpeg:
cd hls
ffmpeg -i bbb.mp4 -profile:v baseline -level 3.0 -s 640x360 -start_number 0 -hls_time 10 -hls_list_size 0 -f hls bbb.m3u8

FFmpeg will break bbb.mp4 into small TS file chunks at around 10 seconds each with a resolution of 640×360. The conversion process is CPU intensive and the amount of time this will take depends on your machine’s processing power. Also keep in mind that the m3u8 file is not a video file. Think of it as a playlist or set of instructions for the video player to follow.

  • Check the hls folder to see if your hls files have been created. The terminal command for that is:
ls

You should see a long list of numbered files ending with the .ts extension and a file called bbb.m3u8 – you can delete the original mp4 if you do not want to keep it.

rm bbb.mp4
  • Now it’s time to upload your HLS files to the streaming server. Again, you’ll need access to a streaming server running NGINX with the RTMP module installed for this to work.
  • Set ownership and permissions so the path is accessible:
chown -R www-data:www-data /real/path/to/video/files
chmod -R 0755 /real/path/to/video/files

http(s)://<IP address>:<Port number>/<Application>/bbb.m3u8

http(s)://This will be http or https if SSL is enabled.
<IP address>You can also use a domain name if configured.
:<Port number>There’s no need for this if using TCP port 80.
/<Application>/Not a directory. This is set in an NGINX config file like so:
rtmp {
     server {
          application vod {
               hls on;
               play /real/path/to/video/files;
          }
     }
}

Here are some examples of what the URL might look like based on the above configuration:

http://192.168.0.12/vod/bbb.m3u8

In this example, the actual location of bbb.m3u8 and the TS files is /real/path/to/video/files

http://192.168.0.12/vod/cartoon/bbb.m3u8

Your m3u8 and TS files are located in /real/path/to/video/files/cartoon

Set the ownership to www-data and permissions to 0755 for sub directories recursively if you cannot playback content.

Use VLC, or any media player that’s capable of playing videos over a network to view your content. You could also right-click on the bbb.m3u8 in Finder on macOS and open it with VLC to view it locally.