Mysql
Connection Failures and Data Manipulation at Non-Thread Safe Shared JDBC Connection
Pitfalls of sharing a connection among threads
Here is a review of the potential pitfalls of sharing a single Connection among multiple threads.
- Committing or rolling back a transaction closes all open ResultSet objects and currently executing Statements, unless you are using held cursors.If one thread commits, it closes the Statements and ResultSets of all other threads using the same connection.
- Executing a Statement automatically closes any existing open ResultSet generated by an earlier execution of that Statement.If threads share Statements, one thread could close another’s ResultSet.
In many cases, it is easier to assign each thread to a distinct Connection. If thread A does database work that is not transactionally related to thread B, assign them to different Connections. For example, if thread A is associated with a user input window that allows users to delete hotels and thread B is associated with a user window that allows users to view city information, assign those threads to different Connections. That way, when thread A commits, it does not affect any ResultSets or Statements of thread B.
Another strategy is to have one thread do queries and another thread do updates. Queries hold shared locks until the transaction commits in SERIALIZABLE isolation mode; use READ_COMMITTED instead.
Yet another strategy is to have only one thread do database access. Have other threads get information from the database access thread.
Multiple threads are permitted to share a Connection, Statement, or ResultSet. However, the application programmer must ensure that one thread does not affect the behavior of the others.
Recommended Practices (at Oracle)
Here are some tips for avoiding unexpected behavior:
- Avoid sharing Statements (and their ResultSets) among threads.
- Each time a thread executes a Statement, it should process the results before relinquishing the Connection.
- Each time a thread accesses the Connection, it should consistently commit or not, depending on application protocol.
- Have one thread be the “managing” database Connection thread that should handle the higher-level tasks, such as establishing the Connection, committing, rolling back, changing Connection properties such as auto-commit, closing the Connection, shutting down the database (in an embedded environment), and so on.
- Close ResultSets and Statements that are no longer needed in order to release resources.
- -> docs.oracle.com/javadb/10.8.3.0/devguide/cdevconcepts89498.html
Ref : oracle.com
Mysql Dump restore Variable Set
Mysql Dump restore Variable Set
1 2 3 4 5 6 7 8 9 10 11 |
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; |
mysql get biggest id value from mysql
1 2 3 |
SELECT id FROM my_table ORDER BY id DESC LIMIT 1 SELECT MAX(id) FROM my_table |
Mysql Blob data size explanations
1 2 3 4 5 |
L = length of data, # = overhead BLOB = L + 2 bytes (max size is 2^16 - 1 or 65,535 bytes, 65KB) MEDIUMBLOB = L + 3 bytes (max size is 2^24 - 1 or 16,777,215 bytes, 16MB) LONGBLOB = L + 4 bytes (max size is 2^32 - 1 or 4,294,967,295 bytes, 4GB) |
PHP mycryp and Mysql Togetger
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
$key = 'password to kutayzorlu.com'; $string = ' string to be encrypted '; // note the spaces To Encrypt: $iv = mcrypt_create_iv( mcrypt_get_iv_size(MCRYPT_RIJNDAEL_128, MCRYPT_MODE_CBC), MCRYPT_DEV_URANDOM ); $encrypted = base64_encode( $iv . mcrypt_encrypt( MCRYPT_RIJNDAEL_128, hash('sha256', $key, true), $string, MCRYPT_MODE_CBC, $iv ) ); # ----------------------------------------------------------- To Decrypt: $data = base64_decode($encrypted); $iv = substr($data, 0, mcrypt_get_iv_size(MCRYPT_RIJNDAEL_128, MCRYPT_MODE_CBC)); $decrypted = rtrim( mcrypt_decrypt( MCRYPT_RIJNDAEL_128, hash('sha256', $key, true), substr($data, mcrypt_get_iv_size(MCRYPT_RIJNDAEL_128, MCRYPT_MODE_CBC)), MCRYPT_MODE_CBC, $iv ), "\0" ); |
Mysql Convert DateTime to UnixTime
Try this Query for CONVERT DATETIME to UNIX TIME STAMP
1 |
SELECT UNIX_TIMESTAMP(STR_TO_DATE('Apr 15 2012 12:00AM', '%M %d %Y %h:%i%p')) |
This Query for CHANGE DATE FORMATE
1 |
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE('Apr 15 2012 12:00AM', '%M %d %Y %h:%i%p')),'%m-%d-%Y %h:%i:%p') |