Yay to CSS3 \o/

After a long long time I've gotten a sudden surge in motivation to work on Anego again. Besides a bunch of bugfixes, UI Improvements and some translations I also tinkered with CSS3 Stuff, which makes dialogs look pretty awesome now. Let these dialogs be a statement of my resentment towards the new Win8/IOS7 Flat design.

Example, the 'Add page' Dialog Before

And after

jQuery Drag&Drop Tree fun

Did you know my Anego CMS implements it's own, modular jQuery Drag&Drop Tree for the menu management?

Here's a demo of it

Why you ask?

Because ~10 hours of searching for a user friendly, compact, styleable, clean jQuery Drag&Drop Tree Plugin yielded in nothing :D

Feel free to get above demo at http://tyron.at/files/sortabletree/ and use it for your own projects. The plugin is licensed under the GPL v2.0. It has a few graphical glitches, but apart from that it works flawlessly, from what I experienced. If you want to know how to handle the callbacks on the server side, check out the code of Anego. The icons are taken from ExtJS and FamFamFam.

Anego CMS reaching stable

I am happy to announce that my CMS is turning into a quite stable piece of software, and I am considering of doing an actual release. So far, one was only able to download the most bleeding edge version from the Github page, which means you've been at the developers mercy whether it works or not ;-)

I've been quite fanatical about features, bug fixing and clean code base lately. In the whole development of Anego I probably spent 30-40% of the time just refactoring the code to be cleaner, faster and more extensible.

Also new are the Gallery and Contact-Form/Mailer module, which should turn out to be very useful for website owners.

Talking about code. Since I love statistics, I've summarized all the lines of code for Anego only, so excluding 3rd party libraries (Smarty, TinyMCE, jQuery, etc. - those probably would add another 30-40k LoC)

Module	PHP LoC	JavaScript LoC
Core System	3.908	4.311
Blog	501	292
Gallery	547	1.094
Mailer	320	79
Richtext	66	135
PlainHTML	29	63
Separator	34	5
Total	5.405	5.979
Grand Total (+CSS/TPL LoC)		13.184

Hint of the day: Did you know, Anego CMS does not send your plain text password when you log in? Before it's sent to the server, a sha256-hash value is generated, which prevents a hacker to read your password, e.g. when your on a WLAN (though replay attacks are still possible).

Why the phpMyAdmin Status Monitor is awesome

So today I checked out my vServer performance using the phpMyAdmin Status Monitor that I have coded last summer, because a user notified me that a certain webpage doesn't load.
I couldn't figure out the problem as the site loaded normally for me and the server wasn't overloaded, however somehow I ended up hammering on the refresh button in the server tab to check what MySQL processes were running because it was fun and interesting.

So then one process did appear that was currently sorting the results of a query. It looked like a candidate for optimization. So I threw it into the query analyzer.

My assumption turned out to be correct. This query is used to determine when the visitor last visited the website, and it took 0.3 seconds to execute. It happens to be run on every page visit on of my websites.

So, being already in phpMyAdmin, I head over to the table in question and add an index for this field.

ALTER TABLE `cc_counter` ADD INDEX ( `ip` )

Now lets run this query again:

Well that sounds much better doesn't it! From 300ms to 0.8ms. Around 375 times faster now. While we're at it, we might as well write this query correctly.

As we only require the newest visit it is enough find MAX(time) of the visitor in question. Another 40% faster, which results in a speedup of around 525 times compared to the original.

So we not only saved ~0.3 seconds of loading time per visit but also reduced the required CPU time allowing greater workload on the server.

If you are running whatever self written code on your servers or webspace, get the new phpMyAdmin 3.5, check out your database load and optimize your queries. It's very gratifying work ;-)

P.S.: I'm aware that this might be an exceptional case, since a well designed database layout would have proper indexes from the very start. Nonetheless the Status Monitor is a great tool for surgical improvements on bad code like this one. Also in projects that slowly grow over time the chances are not too low to miss out on creating an index where it would be needed.

