-
Notifications
You must be signed in to change notification settings - Fork 161
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Incomplete data transfer when pulling over a large table #111
Comments
Can you please share your test case including your DDL and perhaps with some sample data that can help us reproduce the issue? Can you also do a select count(*) from both remote and local table after you have run the following statement. create table foo as select * from mysql_remote.foo; |
@ahsanhadi Sure! Yea it's really on me to make a reproducible failing test, isn't it? 😝 It may very well be just in the MySQL version I'm using |
@munro Yes if you can share a sample test case, it will help narrow down and investigate the issue... |
Here's the structure of the MySQL table [1], it's running on AWS's RDS MySQL InnoDB [1] CREATE TABLE `foo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`created_on` datetime NOT NULL,
`updated_on` datetime NOT NULL,
`uuid` char(32) NOT NULL,
`foo_type_id` int(11) NOT NULL,
`from_email` varchar(255) NOT NULL,
`subject` varchar(255) NOT NULL,
`raw_email` varchar(200) NOT NULL,
`plaintext` longtext NOT NULL,
`status` int(11) NOT NULL,
`test_group` varchar(25) DEFAULT NULL,
`unique_id` varchar(255) NOT NULL,
`from_name` varchar(70) NOT NULL,
`group_id` int(11),
`message_id` varchar(150),
`campaign_id` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uuid` (`uuid`),
UNIQUE KEY `message_id` (`message_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11370857 DEFAULT CHARSET=utf8; |
I have this similar problem lately and find out that if a column in mysql contains a huge text, mysql_fdw just simply ignore that row and the rows follow it. After some tracing, the problem can be remedied by changing the MAXDATALEN in mysql_fdw.h to a larger value. |
Yes got the same issue with mediumtext , where rows get forgotten. |
Ah! thought I was alone with this problem, but it seems not :-) |
Yeah, it's really annoying. I fixed it by increasing MAXDATALEN, not nice but workable. I dig up the source code, notice a comment somewhere in mysqlIterateForeignScan at mysql_fdw.c, it recognize MYSQL_DATA_TRUNCATED, but did not really handle it properly, it says it will report this error as MYSQL_REPORT_DATA_TRUNCATION in option, not sure how that works. Hopefully, someone with knowledge of both mysql and postgresql library submit a pull request on this. |
Ran this, and it returned successfully, but the pull was incomplete. Pulled down 148,8082 rows from the original 7,958,064 table. Just for context, this table stores message data around ~3 KiB in each column. MySQL says the table is 32.6 GiB (not sure if that's with indexes).
Here's the PG table size—
![screen shot 2016-06-23 at 10 43 06 am](https://cloud.githubusercontent.com/assets/500774/16313902/313b1d66-3930-11e6-9a20-0ca9e7d3a0dc.png)
The text was updated successfully, but these errors were encountered: