Wednesday, December 31, 2014

phpMyAdmin work during the twelfth week

A merry Christmas to everyone!!!

Christmas happened to fall on my Twelfth week of work. The work week was a short one since I took leave on Thursday and Friday (25th and 26th) around Christmas. Just like the last week, I focused on bug fixing during this week as bugs were reported frequently. Following are the bugs that were fixed during the week

Bugs fixed
#4651 don't open console with esc
#4653 Always connection error was shown, on /setup at tab "configuration storage"
#4661 Drag and drop file import always fails
#4663 Export missing back ticks for order table name
#4664 select min() displays 1 row, but reports the table amount of rows returned
#4666 Undefined indexes in table print view of a view
#4668 Remove from central columns error
#4670 CSV import reads both commas and values into first column after first row
#4671 Unable to move all columns

Additionally following feature request was addressed.

Feature requests implemented
#1563 Change keyboard shortcut for console display to other then 'escape'

The last time I updated the TCPDF library we got an error and I reported it to upstream. Since the developer has fixed it, TCPDF library used in phpMyAdmin was updated to its latest version.

Other
Updated TCPDF to its latest version 6.2.3

Sunday, December 21, 2014

phpMyAdmin work during tenth and eleventh weeks

Here I am combining work done on two weeks since I was on leave for four days during the latter week and there is no point of writing a separate blog post for just one day of work.

During the tenth week I concentrated solely on the bug fixes as a load of bugs were reported during the week. Most of them were on the 4.3 series and this indicated that more and more people are starting to use the new version, which is great.

So here is the list of bugs I fixed during the six days

Bugs fixed
#3794 failure to handle repeating empty columns when importing ODS
#4617 UI issues with sortable tables
#4625 "Insufficient space to save the file" on export SQL to file on server
#4626 Ctrl + click on a column not is sort triggers a server call to erroneous url
#4627 "file_get_contents(examples/create_tables.sql): failed to open stream" after update
#4628 PHP error while exporting schema as PDF
#4629 Problem with custom SQL queries using cookie authentication
#4630 AJAX request infinite loop
#4631 Server selector submits two server parameters
#4632 Notice in ./libraries/Util.class.php#1916 Undefined index: query
#4633 Wrong parameter in fetchValue
#4634 Error reporting creates an infinite loop
#4638 Default Export Method setting broken
#4639 Export SQL missing indentation first field
#4644 Error when browsing tables
#4646 Version Check Broken
#4649 Attributes field size smaller than others

In addition to that I attended to the following list of bugs

Bugs attended
#4438 “Token Mismatch” Error In Safari via MAMP
#4589 Can not add new procedures
#4641 Zeroconf PMA tables support
#4642 phpmyadmin often fails to load due to specific load order

Public Transportation Usage Analysis for City of Chicago

As part of my BI course I'm taking for my part time MSc, I started working on an analysis on the public transit data of the city of Chicago.

Motivation

Public transportation system can benefit greatly from insights into the traffic volumes. These insights can be helpful in identifying the peak times and peak routes and will be instrumental in effective allocation of buses and other transportation modes to cater those transportation needs. This can further help the authorities in making other corporate decisions such as purchase decisions and decisions to issue route permits (in SL context) etc.

Further, such insights would be used to do demand management. In demand management the user is offered incentives to direct users to use the transportation system when the congestion is less. Airline industry benefits greatly from demand management and this can be applied to other transportation to various extends. To do demand management, insights into the traffic details would be crucial and it can in turn help the authorities of companies identify the effectiveness of such demand management strategies.

Data Set

The data was available from the Open Data Portal of City of Chicago and it included data for both bus and metro lines. The data was in the granularity of days and for buses it had data for each of the bus routes. In the case of metro, the data was in the form of metro station entries. More data was available on bus stops and metro stop. However, these data could not be used for the analysis since the fact tables were in the granularity of bus route and station entries.

