Rebuild OCI DWH on A Fresh MySQL DB Instance: 3 & 4 & 5: Re-Creating Views, Stored Procedures, and Triggers

Carrying on from the previous post previous post.

3) Re-Creating the Views

3.1) First to get the show create views commands running MySQL from the command line:

set sqlquery="SELECT CONCAT('show create view ',TABLE_SCHEMA,'.',TABLE_NAME,';') FROM information_schema.tables WHERE TABLE_SCHEMA = 'dwh_custom' AND TABLE_TYPE != 'BASE TABLE'"

set outfile=Z:\dwh_custom.get_the_show_create_views.txt

And run from the command prompt (might need to run from where your MySQL.exe is, if you don't have the path configured)>

MYSQL -uUSERNAME -pPASSWORD -hHOST -e %sqlquery% > %outfile%

3.2) Then get the create view outputs:

set outfile=Z:\dwh_custom.get_the_create_views.txt

Basically, you need to wrap the output from the 1 (each line) with:

MYSQL -uUSERNAME -pPASSWORD -hHOST -e "SHOW CREATE VIEW..." >> %outfile%

  • TIP 1: Use Notepad++ to add the first part
  • TIP 2: Do a replace all on ; to add the end part

And copy and paste everything into the command prompt.

3.3) Creating the views:

The output from 2 needs a bit of parsing.

  1. Remove the lines "View[Tab]Create View[Tab]charcter_set_client[Tab]collation_connection".
  2. There are 4 columns of [Tab] separated output, and we are only interested in "Create View".
  3. Add a semi-colon on the end.
  4. Run the commands.
And that's it. Run the "create view" commands. If you are missing any tables referred to in those "Create Views" the command will fail. If the view is not important, just skip it.

The parsing can be done with a few simple lines of PowerShell - set the input and output filenames, load the CSV, output the create view commands:

$InFilePath = "INPUT_FILE_PATH"
$OutFilePath = "Create_Views.txt"

$i = Import-CSV -Delimiter "`t" -Path $InFilePath

$o = @(); $i | Foreach{If($_.View -ne "View"){$o+=($_.'Create View' + ';')}}; $o | Out-File $OutFilePath

Note 1: Run 'use SCHEMANAME' first, before you run the create view, otherwise you'll get:

ERROR 1142 (42000): ANY command denied to user...

Note 2: One thing to watch out for with creating views is the MySQL user referred to in the create view statement. If the MySQL user does not exist, the create view will fail with:

ERROR 1227 (42000): Access denied, you need (at least one of) the SUPER privilege(s)...

Note 3: And if the table referred to in the create view does not exist, you'll get:

ERROR 1146 (42502): Table '...' doesn't exist


4) Creating Stored Procedures

Use -

SHOW PROCEDURE STATUS WHERE Db = "SCHEMA_NAME";

- to find any stored procedures. Then use -

SHOW CREATE PROCEDURE SCHEMA_NAME.PROCEDURE_NAME;

- to show how to create the stored procedure.

You will need to wrap the create procedure command in -

DELIMITER //
...
END//
DELIMITER ;

- otherwise you will encounter this error:

ERROR 1064 (42000): You have an error in your SQL syntax

5) Creating Triggers

Use -

SHOW TRIGGERS FROM SCHEMA_NAME;

- to find any triggers. Then use -

SHOW CREATE TRIGGER SCHEMA_NAME.TRIGGER_NAME;

- to find the trigger create (CREATE DEFINITER) statement. And remember to wrap it with DELIMITER as above.

With the one trigger I had, I did get a -

ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

- and I decided to not resolve it after reading this.

Comments