Digests extension update: new manually run the mailer interface

The new digests extension will include an ACP interface that will allow the admin to manually run digests. Previously this had to be done by specifying a URL. Mostly it is for initial testing of digests or for occasional troubleshooting. However, you will also use it to manually send digests for days and hours that might have been missed, such as when the cron job was down, using the option to create digests in the past. I have the front end done and am working on the backend code. Take a look:

Manually run digests mailer
Manually run digests mailer

This option will also let you write digests to files on your server to see them via a URL as HTML or text. They will hide in the extension’s cache folder. This potentially allows a static digest to be created showing, say, all public posts on the forum for a particular day or week. Such an interface might get more thoroughly thought through later. Right now this is mostly for testing and troubleshooting. Email clients can ignore HTML markup, but by writing digests to a file you can see it in a browser. Of course writing it to the extension’s cache folder also makes potentially private content publicly accessible, which is why the interface will have a way to clear this cache folder too.

On the backend, since mailing will be done via phpBB’s cron system, mail_digests.php goes away as a standalone program. Instead it will be invoked through a run method on a mailer class for the extension, which will hide in the extension’s cron/task folder.

Digests extension user control panel interface developed

I have replicated the digests modification user control panel interface for my digests extension under development. It all looks and behaves the same as it does for the modification. Fortunately a lot of the code could be copied and pasted with some changes for the new architecture. The hardest part was creating the interface in the first place, as the methods for doing so are not well documented. I spent a lot of time on issues like getting form keys to show as hidden fields. Fortunately, the people contributing to the phpBB extension writers forum were of great help. A snapshot of the Forums Selection screen is shown below.

Forums selection, Digests extension UCP interface
Forums selection, Digests extension UCP interface

With the Administration Control Panel digests extension user interface also complete, the next step is to port mail_digests.php, which as the name implies actually sends the digest. This will include challenges too including:

  • I will need to extend the mailer class to allow HTML formatted emails. I’ve never done this before.
  • It’s not advisable to print raw text to the screen anymore, so I’ll need to develop some sort of template that shows progress as digests are created and mailed.  Most of the time this won’t be seen as digests are run automatically.
  • Add logic to hook mail_digests into phpBB’s cron structure, as it will kick off digests.

So it’s fairly challenging work but a lot of the code can be ported. It’s the stuff above that will consume most of the time and puzzling.

Changing your phpBB tables’ storage engine

Note: this post was updated October 17, 2019.

Most phpBB forums use the MySQL database to hold the forum’s data. It’s an obvious choice because community editions are free and it comes bundled with your hosting package. If your database is MariaDB, don’t worry. These instructions work for MariaDB too. Why? It’s because MariaDB is a clone of an earlier community edition of MySQL. Functionally, it is virtually identical.

When MySQL was first created, there were few storage engines available. Storage engines contain the code (software) and internal data structure for storing data in tables. Consequently unless you have created a new forum it’s likely that your forum’s tables use the old MYISAM storage engine.

With phpBB 3.1, the default storage engine for phpBB tables using MySQL and MariaDB has changed from MYISAM to INNODB. However, the conversion program from 3.0 to 3.1 will not change the storage engine. INNODB is a different way of storing the data in a table. It’s generally more self sufficient than MYISAM and you are less likely to need to repair it. INNODB tables are best used for tables that are frequently read and written to. In fact, the REPAIR command does not work on tables using the INNODB engine.

I generally recommend to my clients to change their tables’ storage engines to INNODB because they are less likely to have issues. I see clients that struggle through issues like the sessions table needs to be repaired and they don’t know how to do it. Sometimes tables using MYISAM just get inconsistent leading to issues such as orphaned posts.

phpMyAdmin is a tool generally provided by your web host in its control panel. While you can change your storage engine in phpMyAdmin, it’s not intuitive. There is nothing in the software that allows you to do this in the user interface. You can do it on the SQL tab, however. You can use phpMyAdmin to change your storage engine but you can also do it from SSH with the right privileges by running the mysql command. These instructions assume phpMyAdmin is used.