Anego CMS Modules - Report 6

Due to the holidays this report has been slightly delayed ;-)
However, I'm happy to report that the gallery module is now mostly finished and only a number of smaller features are missing. Since the last report I've completed:
- Store/Restore image size and crop area
- All the server side code
- Default sizes from which the user can select from. This includes default sizes for the original image, the preview image and the crop area.
- Pictures can be deleted.
- There's now default sizes for the original and preview image which is a per-gallery setting.
- Drag&Drop image reordering is finally working too. I've had difficulties getting jQuery UI Sortable to work, the bug being wrong event bubbling.
- Added a very simple pagination for the blog (not related to the gallery module)
- A ton of bug fixing

The gallery module is now also being tested and running the gallery on my wife's website saraty.com. I was happy to hear that she is very pleased with the simplicity of setting up a gallery. Below is a screenshot of an image being edited.

Until the end of January the only things left to do are:
- There is one bug left to fix with image reordering
- Classic style file upload with file selection
- If there is time left, there could be some per-gallery-batch-image-resize so one could easily change all the preview and original image sizes.
- Write documentation.

Anego CMS Modules - Report 5

In the last 2 Weeks I've been busy implementing a small image editor that allows the user to crop and resize images for the gallery. It turned out to be unexpectedly much work to create a 3-in-1 Image resizer, Cropper and Dragging tool that has some usability. Hence, I wasn't able to write the server code for it yet, which is however the easiest part. For the next report there's a number of functionalities that I have to build:

• Store and restore the image size and cropped area so a user doesn't need to select it from anew if he wanted the picture just a bit bigger.
• Server code for cropping and resizing
• Allow image editing of both thumbnail and full size picture. For this I have already refactored the image editing code into a jquery plugin for it to be modular.

For this project I will probably drop my plans for improving the blog and focus entirely on the gallery. I realize it's a better choice to have a feature-complete, versatile gallery module, rather than having 2 not very finished modules.

Anego CMS Modules - Report 4

Since last update I've added a settings dialog for each image so users can change their properties such as description and thumbnail. This information is also saved and stored in database now.

Also I've found a great image cropping tool named Jcrop which I probably will be implementing until next time. I'm also considering to add a thumbnail resize feature like in this demo.

And finally I've been fixing a few bugs in the dialog code.

Anego CMS Modules - Report 3

In this 2 weeks I've had to do some major refactoring of Anegos dialog code so that it allows multiple dialogs. This will be needed so e.g. one can open a gallery settings dialog while being in the edit mode.

In the process I had to touch a lot of code, so I also cleaned up much of the code style. I've also found a number of bugs that I fixed.

The progress on the Gallery itself though hasn't gone much further. I've made only small additions and until the last day I spent a lot of time getting jQuery UI Sortable working on the images so one can Drag&Drop them but I failed. There must be something bugged with that widget.

Anego CMS Modules - Report 2

Unfortunately I couldn't find much time to invest on this Project so I will have to catch up within the following 2 weeks.

Anego CMS Modules - Report 1

Week 1
I've spent most time contemplating on how to build a good UI for the Gallery module and researching on HTML5 File upload. Since there is already existing libraries for Drag&Drop file upload I don't have to reinvent the wheel. However as this technology is fairly new, I guess not to many solutions exists for it yet. At least not as jQuery plugin, which for me is a must for better code quality. In particular I've been weighing the pros and cons of:

• jQuery filedrop: A basic do-it-yourself Drag&Drop file upload that only covers the actual uploading and lets the user implement all the UI related stuff.
• jQuery File Upload: A fully featured Drag&Drop file upload with GUI that has it all, but requires jQuery UI and the jQuery Templates plugin.

In the end I decided to go for jQuery filedrop because I have a strong desire to directly integrate the uploading part into the gallery view, which is not possible with the already integrated UI of jQuery File Upload. 


Week 2
This week I mostly invested in a massive refactoring of the module system of Anego and improve the code style in many areas to increase the readability and extensibility. Towards the end of the week I was able to implement the basic Drag&Drop file upload and thumbnail generation. The most important infrastructure for the gallery module is now ready. This basically means the most important design decisions are made and it's mostly just fleshing out each functionality now.

Also I'm am happy to report that Anego also has been accepted as part of a project for my Lecture 'Advanced Software Engineering' where a Team of 6 Students (including me) will do some great improvements on the core  system. The blog and gallery module stays untouched by this though - they are treated as a different project.

A new Project: 2 Anego CMS Modules

Starting from last week, I have begun a new project that will focus on 2 modules of my experimental content management system Anego CMS. This project will end not later than January 2011 due to the fact that this project will be part of my bachelor studies in software engineering.

Anego is a new, basic and still pretty unstable (in terms of backwards compatibility) cms that I have been working on for the last few years and is powering not only my website (tyron.at) but also my wife's (saraty.com), my brother's (simpleit.at) and any small business websites that I can make my customer (I've got 3 so far!) ;-)

My CMS is using a new model for representing a website page - it is broken down into elements that can be dragged and organized to the users preference. Each of these elements is seen as a "Content Element" plugin/module that users may add and remove to Anego.

There already exists a very basic blog module that allows the creation, editing and deleting of blog entries and a simple comment feature to each blog entry. My Job is it now to improve the blog module, that include the following points:
- Module Interface - The current implementation of module support within Anego is rather unstable, it may have to change quite substantially, thus any already written modules will need to be adjusted accordingly
- Pagination - Currently all blog entries are displayed in one page. Proper pagination needs to be added.
- Archive and Navigation - There currently no overview over the written blog entries, an intelligent way of displaying a list of blog entries needs to be found, as well as implementing archivation features (navigation by year/month or similar)
- RSS Feeds - A blog without RSS support is quite useless.
- Settings: A settings dialog for changing some basic properties such as commenting, entries per page, etc. should be added.
- Comment Anti-Spam and maybe signup by google/facebook/openid/etc: The current implementation of comments may be vulnerable to spam
- Ideally possibility of writing hidden drafts and preview

Also I will implement a Gallery module that should have at least following features:
- HTML5 Multi-Upload-Support and ideally batch-resizing of Pictures
- Display gallery as grid with fancy zooming
- Drag&Drop reordering of pictures
And depending on the required time investment I would also add:
- Gallery Settings: Allowing some display options such as thumbnail size, picture descriptions, pre-loading of images, image zoom type, maybe watermark
- Different gallery display types: classic gallery, image slider, etc.

So altogether its going to be an exciting winter!

Web Application Security Scanner

Yesterday I stumbled upon a whole range of software I didn't know it exists. I've found Netsparker, that can automatically scan a website for vulnerabilities such as SQL Injection and Cross Site Scripting threats. All it needs is the URL to your website and it searches and exploits every possible interaction with server side scripts.

There is a Community Edition of Netsparker, which is free but has limited features. Nonetheless it helped me find *a lot* of SQL Injection and XSS threats on my websites. If you run any type of non-static website, I'd strongly suggest to try out this tool. I am damn glad to have used it.

You might also want to check out the comparison of 60 similar tools in this area.

Week 13 report

Since my project was pretty much completed already I didn't have much left to do other than to keep testing and build some additional features.

I originally had only a few regex's to pretty print the sql queries which of course doesn't work for recursing patterns of subqueries. So I've used the tokenizer from CodeMirror and the MySQL Mode to iterate through each token and pretty print it.

What I made this week:

- Rewrote the SQL Pretty printer for the Query Analyzer dialog.

- Easy editing of byte values on the Server variables page

- Lots of fixes and small adjustments

Here's a screenshot that shows the majority of what I've been building this summer:

Week 12 report

My Project is completed. The only thing that is left to do is to improve the code style, write some documentation on the phpMyAdmin wiki, fix any occurring bugs and tweak some things here and there.

I still added a new feature this week which took a lot of rewriting in the monitor code. It is now very easy to add preset charts over calculated values. I've already added two, which is for Query cache efficiency and Query cache usage (%). The reason why I had to rewrite a lot is because in case of the query cache usage, it also required a server variable (query cache size). So I've adjusted the chart configuration code to be more generic about the required data.

What I did this week:

- One new feature: Preset charts over calculated values. Newly added: Query cache efficiency and query cache usage (%).

- Improved page load performance by loading the monitor code on demand (+ other speed improvements)

- Implemented and improved the new tooltip system made by Aris

- Added and adjusted some rules for the Advisor system

- Made lots of code style improvements

- Lots of bugfixing

What I will do next week:

- Documentation

- Keep tweaking and scrubbing the code :-)

