Linux下的BULK INSERT [英] BULK INSERT under Linux

问题描述

我在Linux上遇到了很多麻烦，其中一个有效的路径名被拒绝：

 ＃sqlcmd -S localhost -d leak -U sa -Q" BULK INSERT leak FROM'/ tmp / data.txt'" 
消息12703，级别16，状态1，服务器mssql，行1 
引用的外部数据源"（null）"未找到。 
 
＃ll /tmp/data.txt 
 -rw-r  -  r-- 1 root root 30M Feb 20 02:40 /tmp/data.txt

建议的解决方法是用反斜杠替换斜杠并在前面添加C：\不起作用：

＃sqlcmd -S localhost -d leak  - U sa -Q"BULK INSERT leak FROM'C：\\tmp\\data.txt'" 
消息12703，级别16，状态1，服务器mssql，行1 
引用的外部数据源"（null）"找不到。

所以，我调查了一下，通过在守护进程上运行strace我发现了这个：

 #silce -fp 3198 -e open 
 strace：进程3198附有175个线程
 [pid 3202] open（" / proc / self / status"，O_RDONLY）= 170 
 [pid 3202] open（" / proc / meminfo"，O_RDONLY）= 170 
 [pid 3321] open（" /"，O_RDONLY | O_NONBLOCK | O_DIRECTORY | O_CLOEXEC）= 175 
 [pid 3321] open（ " / tmp /"，O_RDONLY | O_NONBLOCK | O_DIRECTORY | O_CLOEXEC）= 175 
 [pid 3321] open（" /tmp/data.txt" ;, O_RDONLY）= 175 
 [pid 3321] open（" /tmp/data.txt"，O_RDONLY | O_DIRECT）= -1 EINVAL（无效参数）

open（）失败，因为tmpfs不支持O_DIRECT 文件系统。所以我在根目录中移动了文件并使其可以访问：

 #ll /data.txt 
 -rw-rw-rw- 1 root root 30M Feb 20 02:28 /data.txt 
 
＃ll /data.txt 
 -rw-rw-rw- 1 root root 30M Feb 20 02:28 /data.txt 
＃sqlcmd -S localhost -d leak -U sa -Q" BULK INSERT leak FROM'/data.txt'" 
消息4860，级别16，状态1，服务器mssql，行1 
无法批量加载。文件"/data.txt"不存在或您没有文件访问权限。

但这次服务器甚至没有尝试访问该文件。将文件移动到除root之外的世界可访问目录中，修复它：

＃sqlcmd -S localhost -d leak -U sa -Q" BULK INSERT leak来自'/media/data.txt'" 
 
（受影响的1000000行）

解决方案

Hi
Matteo Croce，

 

根据您的描述，您已解决了问题。问题似乎是由于SQLCMD无法访问该文件夹。

 

如果你有，如果你已经解决了问题，我 n为了关闭此帖子，请将有用的回复标记为答案或标记您自己的
答案。 通过这样做，  ;这将使所有具有此类问题的社区成员受益。  您的贡献得到高度赞赏。

 

最好的问候，

拉结

I had a lot of troubles with BULK INSERT on linux, where a valid path name was rejected:

# sqlcmd -S localhost -d leak -U sa -Q "BULK INSERT leak FROM '/tmp/data.txt'"
Msg 12703, Level 16, State 1, Server mssql, Line 1
Referenced external data source "(null)" not found.

# ll /tmp/data.txt
-rw-r--r-- 1 root root 30M Feb 20 02:40 /tmp/data.txt

And the suggested workaround to replace slashes with backslashes and prepending C:\ didn't work:

# sqlcmd -S localhost -d leak -U sa -Q "BULK INSERT leak FROM 'C:\\tmp\\data.txt'"
Msg 12703, Level 16, State 1, Server mssql, Line 1
Referenced external data source "(null)" not found.

So, I investigated a bit, and by running strace on the daemon I found this:

# strace -fp 3198 -e open
strace: Process 3198 attached with 175 threads
[pid  3202] open("/proc/self/status", O_RDONLY) = 170
[pid  3202] open("/proc/meminfo", O_RDONLY) = 170
[pid  3321] open("/", O_RDONLY|O_NONBLOCK|O_DIRECTORY|O_CLOEXEC) = 175
[pid  3321] open("/tmp/", O_RDONLY|O_NONBLOCK|O_DIRECTORY|O_CLOEXEC) = 175
[pid  3321] open("/tmp/data.txt", O_RDONLY) = 175
[pid  3321] open("/tmp/data.txt", O_RDONLY|O_DIRECT) = -1 EINVAL (Invalid argument)

The open() fails because O_DIRECT is not supported by the tmpfs  filesystem. So I moved the file in the root and I made it world accessible:

# ll /data.txt
-rw-rw-rw- 1 root root 30M Feb 20 02:28 /data.txt

# ll /data.txt
-rw-rw-rw- 1 root root 30M Feb 20 02:28 /data.txt
# sqlcmd -S localhost -d leak -U sa -Q "BULK INSERT leak FROM '/data.txt'"
Msg 4860, Level 16, State 1, Server mssql, Line 1
Cannot bulk load. The file "/data.txt" does not exist or you don't have file access rights.

But this time the server doesn't even tries to access the file. Moving the file in a world accessible directory other than the root, fixed it:

# sqlcmd -S localhost -d leak -U sa -Q "BULK INSERT leak FROM '/media/data.txt'"

(1000000 rows affected)

解决方案

Hi Matteo Croce,

 

Per your description , you have solved your issue . The problem seems to be caused by SQLCMD not being able to access that folder.

 

If you have If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers or mark your own answer. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

 

Best Regards,

Rachel

这篇关于Linux下的BULK INSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！