Looked at diff, emailed Mark Grennan to ask him to accept it into mytop upstream.

As i sent on the maria-developers mailing list i attached a patch to fix the overflow when the "fullqueries" option is activated and full queries are taking more than a line that might be resulting in a page overflow (the informations shown on mytop will exceed the terminal height).

I created a similar task for innotop as its still maintened upstream while the version packaged with the packaged version of MariaDB isn't.

innotop tracker : MDEV-4537

I fixed another annoying bug of mytop, as mytop strips newlines and carriage returns from the queries, it was making some queries to have "merged" elements, for example a query with "SELECT\nsomething" would be shown as "SELECTsomething" which is particularly annoying when you want to simply copy/paste the queries.

With the patch, it replaces these newlines/carriage returns with a space, knowing that just after, the repeated spaces are collapsed it shouldnt pose any kind of issues or weirdness.

Here is the patch (which is mainly consisting on adding a space on the substitution) : https://github.com/jb-boin/mariadb/commit/7af10eb21f1a78a640c2f294a2d3a3b38828d30e#scripts/mytop.sh

Or the latest revision of my mytop (as of today, its the MariaDB one plus the fullqueries overflow patch and this one) : https://github.com/jb-boin/mariadb/blob/5.5.30/scripts/mytop.sh

A small upstream bug, when you are on the "Query Summary" view, the Com_* status variables are read using this query : SHOW STATUS LIKE 'Com_%' which also outputs the Compression variable (which is not numeric) as "" matches any char and not only "".

The resulting error message (which can be seen on the Query Summary page when backlogging) :
Argument "OFF" isn't numeric in addition at /usr/bin/mytop line 1421.
Argument "OFF" isn't numeric in subtraction at /usr/bin/mytop line 1428.

The fix : replace Com_% on the query with Com
_% to match only the Com_* status variables (the second slash is to escape the other one on Perl).

ps: the mytop on my Github now has the fix.

I hit a problematic bug that is probably from upstream : when some queries with special chars are ran on the server, mytop ends up acting like if a key was pressed and switches to the query summary view (sometimes it also triggers the help page).

I verified it on two different servers with the same kind of queries running on them (the same app is doing queries on these servers) while i couldnt reproduce it on other servers.

This is definitely a potential security issue as crafted queries might manage to execute commands on the client running mytop.

I havent reviewed the code yet but the problematic queries might have antislashes and control chars on them, a better sanitization of the datas retreived from the server should suffice but it doesnt explain why/how a query shown could simulate keystrokes to readkey.

ps: it also might be a terminal bug (running on xfce4terminal in my case), i will try to run mytop on a different terminal to rule that out.

ps 25/06: the same issue appears on Putty ran from Windows but it act like if it was the "h" key that was pressed while its usually the "c" key press that happens with xfce4terminal.
I couldnt reproduce so far the issue with the Debian Squeeze mytop package 1.6-6 (as i havent found a reproducible testcase i cant really confirm that it doesnt happens on this version).

This code is present in the Debian version and not on the MariaDB one and is most probably the solution to the bug : $thread->

=~ s/[[:cntrl:]]/?/g;

Trying to reproduce/find the root of the bug encountered on the previous comment i hit a new one, on a slow server with many running queries, the sorting of the queries by the Time column is not working correctly, most of the sorting is correct but at some point, older queries are sorted after newer ones (but still in ordered fashion) and sometimes a line is "moved" (leaving a blank line) somewhere in the middle of the page (not left aligned as every other lines).

Here is an example : http://plusquenet.net/maria/mytop-sortbug.txt

ps: this is an upstream bug, it is existing as well on the mytop version shipped with MariaDB 5.5.31 (that does not includes any of my modifications) and on the mytop shipped on the official Debian Squeeze package version 1.6-6.

I updated the mytop on my Github, this commit add/change these :

• Control characters (except \n, \r and \t) on queries are now stripped to avoid to possibility to pass commands to Readkey from a query (it should fix the bug i states on my comment from the 22jun2013).
• A message "Truncated query list" has been added at the bottom of the page if not all the queries are shown on the page.
• The test that checks if the query cache is enabled has been changed from "if ($_-> {Value} eq 'YES' or $_->{Value}

  eq 'DEMAND')" to if ($_->

  {Value}

  ne 'NO') in case other query cache type are added in the future.

Feel free to tell me if you think that it would be better to replace the control chars with an "?" (like the Debian version does) instead of stripping them.

The commit: https://github.com/jb-boin/mariadb/commit/134cf8956686e68fd3cd7529b7006ea8b91ff492

