R session crash on query #60
Comments
|
I tried to query another table with 400,000 rows and 167 columns of the following structure without any problem: |
|
Not sure if this has anything to do with r-dbi/RMySQL#204. |
|
I tried with yet another table with more than 1M rows and 95 columns of the following structure without any problem: |
|
Thanks. What driver are you building against? Can you please show the complete output of |
|
|
Thanks. What does |
|
The database is hosted on a Fedora 24 server. Here's the output of |
|
On my client machine, there's a MariaDB server, but there's no |
|
Thanks. I was confused by the IIUC, your client (Ubuntu + libmariadbclient-dev) crashes, while the same code on the server (Fedora 24 + mysql-devel) works. A few things to try:
|
|
I removed |
|
The following is the create statement of the table: |
|
Thanks. Can you replicate with your local MariaDB server, if you copy the data to your local server? |
|
I dump the table to a self-contained |
|
Ouch. So there might be a problem with how the database server transmits the data? Could you try replicating your server setup in a Docker container? |
|
I tried with |
|
I recently added full buffering to work around another problem with the MariaDB libraries. Can you please try |
|
v0.11-7 does not crash. There must be something mishandled in the subsequent changes. |
|
I also notice that with v0.11-8, most other tables are retrievable but much more slowly than on a Fedora client (not the db server). v0.11-7 does not exhibit such performance drawback. |
|
Thanks again. Can you please check |
|
I tried |
|
Sorry for not controlling all the variables. I just notice that the library the package is built against does affect if it crashes. With Following is the crash message using
|
|
Thanks for helping troubleshooting this. In this case, it might help trying to install Connector/C from source, and reinstall I'll try to replicate your setup with a local MySQL server. |
|
I have created a table with your structure with 1e6 random records in my local MySQL server. Neither the |
|
I can only reproduce the crash under the following condition on existing db: DB: MySQL Server 5.7.15 on Fedora 24 with
|
|
Can you replicate it with the following code? I'm using utf8 encoding. library(DBI)
devtools::load_all()
set.seed(20170926)
con <- dbConnect(MariaDB(), dbname = "test")
query <- "DROP TABLE `ASHARECASHFLOW`"
dbExecute(con, query)
query <- "
CREATE TABLE `ASHARECASHFLOW` (
`OBJECT_ID` varchar(100) NOT NULL COMMENT '对象ID',
`S_INFO_WINDCODE` varchar(40) DEFAULT NULL COMMENT 'Wind代码',
`WIND_CODE` varchar(40) DEFAULT NULL COMMENT 'Wind代码',
`ANN_DT` varchar(8) DEFAULT NULL COMMENT '公告日期',
`REPORT_PERIOD` varchar(8) DEFAULT NULL COMMENT '报告期',
`STATEMENT_TYPE` varchar(10) DEFAULT NULL COMMENT '报表类型',
`CRNCY_CODE` varchar(10) DEFAULT NULL COMMENT '货币代码',
`CASH_RECP_SG_AND_RS` decimal(20,4) DEFAULT NULL COMMENT '销售商品、提供劳务收到的现金',
`RECP_TAX_RENDS` decimal(20,4) DEFAULT NULL COMMENT '收到的税费返还',
`NET_INCR_DEP_COB` decimal(20,4) DEFAULT NULL COMMENT '客户存款和同业存放款项净增加额',
`NET_INCR_LOANS_CENTRAL_BANK` decimal(20,4) DEFAULT NULL COMMENT '向中央银行借款净增加额',
`NET_INCR_FUND_BORR_OFI` decimal(20,4) DEFAULT NULL COMMENT '向其他金融机构拆入资金净增加额',
`CASH_RECP_PREM_ORIG_INCO` decimal(20,4) DEFAULT NULL COMMENT '收到原保险合同保费取得的现金',
`NET_INCR_INSURED_DEP` decimal(20,4) DEFAULT NULL COMMENT '保户储金净增加额',
`NET_CASH_RECEIVED_REINSU_BUS` decimal(20,4) DEFAULT NULL COMMENT '收到再保业务现金净额',
`NET_INCR_DISP_TFA` decimal(20,4) DEFAULT NULL COMMENT '处置交易性金融资产净增加额',
`NET_INCR_INT_HANDLING_CHRG` decimal(20,4) DEFAULT NULL COMMENT '收取利息和手续费净增加额',
`NET_INCR_DISP_FAAS` decimal(20,4) DEFAULT NULL COMMENT '处置可供出售金融资产净增加额',
`NET_INCR_LOANS_OTHER_BANK` decimal(20,4) DEFAULT NULL COMMENT '拆入资金净增加额',
`NET_INCR_REPURCH_BUS_FUND` decimal(20,4) DEFAULT NULL COMMENT '回购业务资金净增加额',
`OTHER_CASH_RECP_RAL_OPER_ACT` decimal(20,4) DEFAULT NULL COMMENT '收到其他与经营活动有关的现金',
`STOT_CASH_INFLOWS_OPER_ACT` decimal(20,4) DEFAULT NULL COMMENT '经营活动现金流入小计',
`CASH_PAY_GOODS_PURCH_SERV_REC` decimal(20,4) DEFAULT NULL COMMENT '购买商品、接受劳务支付的现金',
`CASH_PAY_BEH_EMPL` decimal(20,4) DEFAULT NULL COMMENT '支付给职工以及为职工支付的现金',
`PAY_ALL_TYP_TAX` decimal(20,4) DEFAULT NULL COMMENT '支付的各项税费',
`NET_INCR_CLIENTS_LOAN_ADV` decimal(20,4) DEFAULT NULL COMMENT '客户贷款及垫款净增加额',
`NET_INCR_DEP_CBOB` decimal(20,4) DEFAULT NULL COMMENT '存放央行和同业款项净增加额',
`CASH_PAY_CLAIMS_ORIG_INCO` decimal(20,4) DEFAULT NULL COMMENT '支付原保险合同赔付款项的现金',
`HANDLING_CHRG_PAID` decimal(20,4) DEFAULT NULL COMMENT '支付手续费的现金',
`COMM_INSUR_PLCY_PAID` decimal(20,4) DEFAULT NULL COMMENT '支付保单红利的现金',
`OTHER_CASH_PAY_RAL_OPER_ACT` decimal(20,4) DEFAULT NULL COMMENT '支付其他与经营活动有关的现金',
`STOT_CASH_OUTFLOWS_OPER_ACT` decimal(20,4) DEFAULT NULL COMMENT '经营活动现金流出小计',
`NET_CASH_FLOWS_OPER_ACT` decimal(20,4) DEFAULT NULL COMMENT '经营活动产生的现金流量净额',
`CASH_RECP_DISP_WITHDRWL_INVEST` decimal(20,4) DEFAULT NULL COMMENT '收回投资收到的现金',
`CASH_RECP_RETURN_INVEST` decimal(20,4) DEFAULT NULL COMMENT '取得投资收益收到的现金',
`NET_CASH_RECP_DISP_FIOLTA` decimal(20,4) DEFAULT NULL COMMENT '处置固定资产、无形资产和其他长期资产收回的现金净额',
`NET_CASH_RECP_DISP_SOBU` decimal(20,4) DEFAULT NULL COMMENT '处置子公司及其他营业单位收到的现金净额',
`OTHER_CASH_RECP_RAL_INV_ACT` decimal(20,4) DEFAULT NULL COMMENT '收到其他与投资活动有关的现金',
`STOT_CASH_INFLOWS_INV_ACT` decimal(20,4) DEFAULT NULL COMMENT '投资活动现金流入小计',
`CASH_PAY_ACQ_CONST_FIOLTA` decimal(20,4) DEFAULT NULL COMMENT '购建固定资产、无形资产和其他长期资产支付的现金',
`CASH_PAID_INVEST` decimal(20,4) DEFAULT NULL COMMENT '投资支付的现金',
`NET_CASH_PAY_AQUIS_SOBU` decimal(20,4) DEFAULT NULL COMMENT '取得子公司及其他营业单位支付的现金净额',
`OTHER_CASH_PAY_RAL_INV_ACT` decimal(20,4) DEFAULT NULL COMMENT '支付其他与投资活动有关的现金',
`NET_INCR_PLEDGE_LOAN` decimal(20,4) DEFAULT NULL COMMENT '质押贷款净增加额',
`STOT_CASH_OUTFLOWS_INV_ACT` decimal(20,4) DEFAULT NULL COMMENT '投资活动现金流出小计',
`NET_CASH_FLOWS_INV_ACT` decimal(20,4) DEFAULT NULL COMMENT '投资活动产生的现金流量净额',
`CASH_RECP_CAP_CONTRIB` decimal(20,4) DEFAULT NULL COMMENT '吸收投资收到的现金',
`INCL_CASH_REC_SAIMS` decimal(20,4) DEFAULT NULL COMMENT '其中:子公司吸收少数股东投资收到的现金',
`CASH_RECP_BORROW` decimal(20,4) DEFAULT NULL COMMENT '取得借款收到的现金',
`PROC_ISSUE_BONDS` decimal(20,4) DEFAULT NULL COMMENT '发行债券收到的现金',
`OTHER_CASH_RECP_RAL_FNC_ACT` decimal(20,4) DEFAULT NULL COMMENT '收到其他与筹资活动有关的现金',
`STOT_CASH_INFLOWS_FNC_ACT` decimal(20,4) DEFAULT NULL COMMENT '筹资活动现金流入小计',
`CASH_PREPAY_AMT_BORR` decimal(20,4) DEFAULT NULL COMMENT '偿还债务支付的现金',
`CASH_PAY_DIST_DPCP_INT_EXP` decimal(20,4) DEFAULT NULL COMMENT '分配股利、利润或偿付利息支付的现金',
`INCL_DVD_PROFIT_PAID_SC_MS` decimal(20,4) DEFAULT NULL COMMENT '其中:子公司支付给少数股东的股利、利润',
`OTHER_CASH_PAY_RAL_FNC_ACT` decimal(20,4) DEFAULT NULL COMMENT '支付其他与筹资活动有关的现金',
`STOT_CASH_OUTFLOWS_FNC_ACT` decimal(20,4) DEFAULT NULL COMMENT '筹资活动现金流出小计',
`NET_CASH_FLOWS_FNC_ACT` decimal(20,4) DEFAULT NULL COMMENT '筹资活动产生的现金流量净额',
`EFF_FX_FLU_CASH` decimal(20,4) DEFAULT NULL COMMENT '汇率变动对现金的影响',
`NET_INCR_CASH_CASH_EQU` decimal(20,4) DEFAULT NULL COMMENT '现金及现金等价物净增加额',
`CASH_CASH_EQU_BEG_PERIOD` decimal(20,4) DEFAULT NULL COMMENT '期初现金及现金等价物余额',
`CASH_CASH_EQU_END_PERIOD` decimal(20,4) DEFAULT NULL COMMENT '期末现金及现金等价物余额',
`NET_PROFIT` decimal(20,4) DEFAULT NULL COMMENT '净利润',
`UNCONFIRMED_INVEST_LOSS` decimal(20,4) DEFAULT NULL COMMENT '未确认投资损失',
`PLUS_PROV_DEPR_ASSETS` decimal(20,4) DEFAULT NULL COMMENT '加:资产减值准备',
`DEPR_FA_COGA_DPBA` decimal(20,4) DEFAULT NULL COMMENT '固定资产折旧、油气资产折耗、生产性生物资产折旧',
`AMORT_INTANG_ASSETS` decimal(20,4) DEFAULT NULL COMMENT '无形资产摊销',
`AMORT_LT_DEFERRED_EXP` decimal(20,4) DEFAULT NULL COMMENT '长期待摊费用摊销',
`DECR_DEFERRED_EXP` decimal(20,4) DEFAULT NULL COMMENT '待摊费用减少',
`INCR_ACC_EXP` decimal(20,4) DEFAULT NULL COMMENT '预提费用增加',
`LOSS_DISP_FIOLTA` decimal(20,4) DEFAULT NULL COMMENT '处置固定、无形资产和其他长期资产的损失',
`LOSS_SCR_FA` decimal(20,4) DEFAULT NULL COMMENT '固定资产报废损失',
`LOSS_FV_CHG` decimal(20,4) DEFAULT NULL COMMENT '公允价值变动损失',
`FIN_EXP` decimal(20,4) DEFAULT NULL COMMENT '财务费用',
`INVEST_LOSS` decimal(20,4) DEFAULT NULL COMMENT '投资损失',
`DECR_DEFERRED_INC_TAX_ASSETS` decimal(20,4) DEFAULT NULL COMMENT '递延所得税资产减少',
`INCR_DEFERRED_INC_TAX_LIAB` decimal(20,4) DEFAULT NULL COMMENT '递延所得税负债增加',
`DECR_INVENTORIES` decimal(20,4) DEFAULT NULL COMMENT '存货的减少',
`DECR_OPER_PAYABLE` decimal(20,4) DEFAULT NULL COMMENT '经营性应收项目的减少',
`INCR_OPER_PAYABLE` decimal(20,4) DEFAULT NULL COMMENT '经营性应付项目的增加',
`OTHERS` decimal(20,4) DEFAULT NULL COMMENT '其他',
`IM_NET_CASH_FLOWS_OPER_ACT` decimal(20,4) DEFAULT NULL COMMENT '间接法-经营活动产生的现金流量净额',
`CONV_DEBT_INTO_CAP` decimal(20,4) DEFAULT NULL COMMENT '债务转为资本',
`CONV_CORP_BONDS_DUE_WITHIN_1Y` decimal(20,4) DEFAULT NULL COMMENT '一年内到期的可转换公司债券',
`FA_FNC_LEASES` decimal(20,4) DEFAULT NULL COMMENT '融资租入固定资产',
`END_BAL_CASH` decimal(20,4) DEFAULT NULL COMMENT '现金的期末余额',
`LESS_BEG_BAL_CASH` decimal(20,4) DEFAULT NULL COMMENT '减:现金的期初余额',
`PLUS_END_BAL_CASH_EQU` decimal(20,4) DEFAULT NULL COMMENT '加:现金等价物的期末余额',
`LESS_BEG_BAL_CASH_EQU` decimal(20,4) DEFAULT NULL COMMENT '减:现金等价物的期初余额',
`IM_NET_INCR_CASH_CASH_EQU` decimal(20,4) DEFAULT NULL COMMENT '间接法-现金及现金等价物净增加额',
`FREE_CASH_FLOW` decimal(20,4) DEFAULT NULL COMMENT '企业自由现金流量(FCFF)',
`COMP_TYPE_CODE` varchar(2) DEFAULT NULL COMMENT '公司类型代码',
`ACTUAL_ANN_DT` varchar(8) DEFAULT NULL COMMENT '实际公告日期',
`SPE_BAL_CASH_INFLOWS_OPER` decimal(20,4) DEFAULT NULL COMMENT '经营活动现金流入差额(特殊报表科目)',
`TOT_BAL_CASH_INFLOWS_OPER` decimal(20,4) DEFAULT NULL COMMENT '经营活动现金流入差额(合计平衡项目)',
`SPE_BAL_CASH_OUTFLOWS_OPER` decimal(20,4) DEFAULT NULL COMMENT '经营活动现金流出差额(特殊报表科目)',
`TOT_BAL_CASH_OUTFLOWS_OPER` decimal(20,4) DEFAULT NULL COMMENT '经营活动现金流出差额(合计平衡项目)',
`TOT_BAL_NETCASH_OUTFLOWS_OPER` decimal(20,4) DEFAULT NULL COMMENT '经营活动产生的现金流量净额差额(合计平衡项目)',
`SPE_BAL_CASH_INFLOWS_INV` decimal(20,4) DEFAULT NULL COMMENT '投资活动现金流入差额(特殊报表科目)',
`TOT_BAL_CASH_INFLOWS_INV` decimal(20,4) DEFAULT NULL COMMENT '投资活动现金流入差额(合计平衡项目)',
`SPE_BAL_CASH_OUTFLOWS_INV` decimal(20,4) DEFAULT NULL COMMENT '投资活动现金流出差额(特殊报表科目)',
`TOT_BAL_CASH_OUTFLOWS_INV` decimal(20,4) DEFAULT NULL COMMENT '投资活动现金流出差额(合计平衡项目)',
`TOT_BAL_NETCASH_OUTFLOWS_INV` decimal(20,4) DEFAULT NULL COMMENT '投资活动产生的现金流量净额差额(合计平衡项目)',
`SPE_BAL_CASH_INFLOWS_FNC` decimal(20,4) DEFAULT NULL COMMENT '筹资活动现金流入差额(特殊报表科目)',
`TOT_BAL_CASH_INFLOWS_FNC` decimal(20,4) DEFAULT NULL COMMENT '筹资活动现金流入差额(合计平衡项目)',
`SPE_BAL_CASH_OUTFLOWS_FNC` decimal(20,4) DEFAULT NULL COMMENT '筹资活动现金流出差额(特殊报表科目)',
`TOT_BAL_CASH_OUTFLOWS_FNC` decimal(20,4) DEFAULT NULL COMMENT '筹资活动现金流出差额(合计平衡项目)',
`TOT_BAL_NETCASH_OUTFLOWS_FNC` decimal(20,4) DEFAULT NULL COMMENT '筹资活动产生的现金流量净额差额(合计平衡项目)',
`SPE_BAL_NETCASH_INC` decimal(20,4) DEFAULT NULL COMMENT '现金净增加额差额(特殊报表科目)',
`TOT_BAL_NETCASH_INC` decimal(20,4) DEFAULT NULL COMMENT '现金净增加额差额(合计平衡项目)',
`SPE_BAL_NETCASH_EQU_UNDIR` decimal(20,4) DEFAULT NULL COMMENT '间接法-经营活动现金流量净额差额(特殊报表科目)',
`TOT_BAL_NETCASH_EQU_UNDIR` decimal(20,4) DEFAULT NULL COMMENT '间接法-经营活动现金流量净额差额(合计平衡项目)',
`SPE_BAL_NETCASH_INC_UNDIR` decimal(20,4) DEFAULT NULL COMMENT '间接法-现金净增加额差额(特殊报表科目)',
`TOT_BAL_NETCASH_INC_UNDIR` decimal(20,4) DEFAULT NULL COMMENT '间接法-现金净增加额差额(合计平衡项目)',
`S_INFO_COMPCODE` varchar(10) DEFAULT NULL COMMENT '公司ID',
`OPDATE` datetime DEFAULT NULL,
`OPMODE` varchar(1) DEFAULT NULL,
PRIMARY KEY (`OBJECT_ID`),
KEY `STATEMENT_TYPE` (`STATEMENT_TYPE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"
dbExecute(con, query)
tbl <- dbReadTable(con, "ASHARECASHFLOW")
N <- 1e6
data <- matrix(rnorm(N * ncol(tbl)), ncol = ncol(tbl))
colnames(data) <- colnames(tbl)
df <- as.data.frame(data)
df$OPDATE <- Sys.Date()
df$OPMODE <- rep(letters, N)[seq_len(N)]
df$COMP_TYPE_CODE <- rep(LETTERS, N)[seq_len(N)]
df$OBJECT_ID <- as.character(df$OBJECT_ID)
print(sum(duplicated(df$OBJECT_ID)))
dbWriteTable(con, "ASHARECASHFLOW", df, append = TRUE)
# Read table
data <- dbReadTable(con, "ASHARECASHFLOW") |
krlmlr
added a commit
that referenced
this issue
Oct 1, 2017
- Test almost all test cases of the DBI specification. - Queries not supported by the prepared statement protocol can now be run via `dbExecute()` or `dbSendStatement()`, the function `mariadbExecQuery()` has been removed (#28). - Avoid storing all results on the client due to instability with large datasets (#60, #61).
|
I test with the |
|
Thank you for extensive testing. |
|
On Ubuntu 18.04, On Ubuntu 16.04, it is still |
|
This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary. |
Sign up for free
to subscribe to this conversation on GitHub.
Already have an account?
Sign in.
I'm querying a database with the latest dev version of RMariaDB.
On Fedora 24, there's no problem, but on Ubuntu 16.04.3, a query of a full table will crash the session. Here's something I find maybe helpful:
The table contains nearly 1M rows and 120 columns, most of them are
DECIMAL. I tried RMySQL and crash again.The following is the structure of the table:
My session info:
The text was updated successfully, but these errors were encountered: