Date: 09/04/07     Keywords: database

I don’t quite understand how to best design a database…I was wondering if anyone would be willing to give me their thoughts.

• It’s for an ever changing group of about 20 kids (they rotate yearly)
• That can earn an occasionally changing list of 60+ awards

(This is probably obvious) I want to pull down all the awards a child has earned and the date they earned it.

Is there anything wrong with creating a single table with children listed in rows and awards listed in columns? 

(It seems like its wrong—especially on a large scale, but I don’t know why I think that.)

Thank You !

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

Date: 08/31/07     Keywords: php, mysql, sql

I have tables used for storing product information. When product information is updated, I need to archive the current data  before updating. I could extract the data with PHP and insert in the Archived Stuff tables, but I'm trying to find a way to do this with Insert/Update queries directly (without Selecting everything with PHP).

Btw, MySQL table export (in plaintext) is under the LJ-cut section below, for your convenience.



A user is updating a record - before touching the current data I want to back it up into the Archive tables:

INSERT INTO archive_stuff_general (stuff_id) VALUES ($stuff_id);

SELECT LAST_INSERT() as $archive_id

INSERT INTO archive_stuff_colors (archive_id) VALUES ($archive_id);

INSERT INTO archive_stuff_textures (archive_id) VALUES ($archive_id);

UPDATE
archive_stuff_general t1
LEFT JOIN archive_stuff_colors t2 ON t2.archive_id = t1.archive_id
LEFT JOIN archive_stuff_textures t3 ON t3.archive_id = t1.archive_id
LEFT JOIN stuff_general t4 ON t4.stuff_id=t1.stuff_id
LEFT JOIN stuff_colors t5 ON t5.stuff_id=t4.stuff_id
LEFT JOIN stuff_textures t6 ON t6.stuff_id=t4.stuff_id
SET
t1.user_entered = t4.user_entered,
t2.color = t5.color
t3.texture = t6.texture
WHERE
t1.archive_id = $archive_id

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

Date: 08/02/07     Keywords: mysql, database, sql, microsoft, google

This one's got me at the end of my rope, and none of the answers I'm finding searching in the many places I've searched have given me any help, so I'll ask you guys.

First off: I am brand new (as in a couple of weeks) to MySQL, after moving to Ubuntu (Feisty) at home. (Goodbye Microsoft.) I'm a SQL Server DBA/DWA/Database Developer/jack-of-all-trades at work, so I do have some DB experience, just not with MySQL.

