Software to open and edit .MYD, .MYI, and .frm files

Topic

New Reply

I last played with database files almost 30 years ago, in the days of DOS, so I need to state right up front that I am totally unfamiliar with MySQL and its file structure. However, I need to find some basic, simple, and preferably free software to allow me to open and tweak a couple of settings in SimpleInvoices. I don’t want to become expert in MySQL, nor do I want to spend a lot of time at this. I simply want to find a way to be able to adjust a couple of things to make the SimpleInvoices program work better for me.

One example is discussed here. Another is discussed here, with such advice as, “if you really don’t like them open the database and delete all the contents from the si_invoices and si_invoice_items tables.” That instruction is probably a no-brainer to a MySQL pro, but not to me, and I don’t want to become one. I just want to tweak a couple of things and then leave MySQL alone for the rest of my life.

If anyone can suggest a simple and cost-effective way for me to make a few tweaks such as these, I’d be most grateful.

Cheers,
Al

Reply | Quote

Replies

MySQL is a client server database management system and with such a system, you really don’t edit files. Typically such systems support interaction through tools that allow you to run SQL both to manipulate data and the manage the database structure, though most of them also include tools that support some operations through a graphical user interface.

For MySQL, the only tool I know well enough is a paid one, SQLYog. MySQL itself includes the MySQL Workbench, which is supposed to be a GUI tool that allows the management of MySQL databases. It has a Community edition that is free. I have absolutely no idea how good MySQL Workbench is, but I can, of course, vouch for SQLYog, which is the tool I use whenever I need to perform operations on MySQL databases, something that can happen at least a few times a week. I have used it for years.

If you have no experience with MySQL, it may be hard to make sense of a tool to interact with it. A relational database, especially without a user friendly GUI that most do not offer, is not your regular app. The SQL statements to delete the contents from a table is rather simple,though and you can try executing it from :

Code:

DELETE FROM TableName

HTH

Reply | Quote

Hi, Rui,

Thanks for your prompt response. I have to say that your statement that “with such a system, you really don’t edit files” left me puzzled, as surely any database stores records in some sort of file somewhere. On reflection, however, I decided that maybe I had asked my question poorly, so I’ll try to restate it.

I’ve downloaded and set up the Windows-based version of SimpleInvoices (SI). Within SI, it’s possible to set up multiple billers and customers, as well as invoice styles, then create invoices from those records. Clearly, the list of, for example, customers (or products, or whatever) has to be stored somewhere. If we no longer will be doing business with a customer (or selling a particular product) and want to delete that customer or product record from SI, there is no “Delete” function within the program to do that, though there have been several requests for such a Delete function in the SI forum. The only advice given to those who want to delete such records is along the lines of “open the database and delete all the contents from the [XXX] and [YYY] tables.” I’m trying to figure out how to do that.

From what I’ve been able to find on the Internet, it appears that there were similar requests for the ability to delete invoices which had already been created, but there was resistance from the developer initially to adding a Delete function for invoices because deleting invoices runs contrary to the principles of accounting. However, there apparently was enough demand from users that a Delete function is now available for invoices in the current version of SI. However, there is no such Delete function available to remove inactive customers or products from the database, and that is the first thing I’m trying to achieve.

Also, if I want to change the starting number for invoices, SI gives instructions which talk about altering data in tables, as shown below:

The second thing I want to achieve is to be able to alter the starting number that way, but I cannot find a way to locate and edit that table.

I hope that’s clarified what I’m asking, and I likewise hope that I’m not missing something incredibly obvious here, but I can’t figure out A) how to delete existing records in the database, and B) how to change the starting number for invoices as SI directs.

Thanks in advance for any additional insight you can provide.

Cheers,
Al

Reply | Quote

You do not want to attempt to directly edit database files. Use a SQL tool. Check out the MySQL Workbench. It has been several years since I used it but IIRC it was OK. Not difficult to use.

Joe

--Joe

Reply | Quote

One of the goals of a “modern” database is to ensure complete transparency regarding the physical database files. In a relational database this is achieved by the “database software”, by allowing a user / programmer to see the data in the database as a set of logical records. The user / programmer interacts with the database adding, editing or deleting records, through a language created specifically for that purpose – Structured Query Language (SQL), regardless of how the database management software deals with the instructions issued using SQL. In a real situation, a SQL statement usually affects multiple files – in the simplest of scenarios, just the file that keeps the table records, but in a more realistic scenario likely more files – a file that keeps the database records, a file that keeps a log of all database operations and one or more index files. It’s even possible that other files are involved. All this varies depending on the database management system in use, the way the specific database was configured by the person or the program that created it.

