MySQL

This group mission is to address MySQL related issues in Drupal modules development process or discuss efficient use of MySQL within Drupal based projects.

Performance issues with INNODB vs MyISAM

Posted by deja711 on April 23, 2015 at 8:18pm

Not sure where is the most appropriate post for this, let me know if anybody has suggestion.

I am having performance issues with INNODB. My major issue is that with INNODB I am getting approximately 20 times slower performance than with MyISAM. For the purpose of testing I have used Drupal 7 with devel module installed. Further more I used 2 bare bone servers with default installations so I can do parallel comparisons.

Database tutorial for a beginner

Posted by samstamport on April 19, 2015 at 2:42pm

I am a newbie to Drupal.

I am trying to wrap my brain around how Drupal uses MySQL. I am pretty familiar with relational databases especially Microsoft Access. Can you give me a reference to a free ebook, tutorial, or YouTube video that explains exactly where the database(s) is stored on my local Win 7 PC? I am using Acquia Dev Desktop.

Thanks!

Replacing old user tables causes drupal site to crash

Posted by suzannewang on March 28, 2015 at 6:23am

Hi,

I have both a live website and a testing site.

I am trying to replace the user and related tables on my testing site with the tables EXPORTED FROM the live site. My testing site doesn't take them well and encounters unexpected errors.

Has anyone met the the same issues before?

Thanks a lot for reading.

Drupal Database Development

Posted by rmjackson on October 23, 2014 at 1:55pm

The percona-query-digest tool is excellent at tracking down queries that kill performance. The queries can be re-written on modified schemas with better indexes and you're golden. Once that's done you need to institure some healthy happy ways to avoid the problem continuing to recur through a little education and some development routines.

MySQL Config Optimizations?

Posted by nerdcore on March 11, 2013 at 3:29pm

I am curious to know if anyone has advice on tweaking the out-of-the-box my.cnf specifically for Drupal?

The organization I work for typically hosts multisite installations, and may have numerous disparate sites running on a single web server using a single MySQL server... Given that there is a wide range of sites with varying purposes (some more anonymous viewing, some more authenticated content generation), are there any Drupal-specific changes to my.cnf which might be appropriate?

migrating users and content from D6 to D7

Posted by thermalmusic on May 4, 2012 at 5:33pm

Hi all,

I am in the process of developing a site in Drupal 7 to replace a site in Drupal 6 on the same server. We decided to create the new site instead of upgrading the old one for various reasons. I realize there are pitfalls migrating users, nodes, and other data from D6 to D7.

My questions are: has anyone had experience doing this, and what kinds of snags have you encountered? Did you use the migrate V2 module, SQL scripts, drush, or combination of the three?

Any comments, suggestions will be appreciated.

Thanks,
Steven

Select query for Users/Profiles on single rows

Posted by thermalmusic on March 14, 2012 at 2:26am

Hi,

I'm trying to write an SQL query on a D6 database to return users and their profile values. My problem is getting profile values to return as separate fields (columns) in the resulting rows. Profile data is only saved in the values field in the profile_values table, as children, identified by fid.

I've tried Group_Concat but it is not a solution. Profile csv module is not sufficient either. I'd like to have an output like this:

If anyone has done this as a query, please let me know.

Thanks,
Thermal

Custom search results

Posted by stevengm7 on January 10, 2012 at 11:03am

Hi everyone

I have been messing with Drupal and other CMSs for a while (3+ years). But I chose Drupal and stack with it since then no matter how hard the learning curve was.
So far whenever I had issues either I figure it out or search Drupal.org site for any possible solutions time being sacrificed. But now I want to save time and be efficient expediting my projects getting direct help from all my groups. Most issues being discussed here are grabbing my attentions because they are very important that's why I joined so many groups.

How to seach text from .txt file

Posted by digodrupal on December 12, 2011 at 6:37am

i need this help on drupal.

i have a text file and file name is C:\wamp\www\mysite\sites\default\files\abc.txt. in this file is;

aaaaa bbb
ccccc ddd
eeeee fffff

if i type aaaa in the search box and hits enter means the output should come with the node containing the word aaaa and this this text document also.

This will be shown in search output

is there any module available for that?

thanks.

Database development prior to MySQL

Posted by aaron1948 on September 28, 2011 at 7:20am

I am seeking advice about what might be the best database program to use to create a large pre-MySQL database, from which I will select the content to export/import/enter into a Drupal MySQL database.

I am developing my first Drupal site -- a web portal where information will be organized by relevance to various generations. The site will launch with a Baby Boomer orientation, with information organized for various baby boomer niches, and then expand to serve more generational niches.

slow term_data and term_node queries

Posted by jamesclarke on September 17, 2011 at 1:07am

Hello,

I've been trying to figure out why our drupal site is going slow . . . this has ranged from disabling modules (update status and statistics) to looking into the slow queries log.

