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.

DBSlayer

DBSlayer is a simpler way to proxy mysql.

DBSlayer can be queried via JSON over HTTP, and the responses can be given in either one of the following supported languages : JSON, PHP and Python which makes processing the database results.

Multi-threaded server written in C.

Features :

  • Reduce configuration
  • Reduce dependencies
  • Handle failovers
  • Simple load balancing
  • Easy to monitor
  • Minimal performance overhead
  • Work in different configuration scenarios
  • Support different programming languages

Installing DBSlayer :

Database URI :

http://machine:port/db?URLENCODED(JSON OBJECT)

http://machine:port/dbform?URLENCODED(HTML FORM)

Parameters :

SQL – SQL to execute

Example Request :

Example Response :

Example for python :

Start dbslayer :

This starts up a DBSlayer daemon on 9090 (this default port can be changed which acts as a proxy for the backend mysql server. this proxy can be queried via JSON over HTTP).

Stop dbslayer :

Other URI/API endpoints :

http://machine:port/stats [Queries per second]

http://machine:port/stats/log [Last 100 requests]

http://machine:port/stats/errors [Last 100 error]

http://machine:port/shutdown [Only from localhost]

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.