A few caveats:

  • Backup your database first.
  • You should disable your forum during the process. Users might get error messages or timeouts while this is happening.
  • The more posts and users you have the longer it will take. Generally it takes only a few minutes. It’s possible that phpMyAdmin will time out. In this case go back in when you can, see which tables have been converted and convert any remaining tables. Simply look at the tables for your forum’s database and examine the TYPE column to see which tables remain to be done, which are those where the type still shows MyISAM.
  • If your sessions table uses a MEMORY storage engine, this is fine. You might want to use MEMORY for the sessions table only because the sessions table is accessed very frequently. The MEMORY storage engine very fast because nothing is stored to a file, but if the database server crashes or is rebooted all the rows in MEMORY tables are lost. This is okay in the case of the sessions table. It just means that people have to log in again.

These instructions assume you are using phpMyAdmin.

  1. Look at your config.php file for your forum. You can download it with FTP or you can view it in your web host control panel’s file manager. It should look something like this:
// phpBB 3.2.x auto-generated configuration file
// Do not change anything in this file!
$dbms = 'phpbb\\db\\driver\\mysqli';
$dbhost = 'localhost';
$dbport = '';
$dbname = 'forum_database';
$dbuser = 'forum_admin';
$dbpasswd = 'forum_password';
$table_prefix = 'phpbb_';
$phpbb_adm_relative_path = 'adm/';
$acm_type = 'phpbb\\cache\\driver\\file';

@define('PHPBB_INSTALLED', true);
// @define('PHPBB_DISPLAY_LOAD_TIME', true);
@define('PHPBB_ENVIRONMENT', 'production');
// @define('DEBUG_CONTAINER', true);
  1. Make a note of the value for $dbname
  2. Log into phpMyAdmin
  3. Select the database containing your forum, which is the same as the value of $dbname.
  4. You can see your tables’ storage engine in the TYPE column. If you are already using INNODB for all your tables there is no point in doing anything.
  5. Click on the SQL tab.
  6. Copy and paste the SQL below into the window. Replace the value in quotes for dbname with the value of the variable $dbname in your config.php file. The result of the query is a bit strange, because it renders SQL. You will use the generated SQL to actually change the storage engine.
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'dbname' AND engine = 'MyISAM'
  1. It will render a list like this. In most cases the tables will start with “phpbb_” but if it doesn’t don’t worry. The tables should start with the value of $table_prefix in your config.php file. Note that what the previous query does is construct the SQL you need to change the storage engines, but only where the current engine is MYISAM. Only a few rows are shown for demonstration purposes.
ALTER TABLE phpbb_config_text ENGINE=INNODB; 
  1. Check the Show All checkbox if it is there to see all the rows.
  2. In phpMyAdmin at the bottom of the screen look for a link Copy to clipboard. Click on the link and all the generated SQL will be copied to your clipboard.
  3. Open a text editor and paste it into a new document. Remove any lines before the first ALTER statement. If there are any statements where the table name does not start with the value of $table_prefix, you might want to edit these out because these tables are probably not used by phpBB.
  4. Disable your forum while doing these changes: ACP > General > Board configuration > Board settings > Disable Board > Yes
  5. While you could just paste all these statements into the SQL window in phpMyAdmin again for your database, this is not recommended unless your forum is very small in size. Instead, it’s recommended that you do a block of these at a time, say ten statements per block. If there are no errors, then do the next block until all are done. For very large tables like the phpbb_posts table or the phpbb_search_wordmatch table, it is recommended that you do these individually. Each line that is executed should change the storage engine for the table from MyISAM to InnoDB.
  6. When all done, you can verify that the table storage engines have changed by clicking on the link for the database in phpMyAdmin and examining the TYPE column again. They should all report InnoDB.
  7. Reenable your forum: ACP > General > Board configuration > Board settings > Disable Board > No
  8. You don’t need to save the SQL in your editor.

That’s it! Enjoy!

November 2015 work summary

