Asynchronous DB Operations in Twisted

Twisted is an asynchronous networking framework. Other Database API Implementations have blocking interfaces.

For this reason, twisted.enterprise.adbapi was created. It is a non-blocking interface,which allows you to access a number of different RDBMSes.

General Method to access DB API.

1 ) Create a Connection with db.

2) create a cursor.

3) do a query.

Cursor blocks to response in asynchronous framework. Those delays are unacceptable when using an asynchronous framework such as Twisted.
To Overcome blocking interface, twisted provides asynchronous wrapper for db module such as twisted.enterprise.adbapi

Database Connection using adbapi API.

To use adbapi, we import dependencies as below

1) Connect Database using adbapi.ConnectionPool

Here, We do not need to import dbmodule directly.
dbmodule.connect are passed as extra arguments to adbapi.ConnectionPool’s Constructor.

2) Run Database Query

Here, I used ‘%s’ paramstyle for mysql. if you use another database module, you need to use compatible paramstyle. for more, use DB-API specification.

Twisted doesn’t attempt to offer any sort of magic parameter munging – runQuery(query,params,…) maps directly onto cursor.execute(query,params,…).

This query returns Deferred, which allows arbitrary callbacks to be called upon completion (or failure).

Demo : Select, Insert and Update query in Database.

Here, I have used MySQLdb api, agentdata as a database name, root as a user, 123456 as a password.
Also, I have created select, insert and update query for select, insert and update operation respectively.
runQuery method returns deferred. For this, add callback and error back to handle success and failure respectively.

How to delete records by disabling foreign key checks or constraints in mysql

If you have a foreign key constraints on your table, you can’t delete records from parent table, but you can delete from child table. The main reason we use the foreign key constraints is to maintain data integrity.

Some times you many need to disable this kind of enforcing against some operations like delete. To do so, you have to set mysql server system variable foreign_key_checks

Now, you can delete or update with out any restriction because of foreign key constraint. But you will mess up the data integrity.

You can reset it back to enforce for integrity

There are global and local system variables. If you set the global variables it applies to all clients which are connected. If you change the local variable it applies to that current client session only. Here is how you can query local and global variable.

To sent global variable you just need to add global , where to set local you need to local. By default local or session variable will be changed

To set global variable

NOTE: Make sure you to revert the variable back to original state to maintain data integrity in your database.

 

MySQL tips for beginners and intermediate

This article will take you through few MySQL tips those are pretty helpful.

We will start with few useful statements,

Do you know, if you are working offline with mysql  and you need some help on syntax. You can get it right away on terminal  with simple statement  help followed by command or statement.

help

The above statement in the mysql console will give you the whole syntax and it’s usage.

Here is  an example:

For suppose you want know available list of date and time functions. Here is how you will get,

 

status

The  status statement in the mysql console will give you the quick overview about connection, mysql server and client details

Pager

Are you frustrated with querying lot of records to analyze in mysql conventional command line client console? No worries,  can set the pager in mysql client console. You can set it to either less or more. Here is the way to do it,

Let’s say if you set pager to less, you will see your query output like wise when you read file using less command. You try the following statements.

Reset pager:

you can reset your pager using \P with arguments;

Processlist

The following statement show processlist will give you overview about all connected clients and their work, even queries they are being executed,

explain

An other statement which very helpful in optimizing your queries and schema is explain . You can’t imagine how helpful this statement is, it will show how mysql is using indexes how many rows or records it’s searching for a result. Based on the report you can optimize the queries and even you can add indexes to table if required to speed up results.

Here is simple example,

You will see a record for each table from which you are selecting or used in query. Find more about explain here

 

Other than few handy statements explained above, now we will go through few tips to avoid common pit falls in MySQL.

  • Always try to avoid creating the user with wildcard host . Doing so will mitigate the possible risks of getting hacked.
  • Only give required permissions to the user, so other databases or things will not get screwed up either intentionally or accidentally because of user or script.
  • When you are deleting something from production or from important place, use transactions. Here is how you will do it,

This is useful to avoid common problems caused by either typos or accidental mistakes. Before you type commit, you can always review your changes and rollback if changes are unintended.

User Account Management In MySQL

You need to have a user account to execute queries on data. When you install mysql you will prompted to enter root user details or some times you might have installed mysql with root user and no password. It is recommended to have a password for root user after completion of installation and it is best practice to use different set of mysql users to serve different purposes instead of root user to mitigate security vulnerabilities.

To execute any of the following instructions to create and edit user you have to connect with mysql server using mysql client.

mysql -u root -p