- Build stuff from my week 10 ideas list again with the leftover time

Week 11 report

This week I have been coding hard on the Advisor system that I planned for a long time. It uses the mysqltuner 2.0 config as a base, though I have added much more information about each rule/recommendation.

Here is an example rule in mysqltuner2.0

Query cache efficiency (%)|||<20|||Qcache_hits/(Com_select+Qcache_hits)*100|||query cache not efficient, consider increasing query_cache_limit.

This is how it looks in the pma version:

rule 'Query cache efficiency (%)' [Com_select + Qcache_hits > 0 && !fired('Query cache disabled')]

Qcache_hits / (Com_select + Qcache_hits) * 100

value < 20

Query cache not running efficiently, it has a low hit rate.

Consider increasing {query_cache_limit}.

The current query cache hit rate of %s% is below 20% | round(value,1)

As you can see, there is now 3 text lines. They are displayed title with 'issue','recommendation' and 'justification'. This way the user can make a more wise decision on how to improve the performance (or ignore the recommendation altogether)

This rule syntax is being parsed by a simple rules engine I have coded this week.

What I made this week:

- Advisor system:

  - Built rules file and rules parser

  - User interface that displays the recommendations

- New status variables "filter" that allows one to display the unformatted values (so one can copy and calculate own perfomance measures)

- Fixed a security issues with last weeks changes and other small improvements

What I will do next week:

- I will need to find a good way of adding i10n to the rules file

- Improve code style, write documentation etc.

- With leftover time, build some of the features from last weeks idea list (week 10 blog report)

Week 10 report

This was a really interesting weekend. I've been digging through my newly discovered MySQL tuning tools most of the time, and discovered 2 great ideas that I could include to my monitor.

First is the mysqltuner 2.0 ruleset that I already mentioned, which I plan to add this week.

And the other is mk-query-profiler from the maatkit command line tools. It uses 'SHOW SESSION STATUS' to read lots of very interesting data about single executed queries. This would be a wonderful addition to my 'Query Analyzer Dialog'.

What I made this week:

I started out realizing that the slow query log loading didn't work at all, so I had to do some bug fixing there. Having the slow log working again I noticed that I forgot to implement grouping/summarization of the slow query columns such as examined_rows, sent_rows, lock_time etc. It took quite some code rearrangement to fix this.

And I kept finding new bugs that I had to fix first. One for example was that the query profiling information in the 'Query Analyzer Dialog' was recorded for the wrong query.

In the remaining time of the week I implemented

- chart config import and export using json as data format

- chart title and series label editing dialog

