search

openmainframeproject / software-discovery-tool

Software Discovery Tool
Apache License 2.0
32 stars 40 forks source link

Implements changes for a switch to SQL Database #97

Closed arshPratap closed 1 year ago

arshPratap commented 2 years ago

In response to #73 This PR has some work related with the following PR To reproduce the changes on your end :

Signed-off-by: arshPratap arshpratapofficial@gmail.com Co-author : @Princee215 princeesingh215@gmail.com

arshPratap commented 1 year ago

@Princee215 thanks for the updates .. can confirm that the commits that you have made our working correctly. Some of your commits from IBM-Z Validated have been added in as well... I will rebase them accordingly

arshPratap commented 1 year ago

@pleia2 would like to know your thoughts on this as well

Princee215 commented 1 year ago

@arshPratap, Thanks. Yeah, I saw IBM-Z Validated work got added too. It would be really helpful if you'll rebase it. Looking forward to working more on this branch.

pleia2 commented 1 year ago

I bumped into an issue because the src/config/supported_distros.py has a conflict in this repository, but once I resolved that I was able to move forward. Here's exactly what I did to get going:

Followed core instructions in Installation.md including Step 5 which will pull in the JSON files that we're setting up the database with. Only change made was the data source:

cd /opt git clone https://github.com/arshPratap/software-discovery-tool.git git switch sdtSQLBackend

Then installed MariaDB, the mysql Python library, and set up the server and user:

sudo apt install mariadb-server python3-pymysql sudo mariadb sudo mysql_secure_installation mariadb -u root -p MariaDB> CREATE USER 'sdtAdmin'@localhost IDENTIFIED BY 'admin1234'; MariaDB> GRANT ALL PRIVILEGES ON . TO 'sdtAdmin'@localhost; MariaDB> flush privileges; MariaDB> quit cd /opt/software-discovery-tool/bin/ ./database_build.py

And swiftly ran into:

elizabeth@ubuntu:/opt/software-discovery-tool/bin$ ./database_build.py Enter username to use for connecting to MariaDB server : root Enter password for connecting to MariaDB server : 1234qwer DB INITIATILIZED SUCCESSFULLY ZOS_Software_List formed successfully ZOS_Software_List : Entries filled IBMZ_container_registry formed successfully IBMZ_container_registry : Entries filled Debian_Buster_List formed successfully Debian_Buster_List : Entries filled Debian_Bullseye_List formed successfully Debian_Bullseye_List : Entries filled ClefOS_7_List formed successfully ClefOS_7_List : Entries filled OpenSUSE_Leap_15_3 formed successfully OpenSUSE_Leap_15_3 : No Entries found OpenSUSE_Tumbleweed formed successfully OpenSUSE_Tumbleweed : No Entries found OpenSUSE_Leap_15_4 formed successfully OpenSUSE_Leap_15_4 : No Entries found Traceback (most recent call last): File "/opt/software-discovery-tool/bin/./database_build.py", line 99, in db_init() File "/opt/software-discovery-tool/bin/./database_build.py", line 31, in db_init initall(dbName,username,password) File "/opt/software-discovery-tool/bin/./database_build.py", line 83, in initall createTable(db,SUPPORTED_DISTROS[os][distro],username,password) File "/opt/software-discovery-tool/bin/./database_build.py", line 72, in createTable curr.execute(query) File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 148, in execute result = self._query(query) File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 310, in _query conn.query(q) File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 548, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 775, in _read_query_result result.read() File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1156, in read first_packet = self.connection._read_packet() File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 725, in _read_packet packet.raise_for_error() File "/usr/lib/python3/dist-packages/pymysql/protocol.py", line 221, in raise_for_error err.raise_mysql_exception(self._data) File "/usr/lib/python3/dist-packages/pymysql/err.py", line 143, in raise_mysql_exception raise errorclass(errno, errval) pymysql.err.OperationalError: (1049, "Unknown database 'Fedora_34_List'")

Logging into MariaDB to poke around, I saw the following were created before this failure:

