Date: 08/15/08     Keywords: no keywords

How long will queries be stored in the query cache?

Source: http://community.livejournal.com/mysql/131123.html

Date: 07/24/08     Keywords: database, sql

I used to use MS Access a lot and I know a little about SQL. My company wants me to change job roles and part of my new role will be writing queries for our company database. All I've ever had to do with SQL was write simple queries, and I usually just built the query in Access and modified as needed. As a test for this position, which I don't really want, they had me write some test queries. This is the first one I came up with and it's so convoluted I ended up needing Access to do it anyway.


SELECT
tblCust.FName, tblCust.LName, tblAdvCust.CardID, tblContacts.Phone, tblAddresses.FirstLine, tblAddresses.SecondLine, tblAddresses.ThirdLine, tblAddresses.City, tblAddresses.State, tblAddresses.Zip, tblCountry.Description

FROM
tblCountry INNER JOIN (tblAddresses INNER JOIN (tblContactTypes INNER JOIN ((((tblCust INNER JOIN tblAdvCust ON tblCust.CustomerID = tblAdvCust.CustomerID) INNER JOIN tblCustomerMembers ON (tblCust.CustomerID = tblCustomerMembers.CustomerID) AND (tblCust.CustomerID = tblCustomerMembers.MemberID)) INNER JOIN tblDealer ON tblAdvCust.DealerID = tblDealer.DealerId) INNER JOIN tblContacts ON tblCust.OwnerID = tblContacts.ReferTo) ON (tblContactTypes.ContactTypeId = tblContacts.ContactTypeId) AND (tblContactTypes.ContactTypeId = tblContacts.ContactTypeId) AND (tblContactTypes.ContactTypeId = tblContacts.ContactTypeId) AND (tblContactTypes.ContactTypeId = tblContacts.ContactTypeId))ON tblAddresses.OwnerID = tblCust.OwnerID) ON tblCountry.CountryID = tblAddresses.CountryID

WHERE
tblDealer.DealerId="109" AND tblContactTypes.ContactTypeId=1;

Now, maybe they haven't explained it to me well enough, maybe they think I have more experience with SQL than I do and just assume there are things I know that I don't. I don't know. But am I expected to be able to write this sort of thing by hand? If I won't have some tools at my disposal to write this up, how am I expected to do this? I can't even wrap my head around this and for the life of me, because of the way the relationships in the database are set up, I couldn't think of a simpler way to do it.

I'm probably going to turn this job down but it seems like they really want me to do it. So if there's a simpler way to go about this, I want to be able to do it. But this is bollocks.

Source: http://community.livejournal.com/mysql/130809.html

Date: 06/27/08     Keywords: no keywords

http://en.wikipedia.org/wiki/Entity-Attribute-Value_model

Been roaming around to find anyone's experience with the above storage model.    My biggest concern is for performance/speed.... EAV seems like it would be perfect for my new projects needs: categorized directory of businesses ranging from Starbucks to 5 star restuarants, hotels/motels, muesuams, and just about everything else.    It doesn't make sense to build a huge veritable cloud of individual relational tables to support all these different venues and businesses, because hotels can have bars/restuarants,  retail stores have coffee shops, and a bunch of other contradictions will only lead me into flags and short-circuit code.

Source: http://community.livejournal.com/mysql/130173.html

Date: 06/26/08     Keywords: mysql, database, sql

I have two RHEL5 machines, both built & configured at the same time. Both have MySQL 5.0.45 installed and share an identical database.

So - this query returns 334 on one machine, and 0 on the other:

• SELECT COUNT(*) FROM mytable WHERE DATE(datetimefield) = '2008-05-05';

• SELECT COUNT(*) FROM mytable WHERE datetimefield LIKE '2008-05-05%';

Source: http://community.livejournal.com/mysql/130037.html

Date: 06/26/08     Keywords: mysql, sql

Are there performance benefits to using mysqli class over procedural mysqli coding? (or is it just a style & organization thing?)

Source: http://community.livejournal.com/mysql/129620.html

Date: 06/25/08     Keywords: mysql, sql