- a small look into drizzle support. With a small patch most of the status page now works under drizzle (some values don't exist and return php notices though)

- a fix for cpu charts not displaying anything

- error handling when the pma session runs out. It now displays a dialog asking the user to reload the page

- in the 'query analyzer dialog' it now displays all explain outputs (there are multiple explain rows when multiple tables are used)

- added a check for mysql versions that don't support table logging (< 5.1.6)

So lots of small needed fixes and adjustments, plus a unexpected power outage on friday that lasted most of the day kept me from writing actual code for the advisory system.

What I will do next week:

- Advisory system using mysqltuner 2.0 ruleset

What I still would like to do for phpMyAdmin, though not mission critical for my project:

- Display chart from display table page: Handle big amounts of data with zoomable charts

- Debug server variables table width auto-adjustment (doesn't always adjust properly)

- Fix UI of create table dialog / enum editor

- Status Monitor: Add disable general_log + truncate table link to setup dialog

- rewrite charts page on the wiki

[Edit 01.08.11]

- One more great idea: For the Monitor charts: Allow charting over calculated variables, like the query cache hitrate (Hit rate = Qcache_hits / (Qcache_hits + Com_select))

[Edit 02.08.11] I never run out of ideas -_-

- Fix the query analyzer sql formatter to not fail on subqueries or on occurring strings like "this is where the formatter fails"

- Add a 'Search' - link to the monitor tab allowing one to make searches over slow log / general log and their EXPLAIN's (e.g. find all queries using temporary tables)

The extinction of the MySQL Tuning tools

On my journey of implementing tuning/optimizing tools for phpMyAdmin I've came across many already existing open source tools in that area.

I'm starting to realize though, to my surprise, that most of these tools haven't been updated or gained new features for a year or more. You rarely get to read recent blog postings about variable tuning either.

The most well known tools are:

• mytop - Like the *nix top but for mysql threads. Last release is from Feb 2007
• mysqlreport - '...makes a friendly report of important MySQL status values'. Last release on April 2008 and official statement of no longer being maintained.
• mysqltuner - makes tuning recommendations. Last release from April 2011 but from what I could see only bug fixes and cosmetic changes since it's first release in 2008
• mysqltuner 2.0 - a misleadingly named mysqltuner fork by Sheeri K. Cabral. Last release in April 2010. Was only active for 5 months.

So I'm quite baffled that I seem to be the only one who's currently working on a open source MySQL tuning tool, despite the fact that MySQL is getting quite popular nowadays. I really wonder why so little progress is being made in this direction. Though, I'd love to hear that this is not the case and there's is some recent tools I just haven't found.

Either way, while looking at the tools, I saw that mysqltuner 2.0 uses a comprehensive set of rules to build the recommendations. Since it has been build by a database professional, actually quite a celebrity in the db world, from what I could read, I will probably use it as the basis for my suggestions/advisory system. Gladly the GPL-License allows me to do so :)

This is very useful since it will save me a lot of work "inventing" the recommendation rules, for which you need a lot of MySQL experience.

Also I'm the only pma gsoc student that posts project related stuff which is not a weekly report! :(

[Edit 31.07.11] Ok, I just ran into all the more recent tools that I failed to find, the most extensive one being maatkit. Definitely gonna check out that one!

Week 9 report

This week did quite turn out as I planned to but ended with good results nonetheless.

An unusually big amount of time I had to invest in tracking down a series of bugs.

For the CSS Sprites I built last week, I had to rewrite PMA_linkOrButton() which is a core function of phpMyAdmin, so that took quite a while as well. But in the progress I was able to fix a bug and make the code cleaner.

I still didn't find the time to improve the settings import/export and IE compatibility.

But apart from that I have built a 'Query analyzer' Dialog that is reachable through clicking 'SELECT' - Statements in the Log Table. It allows one to get profiling and 'EXPLAIN' information about that query. I have also built a very simple Javascript MySQL Formatter do display the query nicely in an editable area, so one can also tweak the query and check for execution time changes.

And the last addition was another possibility to filter the log data. If enabled, it removes variable data in WHERE statements with regexp magic, then regroups the queries. So for example if you have a user table that has a name and user id column and your code does hundreds of queries to get the name of different user ids, these queries will be grouped together into one.

And finally I have now a concrete idea on how to build the Advisory/Suggestion system.

- I will add a few advisories inside the query analyzer dialog, using the profiling and explain information.

- On the 'Status/Server variables' Tab I will add a button that allows the user to start a variable analyzer that throws out suggestions for the server variables based on the values of the status variables.

Once that is done, my GSoC project is completed :-)

