Archive for mySQL

Change definer for stored procedures in MySQL

UPDATE `mysql`.`proc` p SET definer = ‘root@localhost’ WHERE definer=’root@%’

Advertisements

Comments (2)

Data folder for MySQL 5.1

It is in my.ini.

C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data

Comments (5)

Setting privileges for executing stored procedures in MySQL

GRANT EXECUTE ON PROCEDURE yourdbname.sp_yourspname TO theuser@’localhost’;
GRANT EXECUTE ON FUNCTION yourdbname.fn_yourfunctionname TO theuser@’localhost’;

use mysql
GRANT SELECT ON mysql.proc to theuser@’localhost’ IDENTIFIED BY ‘paswd’;
GRANT SELECT ON mysql.func to theuser@’localhost’ IDENTIFIED BY ‘paswd’;

FLUSH PRIVILEGES;

Leave a Comment

Spring + DBCP

Adding a connection pool to a Spring app is simply a matter of specifying the relevant entries in the Spring servlet configuration file. The XML snippet below shows an example of how a database connection pool can easily be configured.

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
  <property name="driverClassName">
    <value>net.sourceforge.jtds.jdbc.Driver</value>
  </property>
  <property name="url">
    <value>jdbc:jtds:sqlserver://localhost:1433/db;prepareSQL=0;SendStringParametersAsUnicode=False;</value>
  </property>
  <property name="username">
    <value>username</value>
  </property>
  <property name="password">
    <value>password</value>
  </property>
  <property name="initialSize">
    <value>2</value>
  </property>
  <property name="maxActive">
    <value>5</value>
  </property>
  <property name="maxIdle">
    <value>2</value>
  </property>
</bean>

There are more options that can be used to configure the DBCP connection pool,
eg whether to test connections when getting from the pool or the time to wait
before getting a connection. The complete list of options is given
here.

Comments (16)

on delete cascade in mySQL

When using “on delete cascade” in mySQL,

  1. use INNODB;
  2. index foreign key field.

Example:

create table user ( user_id bigint(20) unsigned not null auto_increment primary key) ENGINE=INNODB;

create table foto (
foto_id bigint(20) unsigned not null auto_increment primary key,
user_id bigint(20) unsigned not null,
index (user_id),
foreign key (user_id) references user (user_id) on delete cascade on update no action
) ENGINE=INNODB;

mySQL reference

Comments (2)

mySQL Backup in DOS cmd

  • goto msql install path/..server../bin
  • backup: mysqldump –user=myusername –password=mypassword –opt –databases db01 > c:/data/db01.sql
  • restore: mysql –user=myusername –mypassword db01 < c:/data/db01.sql

Leave a Comment