"mydate" is a DATETIME field. I need to search this field with DATE input and ran into this:

mysql> SELECT COUNT(*) FROM program WHERE mydate LIKE '2008-04-22%';
+-------------------+
| COUNT(*) |
+-------------------+
| 538 |
+-------------------+
1 row in set (0.08 sec)

mysql> SELECT COUNT(*) FROM program WHERE DATE(mydate) = '2008-04-22';
+-------------------+
| COUNT(*) |
+-------------------+
| 0 |
+-------------------+
1 row in set (0.06 sec)

Source: http://community.livejournal.com/mysql/129354.html

Date: 06/17/08     Keywords: mysql, database, sql, linux

Hello all. I'm so bored to kill this error ... can anybody help me?

I have one master and about 10 slave servers. Time to time one (absolutely random) slave server lost replication connection. When i type slave stop; slave start; i see "ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log"

When i look at /var/log/mysql.log, i see

[ERROR] Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.000759' (relay_log_pos 235)
080617 12:25:45 [ERROR] Could not find target log during relay log initialization

OK, "slave reset", drop databases , set MASTER etc, slave start - and replication does just fine. without any errors. Until the next time.

I add to my.cnf next lines:

max-relay-log-size=1M
max-binlog-size=2M
relay-log=/var/run/mysqld/mysqld-relay-bin

But without any effect: replication may work fine long time or break at next hour.

Question: how can set replication, that always work ? :)

Platform: Linux Fedora 6 & 7, mysql 5.0.45, no any errors about disk or memory. Size of replication tables - about 1-2Mb

Source: http://community.livejournal.com/mysql/129177.html

Date: 06/05/08     Keywords: mysql, sql

Two MySQL community members, Keith Murphy and Mark Schoonover collaborated to bring you a survey. Results will be published in the summer issue of the [free] MySQL magazine.

This is a very quick survey, and nicely done in my opinion.

Survey

MySQL Magazine

MySQL Magazine latest issue (spring 2008, issue 4)

Source: http://community.livejournal.com/mysql/128924.html

Date: 06/04/08     Keywords: mysql, database, sql

Is there a way to bind mysql users to a specific server IP?

I have two databases running from the same server. I need to ensure that database ABC only allows activity from server IP 123.000.000.111. A second database (XYZ) should only allow activity from server IP 123.000.000.222. Both IPs and databases are on the same box (multiple nics).


If I've explained this poorly please let me know so I can clarify.

Source: http://community.livejournal.com/mysql/128663.html

Date: 05/21/08     Keywords: mysql, sql, java, security, web

I'm wondering if anyone has any thoughts or pointers about this.

I am setting up a Java Web Start program that accesses a mySQL DB. The program was written a good while ago and was never meant to go outside the office. But now, of course, we want it to. :-)

The issue is...wait for it...security! mySQL, by default, does not have secure communications, although I am seeing that it can be set up to do SSL communications. Save for having to set up our mySQL server to handle that (Certificate administrators, etc.) and having to re-write sections of the Java program to handle the secure communications, that's all theoretically good.

However, I don't really need something that elaborate. Indeed, the data itself is public and does not need to be secured. What we are worried about is someone sniffing out the credentials to the mySQL DB. How can that be stopped?

In my research into this I came across an invokation flag to mySQL called --compress.

The Basic SSL Concepts section of the mySQL 5.0 Reference Manual says, "To improve security a little, you can compress client/server traffic by using the --compress option when invoking client programs. However, this does not foil a determined attacker."

This sounds perfect for me if such an invokation compresses (or otherwise hides) the credentials being used to access the DB? Does anyone know if it does?

So, in a larger sense, I guess I'm asking how you folks secure access to your mySQL DBs in any sort of public setting? Am I forced to set up the SSL solution and rewrite the Java program, or is there a more non-invasive way of doing this?

Thanks!

Source: http://community.livejournal.com/mysql/128489.html

Date: 04/28/08     Keywords: mysql, database, sql, web

I'm working on setting a mirror copy of some websites. The part I'm not sure about the best way to handle things is the MySQL database (I have rsync for the files)

Currently, a script would run from the mirror to collect the MySQL data from the site with the live data (using SSH to run a mysqldump command, then SCP the dump file to itself) and overwrite the data on it's local copy.

I'm just wondering if there is something better (and no, I don't have root access at all. I only have access to some databases VIA the MySQL client)

Source: http://community.livejournal.com/mysql/128050.html

Date: 04/28/08     Keywords: mysql, database, sql

Greetings,

We will be upgrading to MySQL 5.0 (either 5.0.58 or 5.0.60 when it becomes available) 4.1 this summer and we are considering using DRDB. My concerns, as the dba are:

1. Failover time. We are currently running a single, monolithic InnoDB database that has grown to 342G. When I shutdown the server it has taken as long as 40 minutes for InnoDB to flush all data to disk. My concern is that with DRDB, if we have a hardware failure on the DRDB master, with DRDB block-level replication, the DRDB slave's datadir and logdir files will be in the same state as the master when it failed and startup of MySQL could take a long time. Currently we are handling failover with replication and scripts to redirect who the master is.

2. Table Maintenance. Some of our tables are quite large. One is at 90GB. To rebuild indexes and defrag tables, I would run ALTER TABLE blah ENGINE=InnoDB on the slave and then failover to it. With DRDB, if I understand it correctly, this would not be possible.

My concerns are that due to the size of our database DRDB may not be the optimal choice. I would appreciate anybody's comments on this.

Source: http://community.livejournal.com/mysql/127896.html

Date: 04/15/08     Keywords: mysql, sql

   I'm getting ready to upgrade one of our core DB's to 5.0.54 from 5.022, but I figure I am still a week away from that time and in the interim, the machine in question is somewhat spooking/scaring me.   It's been setup to use no more then 6GB of memory and verified to be this way by a couple different "tuning" scripts... yet top is showing the process taking 5.7GB of resident ram and another 2.3GB of swap.  So far there hasn't been any performance issues besides it killing the machine this last sunday from a complete lack of free memory (resident or swap).    

   Has anyone else experienced a similar situation where a MySQL setup using more memory then its been allocated or am I missing something here?     I know one of the BIG problems we have is that in development I had planned to use triggers to handle keeping a series of "materialized" view tables syncronized.  After Insert/Update abbreviated copies of records would be inserted/updated to three sub-tables ( pending 5.1 going stable and the availability of table partitioning)... so in the interim there is a cronjob of doom that updates the other tables based on a record time-stamp field.  Also another department used to have a 2.3GB pdf InnoDB table, but I had them downgrade that to a MyISAM table as they really didn't need transactions for what they were using it for. 



skip-bdb

#old_passwords=1
skip-locking #DJW - Why/who the hell set this?  This is set by default last I checked.

key_buffer = 64M #20080325 - Benchmark for 1 month
max_allowed_packet = 64M
table_cache = 2048
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M

thread_cache_size = 8
thread_concurrency = 8

#DJW - changed from 32
query_cache_size = 64M #Decrement to 48MB by 20080416
query_cache_limit = 3M

tmp_table_size=64M
max_heap_table_size=64M

back_log = 100
max_connect_errors = 10000
join_buffer_size=1M
open-files = 20000

interactive_timeout = 600
wait_timeout = 600

#DJW - 20080324 from 100
#DJW - 20080413 FROM 210 - DO NOT TAKE THIS HIGHER, RHEL5 doesn't appear to handle out of memory events ( normally would kill processes) gracefully
max_connections=180

################################################################################################
####INNODB flags
################################################################################################
innodb_buffer_pool_size = 4500MB
innodb_log_file_size = 500MB
innodb_log_buffer_size = 32MB
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=12
innodb_flush_method=O_DIRECT
#DJW - todo research this for usefulness
#transaction-isolation=READ-COMITTED
innodb_file_per_table 

         

     

Source: http://community.livejournal.com/mysql/127486.html

Date: 03/27/08     Keywords: no keywords

Hi all. Can anyone please tell me what the difference is between these two selects?


SELECT * FROM `table`

and

SELECT * FROM `table` WHERE 1

