Working with SQL Data

1. You are probably importing a database during an install, or possibly as troubleshooting for a data-related issue. It is imperative that you are absolutely sure you:

• Are connected to the right POS. You can be sure by checking through LMI, or by looking at the database name listed on the Help Tab.

• Have the correct version of SuperSalon installed on the salon’s PC. Be sure of this by checking the version listed on the Help Tab.

• Are importing the correct database! Confirm this by comparing the name of the file you’re using, to the Database Name field on the store’s admin page. It can also be confirmed on the POS via the Help Tab!

2. Confirm that you have the right type of data file, for the version of SuperSalon that the salon is running:

NOTE: SuperSalon revision patches are numerical, please notice above that the difference here is a single revision. This is an important fact! Trying to import the wrong data file type may not work or might cause serious harm to the data!

3. Before continuing to the next step, check that the file is more than 0kb. The size of the database will vary based on how long the store’s been in business and how much data they’ve actually input over time. This is to say that new stores will often have a very small data file, while older ones will be much larger.

Extracting the data and launching Windows Command Line (cmd)

1. Download and install 7zip if you don’t already have a file extractor on the POS.

2. Unzip the database. You may notice that it comes unzipped inside of it’s own folder, which is named after the database. You need to go into that folder to actually get the .sql file you will be using to complete the import.

3. Copy the .sql file and paste it directly into C:\ drive.

4. Go to the Windows Start Menu and type cmd into the search bar. Right click on cmd and run it as administrator.

5. Cmd should launch pretty quickly, after which you will be presented with a black box:

Importing the data file

1. At this point, you need to tell cmd which folder to look into so that it knows where to put the data when we run the import command. Do this by copying and pasting, or typing the following command and pressing enter:

• (for Version 5.6.5.13 and above)

  • cd C:\SuperSalon\mariadb\bin

• (for Version 5.6.5.12 and below)

  • cd C:\SuperSalon\mysql\bin

2. You should now see the directory listed on the black box has changed from C:\Windows\system32 to the directory you just pointed it to (the C:\supersalon\mariadb\bin folder).

NOTE: Windows is now ready to do it’s job of importing the data into the right place (mariadb\bin) with your next command.

3. Next, type the following but do not press enter yet:

mysql --default-character-set=utf8 -hlocalhost -uroot -pd60473d5c1780f934f23b3321b85dfee sspos < c:\

IMPORTANT:

In step 2 you told Windows which directory you want to work in (C:\supersalon\mariadb\bin), and in step 3 you are telling Windows-

• Which application you are using in the directory: mysql

• MySQL requires a host to be defined: -hlocalhost (the h stands for host)

• The database requires authentication: -usupersalon (the u stands for user)

• It requires a password: -p<mysql password from admin> (the p stands for password)

• Mysql needs to know the name of the table you are importing the data into: sspos

• Finally, Mysql needs to know where the data file you want to import is located (continue to step 4).

4. What you’ve done here is prepare your command to perform the import, and it’s just waiting for you to name the file you want to use! Since you put it in the C:\ drive earlier, you simply need to type the first few letters of the data file’s name and press the tab key so that it auto completes the file name for you.

5. Press enter, and wait. Depending on the size of the data file and the speed of the hard drive / solid state drive that the POS has, this can take anywhere from 1-3 seconds, to upwards of ten minutes. Be patient and do not close the window – If you do close it, you will need to wipe the install and start from scratch!

6. Once it’s finished, it’s really easy to not notice that it’s done – the line will simply jump down and wait for your next command. There is no indicator or message beyond that, so pay close attention!

Return to Step 4 of the Installing SuperSalon guide to continue the install!

Importing Data from a .CSV File

Instructions for MySQL5 and Newer

CAUTION: Do not, under any circumstances, perform ANY of the procedures shown in this or the next section without first backing up the client’s current database. The potential for irreversible damage is very real, and should not be taken lightly.

1. The customer will usually email the CSV they want imported into the database. If this comes as a.xls format, perform a “Save As” to convert it to CSV.

2. The CSV file will then need to be tailored to the particular import by removing unnecessary columns, and changing their associated headings. Basically, the column headings need to match the fields of the table you wish to import the information to. For the purposes of this article, we will be importing customer information.

3. The example below shows an import of customer data from CSV. Note how the information is kept as basic as possible. With customer information we only need the following:

   1. • Name

      • Address

      • Phone Numbers

      • Gender

      • Email address

      • customer-6-1Download

4. Log on to the local database (localhost/phpmysql or phpMysql) using the Root User credentials:

   1. • Username: root

      • Password: d60473d5c1780f934f23b3321b85dfee

5. Click on the “Customer” table, then click the “Import” tab in the upper part of the screen.

6. Look for the “File to Import” section, then click the “Choose File” button to browse and select the CSV.