The data was in CSV format and my first task was to get them into a relational databases. I did a quick analysis on data in the relational database. Bus rides data had about 700,000 tuples while the station entry data had about 600,000 tuples. I had to derive separate tables for metro stations and bus routes and define required primary keys as well as foreign keys among tables. Since the data was in granularity of days I created a new table for days and added extra columns such as year, month, day of month, day of week which is helpful in further analysis. So my final schema looked like this.

Analysis

For the analysis the preferred BI suite chosen was Pentaho. However, I soon realized the the Community Edition of it lacks much of the ease of use and many things has to be done manually.

BI schema

Since time is the major dimension of the analysis I required time to form a hierarchy and designing time in a hierarchical manner was not possible with the schema generation in Pentaho BI server. For that I used the Pentaho Schema Workbench which allowed me to properly mark the dimension and time for the required hierarchy.

OLAP cube

OLAP cubes were generated using the jPivot plugin for Pentaho. Since time was defined in a hierarchy, now I can drilldown the cube in a more meaningful manner (in a year, month, day hierarchy). 

Dashboard

The dashboard available in the community edition lacks ease of use and dashboard needs to be defined and the tweaks needs to be done with CSS and JavaScript. So this dashboard creation is not for non-geeks. I included the following charts in the dashboard.
  • Bus rides by bus routes
  • Station entries by station
  • Bus rides and station entries by year (to identify overall trend in public transportation usage) 
  • Bus rides and station entries by month (to identify seasonal variations)
  • Bus rides and station entries by day of month
  • Bus rides and station entries by day of week (to identify weekly patterns)
  • Bus rides and station entries over time

Reports

Pentaho comes with a report design tool which allows creating report templates and binding data with them easily . I created reports which included total bus rides and station entries by month, year and by station and routes. Report Designer allows publishing those reports to the BI server. However, I got an error while doing so. Though I did not find any documentation to suggest that, I suspect this is not allowed in the community edition. Report designer allows publishing reports in multiple formats and the following is a sample report in HTML format.

Observations and Interesting Findings

A number of interesting observations were made using the Pentaho dashboard. 

There is an year by year increase of metro usage while the usage of buses remained constant over the course of 13 years. 


As expected, Sunday recorded the least usage of public transport followed by Saturday. However, interestingly Monday had a noticeable drop compared to other week days. May be people feel lazy to wake up on Monday mornings and rush to work with their own vehicles :)


Moreover, interesting seasonal variations were observed which is suspected to be related to four seasons. There was a noticeable drop in the public transit usage in the months of December, January, and February and this is possibly due to the winter conditions.


