last_time column is too short for the data
flyzipper - July 21, 2008 - 17:02
| Project: | Stock API |
| Version: | 5.x-1.0 |
| Component: | Code |
| Category: | support request |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
Jump to:
Description
After upgrading to Drupal 5.8, I can no longer access the stock module.
When I navigate to the stock page, the browser tries to access the page, but is never successful.
The most relevant log entries I could find are like this ...
Data too long for column 'last_time' at row 1 query: INSERT INTO stockapi VALUES ('^GSPC', 'S&P 500 INDEX', 0, 1260.48, -0.2, 1261.81, 1267.74, 1256, '1982912768', '7/21/2008', 1216660385) in /var/www/iic/includes/database.mysql.inc on line 172.
#1
This is strange. The upgrade of Drupal should not affect data in the stockapi table.
I am moving this to the stock API module.
#2
Is there any additional information I can provide to assist in tracking this down?
Adding ^GSPC seems to be the annoyance. I'm curious if you experience problems on your setup if you attempt to add this index.
http://finance.yahoo.com/q?s=^GSPC
Stock is broken for our user accounts which are following this index, but works for others who are not.
I've been following this index for a while without issue, and am still pretty sure it stopped working when I upgraded the site from 5.7 to 5.8.
Another thing ... those log entries that I included an example of ... there's LOTS of them (11,000 pages since the upgrade).
#3
MySQL database 5.0.51a
PHP 5.2.4-2ubuntu5.1
PHP register globals Disabled
Unicode library PHP Mbstring Extension
Web server Apache/2.2.8 (Ubuntu) PHP/5.2.4-2ubuntu5.1 with Suhosin-Patch
#4
These are the same versions that I use, and stock works fine.
#5
Did you try adding ^GSPC?
#6
No. Only some mutual funds symbols.
#7
COULD you try adding ^GSPC?
#8
No, I can't on the live site. Don't have the time to setup a test site for it.
Hopefully, others who use stock can weigh in with their experience in this.
#9
It looks like the date and time data is either being parsed incorrectly or reported differently (which would also cause incorrect parsing). I suspect the latter, or there would be more errors and bug reports.
In the sample below, the first line is the field names, the second line is from your error message and the third line is what the data should look like (I just filled in a random time):
symbol name current_price change_amt opening high low volume last_date last_time updated'^GSPC', 'S&P 500 INDEX', 0, 1260.48, -0.2, 1261.81, 1267.74, 1256, '1982912768', '7/21/2008', 1216660385
'^GSPC', 'S&P 500 INDEX', 0, 1260.48, -0.2, 1261.81, 1267.74, 1256, '7/21/2008', '1:43pm', 1216660385
I'm seeing the same thing on my site (http://www.oadaeh.com/), except that I don't get the error. It just truncates the data and places it where it thinks it should go.
(Man, I spent way too much time on something I haven't done anything with for nearly two years. The plus side is that now all my sites are running the latest version of Drupal. :^))
#10
This is the table definition that I have:
+---------------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| symbol | varchar(10) | NO | PRI | | |
| name | varchar(50) | NO | | | |
| current_price | float | NO | | 0 | |
| change_amt | float | NO | | 0 | |
| opening | float | NO | | 0 | |
| high | float | NO | | 0 | |
| low | float | NO | | 0 | |
| volume | int(10) | NO | | 0 | |
| last_date | varchar(10) | NO | | | |
| last_time | varchar(8) | NO | | | |
| updated | int(10) | NO | | 0 | |
+---------------+-------------+------+-----+---------+-------+
The last_date column is varchar(10), but last_time is varchar(8) and that is too short for the data.
Seems the INSERT in the original message is using '7/12/2008' for the time field, which is not correct.
#11
Well, I "fixed" my problem by reaching into the database and removing the ^GSPC symbol from the accounts which were following it.
I'd like to see the StockAPI module fixed to better handle data mismatches though.
Thanks for everyone's insights.
#12
Hi all--
The issue is that the current parser doesn't deal well if a field returned by Yahoo contains an internal comma, as is the case with the S&P 500 symbol, ^GSPC-- the returned name for that field is "S&P 500 INDEX,RTH". So as you can see, just splitting on commas will mess up the field order.
I've put together this HACKY way of doing this that uses regex to split the arrays. I'm sure there's a smarter way to go about this, but hopefully someone more gifted with regex than me will take the reins from here.
To implement this, go into the stockapi,module file:
Comment out line 112: ($data = explode(',', str_replace('"', '', $result->data));
paste the following code in immediately following line 112:
preg_match_all('/("[^"\r\n]*")?,(?![^",\r\n]*"$)/', $result->data, $matches, PREG_PATTERN_ORDER);$other_matches = preg_split('/("[^"\r\n]*")?,(?![^",\r\n]*"$)/', $result->data);
$data = array();
for ($i=0; $i < count($other_matches); $i++) {
$data[] .= str_ireplace('"', '', (($matches[1][$i]) ? $matches[1][$i] : $other_matches[$i] ));
}
Hope this helps someone.