What I made this week

- 'Query Analyzer' - Dialog. Pretty prints the query, shows profiling information, shows EXPLAIN information

- Filter: Group queries, ignoring the variable data

- Lots of bug fixing

- More work on CSS Sprites, also now documented on the wiki

What I will do next week

- Hopefully get to the import/export part

- Advisory system

- [Edit 26.07] I forgot that I still need to implement the chart editing feature. It should allow one to rename the chart title and series.

- [Edit 28.07] I also forgot that I still need to fix the appearing empty spaces in the chart grid when changing columns count

Week 8 report

This week I've done a good portion on finalizing the monitor, such as chart ordering and i10n. After that I've invested a considerable amount of time in replacing the big amounts of icon images in phpMyAdmin with CSS Sprites, since they improve page loading speed and allow for better style customization.

Since the Monitor is now not far from finished, I can start focusing on some type of Advisory/Suggestion System that can give the user hints for optimizing their configuration and server queries. I will need to put together a plan on how to infer those advisories and a proper visualization of them. For that I've already done some research of whether I can solve that with a business rule engine - as it would be just wonderful to have all these advisories/suggestions getting inferred from a set of well organized rules. To my disappointment there seems very little available for PHP. But I will keep looking.

What I made this week:

- Disable CPU and Memory graph for database server that are not local(-host)

- i10n

- Wrote a jQuery Plugin for convenient element reordering in tables, since I couldn't find any good solution for that: http://tyron.at/tmp/ts.html

This allows for easy reordering of charts now.

- Replaced chart size settings with amount of columns. The charts automatically size to the dimensions of the viewable area.

- The chart ordering is now saved as well. It will keep its order after a reload/restart of the browser.

- Improved usability of the returned log data table by adding a 'total sum' row at the bottom and adding a way of filtering the query texts. So now one can just type a table name and you get to see how many queries have been executed on that table (not pushed to my repo yet).

- I've spent a lot of time to add CSS Sprites to all of phpMyAdmin to replace the big amounts of single icon files currently being used. In a few tests I noticed a reduction of 20-30% of GET requests on a page load, resulting in the page being loaded faster. But it will also make it easier to customize themes and will make the pma code more readable.

What I will do next week:

- Still need to work on that settings import/export & IE compatibility

- Some feature that allows you get profiling information from the queries in the returned log data

- Check out what the possibilities are for displaying statistics or list over server queries that failed with an error and whether it would be of any use to read from the log files instead of the log table.

- Flesh out a concept for the Advisory/Suggestion system.

Under attack

Sigh, I'm currently trying to reduce the damage caused by a DDoS attack on my server.

A hacker has found a weak spot in one of my oldest websites (the PHP code I wrote for it is like 5-8 years old). I've taken the site offline now, nonetheless the flood of requests still seems to slow down the server. But at least it responds again.

The hacker has abused the fact that the comments list on that site has no limit in how much shall be displayed, so the hacker completely overloads the MySQL Server by flooding requests to load all 51000 Comments. Most of you probably gonna slap your forehead when you read this, I do too ;) But this website was made when I started with PHP and serves only a really small gaming community. I have left that community years ago, but left the site online. And the code remained mostly untouched since then. I will need to take it down permanently some day, and let that collection of really bad PHP code rest in peace.

By the way the first indication of a DDoS Attack I found through my newly programmed Monitor Tab in phpMyAdmin which showed me that some queries have been executed several thousand times. So thats an epic win right there :D

Looking at the logs, the attack is truly distributed. I checked some of the IPs with reverse DNS -the results: Saudi Arabia, Brazil, one ip from Amazon Web Services, China, Romania, ....

Might generate some statistics over all these ips once the attack is over, that would be awesome to have.

[Edit:]

Mini-Statistics: (counting from like 1 hour after the attack has started)

Amount of DDoS Attacks: 202.299

Amount of IPs from where the attacks come from: 1.106