What your attachments show are SQL statements – one delete and one insert statement. The delete, to delete all records from the si_index table, the insert to add a new record to the same table.

SQL Workbench will allow you to run the SQL statements you need.

Reply | Quote

Hi, Rui and Joe,

Many thanks for your helpful replies, but I think that this is all starting to look like trying to kill a flea with an elephant gun. All I want to do is to delete an occasional customer or product record and to start my invoice numbering at a different number, but having to download, install, and learn a piece of database management software just to make a couple of tiny tweaks seems like overkill. I can do a workaround by editing unneeded customer or product records to reflect current customers or products, and if I have to start my invoice numbering at the program’s default number, well, it won’t be the end of the world.

I had hoped that there would be a quick and simple way to delete unneeded database records and reset the invoice numbering, but going the SQL Workbench route seems hopelessly time-consuming and complex when compared with a 5-minute “deletion by replacement” of a handful of customer or product records. The quick workaround may not be ideal, and I may have to live with default invoice numbers, but at least I’ll be able to get back to business quickly and be productive.

I really appreciate your time in responding and your excellent advice, but unfortunately I need to go with the quickest and simplest solution, then move on.

Again, many thanks for your help.

Cheers,
Al

Reply | Quote

Well, Al, I am not sure what you would think you would get, but I don’t think SQL Workbench can be very far from it. It’s just an app that allows you access to a database using a graphical user interface. There are other options for the job, maybe have a look at this list:

http://www.databasejournal.com/features/mysql/article.php/3880961/Top-10-MySQL-GUI-Tools.htm

The comments also include other interesting suggestions and there are many free options.

Reply | Quote

Well, Al, I am not sure what you would think you would get, but I don’t think SQL Workbench can be very far from it. It’s just an app that allows you access to a database using a graphical user interface.

Well, Rui, for starters, when I went to the SQL Workbench download page, it gave me the following prerequisites:

To be able to install and run MySQL Workbench 5.2 your System needs to have libraries listed below installed. The listed items are provided as links to the corresponding download pages where you can fetch the necessary files.

Microsoft .NET Framework 4 Client Profile
Microsoft Visual C++ 2010 Redistributable Package (x86)

I don’t have either of those on my machine, so that means I’m already going to be up for downloading and installing three pieces of software to fix a minor problem that I can work around or live with. When I add to that the fact that most of the MySQL web pages seem to be written in a vocabulary straight from Mars or Pluto, it just seemed to be an unnecessarily complex road to go down merely in order to get rid of a handful of unwanted customer records and to reset the starting number for invoices.

That said, however, I really appreciate the list of alternative database tools you sent along, as it includes other free options, including phpMyAdmin. Not only does phpMyAdmin present itself in a less intimidating manner and without demanding the other programs as prerequisites, it is also the tool mentioned/suggested by the SimpleInvoices developer in the SI forum discussions on deletion. I may give that a try and see how it goes.

Thanks again for your always-excellent help.

Cheers,
Al

Reply | Quote

Al:

If the database files were set up as part of a database environment, then there will be certain linkages and dependencies between the various database files. The original program managed these dependencies in a consistent way, so that if data were added or deleted from the database system, all appropriate records were correctly edited / deleted in all appropriate database files.

If you manually delete records from one of the database files without making the corresponding edits in all related files, the appropriate records would end up with orphaned entries.

I’m not saying that you can’t do it manually, but you need to be aware of this possible / probable effect of manually editing these files.

Jim

Group "L" (Linux Mint)
with Windows 10 running in a remote session on my file server

Reply | Quote

I’m not saying that you can’t do it manually, but you need to be aware of this possible / probable effect of manually editing these files.

Hi, Jim,

I appreciate your note of caution, but I am assuming that the instructions given in the forum postings by the developer regarding what to change and how to change it (referenced in Post #3, above] are complete and safe to follow. Also, I am only in the early stages of setting up this new invoicing software, so even if I should happen to make some major mistake now and kill the whole thing, it wouldn’t be a disaster, as I can just rebuild from my last backup. Nevertheless, I’ll take your warning to heart and do my best not to mess things up if I decide to give all this a try.

Thanks for your help.

Cheers,
Al

Reply | Quote

You’re welcome, Al.

Reply | Quote