Removing unwanted posts from Stikked paste bin

Stikked is a pretty handy open source paste-bin application; that I’ve been using as a private paste-bin. It lets you set a time-out on a paste so as it cleans up after you’ve used the paste. Unfortunately occasionally I leave it set at ‘Keep-Forever’. It doesn’t have an admin interface or a delete option for the pastes, so over these unwanted pastes build up in the database.

I failed to find  a guide to delete posts from Stikked on the web, though there were people who had asked how they could remove unwanted posts. So I put this short guide together.

In the browser display the paste that you wish to delete. The paste id is shown at the end of the URL, make a note of the paste ID(s) you wish to remove. The paste IDs appear as an eight character hexadecimal string such as 8ae4ae1a.

Now we have the IDs we can log into the command line on the server and delete the pastes directly from the mysql database.

Log into mysql (you will need to have the root password or an account that has rights over the database).

$ mysql -u root -p

Change to the correct database

List the databases

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| paste              |
| performance_schema |
| phpmyadmin         |
| test               |
+--------------------+
7 rows in set (0.00 sec)

In this case the database was called ‘paste’. Switch to the correct database.

mysql> use paste;

Show the tables in the paste database

mysql> show tables;
+-----------------+
| Tables_in_paste |
+-----------------+
| blocked_ips     |
| ci_sessions     |
| pastes          |
| trending        |
+-----------------+
4 rows in set (0.00 sec)

Stikked has a relatively simple schema with only 4 tables only two of which relate to pastes.

Describe the pastes table

mysql> describe pastes;
+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| id           | int(10)             | NO   | PRI | NULL    | auto_increment |
| pid          | varchar(8)          | NO   | MUL | NULL    |                |
| title        | varchar(50)         | NO   |     | NULL    |                |
| name         | varchar(32)         | NO   |     | NULL    |                |
| lang         | varchar(32)         | NO   |     | NULL    |                |
| private      | tinyint(1)          | NO   | MUL | NULL    |                |
| raw          | longtext            | NO   |     | NULL    |                |
| created      | int(10)             | NO   | MUL | NULL    |                |
| expire       | int(10)             | NO   |     | 0       |                |
| toexpire     | tinyint(1) unsigned | NO   |     | 0       |                |
| snipurl      | varchar(64)         | NO   |     | 0       |                |
| replyto      | varchar(8)          | NO   | MUL | NULL    |                |
| ip_address   | varchar(16)         | YES  | MUL | NULL    |                |
| hits         | int(10)             | NO   | MUL | 0       |                |
| hits_updated | int(10)             | NO   | MUL | 0       |                |
+--------------+---------------------+------+-----+---------+----------------+
15 rows in set (0.00 sec)

The pastes table holds the actual pastes in the ‘raw’ field which is a longtext string. If we have the paste id from the URL we can select for that paste.

If you wish you can check that you have the right paste before deleting the item.

mysql> SELECT pid,title,name FROM pastes WHERE pid='8ae4ae1a';

If you need to see the content to be sure you can add some of the raw data (100 characters) to your query, this will probably not look very pretty as any return characters will disrupt the layout.

mysql> SELECT pid,title,name,left(raw,100) FROM pastes WHERE pid='8ae4ae1a';

Once you are sure that this is the post to delete execute the delete statement with the same ‘where’ clause.

mysql> DELETE FROM pastes WHERE pid='8ae4ae1a'

This will have removed your paste. To be tidy the entries for this paste in the trending table can also be removed.

mysql> DELETE FROM trending WHERE paste_id='8ae4ae1a';

If you have a list of items to remove you can use this alternate where clause, using the ‘in’ keyword.

mysql> DELETE FROM pastes WHERE pid IN  ('8ae4ae1a','<p-id2>',...,'<p-idn>');
mysql> DELETE FROM trending WHERE paste-id IN ('8ae4ae1a>','<p-id2>',...,'<p-idn>');

Quit mysql

mysql> quit

Other cases for deleting pastes would be where your paste bin has been spammed, in which case you could select pastes to delete based on the spammer’s ip_address. In this case you would need to tidy up the trending table with a more resource hungry where clause.

mysql> DELETE FROM trending WHERE NOT paste-id IN (SELECT pid FROM paste);

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>