mysqld keeps crashing
Hello all, I'm running mysql 4.1.0-alpha-log on FreeBSD 5.1, with InnoDB. In the last month, I've had a number crashes, and table corruption. My application is a website of medium volume, and the database is not more than a few hundred MB. The errors I recieve are almost all signal 11 errors. Here's some of what I have, from mysql_error_log: -- InnoDB: Scanning backward trying to find previous allocated mem blocks Mem block at - 68, file w0sel.c, line 2457 Freed mem block at - 324, file w0sel.c, line 2457 Mem block at - 580, file mysql.c, line 324 Mem block at - 836, file w0ins.c, line 82 Mem block at - 964, file m0rec.c, line 443 Mem block at - 1092, file m0rec.c, line 443 Mem block at - 1348, file 0pcur.c, line 28 Mem block at - 1604, file w0upd.c, line 287 Mem block at - 1732, file 0pcur.c, line 162 Mem block at - 1860, file m0rec.c, line 443 InnoDB: Scanning forward trying to find next allocated mem blocks Mem block at + 188, file mysql.c, line 324 Freed mem block at + 2236, file w0sel.c, line 2128 Mem block at + 8380, file w0ins.c, line 82 Mem block at + 10428, file w0ins.c, line 82 Mem block at + 12476, file w0ins.c, line 82 Mem block at + 16572, file mysql.c, line 324 Mem block at + 24764, file w0ins.c, line 82 Freed mem block at + 28860, file x0trx.c, line 78 Mem block at + 29372, file t0mem.c, line 197 Mem block at + 29884, file t0mem.c, line 197 040328 12:49:29 InnoDB: Assertion failure in thread 358640640 in file row0mysql .c line 452 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=33554432 read_buffer_size=131072 sort_buffer_size=2097144 max_used_connections=46 max_connections=500 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 112076 4 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 040328 12:49:29 mysqld restarted -- Thanks in advance, Joshua Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: My.cnf
-Original Message- From: Scott Haneda [mailto:[EMAIL PROTECTED] Sent: Monday, February 02, 2004 6:13 PM To: MySql Subject: My.cnf I have copied my-large.cnf to /etc/my.conf Mysql 4 on OS X server How can I tell these new settings are in effect? What settings were run prior to this file being in place as there was no my.cnf? -- 1) Run 'SHOW VARIABLES' 2) Restart your mySQL server. 3) Run 'SHOW VARIABLES' again to see the changes. 4) Read The Fine Manual: http://www.mysql.com/doc/en/index.html Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- Ninety percent of this game is half mental. - Yogi Berra --- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replace to update records
Mike, have you looked into Perl, and Perl's DBI module?
You can do something like this... (example only, not tested code):
--
use DBI;
use strict;
use warnings;
# Get arguements from command line
my $ether_address = $ARGV[0];
my $location = $ARGV[1];
# Database connection info
$database = yourdatabase;
$hostname = localhost;
$user = youruser;
$password = yourpassword;
# Connect to database
$dsn = DBI:mysql:database=$database;host=$hostname;
$dbh = DBI-connect($dsn, $user, $password) or die($@);
# Prepare queries
my $select_query = SELECT COUNT(*) WHERE ethernet_address =
$ether_address;
my $update_query = UPDATE hardware_assets SET location=$location WHERE
ethernet_address = '$ether_address';
my $insert_query = INSERT hardware_assets (ethernet_address, location)
VALUES
($ether_address, $location);
# Make statement handles
my $select_sth = $dbh-prepare($select_query);
my $update_sth = $dbh-prepare($update_query);
my $insert_sth = $dbh-prepare($insert_query);
# Query for rows
$rv = $select_sth-execute || die(Could not execute $select_query: $!\n);
$count = $select_sth-fetchall_arrayref([0]);
if ($count 0) {
my $update_sth-execute || die(Could not execute $update_query:
$!\n);
} else {
my $insert_sth-execute || die(Could not execute $insert_query:
$!\n);
}
--
Good luck,
Joshua Thomas
Network Operations Engineer
PowerOne Media, Inc.
tel: 518-687-6143
[EMAIL PROTECTED]
---
Ninety percent of this game is half mental.
- Yogi Berra
---
-Original Message-
From: Mike Tuller [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 22, 2004 3:04 PM
To: MySql List
Subject: Replace to update records
I have a shell script that will insert information about
systems I have into
a MySql database. I wanted to have it so that the script
could run daily,
and just update the records if a record for the particular system was
already in the database. This could make the script complex
because I would
have to check to see if the record exists by matching the
ethernet address.
If it does, then it would update the record, if it doesn't,
it would add the
record.
The problem I have is that I want to have an auto-increment
ID number that I
can have display on a web page, where you would click on the link that
displays the ID number of the computer to display details.
If I use replace, when I update the record, it also updates
the ID, so what
was 4 is now 5. I don't want that to change.
Here is what I currently have.
/usr/local/mysql/bin/mysql --user=$username --password=$password
--host=$server cetechnology -e \
REPLACE INTO hardware_assets (ethernet_address, operating_system,
boot_volume, computer_type, number_of_cpus, cpu_type, total_memory,
bus_speed, \
cpu_speed, L2_cache_size, serial_number, ip_address,
network_name,
script_version, date_processed, asset_tag_number, department,
location,
room_number) \
VALUES \
('$ethernet_address', '$operating_system', '$boot_volume',
'$computer_type', '$number_of_cpus', '$cpu_type', '$total_memory',
'$bus_speed', \
'$cpu_speed', '$L2_cache_size', '$serial_number',
'$ip_address',
'$network_name', '$script_version', '$date_processed',
'$asset_tag_number',
\
'$department', '$location', '$room_number');
I am thinking it would be better to have the script search
for all records
that match a certain ethernet address. If a record exists,
then update the
record, and if one does not exist, then insert a record.
I understand how to select, insert, and update individually,
but I am not
sure how to how to put it all together to do what I want this to do.
Something like this:
SELECT * WHERE ethernet_address = $ethernet_address
(if the number of results does not = 0)
UPDATE hardware_assets SET location='my location' WHERE
ethernet_address='$ethernet_address
Else
INSERT hardware_assets (ethernet_address, location) VALUES
($ethernet_address, $location)
Could someone help me finish these statements or show me a
better way of
doing this?
Mike Tuller
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT
\u database SHOW TABLES Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- Ninety percent of this game is half mental. - Yogi Berra --- -Original Message- From: Seena Blace [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 4:04 PM To: [EMAIL PROTECTED] Subject: SELECT Hi, How to see all tables in connected database? thx -seena - Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
tuning key_buffer_size
Hello all, My mySQL 4.1.0 server is currently set to use a key_buffer_size of 32M. My server has 1GB of RAM, and is now using only InnoDB tables. The manual says that 'Key_reads/Key_read_request ratio should normally be 0.01'; mine is ~ 0.12; the manual also says 'Key_write/Key_write_requests is usually near 1'; mine is very, very small ( 0.0001). This server is also running apache/php. Any suggestions on how I should proceed in tuning key_buffer_size? Thanks, Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra ---
RE: consistency checking InnoDB tables
That's ok, we got the right answer. Mailing lists are great like that. :-) Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra --- -Original Message- From: robert_rowe [mailto:[EMAIL PROTECTED] Sent: Thursday, January 08, 2004 4:11 PM To: [EMAIL PROTECTED] Subject: Re: consistency checking InnoDB tables Thanks for the correction. I didn't think to check InnoDB's docs. MySQL's docs don't mention this. Sorry for leading you astray Joshua. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
consistency checking InnoDB tables
Hi folks, I'm familar with using myisamchk to check/repair myISAM databases; how do I do consistency checks and repair operations on an InnoDB database? Thanks, Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra ---
RE: Deciding whether to convert to InnoDB
Do you need foreign keys? Then the switch to InnoDB may be worthwhile. That's part of the reason I switched. I also had occasional table corruption with my myISAM tables; this also went away when I switched to InnoDB. Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra --- -Original Message- From: Travis Reeder [mailto:[EMAIL PROTECTED] Sent: Monday, January 05, 2004 3:02 AM To: MySQL List Subject: Deciding whether to convert to InnoDB Hi, I'm sure this has been asked before, but I cannot find solid evidence as to whether switching would provide us with any benefits. We currently run MyIsam tables on 4.1.x and we are continuously processing 24 hours/day and using about 20 tables heavily. The process is generally doing Updates or Inserts depending on whether the row is available for updates, otherwise new rose is inserted and then updates until the next time bucket. It's always a different time bucket though, not always the same row being used. We found that running 3 processing threads seems to be around optimal (10 was too many, 1 was too little) for being able to process the maximum amount. Mysql runs at 100% pretty much constantly. Now would InnoDB help in this situation? Would it allow us to increase the thread count to push more through in a shorter amount of time (because the tables wouldn't be locking)? And if so, would it be enough to justify the extra space required for innodb? Regards, Travis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql 5.x versus 4.x
Download it and try it yourself. That's the only sure way to know. Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra --- -Original Message- From: Carlos J Souza [mailto:[EMAIL PROTECTED] Sent: Monday, January 05, 2004 8:47 AM To: [EMAIL PROTECTED] Subject: Mysql 5.x versus 4.x Hello Friends, Mysql 5.x is more quick than 4.x ? Regards for all Carlos Souza -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: The Future of MySQL with .NET Plataform
Hi Carlos, Could you please post once and only once, and wait for someone who has insight to respond, instead of spamming this list? Thank you, Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra --- -Original Message- From: Carlos J Souza [mailto:[EMAIL PROTECTED] Sent: Monday, January 05, 2004 9:32 AM To: [EMAIL PROTECTED] Subject: The Future of MySQL with .NET Plataform Hello for all, Please i need comments about future of MySQL with .NET Plataform introduction. Regards for all Carlos Souza -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Dropping a foreign key in 4.1.x
How does one drop a foreign key from a InnoDB table in mySQL 4.1.x? The manual says ALTER TABLE yourtablename DROP FOREIGN KEY internally_generated_foreign_key_id You have to use SHOW CREATE TABLE to determine the internally generated foreign key ID when you want to drop a foreign key. However, my CREATE TABLE statment in 4.1.0 does not return an intertnally generated foreign key ID, but something like this: mysql SHOW CREATE TABLE cm_mail \G *** 1. row *** Table: cm_mail Create Table: CREATE TABLE `cm_mail` ( `id` int(15) NOT NULL auto_increment, `view` varchar(30) NOT NULL default '', `touser` varchar(30) NOT NULL default '', `from2` varchar(30) NOT NULL default '', `message` mediumtext NOT NULL, `datestamp` int(11) NOT NULL default '0', `status` varchar(30) NOT NULL default '', PRIMARY KEY (`id`), KEY `id_2` (`id`), KEY `touser_idx` (`touser`), KEY `from_idx` (`from2`), FOREIGN KEY (`touser`) REFERENCES `usertable` (`name`) ) TYPE=InnoDB CHARSET=latin1 A quick search on Google shows that 4.0.x users got something like CONSTRAINT '0_22' before the FOREIGN KEY declaration and were able to use that number. Any idea how I get this? I'd really like to drop this key so I can re-add it with an ON DELETE and ON UPDATE clause; :-) Thanks, Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra ---
RE: Dropping a foreign key in 4.1.x
Nevermind, I found out that the DROP feature is only available in = 4.0.13 or = 4.1.1 ...*sigh* Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra --- -Original Message- From: Joshua Thomas [mailto:[EMAIL PROTECTED] Sent: Sunday, January 04, 2004 12:33 AM To: '[EMAIL PROTECTED]' Subject: Dropping a foreign key in 4.1.x How does one drop a foreign key from a InnoDB table in mySQL 4.1.x? The manual says ALTER TABLE yourtablename DROP FOREIGN KEY internally_generated_foreign_key_id You have to use SHOW CREATE TABLE to determine the internally generated foreign key ID when you want to drop a foreign key. However, my CREATE TABLE statment in 4.1.0 does not return an intertnally generated foreign key ID, but something like this: mysql SHOW CREATE TABLE cm_mail \G *** 1. row *** Table: cm_mail Create Table: CREATE TABLE `cm_mail` ( `id` int(15) NOT NULL auto_increment, `view` varchar(30) NOT NULL default '', `touser` varchar(30) NOT NULL default '', `from2` varchar(30) NOT NULL default '', `message` mediumtext NOT NULL, `datestamp` int(11) NOT NULL default '0', `status` varchar(30) NOT NULL default '', PRIMARY KEY (`id`), KEY `id_2` (`id`), KEY `touser_idx` (`touser`), KEY `from_idx` (`from2`), FOREIGN KEY (`touser`) REFERENCES `usertable` (`name`) ) TYPE=InnoDB CHARSET=latin1 A quick search on Google shows that 4.0.x users got something like CONSTRAINT '0_22' before the FOREIGN KEY declaration and were able to use that number. Any idea how I get this? I'd really like to drop this key so I can re-add it with an ON DELETE and ON UPDATE clause; :-) Thanks, Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra ---
Unexpected restarts
My mySQL server unexpectedly restarts itself from time to time. I am able to verify this as the server uptime resets. I caught it 'in the act' today, but can't tell what the problem is. Does anyone know what causes this behavior? Thanks, Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra ---
RE: Haw to get specific result?
'SELECT product, COUNT(product) FROM table GROUP BY product' will do this, I believe. Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra --- -Original Message- From: Pawel Filutowski [mailto:[EMAIL PROTECTED] Sent: Monday, December 15, 2003 7:49 AM To: [EMAIL PROTECTED] Subject: Haw to get specific result? Hello, I have table like this: id | product 0 | switch 1 | switch 2 | hub 3 | hub 4 | hub 5 | wire 6 | wire 7 | wire 8 | wire 9 | wire . . I`m looking for query which give me result as array: 0 - count of 'switch' 1 - count of 'hub' 2 - count of 'wire' Haw to do it ?? Regards, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
It is a mystery to me how the PostGreSQL work. I cannot recomm to use any feature discovered in PostGreSQL since some of the more uncommon feature are broken. Would you care to elaborate? I've used both PostgreSQL and mySQL, but certainly not all features, and I'm curious about what issues you had with PGSQL. Cheers, Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra ---
RE: Questions about indexing
Find out the real way: Use EXPLAIN and BENCHMARK() commands to get the speed of your operations with and without an index. That's the only way to know for certain. Cheers, Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra --- -Original Message- From: Dan Anderson [mailto:[EMAIL PROTECTED] Sent: Monday, December 15, 2003 1:18 PM To: Chris Elsworth Cc: [EMAIL PROTECTED] Subject: Re: Questions about indexing With such a small database it really boils down to just being tidy; you don't want indexes you're not going to use. Well the database is going to be like 200MB and executing several hundred queries a minute. Thus my concern about speed. Is a P4 w/ 1GB RAM going to choke and die, or will indexing help out? Thanks in advance, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: Questions about indexing
I agree with all your points. Indexes are very useful, even necessary in some situations. On the other hand, they also use up diskspace, and can slow INSERTs and UPDATEs. If you have a very small database, you may simply not need them. Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra --- -Original Message- From: Chris Nolan [mailto:[EMAIL PROTECTED] Sent: Monday, December 15, 2003 7:09 PM To: Joshua Thomas Cc: 'Dan Anderson'; Chris Elsworth; [EMAIL PROTECTED] Subject: Re: Questions about indexing Even if your database fits entirely in memory, not having indexes in place would not be a good idea. In an interview Monty did regarding in-memory databases, he very specifically made the point that where your database is sitting will never remove the need for various types of index. From some extremely boring computer science/software engineering subject I did a few years back: 1. If there's no index to play with, you'll need to do a full table scan to find the rows matching the conditions predicated in your WHERE clause. 2. If there is an index or two to play with, you'll at the very least cut down the number of rows that will be read from disk for further inspection should the optimiser choose to use them (look up FORCE INDEX(...) in MySQL) 3. For B-Tree and B+Tree indexes, you're looking at an average of 1-2 probes (with a probe being roughly the same expense I/O-wise as reading a row) for finding an item matching an = condition and the same for finding the beginning/end of a range for range-style queries (eg: ... WHERE a 20 AND a 40). Hope this helps! Regards, Chris Joshua Thomas wrote: Find out the real way: Use EXPLAIN and BENCHMARK() commands to get the speed of your operations with and without an index. That's the only way to know for certain. Cheers, Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra --- -Original Message- From: Dan Anderson [mailto:[EMAIL PROTECTED] Sent: Monday, December 15, 2003 1:18 PM To: Chris Elsworth Cc: [EMAIL PROTECTED] Subject: Re: Questions about indexing With such a small database it really boils down to just being tidy; you don't want indexes you're not going to use. Well the database is going to be like 200MB and executing several hundred queries a minute. Thus my concern about speed. Is a P4 w/ 1GB RAM going to choke and die, or will indexing help out? Thanks in advance, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: storing .tar files in mysql
you could very well do that, and frankly that is how alot of websites work. Yep, including one I run. That site has to generate img and a href links for visitors, and it seems far easier to return /pics/imagefoo.jpg then the image itself and decide how to embed that into the page. But storing the actual binary file, weither it is a .jpg or .tar, allows your application/website/whatever to be independent of your files location on the server. Is this really such a problem? When I migrated the above-mentioned website, I had some minor path issues which resolved with symbolic links. If your path for images changes, make a symlink to simulate where it used to be; or use an UPDATE statement to change the path prefix on the images in the database. If you really want to be clever, use a single-table row for the prefix (/home/foo/myimages) and another table for the actually names (foo.jpg) and just update the prefix when location changes. This way, when you select a specific row, you will have the binary... regardless of path locations. It also makes data organization cleaner as well. If you decide you don't need a binary anymore and delete it from the database, it will be gone. A cron job which does a SELECT imagename FROM imagetable, then compares to the directory, and removes the non-exisiting images, would also work. Or you could write a database trigger to call out and delete the image when the row was deleted. I do admit it's less nice than being able to just drop the image from the table, but you also lose the ability to manipulate the content on the filesystem level. For example, in your .tar example, how are you going to view the contents of one of those .tar files? You'll have to SELECT it out of the database, write it somewhere, then view it; if you want to update it, you have to SELECT it out, edit it, and UPDATE the table... where as just storing the path will not, which can get pretty ugly. I hope that helps Works like a charm for us. The downsides I see to storing in the database are: * More overhead reading and writing from the database * Much much larger database sizes. For me it's easy to backup and restore my database. If my 2GB+ of images were IN the database, well, that would be a different story. * I can schedule backups of the database, and filesystem content, at different times; this reduces the impact if something goes wrong during a backup. Of course, I do see the advantages of embedding the images, tar files, etc into the database; I just think it has enough issues of it's own that I would caution against doing it. Here's another idea to chew on: My full-time employer runs over a thousand sites for newspapers across the country. We have image names embedded into the database, not the data. Why? Well, we store images on NAS (Network Attached Storage) devices. We have several of them, and use custom scripts to keep the content on them the same. When we have a failure on one, we simply update a piece of code which defines which server to read from. Now, if we put those images into the database, we'd have a few issue: * Our database size would grow far, far beyond the current size, which is over 60GB. * Restores to the database would be much slower, and in our case, if we have a failure, we'd rather get everything up fast and then fix images then wait longer and get both. Time == money to our customers. * Instead of returning the filename to the application software (ASP and CF in our case), you'd return the whole image, and that's going to be a huge drain on server CPU. We'd need something like 2x the CPU power we have now, with many more high-speed disks. (and 15K RPM isn't cheap!) * Replication from the publisher to the subscribers would be much more intensive and require more bandwith and CPU power, again driving our hardware needs up. You could argue that we'd loose the need for the NASes. I haven't done a comparison on how the pricing would work. That's my .02$ and a then some. Joshua Thomas dan Joshua Thomas wrote: Can I ask why? Why not define a char(50) (or whatever size) with the relative or complete path to the .tar file? Storing it in your database would create huge row sizes. Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra --- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, December 12, 2003 3:55 PM To: [EMAIL PROTECTED] Subject: storing .tar files in mysql Hi all, I am new to mysql and I was wondering if someone could point me in the right direction on how to store .tar and .tar.gz (bzip2) files inside a mysql database. I have googled to try and find some help there but most of the hits come back with binary image files. I have gone thru the mysql tutorial and I can
RE: storing .tar files in mysql
With the NAS - what's to say that MySQL's retrieval and network protocol is not more efficient than whatever is running on your NAS boxes? Well, currently we work like so: Client - Webserver/Application Server - Database The database returns file names to the application/webserver (yes, we're not fully three-tiered) which returns to the client, and the client must then generate more calls for images: Client - Webserver - Image Storage So I have 1 database call and several addtl HTTP calls for each page call. At our stage, the bandwith from the NAS to the webservers are not a limiting factor. If we put everything into the database, then I have each call for an image go to the database (unless there is a better method?), so I have several database calls and several HTTP calls for each primary page. Granted, this is simplified, but it seems like more overhead. What's the best practice method to retrieve images stored in a database for webserving? Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra --- Conversely, MySQL's current 16 MB per transfer limitation may very well not allow it to act in this role at all. Ah, the wonders of open discussion! Best regards, Chris On Sat, 2003-12-13 at 23:33, Joshua Thomas wrote: you could very well do that, and frankly that is how alot of websites work. Yep, including one I run. That site has to generate img and a href links for visitors, and it seems far easier to return /pics/imagefoo.jpg then the image itself and decide how to embed that into the page. But storing the actual binary file, weither it is a .jpg or .tar, allows your application/website/whatever to be independent of your files location on the server. Is this really such a problem? When I migrated the above-mentioned website, I had some minor path issues which resolved with symbolic links. If your path for images changes, make a symlink to simulate where it used to be; or use an UPDATE statement to change the path prefix on the images in the database. If you really want to be clever, use a single-table row for the prefix (/home/foo/myimages) and another table for the actually names (foo.jpg) and just update the prefix when location changes. This way, when you select a specific row, you will have the binary... regardless of path locations. It also makes data organization cleaner as well. If you decide you don't need a binary anymore and delete it from the database, it will be gone. A cron job which does a SELECT imagename FROM imagetable, then compares to the directory, and removes the non-exisiting images, would also work. Or you could write a database trigger to call out and delete the image when the row was deleted. I do admit it's less nice than being able to just drop the image from the table, but you also lose the ability to manipulate the content on the filesystem level. For example, in your .tar example, how are you going to view the contents of one of those .tar files? You'll have to SELECT it out of the database, write it somewhere, then view it; if you want to update it, you have to SELECT it out, edit it, and UPDATE the table... where as just storing the path will not, which can get pretty ugly. I hope that helps Works like a charm for us. The downsides I see to storing in the database are: * More overhead reading and writing from the database * Much much larger database sizes. For me it's easy to backup and restore my database. If my 2GB+ of images were IN the database, well, that would be a different story. * I can schedule backups of the database, and filesystem content, at different times; this reduces the impact if something goes wrong during a backup. Of course, I do see the advantages of embedding the images, tar files, etc into the database; I just think it has enough issues of it's own that I would caution against doing it. Here's another idea to chew on: My full-time employer runs over a thousand sites for newspapers across the country. We have image names embedded into the database, not the data. Why? Well, we store images on NAS (Network Attached Storage) devices. We have several of them, and use custom scripts to keep the content on them the same. When we have a failure on one, we simply update a piece of code which defines which server to read from. Now, if we put those images into the database, we'd have a few issue: * Our database size would grow far, far beyond the current size, which is over 60GB. * Restores to the database would be much slower, and in our case, if we have a failure, we'd rather get everything up fast and then fix
RE: storing .tar files in mysql
Can I ask why? Why not define a char(50) (or whatever size) with the relative or complete path to the .tar file? Storing it in your database would create huge row sizes. Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra --- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, December 12, 2003 3:55 PM To: [EMAIL PROTECTED] Subject: storing .tar files in mysql Hi all, I am new to mysql and I was wondering if someone could point me in the right direction on how to store .tar and .tar.gz (bzip2) files inside a mysql database. I have googled to try and find some help there but most of the hits come back with binary image files. I have gone thru the mysql tutorial and I can create the database and tables, but I can't seem to insert the .tar file properly...Any pointers would be appreicated... Thanks, Jake -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Error
Several possibilites: 1) Your password for user 'master2' is wrong; 2) master2 doesn't have the proper GRANT rights to do what you want; 3) master2 doesn't exist. Check the mySQL manual. Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra --- -Original Message- From: Pushpinder Singh [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 10, 2003 12:14 PM To: [EMAIL PROTECTED] Subject: MySQL Error Hello All, I am trying to import data from a text file into MySQL. I am using phpMyAdmin to control the database. When I try to execute the query I keep getting this error. Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Can anyone throw some light on this. Thanks in advance Pushpinder Singh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Error
If the user master2 did nto exist or have the required GRANT rights, then I wud not have been able to get into the database itslef in the first place. Thanks again though. Any ideas ? If you read the GRANT section of the mySQL manual (http://www.mysql.com/doc/en/GRANT.html) you'll see that you can grant only use of SELECT, or SELECT and UPDATE, and so forth. Your import may be using an option you don't have the correct GRANT rights for. Please review the manual, check your current GRANT rights for your user, adjust as necessary, and let us know how it goes. Cheers, Joshua Thomas
Creating temp table is causing locking?
Hello all, I'm having an issue with mySQL that causes some poor performance. I'm running mysql Ver 13.5 Distrib 4.1.0-alpha, for portbld-freebsd5.1 (i386); front-end is Apache 1.3.27 with PHP 4.1.1. My database is using all MyISAM tables, with 50 or so tables and about 60MB of total data. My overides on variable defaults are: [mysqld] set-variable = max_connections=500 set-variable = table_cache=128 set-variable = key_buffer_size=32M set-variable = query_cache_size=32M set-variable = long_query_time=3 set-variable = tmpdir=/usr/tmp CPU use on the mySQL server is usually 20% - 30%; but every so often it jumps to almost 100% and remains there for some time. My site slows to a crawl. When I check SHOW PROCESSLIST I see a large number of processes in 'Locked' status, and one as 'Creating tmp table'. When I manually kill this process, the server returns to normal. I turned on slow query logging and general query logging, and I'll be looking through them for any strange queries, but aside from that, is there anything I can do to prevent this locking issue? Thanks all, Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra ---