Further I noticed that some days in the month has considerable less usage. In the below graph 31st day of the month record lowest since not every month has a 31st. The drop in 29th and 30 the can probably be asserted to the month of February. Drops on 1st, 4th and 25th can possible be due to US holidays that falls on fixed days (1st Jan - New year's day, 4th Jul - Independence day, 25th Dec - Christmas day). However, I did not find such a drop for 11th Nov which is the Veteran's day.

Tuesday, December 9, 2014

phpMyAdmin work during the ninth week

This was a four day week since I was on leave on 3rd Wednesday.

During the week I was more engaged in code improvement work opposed to putting effort on bug fixing compared to previous weeks. The jQuery and jQuery-ui versions phpMyAdmin was using were too outdated and it needed an upgrade for sometime. jQuery was stuck at version 1.8 since there were major changes in upgrading from 1.8 to 1.9. This included a set of methods removed such as .live() and .die() methods which were deprecated for sometime. However, these methods were heavily used in phpMyAdmin making phpMyAdmin stuck at version 1.8.

I started with going through the upgrade guide for jQuery 1.9 [1] and inspecting the phpMyAdmin for possible breakages in case of an jQuery upgrade. There were heaps of .live() and .die() methods to be replaced and more instances of other methods removed. 

Then I went on to check each of jQuery plugins for their updates as most of them were pre 1.9. Some of them had updates - the ones that are actively being developed -, while for the others, I had to patch them to be compatible with jQuery 1.9+. Now after much effort phpMyAdmin uses jQuery version 1.11.1 and jQuery-ui version 1.11.2.

Code improvements
Making code compatible with jQuery 1.9+ and upgrading phpMyAdmin to use latest versions of jQuery and jQuery-ui
Upgrading phpMyAdmin to use latest versions of jQuery plugins where newer versions are available and upgrading other jQuery plugins to be compatible with jQuery 1.9+

Bug Fixes
#4615 SQL highlighting in process list breaks on auto refresh
#4616 Warning on db structure print view page
#4618 Page scrolls while GIS visualization is zoomed in/out with mousewheel

Security fixese
#4611 DOS attack with long passwords (coordination)
#4612 XSS vulnerability in redirection mechanism (coordination)

[1] http://jquery.com/upgrade-guide/1.9/

Monday, December 1, 2014

phpMyAdmin work during the eighth week

As mentioned in my last blog post I started working on the token mismatch issue that we received a lot of complaints about for so long. The main issue was that both http and config authentications were not able to refresh the page when when PHP session expires. I implemented a solution similar to what we used for cookie authentication on session timeout which would refresh the page with new token. 

I also attended to regular bug fixes reported within the week. Altogether here is the list of bugs fixed and attended.

Bugs fixed
#4605 Unseen messages in tracking
#4606 Tracking report export as SQL dump does not work
#4607 Syntax Error during db_copy operation
#4608 SELECT permission issues with relations and restricted access
#4227 Token mismatch when using HTTP AUTH and the SESSION expires
#4280 Error: Token mismatch
#4387 Token mismatch (one server http auth, one cookie)
#3893 Token mismatch error on every action

Bugs attended
#4569 Issues with Relation View dropdowns
#4409 Not displaying results of archive table

During the latter part of the week I concentrated on refactoring and code improvements. I improved the database and table level tracking pages with facility to enable/disable tracking for multiple tables, ability to delete multiple versions of tracking etc. I also worked on displaying results of multiple queries. This involved quite a lot of changes since most of the work had been done assuming that only one result set would be displayed. This work continued to the next week. So here is the list of feature requests and refactoring done during the week.

Feature requests
#1562 Allow tracking multiple table at once from database level tracking page
#1564 Improve action message on Tracking page

Refactoring and code improvements
Refactoring database level and table level tracking pages
Refactoring query results display page to facilitate display results of multiple queries at once

PS: I took the day off on Monday, the 24th.

Thursday, November 27, 2014

phpMyAdmin work during the seventh week

This week was quite busy with a lot security fixes. At the beginning of the week 5 security vulnerabilities were reported and I spent most of Monday and Tuesday doing fixes, porting them to other branches, preparing security advisories and communicating with reporter, security team and CVE team.

Following are the list of security vulnerabilities fixed.

Security fixes
#4594 Path Traversal in File Inclusion of GIS Factory
#4595 Path Traversal can lead to leakage of line count
#4596 XSS through exception stack
#4597 XSS through pma_fontsize cookie
#4598 XSS in multi submit

During the week I was also engaged in usual bug fixing and following bug fixes are now ready to be released with the next release.

Bug fixes
#4057 db/table query string parameters no longer work
#4444 No insert statement produced in SQL export for queries with alias
#4591 Spinner in navigation running forever
#4599 Input field is erased after keyboard language switch
#4602 Exporting selected rows export all rows of the query
#4603 Field disabled when internal relations used

Additionally, I also attended to the following bugs.

Bugs attended
#4254 Unable to log in after timeout had been exceeded (cookie)
#4008 Unable to log back in after session expired
#3773 No tables shown because of privileges of views
#4367 Import status infinite loop
#4295 Problem when session expires while importing file

Towards the end of the week, I had a look at the token mismatch issue that were reported to us several time. I will update you on the progress of this in the next post.

Tuesday, November 18, 2014

phpMyAdmin work during the sixth week

During the week I mostly attended to bugs. Please find below the list of bugs fixed and attended during the week.

Bug fixes
#4582 Debug SQL works only for the first page
#4581 Some links in query_result doesn't work
#4404 Recordset return from procedure display nothing
#4584 Edit dialog for routines is too long for smaller displays
#4585 Multi query results not shown
#4588 Moving, renaming, dropping actions in table operations page results in token mismatch
#4589 Can not add new procedures

Bugs attended
#3588 X-WebKit-CSP Header breaks Safari
#3940 Content Security Policy errors with Safari 5.1
#4061 No error message when calling an insert stored procedure with too few parameters
#4590 "Browse Foreign Values" not working

In addition to the above I was occupied with upgrading the result showing for multiple queries. Earlier it is at a very primitive level where all the rows are shown without limiting and no additional browsing features present. I refactored the result showing mechanism to reuse it for the multiple queries. However, it was decided that the changes might make the upcoming release 4.3 unstable and now it is targeted for version 4.4. Here is the pull request

https://github.com/phpmyadmin/phpmyadmin/pull/1397

Following feature requests were also implemented during the week

Feature requests
#1556 Disabling Show all
#1553 InnoDB presently supports one FULLTEXT index creation at a time

Tuesday, November 11, 2014

phpMyAdmin work during the fifth week

I am back from the holiday going to GSoC Reunion and doing some wonderful sightseeing in the USA. During the fifth week of work (first week of November) I attended to  a mix of performance improvements as well as a couple of bug fixes.

I got access to the test server prepared by Ann + J.M. and could test the performance improvements done for the environments with thousands of databases. Even though I could achieve reasonable performance for privileged user, unprivileged users were still seeing long delays. Even 'SHOW DATABASES' queries were as slow as queries on information_schema. The only fast query to access database list was 'SHOW DATABASES LIKE '<db_name>' queries. So the navigation was updated to parse the GRANTS for the user to identify the database he/she has access to and those were used with 'SHOW DATABASES LIKE' queries to get the list of databases.

In addition to the above I attended to the following fixes during the week.

Bug fixes
#4577 Multi row actions causes full page reloads
#3481 Designer relations in IE 8 - partial fix
#4582 Debug SQL works only for the first page

Bugs attended
#3046 Tracking + MySQL interactive_timeout yields error
#3554 copy paste with middle mouseclick in colored sqlbox
#3073 auth_type signon - odd behaviour at ex- and importing

Security fixes
#4578 Undisclosed vulnerability
#4579 Undisclosed vulnerability

Refactoring
Refactoring Node class of the navigation

P.S: I was on leave on Friday due to personal reasons

Tuesday, November 4, 2014

phpMyAdmin work during the third week

This blog post was due for sometime. However, I couldn't write it since I was busy attending the GSoC Reunion and doing some sight seeing in the USA afterwards.

During the third week of work I continued to focus on stabilizing the code for upcoming version 4.3 release. I was mainly attending to performance improvements related to the usage of information_schema. Following are the bugs addressed by the performance improvements

#3869 Count(*) on information_scheme.INNODB_BUFFER_PAGE with a huge bufferpool
#4243 Super slow page rendering with tens of thousands of DBs
#4513 phpmyadmin run very slow (information_schema)

Additionally hide_db, only_db directives were fixed as part of performance improvements fixing the following bug

#3820 hide_db, only_db not working in left panel

Further following bugs were also fixed

#4259 reCaptcha sound session expired problem
#4560 PHP error on master branch
#4564 Designer: spaces in table name with edit table link generates bad links
#4557 PHP fatal error
#4568 Date displayed incorrectly when charting a timeline
#4561 PHP error in transformations
#4431 Wiki page on charts is out of date

Two security issues were also identified and fixed during the third week.

#4562 XSS in debug SQL output
#4563 XSS in monitor query analyzer

Tuesday, October 14, 2014

phpMyAdmin work during the second week

The second week of my work was relatively a short one since I had to take 2 days (8th Oct Wednesday and 13th Oct Monday) off. So effectively I only worked for 3 days (9th, 10th and 14th) during the second week.

As suggested in the monthly IRC meeting I focused more on stabilizing the code for the upcoming alpha release of version 4.3. I stated with performance improvements related to the usage of information_schema which was a problem since version 4.1. The work carried out can be found at pull request 1375 [1]

In addition to that the following bug was fixed.
#4556 Fast filters for tables, views etc. should be governed by NavigationTreeDisplayItemFilterMinimum

I also did some bug tracker clean up which which resulted in me attending to the following bugs
#3154 No db table entry for users with table_priv only and no host
#4277 Export problems - low execution time
#3165 Redundant Foreign Keys not supported

[1] https://github.com/phpmyadmin/phpmyadmin/pull/1375

Tuesday, October 7, 2014

Working for phpMyAdmin and my work during the first week

I started working for phpMyAdmin on 1st October after my work proposal got selected for the phpMyAdmin Developer Position (one year contract). Here I am blogging about what I did during the first week. However, going forward I will probably write my blog posts during weekends rather than mid week.

Anyways, here is what I did during the last week.

Bug fixes
#4259 reCaptcha sound session expired problem
#4547 Micro history does not work in Users page
#4548 Inline editing a field converts tab to spaces
#4551 Wrong test in source code
#4552 Incorrect routines display for database due to case insensitive checks
#4553 Add index dialog does not open on choosing index from create table
#4537 BLOB inline-view JPG column transformation does not work for anything except simple queries
#4252 Database-level permission bug for db names containing underscores
#3120 events are not exported when using xml

Bug investigations
#4303 "New" link in navi panel is shown even if no privileges (out of date)
#4309 Cannot create/edit events and procedures (out of date)
#4495 Comment lines in multiquery
#4255 Can't enter when session die
#3154 No db table entry for users with table_priv only and no host

Feature requests
#1542 Better error reporting in Designer; can get stuck Processing

Security issues
#4544 XSS vulnerabilities in table search and table structure pages

Refactoring
Users page refactoring

Improvements
Proper privilege checks in procudures, events and triggers to enable editing, adding new ones
Various coding style and doc improvements

Sunday, August 3, 2014

Retrospect - continued...

If you missed the first part of this post you can find it here.

Last week I concluded the blog post telling you how my FOSS life began with phpMyAdmin. It was just after the GSoC and the team was preparing to release the version 3.4 of phpMyAdmin. There was a lot of testing going on, bringing a continuous stream of bugs. This was a good opportunity for me to get a good hang of the code. I fixed quite a number of bugs. None of the alpha, beta or a release candidate version went without a fix from me. Version 3.4 was released soon and it was quite fascinating to see people using what you have contributed.

I was still an undergrad and wanted to take part in the 2011 version of GSoC. Initially I wasn’t sure whether I could apply under phpMyAdmin since I was already a team member. However Marc Delisle, one of the project admins helped me clear my doubts and I chose to apply for project idea that was due for some time. The project was about supporting geographical data types in phpMyAdmin. My knowledge on the area was very limited, so I started doing my own little research. I tried out other database engines that had GIS support such as PostgreSQL and SQLServer and their clients. This helped me write a better proposal.

To my delight, the project got accepted. I was going to spend my entire summer with phpMyAdmin!!! Michal Čihař, the other project admin was my mentor. My project covered quite a lot of areas related to geographical data types in phpMyAdmin. It included providing an interface to enter geographic data, visualizing them, exporting them, importing from ESRI format for GIS data as well as searching with geographical data specific functions. This covered most of the areas of the phpMyAdmin code and by the end of the summer I was more confident with the code. GSoC was a great experience for me, getting to know my mentor, the team and community well.

2011 was quite an eventful year in my life. I graduated in December and started working. Of course this limited the hours I could put for phpMyAdmin, but I managed to contribute much during the weekends. Another very much looked forward to event was closing by. The team decided to meet in real life at FOSDEM 2012 in Brussels. All expenses covered visit to the best FOSS conference in Europe plus meeting the phpMyAdmin team!!! How cool is that!!! This was the first time I visited the Europe and this was an amazing experience to me. Subzero temperatures and snow was all new to me who is coming from a tropical country where the sun shines the whole year along. The conference was very rich with what it had to offer and it was a great opportunity for developing a closer friendship with my fellow team members. I did not forget to spend some time doing sightseeing in this beautiful country.

GSoC 2013 edition was another milestone in my Open Source life. I was asked by the phpMyAdmin team whether I would like to mentor a GSoC project. The proposal submission period was a one big rush with a lot of discussions going on in the developer’s mailing list and receiving a large number of patches. Students were digging into the code as phpMyAdmin required the potential candidates to display their familiarity with code by submitting patches for bugs or new features. Reviewing proposals was very interesting with proposals varying from students who did not even know what phpMyAdmin is written in to student who knew very well the product as well the their proposed projects.

I chose to co-mentor a project that was aimed at improving the testing mechanism and testing coverage of phpMyAdmin with Michal Čihař. It was indeed a very interesting project. It’s only when you are trying to write unit tests, you find the problems in the architecture. The student was willing to even improve the code by refactoring. He was enthusiastic about the project and quickly grabbed techniques like mocking for testing. It was a pleasure to co-mentor a student who’s enthusiastic and willing to learn. Even I learnt a lot on the technology front and from my co-mentor about mentoring a student.


So this is how my FOSS life started and evolved, thanks to GSoC. I’m contributing to the project to date and I never seem to get bored doing that. Since FOSDEM 2012 I have met the team members twice, once in picture perfect Switzerland and more recently in Germany. I have developed stronger friendships with my fellow team members and even visited them in their home countries and looking forward to receive them back in Sri Lanka. I am mentoring a GSoC project this year as well. All in all I find it very satisfying to being able to contribute to this great work.

Sunday, July 27, 2014

Retrospect

It was the year 2010 and I was into a 6 month internship which was a part of my Bachelor’s Degree. Somebody reminded me that the year’s GSoC program has been announced. Before I tell you anything more, I think I need to tell you about how GSoC was viewed by us at University of Moratuwa.

To us at University of Moratuwa, the GSoC was a big deal. By 2010, the University has topped in terms of the number of proposals accepted for a couple of consecutive years and everybody was so excited about the program. There was a strong motivation inside the university towards contributing to FOSS which encouraged students to apply for GSoC while the GSoC itself made people more aware about the FOSS world, creating a spiral effect. So all in all, winning a GSoC was something to be proud of at Mora.

So, determined to win a GSoC project I started exploring the project ideas. Let alone the project list, the organization list was so long. However, I found this interesting project idea in phpMyAdmin’s idea list which was about adding charts to phpMyAdmin. This was somewhat related to what I was doing at my internship. But I did not have any experience with FOSS communities. Communicating in mailing lists, going through code and documentation to understand the architecture, submitting patches was all new to me. But I did not have any choice; I communicated my interest on dev mailing list and started exploring the code. phpMyAdmin had a strict selection process where the students were required to submit patches to show their familiarity with the code. So the next logical step was to chose a bug from the bug tracker and submit a patch, which would have been little easier if I were more familiar with GIT, the version control system phpMyAdmin was using. phpMyAdmin wiki and other documentation was my savior, providing much valued insight into the project; I read every page of them.

It was very fascinating to see some of my patches getting accepted. Some patches received feedback from developers on how to improve and not so lucky patches got rejected. All in all it was quite interesting to contribute with phpMyAdmin project. I was enjoying the process very much. Then came the accepted list of GSoC projects for the year. Nope, you guessed it wrong, my project was not there! Someone else probably submitted a superior proposal. Was I sad? May be. But I was already enjoying contributing to FOSS.

After a couple of months, my internship had ended and I was back in the University. It was those days where you have more than enough time to do whatever you like. So why not fix a couple of bugs in phpMyAdmin! More interactions on dev mailing lists, more patches, more feedback and I was enjoying working with phpMyAdmin. Then came a surprise, an email from one of the project admins of phpMyAdmin asking whether I am interested in joining the phpMyAdmin team. I was over the moon, happily surprised, sent a reply saying yes. That is how my FOSS life began with phpMyAdmin.

To be continued …