The next modification i will try to implement is to retreive the process list from the I_S instead of parsing the output of the "SHOW FULL PROCESSLIST" command like innotop is doing (when available).

This could simplify the truncation of long queries and makes it easier to add columns with informations that could be retreived from the I_S.

I did made the modification to use INFORMATION_SCHEMA.PROCESSLIST instead of SHOW FULL PROCESSLIST output (when the server has it) on this revision : https://github.com/jb-boin/mariadb/commit/c2ac2971274008fae443b2d7784cdf344d7a2b22

It also adds the support for the TIME_MS when available : the time now shows 1/10 of seconds for the queries run time.
Anothe small modification it that the query from mytop itself to get the processlist is now excluded from the query list.

The next thing to do will be to implement "human" time values, instead of showing thousands of seconds, show time in minutes/hours/days to make things more readable and avoid too long values that would break the layout.

I corrected a small issue with the time_ms introduced on the latest revision : when time in seconds was more than 4 digits long it would overlap on the % column and the additionnal chars wouldnt be removed from the Query column and would result in the query printed in two lines instead of one.

As a simple fix i just made the queries >9999 secs not to show the 1/10th of seconds.
The Time column is still overlapping on the % column is the Time would be longer than 6 digits long but its behaving the same as before and it does properly crop chars on the Query column so it wont use two lines for the output.

Here is the file : https://github.com/jb-boin/mariadb/commit/962e0bbf5b6f0a19fbc5a1212641ff47463c0efe

The progress column on SHOW PROCESSLIST and I_S.PROCESSLIST are not identical, i must modify mytop to take that in account.
As the I_S.PROCESSLIST also have columns for STAGE and MAX_STAGE, its progress column will show the progress for the actual stage and not the total completion of the operation, for example on a 2 stages operation such as an ALTER TABLE ENGINE, while at 80% on the first stage of the operation, we will have these values :

On I_S.PROCESSLIST :
------------------------

------------------------

------------------------

On SHOW PROCESSLIST :
----------

----------

----------

The SHOW PROCESSLIST value seems to be something like : (STAGE-1)/MAX_STAGE +

/MAX_STAGE.

So the question would be : should I implement a similar calculation as on SHOW PROCESSLIST or keep only the actual STAGE completion or should I add informations about the STAGE on the mytop output for operation with more than one STAGE.

I should add an option to display/hide the completion of the queries (and possibly a version with the informations about the stages) as it takes space on the screen but isnt useful on all workload (there isnt much use of it on a server doing quick well-indexed queries for example).

Another detail to "fix" is that the help page show this option : # - toggle short/long numbers (not yet implemented)
While according to the code, the "#" key is used to activate the debug mode.

An update to the embedded POD documentation might also be good as there are some new functions and output.
And should the POD format be dropped in favor of a classical man page for the packaged versions as perl-doc isnt shipped by default on many distros?

New revision : https://github.com/jb-boin/mariadb/commit/468c3508960ae78b7f062ad9b5da9c5b7721be40

• Added dynamic column width for the User and DB columns (can be either set by configuration on ~/.mytop with usercol_with=X and dbcol_width=X or by argument at execution with --usercol_width X and --dbcol_width X or by pressing "w" key while mytop is running). The columns cannot be completely removed (yet) but can be set to a minimum width of their header width (4 and 2 chars).

• Progression display is now showing the actual stage and max_stage at the beginning of the state column on queries with more than 1 stage.

• The first letter of the queries (Info column) is now uppercase for a better readability when long string are in the "State" column as there is only a space between the two columns.

Putting the first query letter uppercase is not be the best way to go but it is the most simple fix at the moment, i will try to make the State column variable in width such as the User and DB ones and try to see if putting the Time and/or % columns between the State and the Info columns would be better in readability without having to add spaces between the columns.

I will try to add the possibility to completely hide columns and maybe add a removable one for the progress/stage/stage_max.
Another thing to do would be to show something else than 0.0% of completion on queries where it is unrelevant.

Another idea would be to put some columns on a variable width, to resize the column to the longest value width if shorter than the column max width to spare some space.

New revision : https://github.com/jb-boin/mariadb/commit/e31296deacfd0f735c943728c959cb274c7c4e0d

• New option to hide the progress "%" column when the server doesnt have progress information (MySQL for example) or dynamically using a keystroke (key a) or putting hide_progress=1 in ~/.mytop

