Posts Tagged ‘binary data’

Manually deleting spam comments from WordPress blogs and websites to free disk space and optimize MySQL

Monday, November 24th, 2014

Reading Time: 3minutes

WordPress-delete_spam_comments_manually_with_sql_query_to-optimize_mysql-and-free-disk-space
If you're a web-hosting company or a web-development using WordPress to build multitudes of customer blogs or just an independent blogger or sys-admin with a task to optimize a server's MySQL allocated storage  / performance on triads of WordPress-es a a good tip that would help is toremoving wp_comments marked as spam.

Even though sites might be protected of thousands of spam message daily caught by WP anti-spam plugin Akismet, spam caught messages aer forwarder by Akismet to WP's Spam filter and kept wp_comments table with comments_approved column  record 'spam'.

Therefore you will certainly gain of freeing disk space uselessly allocated by spam messages into current MySQL server storage dir (/var/lib/mysql   /usr/local/mysql/data – the directory where my.cnf tells the server to keep its binary data.MYI, .MYD, .frm files) as well as save a lot of disk space by excluding the useless spam messages from SQL daily backup archives.

Here is how to remove manually spam comments from a WordPress blog under database (wp_blog1);

mysql> use wp_blog1;
mysql> describe wp_comments;
+———————-+———————+——+—–+———————+—————-+
| Field | Type | Null | Key | Default | Extra |
+———————-+———————+——+—–+———————+—————-+
| comment_ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| comment_post_ID | bigint(20) unsigned | NO | MUL | 0 | |
| comment_author | tinytext | NO | | NULL | |
| comment_author_email | varchar(100) | NO | | | |
| comment_author_url | varchar(200) | NO | | | |
| comment_author_IP | varchar(100) | NO | | | |
| comment_date | datetime | NO | | 0000-00-00 00:00:00 | |
| comment_date_gmt | datetime | NO | MUL | 0000-00-00 00:00:00 | |
| comment_content | text | NO | | NULL | |
| comment_karma | int(11) | NO | | 0 | |
| comment_approved | varchar(20) | NO | MUL | 1 | |
| comment_agent | varchar(255) | NO | | | |
| comment_type | varchar(20) | NO | | | |
| comment_parent | bigint(20) unsigned | NO | MUL | 0 | |
| user_id | bigint(20) unsigned | NO | | 0 | |
+———————-+———————+——+—–+———————+—————-+


The most common and quick way useful for scripting (whether you have to do it for multiple blogs with separate dbs) is to delete all comments being filled as 'Spam'.

To delete all messages which were filled by Akismet's spam filter with high probabily being a spam issue from mysql cli interface:

DELETE FROM wp_comments WHERE comment_approved = 'spam';


For Unread (Unapproved) messages the value of comment_approved field are 0 or 1, 0 if the comment is Red and Approved and 1 if still it is to be marked as read (and not spam).
If a wordpress gets heavily hammered with mainly spam and the probability that unapproved message is different from spam is low and you want to delete any message waiting for approvel as not being spam from wordpress use following SQL query:

DELETE FROM wp_comments WHERE comment_approved = 0;

Another not very common you might want to do is delete only all apprved comments:

DELETE FROM wp_comments WHERE comment_approved = 1;

For old installed long time unmaintained blogs (with garbish content), it is very likely that 99% of the messages might be spam and in case if there are already >= 100 000 spam messages and you don't have the time to inspect 100 000 spam comments to get only some 1000 legitimate and you want to delete completely all wordpress comments for a blog in one SQL query use:

TRUNCATE wp_comments;

Another scenario if you know a blog has been maintained until certain date and comments were inspected and then it was left unmaintained for few years without any spam detect and clear plugin like Akismet, its worthy to delete all comments starting from the date wordpress site stopped to be maintained:

DELETE FROM wp_comments WHERE comment_date > '2008-11-20 05:00:10' AND comment_date <= '2014-11-24 00:30:00'

How to convert SVG to PNG graphic formats (using GUI and console) on GNU / Linux and FreeBSD

Wednesday, December 7th, 2011

Reading Time: 4minutes

SVG to PNG Convert on GNU / Linux FreeBSD using command and GUI

