Using MySQL but noticed that your Python scripts no longer able to retrieve data as usual?

If the following symptoms apply to you:

• You recently migrated from Azure MariaDB to Azure MySQL.
• MySQL Workbench is able to return data successfully.
• You have not made any changes to your Python scripts, but they are no longer able to retrieve data as they did before the migration.

What you should do?

• Please check if you are using mysql-connector as your library. If yes, please uninstall it by using this command:
• pip uninstall mysql-connector -y
• After that, please run this command to install the correct MySQL library for Python:
• pip install mysql-connector-python

Some useful reference articles related to this issue:

• Review this article from Microsoft and you will notice that they are using mysql-connector-python as the library: https://learn.microsoft.com/en-us/azure/mysql/flexible-server/connect-python
• Project page for mysql-connector-python: https://pypi.org/project/mysql-connector-python/
• Project page for mysql-connector, which you can see they mentioned it is deprecated: https://pypi.org/project/mysql-connector/

Hit "Authentication plugin 'caching_sha2_password' is not supported" when you are using mysql-connector with Python?

 Hit "Authentication plugin 'caching_sha2_password' is not supported" when you are using mysql-connector with Python?

Well, the answer is:

a). Uninstall mysql-connector, and

pip uninstall mysql-connector

b). Proceed to install mysql-connector-python

pip install mysql-connector-python

Enjoy scripting!

"Character set 'utf8' unsupported" when you run Python script with MySQL database?

When you run Python script with MySQL database, system gives you this error message:

Character set 'utf8' unsupported

What Do You Need To Do?

pip uninstall mysql-connector-python

Specified key was too long; max key length is 767 bytes

Getting this error "Specified key was too long; max key length is 767 bytes" from your MySQL?

Here is the solution:

• Simple, just upgrade your MySQL to at least version 5.7.30.

You may download the GA releases from here: https://dev.mysql.com/downloads/mysql/

The 'vfpoledb.1' provider is not registered on the local machine.

Not able to find the solution for this error message "The 'vfpoledb.1' provider is not registered on the local machine."?

Here is the solution:

1. Download the msi file from here, https://www.microsoft.com/en-us/download/details.aspx?id=14839, and make sure you install as Everyone. If you choose Only Me, you will hit the same error message.
2. Build your application, regardless it is a web application or desktop application, in x86 mode.

Happy coding!

Want to know your MySQL database size?

Well, you can try the script below:

Select
table_schema As "Database Name",
Round(Sum(data_length + index_length) / 1024 / 1024, 2) As "Database Size in MB"
From information_schema.Tables
Where table_schema = 'xxxxx'
Group By table_schema
Order By table_schema;

It takes years to output data via Microsoft SQL Server BCP (Bulk Copy Program utility)?

If you are using Microsoft SQL Server BCP (Bulk Copy Program) utility, or a.k.a. XPShellCmd, and you found that your scripts take forever to execute.

Solution:

a). Always make sure your Select statement comes with NoLock for each tables.

Oracle Announces Plans to Support Microsoft Visual Studio 2012

Oracle plans to offer a new version of the Oracle Developer Tools for Visual Studio integrated with Microsoft Visual Studio 2012. This new version will be available within 40 days of Microsoft Visual Studio 2012's Release to Manufacturing (RTM) date. As with earlier releases of Visual Studio, Oracle has partnered closely with Microsoft as part of the Visual Studio Industry Partner Program to make this release possible. Keep an eye on on the OTN .NET Developer Center or Twitter for the upcoming release announcements.

Update OpenQuery v.s. Update LinkedServer.Table

In our recent findings, we have found that the Update statement with LinkedServer could cause database performance issue, which end up giving you a "server is not responding" or timeout error message.

The solution to resolve this issue, is to change the Update LinkedServer.Table statement to Update OpenQuery statement.

This solution is tested and proven workable.

The table 'TABLE_NAME' could not be found. Error in File REPORT_FILE_PATH: The table could not be found.

