Date: 07/06/09     Keywords: no keywords

I have two tables (same DB format) that I'd like to merge.

One table has the format of several colums:

ID, AlphaCol, BetaCol, GammaCol

The other has different columns:

ID, Foo, Bar, Baz

The ID colum in both are what ties the separate tables together.

I started out the process with the following command:

create table combinedtable select * from firsttable;

This created combinedtable with the columns ID, AlphaCol, BetaCol, GammaCol. Everything's cool so far.

Then, I thought I could do a command like:

insert into combinedtable select secondtable.Foo, secondtable.Bar, secondtable.Baz from secondtable, combinedtable where combinedtable.ID = secondtable.ID;

When I try this, however, I consistently get the error:

Error 1136 (21S01): Column doesn't match value count at row 1.

So, I'm missing something basic, but I didn't think this would be that difficult. What am I doing wrong?

Source: https://mysql.livejournal.com/136144.html

Date: 06/23/09     Keywords: mysql, sql

In the EXPLAIN below, is ref: NULL a bad thing?

mysql> explain SELECT * FROM mytable WHERE item_id='09N1320347' AND value_id IN (2,3,4,5,6,7,8,9);
+----+-------------+------------------------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------------+-------+---------------+-------+---------+------+------+-------------+
| 1 | SIMPLE | mytable | range | PRIMARY,item_id | item_id | 18 | NULL | 6 | Using where |
+----+-------------+------------------------+-------+---------------+-------+---------+------+------+-------------+
1 row in set (0.00 sec)

Indexes:
Keyname Type Cardinality Action Field
PRIMARY PRIMARY 2240917 item_id, color_id
item_id INDEX 93371 item_id, value_id

Source: http://mysql.livejournal.com/135843.html

Date: 06/23/09     Keywords: mysql, sql

In the EXPLAIN below, is ref: NULL a bad thing?

mysql> explain SELECT * FROM mytable WHERE item_id='09N1320347' AND value_id IN (2,3,4,5,6,7,8,9);
+----+-------------+------------------------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------------+-------+---------------+-------+---------+------+------+-------------+
| 1 | SIMPLE | mytable | range | PRIMARY,item_id | item_id | 18 | NULL | 6 | Using where |
+----+-------------+------------------------+-------+---------------+-------+---------+------+------+-------------+
1 row in set (0.00 sec)

Indexes:
Keyname Type Cardinality Action Field
PRIMARY PRIMARY 2240917 item_id, color_id
item_id INDEX 93371 item_id, value_id

Source: https://mysql.livejournal.com/135843.html

Date: 06/19/09     Keywords: mysql, database, sql

I've been handed a legacy app with a mysql database to extend/upgrade. The system is a mess. Lab tests are stored in a single table, with over 200 columns containing marker values. The labtest table includes the DateTestCollected. A separate table holds patient demographics, including DateOfBirth and Sex.

In the past, we scanned lab results and identified possible abnormalities based on one or more markers. The same cutoff values were used regardless of age or sex. So -- SELECT COUNT(*) FROM labtest WHERE marker_1>$m_1_cutoff_high OR marker_1<$m_1_cutoff_low; Even with the less than ideal schema, indexes on the marker columns made this kind of query work relatively well.

New algorithms are being put into place. Instead of just looking at one or two markers, we now must look at the age & sex to determine which cutoff values to use, then apply our algorithm. I now have 12 possible variations of the same algorithm.

