Member Avatar for Dani

Hey everyone,

As many of you know, we've been having serious database issues here on DaniWeb lately, causing the site to go down just about every single day for a few minutes. We finally got the situation resolved thanks to our database sysadmins over at Percona.

While James and Cameron manage all of our servers on a day-to-day basis, we sometimes have to reach out to the folks over at Percona (www.mysqlperformanceblog.com) for extra MySQL assistance.

We've been working with Percona for many, many years already, and some of the guys over there who we work with will be coming to our hometown, NYC, to host a MySQL conference later this month.

Due to our relationship with them, we were able to get a hold of a couple of free tickets to the event (normally $249), and we're giving the tickets away for free to community members who respond to this thread with any really interesting fact, tidbit, or useful piece of information about MySQL.

Here's a link to the event details: http://www.percona.com/live/nyc-2011/

I'll be there myself, so I hope to see some of you guys there as well!

  • 10 Contributors
  • 14 Replies
  • 365 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by Ramin2000

Dani AI

Generated

Practical, durable MySQL notes to complement the thread — short, actionable guidance that fills some gaps in the replies and is safe to keep in a troubleshooting checklist.

Building on ’s observation about InnoDB log files: when the redo-log size needs changing, perform the operation on a tested maintenance window with backups and a staging rehearsal. Typical safe flow is: confirm current size with SHOW VARIABLES LIKE 'innodb_log_file_size'; stop MySQL cleanly; move or rename the existing ib_logfile* files to a backup location; set the new innodb_log_file_size in my.cnf; restart MySQL so it recreates the logs; then validate database health and replication. Do not skip backups or quick restarts — verifying the restart and checking SHOW ENGINE INNODB STATUS and replication SHOW SLAVE STATUS (if used) are essential.

Following on REPLACE INTO: because REPLACE deletes then inserts, side effects (deleted/inserted triggers, lost columns not supplied in the new row, changed AUTO_INCREMENT behaviour) can bite. Prefer INSERT ... ON DUPLICATE KEY UPDATE to update existing rows without a delete. Example pattern:

INSERT INTO users (id, name, email)
VALUES (42, 'Alice', 'alice@example.com')
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  email = VALUES(email);

For full-text/search context noted by and the migration headache described by : external search/index engines (Sphinx, Solr, Elasticsearch) index data independently and work with many RDBMSs. When migrating from legacy systems and “random” value loss occurs, check character sets/collations, strict SQL modes, triggers, and the client library; enable general query or binary logs to trace the writes. Final note: always test changes on a copy, take backups, and coordinate maintenance across replicas and application teams.

Recommended Answers

All 14 Replies

Member Avatar for jlego

never changes the max log file size on innodb without first getting rid of the existing log files :|

learned that the hard way in the middle of a work day today

Member Avatar for debasisdas

I would, if i could.

Member Avatar for WASDted

I'm totally in!

Member Avatar for Member #739054

I would love free tickets, only problem is I am buried under a pile of work. But I will be sure to pass the message on see if anyone else is interested in going to a mysql conference.

Member Avatar for lg061870

Please set aside 1 free ticket for me. Thank you so much

Member Avatar for Dani

Please set aside 1 free ticket for me. Thank you so much

Do you have an interesting fact to share about MySQL?

Member Avatar for lg061870

I have used MySQL for a couple of projects. It feels lighter than SQL Server but still delivers the same power...that includes scaling.

Member Avatar for jayreis

Not sure if this qualify's for a MYSQL fact but...

Sphinx which is a very common full text search engine many Mysql database administrator use is NOT MySQL specific. It can work with just about any RDBMS such as PostgreSQL, MS SQL and Oracle Servers. It can also read XML woot woot lol

Member Avatar for jayreis

Ok in case the above does not count or in case the more I post the better my chances of scoring a ticket to the MySQL conf lol

For those that use the function "Replace into" function in mysql instead of using an insert their is a draw back you should be aware of
For those that don't know what the Replace Into function in sql is here is what it does..

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted

take note on the part that says
if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted

If you're planning on using REPLACE in one of your own programs, ensure that you've retrieved all columns from that table first.

Member Avatar for pscoln1

I want one pretty please!!

Member Avatar for Dani

I want one pretty please!!

Do you have something interesting to say about MySQL?

Member Avatar for lg061870

Two more guys in the office want free tickets to the MySQL conference! What do they have to do?

Member Avatar for theonlylos

Not sure how useful this is, but I have a client who actually uses Corel Paradox in place of MySQL for reasons too long to explain here, however about a year or so ago I took the initiative to modernize the code to make things simpler for my client, however despite weeks of going through my predecessors code and limited documentation, every attempt to recreate the Paradox functionality with MySQL ended in horrible failure.

After hours and hours of effort, I kept following the workflow, however for some reason my queries were dropping values randomly, so to this day the code I wrote is collecting dust somewhere on the workstation.

Member Avatar for Ramin2000

Just moved to mysql and it saved me from so many problems which I used to have…
Very excited about the event.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.