My.cnf and my.ini
I¹m running MAMP (www.mamp.info) on my Mac OSX box as a local way of developing. I¹m running into a slight problem that the mamp site doesn¹t talk about. The 1.5 beta1 seems to have the option skip-innodb enabled and I can¹t seem to find out where this is located to disable it and enable the use of innodb tables. There is no my.cnf or my.ini file and the command to start MySQL is: /Applications/MAMP/Library/bin/mysqld_safe --port=8889 --socket=/Applications/MAMP/tmp/mysql/mysql.sock --lower_case_table_names=0 --pid-file=/Applications/MAMP/tmp/mysql/mysql.pid --log-error=/Applications/MAMP/logs/mysql_error_log I was wondering what I can do to enable innodb? The only thing I can think of is that the people who make MAMP have disabled innodb when they compiled MySQL in which case am I out of luck? Thanks.
Alter table - adding constraints?
I really don¹t know what to do because I keep getting this error. Any ideas? SQL query: ALTER TABLE pl_reports ADD CONSTRAINT fk_region FOREIGN KEY ( region ) REFERENCES Region( id ) ON UPDATE CASCADE ON DELETE CASCADE MySQL said: Documentation #1005 - Can't create table './survey_localhost/#sql-113_f8.frm' (errno: 150) Thanks!
Re: Alter table - adding constraints?
1) both tables are InnoDB. 2) both tables have data in them. 3) both table are the exact same data types. On 2/20/07 3:51 PM, Chris White [EMAIL PROTECTED] wrote: Jay Paulson wrote: I really don¹t know what to do because I keep getting this error. Any ideas? SQL query: ALTER TABLE pl_reports ADD CONSTRAINT fk_region FOREIGN KEY ( region ) REFERENCES Region( id ) ON UPDATE CASCADE ON DELETE CASCADE MySQL said: Documentation #1005 - Can't create table './survey_localhost/#sql-113_f8.frm' (errno: 150) Thanks! 1) Are both tables InnoDB? 2) Do you have data in either tables? 3) Are the data types of both columns exactly the same ( int(20) unsigned and int(20) will fail, the unsigned has to be there)? you can also run SHOW INNODB STATUS as root to find out exactly what the error is. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Running a Staging and Development DB on the same server?
I'm setting up our one server for staging and development. I am need to set up MySQL to have 2 copies of the same database on the same server. Could anyone direct me to some documentation or tell me how to do this? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Restarting MySQL on Solaris 8?
A couple questions since I'm not a Solaris person I really don't know how to do the following and was hoping that someone could help me out (Google isn't much help on this). How does one start the MySQL daemon on Solaris 8? (it's running MySQL 3.23.49) How does one tell Solaris 8 to start the MySQL daemon on boot? Lastly, tried running /usr/local/bin/safe_mysqld but got the following error: TIMESTAMP mysqld ended Thanks for any help! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Moving database files to larger partition?
I'm currently setting up MySQL 4.1.2 on RHEL 4.1 and I have found the /etc/my.cnf file. I'm trying to find out if I change the basedir from: Basedir=/var/lib To Basedir=/other/path Will that move the actual data files that have all my databases? Do I need to move any of the things that are in /var/lib over to the new path or just the data files of my databases? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Run Apache/PHP/MySQL from CD?
I have no idea if this is possible or not but is there a way to run Apache, PHP, and MySQL from a CD? I'd like it to be possible to run it on Windows, Mac OSX and *nix. If it is possible could someone point me in the right direction? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing x.x.x.x strings
Take a look at the MySQL function INET_ATON and it's counterpart (I don't
remember what it's counterpart is but it should be easy to find once you
find INET_ATON).
I know in PHP there is a function that will convert x.x.x.x to an int and
then you just put that int into a field in your database and then use the
INET_ATON function in MySQL to select the ips you want.
SELECT INET_ATON('3.2.0.13') INET_ATON('3.2.0.2')
That should return TRUE. :)
Hope that helps.
On 3/24/06 2:44 PM, Lucas Vendramin [EMAIL PROTECTED] wrote:
Hi all.
I have a problem:
When I compare two strings in mask (x.x.x.x) the comparation is not true (for
my question :D)... look:
select '3.2.0.13' '3.2.0.2'- FALSE
I want this command return TRUE, but it is returning FALSE.
But, if I put a space first into the smaller string the camparation will
returns what I want:
select '3.2.0.13' ' 3.2.0.2'- TRUE
How can I check it? There is a way to correct my problem? The string is like a
IP-ADDRESS where 192.168.0.13 is better than 192.168.0.2, but it is not a
IPADDRESS column, it is a Version of file (version 3.2.0, version 3.2.1,
etc...)
Thanks for all.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fastest way to log IP's
I'm logging IP addresses and also searching for existing ones. Does anyone happen to know which is more resource intensive: storing the IP's as integers using INET_NTOA() and INET_ATON() to convert them at each query; or simply storing the IP as a varchar? In each case the IP field would be the primary key. I'm actually doing this but with Apache access log files. I tested both methods and found that the INET_NTOA() and INET_ATON() were fairly quick and didn't have much of a performance hit. I also tested the queries for pulling the ips back out. Using the ints vs the ips. Both were indexed, which made the insert a little slower but drastically improved the search. The ints were a bit faster on the search than the varchar ips, but in my case there wasn't a huge difference and I would rather not have to do the extra processing and extra space for storing the ips as ints and index them as I only run reports off the access log once a week. One thing I might suggest is to find how many rows you really need to run your reports (or dates etc whatever it may be). The smaller your row size the faster your searches will be and the fewer indexes you have the faster your inserts will be. You could just archive what you don't need into another table/database and mess with the indexes and run your reports. Hope that helped. :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with query
From the result set below I have 22 rows and the only difference is the date. I was wondering if there was a way to get all of these results using GROUP BY instead of having to use LIMIT?? As this table grows I'm going to want to get a LIMIT 0,77 but would like it to be grouped by date. So basically I want 7 groups of results and in each group there should be 11 rows. Any idea how to do this? Should it be separate queries or should I just break down and use LIMIT? thanks My attempt was, but doesn't really work. SELECT region_id, date, page_hit, score FROM statistics WHERE date = '2006-01-29' GROUP BY region_id, date ORDER BY date DESC; +---++--+---+ | region_id | date | page_hit | score | +---++--+---+ | CE| 2006-01-23 | 978 | 6.72 | | FL| 2006-01-23 | 558 | 2.75 | | MA| 2006-01-23 | 312 | 0.09 | | MW| 2006-01-23 | 478 | 0.25 | | NA| 2006-01-23 | 4846 | 4.85 | | NC| 2006-01-23 | 3281 | 3.03 | | PN| 2006-01-23 | 3281 | 1.22 | | SW| 2006-01-23 | 1964 | 1.05 | | RM| 2006-01-23 | 1964 | 2.80 | | SO| 2006-01-23 | 173 | 0.11 | | SP| 2006-01-23 | 163 | 0.07 | | CE| 2006-01-29 | 978 | 6.72 | | FL| 2006-01-29 | 558 | 2.75 | | MA| 2006-01-29 | 312 | 0.09 | | MW| 2006-01-29 | 478 | 0.25 | | NA| 2006-01-29 | 4846 | 4.85 | | NC| 2006-01-29 | 3281 | 3.03 | | PN| 2006-01-29 | 3281 | 1.22 | | SW| 2006-01-29 | 1964 | 1.05 | | RM| 2006-01-29 | 1964 | 2.80 | | SO| 2006-01-29 | 173 | 0.11 | | SP| 2006-01-29 | 163 | 0.07 | +---++--+---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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.
RE: Find username password on tables
Or if you have any applications that are accessing this database you could look at the config files for it and most likely the password will be there. jay Username you can get it from the user table in Mysql. But I don't think atleast after MySQL 4.1 there is a way to retrieve paswords in MySQL as it uses its own encryption algo to encrypt passwords. You have to reset(make that passwd field to null in the mysql.user table) and add a new password(use grant) to use that. sujay -Original Message- From: Scott Purcell [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 05, 2005 6:39 PM To: mysql@lists.mysql.com Subject: Find username password on tables Hello, A while back, I created a database, and performed the following: GRANT ALL ON util_db.* to XXX identified by XXX; Problem is, a year later, I need to find the username and password, so I can write to these tables. Can this be accomplished, I am the root user. Thanks, Scott -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie needs info!
Hi everyone, Long time reader, first time poster. I've been using MySQL for some years now and never really did any advance stuff in it. I mainly do updates, deletes, and selects. My selects aren't even that complex as I don't have a very good understanding of the join syntax and exactly what it does. I was wondering a few things. First of all what is a good book for beginners/intermediate level people? I'd really like to learn more complex query structure and how to optimize a database as a whole as well as individual tables in the database (example what's the difference between a primary key and just a regular key in a table?). I'm also looking for something that can explain stored procedures and why they are useful (I really don't have any clue about stored procedures) as well as transactions, replication, and master/slave databases. Finally, I'd like to figure out the difference between each table type (MyISM and InnoDB etc). Thanks for any help or advice! jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Locked myself out of the mysql database!
Like an idiot I locked myself out of the mysql database when I went to change the password for the root user. Is there any way I can get back into that database and restore my mistake? Thanks, jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Random Selects
Hello- I'm trying to get random information out of my table and the query I'm using keeps returning the same row every time. In the table I have 3 rows and I want to choose at random in the sql which row to return. Here's an example query I have. SELECT * FROM banner ORDER BY rand() asc limit 0,1 This returns the first row in the table every time. What's strange is that I have a query for a table that has about 500 rows in it and it works fine with the exact same syntex (except the the table is different). Is there anything I'm doing wrong? Thanks!
easy query question
I want to do a query that will find all the titles I have in my db that start with numbers and ambigious characters (i.e. 0-9, , #, $ etc...). My query is below, however I don't know what to change the a too in order for it to return what I just described. select id, shortdescription, title from reviews where title LIKE UPPER(\a%\) ORDER BY title ASC Thanks for any help. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How do you know?
How do you know what the maximum connections you can have at one time with mysql? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How do you know?
Next question: How do you increase that variable? Also, is there a number that is generally considered too high or does that depend on the server you are running MySQL on? (an example would be GREAT!) thanks. - Original Message - From: Roger Baklund [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Jay Paulson [EMAIL PROTECTED] Sent: Thursday, January 24, 2002 10:31 AM Subject: RE: How do you know? * Jay Paulson How do you know what the maximum connections you can have at one time with mysql? mysql show variables like max_connections; -- Roger query, table - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Optimizing and more connections
I'm trying to increase the maxium number of connections to my MySQL database but I am not sure how to do this. I've gone to the manual and it doesn't say too much (maybe i'm looking in the wrong spot?). The machine I'm running is an AMD 650 with 512 Ram on RedHat 7.1 and MySQL 3.23.41 so I found the following line in the manual: safe_mysqld -O key_buffer=64M -O table_cache=256 \ -O sort_buffer=4M -O record_buffer=1M However, it seems that I need to shut down the mysql db before I can run this. This still doesn't solve the problem of the max number of connections. I don't have a my.cnf file to change on the server so the only thing I can think of is to tack on -O max_connections=500 at the end of the line above. Is this correct? What do I need to do to maximize the preformance on my machine? Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Changing root password
I'm trying to change the root password for mysql. This is the first time I've tried to change the password and I can't get it to work. Below is the error message I get: [root@localhost bin]# ./mysqladmin -u root -p password 'new-password' Enter password: ./mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: YES)' I've looked at the manual and it doesn't say much that I've already done. I've also looked up the --skip-grant-tables but the only mysqld file I have to run is in the /etc/rc.d/init.d/ directory and it doesn't take the --skip-grant-tables flag. So I'm at a lost at what to do next. thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Command?
What is the command in MySQL to grab the last item that was inserted into the database? thanks... jay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