(ie:

WHERE
date_of_test>'2008-01-01' AND
date_of_test<'2008-04-01' AND
(

( age_rage='0-9' AND sex='m' AND ( marker_1 < 876 AND marker_1 > 345) ) OR
( age_rage='10-19' AND sex='m' AND ( marker_1 < 824 AND marker_1 > 312) ) OR
( age_rage='20-29' AND sex='m' AND ( marker_1 < 798 AND marker_1 > 311) )

) OR (

( age_rage='0-9' AND sex='f' AND ( marker_1 < 987 AND marker_1 > 465) ) OR
( age_rage='10-19' AND sex='f' AND ( marker_1 < 813 AND marker_1 > 404) ) OR
( age_rage='20-29' AND sex='f' AND ( marker_1 < 701 AND marker_1 > 209) )

) OR (
/* ect */
)

There are actually 12 variations to check against. This query is very slow, especially when iterating through 200+ markers (200 x 12 variations). EXPLAIN showed that MySQL is scanning all rows (50,000) - as soon as I applied a multi-column index (date_of_test, marker_1) MySQL stopped scanning the entire table.

Under the current schema, each marker, age, sex, and date_of_test column is indexed separately (no multi-column indexes). There are limits to the number of indexes per table.

So that's where I am. I have a legacy system in need of work, but I'm not sure of the best, most efficient way of approaching the problem.

I thought of converting the labtest table to something like (labtest_id, marker_id, marker_value), but that alone doesn't solve the issue of having to find the age & sex of each patient to determine the correct marker cutoff value.

What would be the least painful way of working through this?

Source: http://mysql.livejournal.com/135617.html

Date: 06/19/09     Keywords: mysql, database, sql

I've been handed a legacy app with a mysql database to extend/upgrade. The system is a mess. Lab tests are stored in a single table, with over 200 columns containing marker values. The labtest table includes the DateTestCollected. A separate table holds patient demographics, including DateOfBirth and Sex.

In the past, we scanned lab results and identified possible abnormalities based on one or more markers. The same cutoff values were used regardless of age or sex. So -- SELECT COUNT(*) FROM labtest WHERE marker_1>$m_1_cutoff_high OR marker_1<$m_1_cutoff_low; Even with the less than ideal schema, indexes on the marker columns made this kind of query work relatively well.

New algorithms are being put into place. Instead of just looking at one or two markers, we now must look at the age & sex to determine which cutoff values to use, then apply our algorithm. I now have 12 possible variations of the same algorithm.

(ie:

WHERE
date_of_test>'2008-01-01' AND
date_of_test<'2008-04-01' AND
(

( age_rage='0-9' AND sex='m' AND ( marker_1 < 876 AND marker_1 > 345) ) OR
( age_rage='10-19' AND sex='m' AND ( marker_1 < 824 AND marker_1 > 312) ) OR
( age_rage='20-29' AND sex='m' AND ( marker_1 < 798 AND marker_1 > 311) )

) OR (

( age_rage='0-9' AND sex='f' AND ( marker_1 < 987 AND marker_1 > 465) ) OR
( age_rage='10-19' AND sex='f' AND ( marker_1 < 813 AND marker_1 > 404) ) OR
( age_rage='20-29' AND sex='f' AND ( marker_1 < 701 AND marker_1 > 209) )

) OR (
/* ect */
)

There are actually 12 variations to check against. This query is very slow, especially when iterating through 200+ markers (200 x 12 variations). EXPLAIN showed that MySQL is scanning all rows (50,000) - as soon as I applied a multi-column index (date_of_test, marker_1) MySQL stopped scanning the entire table.

Under the current schema, each marker, age, sex, and date_of_test column is indexed separately (no multi-column indexes). There are limits to the number of indexes per table.

So that's where I am. I have a legacy system in need of work, but I'm not sure of the best, most efficient way of approaching the problem.

I thought of converting the labtest table to something like (labtest_id, marker_id, marker_value), but that alone doesn't solve the issue of having to find the age & sex of each patient to determine the correct marker cutoff value.

What would be the least painful way of working through this?

Source: https://mysql.livejournal.com/135617.html

Date: 06/05/09     Keywords: mysql, database, sql

Hi Folks. I'm hoping this is a simple question, but its baffling me.

I have a mySQL 5.0.27 DB. I am trying to give people "show view" and "create view" privs for a specific database. When I execute the commands to do this, they seem to be accepted with no problem - no syntax error, no warning. However when I then do a show grants on the person the "view privs" do not show up, and they confirm that they do not have those privs.

I have flushed privileges as well, which was my first thought, but that is not the case.

I do seem to be able to grant or revoke other privs.

Views are new to us, but they were included in 5.0.1 so I should have them.

Anyone have any thoughts?

Source: http://mysql.livejournal.com/135180.html

Date: 06/05/09     Keywords: mysql, database, sql

Hi Folks. I'm hoping this is a simple question, but its baffling me.

I have a mySQL 5.0.27 DB. I am trying to give people "show view" and "create view" privs for a specific database. When I execute the commands to do this, they seem to be accepted with no problem - no syntax error, no warning. However when I then do a show grants on the person the "view privs" do not show up, and they confirm that they do not have those privs.

I have flushed privileges as well, which was my first thought, but that is not the case.

I do seem to be able to grant or revoke other privs.

Views are new to us, but they were included in 5.0.1 so I should have them.

Anyone have any thoughts?

Source: https://mysql.livejournal.com/135180.html

Date: 04/22/09     Keywords: no keywords

is it bad to do "SELECT *"? What if you're really pulling every column?

Source: http://mysql.livejournal.com/135018.html

Date: 04/22/09     Keywords: no keywords

is it bad to do "SELECT *"? What if you're really pulling every column?

Source: https://mysql.livejournal.com/135018.html

Date: 04/04/09     Keywords: database, web

I want to write my own little message board.
Post something…and allow others to respond, keeping message trees intact.

I’ve no idea how to manage the database for something like this.
Can anyone give me a recommendation (book or website) that might give me a clue?

:)
-246

Source: http://mysql.livejournal.com/134519.html

Date: 04/04/09     Keywords: database, web

I want to write my own little message board.
Post something…and allow others to respond, keeping message trees intact.

I’ve no idea how to manage the database for something like this.
Can anyone give me a recommendation (book or website) that might give me a clue?

:)
-246

