Skip to content

MySQL

MySQL stores and queries records in SQL tables. A shop can keep the same part number for two suppliers while preventing a duplicate entry for either supplier.

Visit MySQL

When does MySQL fit an app that keeps stock?

Start by checking what your existing app expects. Its database connection library and table definitions may already use MySQL. In a parts list, the app can look up a part by supplier and part number and update the stock count when it arrives.

InnoDB is the default part of MySQL that stores tables. It supports transactions, which let your app save a group of changes together. For example, the app can save both a new stock count and the note explaining it. If either change fails, the app can roll the group back so the count and note stay in agreement.

How can you check that MySQL treats two suppliers separately?

Trace the example on paper or use a private test server and a new empty practice database. Before writing anything, confirm the server address, database name and test-only login. Leave any live inventory, orders and customer accounts disconnected. Keep the test on paper if you cannot verify the target.

  1. Give the table a rule for the pair
    Create an InnoDB parts table with a supplier code, a part number and a stock count. Require the first two fields and add a unique index over them together. That tells MySQL to allow the pair only once.
  2. Save a part and try a duplicate
    Save part A17 from supplier NORTH with a count of two. Try a new row for the same supplier and part number with a count of five. The second save should report the duplicate. Check that NORTH’s count is still two.
  3. Reuse the part number for a different supplier
    Save A17 for supplier SOUTH with a count of one. Use a fresh read to confirm both intended rows and their counts. If a reply is unclear, read the stored row for that supplier and part before you retry.

Who runs MySQL and who can change the inventory?

The app needs a MySQL server, a way to connect to it and a database account that can do its work. Keep the login in the server-side app, grant only what the app needs, and let the app decide which staff members can change inventory.

When you connect to a remote server, check the host’s settings for encrypted connections and how the client verifies which server it reached. If several copies of your app update the same stock, point them to one durable database.

MySQL offers Long-Term Support and Innovation release tracks. Check which track your host and app support, and rehearse upgrades on a separate test copy. Confirm the inventory view and one dummy write still work before you plan a live move.

What can you run yourself and what can you pay for?

MySQL Community Server is the downloadable GPL edition. Running it yourself means taking care of the machine, storage, updates and backups. Oracle sells separate commercial editions and operates HeatWave as a managed service.

For a hosted setup, compare what the provider charges for compute, database storage and backup storage. HeatWave’s Oracle Cloud pricing lists compute separately from storage capacity. Confirm which tasks the host takes on, including upgrades and recovery, before comparing it with a self-managed installation.

What should you check before relying on a MySQL dump?

mysqldump can produce a SQL file for a selected database. For practice, use only the sample dump from this exercise. Before loading it, verify the address, database name and test-only login of an empty, disposable MySQL deployment separate from your live server. Keep live data and services disconnected. Stay on paper if you cannot confirm the destination. Load the sample in the verified destination and check both suppliers’ A17 entries. Try the duplicate again to confirm the restored table still enforces the rule.

Check which table types are in use. The single-transaction option gives a consistent view of InnoDB tables, and changing table structure during the dump can break that view. If you use stored routines or scheduled database events, check the options that include them. Confirm the account running the dump and the restore has the required access.

Plan separately for user grants, server settings and files your app stores elsewhere. Restoring to a time after a base backup requires the right binary logs, MySQL’s history of changes since that backup. Check what your host retains and try a recovery on an isolated copy.