[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

Adding a count to a command line query

Here is a sample command line query:

E:\mysql\bin\mysql -urjohnsonjr -pMyHardPassword  -D%db% -h192.168.1.100 -P 3301  -e "delete from mlsv20master.mastertable where mlsnumber not in (select mlnumber from %db%.%mls%_allmlsnumbers) and mlsid=1;"  -v -v -v

Is there a way for me to add some kind of count to the query to make sure that %db%.%mls%_allmlsnumbers  has more than 1000 rows in it?

I was thinking about adding these types of queries to a stored procedure so I could use more logic, but it would be much better if I could add to the query or do something else right there in the command line query to check the count and not actually do the delete if something happens and the %db%.%mls%_allmlsnumbers is empty

What is happening is another part of the script fails  and the allmlsnumbers table ends up being blank so the delete statement wipes out all the data in the mastertable which is bad news.
1 Solution
Steve BinkCommented:
I have a few notes for you:

1) Using your password hard-coded on the command line is insecure.  Only marginally better is using the my.cnf file to store it for this client.  MySQL 5.6 introduces the .mylogin.cnf files, which are much better for security.  Upgrading MySQL (if necessary) is a cakewalk.

2) Deleting records should be a last resort type of scenario.  If you don't need the records, create a field for "is_deleted" and set it appropriately, or archive them to a separate table.  This small adjustment can save you from tons of grief when something unexpectedly goes wrong.

3) If you are running multiple statements with interdependencies (e.g., your delete query depends on the query before it), you should consider using transactions on the process.  This is never more relevant than when your process has the potential to be destructive, and is prone to failure.

4) It is very possible to retrieve a count based on the SELECT portion of your query: SELECT COUNT(*) FROM mastertable a LEFT JOIN allmlsnumbers b ON a.mlsnumber=b.mlnumber WHERE b.mlnumber IS NULL.  However, you cannot use flow logic in standard console statements.  You'll need to create a stored procedure to run the process, and call the SP from your command line.

Overall, using a stored procedure is going to be much better for you.  You can incorporate logic to handle sanity checks before anything is done, giving you the opportunity for error checking/reporting, and the ability to halt the process if anything unexpected is encountered.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now