Source: https://mysql.livejournal.com/134519.html

Date: 04/01/09     Keywords: php

hey everyone,
i've got a problem and having a hard time wrapping my head around a solution.

i'm writing an app in php that will send an email notification to about 100 people, my boss wants to know how many people actually open the emails, so what i've done is created a field in the db for views.

what i want to do is when someone opens the email it increments the views by 1. simple enough right?
i thought maybe i'd hide an iframe in the email that goes to a php script on the server that would update the views in the db. hit a road block since 99% of email clients now block or disable iframes in emails.


so are there any other ways to go about doing this?

Source: http://mysql.livejournal.com/134169.html

Date: 04/01/09     Keywords: php

hey everyone,
i've got a problem and having a hard time wrapping my head around a solution.

i'm writing an app in php that will send an email notification to about 100 people, my boss wants to know how many people actually open the emails, so what i've done is created a field in the db for views.

what i want to do is when someone opens the email it increments the views by 1. simple enough right?
i thought maybe i'd hide an iframe in the email that goes to a php script on the server that would update the views in the db. hit a road block since 99% of email clients now block or disable iframes in emails.


so are there any other ways to go about doing this?

Source: https://mysql.livejournal.com/134169.html

Date: 03/30/09     Keywords: mysql, database, sql

We are all familiar with how valuable indexes are to mySQL (and in general) database reads. However - and specifically with respect to mySQL - what effect to indexes have to database writes?

A colleague of mine, as we are changing our DB indexing structure, has argued that indexes harm database writes since the data must be written to the database and to the index. This is a compelling argument. However, in my last job in working with Sybase SQL databases we had instances where indexes would greatly increase write efficiency apparently. I had specific experience that this was the case, but no one was able to explain to me how that was the case given what I could see where the data would have to be written in two separate places.

So, how do indexes effect the efficiency of database writes in mySQL?

Source: http://mysql.livejournal.com/133925.html

Date: 03/30/09     Keywords: mysql, database, sql