MariaDB [sdtDB]> show tables; +-------------------------+ | Tables_in_sdtDB | +-------------------------+ | ClefOS_7_List | | Debian_Bullseye_List | | Debian_Buster_List | | IBMZ_container_registry | | OpenSUSE_Leap_15_3 | | OpenSUSE_Leap_15_4 | | OpenSUSE_Tumbleweed | | ZOS_Software_List | +-------------------------+

Since I noticed above the OpenSUSE ones showed "No Entries found" I looked...

MariaDB [sdtDB]> select * from OpenSUSE_Leap_15_3; Empty set (0.000 sec)

Whereas selecting Debian_Bullseye_List returns 56158 rows in set, as expected.

Hope this helps you make a little forward progress before our next meeting!

My only comment so far is that once I am able to make it a bit further, we'll want to make sure we can create these databases under different circumstances, like a situation where it's a hosted MySQL environment and all we're providing is some endpoints, rather than localhost.

pleia2 commented 1 year ago

Looking better! Still having some trouble with OpenSUSE:

OpenSUSE_Leap_15_3 formed successfully OpenSUSE_Leap_15_3 : No Entries found OpenSUSE_Tumbleweed formed successfully OpenSUSE_Tumbleweed : No Entries found OpenSUSE_Leap_15_4 formed successfully OpenSUSE_Leap_15_4 : No Entries found

...but it turns out that's a problem of my own making. Can someone quickly tackle https://github.com/openmainframeproject/software-discovery-tool-data/issues/59 this and then we can test again?

rachejazz commented 1 year ago

@Princee215 @arshPratap Since this a quite large change, will it be possible for a demo recorded over https://asciinema.org/ or just screen recording? I see @pleia2 has run into some errors, are these resolvable without the dependent issue?

Some questions needs to be answered for me to review

  1. What is the source of the db? is it from the existing data files on the sdt server or will it do a fresh pull from OS servers?
  2. I see you have removed ".json" completely from SUPPORTED_DISTROS, have we verified if the db setup isn't a single point of failure?
  3. If something goes wrong after this is deployed on production (which I think is done automatically afair on -deploy repo), how do we ensure we're able to revert to old setup? Will restoring previous deployment (pre commit of db setup) work?

(a recorded demo of terminal will help a lot. I am not concerned about GUI, that can be fixed later as long as we are clean on the tui console)

pleia2 commented 1 year ago
  1. The source of the data files are the files on disk, so you need to pull in the data files before running the database script.
  2. In this new model, a database will be 100% required, we can't scale with the JSON format we have (It's quite slow. And I actually had to pull old sources from production to support all the distributions we've been adding, this is after making the VM bigger).
  3. Since we've already outgrown our JSON back-end, we're basically moving forward with this implementation and not having a roll-back plan. In production we can do something with an external monitoring service on the database application to make sure it's still running and returning responses.

I'm happy to run through the installation procedure, it's mostly taking my instructions above and merging them with the existing installation documentation. The errors I noted above have been fixed, so with the exception of openSUSE (we have other problems there), it's all working fine for me, and the new database back end is very fast! :smile:

arshPratap commented 1 year ago

@Princee215 @arshPratap Since this a quite large change, will it be possible for a demo recorded over https://asciinema.org/ or just screen recording? I see @pleia2 has run into some errors, are these resolvable without the dependent issue?

Some questions needs to be answered for me to review

  1. What is the source of the db? is it from the existing data files on the sdt server or will it do a fresh pull from OS servers?
  2. I see you have removed ".json" completely from SUPPORTED_DISTROS, have we verified if the db setup isn't a single point of failure?
  3. If something goes wrong after this is deployed on production (which I think is done automatically afair on -deploy repo), how do we ensure we're able to revert to old setup? Will restoring previous deployment (pre commit of db setup) work?

(a recorded demo of terminal will help a lot. I am not concerned about GUI, that can be fixed later as long as we are clean on the tui console)

@rachejazz currently @Princee215 is working on some changes and pushing them in soon and will add in the video soon (along with the blog perhaps) currently I built an old video that you can refer here