Re: update a row only if any column has changed, in a very large table

2013-04-08 Thread Andrés Tello
Take a look here. http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html timestamp field can be autoupdated and autoinitilizated With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP, the column has the current timestamp for its default value and is automatically updat

Re: Update and lock question.

2013-04-05 Thread Andrés Tello
vel > locking. > > > -Urvi > > -Original Message- > From: Andrés Tello [mailto:mr.crip...@gmail.com] > Sent: Thursday, April 04, 2013 9:08 AM > To: mysql > Subject: Update and lock question. > > I'm doing some tests, but have a questions about locking. &

Update and lock question.

2013-04-05 Thread Andrés Tello
I'm doing some tests, but have a questions about locking. In a innodb table, if you issue an select for update lock for a row, supposedly, it only locks that row, but if you don't issue a select for update, and trow the update... does it locks the hole table? The update goes over an indexed field

Re: MySQL dying?

2012-12-04 Thread Andrés Tello
Are u kidding? Mysql is dead easy and damn good... obviously it has its perks, but any database engine has them... (I'm looking at you DB2)... There has been a lot of improvements lately, I "feel" that mysql is moving much more faster under oracle umbrella than when it was alone... Replication..

Still struggling witn like 'CTV%' over varchar.... I simple cannot understand..

2012-11-27 Thread Andrés Tello
mysql> explain select * from cuenta where rutaCuenta like 'CTV%'; ++-++---+---++-+--++-+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra | ++-

Re: Error message I am getting today. All help appreciated.

2012-10-19 Thread Andrés Tello
humm... I bet he have just run of space... Some thing like tht... On Tue, Oct 16, 2012 at 10:43 PM, Dehua Yang wrote: > Hi > There many reasons to lose connection to MySQL server. > And what's more , the important thing is that you should provide the error > code to us. > > If you got the err

Re: innodb_lock_wait_timeout

2012-10-11 Thread Andrés Tello
Are you managing transactions with mysql + innodb? I had a similar issue, and I need to rework the application. innodb does a row level transaction lock. Read locks aren't exclusive, update locks are exclusive. What I was doing was something like this: Thread P1 begin; innodb: update field set

Re: Slow queries / inserts InnoDB

2012-10-09 Thread Andrés Tello
You are forcing mysql to do full table scans with the substr... Use explain to see that you aren't using any index. Avoid the use of substr in the where clause, by splitting your data, index that field and do you query over that field. That is why your query is so slow. the slow insert, is du

InnoDB: The InnoDB memory heap is disabled

2012-07-23 Thread Andrés Tello
Hello everyone. Today I restarted a server and got this at the log file: InnoDB: The InnoDB memory heap is disabled I read a little (maybe to little) about and it says something about the native use of malloc. The system is a linux Linux 2.6.34.7-0.7 #1 SMP PREEMPT 2010-12-13 11:13:53 +0100 x8

Re: Mysql starts to die at 27 SQL processes

2012-05-31 Thread Andrés Tello
Don't you have any message? For the number of max connections, I suppose you are hitting the limit of file opened, there are no messages at error log? Check this: http://www.geeksww.com/tutorials/database_management_systems/mysql/configuration/mysql_open_files_limit_openfileslimit_vs_openfileslimi

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

2012-05-14 Thread Andrés Tello
Claudio, would you please extend the example to the use of in? On Mon, May 14, 2012 at 10:08 AM, Claudio Nanni wrote: > In my experience if you have a poor designed code that run the same query > for hundreds or thousands of times in a very short timespan (like some > programmers do in for-loop

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

2012-05-14 Thread Andrés Tello
r column > > > On Sat, May 12, 2012 at 7:38 PM, Andrés Tello wrote: > >> While doning a batch process... >> >> show full processlist show: >> >> | 544 | prod | 90.0.0.51:51262 | tmz2012 | Query |6 | >> end | update `account` s

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

2012-05-12 Thread Andrés Tello
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.

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

2012-05-11 Thread Andrés Tello
humm, I see.. and if is encapusulated with it's own begin-commit inside a bigger transacion, only that small part get rolled back... If I get this straigth... On Fri, May 11, 2012 at 2:32 PM, Baron Schwartz wrote: > Andres, > > On Fri, May 11, 2012 at 1:48 PM, Andrés Tello &

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

2012-05-11 Thread Andrés Tello
e record, > I would be confused exactly as InnoDB is because I would not know which > update is the 'good' one, > I'd close my eyes and kill one. > This is a deadlock. > > Claudio > > 2012/5/11 Andrés Tello > >> Yup, but a far I understand... >>

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

2012-05-11 Thread Andrés Tello
bits 176 index* > > Cheers > > Claudio > > 2012/5/11 Andrés Tello > >> Ok, so I had a deadlock... >> >> But then, why a deadlock doesn't rollback all the transaccion? >> >> >> On Fri, May 11, 2012 at 9:55 AM, Baron Schwartz wrote: &

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

2012-05-11 Thread Andrés Tello
On Fri, May 11, 2012 at 3:06 AM, Andrés Tello > wrote: > > 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 dat

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

2012-05-11 Thread Andrés Tello
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 statu

Re: RFE: Allow to use version-specific my.cnf files

2012-04-25 Thread Andrés Tello
Reads interesting, but... Why would you need that? I mean... If I run several databases in the same hardware, I use completely diferent paths for evertying, so I can have atomic, clean and specific files for each instance/version of the database I think is much more easy to migrato to anoth

Re: problems with INNODB tables

2012-04-25 Thread Andrés Tello
] > Sent: Monday, April 23, 2012 9:42 PM > To: Andrés Tello; Malka Cymbalista > Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun > Subject: RE: problems with INNODB tables > > Check your memory usage according to > http://mysql.rjweb.org/doc.php/memory > > > -Original

Re: problems with INNODB tables

2012-04-23 Thread Andrés Tello
Weird, I use a lot Innodb, and no issue, I even kill bravely the mysql process with pkill -9 -f mysql Y suppose the way drupal is being programed. PHP open and closes database connections each time a webpage with db access is issued. When a php exceution ends and the apache webserver have fullfill

Re: Design advice for hotel availability program

2012-02-21 Thread Andrés Tello
Verify the regulation that a Hotel should submit, read the part of the occupation reports, information gathering, ask management for their currents report... Remember the data output should came from the data stored... On Mon, Feb 20, 2012 at 4:42 PM, Chaim Rieger wrote: > On 2/17/2012 4:01 AM

How to split a mysqldump file of multiple databases to singlefile databases... SOLVED with script

2012-02-20 Thread Andrés Tello
Today I needed to split a mysqldump -A into it several databases. I didn't have access to the original source, so I only had the texr file to work. It was a webhosting server dump, so there was a LOT of databases... I split the file with this little script I made: file= nextTable="" nextStart=0 n

Re: 'myisam_use_mmap' unstable like hell

2011-12-15 Thread Andrés Tello
When I had memory issues, with something relatively stable, mostly is due faulty ram... Can you use or less ram or change fisically the ram? On Thu, Dec 15, 2011 at 2:23 AM, Reindl Harald wrote: > > > Am 15.12.2011 08:47, schrieb Rob Wultsch: > > To be brutally honest, if you want stability yo

Re: 4 minute slow on select count(*) from table - myisam type

2011-10-03 Thread Andrés Tello
have you tried select count(yourindex) instead of select count(*) ? On Mon, Oct 3, 2011 at 7:53 AM, Joey L wrote: > Thanks for the input - > 1. I will wait 48 hours and see what happens. > 2. can you tell me what are some performance tests I can do to help me > better tune my server ? > 3. I a

Re: problem

2011-05-02 Thread Andrés Tello
Seems more a java issue than a mysql issue... check for your parameters for any keepalive or persostent option... I think you wouldn't need to recompile just re-run or at leat, reboot the server.. On Mon, May 2, 2011 at 2:34 AM, swaroop jois wrote: > Hello friends, I have MySQL server versio

Memory Usage.

2011-04-25 Thread Andrés Tello
How can I know how memory is being used by Mysql? I have 32GB Ram, but I can't make mysql to use more than 12GB Ram , and even that I have tables over 40GB... Thanks! xD

How to avoid deadlocks.. advice needed also insight, illumination and a bit of hallucination...

2011-04-20 Thread Andrés Tello
I'm running into some deadlocks issues. I have this structure accounting |---movements To know the balance of the account, I usualy do a sum(movements.amount) where accounting.id=someid The issue is that the sum is starting to run very slow due hardware constraints, and I can't trow more hardw

Re: Performance issue old server witn mysql 4 vs new server with mysql 5 and old server WINS!

2011-02-16 Thread Andrés Tello
like > RAID10 on the old and RAID6 on the new? > > Singer > > > > On Sun, Feb 13, 2011 at 16:40, Andrés Tello wrote: > >> I have a test process, which runs in the "old server" in 35 seconds, the >> new >> server runs the same process in 110. >&g

Re: Messed up mysql-server, cant reinstall?!

2011-02-13 Thread Andrés Tello
didn't u forgot to run mysql_intsall_db? On Fri, Feb 11, 2011 at 6:53 AM, Adam smalin wrote: > I accidentally drop the database mysql. I now cannot restore tables to the > database or do much of anything some details can be found here > > http://serverfault.com/questions/234321/i-accidently-dr

Performance issue old server witn mysql 4 vs new server with mysql 5 and old server WINS!

2011-02-13 Thread Andrés Tello
I have a test process, which runs in the "old server" in 35 seconds, the new server runs the same process in 110. There is a change of version from mysql 4.1.22 to 5.1.22. We were stuck at 5.1.22 because higher version give us another issules like encoding, case sensitivity... I really belive th

Re: Moving from one MySQL server to three MySQL servers?

2010-08-06 Thread Andrés Tello
Any kind of optimization you need works. I would use a explain sql statements istead to import without indexes, that will shed more light... Even if u optimize the report, if you have concurrent access demanding a bunch load of data you will other operations get stuck... for that is a good reason

Re: Moving from one MySQL server to three MySQL servers?

2010-08-04 Thread Andrés Tello
My experience with replication: Most of the times, is good enough, fast enough... I have just reworked some part of an application to split the reporting module from all other modules. We are still using php 4.3 with pear::db module (what? legacy software is hard to kill! we are trying!, Honest!

Re: which version is better for production servers?

2010-07-19 Thread Andrés Tello
I have just upgraded from mysql4.1 to mysql 5.5.3.. I will upgrade to 5.5.4. 5.5. has a lot of speed improvements... and OF course you should NOT use mysql developtment release for production server! On Mon, Jul 19, 2010 at 8:05 AM, Nilnandan Joshi wrote: > Hi all, > > I just wanna make new My

First impression of mysql 5.5.3 vs mysql 4.1.22

2010-07-19 Thread Andrés Tello
WW... While uploading the database to a clean mysql, mysql 4.1.22 didn't even get over 26 mb/s of writting speed, but I'm monitoring the speed mysql 5.5.3 is reaching and can squeeze 100mb/s, averga I'm seeing like 35mb/s 22GB at mysql 4.1 lasted like 3 hours to fully load, this one I bel

Re: mysql 5.5.3 and innodb from source. (SOLVED)

2010-07-18 Thread Andrés Tello
Thanks to everybody, and to Rob Wultsch, his link helped me to understand what I was doing... ./configure select an automake configure or a perl one... I ended building mysql with innodb,heap,myisam and partitions, statically built with: ./configure --with-plugins=heap,partition,innobase,myisam

mysql 5.5.3 and innodb from source.

2010-07-17 Thread Andrés Tello
Who you build mysql 5.5.3 with innodb suport? I made sh configure.am --with-plugins=all I see the makefile at innodb subdir being created. I build the system correctly but when I log in to the mysql 5 instance and do a show engines; I only have this: ++-+---

Mysql 4 and or Partitions.

2010-07-15 Thread Andrés Tello
Ok... I solved my mistery of the slow 22G table rebuild the kernel to support memory and now things are working. But still quite slow. I do sum() operations over the 22G table. I'm using the latest version of mysql 4... (I WILL migrate soon, I promise), one thing I have notice, is that operat

Re: Why is MySQL always linked to Php? The history channel version...

2010-07-15 Thread Andrés Tello
HIstorical convinience: A certification program for oracle or db2 requires a lot of money and time. The facilities oracle and db2 can offer to the dba includes things that the S.O should to. Exaple: raw spaces and table spaces, hot copy, replication, etc... Many of this "facilities" used to be pr

Re: Slow disk access: 1.4m to do a select count over 23GB table.

2010-07-10 Thread Andrés Tello
As usual, after you send a mail, you check for other things >From the 32GB of ram, I only was allocating 2G, not by the process, but by all the S.O... free -G reported barely 1 gig of ram... XD -> happy face that I have lots of ram... now my face is like: ¬¬ -> why I didn't verfy the amount

Slow disk access: 1.4m to do a select count over 23GB table.

2010-07-09 Thread Andrés Tello
Hi, I'm using mysql 4.1.21, a legacy system. Next step to migrate it, but for now, I need the community help. I have a 23GB table, if I do a select count(over_an_index) from table it uses 1.4minutes to read. The main issue is that this table is the main table of a system and each query is taking t

Re: How to slim MySQL?

2010-07-06 Thread Andrés Tello
The more information, the easiest to pinpoint solutions. delete all client and administrativ tools (mysql, mysqladmin, etc from the "server", since you aren't doing any transactions, I think you can ditch all engines excepto the isam/myisam, the basic one, also you migth want to leave memory engin

Re: How to corrupt a database please???

2010-04-18 Thread Andrés Tello
What if the DBA ask for the backup? And those recommendations can be "fixed" or they have a very high chance of making recovery impossible? On Sun, Apr 18, 2010 at 1:09 PM, Rob Wultsch wrote: > On Sun, Apr 18, 2010 at 10:39 AM, Suresh Kuna > wrote: > > open the file and remove some data an

Windows installer source code...

2010-03-22 Thread Andrés Tello
I followed the instructions from Mysql site to donwload the code using bazaar... I need to make a modification to the installer, but I wasn't able to find the source code where the is the reference to a screen at the wizard... so I wonder, does the windows installer code is distributed with the m