We are all familiar with how valuable indexes are to mySQL (and in general) database reads. However - and specifically with respect to mySQL - what effect to indexes have to database writes?

A colleague of mine, as we are changing our DB indexing structure, has argued that indexes harm database writes since the data must be written to the database and to the index. This is a compelling argument. However, in my last job in working with Sybase SQL databases we had instances where indexes would greatly increase write efficiency apparently. I had specific experience that this was the case, but no one was able to explain to me how that was the case given what I could see where the data would have to be written in two separate places.

So, how do indexes effect the efficiency of database writes in mySQL?

Source: https://mysql.livejournal.com/133925.html

Date: 02/03/09     Keywords: mysql, database, sql

Folks may remember last week when I was asking questions about mysqldumps. I was doing that in order to get a DB backup for a specific database we wanted to replicate on another server, and I've been working on that.

After some hit and miss I think I nearly got it working, at least to a point where the replication started between the Master and the slave. It seemed to work for a few seconds, then bombed when the slave tried to execute a statement that was for a DB that it didn't have in it.

The point is that of all the several databases on the Master machine, at this point we only want to replicate one on the slave machine. I thought I had had that set up in the slave's my.cnf with the line

replicate-do-db - aid #aid is the database we want to replicate

however High Performance MySQL says that this is not the way to do this since filtering will be done on the current default database. "This is not usually what you want." :-)

The book does indicate that "On the slave, the relicate_* options filter events as the slave SQL thread reads them from the relay log." This make sense to me, and at this point it also makes sense that the log coming from the master has every statement coming into the DB. It goes on to say that "You can replicate or ignore one or more databases (emphasis mine)...based on LIKE pattern matching syntax." There is where I lose it. I understand how to use the LIKE syntax in mySQL statements, but not in this environment.

Is it possible to set up a small battery of statements in the slave my.cnf along the lines of:

replicate_ignore_table = .%

Would that do it?

Is anyone basically doing replication in a simple Master-Slave relationship where you are only replicating one DB? If so, how are you doing it?

THANKS, folks!

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

Date: 02/03/09     Keywords: mysql, database, sql

Folks may remember last week when I was asking questions about mysqldumps. I was doing that in order to get a DB backup for a specific database we wanted to replicate on another server, and I've been working on that.

After some hit and miss I think I nearly got it working, at least to a point where the replication started between the Master and the slave. It seemed to work for a few seconds, then bombed when the slave tried to execute a statement that was for a DB that it didn't have in it.

The point is that of all the several databases on the Master machine, at this point we only want to replicate one on the slave machine. I thought I had had that set up in the slave's my.cnf with the line

replicate-do-db - aid #aid is the database we want to replicate

however High Performance MySQL says that this is not the way to do this since filtering will be done on the current default database. "This is not usually what you want." :-)

The book does indicate that "On the slave, the relicate_* options filter events as the slave SQL thread reads them from the relay log." This make sense to me, and at this point it also makes sense that the log coming from the master has every statement coming into the DB. It goes on to say that "You can replicate or ignore one or more databases (emphasis mine)...based on LIKE pattern matching syntax." There is where I lose it. I understand how to use the LIKE syntax in mySQL statements, but not in this environment.

Is it possible to set up a small battery of statements in the slave my.cnf along the lines of:

replicate_ignore_table = .%

Would that do it?

Is anyone basically doing replication in a simple Master-Slave relationship where you are only replicating one DB? If so, how are you doing it?

THANKS, folks!

Source: http://mysql.livejournal.com/133717.html

Date: 02/03/09     Keywords: mysql, database, sql

Folks may remember last week when I was asking questions about mysqldumps. I was doing that in order to get a DB backup for a specific database we wanted to replicate on another server, and I've been working on that.

After some hit and miss I think I nearly got it working, at least to a point where the replication started between the Master and the slave. It seemed to work for a few seconds, then bombed when the slave tried to execute a statement that was for a DB that it didn't have in it.