7. In the “Format” section, select “CSV“. Be sure to leave the “Format Specific Options” set to their default values.

8. Where it says “Column Names“, list the name of the fields that are to be imported in the following manner (much like the example CSV):

   1. • firstname,lastname,address1,address2,city,state,zip,homephone,cellphone,sex,email

      • Now click “Submit“.

      • Note there are no spaces between the fields.

9. Click on the “Go” button, and then verify the data has been imported successfully to SuperSalon.

Instructions for Pre-MySQL5 Databases (Archived)

1. Follow steps one through four of the section above, making the same adjustments to the spreadsheet, and using the same login credentials.

2. Select the customer table, then click on the “SQL” tab at the top of the screen.

3. Click on the tiny link at the bottom that says “Insert Data from a Text File Into Table“.

4. Browse to and select the applicable CSV file.

5. In the section labeled “Fields Terminated By“, remove everything there (; be default) and put a comma (,). Remember, CSV stands for Comma-separated values.

6. In the area labeled “Column Names“, list the names of the fields you are importing like this:

   1. firstname,lastname,address1,address2,city,state,zip,homephone,cellphone,sex,email

   2. Then click “Submit“.

   3. Note there are no spaces between the fields.

7. Verify that all data was correctly imported into SuperSalon.

Exporting SQL Data

CAUTION: Improperly handling client databases can be a serious problem, especially if you fail to notice issues early on. Lost business, lost data, and an extremely upset customer (and rightly so) are a few of the possible consequences. Please check with a senior support agent or support manager if you aren’t sure. Double checking your work isn’t difficult – don’t forget!

Exporting MySQL 5/ Maria DB Dump Files for SysAdmin

1. Open a CMD Prompt with Start > Run > CMD, or other method.

2. Confirm the location as either c:\users\username\ or c:\Documents and Settings\user\. If different, change to the correct directory.

3. Enter the following text: C:\Supersalon\Mariadb\bin\mysqldump.exe –skip-triggers -usupersalon –add-drop-table -pgr3*7 -Q sspos > c:\backup.sql

4. Hit the Enter key.

5. Wait for the export to finish, you’ll know when it’s finished when cmd drops down to a new line.

6. Zip it up and escalate your ticket to the Level 2 queue.

Exporting Legacy MySQL 3 Dump Files for SysAdmin and Archives

1. Open a CMD Prompt with Start > Run > CMD, or other method.

2. Confirm the location as either c:\users\username\ or c:\Documents and Settings\user\. If different, change to the correct directory.

3. Enter the following text: C:\Supersalon\mysql\bin\mysqldump.exe –skip-triggers -usupersalon –add-drop-table -pgr3*7 -Q sspos > Desktop\sspos.sql

4. Hit the Enter key.

5. Wait for the export to finish, you’ll know when it’s finished when cmd drops down to a new line.

6. Zip it up and escalate your ticket to the Level 2 queue.

Development Mode

NOTE: As with any complex piece of software, SuperSalon can sometimes require advanced troubleshooting in rare instances where conventional methods and re-installs prove fruitless. Entering the POS into Development Mode is one such advanced method that will allow support agents to get a closer look at the inner workings of the POS in order to better glean the source of the issue at hand.

Getting into DEV MODE

1. Rename Autorun file to DEVELOPMENT_MODE, open SS.

2. You should notice a Red flashing Dev Mode tag in the top right corner.

3. When done, delete the file you renamed. Make sure not to leave system in Dev mode

Data Issues and Plugin Fixes

Getting Started

SuperSalon utilizes and SQL and Apache based database to function in its role as a point of sale system. All Rogers Software support technicians are presumed to have at least rudimentary familiarity of this programming language in order to better perform their tasks.

The database can be accessed im any POS that has the PhpMyAdmin folder within it’s ssproject directory. The default username for access is “supersalon”, and the password is “gr3*7” (without the quotes).

Plugins Tables by Version

Please see the phpMysql plugins table settings below. Different versions have different settings. Sometimes when installing the update for supersalon the plugins tables do not update properly and knowing these settings can be very helpful.

Version 5.6.x and Above

11 Appointments 4 ../app/index.php?controller=appointments 0.1 appointments 0

2 Setup 10 setup/setup.php 0.1 setup 0

12 Reports 9 reports/reports.php 0.1 reports 0

5 Sales 1 ../app/index.php 2.0 sales 0

9 Time Clock 6 ../app/index.php?controller=time_clock 0.1 timeclock 0

10 Manager 9 manager/manager.php 0.1 manager 0

13 Help 12 help/help.php 0.1 help 0

14 Messages 11 ../app/index.php?controller=message&action=messageLandingPage 2 messages 0

15 Scheduler 7 ../app/index.php?controller=employee_schedule 0.1 schedule 0

Version 4.5.x to Version 5.x Plugins Table