Source: http://community.livejournal.com/mysql/126599.html

Date: 03/22/08     Keywords: database, asp

I need to design a database to store a large number of numeric markers and am interested in feedback on how best to do this.

There are currently 80+ markers that need measuring, and additional markers will likely be added in the future. Each set of markers is associated with a test_ID (containing date of collection, ect). Also, each marker will have meta-descriptive info stored in a separate table.

Reporting and performance are important aspects of this system. I need to be able to generate percentiles, max/min, standard deviation, and other statistics on a given date range. I will also need to query against certain markers with values above or below certain ranges.

An existing (old) database was created years ago with each marker as a separate column - we've worked with this but any time new markers are added I have to spend several days revising & testing scripts. I want to avoid this pitfall in the new system if possible.

There will be a LOT of data stored in the database, so performance and optimization are key objectives.

So, my initial thought was to store markers in a table like this:

CREATE TABLE results (
id int(20) NOT NULL auto_increment,
test_id int(10) NOT NULL,
marker_id int(3) default NULL,
marker_value decimal(8,3) default NULL,
marker_second_value decimal(8,3) default NULL,
PRIMARY KEY (id),
KEY test_id (screen_id),
KEY marker_id (analyteid)
)

Source: http://community.livejournal.com/mysql/126320.html

Date: 03/11/08     Keywords: mysql, sql

*long story cut out*

Anyone know of a company that uses replication as a way of backing up a MySQL db?  The current solution isn't hacking it right now.

Source: http://community.livejournal.com/mysql/126155.html

Date: 02/21/08     Keywords: mysql, browser, database, sql, web

Hey all

I'm having difficulties connecting to an online database.

I uploaded it online using the webhost's database manager.

As I would need update the database very frequently, therefore going through the webhost way (using the database manager to update) is pretty troublesome... I thought of connecting to the database directly from MySQL query browser. 

So I have the following details (they are all correct, as given and checked by webhost):
Database name
Username
Password
Server IP

When I start up MySQL query browser, it shows me this "Connect to MySQL Server Instance" window, prompting me for:
Stored Connection (i filled it up with 'nameofdatabase' @ 'server IP')
Server Host (i filled it up with 'server IP')
Port (it's 3306 by default, so i left that number there)
Username (i filled it up as given by webhost)
Password (self explanatory, filled that up too.)
Default Schema (i filled it up with database's name)

After filling all those up, I SHOULD be able to connect to my database. Instead, I keep getting this error:
"Access violation at address 00342F02 in module 'libmysql.dll'. Read of address 00000004."

What exactly does this error mean??? I really need to connect to the database ASAP. Would appreciate any form of help. Thanks!

Source: http://community.livejournal.com/mysql/125471.html

Date: 02/02/08     Keywords: php, mysql, sql

Hi!

According to my host, this query is blocking the mysql by causing a max_user_connections error, but I can't see the problem in it. Can anyone help me, please?

(The code is PHP)

$query = "SELECT $entries_table.fic_title, $entries_table.id, $entries_table.fic_url, $entries_table.wip, $entries_table.notes, $entries_table.date, $entries_table.hits,
$fic_cats_table.fic_id, $fic_cats_table.cat_id
FROM $fic_cats_table
LEFT JOIN $entries_table ON $entries_table.id = $fic_cats_table.fic_id
WHERE $fic_cats_table.cat_id = $cat AND $entries_table.date = '$date' ";

Source: http://community.livejournal.com/mysql/125324.html

Date: 01/31/08     Keywords: no keywords

hi,
new to this community, first post in here. i was wondering if anyone out there that can help me thru IMs on trying to get multiple categories per entry. right now, i have one category per entry and i have no idea how to get multiples.

Source: http://community.livejournal.com/mysql/125025.html

Date: 01/31/08     Keywords: no keywords

I have two tables with the same dimensions (say, prices for two consecutive years, 2004 and 2005)-specifically, same number of columns.  Is  there any way to combine  these two tables in one? Somehow didn't find it in Toad help...

thanks,

j_a 

Source: http://community.livejournal.com/mysql/124685.html