LOAD DATA INFILE
I'm creating a file via PHP after getting information from a log file. I create a new file for data import into a table in MySQL. For some reason now I'm getting this error below. I have no clue what it means. I've checked the file and it is all there and I've even changed the permissions on it so that anyone can do anything with it, but still no luck. Any ideas on what I could do? Can't get stat of './import_file.txt' (Errcode: 13)
RE: LOAD DATA INFILE (SOLVED!)
For any of you that run into this problem I found this: After receiving some help from a friend it turns out that one of the directories within the path to the target destination was not flagged 755, and because of that one ( which happened to be 3 levels back) it didn't allow mysql to properly execute what it needed. So if you run into this simply check each directory within the path and make sure of it's settings -Original Message- From: Jay Paulson (CE CEN) [mailto:[EMAIL PROTECTED] Sent: Wed 1/11/2006 11:17 AM To: mysql@lists.mysql.com Subject: LOAD DATA INFILE I'm creating a file via PHP after getting information from a log file. I create a new file for data import into a table in MySQL. For some reason now I'm getting this error below. I have no clue what it means. I've checked the file and it is all there and I've even changed the permissions on it so that anyone can do anything with it, but still no luck. Any ideas on what I could do? Can't get stat of './import_file.txt' (Errcode: 13)
RE: General Questions regarding mysql and php
I would totally agree with this. I moved from using Fedora Core 3 to SuSE 9.3 and haven't looked back. YaST is one of the best tools out there. With the stuff you have installed it would be best just to start over with a new install. :) jay -Original Message- From: George Law [mailto:[EMAIL PROTECTED] Sent: Sun 1/8/2006 8:42 PM To: Andrew Burrows; MYSQL General List Subject: Re: General Questions regarding mysql and php Andrew, I used to be a big redhat fan - but if you are looking to totally bring everything up to date, I would suggested opensuse. having used redhat for years, Suse's not a big step - everything is still RPM based, however, Suse's admin tool, YaST, kicks butt :) Suse 10 comes with Apache 2, PHP5, and one of the latest 4.X versions of mysql. There are generic RPMs for mysql 5 on mysql's website - but I haven't used them - I am running 5.0.18, but using the binary distro because I needed to run both 4.x and 5.x at the same time to migrate some data. -- George Law - Original Message - From: Andrew Burrows [EMAIL PROTECTED] To: MYSQL General List mysql@lists.mysql.com Sent: Sunday, January 08, 2006 8:01 PM Subject: General Questions regarding mysql and php Hi MYSQL users, Just started playing with mysql apache php and other tricky stuff and have a few question to get me going after many years. Was wondering what the best GUI based administration tool is used today, I lasted used phpMyAdmin, is this still used or are there better applications available? Looking for some basic documentation on MYSQL could someone recommend something online or maybe a book?? I have an old system that will probably need upgrading. Apache 1.3 Mysql 3.22.32 Tomcat 3.1.1 Red Hat 8 Would you recommend upgrading this system or starting from scratch? Thanks in advance. Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with a SELECT query
This helps a ton! Thanks! I didn' tknow about the INET_NTOA() or the
INET_ATON() functions. That is much quicker to query on them than on a char
set of ips. However, I did notice on the mysql web site that these functions
are only available in 5.x but the way the page is set up I'm a little confused
if this is true or not. I'm running 4.1.x and it would be nice to have those
functions.
The page below is where I found information about the function.
http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html
Thanks!
-Original Message-
From: Gleb Paharenko [mailto:[EMAIL PROTECTED]
Sent: Fri 1/6/2006 5:28 AM
To: mysql@lists.mysql.com
Subject: Re: Help with a SELECT query
Hello.
Usually working with IP addresses in a numeric form is faster. Use
INET_NTOA() and INET_ATON() functions to store IP addresses as unsigned
ints. To work with subnetworks instead of like 'xxx.xxx.%' use
ip_address_in_numeric_form between inet_aton('xxx.xxx.0.0') and
inet_aton('xxx.xxx.255.255') or similar condition (check if between
covers the borders of the subnet). %php% are usually slow. Force your
application which inserts data to the database explicitly determine
the type of the content (perhaps, you should add a field which will
indicate the content. I agree, that this is a superfluity, however
the speed of the query is more important).
Jay Paulson (CE CEN) [EMAIL PROTECTED] wrote:
Below is a query I'm trying to create and the sql for the table I'm pulling the
information out of. The query is definitely not the best query out there
especially
since I'm still pretty new with sql. I know there has to be a better way of
getting the
information I want. A little background. I'm parsing an Apache access_log
file and
throwing it all into a DB so I can run some reports on it. What I'm wanting
to get are
certain file types that were downloaded (in this case all .html, .php, .pdf,
.doc, and
.flv files) in a certain date range but grouped by certain ip addresses. Some
groups of
ips might have 2 or 3 subnets it needs to get (ie xxx.xxx.% and yyy.yyy.%).
What needs
to be returned is the count of all the file types that have been downloaded
but grouped
by certain ips that I'm looking for.
I hope I didn't confuse anything because I think I confused myself! ;)
Thanks for any help!
CREATE TABLE `apache_statslog` (
`STATS_ID` int(11) NOT NULL auto_increment,
`ip` varchar(25) default NULL,
`accesstime` datetime default NULL,
`thepage` varchar(250) default NULL,
`thetype` varchar(25) default NULL,
`thecode` char(3) default NULL,
`thebytes` int(11) default NULL,
`theref` varchar(250) default NULL,
`browser` varchar(250) default NULL,
PRIMARY KEY (`STATS_ID`),
KEY `ip` (`ip`),
KEY `accesstime` (`accesstime`),
KEY `thepage` (`thepage`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=59145 ;
SELECT count(swrm.ip) as swrm_page_hits, count(mw.ip) as mw_page_hits,
count(fl.ip) as
fl_page_hits, count(so.ip) as so_page_hits
FROM apache_statslog as swrm, apache_statslog as mw, apache_statslog as fl,
apache_statslog as so
WHERE (swrm.accesstime = '2006-01-01 00:00:00' AND swrm.accesstime =
'2006-01-04
23:59:59')
AND (swrm.ip LIKE 'xxx.xxx.%' OR swrm.ip LIKE 'xxx.xxx.%' OR swrm.ip
LIKE 'xxx.xxx.%')
#this is a group that needs to return a count
AND (mw.ip LIKE 'xxx.xxx.%' OR mw.ip LIKE 'xxx.xxx.%') #this is a
group that needs to
return a count
AND (fl.ip LIKE 'xxx.xxx.%' OR fl.ip LIKE 'xxx.xxx.%') #this is a
group that needs to
return a count
AND (so.ip LIKE 'xxx.xxx.%' OR so.ip LIKE 'xxx.xxx.%') #this is a
group that needs to
return a count
AND (swrm.thepage LIKE '%.html%' OR swrm.thepage LIKE '%.php%' OR
swrm.thepage LIKE
'%.doc%' OR swrm.thepage LIKE '%.pdf%' OR swrm.thepage LIKE '%.flv%')
ORDER BY swrm.accesstime ASC
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with a SELECT query
My quick opinion about this comment below. Unfortunately I'm still using MySQL 3.23.x in production (an on going battle to get them to upgrade is still in progress). However, our development server is using MySQL 4.1.x (yet another on going battle to get them to install 3.23.x). Therefore, having the information with the version number of when a function was introduced would be GREAT! I'm sure I'm not the only one with this confusion. :) If there ever becomes a time where you guys have enough resources to add that into the manual that would GREAT!!! Thanks! -Original Message- From: Stefan Hinz [mailto:[EMAIL PROTECTED] Sent: Fri 1/6/2006 12:40 PM To: Michael Stassen Cc: Jay Paulson (CE CEN); Gleb Paharenko; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Help with a SELECT query [1] The reasoning behind this: Is it relevant for a 5.0 user to know that INET_ATON() was introduced in 3.23.15? We don't think it is, and many users had complained that the manual was full of clutter like that. Regards, Stefan -- Stefan Hinz [EMAIL PROTECTED] MySQL AB Documentation Team Lead Skype: stefanhinz SIP: 4429 Desk: +49308270294-0 Fax: -1 TZ: Berlin Mobile: +491777841069 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bringing mysql server back up?
I was doing some testing with our development mysql server (4.1.x) and one of the databases died and wouldn't let me do anything not even read the tables in the database. So I thought I would bring it down and start it back up. I proceeded to use ./mysqladmin shutdown command which did what I wanted it to do and shutdown the server. Now I can't seem to figure out how to bring it back up. What is the command line I need to use? I've tried ./mysqld -start and I get the following error message. $:/usr/local/mysql/bin $ ./mysqld -start 060106 14:58:53 [Warning] Can't create test file /usr/local/mysql-standard-4.1.12-apple-darwin7.9.0-powerpc/data/wfmudev1.lower-test ./mysqld: Can't change dir to '/usr/local/mysql-standard-4.1.12-apple-darwin7.9.0-powerpc/data/' (Errcode: 13) 060106 14:58:53 [ERROR] Aborting 060106 14:58:53 [Note] ./mysqld: Shutdown complete Thanks!
Help with a SELECT query
Below is a query I'm trying to create and the sql for the table I'm pulling the information out of. The query is definitely not the best query out there especially since I'm still pretty new with sql. I know there has to be a better way of getting the information I want. A little background. I'm parsing an Apache access_log file and throwing it all into a DB so I can run some reports on it. What I'm wanting to get are certain file types that were downloaded (in this case all .html, .php, .pdf, .doc, and .flv files) in a certain date range but grouped by certain ip addresses. Some groups of ips might have 2 or 3 subnets it needs to get (ie xxx.xxx.% and yyy.yyy.%). What needs to be returned is the count of all the file types that have been downloaded but grouped by certain ips that I'm looking for. I hope I didn't confuse anything because I think I confused myself! ;) Thanks for any help! CREATE TABLE `apache_statslog` ( `STATS_ID` int(11) NOT NULL auto_increment, `ip` varchar(25) default NULL, `accesstime` datetime default NULL, `thepage` varchar(250) default NULL, `thetype` varchar(25) default NULL, `thecode` char(3) default NULL, `thebytes` int(11) default NULL, `theref` varchar(250) default NULL, `browser` varchar(250) default NULL, PRIMARY KEY (`STATS_ID`), KEY `ip` (`ip`), KEY `accesstime` (`accesstime`), KEY `thepage` (`thepage`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=59145 ; SELECT count(swrm.ip) as swrm_page_hits, count(mw.ip) as mw_page_hits, count(fl.ip) as fl_page_hits, count(so.ip) as so_page_hits FROM apache_statslog as swrm, apache_statslog as mw, apache_statslog as fl, apache_statslog as so WHERE (swrm.accesstime = '2006-01-01 00:00:00' AND swrm.accesstime = '2006-01-04 23:59:59') AND (swrm.ip LIKE 'xxx.xxx.%' OR swrm.ip LIKE 'xxx.xxx.%' OR swrm.ip LIKE 'xxx.xxx.%') #this is a group that needs to return a count AND (mw.ip LIKE 'xxx.xxx.%' OR mw.ip LIKE 'xxx.xxx.%') #this is a group that needs to return a count AND (fl.ip LIKE 'xxx.xxx.%' OR fl.ip LIKE 'xxx.xxx.%') #this is a group that needs to return a count AND (so.ip LIKE 'xxx.xxx.%' OR so.ip LIKE 'xxx.xxx.%') #this is a group that needs to return a count AND (swrm.thepage LIKE '%.html%' OR swrm.thepage LIKE '%.php%' OR swrm.thepage LIKE '%.doc%' OR swrm.thepage LIKE '%.pdf%' OR swrm.thepage LIKE '%.flv%') ORDER BY swrm.accesstime ASC
RE: Calendar table workaround
I have no idea what you are asking, which may explain why no one has replied to your question yet. I've been working with relational databases for 20 years and I've never heard the term calendar table. What are you trying to accomplish? If you describe clearly what you are trying to do, perhaps someone can help you devise a way to do it in MySQL. Rhino A table of dates to which to join other tables, ensuring reports that reflect days for which no data is available. I forget the query but I know it can be done. But can't you just have a table (called calendar?) with each entry having it's own row with a date column that gives whatever date you need in it and then other columns for any other details you need to have to go along with the date. After you have a table full of dates you can just do a query that will grab all the information and display it like a calendar and show you days that have information and days that don't have anything. I'll see if I can't find that query for you and explain it to you. jay
LIMIT on GROUP BY?
My query below returns however many rows fit the WHERE condition, in this case when they year, period, week is = 2009131. In my case it is returning 11 rows because I have 11 rows where the year,period, week is 2006XXX. However, this is not what I want. I only need 4 rows returned to me and not all 11 no matter what the = XXX part of the where is. I guess my question becomes is it possible to put a LIMIT of how many rows are returned on a GROUP BY? Thanks! SELECT ROUND(AVG(page_hit)) as page_hit, ROUND(AVG(training_tracking)) as training_tracking, ROUND(AVG(certificates)) as certificates, ROUND(AVG(team_members)) as team_members, ROUND(AVG(evaluation)) as evaluation, CONCAT(year,period,week) as date FROM statistics WHERE CONCAT(year,period,week) = 2009131 AND region_id != 'AA' GROUP BY date ORDER BY date DESC LIMIT 0,77
Changing types on the fly in select queries?
I have a strange question for you all. I've inherated some code and the way the code works is that I can only mess with the WHERE part of a query. Therefore, I was wondering if something like this would be possible. WHERE where concat(year,period,week) as type int 2007031 Note that I'm trying to change the type of what the concat() is doing. Is this even possible? If so is it possible to do it in the WHERE? The reason why I think I need to do this is that 'period' is a char(2). I have to have the leading zero for every entry into the database so I can run my less than compare to it. Is there a better way of doing this than having the 'period' a char(2) type and trying to make whole concat() a type of int() on the fly? Thanks!
RE: Changing types on the fly in select queries?
You can cast data tyes explicitly: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html Now that is cool. Too bad we are using MySQL 3.23.x on our production box. I can't use it. :( If this table is going to get large then you might find it too slow to use that method. Your query is logically the same as this (assuming integer columns): WHERE (year 2007) OR (year = 2007 AND period 3) OR (year = 2007 AND period = 3 AND week 1) ; I'm not quite sure if this will work. For the following reasons. - period is of type char(2) so I can have the leading zero for the concat(). Is it possible to compare a char() and an int() value? example '4' 2? - The values in the database for period are 01 through 13. The values of week are 1 through 4. The values of year is obvious. With the way the values are set I'm not sure if this WHERE you have will work on not. Guess I'll have to do some testing. Can't remember off the top of my head if that would be able to use an index on (year,period,week) though. Anyone? That would be intersting to see if that did work. Thanks for the help!
RE: Changing types on the fly in select queries?
You might be able to use the CAST() function. http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html Unfortunately our producation machine is using MySQL 3.23.x and from what I've found the cast() function isn't available. :( CAST() and CONVERT() are available as of MySQL 4.0.2.