11 Appointments 4 ../app/index.php?controller=appointments 0.1 appointments

2 Setup 10 setup/setup.php 0.1 setup

12 Reports 9 reports/reports.php 0.1 reports

5 Sales 1 ../app/index.php 2.0 sales

9 Time Clock 6 ../app/index.php?controller=time_clock 0.1 timeclock

10 Manager 9 manager/manager.php 0.1 manager

13 Help 12 help/help.php 0.1 help

14 Messages 11 ../app/index.php?controller=message 2 messages

15 Scheduler 7 ../app/index.php?controller=employee_schedule 0.1 schedule

Version 4.41 Appointments Information

11 Appointments 4 ../app/index.php?controller=advappointments 0.1 appointments

Version 4.36 and Prior Plugins Table

11 Appointments 4 schedule/appointments.php 0.1 appointments

2 Setup 10 setup/setup.php 0.1 setup

12 Reports 9 reports/reports.php 0.1 reports

5 Sales 1 sales/sales.php 0.1 sales

9 Time Clock 6 timeclock/timeclock.php 0.1 timeclock

10 Manager 9 manager/manager.php 0.1 manager

13 Help 12 help/help.php 0.1 help

14 Messages 11 ../include/officemessaging.php 0.1 messages

15 Schedule 8 schedule/schedule.php 0.1 schedule (must be manually entered)

MYD (Month / Year / Day) Issues

CAUTION: From Mike Rigby the Great, First of his Name, King of Alt Coins, Zaguate Aficionado

“The MYD errors are very very serious and need to be handled with great caution. One step missed or not followed correctly or a failure to back up the right files at the time could wipe all data from a table and should only be handled by experienced techs.

The fixes below are last resort. 99% of MYD errors can be fixed now by logging into the DB, manually clearing session variables and doing a manual DB repair.

Again, if you do run into this and are not sure what to do, please ask! The loss of data is one thing we need to absolutely avoid.”

Fixing .MYD Errors Manually When:

• phpMyAdmin reported a table (using sales for this example) “in use”

• PHP errors referred to “can’t open file sale.MYD” or “can’t find file sale.MYD”

• Running the SQL command CHECK TABLE sale in the database presented the error “can’t open sale.MYD”

• The file sale.MYD did not exist in C:\SuperSalon\Mysql\data\sspos\; instead there was a sale.TMD file

1. Net stop mysql [what follows must be done while the database is not running]

2. C:\SuperSalon\Mysql\bin\myisamchk.exe C:\SuperSalon\Mysql\data\sspos\sale.MYI [Yes, MYI] This command produced a message to the effect of “table is usable, but should be repaired”.

3. C:\SuperSalon\Mysql\bin\myisamchk.exe -o C:\SuperSalon\Mysql\data\sspos\sale.MYI [Note the addition of the -o flag.]

4. C:\SuperSalon\Mysql\bin\myisamchk.exe C:\SuperSalon\Mysql\data\sspos\sale.MYI [No -o, just like the first time] No error or warning was output this time.

5. Net start mysql

After these steps, proceed to run the dbchecker and other utilities.

The above sequence of commands should allow you to recover from this same error if it comes up again, regardless of the table; simply replace references to sale to the name of whatever table you are having issues with.

*IF THE ABOVE STEPS DO NOT WORK PROCEED WITH CAUTION USING THE OLD METHOD BELOW*

1. Net stop apache > net stop mysql and go to C:\SuperSalon\Mysql\data\sspos

2. Read error message to see if you need to repair “transaction” or “ticket” files

3. Cut and paste “t****.MYD, t****.MYI and t****.frm” in to a safe place

4. Net start apache > net start mysql

5. Run supersalon from browser > localhost/include/rundbchecker.php

6. Net stop apache > net stop mysql.

7. Copy (Not Cut, this is your bakup if steps fail) and paste “t****.MYD back in to C:\SuperSalon\Mysql\data\sspos

8. Net start apache > net start mysql

9. In browser type localhost/phpmysql

10. Select all and repair database

NOTES:

• Possible Error Messages “Fatal error” “Error selecting SELECT Time In from ticket WHERE ticket ID =[1] Can’t open file Ticket MYD error no. 145. (or something similar) Fatal error: Error selecting: SHOW COLUMNS FROM transaction Can’t open file: ‘transaction.MYD’. (erno:144) in C:\SuperSalon\ssproject\include\dbclass.php on line 56

• If you skip the part about putting the ticket.MYD, ticket.MYI and ticket.frm files back in the sspos folder, you will get the following in the repair list/notes: sspos.transaction repair info Wrong bytesec: 0-0-0 at 2182176; Skipped sspos.transaction repair warning Number of rows changed from 66535 to 66530

• After putting back ticket.MYD and MYI and frm all is good except you might find that sspos._options repair status OK is highlighted in red.