The point is that of all the several databases on the Master machine, at this point we only want to replicate one on the slave machine. I thought I had had that set up in the slave's my.cnf with the line

replicate-do-db - aid #aid is the database we want to replicate

however High Performance MySQL says that this is not the way to do this since filtering will be done on the current default database. "This is not usually what you want." :-)

The book does indicate that "On the slave, the relicate_* options filter events as the slave SQL thread reads them from the relay log." This make sense to me, and at this point it also makes sense that the log coming from the master has every statement coming into the DB. It goes on to say that "You can replicate or ignore one or more databases (emphasis mine)...based on LIKE pattern matching syntax." There is where I lose it. I understand how to use the LIKE syntax in mySQL statements, but not in this environment.

Is it possible to set up a small battery of statements in the slave my.cnf along the lines of:

replicate_ignore_table = .%

Would that do it?

Is anyone basically doing replication in a simple Master-Slave relationship where you are only replicating one DB? If so, how are you doing it?

THANKS, folks!

Source: https://mysql.livejournal.com/133717.html

Date: 01/28/09     Keywords: mysql, database, sql

Folks, I'm having what I think is a weird problem restoring a DB from a mysqldump. What I'm trying to do is basically copy a DB from one mysql database server to another.

The mysqldump command I used originally was:

mysqldump --quick --add-locks --extended-insert -u root -p dbname > dbname.sql

When this didn't work upon restoration I also tried:

mysqldump --opt -u root -p dbname > dbname.sql

Both these commands created the dbname.sql file with no problems or complaints.

I copied the SQL file over to the target computer. I went into mySQL as root and did:

create database dbname;
use dbname;
source dbname.sql;

The import would start, but it wouldn't get very far. In the end, after not many seconds it would just...stop. Here is an example:

Query OK, 8708 rows affected (0.11 sec)
Records: 8708 Duplicates: 0 Warnings: 0

Query OK, 8750 rows affected (0.12 sec)
Records: 8750 Duplicates: 0 Warnings: 0

Query OK, 8740 rows affected (0.11 sec)
Records: 8740 Duplicates: 0 Warnings: 0

Query OK, 8758 rows affected (0.11 sec)
Records: 8758 Duplicates: 0 Warnings: 0

Query OK, 8745 rows affected (0.12 sec)
Records: 8745 Duplicates: 0 Warnings: 0

^CQuery aborted by Ctrl+C
^CAborted
mysql: 0 files and 1 streams is left open

$

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

Date: 01/28/09     Keywords: mysql, database, sql

Folks, I'm having what I think is a weird problem restoring a DB from a mysqldump. What I'm trying to do is basically copy a DB from one mysql database server to another.

The mysqldump command I used originally was:

mysqldump --quick --add-locks --extended-insert -u root -p dbname > dbname.sql

When this didn't work upon restoration I also tried:

mysqldump --opt -u root -p dbname > dbname.sql

Both these commands created the dbname.sql file with no problems or complaints.

I copied the SQL file over to the target computer. I went into mySQL as root and did:

create database dbname;
use dbname;
source dbname.sql;

The import would start, but it wouldn't get very far. In the end, after not many seconds it would just...stop. Here is an example:

Query OK, 8708 rows affected (0.11 sec)
Records: 8708 Duplicates: 0 Warnings: 0

Query OK, 8750 rows affected (0.12 sec)
Records: 8750 Duplicates: 0 Warnings: 0

Query OK, 8740 rows affected (0.11 sec)
Records: 8740 Duplicates: 0 Warnings: 0

Query OK, 8758 rows affected (0.11 sec)
Records: 8758 Duplicates: 0 Warnings: 0

Query OK, 8745 rows affected (0.12 sec)
Records: 8745 Duplicates: 0 Warnings: 0

^CQuery aborted by Ctrl+C
^CAborted
mysql: 0 files and 1 streams is left open

$

Source: http://mysql.livejournal.com/133572.html