headers
GF | 16 May 09:54
Picon

Foreign key and uppercase / lowercase values

Good morning,
I have an application where the user ids were stored lowercase.
Some batch import, in the user table some users stored  a uppercase
id, and for some applicative logic, in other tables that have a
foreign key to the user table, their user ids are stored lowercase.
MySQL didn't throw any error probalby because the collation used is
"case insensitive".
My problem is that the application is Java and java strings are case
sensitive, so now I want to set user ids to lowercase EVERYWHERE.

I supposed that I could execute with ease these commands:
- update mytable1 set USER_ID = LOWER(USER_ID);
- update mytable2 set USER_ID = LOWER(USER_ID);
- update mytable3 set USER_ID = LOWER(USER_ID);

But for some tables I got some Foreign key constraint to throw an
error. (butwhy they didn't throw an error on the insert but just on
the update???)
And if I try to disable foreign key checks during these updates, I get
some "duplicate key" errors where USER_ID is a part of composite key
with other columns. (but I don't have any data that might cause a real
duplicate key error just changing the case of one column)

Have you any idea how to solve this situation without
stopping/recreating the DB? (it's a production environment)
Thanks

--

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
(Continue reading)

Permalink | Reply | 3 comments |
headers
Sena Engenharia | 15 May 22:32
Picon

Seu imóvel precisa estar sempre em perfeitas condições na aparência, segurança e conforto?

Seu cliente de e-mail não pode ler este e-mail.
Para visualizá-lo on-line, por favor, clique aqui:
http://www.engsena.com.br/market/display.php?M=88578&C=a422ea16f0fc983e666748f74186ae45&S=9&L=7&N=1

Para parar de receber nossos
Emails:http://www.engsena.com.br/market/unsubscribe.php?M=88578&C=a422ea16f0fc983e666748f74186ae45&L=7&N=9

E-Marketer
Permalink | Reply | 0 comments |
headers
Pothanaboyina Trimurthy | 15 May 13:57
Picon

Is there any performance difference, maintaining separate ibdata files for each and every table insted of having one singl tabale for all databases.

hi every one

  Is there any performance difference, maintaining separate ibdata
files for each and every table insted of having one singl tabale for
all databases, for InnoDB Storage Engine.

please let me know the difference.

-- 
3murthy

--

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql
Permalink | Reply | 0 comments |
headers
Nicolas Rannou | 14 May 23:05
Picon

Performance question

Hi all,

We are currently designing a database for our application (python/mysql)
and we have some performance concern:

We would have "users" and "images".
"users" can view some "images".
"images" can be viewed by several "users".
(n to m mapping)

Which would be most efficient practice (regarding speed)?
We want to figure the most efficient way to get the images that a user see?
We want to handle 10 000 users and 100 000 images.

*1*  to create 3 tables:*

user - info about a user
images - info about an image
user_image_mapping
 -> one row per association:
 user 1 -> image 22
 user 1 -> image 8888
 user 2 -> image 567
 user 3 -> image 888
 user 3 -> image 44453

*2* to create 2 tables*

user - info about a user
 -> a field would contain a list which represents the ids of the images
(Continue reading)

Permalink | Reply | 1 comment |
headers
Baron Schwartz | 14 May 16:47
Favicon
Gravatar

Re: Deadlock due lockwait. How can I tell mysql to wait longer?

Argh. I meant to send this to the list but it doesn't have the
reply-to set as I expect... <the usual gripe>

On Mon, May 14, 2012 at 10:46 AM, Baron Schwartz <baron <at> xaprb.com> wrote:
> Johan,
>
> On Mon, May 14, 2012 at 9:27 AM, Johan De Meersman <vegivamp <at> tuxera.be> wrote:
>> What I fail to understand, Baron, is how there can be a deadlock here - both transactions seem to be hanging
on a single-table, single-row update statement. Shouldn't the oldest transaction already have
acquired the lock by the time the youngest came around; and shouldn't the youngest simply wait until the
eldest finished it's update?
>
> Take a look at the output again:
>
> ======================== 8< ===============================
>
> *** (1) TRANSACTION:
> TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read
> mysql tables in use 1, locked 1
> LOCK WAIT 13 lock struct(s), heap size 3112, 27 row lock(s)
> update `account` set `balance`= 0.00 + '-6.07' where accountid='3235296'
> *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
> RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
> table `f_database`.`account` trx id 5F7EA9A lock_mode X locks rec but not
> gap waiting
>
> *** (2) TRANSACTION:
> TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread declared
> inside InnoDB 500
> mysql tables in use 1, locked 1
(Continue reading)

Permalink | Reply | 4 comments |
headers
Martijn Tonies | 14 May 15:16

ANN: 70% Ten Year Anniversary discount on all our products!

Upscene Productions is celebrating it's 10 year anniversary with a massive 
discount on all our products: 70% discount until the end of May.

Don't forgot to blog and twitter about this!

We produce database development, management and testing tools for:
* Oracle
* Microsoft SQL Server
* MySQL
* InterBase
* Firebird
* SQL Anywhere
* NexusDB
* Advantage Database
* Generic connectivity tools for ADO and ODBC

These include test data generator tools, database design and development 
tools, auditing tools, a dbExpress driver for Firebird, debugging tools, 
performance analysis tools.

Coupon code TENYEARS will get you this discount, check 
http://www.upscene.com for more information.

--

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql
Permalink | Reply | 0 comments |
headers
Rafał Radecki | 14 May 11:48
Picon

MySQL 5.1.59 - slow_log purge problem.

Hi all.

I write a script to delete rows from slow_log older than 2 weeks.

#!/bin/bash

if [ $# -ne 1 ]; then
echo "Usage: $0 mysql_config_file"
exit 1
fi

SELECTQUERY="select * from slow_log where start_time <
DATE_ADD(NOW(),INTERVAL - 2 WEEK)"
DELETEQUERY="delete from slow_log where start_time <
DATE_ADD(NOW(),INTERVAL - 2 WEEK)"
CONFIG_FILE="$1"
HOSTNAME="$(hostname | awk -F'.' '{print $1}')"
INSTANCENAME="$(grep datadir $CONFIG_FILE | awk -F'/' '{print $4}'|uniq)"
LOG_FILE="/var/log/${INSTANCENAME}-${HOSTNAME}-slowlog-clean.log"

echo "***" >> $LOG_FILE
echo "$(date +'%Y-%m-%d %H:%M:%S %Z') Started cleaning..." >> $LOG_FILE
echo "Before there are $(mysql --defaults-file=${CONFIG_FILE} mysql -e
\"${SELECTQUERY}\" | wc -l) entries older than 2 weeks" >> $LOG_FILE
mysql --defaults-file=${CONFIG_FILE} mysql -e \"$DELETEQUERY\" > /dev/null
2>&1
echo "After there are $(mysql --defaults-file=${CONFIG_FILE} mysql -e
\"${SELECTQUERY}\" | wc -l) entries older than 2 weeks" >> $LOG_FILE
echo "$(date +'%Y-%m-%d %H:%M:%S %Z') Stopped cleaning..." >> $LOG_FILE
echo "***" >> $LOG_FILE
(Continue reading)

Permalink | Reply | 0 comments |
headers
Andrés Tello | 12 May 16:08
Picon

Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz this

While doning a batch process...

show full processlist show:

| 544 | prod | 90.0.0.51:51262 | tmz2012 | Query   |    6 |
end                          | update `account` set `balance`= 0.00 +
'-4000' where accountid='2583092'

No other process, lo locking no nothing...

so you take this same query... run it isolated, and the mu....fu... is
just...f.... fast!!!!!

update `account` set `balance`= 0.00 + '-4000' where accountid='2583092'
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

ARRRG!  I have seen this type of query take as long as 100+ seconds.. and I
don't have a F*** clue...
Permalink | Reply | 6 comments |
headers
Rafał Radecki | 11 May 11:10
Picon

MySQL slowlog - only in file?

Hi all.

Is there a possibility to see the info from slowlog somewhere in database?
I would like to see slow queries using mysql and not by watching the log
file.
I've searched on google and mysql website but hasn't found the solution.

Best regards,
Rafal Radecki.
Permalink | Reply | 5 comments |
headers
louis liu | 11 May 10:34
Picon

drop partitions

Hi all

    last night  we droped some partitions and we found that the first drop
costs about 30mins  and then it's about 3 seconds.

when droping the partition  all processes are shown waiting for opening
table like:

+---------+-------------+-------------------+---------------+-------------+---------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id      | User        | Host              | db            | Command     |
Time    | State                                                          |
Info
                          |
+---------+-------------+-------------------+---------------+-------------+---------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| 4656803 | bkdloguser  | 10.3.0.116:48642  | backend_log   | Query       |
   1420 | Opening tables                                                 |
insert into INTERFACE_LOG
(DOC_ID,DOC_CODE,CALL_CLASS,CALL_METHOD,PARAMETERS,CALL_RESULT,CREATE_TIME |
| 4656804 | bkdloguser  | 10.3.0.116:48643  | backend_log   | Query       |
   1420 | Opening tables                                                 |
insert into INTERFACE_LOG
(DOC_ID,DOC_CODE,CALL_CLASS,CALL_METHOD,PARAMETERS,CALL_RESULT,CREATE_TIME |
| 4656805 | bkdloguser  | 10.3.0.116:48644  | backend_log   | Query       |
   1420 | Opening tables                                                 |
insert into BACK_SQL_OPERATION_LOG
(SUB_SYSTEM,OPERATION_TYPE,OP_CONDITION,OPERATION_TIME,OPERATION_ |
| 4656806 | bkdloguser  | 10.3.0.116:48645  | backend_log   | Query       |
   1420 | Opening tables                                                 |
insert into INTERFACE_LOG
(DOC_ID,DOC_CODE,CALL_CLASS,CALL_METHOD,PARAMETERS,CALL_RESULT,CREATE_TIME |
(Continue reading)

Permalink | Reply | 6 comments |
headers
Andrés Tello | 11 May 09:06
Picon

Deadlock due lockwait. How can I tell mysql to wait longer?

Ok... I have one of those pesky error, in an application not handling
deadlocks or lockwaits.

The database object can't be modified to support deadlock/lockwatis...
I can only change database parameteres

Database info: Server version: 5.5.22-log Source distribution

from show engine innodb status;
{abstract}
*** (1) TRANSACTION:
TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 13 lock struct(s), heap size 3112, 27 row lock(s)
update `account` set `balance`= 0.00 + '-6.07' where accountid='3235296'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
table `f_database`.`account` trx id 5F7EA9A lock_mode X locks rec but not
gap waiting

*** (2) TRANSACTION:
TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread declared
inside InnoDB 500
mysql tables in use 1, locked 1
14 lock struct(s), heap size 3112, 27 row lock(s)
MySQL thread id 92442, OS thread handle 0x7f903b949710, query id 32378480
90.0.0.51 mario Updating
update `account` set `balance`= 0.00 + '-1.37' where accountid='3235296'

*** (2) HOLDS THE LOCK(S):
(Continue reading)

Permalink | Reply | 10 comments |

Gmane