I’ve playing trying to learn InkScapeThe Open Source vector graphic editor .By so far I’m quite impressed on how easy this program is learned and how easy graphical manipulation with this nifty program can be done.
The default format in which InkScape saves its files is SVG (Scalable Vectors Graphics). For all those unfamiliar with SVG – SVG is an open (free format) format developed in 1999 which insetad of containing binary data like PNG or JPEG does contain plain XML content. SVG being consisted of plain XML has multiple advantageous, the most important one makes it easy for text and visual data to be displayed among different program svg readers in absolutely identical way. Besides that the format if read with plain text editor like vim or emacs can be altered directly via the source.
Being multi system interoperable makes SVG as a great format for text and visual data storage in HTML5, actually SVG is already a part of the HTML5 html coding standard. And most probably its adoption rate will raise up drastically as soon as HTML5 starts substitute HTML4 and lower web standards.

Anyways I’m slipping away from the aim of this post so I’ll stop blabbering on how great SVG is and let people check it out for themselves (if not already).

Going back to the aim of my article to show How to convert SVG to PNG graphical extension on GNU / Linux and FreeBSD

After producing a bunch of files with InkScape I realized the default format in which Inkscape stores its files is SVG , this was okay with me but since I wanted to have my experimental produced content in PNG I needed a way to convert SVG to
My first logical guess was that The Gimp will be able to handle the situation and after opening my SVG file with GIMP and used the gimp File -> Save As option and give the SVGfile an extension of PNG , Gimp succesfully converted the file to PNG.

However I wanted to dig further and check out what is the standard accepted way to convert SVG files using a plain command. This will possibly be handy to me if I had to do something online (let’s say a website) which will accept SVG and will require the SVG files to be converted and also stored in PNG or other Graphic file formats.

After checking online, I’ve found a post which pointed me to librsvg2 which contains RSVG(Turn SVG files into raster images.)

librsvg is available as a package in most mainstream Linux distributions nowdays, Fedora, Debian etc., as well as contains a port inside the FreeBSD ports system. Since I’m using Debian on my notebook where I installed and tested the command line SVG to PNG convertion the way I did it is:

noah:/home/hipo/Desktop# apt-get --yes install librsvg2-bin
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following NEW packages will be installed:
librsvg2-bin
0 upgraded, 1 newly installed, 0 to remove and 16 not upgraded.
Need to get 72.5 kB of archives.
After this operation, 180 kB of additional disk space will be used.
Get:1 http://ftp.nl.debian.org/debian/ squeeze/main librsvg2-bin amd64 2.26.3-1 [72.5 kB]
Fetched 72.5 kB in 0s (184 kB/s)
Selecting previously deselected package librsvg2-bin.
(Reading database ... 376046 files and directories currently installed.)
Unpacking librsvg2-bin (from .../librsvg2-bin_2.26.3-1_amd64.deb) ...
Processing triggers for man-db ...
Setting up librsvg2-bin (2.26.3-1) ...

Afterwards the exact convertion of my Inscape SVG file drawing.svg to drawing.png using rsvg I’ve done like so:

hipo@noah:~/Desktop$ rsvg drawing.svg drawing.png

The convertion results for me was 100% uniqueness between the file converted and the output PNG. Some people might wonder why I didn’t used Inkscape’s Export to Bitmap function and then use convert command part of ImageMagick in order to convert the produced Inkscape bitmap to PNG.

 

One other thing worthy to mention is on  Debian,  librsvg2-bin contains 2 more executable besides rsvg. One is the rsvg-view command which allows one to view SVG files using command line or Graphic enviroment, the other one is rsvg-convert which supports again SVG convertion to PDF and to PNG

Before proceeding with the other described ways to convert SVG to PNG earlier in this article, I give a try to Inkscape’s Export to Bitmap embedded function but the produced bitmap did not resembled the original SVG file so I decided to completely abandon this method
Maybe there is some particular reason of the chaotic way I’ve tested Inkscape to place random images sometimes going out of the field of a paper etc. which influenced the improper generation of Bitmap using Inkscape, despite that it seems InkScape needs some more development until the bugs in Bitmap producing get fixed and it can be freely used to produce Bitmaps.

Maybe there is some particular reason for the failure of Inkscape to produce a good BMP file, like for example the chaotic way I’ve tested Inkscape to place random images sometimes going out of the field of a paper borders etc.This should have influenced the improper generation of Bitmap using Inkscape, anyhow it seems InkScape needs some more development until the bugs in Bitmap creation get fixed.

By the way if you’re wondering how to convert PNG to bitmap BMP after, once having converted SVG to PNG this is easily doable with convert command, like so:

hipo@noah:~/Desktop$ convert drawing.png drawing.bmp

Maybe in future releases it will be a good idea if InkScape developers integrate a convertion to other formats this will make it handy and make surely these nice program more popular among users. Hope this is helpful. Cheers and as RMS likes to say Happy Hacking 😉