When I look into the log, the overwhelming majority of the entries are from term_data or term_node

E.g:

SELECT t.* FROM term_node r INNER JOIN term_data t ON r.tid = t.tid INNER JOIN vocabulary v ON t.vid = v.vid WHERE r.vid = 169 ORDER BY v.weight, t.weight, t.name

and

Porting drupal's database to MySQL Cluster possible?

Posted by showmanlkz on August 4, 2011 at 4:24am

Hi guys, my organization is running a MySQL Cluster 7.x. I have been quite comfortable to port some of our MySIAM type tables to MySQL Cluster's NDB type.

I found that drupal uses innoDB, so I am wondering if it's possible to port it to NDB type?

FYI, MYSQL Cluster NDB doesn't support few things in innoDB include foreign key constraints.

http://dev.mysql.com/doc/refman/5.0/en/faqs-mysql-cluster.html

Any opinion will be welcomed.

Derrick

SQL select query for returning all roles and permissions

Posted by thermalmusic on July 24, 2011 at 4:40pm

Hi everybody,

I want to create a select query to return a list of all roles and their related permissions directly from a MySQL database. Does anyone have a good one for that?

Thanks,
Thermal

SQL ICE Demo for Drupal Users - Webinar Tues, March 22

Posted by lfolta on March 18, 2011 at 2:59pm

SQL ICE Demo - How to Use SQL ICE to Access Your Drupal Data

Join us for a Webinar on Tuesday, March 22, 10am Central

Reserve your Webinar seat now at:
https://www1.gotomeeting.com/register/882042968

Are you a Drupal user and do you know SQL? If you answered "yes", then SQL ICE could be a useful tool to have on your desktop.

In this session, we will demonstrate how you can use SQL ICE to access your Drupal data, using the same version of SQL ICE (v.1.1.1) that is available as a free download from our site, to run queries against the data from our live Drupal site.

search option in particular table

Posted by sabarins on March 17, 2011 at 7:01am

I'm a newbie to drupal and i need to search data from a particular table. Can any one give me solution to my question. Idea needed ASAP.

Greatly Appreciated to the answering guys!!!!

Post-installation: Default Data Scripts

Posted by plditallo on March 10, 2011 at 12:33am

Hi Techies--
I've installed Drupal on 2 different servers. On one I remembered to click on the checkbox for installing the default data. On the other, I did not. Other than that, the installation went fine. Does anyone know which script dumps the default stories, menus, etc. into the mySQL data schema? I'd hate to re-run an install script if I could just run a *.sql script from phpAdmin or a *.php from cpanel.

Thanks--

SQL ICE - New Tool for Drupal Users who know MySql

Posted by lfolta on January 12, 2011 at 7:15pm

Business Integration Technology has just released SQL ICE, the Internet Computing Environment for SQL Experts. Although SQL ICE can do a lot of things, one that may be of interest to Drupal Users is the ability to connect to your Drupal database and write MySQL queries to get reports that can be displayed, downloaded, and shared with other users.

All you need to do is add a Connection to the target database and write a query in SQL. The data is exposed as an XML document accessed by a REST Web service. For example, a simple query like:
Select

Question on MYSQL INNER JOIN

Posted by shyamala on December 3, 2010 at 4:05pm

SELECT node.nid AS nid, node_data_field_article_image.field_article_image_fid AS node_data_field_article_image_field_article_image_fid, node_data_field_article_image.field_article_image_list AS node_data_field_article_image_field_article_image_list, node_data_field_article_image.field_article_image_data AS node_data_field_article_image_field_article_image_data, node.type AS node_type, node.vid AS node_vid, node.title AS node_title, node.nid AS node_nid, nodequeue_nodes_node.position AS nodequeue_nodes_node_position FROM node node

DB Management with multiple data centers

Posted by ilikepie on October 27, 2010 at 4:15pm

Hi there,

We had a second site in the cloud that has all of our sites set up and ready to go with a quick DNS change. MySQL is kept up to date with replication over an SSH tunnel, and we use lsync to keep the files in sync. The problem that I'm seeing is that, if we switch to the disaster recovery/second site and new data starts to be written to MySQL on the slave server, what would be the best workflow for returning this data back to the primary site?

Please help with tables

Posted by andribas on August 2, 2010 at 11:22am

I have the following issue;
table node contain rough 200k entries;

I would like to use view which select "node.created, node.title" where node is published, belongs to term (not multiple), and content-type = news. Order by created desc.

Here is query: 255.44ms

SELECT node.nid AS nid, node.title AS node_title, node.created AS node_created FROM node node INNER JOIN term_node term_node ON node.vid = term_node.vid WHERE (node.type in ('news')) AND (node.status <> 0) AND (term_node.tid = 6) ORDER BY node_created DESC LIMIT 0, 10

Subscribe with RSS