apache 为什么 DBD::SQLite 不能通过我的 Perl CGI 脚本插入数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1122885/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why can't DBD::SQLite insert into a database through my Perl CGI script?
提问by Todd Hunter
I am running a SQLite database within a Perl CGI script which is being accessed by DBD::SQLite. This is being run as a straight CGI on Apache.
我正在DBD::SQLite访问的 Perl CGI 脚本中运行 SQLite 数据库。这是在 Apache 上作为直接 CGI 运行的。
The DBI connection works fine and selects are able to be run. However, when I attempt to do an insert I get a die with the following error:
DBI 连接工作正常并且可以运行选择。但是，当我尝试插入时，我得到一个带有以下错误的模具：
DBD::SQLite::st execute failed: unable to open database file(1) at dbdimp.c line 402 at index.cgi line 66
I have tried changing the database file permission to 666 to try to fix this however I am still receiving the error.
我已尝试将数据库文件权限更改为 666 以尝试解决此问题，但是我仍然收到错误消息。
Any advice?
有什么建议吗？
回答by Todd Hunter
It looks like the directory needs write permission, the reason is:
看起来目录需要写权限，原因是：
SQLite needs to be able to create a journal file in the same directory as the DB, before any modifications can take place. The journal is used to support transaction rollback.
SQLite 需要能够在与数据库相同的目录中创建日志文件，然后才能进行任何修改。日志用于支持事务回滚。
From: seem to need write permission on db's parent directory
回答by Robert Harvey
SQLite momentarily locks the entire file when it is doing inserts and updates (there is no record-level locking as such). Are you sure you're freeing the locks?
SQLite 在执行插入和更新时会暂时锁定整个文件（因此没有记录级锁定）。你确定要释放锁吗？
The SQLite literature recommends that you start a transaction, collect all of your inserts and updates du jour in that transaction, and then commit. This avoids numerous successive file locks, and improves performance.
SQLite 文献建议您启动一个事务，收集该事务中的所有插入和更新，然后提交。这避免了大量连续的文件锁定，并提高了性能。
回答by hillu
Since SQLite locks the entire database file, you may want to use a timeout-based retry mechanism. I was working on pretty much the same problem when I asked this related question.
由于 SQLite 锁定整个数据库文件，您可能希望使用基于超时的重试机制。我工作在几乎同样的问题，当我问这个相关的问题。
I ended up writing something similar to Mark Fowler's Attemptthat retries if the exception thrown by the sub matches a regular expression, in my case:
我最终写了一些类似于 Mark Fowler's Attempt 的东西，如果 sub 抛出的异常与正则表达式匹配，则重试，在我的例子中：
qr(already in a transaction|database is locked)i
回答by sadesyllas
The path to the directory where the db file resides should have both executable and writable bits set, in order to access it from the script.
Furthermore, if you don't want the db file to be directly accessed (even without the use of special server files), it should have access permissions such as 600 and if the containing directory should not be directly browsed (again, even without the use of special server files) it should have access permissions such as 700.
I use this setup and it works fine both locally and on the server where I host my site.
Of course, the permission of the containing directory cannot be 700 if there exists any other file inside it that should be accessible via html, css or javascript. It should be 755 instead.
db 文件所在目录的路径应该设置可执行位和可写位，以便从脚本访问它。
此外，如果您不希望直接访问 db 文件（即使不使用特殊的服务器文件），它应该具有访问权限，例如 600，并且如果不应该直接浏览包含目录（同样，即使没有使用特殊服务器文件）它应该具有访问权限，例如 700。
我使用此设置并且它在本地和托管我的站点的服务器上都可以正常工作。
当然，如果包含目录中存在其他可以通过html、css或javascript访问的文件，则包含目录的权限不能为700。应该是 755。
回答by mivk
If you don't want to set write permissions on the whole directory as explained in Todd Hunter's answer, you could instead set the journal_modePRAGMA to "MEMORY".
如果您不想按照 Todd Hunter 的回答中的说明对整个目录设置写权限，则可以将journal_modePRAGMA设置为“MEMORY”。
In that case, beware that:
在这种情况下，请注意：
The MEMORY journaling mode stores the rollback journal in volatile RAM. This saves disk I/O but at the expense of database safety and integrity. If the application using SQLite crashes in the middle of a transaction when the MEMORY journaling mode is set, then the database file will very likely go corrupt.
MEMORY 日志模式将回滚日志存储在易失性 RAM 中。这节省了磁盘 I/O，但以牺牲数据库安全性和完整性为代价。如果在设置 MEMORY 日志模式时使用 SQLite 的应用程序在事务中间崩溃，那么数据库文件很可能会损坏。
So whether this is a good solution depends on your database and how it is used.
因此，这是否是一个好的解决方案取决于您的数据库及其使用方式。
Others also mention the temp_store pragma. I didn't need to set that, but it may depend on how the database is used.
其他人还提到了temp_store pragma。我不需要设置它，但这可能取决于数据库的使用方式。
So in your Perl CGI script, you could try this:
所以在你的 Perl CGI 脚本中，你可以试试这个：
$dbh->do("PRAGMA journal_mode = MEMORY");
$dbh->do("PRAGMA temp_store = MEMORY");
