Simplenews is a quick and easy way to set up and manage newsletters on Drupal, but if you add it to a site which already has users, you might want a way to quickly subscribe all your users to your newsletter.

Rather than click through all users and manually subscribe them - which can be a little bit boring if you have thousands of users - you can run a few SQL queries to mass-subscribe everyone in one go.

Each user that is subscribed to ANY newsletter has an entry in the simplenews_subscriptions table, which contains their user id, their email address and an assigned subscription id. In addition to that, a user has a discrete entry in the simplenews_snid_tid table for each separate newsletter they're subscribed to.

The latter basically links the unique ID from the subscriptions table to the term ID from the Newsletter taxonomy.

Dear MySQL,

I will do this to rest of your code, if you continue breaking replication for me.

– Domas

This procedure will split  a “;” separated column in to new fields preserving ids. This is very specific problem, lets check it with example. Consider a sample table test: And…

The post MySQL Stored procedure – Split Delimited string into Rows first appeared on Change Is Inevitable.

Well procedures mainly carried out working with information schema and it’s usage in stored procedure. Procedures are fairly simple and easy to understand. 1. Edit_table – following procedure executes queries to…

The post MySQL Stored procedure – Execute query if table or Column exists first appeared on Change Is Inevitable.

This procedure lists available database objects under passed database name. It lists present Tables, Views, Stored Procedures, Functions and Triggers under particular database. It also lists storage engine of tables.…

The post Stored procedure to Find database objects first appeared on Change Is Inevitable.

SQLyog MySQL GUI 8.2 Has Been Released.

From SQLyog 8.2, we have introduced 3 editions of the commercial version of SQLyog. When we had first released SQLyog in 2003, it had a very few features. We have been aggressively adding new features for the last 6 years. Now SQLyog has got a huge number of tools and features.

Not all tools are useful to every SQLyog customer. In particular we had several requests for a cheaper ‘entry’ edition with the most popular features: HTTP-tunneling, SSH-tunnelling and autocomplete. This is now available as Professional edition.

SQLyog is no longer an “one-size-fits-all” application. SQLyog caters to a very diverse range to customers. Some use it as MS-Access type application for simple database needs while others might use it only to profile and tune queries for a large scale production environment. The Ultimate edition is for those users in particular that have full …

I had an interesting case recently. The customer dealing with large MySQL data warehouse had the table which was had data merged into it with INSERT ON DUPLICATE KEY UPDATE statements. The performance was extremely slow. I turned out it is caused by hundreds of daily partitions created for this table. What is the most interesting (and surprising) not every statement is affected equally as you can see from the benchmarks above:

I got the following test table created:

PLAIN TEXT SQL:

1. CREATE TABLE `p10` (
2.     `id` int(10) UNSIGNED NOT NULL,
3.     `c`  int(10) UNSIGNED NOT NULL,
4.      PRIMARY KEY (`id`),
5.      KEY(c)
6.      ) ENGINE=InnoDB
7.      PARTITION BY RANGE(id) (
8.     …

mysql_safe is responsible for restarting mysqld if it crashes and will exit cleanly if mysqld was shutdown. The way it determines if mysqld shutdown correctly is if the pid file is cleaned up correctly. MySQL does quite a few things before creating the pid file like initializing storage engines. It can take quite a while to initialize innodb if it has to roll forward the transaction log. During this time if mysqld crashes mysqld_safe won’t restart it. Normally this is ok because the server would just crash again but it can mess with your head a bit if you’re testing changes to mysqld_safe. Especially if those changes involve what mysqld_safe does if mysqld crashes. I think it makes sense to create the pidfile earlier and there is a bug for it. Chime in on the bug if this has burned you.

Topics for this podcast:

*As the Oracle-Sun-MySQL EC world turns
*Google gets its Web on with Go and Chrome
*Open source and cloud computing complement, compete
*How transparent is your open core?

iTunes or direct download (26:20, 6.0 MB)

I recently had the chance to witness the effects of innodb_adaptive_flushing on the performance of InnoDB Plugin 1.0.5 in the wild, which Yasufumi wrote about previously here and here.

The server in question was Solaris 10 with 8 disk RAID10 and 2 32GB SSDs used for ZIL and L2ARC, 72G RAM and 40G buffer pool. We started it up with innodb_adaptive_flushing=OFF and innodb_doublewrite=OFF, then ramped up traffic and everything looked stable ... but I noticed one troubling thing: ~2GB of uncheckpointed data.

mysql> SHOW INNODB STATUS\G
....
Database pages      2318457
Old database pages  855816
Modified db pages   457902
Log flushed up to   10026890404067
Last checkpoint at  10024612103454
....

We enabled …