Assuming your mysql server is on localhost else specify server with option -h

Create User

To create user we use the statement CREAT USER.  Find the definitive syntax here

mysql>  CREATE USER ‘Bob’@’localhost’ IDENTIFIED BY ‘password‘;

In mysql every user is associated with host. Here it is localhost specified in the statement above. It meas user Bob can only login from host localhost(same machine)

After your create user, he will be assigned the default permissions. To let him access specific database or table you have to grant him the permissions. To do so, we use the statement GRANT

To grant all permissions on all database,

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘Bob’@’localhost’;

To grant all permissions on specific database,

mysql> GRANT ALL PRIVILEGES ON db_name.* TO ‘Bob’@’localhost’;

Once you are done with creating user. Reload privileges using

mysql> flush privileges;

This command will reload users and their privileges.

Change User Password

User can be altered using the statement ALTER USER. To change the password of an user, use the following statement

mysql> ALTER USER ‘Bob’@’localhost’ IDENTIFIED BY ‘password’;

You can change your own password by following statment,

mysql> ALTER USER USER() IDENTIFIED BY ‘password’;

You can also change the password of an user using following two statements as well,

SET PASSWORD
GRANT

Change password using SET PASSWORD

mysql> SET PASSWORD FOR ‘Bob’@’localhost’ =’password’;

Note: Before MySQL 5.7.6 you have to give hash to the statement SET PASSWORD using password function.

Change password using GRANT

mysql> GRANT ALL PRIVILEGES ON  *.* TO ‘Bob’@’localhost’ IDENTIFIED BY ‘new_password’;

List All Users

Following query will list all available users

mysql> select User, Host from mysql.user;

Change host associated with user account

You can change host of any user by updating the Host column of table mysql.user for that user. With out having permissions to update this table you can’t change the host of any user

mysql> update mysql.user set Host=’host’ where Host=’old_host’;

Delete User

To delete an user, we use the statement DROP USER.

mysql> DROP USER ‘Bob’@’localhost’; 

 

How to reset wordpress user password using mysql

WordPress uses md5 hash for password. We can reset wordpress user password using mysql single query.

To reset password using mysql,

Login to mysql and select the wordpress database

Following single query is helpful to reset wordpress user password

update wp_users set user_pass=md5(‘new_password’) where user_login=’username’ ;

Replace, new_password and username with your password and username.

10 MySQL best practices

When we design database schema it’s recommended to follow the beast practices to use memory in optimal way and to gain performance. Following are 10 MySQL best practices

Always try to avoid redundancy

We can say database schema designed is the best one if it is having no redundancy. If you want to avoid redundancy in your schema, normalize it after you design.

Normalize tables

Database normalization is the process of  organizing columns and tables in relational database to avoid redundancy. Find more about normalization here

Use (unique) indexes on foreign key columns

We use foreign keys for data integrity and to represent relation. Some times these are result of process called normalization. When tables are mutually related obviously we can’t query  the data without using joins

Avoid using varchar for fixed width column instead use char

Choose the right one CHAR vs VARCHAR. CHAR(15) will just allocate the space for 15 characters but VARCHAR(15) will allocate the space only required by number of characters you store.

Always use explain  to investigate your queries and learn about how mysql is using indexes

EXPLAIN  statement is very handy in mysql. I’m sure it will spin your head. This statement will give you analyzed report. Where you can use it to improve your queries and schema. It works on both select and update. If you try it on update queries it will that query as select and will give you the report.

Use right data type

Choosing right data type for you column will help you to get rid of many bottle necks. MySQL query optimizer will choose the indexes based on data type you used in query and column datatype. There are many MySQL datatype.

Use ENUM if required  

ENUM is one datatype that mysql supports. By using this you can save lot of memory if you have predefined and predictable values in your database column.

Don’t use too many indexes, it will slow down the inserts and updates. Only use the indexes on selected column

As you know indexes will help you query data much faster than expected. It’s very tempting to you indexes on unintended columns. Choosing index on every column or unnecessary columns will get you slow inserts and updates. You need to think of indexes as seperate table. Where MySQL needs to create a index for every insert in seperate table/file. It’s extra overhead.

Tune  mysql default parameters

MySQL comes with default parameters. These parameters are not suitable if you want use mysql on dedicated machine or production. You have to tune these parameters. Formally we call them as system variables.

 Always create an account with associated hosts instead of wildcard %

MySQL manages the user with associated hosts. i.e, the user  root@localhost can’t login to mysql from everywhere except localhost. but root@% can login from every where. Using only associated hosts will mitigate many attacks those are in your blind spot.