RE: Error Code 28
Morning Jorg, Thanks for the reply. The limit clause restricts the data returned, but not the data examined. You ask the server to examine a certain set of rows (where ...), sort it (order by ...), and then to return a limited number of these rows (limit ...). As the data only exist in some other order and you (probably) do not have a way to access them in the order desired (or do you have an index on bluetooth_session.created?), the server must first sort all qualifying rows. This is where it needs so much space. This makes perfect sense, I see what you mean about the fact that the data has to effectively be collated in its entirety before it is ordered and limited, that's a very fair statement. Somebody else has already commented on the limit clause you use, I need not repeat that. They did indeed, I hadn't noticed this myself, the SQL is produced by an ORM so I'll examine how I'm using that, thanks to all who pointed that one out. I strongly suspect an index on bluetooth_session.created would help, both in evaluating the where condition and in avoiding the sort for order by, and so both reduce the time and avoid the temp space. I can see your point on this, I've not worked with index's at all at this point in time so I'll give them a shot and see what difference that makes, the created date definitely makes a very strong candidate as this is generally how the data is queried, so that'll be my first index, I also regularly query on the address column so I'll be sure to try indexing that too. Disclaimer: Not tested, not checked - just assuming. I really appreciate the suggestions, I certainly see the logic in them, let's hope they bare fruit :-) Cheers, Heston -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error Code 28
Hi Rob, Thanks for getting back to me on this. You were right on the money with the imposed quota on that directory, I tried copying some large files across to the folder and found it limited to 8Mb for some reason, I'll look into removing that limit. One thing which is puzzling me at the moment is why its producing such large record sets from my query. Even when limiting the query to 700 records it still exceeds this 8Mb limit. The query is quite basic and is only returning very simple strings and integers, no more than maybe 8 chars long, like so: SELECT bluetooth_session.bluetooth_session_id AS bluetooth_session_bluetooth_session_id, bluetooth_session.result AS bluetooth_session_result, bluetooth_session.address AS bluetooth_session_address, bluetooth_session.message_id AS bluetooth_session_message_id, bluetooth_session.campaign_id AS bluetooth_session_campaign_id, bluetooth_session.created AS bluetooth_session_created, bluetooth_session.modified AS bluetooth_session_modified FROM bluetooth_session WHERE bluetooth_session.created %s ORDER BY bluetooth_session.created LIMIT 1, 699 bluetooth_session.result and bluetooth_session.address are both 8 character varchars and the rest are integers and dates. Would you really expect a record set from this query to be so large? Is there any way to make it smaller and more efficient? Cheers Rob, Heston -Original Message- From: Rob Wultsch [mailto:[EMAIL PROTECTED] Sent: 21 October 2008 16:49 To: Heston James - Cold Beans Cc: mysql@lists.mysql.com Subject: Re: Error Code 28 On Tue, Oct 21, 2008 at 8:26 AM, Heston James - Cold Beans [EMAIL PROTECTED] wrote: Afternoon all, I have a SELECT query which is returning the following error: (InternalError) (3, Error writing file '/tmp/MYqlGnfn' (Errcode: 28)) After doing a little searching on google all signs seem to point to a lack of disk space to be able to store the query results. However, I have several hundred MB left on the storage device and the database itself, in its entirety is only around 19Mb in size so it sees very strange to be causing that. Here is a quick output from 'fd -h' which displays the space on my storage device. FilesystemSize Used Avail Use% Mounted on rootfs973M 595M 330M 65% / udev 10M 20K 10M 1% /dev /dev/disk/by-label/ROOT_FS 973M 595M 330M 65% / /dev/disk/by-label/ROOT_FS 973M 595M 330M 65% /dev/.static/dev tmpfs 126M 0 126M 0% /lib/init/rw tmpfs 126M 0 126M 0% /dev/shm tmpfs 8.0M 0 8.0M 0% /rw/tmp Can anyone offer any suggestions as to what might be causing this issue and anything I can do to correct this? I'd really appreciate some help. I'm running MySQL 5 on a Debian based system. If you need any more information what so ever, please let me know. Cheers in advance, Heston 1. Just because your data set is small does not mean that mysql will not create a larger temporary file to store a temporary table. 2. If I had to guess I would say some sort of quota is in effect, also possibly for /tmp/ in particular. -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error Code 28
Afternoon all, I have a SELECT query which is returning the following error: (InternalError) (3, Error writing file '/tmp/MYqlGnfn' (Errcode: 28)) After doing a little searching on google all signs seem to point to a lack of disk space to be able to store the query results. However, I have several hundred MB left on the storage device and the database itself, in its entirety is only around 19Mb in size so it sees very strange to be causing that. Here is a quick output from 'fd -h' which displays the space on my storage device. FilesystemSize Used Avail Use% Mounted on rootfs973M 595M 330M 65% / udev 10M 20K 10M 1% /dev /dev/disk/by-label/ROOT_FS 973M 595M 330M 65% / /dev/disk/by-label/ROOT_FS 973M 595M 330M 65% /dev/.static/dev tmpfs 126M 0 126M 0% /lib/init/rw tmpfs 126M 0 126M 0% /dev/shm tmpfs 8.0M 0 8.0M 0% /rw/tmp Can anyone offer any suggestions as to what might be causing this issue and anything I can do to correct this? I'd really appreciate some help. I'm running MySQL 5 on a Debian based system. If you need any more information what so ever, please let me know. Cheers in advance, Heston
RE: Deployment of Database in a DEB package.
Andre, Dave, Thanks for pointing this out guys. I would certainly like to think about this and see what the best option is. Dave, we can effectively have the scripts do anything we like in the DEB package so we could certainly have it request the username and password from somewhere, either the user or a remote server. In all honest though, the data which is kept in the database, or the database itself is of no real consequence at all and if it call hot destroyed it really wouldnt matter in the slightest, it merely serves as a basic storage mechanism for an application. Cheers, Heston -Original Message- From: Ellison, David [mailto:[EMAIL PROTECTED] Sent: 03 October 2008 07:35 To: mysql@lists.mysql.com Subject: RE: Deployment of Database in a DEB package. That's true, you would need to consider that risk. Can a DEB package ask for information before deployment? If so you could ask for the username/password and run the mysql command using the provided info. Therefore not risking the problem of having a username/password within the files. Although I am making a big assumption on whether you can grab input and use that in scripts of course :) Dave -Original Message- From: Andre Matos [mailto:[EMAIL PROTECTED] Sent: 02 October 2008 17:51 To: Heston James - Cold Beans Cc: mysql@lists.mysql.com Subject: Re: Deployment of Database in a DEB package. Just be aware about the security. I use shell or perl scripts to make automatic dumps (backups) or updates/changes in the database. Unfortunately, because they are automatic scripts, I need to add the username and password inside the scripts and if someone else have access to these scripts (e.g., read access), he/she will see this information. Andre On 2-Oct-08, at 11:57 AM, Heston James - Cold Beans wrote: Dave, This looks like a perfect solution. We can indeed use a shell script for the task in hand and knowing that we an action the .sql script so simply from the command line like that makes life very simple indeed :-) I can't see this will cause too many challenges. Thanks Dave, I appreciate it a great deal. Heston -Original Message- From: Ellison, David [mailto:[EMAIL PROTECTED] Sent: 02 October 2008 07:24 To: mysql@lists.mysql.com Subject: RE: Deployment of Database in a DEB package. I know nothing of DEB packages. However, when we need to change / make a database etc on automatic scripts. We would write the script and save it to a .sql file on console. We would then create a shell script that had something like: mysql -u username -p etc yourinsertsql.sql Essentially the username would have the ability to create tables/ db's if the DB does not exist already, so make sure the user exists first. If you wanted to remove the DB afterwards, although if a DEB can do it, ask first if you want to remove the DB. Have another .sql script with essentially dropping the database. Then same again a shell that: mysql -u username -p etc yourremovesql.sql This is of course assuming you can refer to a shell script in a DEB package. But from what I have seen when using them, I would guess they are used. Hope this helped. Dave -Original Message- From: Heston James - Cold Beans [mailto:[EMAIL PROTECTED] Sent: 01 October 2008 15:22 To: mysql@lists.mysql.com Subject: Deployment of Database in a DEB package. Afternoon All, I'm hoping you'll have some recommendations for me on this little challenge. I have an application which we're preparing to deploy in a DEB package using APT on Debian based systems, although I would imagine this very same rule/process applies to other packaging methods. On the back end of my application we have a MySQL Database which needs to be created as part of the deployment process, which is automated in a script, usually SHELL or Perl, something like that. How would you deploy the database using an automated script? Would you have a SQL script which you action through the console to create the database? What's the simplest way to action a SQL script to run on the local server? Do we need to keep in mind what permissions/users are assigned to the database? In addition to this create script, we'll also need to have an automated, scripted process for removing the database if the package is removed, I'd be interested to get your thoughts on that too. Thanks in advance for any suggestions guys, Heston This message has been scanned for viruses by MailControl - (see http://bluepages.wsatkins.co.uk/?6875772) This email and any attached files are confidential and copyright protected. If you are not the addressee, any dissemination of this communication is strictly prohibited. Unless otherwise expressly agreed in writing, nothing stated in this communication shall be legally binding. The ultimate parent company of the Atkins Group is WS Atkins plc. Registered in England No. 1885586
RE: Deployment of Database in a DEB package.
Dave, This looks like a perfect solution. We can indeed use a shell script for the task in hand and knowing that we an action the .sql script so simply from the command line like that makes life very simple indeed :-) I can't see this will cause too many challenges. Thanks Dave, I appreciate it a great deal. Heston -Original Message- From: Ellison, David [mailto:[EMAIL PROTECTED] Sent: 02 October 2008 07:24 To: mysql@lists.mysql.com Subject: RE: Deployment of Database in a DEB package. I know nothing of DEB packages. However, when we need to change / make a database etc on automatic scripts. We would write the script and save it to a .sql file on console. We would then create a shell script that had something like: mysql -u username -p etc yourinsertsql.sql Essentially the username would have the ability to create tables/db's if the DB does not exist already, so make sure the user exists first. If you wanted to remove the DB afterwards, although if a DEB can do it, ask first if you want to remove the DB. Have another .sql script with essentially dropping the database. Then same again a shell that: mysql -u username -p etc yourremovesql.sql This is of course assuming you can refer to a shell script in a DEB package. But from what I have seen when using them, I would guess they are used. Hope this helped. Dave -Original Message- From: Heston James - Cold Beans [mailto:[EMAIL PROTECTED] Sent: 01 October 2008 15:22 To: mysql@lists.mysql.com Subject: Deployment of Database in a DEB package. Afternoon All, I'm hoping you'll have some recommendations for me on this little challenge. I have an application which we're preparing to deploy in a DEB package using APT on Debian based systems, although I would imagine this very same rule/process applies to other packaging methods. On the back end of my application we have a MySQL Database which needs to be created as part of the deployment process, which is automated in a script, usually SHELL or Perl, something like that. How would you deploy the database using an automated script? Would you have a SQL script which you action through the console to create the database? What's the simplest way to action a SQL script to run on the local server? Do we need to keep in mind what permissions/users are assigned to the database? In addition to this create script, we'll also need to have an automated, scripted process for removing the database if the package is removed, I'd be interested to get your thoughts on that too. Thanks in advance for any suggestions guys, Heston This message has been scanned for viruses by MailControl - (see http://bluepages.wsatkins.co.uk/?6875772) This email and any attached files are confidential and copyright protected. If you are not the addressee, any dissemination of this communication is strictly prohibited. Unless otherwise expressly agreed in writing, nothing stated in this communication shall be legally binding. The ultimate parent company of the Atkins Group is WS Atkins plc. Registered in England No. 1885586. Registered Office Woodcote Grove, Ashley Road, Epsom, Surrey KT18 5BW. A list of wholly owned Atkins Group companies registered in the United Kingdom can be found at http://www.atkinsglobal.com/terms_and_conditions/index.aspx Consider the environment. Please don't print this e-mail unless you really need to. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Deployment of Database in a DEB package.
Afternoon All, I'm hoping you'll have some recommendations for me on this little challenge. I have an application which we're preparing to deploy in a DEB package using APT on Debian based systems, although I would imagine this very same rule/process applies to other packaging methods. On the back end of my application we have a MySQL Database which needs to be created as part of the deployment process, which is automated in a script, usually SHELL or Perl, something like that. How would you deploy the database using an automated script? Would you have a SQL script which you action through the console to create the database? What's the simplest way to action a SQL script to run on the local server? Do we need to keep in mind what permissions/users are assigned to the database? In addition to this create script, we'll also need to have an automated, scripted process for removing the database if the package is removed, I'd be interested to get your thoughts on that too. Thanks in advance for any suggestions guys, Heston
