Problems Viewing MySQL Query Results

Question

When running a query from the mysql> prompt the results are sometimes to big to view in the mysql cmmand window

I see the results come up as they come through and they scroll down but on completion I cant scroll up to view the results

eg If I run a script for todays racecards I see all of them come out but then can only view the latter Perth Punchestown and Warwick cards for today but none of the alphabetically earlier ones like Beverley even though I know they were extracted from the database

Any suggestions on how I can overcome this? It just seems a little too inconvenient to run the queries for one or two meetings at a time

Hope I have explained this properly

Answer 1

It sounds like you're describing the command line interface within DOS or a shell.

There are other ways of connecting with MySQL that give you much more control over the results of queries - being able to view them in spreadsheet like tables, with the ability to sort results and also export them easily to a spreadsheet program like Excel.

For free tools:

If you're on a Mac, we recommend Sequel Pro - https://www.sequelpro.com/

If on Windows, try Heidi SQL -  https://www.heidisql.com/download.php

Or for a cross platform (Windows, Mac, Linux) paid tool, try Navicat (they also offer a free trial) - https://www.navicat.com

Comments

Unfortunately when I try and set up HeidiSQL or SQLyog I cant get past the front page/panel as I keep getting error messages of:

Authentication plugin 'caching_sha2_password' cannot be loaded

and this happens for all combinations I use for fdatabase name and location and user etc etc and despite going through all the support documentation havent found a way to link it to The Smartform Database - yet :-( :-( :-(

---

Am not using HeidiSQL at the moment so can't comment - however, a review of their help Q&A or posting to help should reveal the potential sources of this error.

In the interim, suggest you try the Navicat free trial for 14 days and see how that goes.

We'll also look at doing a blog post that shows how to set up a connection for all 3 of the tools recommended above.

---

Hi, I think perhaps you are using MySQL 8? Apparently HeidiSQL has not yet been updated for the new MySQL 8 password scheme:

https://www.heidisql.com/forum.php?t=25220

The above posting mentions a possible strategy to fix it (using the mysql native password scheme).

I imagine the same problem (and the same mitigation) affects SQLyog.

However, you should be able to connect using the latest MySQL workbench:

https://dev.mysql.com/downloads/workbench/

---

I am just so Techo useless its awful

Downloaded Navicat and went to make a new connection to The Smartform Database and got the exact same error as with the other MySQL access software

Going to scour their help files now and see what might be causing the problem but am now wondering f its anything to do with password security problems like I experienced when installing MySQL and the database and fetch updates

---

You might want to try the solution suggested in that HeidiSQL forum post, which involves logging in to the main 'mysql' database as root and then changing your password so it uses the older mysql native password scheme, something like:

ALTER USER 'user'@'host' IDENTIFIED WITH mysql_native_password BY 'YourPassword';

---

Failing that, do try MySQL Workbench which I would imagine must support this:

https://dev.mysql.com/downloads/workbench/

We don't currently have a MySQL 8 install to test against (and certainly weren't anticipating problems like this) but this is something we will look at to make sure our advice is up to date.

---

Thanks Mike...I am using MySQL8

Off to scour the link and support forums

---

OK so now I have MySQL Workbench v 8 up and running so all that is left now is for me to learn how to use it effectively

Many thanks to both Colin and Mike for all your assistance and apologies for my significant ignorance on all matters related to SQL databases

---

Oh no worries -- this one was new to me so it was good to find that out!