Archive for December, 2021
macOS 2021+ DirectX
Obsolescence is always a factor with the macOS. After all, it does keep the revenue flow stable in hardware sales. Last October, Apple released macOS Monterey (12). It gets problematic for me when helping my students setup MySQL on their Apple computers. Some students come with very old machines. Take for example my wonderfully maintained MacBook Pro (mid 2014), which became obsolete with the terminal release of macOS Big Sur (11.6.2). When I did finally upgrade to that terminal release, I found my VMware (11.5.*) failed to load with a “Not enough physical memory is available …” error dialog.
The pretty Apple Dialog box is quite misleading unless you place it in context of a VMware application problem that occurs when you upgrade from one to another version of the macOS. You might go down any of three provided generalized rat holes, like paring running applications or the other nonsense on the Apple Discussion Board. While these three suggestions are useful when you’re macOS is truly running short of memory, it appears the same error can simply mean the Application isn’t supported on the new macOS release. It strikes me that this error message may be linked to a VMware virtualization issue with Hyper-V memory management with a combination of VMware (11.5.*) and macOs Big Sur (11.6).
For that reason, you can’t believe discussion threads unreservedly whether they’re from Apple or other vendors because they’re targeted to a universal context. Often users are looking for a specific fix, which means an answer to a specific use-case or problem context. The VMware Fusion 11 Release Notes clearly state that it only supports macOS Mojave (10.14) and macOS Catalina (10.15), which narrows the context, or use-case, for the error.
The error message, in this context, is most likely raised because the product is incompatible with how VMware Fusion manages memory at some level in the macOS Big Sur (11.6.*) version. As I speculated earlier, the out of memory error may be linked to how VMware uses Hyper-V but that’s a shot in the dark (or a random guess not the Peter Sellers movie of the same name that reprises his role as Inspector Jacques Clouseau).
How VMware works on the macOS is important to my students because we give them two alternatives for setting up MySQL on osMac: one is inside a Linux VM and the other uses Docker (my notes from January this year for creating a Docker instance on the macOS). My students reported errors like this earlier in the year and I suggested they upgrade to VMware Fusion 12. It seemed to work for everybody but now I can report the exact error message and verify the fix with a qualified reason.
While I’m on this topic, it’s probably best to deal with DirectX support on Apple Hardware. The Apple hardware requirements for support DirectX 11 3D Acceleration in a virtual machine is currently:
- Mac Pro 2013 and later
- iMac 27-inch 2014 and later
- MacBook Pro 13-inch 2015 and later
- MacBook Pro 15-inch 2015 with dual graphics and later
- MacBook Air 2015 and later
- MacBook 2015 and later
- iMac 21-inch 2015 and later
- iMac Pro 2017 and later
- MacMini 2018 and later
It looks like everybody must upgrade any older Apple machines because we can probably assume most 2015 hardware will become obsolete with the new macOS in October 2022. If it’s not in your budget, you should plan for that cost now.
Fortunately, I also have a MacBook Pro (Mid 2021), the last of the Intel 9 chip models. I bought it the week before the announcement of the new tech. A little birdie told me it would be only the M1 after the announcement. The newer MacBook Pro is awesome and the 16″ screen is better than the older 15″ screen. I just hate the lack of a magnetic power cord. Alas, that’s the price of ensuring I had an Intel chip set.
As always, I hope this helps those looking for an answer.
Oracle EBS Forms
Somebody wanted to know how to discover the difference between a customized and generic set of forms in an Oracle EBS solution without manually cataloging. That’s simple, create a vanilla instance from the media and then designate the customized instance as production; and create database links for them respectively as @vanilla and @production.
After doing that, here a query that will return only the customized forms:
COL application_short_name FORMAT A20 HEADING "Application|Short Name" COL form_name FORMAT A20 HEADING "Form Name" COL basepath FORMAT A12 HEADING "Basepath|Product|Top" SET PAGESIZE 9999 SELECT a.application_short_name , f.form_name , a.basepath FROM fnd_form@production f INNER JOIN fnd_application@production a ON f.application_id = a.application_id WHERE NOT EXISTS (SELECT NULL FROM fnd_form@vanilla vf WHERE f.application_id = vf.application_id AND f.form_id = vf.form_id AND f.form_name = vf.form_name) ORDER BY form_name; |
As always, I hope this helps.
Read CSV with Python
In 2009, I showed an example of how to use the MySQL LOAD DATA INFILE command. Last year, I updated the details to reset the secure_file-priv privilege to use the LOAD DATA INFILE command, but you can avoid that approach with a simple Python 3 program like the one in this example. You also can use MySQL Shell’s new parallel table import feature, introduced in 8.0.17, as noted in a comment on this blog post.
The example requires creating an avenger table, avenger.csv file, a readWriteData.py Python script, run the readWriteData.py Python script, and a query that validates the insertion of the avenger.csv file’s data into the avenger table. The complete code in five steps using the sakila demonstration database:
- Creating the avenger table with the create_avenger.sql script:
-- Conditionally drop the avenger table. DROP TABLE IF EXISTS avenger; -- Create the avenger table. CREATE TABLE avenger ( avenger_id int unsigned PRIMARY KEY AUTO_INCREMENT , first_name varchar(20) , last_name varchar(20) , avenger_name varchar(20)) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
- Create the avenger.csv file with the following data:
Anthony,Stark,Iron Man Thor,Odinson,God of Thunder Steven,Rogers,Captain America Bruce,Banner,Hulk Clinton,Barton,Hawkeye Natasha,Romanoff,Black Widow Peter,Parker,Spiderman Steven,Strange,Dr. Strange Scott,Lange,Ant-man Hope,van Dyne,Wasp
- Create the readWriteFile.py Python 3 script:
# Import libraries. import csv import mysql.connector from mysql.connector import errorcode from csv import reader # Attempt the statement. # ============================================================ # Use a try-catch block to manage the connection. # ============================================================ try: # Open connection. cnx = mysql.connector.connect( user='student' , password='student' , host='127.0.0.1' , database='sakila') # Create cursor. cursor = cnx.cursor() # Open file in read mode and pass the file object to reader. with open('avenger.csv', 'r') as read_obj: csv_reader = reader(read_obj) # Declare the dynamic statement. stmt = ("INSERT INTO avenger " "(first_name, last_name, avenger_name) " "VALUES " "(%s, %s, %s)") # Iterate over each row in the csv using reader object for row in csv_reader: cursor.execute(stmt, row) # Commit the writes. cnx.commit() #close the connection to the database. cursor.close() # Handle exception and close connection. except mysql.connector.Error as e: if e.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif e.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print("Error code:", e.errno) # error number print("SQLSTATE value:", e.sqlstate) # SQLSTATE value print("Error message:", e.msg) # error message # Close the connection when the try block completes. else: cnx.close()
- Run the readWriteFile.py file:
python3 readWriteFile.py
- Query the avenger table:
SELECT * FROM avenger;It returns:
+------------+------------+-----------+-----------------+ | avenger_id | first_name | last_name | avenger_name | +------------+------------+-----------+-----------------+ | 1001 | Anthony | Stark | Iron Man | | 1002 | Thor | Odinson | God of Thunder | | 1003 | Steven | Rogers | Captain America | | 1004 | Bruce | Banner | Hulk | | 1005 | Clinton | Barton | Hawkeye | | 1006 | Natasha | Romanoff | Black Widow | | 1007 | Peter | Parker | Spiderman | | 1008 | Steven | Strange | Dr. Strange | | 1009 | Scott | Lange | Ant-man | | 1010 | Hope | van Dyne | Wasp | +------------+------------+-----------+-----------------+ 10 rows in set (0.00 sec)
MySQL Query Performance
Working through our chapter on MySQL views, I wrote the query two ways to introduce the idea of SQL tuning. That’s one of the final topics before introducing JSON types.
I gave the students this query based on the Sakila sample database after explaining how to use the EXPLAIN syntax. The query only uses only inner joins, which are generally faster and more efficient than subqueries as a rule of thumb than correlated subqueries.
SELECT ctry.country AS country_name , SUM(p.amount) AS tot_payments FROM city c INNER JOIN address a ON c.city_id = a.city_id INNER JOIN customer cus ON a.address_id = cus.address_id INNER JOIN payment p ON cus.customer_id = p.customer_id INNER JOIN country ctry ON c.country_id = ctry.country_id GROUP BY ctry.country; |
It generated the following tabular explain plan output:
+----+-------------+-------+------------+--------+---------------------------+--------------------+---------+------------------------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------------------+--------------------+---------+------------------------+------+----------+------------------------------+ | 1 | SIMPLE | cus | NULL | index | PRIMARY,idx_fk_address_id | idx_fk_address_id | 2 | NULL | 599 | 100.00 | Using index; Using temporary | | 1 | SIMPLE | a | NULL | eq_ref | PRIMARY,idx_fk_city_id | PRIMARY | 2 | sakila.cus.address_id | 1 | 100.00 | NULL | | 1 | SIMPLE | c | NULL | eq_ref | PRIMARY,idx_fk_country_id | PRIMARY | 2 | sakila.a.city_id | 1 | 100.00 | NULL | | 1 | SIMPLE | ctry | NULL | eq_ref | PRIMARY | PRIMARY | 2 | sakila.c.country_id | 1 | 100.00 | NULL | | 1 | SIMPLE | p | NULL | ref | idx_fk_customer_id | idx_fk_customer_id | 2 | sakila.cus.customer_id | 26 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------------------+--------------------+---------+------------------------+------+----------+------------------------------+ 5 rows in set, 1 warning (0.02 sec) |
Then, I used MySQL Workbench to generate the following visual explain plan:
Then, I compared it against a refactored version of the query that uses a correlated subquery in the SELECT-list. The example comes form Appendix B in Learning SQL, 3rd Edition by Alan Beaulieu.
SELECT ctry.country , (SELECT SUM(p.amount) FROM city c INNER JOIN address a ON c.city_id = a.city_id INNER JOIN customer cus ON a.address_id = cus.address_id INNER JOIN payment p ON cus.customer_id = p.customer_id WHERE c.country_id = ctry.country_id) AS tot_payments FROM country ctry; |
It generated the following tabular explain plan output:
+----+--------------------+-------+------------+------+---------------------------+--------------------+---------+------------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+------+---------------------------+--------------------+---------+------------------------+------+----------+-------------+ | 1 | PRIMARY | ctry | NULL | ALL | NULL | NULL | NULL | NULL | 109 | 100.00 | NULL | | 2 | DEPENDENT SUBQUERY | c | NULL | ref | PRIMARY,idx_fk_country_id | idx_fk_country_id | 2 | sakila.ctry.country_id | 5 | 100.00 | Using index | | 2 | DEPENDENT SUBQUERY | a | NULL | ref | PRIMARY,idx_fk_city_id | idx_fk_city_id | 2 | sakila.c.city_id | 1 | 100.00 | Using index | | 2 | DEPENDENT SUBQUERY | cus | NULL | ref | PRIMARY,idx_fk_address_id | idx_fk_address_id | 2 | sakila.a.address_id | 1 | 100.00 | Using index | | 2 | DEPENDENT SUBQUERY | p | NULL | ref | idx_fk_customer_id | idx_fk_customer_id | 2 | sakila.cus.customer_id | 26 | 100.00 | NULL | +----+--------------------+-------+------------+------+---------------------------+--------------------+---------+------------------------+------+----------+-------------+ 5 rows in set, 2 warnings (0.00 sec) |
and, MySQL Workbench generated the following visual explain plan:
The tabular explain plan identifies the better performing query to an experienced eye but the visual explain plan works better for those new to SQL tuning.
The second query performs best because it reads the least data by leveraging the indexes best. As always, I hope these examples help those looking at learning more about MySQL.
MySQL DropIndexIfExists
In reply to a question about how to conditionally drop an index on a table in MySQL. It appears the syntax doesn’t exist. However, maybe it does and I missed it. If I did miss it, I’m sure somebody will let me know. However, I simply have a dropIndexIfExists stored procedure for this type of database maintenance.
Below is my dropIndexIfExists stored procedure:
-- Conditionally drop the procedure. DROP PROCEDURE IF EXISTS dropIndexIfExists; -- Change the default semicolon delimiter to write a PSM -- (Persistent Stored Module) or stored procedure. DELIMITER $$ -- Create the procedure. CREATE PROCEDURE dropIndexIfExists ( pv_table_name VARCHAR(64) , pv_index_name VARCHAR(64)) BEGIN /* Declare a local variable for the SQL statement. */ DECLARE stmt VARCHAR(1024); /* Set a session variable with two parameter markers. */ SET @SQL := CONCAT('ALTER TABLE ',pv_table_name,'DROP INDEX ',pv_index_name); /* Check if the constraint exists. */ IF EXISTS (SELECT NULL FROM information_schema.statistics s WHERE s.index_schema = database() AND s.table_name = pv_table_name AND s.index_name = pv_index_name) THEN /* Dynamically allocated and run statement. */ PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; END; $$ -- Reset the default semicolon delimiter. DELIMITER ; |
You call the procedure like:
CALL dropIndexIfExists('payment','idx_payment01'); |
As always, I hope this helps those looking for a solution.
Subscribe via RSS