I want to practice some advanced update queries for my ticket rules, but need to get the syntax right.  I downloaded MySQL and have a local server running.  How do I copy the data from the K1000 to the local MySql without exporting to excel and then reimporting locally.
5 Comments   [ + ] Show Comments

Comments

  • Hi Jordan,
    Are you using MySQL Workbench? If so you should be able to use MySql workbench and directly connect to the K1000 and test your queries.
  • In theory is possible to restore manually a backup of the K1000 database on another MySQL database but then I do not know how much this can be useful.
    The syntax for update queries sometimes contains not pure SQL but they may contain some ''macros'' that are interpreted from the K1000 on the fly.
    Anyway if you go to settings -> control panel -> backup settings you can download the .gz file under the backups section.
    This contain the zipped backup of the DB.
    I'd suggest to have a look to the following article that contains some suggestion about the ticket rules:
    https://support.software.dell.com/k1000-systems-management-appliance/kb/111161
    Kind regards,
    Marco = StockTrader
  • Thanks Stock. I will give the backup a try.

    Mary, I do have Flyspeed and MySQL Workbench, but I am trying to make an Update query that is more complex than the built in wizards allow. For this I need a copy of the DB that I can test it on before I implement it in the K1000
  • What is the OS of the test machine running MySQL ?
    You will need to grab the latest 2014xxxx_k1_dbdata.gz from your K1000 and unzip on the test machine
  • I downloaded MySQL last night and installed it on my Windows 7 x64 machine. Seems to be running OK, but I cannot import the database I extracted from the downloaed .GZ file. I get an error when trying to restore the file with MySQL Workbench 6.2 :
    ------------------
    ERROR 1071 (42000) Specified key was too long; max key length is 1000 bytes
    ------------------
    I am not a MySQL guy so I have no idea what to do next.
    • Here is our procedure to import the DB.

      To bring in DB: log into mysql using ./mysql -u root -p
      then execute the following:
      source <path_to_backup_file_from_kace>

      Works for us.
Please log in to comment

Answers

1
You should be able to install a test copy of a VM KBOX and then restore a backup to that and test out queries that way.
Answered 12/15/2014 by: h2opolo25
Red Belt

  • I was doing that, but when I mess up the data it takes too long to restore the copy of the VM again. That is why I want to do the db copy route.
Please log in to comment
1
You could try this but it is MS Access

Use MS Access to pull down the KBOX DB, you can also set it to update data or you can do it manually. 
Then you can try building update queries. SELECT, UPDATE..the basic commands all work the same when trying things out. 

Make a copy of the access db so if you mess up...just over wright it and you are good to go. 
Answered 12/15/2014 by: nshah
Red Belt

  • how to do?
  • The syntax between Access and MySQL are too different. Once I put it in KACE I have to be absolutely correct or else I can mess it up. I really need a copy of MySQL
Please log in to comment
Answer this question or Comment on this question for clarity

Share