Consulting slowed a bit this month, which was good in a way. It allowed me to work on a beta version of my Smartfeed extension and toward the end of the month I started work on the Digests extension. As always, client information is anonymized. Here’s some of the phpBB work I did in November:

  • I continued ad hoc work for an existing client during the month. I enabled the Windows media extension group so various Windows media would play. First I had to fix another SQLServer CAST bug in phpBB that manifested when enabling the new extension group. The basic issue here is that the client uploads inhouse videos and attaches them to posts, but each video is very large (megabytes). phpBB keeps a configuration variable containing the size of all the items in the files directory. When this value is stored, SQLServer (the client’s database) can’t handle it unless the value is CAST as an integer using the SQL CAST function, which required a code patch that I added. I reported the additional code that is needed as an issue in the phpBB bug tracker. I also participated in an email discussion on the most efficient way to embed videos on the forum and recommended a HTML 5 approach (upload .mp4 files), which phpBB does not currently support. phpBB is behind on integrating HTML 5 videos smartly. I added this issue in the phpBB bug tracker. The template attachment.html needs to be updated to allow the browser to natively play .mp4 videos inside the new HTML 5 <video> tag so no plug in is required. Right now the Quicktime player is loaded, which means it has to be added as a browser plug in, and this is slow and doesn’t usually work on mobile devices. Also provided guidance on Google Analytics: getting an ID for tracking and setting it up. I answered a question about why a user was not able to download an attachment. I also monitored and replied to posts on their forum.
  • I converted a forum from 3.0.12 to 3.1.6 using the prosilver style. I installed the Tapatalk extension and installed and configured the Advertising Management extension, which required renaming a table to port ads from the 3.0 mod to a place where the extension could find them.
  • For one client I installed a new logo with an orange theme. Later in the month I placed a new ad for him and put it above posts in the view topic page. Later in the month I replaced the content of an ad. I investigated issue of a missing user. Apparently his account was deleted. I coached customer on how to find a good web host. The web host saw bad data in private message table in the message_text column. I repaired the table but when it recurred I then changed the storage engine for all tables except the sessions table set to memory to INNODB. So far this is working.
  • I upgraded a forum from 3.0.11 to 3.1.6. I installed the subsilver2 style and made it the default. I integrated the old logo. I provided guidance on using phpBB’s built in ATOM feed and suggested that the user use it to dynamically serve forum topics on his main web site. I provided a link to a phpBB knowledge base article on using phpBB’s feed and showed him some WordPress plug ins that had a feed widget that could consume the feed.
  • I upgraded forum from 3.0.9 to 3.1.6, using prosilver as the default style. Customer agreed to handle the styling issues. When I ran database_update.php, I encountered lots of errors that I had to address, the origins of which are unclear, which meant fixing data and trying again. To get a clean conversion I eventually emptied the migrations table. I still had to do some things manually. This included commenting out code to move the prune users module and emptying then recreating the data in the modules table because module names were not correct. Later in the month the user reported some more issues. After investigation I determined that an acl_option permission that the converter should have added must have missed. Without it a user could not change their profile. I added it and assigned it to 5 roles per my reference database and that solved that problem. A couple of days later another permission issue was discovered when the extensions tab would not appear in the Administration Control Panel. This was also due to a missing permission that I had to add manually. Then the tab reappeared. I double checked all the entries in the configuration table and all the permissions to make sure they were all there compared to a reference. They appear to be so I think there won’t be future problems like this.
  • I placed an additional ad for a client in the Advertising Management extension.
  • To address a serious spam problem, I installed the Cleantalk mod on phpBB 3.0.12. Client must pay $8 for 1 year of service.
  • A HTTP 500 error occurred only on registration. This was due to a bad configuration value for the CAPTCHA (wasn’t changed in the migration from 3.0 to 3.1 and needed the namespace). Post counts were also zeroed on the index page. I manually fixed index to show correct # of posts, last post time and last poster using various SQL queries. I added missing columns in forums table and missing rows in config table that phpBB 3.1 needs. I checked all the tables with a reference to make sure all columns were present for 3.1.
  • After rehosting mysterious HTTP 500 errors were generated on the new host and there was nothing in the error log to provide any clues about what went wrong. I eventually figured out that PHP was not compiled with an integration with the MySQL database. To connect PHP and MySQL, I used Unix yum command with the right integration package and the forum started working. Had to use SSH to do this work as the client had his own server and acted as its tech.
  • A user who upgraded his board for some reason was unable to create a new forum inside it. After investigating, I determined that a column in the forums table did not have a default value. I gave it a null value as a default. Once things were stable I upgraded the forum from 3.1.5 to 3.1.6.
  • I upgraded forum from 3.0.12 to 3.1.6. The old forum had lots of mods but none were critical. Changed style with upgrade from subsilver2 to prosilver.