Date range with empty rows (Was: Intra-table join)
On Wed, 13 Oct 2004 20:25:48 -0700, Chris [EMAIL PROTECTED] wrote: create table test_table (d date not null, name char(32) not null, loc integer not null, type integer not null, amount integer, primary key (d, name, loc, type)); snip snip This is how I would do it: SELECT d as day, SUM(amount) as total, SUM(IF(1=loc,amount,0)) as loc1_total, SUM(IF(2=loc,amount,0)) as loc2_total FROM test_table GROUP BY d ; I'm not sure this is the best way to go, as that SUM(IF()) functionality seems a bit unclean to me, but it works. Thanks, that works great! Now, on a somewhat related note, I'm trying to get the data from mysql in a ready to use format with no post-processing required... I'm graphing the data from this query using dates on the x axis. The input to my graph module (GD::Graph) requires a constant-length list. So if any days in my selection range have no data, I need to fill the space with an empy value. For example, say my date range is '2004-10-01' to '2004-10-05' and the query returns: day, amount 2004-10-01, 50 2004-10-02, 100 2004-10-04, 250 I have to do some date manipulation in perl afterward to check for missing values... If I could get a query that returned: day, amount 2004-10-01, 50 2004-10-02, 100 2004-10-03, NULL 2004-10-04, 250 2004-10-05, NULL That would be so much nicer in some cases. I'm thinking it would be sort of like the output from a LEFT JOIN if I had a table containing just a bunch of sequential dates...but I don't...and the dates can actually be arbitrary, so it would have to be a pretty big table, with no real data in it. Does anyone know of some way to fake this date table? (again, I'm using mysql 4.0.16) ...stuck on these LEFT JOINS lately for some reason ;-) Thanks, -partap -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
relay log hosed
Hi, So I set up replication and everything seemed to be going alright for a couple days until the slave server stopped responding. Any attempts to connect to the slave server hang indefinitely, and if I ssh into the machine, any attempts to access the data disk also hang. I had to hard-boot the server to get any response...the data disk (reiserfs) claimed it was alright, but MySQL says the relay log file is corrupted, verified by mysqlbinlog: [EMAIL PROTECTED] data]# mysqlbinlog stout-relay-bin.001 /dev/null ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 70266926, event_type: 46 ERROR: Could not read entry at offset 756432957 : Error in log format or read error fsck says the file system is consistent. Also, no problems found with the master binlog... Can I just delete the relay-bin files and reset the master info to continue at the point of failure? (assuming the disk is not bad and the data is valid...which it seems to be so far) Thanks, Partap Davis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: relay log hosed
Responding to my own message... Looks like I can't simply delete the relay binlog, since while the slave continued to run it continued to update it's place in the master binlog. So, short of copying all the data over again, what can I do? Can I just pick a random spot in the master binlog that was before the slave choked? Are there any side effects of having some of the replicated commands fail? Most of the updates to the db have been REPLACEs, with a few INSERTs and no DELETEs...If it fails on a few INSERTs is that likely to cause any problems? If it does cause problems, is it possible that failed INSERT commands to the master are causing the slave's relay logs to become corrupt? (There happen to be a good deal of failed inserts to the master as a result of a sloppy script that is run regularly...) I'm really loathe to copy all the data (~250GB) from scratch again, as that took me all day last sunday, and this is the second time my relay logs have been corrupted. My master server: P3 700MHz, 768MB with 3ware IDE RAID0 array My slave server: Dual P3 700MHz, 512MB with Adaptec IDE RAID0 array both running MySQL 4.0.15-Max and linux 2.4.18 kernels Partap Davis wrote: Hi, So I set up replication and everything seemed to be going alright for a couple days until the slave server stopped responding. Any attempts to connect to the slave server hang indefinitely, and if I ssh into the machine, any attempts to access the data disk also hang. I had to hard-boot the server to get any response...the data disk (reiserfs) claimed it was alright, but MySQL says the relay log file is corrupted, verified by mysqlbinlog: [EMAIL PROTECTED] data]# mysqlbinlog stout-relay-bin.001 /dev/null ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 70266926, event_type: 46 ERROR: Could not read entry at offset 756432957 : Error in log format or read error fsck says the file system is consistent. Also, no problems found with the master binlog... Can I just delete the relay-bin files and reset the master info to continue at the point of failure? (assuming the disk is not bad and the data is valid...which it seems to be so far) Thanks, Partap Davis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL crash (replication?)
Hi folks, I just set up a slave server and it seemed to be working alright, catching up with the new data, and then the server suddenly went away. Looking at the slave's data directory, there are approximately 2 relay-bin files. I can't even restart the server now, because it gets a signal 11 immediately. Most of the relay-bin files have nothing in them, but the ones that do have data give me something like this: [EMAIL PROTECTED] data]# mysqlbinlog stout-relay-bin.1588 # at 4 #700101 0:00:00 server id 3 log_pos 0 Rotate to kazoo-binlog.020 pos: 560177478 ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 302717, event_type: 2 ERROR: Could not read entry at offset 47 : Error in log format or read error [EMAIL PROTECTED] data]# I'm not even sure where to start here...I figure I'm going to have to get a fresh copy of all the data from the master, but it's about 350 gigs so that will take a while. Meanwhile I'm trying to figure out how to make this not happen again... Here's a bit from my .err file: 030929 15:28:29 mysqld started 030929 15:28:30 InnoDB: Started 030929 15:28:30 Found invalid password for user: '[EMAIL PROTECTED]'; Ignoring user /usr/sbin/mysqld-max: ready for connections. Version: '4.0.14-Max' socket: '/var/lib/mysql/mysql.sock' port: 3306 030929 19:39:59 mysqld started 030929 19:40:00 InnoDB: Started 030929 19:40:00 Found invalid password for user: '[EMAIL PROTECTED]'; Ignoring user /usr/sbin/mysqld-max: ready for connections. Version: '4.0.14-Max' socket: '/var/lib/mysql/mysql.sock' port: 3306 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=268435456 read_buffer_size=2093056 max_used_connections=2 max_connections=100 threads_connected=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1187439 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x885ff10 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x41f08b78, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80dbe1f 0x40037f75 0x420828fc 0x8351165 0x8350f93 0x8350f54 0x8350f54 0x8350f54 0x8350f54 0x8351b6f 0x83711b9 0x837118d 0x83711c9 0x837118d 0x83711c9 0x837118d 0x837118d 0x83711c9 0x837118d 0x83709b9 0x83520fa 0x8351ed5 0x83508df 0x8136669 0x81108b8 0x814d74c 0x814cf7c 0x80e9363 0x80ea88b 0x80e5ed3 0x80ebe0e 0x80e50bf 0x40034fef 0x420e779a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x8870968 = load data local infile '/db2/data/tmp/FcstHr_ece.txt' replace into table FcstHr_ece thd-thread_id=49 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 031001 00:57:10 mysqld restarted 031001 0:57:11 InnoDB: Started 031001 0:57:11 Found invalid password for user: '[EMAIL PROTECTED]'; Ignoring user /usr/sbin/mysqld-max: ready for connections. Version: '4.0.14-Max' socket: '/var/lib/mysql/mysql.sock' port: 3306 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=268435456 read_buffer_size=2093056 max_used_connections=1 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1187439 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x88323e8 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x41ed7b48, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80dbe1f 0x40037f75
Join using temporary, filesort
I asked this a couple days ago, but never got an answer, so I'll try to include some more detail: MySQL 4.0.12 on linux 2.4.18... I'm doing a join on 2 tables and trying to optimize it but I always end up with Using temporary; Using filesort and the query takes way too long... What can I do to speed this up? Here's my tables: CREATE TABLE `metardata` ( `stn` char(4) NOT NULL default '', `Hr` int(2) NOT NULL default '0', `min` int(2) NOT NULL default '0', `Day` int(2) NOT NULL default '0', `wmoid` int(10) default NULL, `temp` decimal(6,2) NOT NULL default '0.00', `rh` decimal(6,2) default NULL, `wdsp` decimal(6,2) default NULL, `wddir` char(2) default NULL, `dwpt` decimal(6,2) default NULL, `lpre` decimal(6,2) default NULL, `yearmoda` date NOT NULL default '-00-00', PRIMARY KEY (`stn`,`yearmoda`,`Hr`,`min`) ) TYPE=MyISAM CREATE TABLE `FcstHr_avn` ( `model` varchar(12) NOT NULL default '', `yearmoda` date NOT NULL default '-00-00', `modelruntime` int(2) NOT NULL default '0', `modelhr` int(3) NOT NULL default '0', `fyearmoda` date NOT NULL default '-00-00', `fhr` int(2) NOT NULL default '0', `stn` varchar(4) NOT NULL default '', `temp` decimal(6,2) default NULL, PRIMARY KEY (`yearmoda`,`modelruntime`,`modelhr`,`stn`), KEY `stn` (`stn`,`fyearmoda`,`fhr`), KEY `fcst` (`stn`,`yearmoda`,`modelruntime`) ) TYPE=MyISAM MAX_ROWS=4294967295 explain gives me this output: mysql explain select modelruntime, modelhr, avg(m.temp-f.temp) from metardata as m, FcstHr_avn as f where m.yearmoda=f.fyearmoda and m.stn=f.stn and m.hr=f.fhr and m.yearmoda='2003-07-01' and m.stn='kdro' group by modelruntime+modelhr; +---+--+---+-+-+---+ --+--+ | table | type | possible_keys | key | key_len | ref | rows | Extra| +---+--+---+-+-+---+ --+--+ | m | ref | PRIMARY | PRIMARY | 7 | const,const | 18 | Using where; Using temporary; Using filesort | | f | ref | stn,fcst | stn | 11 | const,m.yearmoda,m.Hr | 27 | Using where | +---+--+---+-+-+---+ --+--+ 2 rows in set (0.01 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I display images from a mySQL Database in a web page?
I think there is a way to insert binary image data in your html, but is there any particular reason you need to do that? First you would need to encode it in some ascii equivalent (check w3c.org, I think) ...and it would increase the size of your html page while rendering the browser's image cache useless... This will both increase the page's load time and your server's bandwidth requirements, as well as possibly being a pita to implement :-/ I would suggest you store the image filenames in the table, and paste that into your html... -Partap On 7/15/03 1:25 PM, Dan Anderson [EMAIL PROTECTED] wrote: I have created a BLOB field to store images. Is there any way to embed them within HTML with something like: image start: jpeg /image Thanks in advance, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