New "major" revision (1.99-maria2) : https://github.com/jb-boin/mariadb/commit/97727315b8ea28926da3ace0441b6e030e4c384e#diff-e90d6da09b3da809d24682974d28cc8c followed by a small fix (no version change) : https://github.com/jb-boin/mariadb/commit/e4dfc4e76d6787920298ff27b1057288561b263d

In this revision, I merged most of the patches and changes from the latest Ubuntu version of mytop (1.9.1-2 from the 29 Jul 14 : https://launchpad.net/ubuntu/+source/mytop/1.9.1-2).

The changelog :

• The version string displayed on the first header line is now of the form "MariaDB 10.0.15 on localhost" instead of "MariaDB on localhost (10.0.15-MariaDB-1~wheezy-log"
• Now reading the "database" and "password" options on conf files if "db" and "pass" are not set (from Ubuntu)
• Not using the "test" database by default anymore (from Ubuntu)
• The display of the load average has been put back but with an additional check that /proc/loadavg exists and only the 1/5/15min avg values are shown unlike upstream which outputs the raw /proc/loadavg.
• Not loosing a line in height anymore when the Qcache is disabled (from Ubuntu)
• Only keeping the hostname part of the DNS on the thread host (from Ubuntu)
• Better spaces/control chars/newline replacement/stripping order
• Newlines on queries passed to Execute() are now replaced with a space (from Ubuntu)
• Better alignment on the header for qps/now and Hits/s (from Ubuntu)
• Many documentation fixes (from Ubuntu)
• Many typos and indentation fixes

–
The main differences with the version shipped by Ubuntu (except the additional features and bugfixes) are :

• The Ubuntu version uses "Config::IniFiles" to read config files such as ~/.my.cnf while the MariaDB version uses "my_print_defaults".
• The Ubuntu version uses the key "S" to activate the "SHOW STATUS" mode while the MariaDB version uses "M".
• Control characters on queries are replaced by "?" chars on the Ubuntu versions while i strip them (to avoid possible problems if queries from mytop are directly copy/pasted)
• The Ubuntu version get the major release version by doing a substring of the first char of the version string which will be 1 for MariaDB 10+ which makes mytop to use "SHOW STATUS" instead of "SHOW GLOBAL STATUS" as it test if "$db_release > 4".

–
The second revision of the day fixes a small display bug that i didnt spot before because it was requiring that the version string was smaller than the maximum allowed which wasn ever the case on my test servers using debian packages version which have a long version string.
That the load/uptime part of the first line of the header was not fixed at the absolute right of the screen if the total length of the "host" part (which includes the type of server and hostname as well) of the first header line was less than 50 chars, the length of the "host" part is now calculated and not just assumed to be of 50 chars.

New "minor" revision (1.99-maria3) : https://github.com/jb-boin/mariadb/commit/8c72d7235c213955f947744a051fd2027c262ba3

In this revision, I mainly moved tests for server capabilities from the "main loop" to the beginning of the script so they are only executed once.

The changelog :

• The tests that checks if the server has INFORMATION_SCHEMA.PROCESSLIST, TIME_MS and STAGE support on the PROCESSLIST from the "main loop" that is executed at every refresh of the thread view to the beginning if the script to be executed only once.
• The "GetQcacheSummary()" empty and unused function has been removed.

Pushed in 10.5 with commit c454b8964c10301d.
Still we should document changes in this regard.

anel Could you take downstream patch https://salsa.debian.org/mariadb-team/mariadb-10.4/-/blob/master/debian/patches/mytop-merge_src:mytop_improvements.patch and apply if upstream? It does not apply cleanly, somebody needs to decide which changes to take and how, and test that it works.

In https://jira.mariadb.org/browse/MDEV-22552?focusedCommentId=155100&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-155100 Serg decided we shall keep shiping mytop and maintaining it, thus it would make sense to take in that patch and ensure our mytop if of the latest and best version.

Seems most, but not all, changes carried in the Debian packaging repository to improve mytop were applied upstream and manually reviewing all the ones that were not applied (and deciding if they are needed or not) was too much work, so I simply dropped the whole patch in https://salsa.debian.org/mariadb-team/mariadb-10.5/-/commit/cb2a1609e5693a79c541724798e434c8349d65f0.

Hopefully somebody else has time to review https://salsa.debian.org/mariadb-team/mariadb-10.4/-/blob/master/debian/patches/mytop-merge_src:mytop_improvements.patch and salvage any changes from there that still seem relevant.

Related Debian bug about the wish for somebody to maintain (again) mytop as a separate project/package: https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=863968