Report Tool: Crystal Report from Visual Studio 2008
Database: MySQL Server (any version)
Project Platform: ASP.NET Web

Symptom 1: You can open the Crystal Report in developer's machine successfully.
Symptom 2: You cannot open the Crystal Report in other machines, including other developer's machine.

Error message: The table 'TABLE_NAME' could not be found. Error in File REPORT_FILE_PATH: The table could not be found.

SOLUTION:

• First, make sure the Crystal Report runtime is installed on the web server.
• Second, make sure the correct MySQL ODBC Connector is installed on the web server. For example: if you are using MySQL database version 5, then the version of the MySQL ODBC Connector shall be version 5 as well.
• After that, close all of the internet browsers.
• Run your web application now, and the error shall be resolved.

How to find all trigger information in MySQL?

Select
*
From Information_Schema.Triggers
Where trigger_schema = 'YOUR_SCHEMA_NAME'
Order By event_object_table Asc, event_manipulation Asc;

How to find all of the table information in MySQL?

Select
*
From Information_Schema.Tables
Where table_schema = 'YOUR_SCHEMA_NAME';

How to find all of the foreign keys in MySQL?

Select
*
From Information_Schema.Table_Constraints
Where constraint_schema = 'YOUR_SCHEMA_NAME' And
constraint_type = 'FOREIGN KEY'
Order By constraint_name Asc, table_name Asc;

How to retrieve the partitions name of a MySQL table?

Select *
From Information_Schema.Partitions
Where table_schema = 'Schema_Name' And
table_name = 'Table_Name'
Order By partition_name Asc;

How to add partition to MySQL table?

Alter Table Table_Name
Partition By Range (Field_Name)
(
Partition `201101` Values Less Than (201102),
Partition `201102` Values Less Than (201103),
Partition `201103` Values Less Than (201104),
Partition `201104` Values Less Than (201105),
Partition `201105` Values Less Than (201106),
Partition `201106` Values Less Than (201107),
Partition `201107` Values Less Than (201108),
Partition `201108` Values Less Than (201109),
Partition `201109` Values Less Than (201110),
Partition `201110` Values Less Than (201111),
Partition `201111` Values Less Than (201112),
Partition `201112` Values Less Than (201201)
);

The `201101` represents the partition name while the 201102 represents the Less Than value.

ORA-12557: TNS:protocol adapter not loadable.

Actually this is not the formal way to resolve this error message, but the point is it works:

1. Remove the ORACLE_HOME from Environment Variables.
2. Restart the computer.

The procedure entry point could not be located in the dynamic link library oranl10.dll

You might encounter the following error when you trying to connect to the Oracle database by using SQL PLUS:

The procedure entry point could not be located in the dynamic link library oranl10.dll

The solution is as below:

a). Go to the Oracle Universal Installer.

b). Click the "Installed Products...".

c). System will prompt you a new screen called "Inventory".

d). Now, click the "Environment" tab.

e). In the "Home Name", please move up the original service name by using the arrow.

f). After that, click the "Apply" button and then "Close" button.

g). Exit the Oracle Universal Installer.

h). Try to connect to the Oracle database by using SQL PLUS now.

The request failed with HTTP status 400: Bad Request.

This problem occurred because of the URL consists of special character or space.

If the URL is hard-coded in your web.config, then you have to open your web.config and make sure the URL is OK.

Wrap in Oracle

You are going to deploy a new package to your client. But you do not want to disclose the codes in that package, so what you need to do?

a). Go to the Windows command prompt.

b). Type the following command:

wrap iname=<input file> oname=<output file> edebug=wrap_new_sql

How to quick search in Package_Body?

You might have hundreds of packages in your Oracle database.

Now, you need to check a syntax in those package bodies. But you are not sure which packages are related.

Thus, you will need this command:

Select * From user_source Where text Like '%What_You_Want_To_Find%';