MySql WordPress Issue Primer Tutorial
The WordPress Blog uses MySql as its database and PHP as its language. A great interface between these two worlds is the stupendous, the marvellous, the life-saving, phpMyAdmin, an interface into the MySql database written in PHP. You will see it in action a lot if you administer a WordPress Blog, and it was used during MySql Repair Table Primer Tutorial as shown below.
Today we talk about the same issue, that is, what you do when a WordPress Blog administration piece of functionality is not behaving as expected.
The issue, on this occasion, was, while maintaining comments in the WordPress Blog we were receiving the error message “No comments awaiting moderation… yet.” (and say error here because we happened to know there were comments awaiting moderation (doh! if you have just started your blog) … the approach of “sticking your head in the sand and hoping it will go away” (ie. SYHITSAHIWGO) doesn’t often work with database work … though, that being said, users can create unrealistic queries that can cause issues … or things can become too busy and so have a read of watchdog blog postings here):
No comments awaiting moderation… yet.
-
First thing about resolving such an issue is … have a look to see whether the error is logged in the WordPress Blog error_log file, which sits in the WordPress Blog (sub)domain’s document root directory.
-
In our case there was nothing to note, but often there is, and often you get ideas about how to start resolving the issue from the error message.
-
Next approach can be to submit a query to a search engine … such as (ie. often put exact error message in quotes) … like this.
-
For our case this lead to this excellent link (this resolving advice we’ll talk about more below) … but should such a search lead to nothing concrete, for WordPress, it can be good to ask a question on the WordPress Forum.
-
Login to phpMyAdmin in your usual way … mine is via cPanel (WHM), but you will have been given advice on this, if you are allowed to do this …
-
Click the relevant _wordpress database link at the left
-
It could be good, as the forum posting says, to back up your _wordpress database before proceeding further … you’ll see some Export options in the menu links up the top.
-
Click the wp_comments table link at the left
-
Click the Structure tab link
-
Click the left hand check box for field comment_date_gmt
-
Click Pencil to edit
-
Select Add Index from the More dropdown at the right … effectively performing the MySql sql …
ALTER TABLE `wp_comments` ADD INDEX ( `comment_date_gmt` ) -
On a completion with no errors, retry what you were doing in the first place … in my case everything got fixed … yay!
-
The topic of indexes relating to relational databases is a huge topic area, and if new to the concept would recommend the Wikipedia links database indexes link or further back to relational database link.
Did you know?
WordPress Blog error messages often relate to the underlying MySql database, and can often talk about slowness … for instance, a message like …
[26-Nov-2014 08:45:00 UTC] WordPress database error MySQL server has gone away for query SELECT DISTINCT MONTH(post_date) AS month, YEAR(post_date) AS year
FROM wp_posts
WHERE post_date < '2014-11-01'
AND post_type = 'post' AND post_status = 'publish'
ORDER BY post_date DESC
LIMIT 1 made by require, require_once, include, get_sidebar, locate_template, load_template, require_once, dynamic_sidebar, call_user_func_array, WP_Widget->display_callback, WP_Widget_Calendar->widget, get_calendar
… relates to database slowness because the web server did not complete the query in the designated time period to stop that query being classed as a slow query (this slow query time is configurable).
Previous relevant MySql Repair Table Primer Tutorial is shown below.
MySql Repair Table Primer Tutorial
Today’s tutorial continues the MySql (command line) discussion last heard about with MySql Stored Procedures Primer Tutorial as shown below, to illustrate that MySql has functionality to repair database tables, if they run into trouble.
When you troubleshoot a problem regarding an Apache/PHP/MySql website you can tend to think, firstly, when a bit of functionality is not working (when it has been working fine for a while now) that the problem is with the software code being unable to cater with some unusual situation.
The Apache log may get you closer to discovering the problem, but you may get to know some troubleshooting shortcuts, to rule out the database, or not, by checking the relevant database table using MySql from the command line, or the brilliant phpMyAdmin, as a GUI interface to the database.
Not always, but sometimes, you can be surprised to find that MySql database tables do have problems from time to time, and that MySql marks these tables as “crashed”, as you can see in today’s MySql Repair Table Primer Tutorial.
The functionality shown in the tutorial regarding phpMyAdmin MySql statement REPAIR TABLE [tableName] and the two MySql command line mysqlcheck or, if MyISAM format, myisamchk choices are not guaranteed to fix the problem all the time, and sometimes it can be to do with the size of the table involved, or with the size of the temp area set aside on your server. When a REPAIR TABLE operation runs too long, in phpMyAdmin the table will be still marked as crashed and you may see the string “(in use)” shown in the Collation column of the table screen. In such cases you can:
- continue waiting for repair process to finish … or …
- use a restore operation to restore the table (that you have backed up recently) … or … (the next two options lose data, and so should only be done when you know the table is independent of others) …
- truncate the table with the phpMyAdmin “empty” icon (this may not work for a while, but becomes an option as time goes on) … or …
- drop the table (then recreate it) with the phpMyAdmin “drop” icon (this may not work for a while, but becomes an option as time goes on)
Previous relevant MySql on Command Line Primer Tutorial is shown below.
MySql on Command Line Primer Tutorial
Today’s tutorial follows up on MySql Stored Procedures Primer Tutorial as shown below, to illustrate that MySql database (and PHP server-side language) are friends of the command line, and you can use all these tools in batch processing, even though you most often associate PHP and MySql with interactive web use (or maybe you don’t?!).
This blog talks a fair bit about batch processing and command line work, not so much from the point of view of nostalgia, but more from the point of view that lots of computer software functionality can still be modelled on a procedural set of tasks which can be planned so that interactivity stops at a certain point, and then you should let loose the power of the computer to actually do things (the joy of batch processing). Do enough batch processing, and you feel what you feel like when you have trodden softly on this earth.
Anyway, enough of the rave, but with the tutorial we interactively, on our local webserver’s MAMP environment, create a new database called testdb, use the previous tutorial’s code to create some MySql tables and stored procedures in that database, so that we have one record in each of our two tables that we show you via phpMyAdmin (in an interactive way) and via a Mac OS X laptop’s Terminal application’s Linux bash environment command line (via the mysql program in a Linux command line environment).
This example is simple, but you build off the simple, and imagine the big, and imagine the procedural, and imagine the compartmentalization of tasks … if you want to have fun that is … the bungee jumpers are all just faking it!
So today, as well as with the previously relevant tutorial we used (MySql) stored procedures, and we’ll let Wikipedia explain this important database tool below.
A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure (sometimes called a proc, sproc, StoPro, StoredProc, sp or SP) is actually stored in the database data dictionary.
Typical use for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures can consolidate and centralize logic that was originally implemented in applications. Extensive or complex processing that requires execution of several SQL statements is moved into stored procedures, and all applications call the procedures. One can use nested stored procedures by executing one stored procedure from within another.
Stored procedures are similar to user-defined functions (UDFs). The major difference is that UDFs can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement.[1]
Thanks to Wikipedia for the quote above, and would recommend that if you want to get the most out of what is possible with databases, like MySql you also study triggers, and for that will leave you with a Wikipedia entry about this topic. Happy databasing!
Previous relevant MySql Stored Procedures Primer Tutorial is shown below.
MySql Stored Procedures Primer Tutorial
Today’s tutorial follows up on phpMyAdmin interface to MySql and PHP Primer Tutorial in that we again use the brilliant phpMyAdmin to oversee the results of some PHP code which uses MySql calls to create tables called POINT and CIRCLE used to store information defining a circle, and then it creates three MySql stored procedures in the database to help add circle data with the single MySql statement that goes CALL AddCircle(x, y, radius); via the use of those stored procedures. Let’s see below what Wikipedia says about Stored Procedures generally.
A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure (sometimes called a proc, sproc, StoPro, StoredProc, sp or SP) is actually stored in the database data dictionary.
Typical use for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures can consolidate and centralize logic that was originally implemented in applications. Extensive or complex processing that requires execution of several SQL statements is moved into stored procedures, and all applications call the procedures. One can use nested stored procedures by executing one stored procedure from within another.
Stored procedures are similar to user-defined functions (UDFs). The major difference is that UDFs can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement.[1]
Here is some downloadable PHP programming source code which shows the results of the MySql SQL requests made and can be renamed to ourmysqlstoredprocedure.php as required.
Here is some downloadable supervisory PHP programming source code which gathers the MySql SQL requests made and can be renamed to ourmysql_storedprocedure.php as required.
If you want to develop your own live usage (have provided a live usage link here which will not work but you can use if you are a beginner, to get used to mysql errors, which will occur after you hit either button of the link (these errors indicate bad MySql connection details which are the same reason the database dropdown is not full of options), or you can use this link to see the raw MySql SQL involved in piecing this tutorial together) of these two PHP source codes then you could fix up the hard codings for MySql host/username/password up the top of ourmysql_storeprocedure.php (where you may notice that the default host in the code is localhost:8889 which is the default host string for MySql (ie. port 8889 is used) when using a MAMP (Mac laptop) local web server (which uses localhost:8888 as its local “domain name” for http usage)) or you can keep the same code and use a URL like:
[your-domain-name-plus-a-bit-maybe]/ourmysql_storeprocedure.php?host=[your MySql host address]&username=[your MySql username]&password=[your MySql password]&database=[your optional MySql default database name within the looked up list presented]
Previous phpMyAdmin interface to MySql and PHP Primer Tutorial is relevant and shown below.
phpMyAdmin interface to MySql and PHP Primer Tutorial
Transcript:
You never hear much about the data when you hear about great PHP products, but we
all know it is the data that differentiates the quality of the end result.
That is probably because database products like MySql, SqlServer, Oracle SQL, Advantage and Access
are pretty good at what they do, and emphasise reliability rather than flashiness.
MySql and PHP have a great open source interface with phpMyAdmin, which is so good,
you forget that it is not the default MySql administrator’s interface product.
Let’s have a look at this WordPress database and a bit of how it looks, looking
through the prism of phpMyAdmin …
If this was interesting you may be interested in this too.
If this was interesting you may be interested in this too.
If this was interesting you may be interested in this too.
If this was interesting you may be interested in this too.
If this was interesting you may be interested in this too.