Anyway, I've created a schema and the tables in it. Now I'm trying to relate those tables. All use the InnoDB engine.* The guinea pigs I'm using are the BOOK and AUTHOR tables, and I'm trying to relate the ID field in AUTHOR to the AUTHORID field in BOOK. (And, yes, I've tried changing the name of ID to AUTHORID with no success.) Both are type INT. ID/AUTHORID is an ident-seeded primary key. AUTHORID in the BOOK table is a foreign key. Or it's supposed to be, but when I try to relate them, I get the infamous error 1005 (can't create table './directory/filename.frm'). I've found extensive discussion on this at ubuntuforums, forums.mysql.com, and a number of blogs, and have checked everything I've found, modified everything I can, and I still get the error. Basically, if you search on "error 1005" on google and check the first 150 non-repeating pages that come up, I've tried/checked everything that comes up to no avail. I'm at a loss for what do do next, and hoping someone has some ideas.

Also, I tried running a MySQL instance on my XP machine at work, created the exact same tables the exact same way, and tried the relation. It worked on the first try without a hitch.

If anyone's got some help, I'd be truly grateful. Thanks. If more info is needed, I'll report back tonight when I'm actually sitting at my Ubuntu machine.

* As a side note, for philosophical purposes only: why are there so many available engines that don't support foreign keys? Do that many people create non-relational databases? Just wondering.

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

Date: 07/30/07     Keywords: mysql, sql

I have a table with 341K records. When I query for all records with a certain string that have occurred in the in the last 24 hours, it returns in .66 seconds. When I sort that same query by any field, it returns in 3 minutes. Any thoughts on how to optimize this query for sorting?

mysql> describe eventlog_data ;
+----------+---------------+------+-----+---------------------+-------+
| Field    | Type          | Null | Key | Default             | Extra |
+----------+---------------+------+-----+---------------------+-------+
| host     | int(11)       | NO   | MUL | 0                   |       |
| event    | int(11)       | NO   |     | 0                   |       |
| record   | bigint(20)    | NO   |     | 0                   |       |
| cname    | varchar(255)  | NO   |     |                     |       |
| time     | datetime      | NO   |     | 0000-00-00 00:00:00 |       |
| type     | varchar(16)   | NO   |     |                     |       |
| log      | varchar(16)   | NO   |     |                     |       |
| source   | varchar(128)  | NO   |     |                     |       |
| message  | text          | NO   |     |                     |       |
| notified | enum('y','n') | NO   |     | n                   |       |
+----------+---------------+------+-----+---------------------+-------+
10 rows in set (0.02 sec)

mysql> select count(record) from eventlog_Data ;
+---------------+
| count(record) |
+---------------+
|        341414 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT cname, time, event, log
    -> FROM   eventlog_data
    -> WHERE  time > SUBDATE(NOW(), INTERVAL 1 DAY)
    -> AND    eventlog_data.message LIKE '%adm%';
SOME DATA RETURNED
16 rows in set (0.66 sec)

mysql> SELECT cname, time, event, log
    -> FROM   eventlog_data
    -> WHERE  time > SUBDATE(NOW(), INTERVAL 1 DAY)
    -> AND    eventlog_data.message LIKE '%adm%';
    -> ORDER BY 1
SOME DATA RETURNED
16 rows in set (2 min 53.63 sec)

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

Date: 07/08/07     Keywords: programming, mysql, sql

Short info:
Is there a way to use a * operator in a query like so:

`SELECT newSrc.*, oldSrc.* FROM current_raw_table newSrc LEFT JOIN yesterday_raw_table oldSrc ON(newSrc.id = oldSrc.id) WHERE newSrc.* != oldSrc.*);`

to use mysql to compare two tables of data created by a `load data infile` where one table is today's and the other is yesterday. My tests with using *'s failed so plan B is to use my l33t programming skillz to generate a bunch of
`if(newSrc.{fieldName} != oldSrc.{fieldName},"fieldName") as "{fieldName}",` then wrap the whole mess with concat_ws(' ',...);

but before I use said l33t skelz, I figured it might be smart to ask before I reinvent something else.

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

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

Quick question to see if anyone has a better way.

I have 3 development environments: Workstation at work, my laptop, and the workstation at home... my code is moved between all these machines with svn and it works perfectly, so it makes me wish I could do the same with development schema's or whole Databases.

Is there anything like svn for Databases? I know about and use replication, but that's a tad too much just to allow me to work from a coffee shop or catch up on some work on the weekends. Right now I have a fetchSchema.bat file for my windows machines that grabs the current schema/data from the dev server using mysqldump.

Which reminds me of another question for another post.

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

Date: 07/02/07     Keywords: mysql, sql

I'm beyond rusty on my MySQL, and I don't have my book handy (I'm at work, it's not). I'm plugging along working on this little program and I run into this little quandry. I'm positive it's an exceptionally easy question, but sometimes (especially with me) the easiest questions are the ones that I just have no flipping clue on.

So...would this be a legal SELECT statement in MySQL?

SELECT this_value FROM table_a AND this_other_value FROM table_b WHERE `this` = `that`;

I'd upload it to the server and test it out, but I haven't bothered to put any data up yet. I'm just trying to get this down before I forget.

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

Date: 06/30/07     Keywords: database, web

Recently, I thought up a new project that will be used for storing Recipes. This way I'd have access to my recipe box from anywhere, and all my recipes would be stored in one place, rather than at a bunch of different sites.

At this point I'm mostly just writing down the details of what I want it to do, but it occured to me that there are going to be a TON of database calls in this thing. Like, oodles. At least until I figure things out a bit better.

This brought up a question that has been lurking in the back of my mind for a while...Is it better to open a connection to a database, make your query/insert/whatever, and then close it again? Or is it better to open the connection at the initial pageload and then close it at the end of everything?

Since this is going to be web based, if I did just the single open/close, how would I guarantee that the connection is closed out properly?

Usually when I do something with databases, it's not intended to be used by a huge lot of people (this project included), but every function that makes queries/inserts/etc from the database also has it's own open/close statements to the database. I'm just wondering if that's the best way to do it on what might become a large scale project (because, frankly, this thing may turn into a beast really quickly).

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

Date: 06/27/07     Keywords: no keywords

I use the following query to rip the contents of a CSV file into a temp table:

protected $loadfiledef = "
load data concurrent local infile '%s'
into table `%s`
FIELDS
TERMINATED by \",\"
OPTIONALLY ENCLOSED BY '\"'
LINES
TERMINATED by \",\r\n\"
IGNORE 1 LINES
;";

It's been pretty effective over running a script to parse individual lines, except for weird events where a field ends with a \ like so.
"JASMINE ESTATES\",80602,3,3,"RES"

NOTE: Above is only 4 fields out of 180 fields for that row.

That entire line ends up being mangled and causing corruption issues for all the following fields until the line is terminated.

I suppose I could try using sed or something similar to look for \", and replace it with \\", but I think this is just a bad value and something not checked by the IDX source.

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

Date: 06/24/07     Keywords: mysql, sql

Does anybody has an experience with japanese language with mySQL?
my client asked to implement "two bite kanji". What should I set in mySQL in order to get it work?
Thanks,
Roman

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

Date: 06/13/07     Keywords: mysql, sql

I have a query that worked fine against the MySQL 4.0.x binaries, but when I perform the same query on a 5.0.x install I have a problem. I'm using a query with a GROUP_CONCAT clause to put the results into a single field. This is a section of the query, just to show how I'm using it:

GROUP_CONCAT( DISTINCT album ORDER BY album SEPARATOR '||' ) AS album_list

The problem is that it seems to truncate all results at exactly 341 characters. Again, on 4.x it worked fine, but something changed in 5.x that now limits the size of the returned dataset. I read something about group_concat_max_len and a MySQL bug with utf8 columns (all of my tables are utf8). Has anyone come across this problem or a solution? How does one set the group_concat_max_len variable--is that an SQL command or a DB startup parameter?

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

Date: 06/08/07     Keywords: php, mysql, sql, web, apache

I have what is probably an easy question, but I've been Googling and don't quite know where to go.

I write and test MySQL queries on a virtual Apache server on my PC before transferring it to the live server on a website. At the moment, I write the MySQL through a command-line interface, which makes it very difficult (almost impossible) to go back and edit the query if I need to change things or have made mistakes. It seems to take much longer to develop things than it should!

At work, I use Teradata SQL and we have a nice graphical interface where we can type in SQL, edit it easily, save SQL to re-use and all those good things. It's much easier to develop in and also easier to copy and paste the query into other files, such as Excel or PHP.

Can anyone recommend a good equivalent program for MySQL?

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

Date: 05/30/07     Keywords: php, mysql, sql, web

Howdy. I have a MySQL 5.0.41 (community) installed on my home computer under win32. When phpMyAdmin starts, I get a 2003 (server is not responding) error.

Judging from what the console tells me (was checked via "sq query mysql"), MySQL service starts successfully (state: 4 RUNNING) and runs. Until the moment any site from localhost tries to connect to MySQL - after that the service crashes (with win32 exit code 1067).

Didn't find an answer on the web (didn't search thoroughly enough, perhaps). Can someone point me to a solution for this problem, or some further diagnostics?

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

Date: 05/28/07     Keywords: mysql, sql

Hello,
I'm a new mysql user. I had download the GUI mysqlcc (mysql control center) from source forge. I didn't find any user guide or reference book for this tool on the internet. Please, do you know if it is available anywhere?
Thank you in advance for yours answer.

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

Date: 05/25/07     Keywords: php, database

i have a production database and a development database running on the same server. (i know that's dumb but it's all we've got.) i copied a table from production to dev and performed an update on it via a php script. the script reads a csv file, checks to see if the item is already in the table, updates the row if it is, and inserts if it's not. 90% of the time it's an update. when i ran the script on the dev table, the process took about 30 minutes. everything looked good so i ran it on the production table. it's been running for ~3 hours. as far as i know, the tables in production and dev are exactly the same -- same server, same data, same indexes. almost no one is working today so the server is practically idle. is there any reason the same process would take so much longer in another database?

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

Date: 05/10/07     Keywords: php, mysql, software, database, sql, postgresql, web, linux

I am currently working on 2 great jobs for Linux-loving database folk.

1-MySQL and PHP software engineer in PA near Philly (NE). This is for a young software co founded by a guru who had already built another company. He wants hot programmers who will grow with the company. He currently is looking for a Sr person as well as 1-2 junior folk.

1- Linux DBA in Utah - a company with several websites and millions of pageviews and transactions/day. Needs both senior and junior database tuners to keep their PostgreSQL DBS running smoothly. Every millisecond counts. He will take people w/ MySQL and Linux admin and perl  background for the junior position. Full benefits, great work and flexible working conditions, and powder days!

My email is on my info page if you want to pass this on. Thanks!

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

Date: 04/29/07     Keywords: database

Could anybody help me? I've copied a table 'X' to the database from one server to another. An attempt to change the content using a command UPDATE X SET var1 = "somevalue" leads to the ERROR 1036: Table 'X' is read only. Why is it and how can it be corrected? Thank you in advance. Julia

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

Date: 04/13/07     Keywords: mysql, sql

Hello all,

I'm trying to delete a range of rows in MySQL and coming up broke.

On my command line, I'm doing the following:

mysql> delete from table where table.id in (11700)

Or

mysql> DELETE FROM `table` WHERE `table`.`id` = 11700;

That's great, but, I'll still have 10,000+ more rows to go, and both methods require me to delete rows one by one. I'd rather just delete a range of rows.

I want to only delete rows from 300-11800.Does anyone know how to delete a certain range of rows? Or just tell MySQL to delete a certain range of rows?

Thanks!

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

Date: 04/06/07     Keywords: mysql, sql

i need to merge rows in a table where column1 = column1 or column2 = column2 or column1 = column2. i'd like to review the data before i merge it. i'm having trouble constructing a query that returns what i need to see. if the table looked like this:

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

Date: 04/01/07     Keywords: mysql, sql

I swore there was an open source package to do this, but I wanted to monitor in 30 minute increments, the state of a server dedicated to MySQL. Generally its completely dedicated to MySQL, but it also has a number of IDX aggregator scripts that run throughout the day, some of which take up a considerable amount of resources to transform/normalize it. Instead of just giving MySQL everything (resource wise), I wanted to incrementally move up the caches and allocations until its using only what it needs to be at its best.


If there isn't an open source package, I was thinking a cronscript on the machine would work out, but wanted to see if I could save myself the time.

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