RE: Changing the Prompt for timing purposes
On Wednesday, April 13, 2005 11:29, [EMAIL PROTECTED] wrote: Hi, The MYSQL command line interface is very basic. Can it be modified like the shell command prompts so that I can include date/time for timing benchmarks? Secondly, is there an echo command in MYSQL command prompt so that I can see the command I issued or a log file that I can write to.sorry about the basic questions but I'm a newbe. George Read the following for prompt modification: http://dev.mysql.com/doc/mysql/en/mysql-commands.html -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unix time as year in select query -nooby
On Wednesday, April 13, 2005 20:17, [EMAIL PROTECTED] wrote:
MySql vers 4.0.20
A table noticeboard has three test entries.
A unix time from date(U) is stored in the field published of
type, bigint20.
I am not able to workout how to select the year. Assuming that the
value from date(U) can be treated as a unixtime value, my latest
unsuccessful effort is
$yearslist = mysql_query(SELECT published,
FROM_UNIXTIME(UNIX_TIMESTAMP(published),'%Y') AS year FROM
noticeboard) or die(Cannot get list of years.br .
mysql_error());
$counttotal = mysql_num_rows($yearslist); echo divRows found
$counttotal/div;
while ($myrow = mysql_fetch_array($yearslist)) {
if (ISSET($myrow['published'])){$published=$myrow['published'];}
$yearpub=date(Y,$published);
if (ISSET($myrow['year'])){
$year=$myrow['year'];}
else {echo divYear is not set/div;}
echo divYear $year but value was $published. Year value should be
$yearpub/div;
}
The result is
Rows found 3
Year 1969 but value was 1083923875. Year value should be 2004 Year
1969 but value was 1113300220. Year value should be 2005 Year 1969
but value was 1113351870. Year value should be 2005
Louise
My guess is that you are passing what is already a unix timestamp to
the function unix_timestamp, and since that is an invalid datetime it
returns zero which then causes from_unixtime to return 1969 (when epoch
time started in your time zone).
Try using FROM_UNIXTIME(published,'%Y')
--
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CSV storage engine
On Wednesday, April 06, 2005 02:30, Martijn Tonies wrote: Sorry, I don't use windows 8-) except when I have to. All my servers are unix/linux and I can build them at will. I think there are instructions in the manual for building it but I think you would probably have to purchase a C++ compiler or similar. Exactly the reason why I do NOT want to build it :-) I know MySQL used to be build by everyone ... but IMO, this doesn't make sense no more ... I don't want to build my own binary for a database engine, no matter how detailed the instructions are... I hope for a binary with CSV enabled. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com Perhaps you can convince mysql ab to build a windows package similar to the linux max package which includes support for the csv engine along with many other things. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Changed Number
On Tuesday, April 05, 2005 20:31, [EMAIL PROTECTED] wrote: I have loaded a large *.csv spreadsheet into mysql and one number, the grand total, changes from 16996941 on the Excel spreadsheet to 8388607 in the mysql database. The numbers surrounding this number are correct at all stages. I have reloaded, checked formatting and done various other things without success. Then I went to the text (*.sql) file in which I had dumped the mysql table, manually changed the number to 16996941 and put the text file on the server. However, the number that showed up on the web page table and the number in the database on the server is 8388607. Note that the smaller number is just under half of the larger number. This is exactly the maximum value of a signed mediumint. Might I suggest a column type of int or bigint. In a numeric field, anything over the maximum will be converted to the maximum, and anything under the minimun will be converted to the minimun. This makes no sense. Any solutions? Ken -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Q] Database design
On Saturday, April 02, 2005 12:48, Eric Gorr wrote: Peter Brawley wrote: Eric, If I understand you correctly, you propose to encode user and group info as table names. That's a mistake. To use an RDBMS like MySQL effectively, you want to encode your information as rows of data in tables whose names and structures do not vary. Thank you for your comments. Would this design be better? ( assume that one of the group names will be 'Group_A' ) Database Table_Groups group name # of user columns Table_Group_A_users username Column 1 Data Column 2 Data ... Column N Data I am still encoding group info into a table name, but I am unsure of how to avoid this and not have a table with a lot of wasted space. May I suggest you read some of the tutorials listed at http://www.artfulsoftware.com/dbresources.html, and/or read http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch01.pdf. Thank you for the pointers. Unfortunately, http://www.artfulsoftware.com/dbresources.html seems to have a couple of broken links (Codd's Rules)...any idea where this information can be found? user table: id (pk) name any other user info only dependant on the user group table: id (pk) name any other group info only dependant on the group usergroup table: user_id (pk) group_id (pk) any info specific to individual user/group combo pk = primary key This third table is called a linking table. It allows you to deal with a many-to-many relationship. This setup allows a group to have multiple users, and users can belong to multiple groups. You will need to look into joins to see how to query these tables effectively. For example to find out what users are in group A. SELECT u.name FROM user as u INNER JOIN usergroup as ug ON (u.id = ug.user_id) INNER JOIN group as g ON (ug.group_id = g.id) WHERE g.name = 'A' -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Hex data in VARBINARY fields -- Is it me, or MySQL?
On Friday, April 01, 2005 17:57, Adam Wilson wrote: OK so... I'm having this problem where I'm trying to store (rather small (36-byte)) hex values in MySQL, but some of them end up getting truncated, therefore breaking my app... I'm using 4.1.10, with --default-table-type=InnoDB... Or what ever option that is anyway... point is, all of these tables are InnoDB... Here's the problem.. -- mysql CREATE TABLE `table` (`field` VARBINARY(36) DEFAULT NULL); Query OK, 0 rows affected (0.01 sec) mysql INSERT INTO `table` SET field = 0xdee96318a69c8ba3208e1b2e91233725f5bb99a4708df7ad367c7285041bbb17652a0f20; Query OK, 1 row affected (0.00 sec) mysql SELECT HEX(`field`) FROM `table` WHERE `field` = 0xdee96318a69c8ba3208e1b2e91233725f5bb99a4708df7ad367c7285041bbb17652a0f20; Empty set (0.00 sec) mysql SELECT HEX(`field`) FROM `table`; ++ |HEX(`field`) ++ | DEE96318A69C8BA3208E1B2E91233725F5BB99A4708DF7AD367C7285041BBB17652A0F ++ 1 row in set (0.00 sec) ...[snip]... I'd appreciate any Ideas anyone has any ideas/suggestions... I'd hate to unnecessarily submit a bug report, if it's something on my end. THANKS!!! --Adam You need to you a blob column type instead. Varbinary strips trailing spaces (0x20). Refer to the folowing page for further explanation. http://dev.mysql.com/doc/mysql/en/blob.html Quote from page: There is no trailing-space removal for BLOB and TEXT columns when values are stored or retrieved. Before MySQL 5.0.3, this differs from VARBINARY and VARCHAR, for which trailing spaces are removed when values are stored. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UNION ALL and GROUP BY
On Friday, April 01, 2005 19:27, Chris wrote: Hi all, I've got 3 or 4 queries UNIONed together in a single query. I want to GROUP the UNIONed result, by one field, and SUM() another field. Is that possible in one query, or will I need to use temporary table and group it from that? Thanks, Chris I don't think you can tacka group by directly on the end of a union. Try something like this: select groupcol, sum(sumcol) from (select col1 as groupcol, col2 as sumcol from table1 UNION select col1 as groupcol, col2 as sumcol from table2) as tmptable group by groupcol; Obviously you can add in your where clause. This is messy but it should work. This is basically using a temp table without 2-stepping it. If you look at the explain it will say using temporary. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to group records by using regular expression?
On Saturday, April 02, 2005 01:51, ON.KG wrote: Hi Everybody I need to group records by regular expression Example: Table has column - names (just example) records are: idnames time 1 John, Max 12:15 2 Max, Jeff 15:55 3 Ken 20:45 I need to get grouped records by name Max in column names SELECT COUNT(*) FROM my_table GROUP BY reg_expression Are you just looking for a count of names that contain max? SELECT COUNT(*) FROM my_table WHERE names REGEXP 'max'; or SELECT COUNT(*) FROM my_table WHERE names LIKE '%max%'; If you are really trying to group the names you can do this with a regex but this will only give you at most three rows (two if the column doesn't allow nulls). This is because the regex can only evaluate to true, false, or null. If you want just the ones that contain Max grouped together and everything else separate you could rig it with an if. SELECT names, COUNT(*) FROM my_table GROUP BY if(names REGEXP 'max', -1, names) The -1 would need to be replaced by a value that will not exist in the table. This is why I said it is rigged. Maybe if you could explain why you are trying to do this, someone could help you come up with a better solution. Could somebody help me? Thanx -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Central UDF project at mysql.com?
I also think this would be a good idea. I have written some UDF's for specific internal use and often wondered if they would be useful to others. It would be nice if there was a centralized place to search for UDFs, so you don't have to re-invent the wheel everytime you need a function. I think that the community list may be the appropriate list for disscussion on how to get this going. I believe it is run by Arjen Lentz. I have CC'd him on this message. Maybe he can give some input. Regards, -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa On Wednesday, March 30, 2005 09:12, Dan Bolser wrote: On Wed, 30 Mar 2005, Mark Papadakis wrote: That is a really good idea - though I am not sure there is much of those out there to justify the cause, nor many developers actually using UDFs. Cheers :) I think with the right infra more people would start using and developing UDF's, especially if the work was a part of 'MySQL' proper and not just different groups of individuals. I know it wouldn't make any *real* difference, but it would make a psychological difference. Also a central project would be a way to boost visibility for different UDF projects, for example good (ongoing) work could be showcased in the MySQL news letter. Still, having them all in one place could be nothing but a good thing. Yeah, I totally agree :) Especially if resources like the MySQL bugtracker and mailing lists could be shared. Is this the right forum for requesting such things? All the best, Dan. MarkP On Wed, 30 Mar 2005 14:39:11 +0100 (BST), Dan Bolser [EMAIL PROTECTED] wrote: Hi, I searched for previous discussion on this topic, but didn't find any. I would like to see a centralized MySQL hosted UDF archive and development project. The only existing 'archives' seem to be somewhat poorly maintained (sorry), and suffer for their duplicated efforts and being loosely distributed throughout the web. The best I can find are here (ranked according to Google)... http://empyrean.lib.ndsu.nodak.edu/~nem/mysql/udf/ http://www.oreillynet.com/pub/wlg/2292 http://www.thecodeproject.com/Purgatory/mygroupconcat.asp http://mysql-udf.sourceforge.net/ http://www.linuxjournal.com/article/6841 I think a centralized project would do wonders for the UDF community, allowing UDF's to be discussed, suggested and developed under one roof. A first step should be to create a [EMAIL PROTECTED] mailing list. Without such a central list the UDF community can't communicate effectively. Who better than MySQL to organize the MySQL UDF community? A simple not officially supported statement is all that is needed. Good UDF's could become part of MySQL proper, and a UDF 'bundle' would be a great development. MySQL programmers could help build UDF's, and the community could vote on 'wanted' functions. You could probably guess where all this is going, and that is towards my own UDF request (where to ask?), but I will leave that for later. Any comments? Any postings that I have missed? Any reason that their is no udf mailing list? I think that their are tons of UDF's waiting to happen, given the right conditions. All the best, Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: if statement help
On Wednesday, March 30, 2005 09:49, Christopher Vaughan wrote: I have data in a table listed as 44:22:22 333:33:33 It stands for hhh:mm:ss If you convert it to a time field you can use mysql built-in functions to do what you want. You are limited to the range -838:59:59 to 838:59:59 though. http://dev.mysql.com/doc/mysql/en/time.html http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html I want to break each part of the data into different parts based on the ':' to separate them. Then I want to take that data and sum it. I wrote an if statement to parse through this table but I can't get it to work. I am not sure If my syntax is wrong because I can't find anything to check against it. Here is the syntax: IF (SELECT job_walltime FROM time WHERE CHAR_LENGTH( job_walltime ) =9) THEN (SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( `job_walltime` , 4, 2 )) , sum( right( job_walltime, '2' ) ) seconds FROM `time`) ELSE (SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( `job_walltime` , 3, 2 ) ), sum( right( job_walltime, '2' ) ) seconds FROM `time`) END I know this isn't the only way to do this but this but this is the first suggestion that comes to mind. Any input would be great. Further info MySQL 3.23.58 running on RedHat 9 -Chris Vaughan -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: if statement help
On Wednesday, March 30, 2005 10:24, Christopher Vaughan wrote: Tom Crimmins on Wednesday, March 30, 2005 at 11:10 AM -0500 wrote: If you convert it to a time field you can use mysql built-in functions to do what you want. You are limited to the range -838:59:59 to 838:59:59 though. http://dev.mysql.com/doc/mysql/en/time.html http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html I'm not sure that this is going to work. Since the length of the data ranges from 8-9 characters in length and I also need to sum all the times on hour, minute and second. I looked at the addtime function but for the version of MySQL we have installed it does not work and upgrading it would be an unwanted hassle. -Chris Vaughan www.clusters.umaine.edu Look at the functions HOUR(time), MINUTE(time), SECOND(time). These will give you interger output for each part of the time field. Also TIME_TO_SEC(time) may be useful for you. I believe that all of these are supported in 3.23. If your times aren't greater than 838:59:59 this should work for you. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: if statement help
Just forwarding this to the list. On Wednesday, March 30, 2005 10:43, Christopher Vaughan wrote: Tom Crimmins on Wednesday, March 30, 2005 at 11:31 AM -0500 wrote: Look at the functions HOUR(time), MINUTE(time), SECOND(time). These will give you interger output for each part of the time field. Also TIME_TO_SEC(time) may be useful for you. I believe that all of these are supported in 3.23. If your times aren't greater than 838:59:59 this should work for you. Thanks for the help: SELECT sum( HOUR ( job_walltime ) ) Hours, sum( MINUTE ( job_walltime ) ) Minutes, sum( SECOND ( job_walltime ) ) Seconds FROM `Jobs` This cold medicine that I'm on has slowed me down a bit. -Chris Vaughan www.clusters.umaine.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What's up with this syntax?
On Wednesday, March 30, 2005 22:25, Daniel Kasak wrote: update _cached_LinesNotTolling LNT inner join TelecomLinePosting TLP on LNT.Line=TLP.Line inner join TelecomAccountPosting TAP on TLP.TelecomLinePostingID=TAP.DanPK inner join PhoneTypes on TLP.LineType=PhoneTypes.ID set AnnualService=sum(TLP.Service)/1*12, LNT.PhoneType=SitRepDesc, MaxOfInvDate=InvDate where TAP.DanPK=41675 group by TLP.Line It's giving me: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by TLP.Line' Looks right to me... Remove the group by. A group by is used to group rows returned by a select statement. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql not starting at boot
On Wednesday, March 30, 2005 23:26, Mark Sargent wrote: Hi All, below is my /etc/init.d/mysql content, but, mysql is not starting at boot on Fedora3. Have I missed something fundamental.? I need mysql to start at boot for snort to connect to it. Currently, snort gives an error stating it can't connect. Cheers. Mark Sargent. I assume you are able to start it after boot using 'service mysql start'. Run: chkconfig --list mysql This should show a list of runlevels with on and off. If not run: chkconfig --add mysql -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: upgrading mysql on RH fedora core 3
On Tuesday, March 29, 2005 21:29, bruce wrote: hi... we're trying to install mysql/mysql-server (4.1.10a-1.i386) and are running into some serious problems. we had mysql/server (3.23.52-3.i386) running, but needed to go to the higher version... can someone tell us how/what we need to do to get this working correctly. actually, if anybody's managed to do this, can you tell us exactly what rpm packages you used? as you know, dealing with the rpms gets into dependency hell, which we believe has a lot to do with our issues... if you managed to get this version of mysql running on FC3, and you built it from source, can you provide directions/pointers on what you did, where you placed the resulting libs/etc... also, this has to be running with apache/php/perl/etc... thanks bruce I use mysql primarily on RHEL3, but I just upgraded a FC2 box just to try it. I was able to upgrade it using the rpm's from dev.mysql.com. I did notice one thing. The rpm install didn't seem to kill the old mysqld properly, so I got an access denied right after the install. I ran the following as root: #killall mysqld #service mysql start After that I was then able to connect just fine. As far as perl, php, and apache, everything will continue to work without modification if you continue to use the old password hashes. You can force this. Refer to http://dev.mysql.com/doc/mysql/en/old-client.html, specifically the old-password option. To make perl work with the new passwords, all you need to do is build DBD::mysql from source. To do this, uninstall the dbd-mysql rpm if you have it installed. I can't remember the exact name because I don't use it. Then as root run: #perl -e shell -MCPAN cpaninstall DBD::mysql Regards, -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL account permissions
On Monday, March 28, 2005 15:07, Philippe Reynolds wrote: Hi, I've just create an account and given it all privileges for database_name.*. However when I try to 'load data infile' it tell's me that the account doesn't permit it. When I use the 'root' account everything is fine. Can you guys help? Cheers Phil The user needs the FILE priv. This is a global priv. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is there a way to use LIMIT in both UNION ALL statement and t hen ORDER?
On Monday, March 28, 2005 21:07, Homam S.A. wrote: MySQL seems to let me use the LIMIT clause in both parts of a UNION ALL query, but as soon as I add an ORDER BY CLAUSE, it gives me a syntax error. For example, this query executes fine: SELECT * FROM A WHERE X = 1 LIMIT 1000 UNION ALL SELECT * FROM B WHERE Y = 1 LIMIT 1000 But this returns an error: SELECT X, Y FROM A WHERE W = 1 LIMIT 1000 UNION ALL SELECT X, Y FROM B WHERE W = 1 LIMIT 1000 ORDER BY X (SELECT X, Y FROM A WHERE W = 1 LIMIT 1000) UNION ALL (SELECT X, Y FROM B WHERE W = 1 LIMIT 1000) ORDER BY X Without the parens, this looks like an order by on just the second query, and since this is after the LIMIT clause that is invalid. It should work fine with the parens. Any way to let sort the result other than a temp table? Thanks, Homam -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 'Can't connect to local MySQL server....' error
On Monday, March 28, 2005 21:36, bruce wrote: hi... a server went from RH8 to FC2. it appears that the guy who did the upgrade didn't perfrom any backups... i get a 'Can't connect to local MySQL server through socket...' error. i've tried to 'fix' the tables 'mysql_fix_privilege_tables' with no luck... i've tried to start/restart with no luck. i've lloked through google/mysql with no luck... any ideas as to what might be causing the problems... if i can get the daemon started, i'll (hopefully) be ok... Is there anything in the error log? You could try starting it from the command line to see what errors you get. The following will work assuming you installed using the rpm's. Otherwise the location of mysqld and the user may differ. #su - mysql #/usr/sbin/mysqld Run this and see what errors are reported. thanks bruce [EMAIL PROTECTED] -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: show duplicate entries
On Sunday, March 27, 2005 19:48, Louie Miranda wrote: mysql select count(*) - count(distinct username,email) as 'duplicate names' from users; +-+ duplicate names | +-+ 2 | +-+ 1 row in set (0.00 sec) I got this select syntax to count how many duplicate entries i have. But i dont know how to show the duplicate entries i have on that table. SELECT username, email, COUNT(*) as n FROM users GROUP BY username, email HAVING n 1 Pls help! -- Louie Miranda http://www.axishift.com -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: changing default date format on server
On Thursday, March 24, 2005 10:06, J.R. Bullington wrote: You can change it on the command line by mysql set date_format = '%m-%d-%Y'; However, this may be a client-only view, as I am still trying to get the global variable to change. [shell] #mysqld -v --help | grep date_format --date_format=name The DATE format (For future). date_format (No default value) [/shell] This has been said before, but date_format is not implemented on the server. You can set it, but I don't think it does anything. J.R. PS - Sorry it took me so long Mark, was busy and AFK -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, March 24, 2005 10:57 AM To: Mark Mchugh Cc: mysql list Subject: Re: changing default date format on server Mark Mchugh [EMAIL PROTECTED] wrote on 03/24/2005 10:26:44 AM: hi, How can i change the default date field to european format, i.e. dd/mm/ ? thanks MArk However, your _client_ may have many ways to format date data for viewing. How you change _what_you_see_ depends entirely on which tool you are using to get data from the server and present it for viewing or other operations. Refer to the documentation for the client you are using for details on how to get it to show you dates in the format you want to see. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie: Searching for empty fields
On Wednesday, March 23, 2005 12:13, Graham Anderson wrote: how do you search for an empty field ? I want to filter out records where the ipAddress field is empty SELECT * FROM 'userLog' WHERE 'ipAddress' IS [empty] ? Searching for NULL's -- SELECT * FROM userLog WHERE ipAddress IS NULL Searching for empty string -- SELECT * FROM userLog WHERE ipAddress = '' Or either -- SELECT * FROM userLog WHERE ipAddress IS NULL OR ipAddress = '' There is no space between the single quotes. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: again on encryption function, with bug 7846 question
On Tuesday, March 22, 2005 07:15, symbulos partners wrote: Dear friends, thanks for the link to the dev manual page on encryption function. Which encryption is more secure (strongest) AES DES SHA ? Are you using this for password storage or encrypting actual data? If you are using it for passwords, I would suggest using SHA1 or MD5 since these are one-way hashing algorithims. I would have said AES, but after reading the manual chapters on DES, SHA I am not s(ec)ure any more. By the way, I did not understand if the bug http://bugs.mysql.com/bug.php?id=7846 has been fixed. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: functions md5, crypt
On Monday, March 21, 2005 09:27, symbulos partners wrote: Dear friends, where is the description of the functions md5 in the manual? where is the description of the function crypt()? are there are good alternatives? http://dev.mysql.com/doc/mysql/en/encryption-functions.html -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Queries inside UDF
On Wednesday, March 16, 2005 09:30, sguazt sguazt wrote: Hi folks! (I hope this is the right list ... if not please tell me where I can submit this post) I would like to create a MySQL UDF (i.e. User Defined Function) that embeds a query; for instance, suppose the UDF is named foobar: mysql SELECT foobar(); When foobar function receives the control from the MySQL, it attempts to create a query. To do so it has to connect to DB (since it seems there's no way to access to current DB connection from a UDF function -- at least I did'nt find it any way). So the flow of execution is: SELECT foobar() 1 -- Call foobar 2 - init MySQL 3 - connect to MySQL 4 - create/execute query 5 - get query result 6 - close MySQL connection 7 - return result [...snip...] const char* query = SELECT COUNT(*) FROM tblfoobar; Can you explain exactly what you are using this for? What benefit does this provide over just executing the query? You can either execute SELECT foobar() or SELECT COUNT(*) FROM tblfoobar, and you don't have to do anything to make the latter work. Creating the UDF just seems like a lot of extra work. Maybe this is my ignorance, but I don't see much use for executing a query within a UDF esspecially if you are using 4.1 with subqueries. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Reg creating log
On Monday, March 14, 2005 03:27, [EMAIL PROTECTED] wrote: Hi, Thank you for your reply. I tried even with --tee option with mysql as follows Mysql -uroot db_name --tee = test 'input_file' 'output_file' mysql -uroot db_name input_file 1 output_file 2 err_file But in the out put file I am getting as below Logging to file 'test' Actual error is not logging into the ouput file. I am able to see the error messages in the command prompt. How to get these error messages in the output file. Please help me in this. Thanks, Narasimha -Original Message- From: Gleb Paharenko Sent: Friday, March 11, 2005 7:34 PM To: mysql@lists.mysql.com Subject: Re: Reg creating log Hello. You may use --tee option of mysql to store the results in the file. Or just redirect the output: mysql -uroot db_name 'input_file' 'output_file'. See: http://dev.mysql.com/doc/mysql/en/mysql-commands.html [EMAIL PROTECTED] wrote: Hi, What is the command to create log file while executing mysql command. I.e I want to run a file using mysql as follows Mysql -uroot db_name Inputfile I want to log the above results into a log file, to do the same what option I have to use here with mysql command. PS: Input file contains some sql statements. Please help me in this. This is very urgent. Thanks, Narasimha -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CASE statement and version 4.1.x
On Wednesday, March 09, 2005 07:49, Philippe Poelvoorde wrote: Daniel Kasak wrote: Homam S.A. wrote: In the documentation, it doesn't mention which version of MySQL supports the CASE statement, but it refers to stored procedures, so is it only supported for 5.x? I can't get any example of a CASE statement work in MySQL. snipped For example, I can execute the following in MS SQL Server: UPDATE MyTable SET field1 = CASE WHEN field3 = 1 THEN 10 ELSE 20 END, field2 = field2 | CASE WHEN field4 = 'B' THEN 1 ELSE 0 END | CASE WHEN field4 = 'C' THEN 2 ELSE 0 END What error do you get? I can get a statement similar to this to work. Case works in 4.0.x ( and maybe before, haven't checked ). Your problem is elsewhere. I'm not sure if you can use the pipe symbol as an OR operator in the way you want - to be honest I haven't used it at all in SQL. Try rewriting it to use nothing but case statements ( no | operators ). http://dev.mysql.com/doc/mysql/en/logical-operators.html it's || not | (not sure about bit-operation...) The | is a bitwise OR. http://dev.mysql.com/doc/mysql/en/bit-functions.html -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How do I move the Mysql database files from /var/lib/mysql to someother directory
On Monday, March 07, 2005 22:19, digvijoy chatterjee wrote: I have Mysql 4.1.9 on RHES-3.0 installed by rpms..the default socket path and database creation directory is /var/lib/mysqlnow there is a a dearth of space in /var therefore i would like to move the database files to something like /usr which has lots of free space , creating Symlinks does not seem to help.what is the way out If you are moving all of the databases you can do the following: -Shutdown mysql -Move the that database files from /var/lib/mysql to /my/new/path -Edit /etc/my.cnf - change datadir=/var/lib/mysql to datadir=/my/new/path -Start mysql Here is some info on symbolic links and databases from the manual: http://dev.mysql.com/doc/mysql/en/symbolic-links-to-databases.html -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Use MySQL with Microsoft Office
On Monday, March 07, 2005 23:03, GH wrote: Greetings, I would like to know if it is possible to do a mail merge in Microsoft Office with a mySql database as the source? Thanks You can use install MyODBC, set up a DSN, and then use this in Office. http://dev.mysql.com/downloads/connector/odbc/3.51.html -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Partial and inexact matches
On Saturday, March 05, 2005 15:21, Robert S wrote: Probably a real n00b question. I am writing a simple contact management database with MySQL (version 3.23.49-8.9, Debian Woody). I'd like users to be able to enter the first few characters of a contact's name to do a search, and I'd like the option of doing inexact matches or phonetic matches. For phonetic matches you will want to check out the soundex function. http://dev.mysql.com/doc/mysql/en/string-functions.html What sort of SELECT/WHERE statement do I need to do these things? I've combed the documentation and used Google but haven't found any such thing. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup scripts
On Thursday, March 03, 2005 13:12, [EMAIL PROTECTED] wrote:
Hi Kelly,
Hello,
When I attempt to try and run the backup:
shell mysqldump --tab=/path/to/some/dir --opt db_name
I get the following errors:
./mysqldump: Got error: 1: Can't create/write to file
'/usr/local/mysql/bakups/config.txt' (Errcode: 13) when executing
'SELECT INTO OUTFILE'
[EMAIL PROTECTED] tom]$ perror 13
Error code 13: Permission denied
You need to make sure that the user mysqld is running as has write and
execute permission to /usr/local/mysql/bakups.
Read the following page. Specifically the part about the tab option.
http://dev.mysql.com/doc/mysql/en/mysqldump.html
Or:
shell mysqlhotcopy db_name /path/to/some/dir
DBI-connect(;host=localhost;mysql_read_default_group=mysqlhotcopy)
failed: Client does not support authentication protocol requested by
server; consider upgrading MySQL client at ./mysqlhotcopy line 178
You need a newer version of DBD-mysql (you can get this from CPAN),
or you can use the old password option.
http://dev.mysql.com/doc/mysql/en/old-client.html
I followed the directions from:
http://dev.mysql.com/doc/mysql/en/backup.html
I also attempted to follow these directions, to no avail:
--
mysqlhotcopy, etc is great - but using it (and most other myql
automation scripts) requires placing a user/password on the command
line for all/some to see (ps axw)
There doesn't appear to be a way to place the user/pass into a file
somewhere and specify only that (secured) filename on the command
line.
I get around this in the case of mysqlhotcopy by taking a local copy
of the script (perl) and hard-coding the auth info into that copy
thus:
mysqlhotcopy - line 164ish:
my $dbh =
DBI-connect(dbi:mysql:$dsn;mysql_read_default_group=mysqlhotcopy,
'backup_user', 'backup_password'},
and again at around line 745:
my $dbh =
DBI-connect(dbi:mysql:${db}${dsn};mysql_read_default_group=mysqlhotcopy,
backup_user, backup_password,
then, just to be sure,
chown root.nobody mysqlhotcopy
chmod 700 mysqlhotcopy
Any ideas would be greatly appreciated. I would really like to add
this to a cronjob to have it run automatically. Thanks in advance!
--
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup scripts
Kelly,
You can find out what user mysqld is running as with the following:
#ps axu | grep mysqld
To change the permissions on the directory run the following as root:
#chown mysql.mysql /usr/local/mysql/bakups
#chmod 770 /usr/local/mysql/bakups
If it is a different user, substitute it in for mysql in the first
command.
--
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa
On Thursday, March 03, 2005 13:58, [EMAIL PROTECTED] wrote:
Tom,
I seem to be having difficulty allowing the mysqld user wx privs on
the /backup folder. I read the following, but I don't see how to
allow mysqld to w and x to that directory:
http://dev.mysql.com/doc/mysql/en/mysqldump.html
Kelly
On Thursday, March 03, 2005 13:12, [EMAIL PROTECTED]
wrote:
Hi Kelly,
Hello,
When I attempt to try and run the backup:
shell mysqldump --tab=/path/to/some/dir --opt db_name
I get the following errors:
./mysqldump: Got error: 1: Can't create/write to file
'/usr/local/mysql/bakups/config.txt' (Errcode: 13) when executing
'SELECT INTO OUTFILE'
[EMAIL PROTECTED] tom]$ perror 13
Error code 13: Permission denied
You need to make sure that the user mysqld is running as has write and
execute permission to /usr/local/mysql/bakups.
Read the following page. Specifically the part about the tab option.
http://dev.mysql.com/doc/mysql/en/mysqldump.html
Or:
shell mysqlhotcopy db_name /path/to/some/dir
DBI-connect(;host=localhost;mysql_read_default_group=mysqlhotcopy)
failed: Client does not support authentication protocol requested by
server; consider upgrading MySQL client at ./mysqlhotcopy line 178
You need a newer version of DBD-mysql (you can get this from CPAN),
or you can use the old password option.
http://dev.mysql.com/doc/mysql/en/old-client.html
I followed the directions from:
http://dev.mysql.com/doc/mysql/en/backup.html
I also attempted to follow these directions, to no avail:
--
mysqlhotcopy, etc is great - but using it (and most other myql
automation scripts) requires placing a user/password on the command
line for all/some to see (ps axw)
There doesn't appear to be a way to place the user/pass into a file
somewhere and specify only that (secured) filename on the command
line. I get around this in the case of mysqlhotcopy by taking a
local copy of the script (perl) and hard-coding the auth info into
that copy thus:
mysqlhotcopy - line 164ish:
my $dbh =
DBI-connect(dbi:mysql:$dsn;mysql_read_default_group=mysqlhotcopy,
'backup_user', 'backup_password'},
and again at around line 745:
my $dbh =
DBI-connect(dbi:mysql:${db}${dsn};mysql_read_default_group=mysqlhotcopy,
backup_user, backup_password,
then, just to be sure,
chown root.nobody mysqlhotcopy
chmod 700 mysqlhotcopy
Any ideas would be greatly appreciated. I would really like to add
this to a cronjob to have it run automatically. Thanks in advance!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ODD COUNT(*) Results on Self-Join (Bug?)
On Monday, February 28, 2005 14:54, Van wrote: Tom: I see your point, but the group by is necessary so I can walk through all Song Title groups and get the total number of unique versions of that song. If I do this: SELECT DISTINCT file_details.Title, file_details_1.CD, file_details_1.mp3Name, COUNT(*) AS cnt FROM file_details LEFT JOIN file_details AS file_details_1 ON file_details.Title = file_details_1.Title WHERE (((file_details.Type) Like 'Song%') AND file_details.CD = 'Wasted Tears' AND file_details_1.Type LIKE 'Song%' AND file_details_1.FileName NOT LIKE '%_ds.php3' AND file_details.Title = 'Seems I\'ll') GROUP BY file_details.Title ORDER BY file_details_1.Title; I get this: ++--+---+-+ Title | CD | mp3Name | cnt | ++--+---+-+ Seems I'll | Wasted Tears | mp3/mp3s/seemsill.mp3 | 6 | ++--+---+-+ COUNT = 6; And, it's distinct. Watch what happens when I do a row listing without the grouping; SELECT DISTINCT file_details.Title, file_details_1.CD, file_details_1.mp3Name FROM file_details LEFT JOIN file_details AS file_details_1 ON file_details.Title = file_details_1.Title WHERE (((file_details.Type) Like 'Song%') AND file_details.CD = 'Wasted Tears' AND file_details_1.Type LIKE 'Song%' AND file_details_1.FileName NOT LIKE '%_ds.php3' AND file_details.Title = 'Seems I\'ll') ORDER BY file_details_1.Title; ++-+-+ Title | CD | mp3Name | ++-+-+ Seems I'll | Fear of Success | mp3/mp3s/SeemsIll20031029.mp3 | Seems I'll | n/a | mp3/mp3s/SeemsIllUnpluggedMixed.mp3 | Seems I'll | Wasted Tears| mp3/mp3s/seemsill.mp3 | ++-+-+ So, here the listing is the group of records I want and there are 3, and it's distinct. Why when I put the group on this query (which is what I need) does it double the count? I think it's a bug. If you displayed all the fields from both tables, I think you would understand the join better. This is somewhat difficult to explain, but because you are only saying file_details_1.FileName NOT LIKE '%_ds.php3' and not file_details.FileName NOT LIKE '%_ds.php3' as well, this will in effect double all of your groupings since you are not grouping by filename, and two rows from table 1 will be left after the where clause to be joined to the three rows left in table 2. This in effect gives you your six rows, or 3 groups of 2 with you group by clause. Like I said above, I would suggest showing all of the fields ie. SELECT file_details.*, file_details_1.* FROM ., so you can get a better idea of what is going on here. Also, there really isn't any reason to do a left join here. An inner join would work just fine since you are joining a table with itself on the same field there will always be a match. By the way this is a horrible explaination, maybe someone else can do a better job of it. Tom Crimmins wrote: On Sunday, February 27, 2005 19:20, Van wrote: Hi Van, Greetings: I've got a table that has the following fields that are relevant to my self-join: FileName | varchar(100) | | MUL | | Title | varchar(45) | | MUL | | Type | varchar(20) | | | HTML | mp3Name | varchar(100) | | | | CD| varchar(25) | | | | Here are the relevant values for the fields for the song Seems I'll: ++++-- FileName | Title | Type | mp3Name | CD | ++++-- mp3/php3/seemsill.php3 | Seems I'll | Song | mp3/mp3s/seemsill.mp3 | Wasted Tears| mp3/php3/SeemsIllUnplugged.php3| Seems I'll | Song | mp3/mp3s/SeemsIllUnpluggedMixed.mp3 | n/a | mp3/php3/SeemsIll20031029.php3 | Seems I'll | Song | mp3/mp3s/SeemsIll20031029.mp3 | Fear of Success | lyrics/seemsill.php3 | Seems I'll | Lyrics | mp3/php3/SeemsIll20031029_ds.php3 | Wasted Tears| mp3/php3/seemsill_ds.php3 | Seems I'll | Song | mp3/mp3s/seemsill.mp3 | Wasted Tears| mp3/php3/SeemsIll20031029_ds.php3 | Seems I'll | Song | mp3/mp3s/SeemsIll20031029.mp3 | Fear of Success | mp3/php3/SeemsIllUnplugged_ds.php3 | Seems I'll | Song | mp3
RE: MySQL and triggers
On Monday, February 28, 2005 18:35, [EMAIL PROTECTED] wrote: Hey. We are two girls who are new to using MySQL. We are using MySQL 4.1, and we wonder if this version supports the use of triggers? We have tried to find the answer ourself, but with no luck - can anyone help us? Not in 4.1. Triggers aren't supported until 5.0.2. http://dev.mysql.com/doc/mysql/en/using-triggers.html -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ODD COUNT(*) Results on Self-Join (Bug?)
On Sunday, February 27, 2005 19:20, Van wrote:
Hi Van,
Greetings:
I've got a table that has the following fields that are relevant to my
self-join:
FileName | varchar(100) | | MUL |
|
Title | varchar(45) | | MUL |
|
Type | varchar(20) | | | HTML
|
mp3Name | varchar(100) | | |
|
CD| varchar(25) | | |
|
Here are the relevant values for the fields for the song Seems I'll:
++++--
FileName | Title | Type |
mp3Name | CD |
++++--
mp3/php3/seemsill.php3 | Seems I'll | Song |
mp3/mp3s/seemsill.mp3 | Wasted Tears|
mp3/php3/SeemsIllUnplugged.php3| Seems I'll | Song |
mp3/mp3s/SeemsIllUnpluggedMixed.mp3 | n/a |
mp3/php3/SeemsIll20031029.php3 | Seems I'll | Song |
mp3/mp3s/SeemsIll20031029.mp3 | Fear of Success |
lyrics/seemsill.php3 | Seems I'll | Lyrics |
mp3/php3/SeemsIll20031029_ds.php3 | Wasted Tears|
mp3/php3/seemsill_ds.php3 | Seems I'll | Song |
mp3/mp3s/seemsill.mp3 | Wasted Tears|
mp3/php3/SeemsIll20031029_ds.php3 | Seems I'll | Song |
mp3/mp3s/SeemsIll20031029.mp3 | Fear of Success |
mp3/php3/SeemsIllUnplugged_ds.php3 | Seems I'll | Song |
mp3/mp3s/SeemsIllUnpluggedMixed.mp3 | n/a |
++++--
Here is the query in question (I'm trying to get the count of all
versions of Seems I'll songs, which was originally on the CD Wasted
Tears, so I can display the other versions, including the one on
Wasted Tears {mp3/mp3s/seemsill.mp}):
SELECT file_details.Title,
file_details.Type,
file_details_1.CD,
file_details_1.mp3Name,
COUNT(*) AS cnt
FROM file_details LEFT JOIN file_details AS file_details_1
ON file_details.Title = file_details_1.Title
WHERE (((file_details.Type) Like 'Song%')
AND file_details.CD = 'Wasted Tears'
AND file_details_1.Type LIKE 'Song%'
AND file_details_1.FileName NOT LIKE '%_ds.php3'
AND file_details.Title = 'Seems I\'ll')
GROUP BY file_details.Title, file_details.Type, file_details_1.CD,
file_details_1.mp3Name
ORDER BY file_details_1.Title;
But, oddly here is the result of this query:
++--+-+-
Title | Type | CD |
mp3Name | cnt |
++--+-+-
Seems I'll | Song | Fear of Success |
mp3/mp3s/SeemsIll20031029.mp3 | 2 |
Seems I'll | Song | n/a |
mp3/mp3s/SeemsIllUnpluggedMixed.mp3 | 2 |
Seems I'll | Song | Wasted Tears|
mp3/mp3s/seemsill.mp3 | 2 |
++--+-+-
The count should be 3, right? What gives?
The cnt field looks to be correct here based on your data. The count
here is the number of results in that grouping not the total number of
rows returned. If you remove the group by clause from your query, you
should see 6 rows returned (2 of each).
--
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: GROUP BY Clause
On Friday, February 25, 2005 15:05, Asad Habib wrote: I am trying to use GROUP BY with a field of type text that is set to NOT NULL by default. However, in practice this field does not always contain a string for every record and defaults to the empty string in this case. When I try to use GROUP BY with this field in a SELECT statement, only 1 record is retrieved. Anyone experience a similar problem? Your help would be greatly appreciated. Thanks. This should only happen if the values in the column you are grouping by are the same for every row that would be returned without the GROUP BY. Without knowing your query and some of the data in your table, I can't really tell you anything else. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Shell execution of mysql query
On Wednesday, February 23, 2005 12:30, Nupur Jain wrote: I am executing a mysql query through shell and expecting to see a return of SQL execution. mysql -D $dbName --vertical -u $DBUSER -p$DBPASS $queryFile $opFile rc=$? Here rc is always 0 and so are $opFile entries. $queryFile contains exactly the same query as listed below. If the same query is run in mysql client, I see the result as Empty set (0.00 sec). Why don't I get a return in shell. mysql select * from usrtbl where username='vou_0004016'; Empty set (0.00 sec) It makes sense the the return code from mysql is zero, since it sucessfully executed. If it is an empty set, your output file will be empty. If you want the file to show you the query and empty set, change your command to the following: mysql -vv -D $dbName --vertical -u $DBUSER -p$DBPASS $queryFile $opFile The -vv controls sets the verbosity of the output from the client. Thanks, Nupur -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is there a limit on Auto-increment
On Tuesday, February 22, 2005 16:12, [EMAIL PROTECTED] wrote: I am using the memory table in 4.1 to auto increment is there a limit to how big that number can get? It is only limited by the size of your int. I would suggest declaring the column unsigned. This will give you twice the positive values for your column. An unsigned bigint gives you the ability to have 2^64 - 1 unique values. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: newbie question
On Tuesday, February 22, 2005 22:08, jsf wrote: This may be more of a PHP question than a MySQL question but here goes: I have a small database with two tables in it. It's a database of Botanical Gardens in the US. Table 1 contains the botanical gardens and has 8 fields: (I'm abbreviating for brevity and clarity): id, name, address, town, state_id, zip, phone, url Table 2 contains the states referred to in the 'state_id' field and itself has 3 fields id, abbreviation, name So, 'id' in the 'state' table is linked to the 'gardens' table via that table's 'state_id' field. Now, in pulling data out of the database to display on a web page I have all of my connection stuff working and the query of 'select * from gardens' along with this php code: You need join the two tables: SELECT t1.name, t1.address, t1.town, t2.abbreviation, t1.zip, t1.phone, t1.url FROM table1 as t1 INNER JOIN table2 as t2 ON (t1.state_id = t2.id); Obviously you will have to modify this example since I don't have the exact info for your tables (such as names). You can find more info on joins here: http://dev.mysql.com/doc/mysql/en/join.html Also, you can alias the columns as well, to make them easier to reference in your code ie. t1.name as name td align=center?php echo $row_Recordset1['botgard_name']; ?/td td align=center?php echo $row_Recordset1['botgard_address']; ?/td td align=center?php echo $row_Recordset1['botgard_town']; ?/td td align=center?php echo $row_Recordset1['state_id']; ?/td td align=center?php echo $row_Recordset1['botgard_zip']; ?/td td align=center?php echo $row_Recordset1['botgard_phone']; ?/td td align=center?php echo $row_Recordset1['botgard_url']; ?/td works fine, pulling records out of the 'gardens' table and displaying it on a web page, but, of course, I'm seeing the 'state_id' instead of either the 'state_abbreviation' or the 'state_name'. I am at a complete and total loss as to how to edit my code at this point so that, before displaying anything, i can grab either 'state_abbr' or 'state_name' from the states table, properly associated with the 'state_id' and display the actual state abbreviation or state name in my web page. I'm so close, yet so far. I know if I can be shown once how this works, I'll be able to apply the solution again in the future. Thanks in advance for any help with this. Sincerely, Joshua -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL and DNS problem
On Thursday, February 17, 2005 09:41, Ian Meyer wrote: Hello everyone, We have a few MySQL servers (4.1.8) running on RedHat ES3. We're having problems when trying to use hostnames in the grant command. Example: create database blah; grant all on blah.* to 'user'@'host' identified by ''; (also have used the FQDN instead of just host) When trying to connect, it fails with the message: 'MySQL Error Number 1045 Access denied for user 'user'@'192.168.2.103' (using password: YES' Our DNS servers have correct forward and reverse entries for all of our machines. I read the docs about MySQL and DNS, but I still can't figure this out. I know you said you have correct reverse entries, but just as a test if you run 'host 192.168.2.103' on the mysql host, does it give back the hostname you used in your grant? Thanks, Ian -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL and DNS problem
On Thursday, February 17, 2005 10:26, Ian Meyer wrote: Tom Crimmins wrote: On Thursday, February 17, 2005 09:41, Ian Meyer wrote: Hello everyone, We have a few MySQL servers (4.1.8) running on RedHat ES3. We're having problems when trying to use hostnames in the grant command. Example: create database blah; grant all on blah.* to 'user'@'host' identified by ''; (also have used the FQDN instead of just host) When trying to connect, it fails with the message: 'MySQL Error Number 1045 Access denied for user 'user'@'192.168.2.103' (using password: YES' Our DNS servers have correct forward and reverse entries for all of our machines. I read the docs about MySQL and DNS, but I still can't figure this out. I know you said you have correct reverse entries, but just as a test if you run 'host 192.168.2.103' on the mysql host, does it give back the hostname you used in your grant? This was run on the database server: [EMAIL PROTECTED] imeyer]$ host 192.168.2.103 103.2.168.192.in-addr.arpa domain name pointer x.x.com. [EMAIL PROTECTED] imeyer]$ host x.x.com x.x.com has address 192.168.2.103 The error message MySQL shows the IP address. You don't happen to have skip-name-resolve in your my.cnf do you? I'm sure you probably already checked that. I think the grant will create a warning anyway if you try to give a hostname with this option enabled. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What is the max length of IN() function?
-Original Message- From: Daevid Vincent Sent: Wednesday, February 16, 2005 20:59 To: mysql@lists.mysql.com Subject: What is the max length of IN() function? I tried to find this function on the dev.mysql.com site, but good luck finding in... ;-) Can someone tell me what the maximum length is for this function? SELECT * FROM foo WHERE bar IN(1,2,3,4,. N); How many entries can there be in between 1 and N ? Hundreds? Thousands? Millions? From http://dev.mysql.com/doc/mysql/en/comparison-operators.html, The number of values in the IN list is only limited by the max_allowed_packet value. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: one hour is/is not 60 minutes, that's the question...
The minute part of a time expression only has a valid range of 0 to 59. http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html http://dev.mysql.com/doc/mysql/en/time.html --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: schlubediwup [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 15, 2005 11:48 To: mysql@lists.mysql.com Subject: one hour is/is not 60 minutes, that's the question... Hi again mysql-listers mysql select version(); ++ | version() | ++ | 4.1.9-standard-log | ++ 1 row in set (0.00 sec) mysql [EMAIL PROTECTED]:~ uname -a Linux mydom 2.6.4-54.5-default #1 Fri May 7 21:43:10 UTC 2004 i686 i686 i386 GNU/Linux [EMAIL PROTECTED]:~ mysql select addtime(now(), '00:00:00'); ++ | addtime(now(), '00:00:00') | ++ | 2005-02-15 16:49:17| ++ 1 row in set (0.00 sec) mysql select addtime(now(), '00:60:00'); ++ | addtime(now(), '00:60:00') | ++ | NULL | ++ 1 row in set, 1 warning (0.00 sec) mysql select addtime(now(), '01:00:00'); ++ | addtime(now(), '01:00:00') | ++ | 2005-02-15 17:50:27| ++ 1 row in set (0.00 sec) in my opinion the result of the second and third example above must be the same. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database creation privileges
-Original Message- From: Tim Traver Sent: Tuesday, February 15, 2005 19:30 To: mysql@lists.mysql.com Subject: Database creation privileges Hi all, ok, I thought I had it figured out. I am using 4.1.9 now, and it looks like it behaves a little bit differently (or maybe not) than the previous 4.0.20 did when it comes to privileges. I want to create a user that does not have the ability to create databases. But, I do want them to be able to create tables in a specific database. Currently, I create the user in the global user table, and give then no privileges. Then, when I create a database, I assign them the privileges to that database by using a command like this : GRANT select,insert,update,delete,create,drop ON dbname.* to username@'%' identified by 'userpass'; This seems to work, but when that user logs in, they are able to create a database ! Your grant statement should work fine on 4.1.9. Check the permissions with: SHOW GRANTS FOR 'username'@'%'; It should return the following: GRANT USAGE ON *.* TO 'username'@'%' IDENTIFIED BY PASSWORD 'password_hash' GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `dbname`.* TO 'username'@'%' They will be able to drop the database dbname, but I doubt this is an issue since you want them to be able to have the ability to drop tables from this db anyway. Also when you connect as this user, try: SELECT CURRENT_USER(); Make sure that it returns [EMAIL PROTECTED], to verify that this connection is not falling under a different grant that does not have a wildcard. If you are connected as that user, then SHOW GRANTS FOR CURRENT_USER() will accomplish both of the above in one step. If I don't have the create privilege specified, then they aren't able to create tables, which I want them to be able to do... Is there a way to assign a user to a database, and give them the ability to do anything within that database, but not create another database Thanks, Tim. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select last row
If you have an auto_increment column, SELECT * FROM my_table ORDER BY auto_increment_col DESC LIMIT 1 If you don't have an auto_increment this might be a good time to add one. -Original Message- From: Mulley, Nikhil [mailto:[EMAIL PROTECTED] Sent: Monday, February 14, 2005 08:15 To: mysql@lists.mysql.com Subject: select last row Hi All, I have a table which is being continuosly updated, I just wanted to know how to output only the last row with the select statement. can anyone please tell me howto. thanks, Nikhil --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help with historic aggregation of data
-Original Message- From: Daevid Vincent Sent: Friday, February 11, 2005 14:32 To: mysql@lists.mysql.com Subject: Need help with historic aggregation of data I need to get the aggregate data from various tables for a report. The idea is that we audit devices daily on a schedule, and also allow users to audit the devices by choosing certain tests to run. It is also the case that new tests are added daily. So the scheduled test today has more tests than yesterdays and that has more than the day before's, etc. I want to get a report that shows ALL tests ever run on the device in it's lifetime, but only the most recent of each test (and the date it was from). So if I ran tests like this: Date Device TestResult - -- - -- 02/011 100 [scheduled] blah blah blah... 02/011 101 [scheduled] blah blah blah... 02/011 102 [scheduled] blah blah blah... 02/011 105 [one off] foo foo foo... 02/021 100 [scheduled] blah blah blah... 02/021 101 [scheduled] blah blah blah... 02/021 102 [scheduled] blah blah blah... 02/021 103 [scheduled] ble ble ble... 02/021 106 [one off] bar bar bar... 02/031 100 [scheduled] blah blah blah... 02/031 101 [scheduled] blah blah blah... 02/031 102 [scheduled] blah blah blah... 02/031 103 [scheduled] ble ble ble... 02/031 104 [scheduled] blo blo blo... 02/012 100 [scheduled] blah blah blah... 02/012 101 [scheduled] blah blah blah... 02/012 102 [scheduled] blah blah blah... 02/012 106 [one off] bar bar bar... ... Etc ... What I'd expect to get back for device 1 is Test Date - 100 02/03 this is more current than others 101 02/03 this is more current than others 102 02/03 this is more current than others 103 02/03 this is more current than others 104 02/03 this is more current than others 105 02/01 since this was run long ago once 106 02/02 since this was ran recently SELECT device,test,MAX(date) FROM my_table GROUP BY device,test ORDER BY device,test This will give you all devices. and SELECT test,MAX(date) FROM my_table WHERE device=1 GROUP BY test ORDER BY test will give you results for device 1. My actual tables are pretty huge, and I'll spare you them. I also am coding this in PHP, in case I need to split this task up somehow. We are using v4.0.18 and can't change. I'm hoping there is some magic incantation of MAX(), GROUP BY, DISTINCT, that will harvest this info for me. Thanks in advance, Daevid. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Remove spaces
Please post the error because this looks correct. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: John Berman [mailto:[EMAIL PROTECTED] Sent: Friday, February 11, 2005 16:05 To: 'Homam S.A.' Cc: mysql@lists.mysql.com Subject: RE: Remove spaces Thanks for this I did this: update mc_census set surname = trim(surname) however it fails with a syntax error ? I'm on 4.1 Regards John B -Original Message- From: Homam S.A. [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 21:29 To: mysql@lists.mysql.com Subject: Re: Remove spaces update your_table set your_field = trim(your_field) --- John Berman [EMAIL PROTECTED] wrote: Hi I have a table with a number of fields The table is already populated, however some entries have got spaces both before and after the data. Future imports into the table will have the spaces removed, however im still stuck with my extra spaces. I have checked the Mysql manual but could not figure out how to remove the extra spaces that are already in the dbase ? Any help appreciated. Regards John B -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Remove spaces
-Original Message- From: John Berman [mailto:[EMAIL PROTECTED] Sent: Friday, February 11, 2005 16:22 To: 'Tom Crimmins' Cc: mysql@lists.mysql.com Subject: RE: Remove spaces The error is simply: [JGSGB 4.1 Host] ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update mc_census set surname = trim(surname)' at line 6 line 6 ??? Something is wrong here. Are you excuting this from the mysql client? Something is getting sent to the server before this. Put a semi-colon before your statement and it will probably work, but I don't know exactly what the problem is. John B -Original Message- From: Tom Crimmins [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 22:16 To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: Remove spaces Please post the error because this looks correct. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: John Berman [mailto:[EMAIL PROTECTED] Sent: Friday, February 11, 2005 16:05 To: 'Homam S.A.' Cc: mysql@lists.mysql.com Subject: RE: Remove spaces Thanks for this I did this: update mc_census set surname = trim(surname) however it fails with a syntax error ? I'm on 4.1 Regards John B -Original Message- From: Homam S.A. [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 21:29 To: mysql@lists.mysql.com Subject: Re: Remove spaces update your_table set your_field = trim(your_field) --- John Berman [EMAIL PROTECTED] wrote: Hi I have a table with a number of fields The table is already populated, however some entries have got spaces both before and after the data. Future imports into the table will have the spaces removed, however im still stuck with my extra spaces. I have checked the Mysql manual but could not figure out how to remove the extra spaces that are already in the dbase ? Any help appreciated. Regards John B --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Innodb auto increment - reset itself automatically?
You may want to read this section of the manual: http://dev.mysql.com/doc/mysql/en/innodb-auto-increment-column.html --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Rishi Daryanani [mailto:[EMAIL PROTECTED] Sent: Friday, February 11, 2005 22:16 To: mysql@lists.mysql.com Subject: Innodb auto increment - reset itself automatically? Hi, My database is mostly made up of MyIsam tables, and some InnoDB tables. One particular Innodb table works fine with an auto increment field. The table is updated often, records being added and deleted at pretty much the same rate. So, there are only a very few records in the table at any given time. It was being used and the auto increment value was around 21. Recently, after the records were deleted in the system (by my client - through a database system that I created - NOT directly via the db), I noticed that new records to the table start with the auto increment field '1'. I don't understand this. The autoincrement field seems to have reset itself back to 0. I know this because there are now 3 records in that table with ids 1,2,3 I tested it again by adding a new record, which was assigned the id 4. Then, i deleted that, and added a new record, which was assigned the id 5. So its working as normal again! I just dont understand how Mysql reset the autoincrement field from 21 back to 0 Can anyone think of a reason why this would happen? I'm really worried about the database now :( Thanks very much! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: aborting slipped keys
-Original Message- From: Scott Haneda Sent: Thursday, February 10, 2005 04:40 To: MySql Subject: aborting slipped keys In the myslq monitor, in my shell, sometimes I mess up and slip a few keys, and mysql is just stuck, waiting on more input, and nothing I tell it, other than a control-C gets me out, at which point I have to login again, which is a time killer. How do you get out of a stuck mysql? By stuck, do you mean that you need to cancel the query? If so \c will do the trick. Sometimes, and I can not figrue out what triggers it, but I like it, I get Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A auto completion is very nice, how do I make sure it is on all the time? thanks -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED] Novato, CA U.S.A. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa office 712.328.4808 mobile 402.677.1592 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Daily Incremental Backups on Mysql
-Original Message- From: Jerry Swanson Sent: Thursday, February 10, 2005 08:33 To: Daniel Kasak Cc: mysql@lists.mysql.com Subject: Re: Daily Incremental Backups on Mysql Does binary logs are avaialbe in Mysql 4 or only in Mysql 5? Thanks Binary logs are available in version 4. There are differences in format between the versions. See the following for detail. http://dev.mysql.com/doc/mysql/en/binary-log.html On Thu, 10 Feb 2005 10:28:56 +1100, Daniel Kasak [EMAIL PROTECTED] wrote: Jerry Swanson wrote: Is it possible to do daily incremental backups on mysql? Thanks Yes. mysqldump will give you a starting point, and the binary transaction logs give you your incremental backups - copy these somewhere each day - onto a backup tape or something. Read the documentation for mysqldump and for processing the binary transaction logs. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need a New Password Username
-Original Message- From: David Blomstrom Sent: Thursday, February 10, 2005 08:52 To: mysql@lists.mysql.com Subject: Need a New Password Username I just recreated a database after my computer crashed. All my MyISAM tables survived, but my InnoDB tables vanished. The other problem is that I need to reassign a username and password, and I can't remember how I did that originally. http://dev.mysql.com/doc/mysql/en/grant.html I now have the latest version of phpMyAdmin, but I can't find any reference to username or password. There are instructions for assigning passwords in MYSQLADMIN at http://dev.mysql.com/doc/mysql/en/passwords.html Would these same instructions work in phpMyAdmin...I'd just click Query and type the commands in the window under Run SQL query/queries on database XXX_ZZZ:? As I recall, localhost is the standard ROOT name and is presumably already assigned. I've assigned a database name, so I just need to reassign my username and password. Thanks. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need a New Password Username
-Original Message- From: David Blomstrom Sent: Thursday, February 10, 2005 09:21 To: mysql@lists.mysql.com Subject: RE: Need a New Password Username Wow, I don't remember dealing with all that code the first time around. Can someone show me EXACTLY what I would write if my database is named my_database, and I want to add the username private_host and the password superstar? And if I don't have mysqladmin, can I type it into the SQL Query window on phpMyAdmin? GRANT ALL ON my_database.* TO 'private_host' IDENTIFIED BY 'superstar'; You can add host restrictions to by adding @'hostname' after the username. It's all there in the manual. Anyway, I haven't used phpMyAdmin, but if you can just enter queries, which I assume you can, then this will work. Thanks. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Importing Tables on Top of Tables
-Original Message- From: David Blomstrom Sent: Thursday, February 10, 2005 10:06 To: mysql@lists.mysql.com Subject: Importing Tables on Top of Tables If I revise a MySQL table and try to publish it online, I often get the error message, Table my_table already exists. So I have to delete the online version before I can import the revised version. This error comes from issuing a CREATE TABLE when a table with that name already exists. Is there a way to just publish one table over another, as long as both have the same name? There are two ways to do this. Issue a DROP TABLE IF EXISTS my_table, before the CREATE TABLE my_table, or you can issue TRUNCATE TABLE my_table and ditch the CREATE TABLE statement. Thanks. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 1 to many query
-Original Message- From: livejavabean Sent: Thursday, February 10, 2005 16:47 To: mysql@lists.mysql.com Subject: 1 to many query Hi there.. Hope you can give me some thoughts on this. let say we have 3 tables table 1 (pk=project_id) === - project_id - project_name table 2 (pk=project_id, project_state_flag) === - project_id - project_state_flag (fk to state_flag) table 3 (pk=state_flag) === - state_flag - state_flag_name thank you.. but do u think it is possible to make the query return: - 1 row per project - each project state row's state become a column e.g. project 1, name, state a, state b, state c... project 2, name, state a, state b, state c. thanks in advance.. This looks like a many to many relationship to me. Each project is associated with multiple state_flags, and each state_flag can be associated with multiple projects. If you have mysql 4.1 or greater, you can use try the following. It won't get you separate columns for each state_flag_name, but it will give you a list of all the state_flag_names associated with each project in a single column. SELECT t1.project_id, t1.project_name, GROUP_CONCAT(t3.state_flag_name) as state_flags FROM t1 INNER JOIN t2 ON (t1.project_id = t2.project_id) INNER JOIN t3 ON (t2.project_state_flag = t3.state_flag) GROUP BY t1.project_id regards, -ljb --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 1 to many query
-Original Message- From: Tom Crimmins Sent: Thursday, February 10, 2005 17:08 To: livejavabean Cc: mysql@lists.mysql.com Subject: RE: 1 to many query -Original Message- From: livejavabean Sent: Thursday, February 10, 2005 16:47 To: mysql@lists.mysql.com Subject: 1 to many query Hi there.. Hope you can give me some thoughts on this. let say we have 3 tables table 1 (pk=project_id) === - project_id - project_name table 2 (pk=project_id, project_state_flag) === - project_id - project_state_flag (fk to state_flag) table 3 (pk=state_flag) === - state_flag - state_flag_name thank you.. but do u think it is possible to make the query return: - 1 row per project - each project state row's state become a column e.g. project 1, name, state a, state b, state c... project 2, name, state a, state b, state c. thanks in advance.. This looks like a many to many relationship to me. Each project is associated with multiple state_flags, and each state_flag can be associated with multiple projects. If you have mysql 4.1 or greater, you can use try the following. It won't get you separate columns for each state_flag_name, but it will give you a list of all the state_flag_names associated with each project in a single column. SELECT t1.project_id, t1.project_name, GROUP_CONCAT(t3.state_flag_name) as state_flags FROM t1 INNER JOIN t2 ON (t1.project_id = t2.project_id) INNER JOIN t3 ON (t2.project_state_flag = t3.state_flag) GROUP BY t1.project_id Sorry, also forgot to add that if you want projects returned that don't have any state_flags associated with them you will need to make that first inner join a left join. regards, -ljb --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Syntax diagram, where is it located in the doc?
-Original Message- From: Thomas Sundberg Sent: Friday, February 04, 2005 07:48 To: mysql@lists.mysql.com -Original Message- From: Michael Stassen Sent: den 4 februari 2005 14:19 To: Thomas Sundberg Cc: mysql@lists.mysql.com From the manual, where_definition consists of the keyword WHERE followed by an expression that indicates the condition or conditions that rows must satisfy to be selected. http://dev.mysql.com/doc/mysql/en/select.html That seems simple and straightforward to me. Perhaps if you told us why you need this, someone could provide you with the answer you need. It is very simple but absolutely not straight forward. It really doesn't say anything. Just that you should do things right and then you will not have any problems. The concrete problem I tried to solve were if MySQL supports xor in a where clause. And if so, how should the syntax be written? That Yes, you can use XOR in the where clause. SELECT * FROM mytable WHERE col1 XOR col2; This is not a bitwise XOR, it evaluates each column to true or false first then evals the XOR. example for an int column: a | b | eval 0 | 0 | false 1 | 0 | true 1 | 1 | false -1| 12| false 12| 0 | true would have been extremely simple if the syntax diagram started just above the quote you supplied us with had been completed and not ended when things got a bit interesting. /Thomas --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Indexing Questions (Problem?)
; +---+--+---+--+-+--+ +-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+ +-+ | logs | ALL | priority | NULL |NULL | NULL | 139817 | Using where; Using filesort | +---+--+---+--+-+--+ +-+ 1 row in set (0.00 sec) mysql EXPLAIN SELECT * FROM logs WHERE priority=notice ORDER BY seq DESC; +---+--+---+--+-+---+-- +-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+---+-- +-+ | logs | ref | priority | priority | 11 | const | 4003 | Using where; Using filesort | +---+--+---+--+-+---+-- +-+ 1 row in set (0.00 sec) It doesn't look like it is helping at all for any but the last SELECT. There are several things that I don't understand. Why does the second query not benefit from the index but the third does? My guess is that most of your rows have priority=warning. Due to the cardinality of the index, the optimizer knows that it will not really benefit from this index. In the case of priority=notice, the query will benefit from the index. You could add the following index, but I don't know how useful these last queries will be since both return a lot of rows. ALTER TABLE logs ADD UNIQUE (priority,seq); Why does the select say Using filesort but seq is indexed? The column used to restrict rows s not part of the key, therefore this index will not help. The above index will fix this. You may want to read the following. It explains how mysql uses indexes with the order by clause. http://dev.mysql.com/doc/mysql/en/order-by-optimization.html Do I need to make a special index to index time on HOUR? Is it even possible? I believe the index on time will work for this. Thanks, BMG --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Indexing Questions (Problem?)
Do I need to make a special index to index time on HOUR?
Is it even
possible?
I believe the index on time will work for this.
No, it won't. At least, not with the query as is:
SELECT * FROM logs
WHERE host IN ('10.20.254.5')
AND date='2005-02-03'
AND HOUR(time) BETWEEN '16' AND '17'
ORDER BY seq DESC;
Once you feed a column through a function, you prevent use of
its index.
Yep my fault, meant to explain that the query should be changed. I thought I
did that above when I suggested adding the index for the first query, but I
obviously did not.
---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: List of connection error
Is there any documentation where I can find a list of all connection related error/error codes returned by MySQL? OS error codes : http://dev.mysql.com/doc/mysql/en/operating-system-error-codes.html Server error messages : http://dev.mysql.com/doc/mysql/en/error-handling.html --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Ignoring username parameter when connecting via ssh tunnel
I am trying to connect to my mysql server through an SSH tunnel. On the server, I have a local instance of mysql running, but one of the hosted domains needs to access another remote mysql server. For security, I want to connect to the remote server via an ssh tunnel. I am creating the tunnel using the following command: ssh -L 3307:xxx.xxx.xxx.xxx:3306 -l root -N xxx.xxx.xxx.xxx -f This creates a tunnel so I can connect to port 3307 on the local server, and end up talking to the remote server on 3306. Telneting to 127.0.0.1:3307 gives me the mysql handshake. Now the fun begins when I try to use the connection. If I do: mysql -h xxx.xxx.xxx.xxx -u leg_shop -p, I can log into the remote server using the username leg_shop. This works fine with no problems except the fact the traffic is not encrypted as it isnt using the ssh tunnel. If I do: mysql -h 127.0.0.1 -P 3307 -u leg_shop -p, it connects to the remote server through the ssh tunnel, but for some insane reason, it ignores the -u leg_shop. I can enter any username of my choice (e.g. a user which is DEFINATELY not valid on the remote server), and yet it still connects. Am I missing something here ? On the server where I am trying to connect FROM, it has mysql client mysql Ver 14.7 Distrib 4.1.7, for pc-linux (i686), and on the remote server I am trying to connect to via the tunnel, it is running mysql Ver 12.22 Distrib 4.0.16, for pc-linux (i686) Any ideas or suggestions welcome. Richard Since you are using an ssh tunnel, you will be able to connect with any username if you have not removed the anonymous accounts from the user table. This is because to mysql, I believe that it will look like these connections are coming for the local machine. You could test this by putting -p nothepassword on your connect line and look at the access denied message to see who you are connecting as. What I am trying to say is that connecting from the remote machine with out the tunnel is not the same as connecting with the tunnel as far as permissions are concerned. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why does dropping indexes takes such a long time?
I guess this should be a reminder to everyone that your out of office replies should not go to mailing lists :) --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Homam S.A. [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 02, 2005 2:56 PM To: Keith Ivey; mysql@lists.mysql.com Cc: Homam S.A. Subject: Re: Why does dropping indexes takes such a long time? This is the reply I got when I posted the same message last week. I checked the list for hours and my message didn't show up. This is why I assumed it never made it. I have no idea what this auto-reply is saying. I tried the AltaVista bablefish with Spanish. It was the closest, but it gave me garbled English. - The following text is an automated response to your message - Sres. Debido a que me encuentro de vacaciones les informo que las actividades seguidas por nuestro grupo de trabajo seran atendidas por las siguientes personas: Contacto con clientes externos : Enrique Diaz. Modificacion y control de accesos y permisos a sistemas y bases de datos : Enrique Diaz. Coordinacion de cambios y mantencion de la red : Mauricio Guajardo. Administracion de cambios en servidores de produccion : Mauricio Guajardo. Supervision pauta diaria Operaciones : Enrique Diaz. Administracion de sistema de respaldo : Mauricio Guajardo. Saludos, Alvaro Avello Administrador de Red. Servinco S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How-to copy a column
[snip] Does anyone know the easiest way to copy a column in mysql? I have a table (table1) which has 4 columns, I want to copy all the contents of col1 into col2. Col3 is the primary unique key, so the copy has to keep the data matched with col3. [/snip] UPDATE table1 SET col2=col1; --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL syntax error: help a noob
[snip] My ColdFusion server tells me I have an error in my query syntax, but I can't work out what it is - because I'm working with code that someone very kindly gave me and I only have a vague idea of what the first line's doing! Can anyone see the problem here? SELECT DATEDIFF(leadtime_type, GETDATE(), deadline)'Difference', tasks.leadtime, tasks.lead_time_type_id, leadtime_type.leadtime_type FROM tasks JOIN leadtime_type ON tasks.lead_time_type_id = leadtime_type.leadtime_type_id [/snip] The datediff() function is new to version 4.1. What version of mysql are you running? --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL syntax error: help a noob
I think datediff only takes two arguments and you have three listed. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Chris Kavanagh Sent: Monday, January 31, 2005 5:33 PM To: mysql@lists.mysql.com Subject: Re: SQL syntax error: help a noob Thanks very much for the replies, guys. My version is 4.1.7-max. The error message I get is: -- Error Executing Database Query. Syntax error or access violation: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' deadline)'Difference', tasks.leadtime, tasks.lead_time_type_id, leadtime_' at line 1 -- I changed GETDATE() to CURDATE() but it still gives me the same error. Thanks for the suggestion anyway, Roger. Best regards, CK. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with a query using multiple LEFT JOINS
If you mean that you want to get a row even if tbl2 does not have a matching row for dcode, then move the conditions into the ON clause. Example based off of what you had: SELECT FROM tbl1 as d LEFT JOIN tbl2 as r ON (d.dcode=r.dcode AND r.mcode='AB' AND r.year=2004 AND r.month IN (1,2,3,4,5,6,7,8,9,10,11,12)) LEFT JOIN tbl3 as pc ON (r.code=pc.code AND pc.from_period = 200412 AND pc.to_period 200412) LEFT JOIN tbl4 as st ON st.scode=r.scode WHERE d.status!='X' AND d.region='1A' AND st.group = 'B' GROUP BY d.dcode, r.code You may want to do the same for tbl4 depending on the behavior you are looking for. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Graham Cossey Sent: Monday, January 31, 2005 5:48 PM To: mysql@lists.mysql.com Subject: Help with a query using multiple LEFT JOINS I'm hoping someone can help with a little problem I'm having with a query. In the query below I wish to return as least one row per tbl1, however I am only getting rows where there is at least an entry for tbl2 : SELECT ... FROM tbl1 as d LEFT JOIN tbl2 as r ON d.dcode=r.dcode LEFT JOIN tbl3 as pc ON (r.code=pc.code AND pc.from_period = 200412 AND pc.to_period 200412) LEFT JOIN tbl4 as st ON st.scode=r.scode WHERE r.mcode='AB' AND d.status!='X' AND d.region='1A' AND r.year=2004 AND r.month IN (1,2,3,4,5,6,7,8,9,10,11,12) AND st.group = 'B' GROUP BY d.dcode, r.code Can anyone help me see the light and show me where I'm being stupid? TIA Graham -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: add auto-increment field to fix table with no primary key - h elp
[snip] I have a table with no primary key. I would like to add a new auto-increment column field to each record - that would be the easiest way to remedy this. Eg. 1,2,3,4tagged onto each record successively. [/snip] ALTER TABLE t ADD id INT UNSIGNED NOT NULL auto_increment PRIMARY KEY FIRST; --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ERROR 1006: Can't create database
[snip] How can find the user mysql password? The hosting company which I got the dedicated server package from set that up of course. Should I go to phpAdmin and reset that password so that I know what it is from now on? [/snip] The mysql linux user should not be able to login interactivly, just as they should not have a shell. To do anything as the mysql user, simply become root, then 'su - mysql'. You will not be asked for a password. example: [EMAIL PROTECTED] tom]$ su - Password: [EMAIL PROTECTED] root]# su - mysql -bash-2.05b$ id uid=100(mysql) gid=101(mysql) groups=101(mysql) -bash-2.05b$ --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB recovery
[snip] This box recently crashed as a result of a power outage (possible surge, my surge protector may have failed) The box doesn't boot up, but the HD is ok... all my docs are there.. So, I'm wondering if it's somehow possible to get the MySQL 3 files and recreate them on another box we have here that runs MySQL 4.0. Is this possible? It wouldn't be as simple as getting the files from the dead box and putting them in the new box, would it? [/snip] If these were myisam tables, assuming the files didn't get badly damaged, you should be able to copy the directory for each database you need to recover into the mysql datadir on the new host. You may have to use myisamchk to repair the indexes. http://dev.mysql.com/doc/mysql/en/myisamchk-syntax.html You will want to do all of this with mysqld stopped. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ERROR 1006: Can't create database
[snip] I'm using version 3.23.58. I tried to create a database foo using phpAdmin(logged in as root) and got: ERROR 1006: Can't create database 'foo'. (errno: 13) ... drwx--x--x 2 mysql root 4096 Sep 15 10:34 mysql [/snip] perror 13 Error code 13: Permission denied File permissions look ok at that level, and I would assume that mysql user can get to that directory. You could login to your linux box as root then 'su - mysql' and see if you can create a directory in the mysql datadir as the mysql user. This isn't a grant table issue because I believe that will give you an access denied error. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Server crached problem
[snip] Is my data inside the frm file or in another file? How can I access and retrieve my data? [/snip] The data is not in the frm files. If you are using myisam tables the data is in the myd files and the indexes are in the myi files. Assuming the files didn't get badly damaged, you should be able to copy the directory for each database you need to recover into the mysql datadir on another host or the same host after you get it fixed. You will probably have to use myisamchk to repair the indexes. http://dev.mysql.com/doc/mysql/en/myisamchk-syntax.html You will want to do all of this with mysql stopped. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: list of error codes
[snip] I looked around and didn't see documentation of MySQL error codes. I did find a short list of INNODB codes but nothing comprehensive. Is there such a page? [/snip] You can use perror to find out want a mysql errno means. http://dev.mysql.com/doc/mysql/en/perror.html --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: list of error codes
[snip] I looked around and didn't see documentation of MySQL error codes. I did find a short list of INNODB codes but nothing comprehensive. Is there such a page? [/snip] OS error codes : http://dev.mysql.com/doc/mysql/en/operating-system-error-codes.html Server error messages : http://dev.mysql.com/doc/mysql/en/error-handling.html (this page also tells what files to find these in) --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Access denied for user - I cant work this out
The problem is with your php. Just as a test, print
$db_host,$db_user,$db_pass in your function before you try the connect. My
guess is that they will be blank. The reason for this is that you assign
them outside of the function. This means you either need to pass them to the
function or explicitly state that they are global. Since these are all
blank, it is using the defaults, which on windows are localhost, ODBC, and
no password.
---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa
-Original Message-
From: Christian Biggins
Sent: Wednesday, January 26, 2005 7:30 AM
Subject: RE: Access denied for user - I cant work this out
Hi Peter,
There is a password being suppled - see code (btw, its local testing only,
hence the root user)
//Database Settings
$db_host = 'localhost'; //database hostname $db_name =
'powerpla_powerplay'; //database name $db_user = 'root'; //database USER
name $db_pass = 'rootpass'; // database password
function db_connect()
{
if ($dbc = @mysql_connect($db_host,
$db_user, $db_pass)) {
if
(!mysql_select_db($db_name)) {
die('pCould not connect to the database because: b' . mysql_error() .
'/b/p');
}
} else {
die('pCould not connect to the database because: b' . mysql_error() .
'/b/p');
}
}
-Original Message-
From: Peter Lovatt
Sent: Thursday, 27 January 2005 12:27 AM
To: Christian Biggins; mysql@lists.mysql.com
Subject: RE: Access denied for user - I cant work this out
hi
you are not passing a password to mysql - check your code to see if this is
correct.
Peter
-Original Message-
From: Christian Biggins
Sent: 26 January 2005 12:27
To: mysql@lists.mysql.com
Subject: Access denied for user - I cant work this out
Hi All,
I am consistently getting;
Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)
I am connecting through php with the same script I use all the time, I
have checked it and dbl checked it for problems.
I can connect to mysql in a prompt and I have added new users with all
priv's and connected with them - obviously its more a server issue
than mysql (I think)...
MySQL version is 4.0.21
PHP Version 4.3.8
On a Win2k server using apache 2 (just a local testing server).
Any info would be muchly appreciated.
Christian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to re-use InnoDB tablespace
[snip] I assume that the space of InnoDB free: 201787392 kB was resulted from the dropping of DB_B. Will this chunk be re-used when new data is inserted? [/snip] InnoDB tablespace will not shrink when data is removed. The space the you have from the dropped database will be reused. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Locked myself out of the mysql database!
[snip] 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? [/snip] Stop the mysql serivce, then start it from a command line with the skip-grant-tables option. You can then log in as root with no password and then reset the password. Then stop mysql and restart it normally. In linux: mysqld --skip-grant-tables In windows: mysqld-nt --skip-grant-tables --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: GRANT can't grant with a password?
[snip] It seems that the GRANT syntax should allow the setting of a password upon account creation without requiring access to the mysql db. [/snip] Example: GRANT SELECT ON dbihavegrantprivon.* TO 'root'@'localhost' IDENTIFIED BY 'newpassword'; If you don't have privs to the mysql.user table you definitely should not be able to do that. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Joshua J. Kugler Sent: Tuesday, January 11, 2005 1:09 AM To: mysql@lists.mysql.com Subject: Re: GRANT can't grant with a password? Right, I understand that, but then *why* can a user create another user, with all the priveleges they have, but with now password. That seems like a great security hole. It seems that the GRANT syntax should allow the setting of a password upon account creation without requiring access to the mysql db. j- k- On Saturday 08 January 2005 05:55, Gleb Paharenko said something like: Hello. As said at: http://dev.mysql.com/doc/mysql/en/SET_PASSWORD.html Only clients with access to mysql database can set passwords for other accounts. Joshua J. Kugler [EMAIL PROTECTED] wrote: I've read the sections on GRANT's and permissions, and done some googling, and still haven't found what I'm looking for. I have a user that has USAGE and GRANT global privs and all privs and GRANT on database rubric. However, when they try to run this query: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON rubric.* TO 'user'@'localhost' IDENTIFIED BY 'password'; They get the error ERROR 1044: Access denied for user 'user'@'host' to database 'mysql' They can log in just fine, so it is not a matter of host name. I found a post that seemed to allude to the fact that a user with GRANT could only create a new user via GRANT if there was not IDENTIFIED BY clause. (However, a user with write permissions to the mysql database could). I verified this to be the case when this query, GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON rubric.* TO 'user'@'localhost' run as the user in question, worked and created the user, albeit with no password. Is there a way for a user with GRANT privs to create a user *with* a password? -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext search question
[snip] Given a search string of 'NASD' my default Fulltext search doesn't find it. Wondered why? [/snip] Quote from http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html: MySQL uses a very simple parser to split text into words. A word is any sequence of true word characters (letters, digits, and underscores), optionally separated by no more than one sequential `'` character. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Quick query for multiple fields?
[snip] I have a table with about 50 fields in it, I want to copy certain records into another identical table... i.e INSERT INTO mail_inbox (userid, subject, message, blah blah) (Select userid, subject, message, blah blah... from mail_inboxold where userid = 10) Is there an easy way to do this without having to write all the field names out? [/snip] If the columns are in the same order you can use the following: INSERT INTO mail_inbox (SELECT * FROM mail_inboxold WHERE userid=10); --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: lock the tables
Unless your form is changing the same rows and order of operations is important (which is probably not the case), there is no need to lock the tables. So if each time the form is submitted it inserts a new row, there is no reason to lock the tables. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: leegold Sent: Saturday, January 08, 2005 3:39 AM To: No name Subject: lock the tables I'm going to have a php web form that potentially many users will use to insert into a MYSQL DB, maybe they will try at the same time. Do I have to lock the tables that are being populated? Will a solution queue insert requests or just say I am busy? Thanks, Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: lock the tables
Correct, if the form generates independent insert statements then they will not bump into each other, even with an auto_increment. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: leegold Sent: Saturday, January 08, 2005 4:46 AM To: No name Subject: RE: lock the tables On Sat, 8 Jan 2005 04:06:44 -0600 , Tom Crimmins said: Unless your form is changing the same rows and order of operations is important (which is probably not the case), there is no need to lock the tables. So if each time the form is submitted it inserts a new row, there is no reason to lock the tables. So in MYSQL/PHP different users inserting data into the same tables (w/an autoincremt as the PK) - they will not bump into each other Under normal circumstances. Correct? If they did i will know it very fast:^) --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: leegold Sent: Saturday, January 08, 2005 3:39 AM To: No name Subject: lock the tables I'm going to have a php web form that potentially many users will use to insert into a MYSQL DB, maybe they will try at the same time. Do I have to lock the tables that are being populated? Will a solution queue insert requests or just say I am busy? Thanks, Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why DATETIME takes 8 bytes?
[snip] datetime is displayed as -MM-DD HH:MM:SS - it is *not* stored that way. It is stored as a *nix timestamp - an integer number of seconds since 1970-01-01 00:00:00. [/snip] Actually datetime is not stored as epoch time. It has a range from 1000-01-01 00:00:00 to -12-31 23:59:59 because it is a combination of a date and a time field as Neculai wrote. You may be thinking of a timestamp, which is tored as a 4 byte int. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Frank Bax [mailto:[EMAIL PROTECTED] Sent: Saturday, January 08, 2005 9:04 AM To: mysql@lists.mysql.com Subject: Re: Why DATETIME takes 8 bytes? At 06:00 AM 1/8/05, Ehud Shapira wrote: I don't understand why DATETIME takes 8 bytes. It's just a waste, since DATE+TIME take 6 bytes. And in fact, while DATE and TIME are each DATE+rounded up to bytes on its own, a combined DATETIME should only take 5 bytes: 14 bits for year 04 bits for month 05 bits for day 05 bits for hour 06 bits for minutes 06 bits for seconds --- 40 bits datetime is displayed as -MM-DD HH:MM:SS - it is *not* stored that way. It is stored as a *nix timestamp - an integer number of seconds since 1970-01-01 00:00:00. A 4-byte integer field has historically been used on *nix systems for this purpose, but this has an upper limit of 2038. A larger *nix timestamp is now used to avoid the equivalent of Y2K in 2038. The 8-byte *nix timestamp accommodates micro-seconds. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: creating first table
[snip] How can I list the rows and columns. [/snip] http://dev.mysql.com/doc/mysql/en/SELECT.html --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow queries, need advice on how to improve; key_buffer?
[snip] I tried to improve MySQL speed/performance by adding key_buffer=50M to my my.cnf file for [mysqld]. When I restarted MySQL, I'm getting a 0 DB_ERROR_NOT_CONNECTED error when visiting my php-MySQL web application via http. When I removed above and then restarted MySQL the error goes away. [/snip] You can set it without stopping mysql using the following: 'SET GLOBAL key_buffer_size = [size] * 1024 * 1024' where [size] is the size in Mb you want the key_buffer_size to be. You'll need to add it to your my.cnf if you want this setting to hold on a restart. Try adding 'set-variable = key_buffer = 64M' to your my.cnf. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: BD Sent: Friday, January 07, 2005 9:07 AM To: mysql@lists.mysql.com Subject: Slow queries, need advice on how to improve; key_buffer? Hi, I'm having a problem with slow query and parse times with a MySQL - PHP ecommerce application I am testing out. Also getting an error message with the key_buffer variable. I tried to improve MySQL speed/performance by adding key_buffer=50M to my my.cnf file for [mysqld]. When I restarted MySQL, I'm getting a 0 DB_ERROR_NOT_CONNECTED error when visiting my php-MySQL web application via http. When I removed above and then restarted MySQL the error goes away. I read in my Widenius - MySQL book that I should use the command set_variable=key_buffer=64M (or some other memory limit - I have 1.2 gig of ram on server). I also tried this instead of adding the above to my.cnf. However upon logging on as root in mySQL and running the command, I'm, getting a command not found error. Can anyone tell me what I'm doing wrong with the above two attempts? Or give any advice as to how to improve speed of accessing the mySQL database for my web application? I'm running MySQL 3.23.56 on a linux box. Thanks anyone, BD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow queries, need advice on how to improve; key_buffer?
[snip] I tried to improve MySQL speed/performance by adding key_buffer=50M to my my.cnf file for [mysqld]. When I restarted MySQL, I'm getting a 0 DB_ERROR_NOT_CONNECTED error when visiting my php-MySQL web application via http. When I removed above and then restarted MySQL the error goes away. I'm running MySQL 3.23.56 on a linux box. [/snip] Sorry, I didn't explain that you were probably not able to connect because prior to version 4.1 setting variable in my.cnf require that you add set-variable = before the setting ie. 'set-variable = key_buffer = 64M'. When you restarted mysql it probably exited on error. [snip] OK I tried this, I must be doing something wrong, I entered in: SET GLOBAL key_buffer_size = 64 * 1024 *1024; and got the error: You have an error in your SQL syntax near 'key_buffer_size = 64 * 1024 *1024' at line 1 [/snip] Sorry about this too, in 3.23 leave out the word global. If you can restart without a problem though, I would jest add the 'set-variable = key_buffer = 64M' line to your my.cnf file and restart mysql. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: BD Sent: Friday, January 07, 2005 9:07 AM To: mysql@lists.mysql.com Subject: Slow queries, need advice on how to improve; key_buffer? Hi, I'm having a problem with slow query and parse times with a MySQL - PHP ecommerce application I am testing out. Also getting an error message with the key_buffer variable. I tried to improve MySQL speed/performance by adding key_buffer=50M to my my.cnf file for [mysqld]. When I restarted MySQL, I'm getting a 0 DB_ERROR_NOT_CONNECTED error when visiting my php-MySQL web application via http. When I removed above and then restarted MySQL the error goes away. I read in my Widenius - MySQL book that I should use the command set_variable=key_buffer=64M (or some other memory limit - I have 1.2 gig of ram on server). I also tried this instead of adding the above to my.cnf. However upon logging on as root in mySQL and running the command, I'm, getting a command not found error. Can anyone tell me what I'm doing wrong with the above two attempts? Or give any advice as to how to improve speed of accessing the mySQL database for my web application? I'm running MySQL 3.23.56 on a linux box. Thanks anyone, BD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow queries, need advice on how to improve; key_buffer?
[snip] The problem now is, this did not do anything to improve the query and parse times. I'm testing out an on line store which has about 12,000 product entries in it and about 300 product categories. Both the parse and query times are running over 12 seconds [/snip] This is probably related to not having proper indexes on your tables. If you post the query, and a 'SHOW CREATE TABLE [tablename]' for each table involved, someone maybe able to help you speed it up. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: BD Sent: Friday, January 07, 2005 11:21 AM To: Tom Crimmins Cc: mysql@lists.mysql.com Subject: RE: Slow queries, need advice on how to improve; key_buffer? Hi Tom, OK thanks I just added the set-variable = key_buffer = 64M line to my my.cnf file and at least I got no errors and the MySQL server restarted OK and I got my test site running. The problem now is, this did not do anything to improve the query and parse times. I'm testing out an on line store which has about 12,000 product entries in it and about 300 product categories. Both the parse and query times are running over 12 seconds - that's just to bring up the front page of the store with the category - sub cat list. I'm sure there are lots of other switches in MySQl, do you have any other suggestions as to what I could do to reduce query times down to a reasonable 1-3 seconds? I just bought the book http://www.amazon.com/exec/obidos/tg/detail/-/0596003064/102-0076963-3409775 ?%5Fencoding=UTF8v=glance but it has not arrived yet. Any suggestions in the mean time? BD -Original Message- From: Tom Crimmins Sent: Friday, January 07, 2005 12:08 PM To: BD Cc: mysql@lists.mysql.com Subject: RE: Slow queries, need advice on how to improve; key_buffer? [snip] I tried to improve MySQL speed/performance by adding key_buffer=50M to my my.cnf file for [mysqld]. When I restarted MySQL, I'm getting a 0 DB_ERROR_NOT_CONNECTED error when visiting my php-MySQL web application via http. When I removed above and then restarted MySQL the error goes away. I'm running MySQL 3.23.56 on a linux box. [/snip] Sorry, I didn't explain that you were probably not able to connect because prior to version 4.1 setting variable in my.cnf require that you add set-variable = before the setting ie. 'set-variable = key_buffer = 64M'. When you restarted mysql it probably exited on error. [snip] OK I tried this, I must be doing something wrong, I entered in: SET GLOBAL key_buffer_size = 64 * 1024 *1024; and got the error: You have an error in your SQL syntax near 'key_buffer_size = 64 * 1024 *1024' at line 1 [/snip] Sorry about this too, in 3.23 leave out the word global. If you can restart without a problem though, I would jest add the 'set-variable = key_buffer = 64M' line to your my.cnf file and restart mysql. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: BD Sent: Friday, January 07, 2005 9:07 AM To: mysql@lists.mysql.com Subject: Slow queries, need advice on how to improve; key_buffer? Hi, I'm having a problem with slow query and parse times with a MySQL - PHP ecommerce application I am testing out. Also getting an error message with the key_buffer variable. I tried to improve MySQL speed/performance by adding key_buffer=50M to my my.cnf file for [mysqld]. When I restarted MySQL, I'm getting a 0 DB_ERROR_NOT_CONNECTED error when visiting my php-MySQL web application via http. When I removed above and then restarted MySQL the error goes away. I read in my Widenius - MySQL book that I should use the command set_variable=key_buffer=64M (or some other memory limit - I have 1.2 gig of ram on server). I also tried this instead of adding the above to my.cnf. However upon logging on as root in mySQL and running the command, I'm, getting a command not found error. Can anyone tell me what I'm doing wrong with the above two attempts? Or give any advice as to how to improve speed of accessing the mySQL database for my web application? I'm running MySQL 3.23.56 on a linux box. Thanks anyone, BD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow queries, need advice on how to improve; key_buffer - zen -cart?
[snip] The application I am using for the site is www.zen-cart.com so I'm not sure I can do anything about changing the table indexes because it is a pre written php-MySQL open source freeware script. I'm getting the query times directly from the application and not MySQL. [/snip] You could turn on logging in mysql to see what the query is that is taking so long, then make sure the tables are properly indexed based on this. I would hope that this software has properly indexed the tables, but you can verify this on your own. Add the following to your my.cnf to enable the logging of slow queries. [mysqld] set-variable = long_query_time=2 log-long-format log-slow-queries = /var/log/mysqld.slow.log (or whatever file you want, just make sure the user mysqld is running as has write permissions to it.) --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: BD Sent: Friday, January 07, 2005 11:58 AM To: Tom Crimmins Cc: mysql@lists.mysql.com Subject: RE: Slow queries, need advice on how to improve; key_buffer - zen-cart? OK thanks Tom, The application I am using for the site is www.zen-cart.com so I'm not sure I can do anything about changing the table indexes because it is a pre written php-MySQL open source freeware script. I'm getting the query times directly from the application and not MySQL. However I could post a query I generate directly from MySQL, how could I do that? What would be the command I should use to generate query times from MySQL? For the SHOW CREATE TABLE, I have posted below the five tables that (I believe) are directly involved in generating a product and category list on the front page of my test store. I did not post all tables in the database because there are 97 tables total in the database, but I think these are the pertinent tables involved in slow query times; any suggestions that I get here I will definitely pass along to the zen cart developers. If there is something that I can do without changing the PHP code of the application and fix just with MySQL settings that would be great... zen_products |CREATE TABLE `zen_products` ( `products_id` int(11) NOT NULL auto_increment, `products_type` int(11) NOT NULL default '1', `products_quantity` float NOT NULL default '0', `products_model` varchar(32) default NULL, `products_image` varchar(64) default NULL, `products_price` decimal(15,4) NOT NULL default '0.', `products_virtual` tinyint(1) NOT NULL default '0', `products_date_added` datetime NOT NULL default '0001-01-01 00:00:00', `products_last_modified` datetime default NULL, `products_date_available` datetime default NULL, `products_weight` decimal(5,2) NOT NULL default '0.00', `products_status` tinyint(1) NOT NULL default '0', `products_tax_class_id` int(11) NOT NULL default '0', `manufacturers_id` int(11) default NULL, `products_ordered` float NOT NULL default '0', `products_quantity_order_min` float NOT NULL default '1', `products_quantity_order_units` float NOT NULL default '1', `products_priced_by_attribute` tinyint(1) NOT NULL default '0', `product_is_free` tinyint(1) NOT NULL default '0', `product_is_call` tinyint(1) NOT NULL default '0', `products_quantity_mixed` tinyint(1) NOT NULL default '0', `product_is_always_free_shipping` tinyint(1) NOT NULL default '0', `products_qty_box_status` tinyint(1) NOT NULL default '1', `products_quantity_order_max` float NOT NULL default '0', `products_sort_order` int(11) NOT NULL default '0', `products_discount_type` tinyint(1) NOT NULL default '0', `products_discount_type_from` tinyint(1) NOT NULL default '0', `products_price_sorter` decimal(15,4) NOT NULL default '0.', `master_categories_id` int(11) NOT NULL default '0', `products_mixed_discount_quantity` tinyint(1) NOT NULL default '1', PRIMARY KEY (`products_id`), KEY `idx_products_date_added` (`products_date_added`) ) TYPE=MyISAM | | zen_categories_description | CREATE TABLE `zen_categories_description` | ( `categories_id` int(11) NOT NULL default '0', `language_id` int(11) NOT NULL default '1', `categories_name` varchar(32) NOT NULL default '', `categories_description` text NOT NULL, PRIMARY KEY (`categories_id`,`language_id`), KEY `idx_categories_name` (`categories_name`) ) TYPE=MyISAM | zen_categories | CREATE TABLE `zen_categories` ( `categories_id` int(11) NOT NULL auto_increment, `categories_image` varchar(64) default NULL, `parent_id` int(11) NOT NULL default '0', `sort_order` int(3) default NULL, `date_added` datetime default NULL, `last_modified` datetime default NULL, `categories_status` tinyint(1) NOT NULL default '1', PRIMARY KEY (`categories_id`), KEY `idx_categories_parent_id` (`parent_id`), KEY `idx_sort_order` (`sort_order`) ) TYPE=MyISAM | | zen_products_description | CREATE TABLE `zen_products_description` ( `products_id` int(11) NOT NULL auto_increment, `language_id` int(11) NOT NULL default '1
RE: Slow queries, need advice on how to improve; key_buffer - zen -cart?
[snip] # Time: 050107 17:40:41 # [EMAIL PROTECTED]: zencarttest[zencarttest] @ [xxx.xxx.xxx.xxx] # Query_time: 13 Lock_time: 0 Rows_sent: 148 Rows_examined: 1567270 use zencarttest; select distinct m.manufacturers_id, m.manufacturers_name from zen_manufacturers m left join zen_products p on m.manufacturers_id = p.manufacturers_id where m.manufacturers_id = p.manufacturers_id and p.products_status= '1' order by manufacturers_name; [/snip] This appears to be the problem query. Looks like zen_products could use an index on (manufacturers_id, products_status), and zen_manufacturers could use an index on (manufacturers_id,manufacturers_name). You can try to add these indexes and run the query to see if it helps. You may want to do an EXPLAIN after adding the indexes to make see if it is using them. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: first time accessing
[snip] DBI connect'test:GJWPP88','Jerry' failed: Access denied for user 'Jerry'@'gjwpp88' using password: yes at line 17 [/snip] I would try your connection from a command line ie. 'mysql -u Jerry -h GJWPP88 -p'. The problem is now not with your perl, it is your mysql user permissions. From your error, I see that mysqld is running on your local machine. In this case you should use localhost instead of the actual hostname ie. 'mysql -u Jerry -h localhost -p' (you can omit the host on this one, it is default). If this works, you can change the host in your perl script to localhost, and you should be ready to go. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Gerald Preston Sent: Thursday, January 06, 2005 5:25 AM To: 'Tom Crimmins' Cc: mysql@lists.mysql.com Subject: RE: first time accessing Tom, I tried the following: #!/perl use warnings; use strict; use DBI; # my $dbh=DBI-connect( 'DBI:mysql:test:GJWPP88', 'Jerry', 'password' ) or die Cannot connect - gjwpp88!!br$DBI::errstr; #Local Host Name - GJWPP88 #Local User Name - Jerry #database under GJWPP88 - mysql and test my $driver = mysql; my $server = GJWPP88; my $database = test; my $url = DBI:$driver:$database:$server; my $user = Jerry; my $pass = password; my $dbh = DBI-connect($url, $user, $pass) || die Couldn't connect to database: . DBI-errstr; And I get the following: DBI connect'test:GJWPP88','Jerry' failed: Access denied for user 'Jerry'@'gjwpp88' using password: yes at line 17 All I am wanting to do is connect and create a new table. Any ideas? Thanks, Jerry -Original Message- From: Tom Crimmins Sent: Monday, January 03, 2005 3:52 PM To: Gerald Preston Cc: mysql@lists.mysql.com Subject: RE: first time accessing Try something like this: use DBI; my $driver = mysql; my $server = myhost; my $database = mydb; my $url = DBI:$driver:$database:$server; my $user = username; my $pass = password; my $dbh = DBI-connect($url, $user, $pass) || die Couldn't connect to database: . DBI-errstr; Obviously you don't have to make everything a variable, this is just one possibility. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Gerald Preston Sent: Monday, January 03, 2005 7:40 PM To: mysql users Subject: first time accessing I am making my first attempt to access MySQL with Perl #!/perl use warnings; use strict; use dbi; my $dbh=DBI-connect( 'dbi:MySQL, 'user', 'pass' ) or die Cannot connect - !br$DBI::errstr; and I get the following error: Can't connect to data source dbi:MySQL, no data driver specified and DBS_DSN env var not set Any ideas? Thanks, Jerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Copying table to another server.
It should just exit unless you are using -p with mysql. In that case, if mysqldump got an access denied, nothing would output to stdout and mysql would be left waiting for a password. I would try the following: mysqladmin -h 'other_hostname' create db_name mysqldump --opt db_name dump.sql mysql -h 'other_hostname' -D db_name dump.sql Running the commands separately will allow you to see what is going on more easily. You may need to specify a username and a password with each command i.e. 'mysql -u user -p'. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Andrew Mull [mailto:[EMAIL PROTECTED] Sent: Thursday, January 06, 2005 11:17 AM To: mysql@lists.mysql.com Subject: Copying table to another server. Hello, I'm having a problem with moving a database from one server to another using mysqldump. Since the servers are different platforms, some ascii characters are getting corrupted. So, my thought was to copy the table from one database to the other via the mysql interface. I found this online at mysql: mysqladmin -h 'other_hostname' create db_name mysqldump --opt db_name | mysql -h 'other_hostname' db_name However, all it does is hang...so I'm guessing its a permission problem? Any suggestion to how to setup this up so it works or is there an easier way? Thanks! -Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: first time accessing
If you are using a fresh install of mysql, you need to connect with 'mysql -u root' from the local machine and then configure access for other users with the GRANT command. See http://dev.mysql.com/doc/mysql/en/GRANT.html. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Gerald Preston [mailto:[EMAIL PROTECTED] Sent: Thursday, January 06, 2005 3:08 PM To: 'Tom Crimmins' Cc: mysql@lists.mysql.com Subject: RE: first time accessing Tom, I still get access denied. I just reinstall the latest version yesterday. What are my options now? Thanks, Jerry -Original Message- From: Tom Crimmins [mailto:[EMAIL PROTECTED] Sent: Thursday, January 06, 2005 6:28 AM To: Gerald Preston Cc: mysql@lists.mysql.com Subject: RE: first time accessing [snip] DBI connect'test:GJWPP88','Jerry' failed: Access denied for user 'Jerry'@'gjwpp88' using password: yes at line 17 [/snip] I would try your connection from a command line ie. 'mysql -u Jerry -h GJWPP88 -p'. The problem is now not with your perl, it is your mysql user permissions. From your error, I see that mysqld is running on your local machine. In this case you should use localhost instead of the actual hostname ie. 'mysql -u Jerry -h localhost -p' (you can omit the host on this one, it is default). If this works, you can change the host in your perl script to localhost, and you should be ready to go. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Gerald Preston Sent: Thursday, January 06, 2005 5:25 AM To: 'Tom Crimmins' Cc: mysql@lists.mysql.com Subject: RE: first time accessing Tom, I tried the following: #!/perl use warnings; use strict; use DBI; # my $dbh=DBI-connect( 'DBI:mysql:test:GJWPP88', 'Jerry', 'password' ) or die Cannot connect - gjwpp88!!br$DBI::errstr; #Local Host Name - GJWPP88 #Local User Name - Jerry #database under GJWPP88 - mysql and test my $driver = mysql; my $server = GJWPP88; my $database = test; my $url = DBI:$driver:$database:$server; my $user = Jerry; my $pass = password; my $dbh = DBI-connect($url, $user, $pass) || die Couldn't connect to database: . DBI-errstr; And I get the following: DBI connect'test:GJWPP88','Jerry' failed: Access denied for user 'Jerry'@'gjwpp88' using password: yes at line 17 All I am wanting to do is connect and create a new table. Any ideas? Thanks, Jerry -Original Message- From: Tom Crimmins Sent: Monday, January 03, 2005 3:52 PM To: Gerald Preston Cc: mysql@lists.mysql.com Subject: RE: first time accessing Try something like this: use DBI; my $driver = mysql; my $server = myhost; my $database = mydb; my $url = DBI:$driver:$database:$server; my $user = username; my $pass = password; my $dbh = DBI-connect($url, $user, $pass) || die Couldn't connect to database: . DBI-errstr; Obviously you don't have to make everything a variable, this is just one possibility. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Gerald Preston Sent: Monday, January 03, 2005 7:40 PM To: mysql users Subject: first time accessing I am making my first attempt to access MySQL with Perl #!/perl use warnings; use strict; use dbi; my $dbh=DBI-connect( 'dbi:MySQL, 'user', 'pass' ) or die Cannot connect - !br$DBI::errstr; and I get the following error: Can't connect to data source dbi:MySQL, no data driver specified and DBS_DSN env var not set Any ideas? Thanks, Jerry -- 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]
RE: Cannot create Windows service for MySql. Error: 0
I had this problem once. My fix was to delete the innodb files from the mysql datadir (log and data files), then run mysqld-nt from a command prompt. After it starts normally, you can stop it and then install it as a service. I don't know if this will work for you, but it did for me. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Eugenia Mariani [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 05, 2005 11:57 AM To: mysql@lists.mysql.com Subject: Cannot create Windows service for MySql. Error: 0 My O.S in Win Xp Pro SP2. My web server is Apache 5.0 While I am trying to install MySql Server 4.1, I have the following error and I cannot install the Server: Cannot create Windows service for MySql. Error: 0 Can someone help me to install without error? Thanks Eugenia Mariani -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyODBC 3.5.9 and MySQL 4.1.8
Try MyODBC 3.51.10. It supports 4.1 auth. Here is a link to a mirror that has it: http://mysql.netvisao.pt/downloads/connector/odbc/3.51.html --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: nikos Sent: Wednesday, January 05, 2005 3:09 AM To: mysql@lists.mysql.com Subject: MyODBC 3.5.9 and MySQL 4.1.8 Hello list and happy new year. Recently I've install mysql 4.1 on win 2000 with IIS 5 and works perfect. My problem is that when i'm trying to make a connection with myodbc (latest release) as localhost I got the following message: Client does not support authentication protocol requestet by server. Consider upgrading mysql client. MyODBC whorks fine because I 've allready make connection throw lan on a Linux RH-9 with apache and mysql 4.0.22 Any suggestions? Thanky you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Grant question
[snip] Is it possible to grant all these databases in just one GRANT instruction such as: GRANT SELECT,INSERT,UPDATE,DELETE ON dbexample*.* TO 'user'@'localhost' BY 'password'; [/snip] GRANT [privs] ON `dbexample%`.* TO 'user'@'localhost' IDENTIFIED BY 'password'; --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Andre Matos [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 04, 2005 4:26 PM To: mysql@lists.mysql.com Subject: Grant question Hi list, I have many databases on my MySQL server such as: dbexample dbexample_clients_abc dbexample_clients_def dbexample_clients_ghi dbexample_local_abc dbexample_local_def dbexample_local_ghi Is it possible to grant all these databases in just one GRANT instruction such as: GRANT SELECT,INSERT,UPDATE,DELETE ON dbexample*.* TO 'user'@'localhost' BY 'password'; I tried this instruction but did not work. Is someone knows how to do if it is possible? Thanks for any help. Andre -- Andre Matos [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]
RE: MyODBC 3.51.10
4.1 auth is not supported until MyODBC 3.51.10. I don't know why the win binaries are not on the download page anymore, though the source is there. Here is a mirror with the windows binaries. http://mysql.netvisao.pt/Downloads/MyODBC3/MyODBC-3.51.10-x86-win-32bit.exe --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Erich Beyrent Sent: Tuesday, January 04, 2005 2:53 PM To: 'J.R. Bullington';Subject: RE: MyODBC 3.51.10 You need to set the OLD_PASSWORDS variable in the [MYSQLD] section of the my.ini file. Set-variable = old_passwords=1 It's not the ODBC, but your version of MySQL. 4.0.x uses 1 variant of password hashing, 4.1.x uses a more secure one that will be covered in MyODBC 3.53 (coming soon...). J.R. I did read that in the manual, but I was confused as to why I get the error on one pc and not the other. I am also somewhat confused as to why I have 3.51.10 on one pc, but cannot find it anymore for my other. Did it get pulled from the website? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: distinctSelection(veryUrgent)
SELECT s.* FROM Second s LEFT JOIN First f USING (Flower,Color) WHERE f.Flower IS NULL; --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: N. Kavithashree Sent: Tuesday, January 04, 2005 11:17 PM To: mysql@lists.mysql.com Subject: distinctSelection(veryUrgent) hello, i m using mysql 4.0.18-standard version it is not accepting subqurries. problem is: i have 2 tables: First(id,Flower,Color); = color is char(2); Second(Shop,Date,Flower,Color); first table will contain only unique flowers , and colors, id is autoincrement. second table may contain duplicate entries. i want to select from Second table only those Flowers and Colors which are not there in First table. i tried with : select distinct(First.Flower),First.color from First,Second where First.color!=Second.color; but it will select all fileds in first. As this version of mysql will not support for subqurries...problem. can anybody reply with answer? === kavi === -- 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]
RE: MySQL Load on server
If I understand correctly, this is what you want: SHOW PROCESSLIST --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Sheni R. Meledath [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 04, 2005 11:40 PM To: MySQL Masters Subject: MySQL Load on server Hello: We have multiple web sites hosted on a virtual web server. Many of the web sites are using MySQL databases. Recently we are having problems with the CPU load due to mysql processes. We are not able to track the corrupted database or script (PHP). There are many databases on the server. CPU Load on server CPU states: % user, % nice, % system, % interrupt, % idle Mem: 654M Active, 82M Inact, 142M Wired, 37M Cache, 112M Buf, 90M Free Swap: 1024M Total, 226M Used, 798M Free, 22% Inuse PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND 28181 cyber001 50 0 32088K 7180K RUN 282:01 71.29% 71.29% mysqld Is there any way to track the processes on the MySQL databases? Please suggest a solution to fix this problem. Thank You. Regards Sheni R Meledath [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Load on server
In you're my.cnf file (in /etc by default on a linux box) add under [mysqld] the option log for text logging. for example [mysqld] log=mylogfile Whatever user mysqld is running as must have write perms to the log file. You can then anaylze this as it will contain connections and queries. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Sheni R. Meledath Sent: Wednesday, January 05, 2005 12:18 AM To: Tom Crimmins Cc: mysql@lists.mysql.com Subject: RE: MySQL Load on server Dear Tom, Thank you very much. Is there a way to log all the processes on the MySQL server to analyze later. At 09:48 AM 1/5/2005, Tom Crimmins wrote: If I understand correctly, this is what you want: SHOW PROCESSLIST --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Sheni R. Meledath Sent: Tuesday, January 04, 2005 11:40 PM To: MySQL Masters Subject: MySQL Load on server Hello: We have multiple web sites hosted on a virtual web server. Many of the web sites are using MySQL databases. Recently we are having problems with the CPU load due to mysql processes. We are not able to track the corrupted database or script (PHP). There are many databases on the server. CPU Load on server CPU states: % user, % nice, % system, % interrupt, % idle Mem: 654M Active, 82M Inact, 142M Wired, 37M Cache, 112M Buf, 90M Free Swap: 1024M Total, 226M Used, 798M Free, 22% Inuse PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND 28181 cyber001 50 0 32088K 7180K RUN 282:01 71.29% 71.29% mysqld Is there any way to track the processes on the MySQL databases? Please suggest a solution to fix this problem. Thank You. Regards Sheni R Meledath [EMAIL PROTECTED] Regards Sheni R Meledath [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup/restore
Drop the tables manually then try the restore. [snip] ERROR 1051 at line 11: Unknown table 'alarm' ERROR 1050 at line 12: Table 'alarm' already exists [/snip] It seems strange that you get these errors in this order. If you post maybe the first 15 lines of your dump file, I may be able to provide more help. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- Hi, Thank you for your reply. Even though I used the --add-drop-table option, I am getting the error messages. Actually I am taking the backup for a group of tables as following D:\Code\OPS\6.9.1.12\NetMgmt\OPS_src\C_src\backupmnms\Debugmysqldump -uMNMSDBA -p MNMSDBA --databases mnms --add-drop-table --add-locks --disable-keys -f --tables ALARM ALARM_CATEGORY ALARM_REPORT ALARM_REPORT_CATEGORY ALARMINFOc:\progra~1\mitel\opsman~1\temp\almhist.dmp And doing the restoring as D:\Code\OPS\6.9.1.12\NetMgmt\OPS_src\C_src\backupmnms\Debugmysql -uMNMSDBA -pMNMS DBA -f mnmsc:\progra~1\mitel\opsman~1\temp\almhist.dmp ERROR 1051 at line 11: Unknown table 'alarm'// Trying to drop alarm table ERROR 1050 at line 12: Table 'alarm' already exists //Creating alarm ERROR 1051 at line 72: Unknown table 'alarm_report' //Drop alarm_category ERROR 1050 at line 73: Table 'alarm_report' already exists//Create ERROR 1062 at line 94: Duplicate entry '1' for key 1 // Insert ERROR 1062 at line 95: Duplicate entry '4' for key 1 // Insert ERROR 1062 at line 96: Duplicate entry '5' for key 1 // Insert ERROR 1062 at line 97: Duplicate entry '8' for key 1 // Insert ERROR 1062 at line 98: Duplicate entry '10' for key 1 // Insert ERROR 1062 at line 99: Duplicate entry '11' for key 1 // Insert ERROR 1062 at line 100: Duplicate entry '13' for key 1 // Insert ERROR 1062 at line 101: Duplicate entry '15' for key 1 // Insert ERROR 1062 at line 102: Duplicate entry '16' for key 1 // Insert ERROR 1062 at line 103: Duplicate entry '17' for key 1 // Insert Note : ALARM, ALARM_CATEGORY, ALARM_REPORT, ALARM_REPORT_CATEGORY, ALARMINFO all belongs to one group. Alarm and Alarm_category are parent and child tables. ALARM_REPORT, ALARM_REPORT_CATEGORY are parent and child tables Please advise me, how to get rid of the above error messages. Thanks, Narasimha -Original Message- Hi, with which options of mysqldump you have taken backup. if you use --add-drop-table option then it will add drop table statement in dump file. otherwise it wont add that statement and you will get that type of errors. if you didn't use that option then drop the schema and then try to restore it from backup file. Thanks Anil -Original Message- Hi, Thank you for your reply. If I have the create table info in my dump file, while doing the restore using mysql dbname dump.dmp, I am getting errors like mysql -uMNMSDBA -pMNMSDBA -f mnms c:\progra~1\mitel\opsman~1\temp\almhist.dmp ERROR 1050 at line 11: Table 'alarm' already exists ERROR 1050 at line 40: Table 'alarm_category' already exists ERROR 1050 at line 70: Table 'alarm_report' already exists ERROR 1062 at line 91: Duplicate entry '1' for key 1 ERROR 1062 at line 92: Duplicate entry '4' for key 1 ERROR 1062 at line 93: Duplicate entry '5' for key 1 ERROR 1062 at line 94: Duplicate entry '8' for key 1 ERROR 1062 at line 95: Duplicate entry '10' for key 1 ERROR 1062 at line 96: Duplicate entry '11' for key 1 ERROR 1062 at line 97: Duplicate entry '13' for key 1 ERROR 1062 at line 98: Duplicate entry '15' for key 1 ERROR 1062 at line 99: Duplicate entry '16' for key 1 ERROR 1062 at line 100: Duplicate entry '17' for key 1 ERROR 1050 at line 108: Table 'alarm_report_category' already exists ERROR 1050 at line 137: Table 'alarminfo' already exists ERROR 1062 at line 155: Duplicate entry '56' for key 1 ERROR 1062 at line 156: Duplicate entry '57' for key 1 ERROR 1062 at line 157: Duplicate entry '58' for key 1 ERROR 1062 at line 158: Duplicate entry '59' for key 1 ERROR 1062 at line 159: Duplicate entry '75' for key 1 ERROR 1062 at line 160: Duplicate entry '76' for key 1 ERROR 1062 at line 161: Duplicate entry '77' for key 1 Please advise me, how to suppress the above error messages. While taking backup used mysqldump -uMNMSDBA -pMNMSDBA --databases mnms --add-locks --disable-keys -f --tables ALARM ALARM_CATEGORY ALARM_REPORT ALARM_REPORT_CATEGORY ALARMINFO c:\progra~1\mitel\opsman~1\temp\almhist.dmp Please help me in this. Thanks, Narasimha -Original Message- From: Tom Crimmins [mailto:[EMAIL PROTECTED] Sent: Monday, January 03, 2005 10:55 AM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) Cc: mysql@lists.mysql.com Subject: RE: backup/restore [snip] I am doing backup for tables using Mysqldump. But while doing the restore I am not able to do that using the same Mysqldump. Could you please help me in that. [/snip] mysqldump is not intended to be used for the restore. You need to run the following: mysql -D dbname mysqldumpfile You may
RE: changing data dir
[snip] Do I have to reinstall mysql in order to change the default data dir from /var/lib/mysql? Can I do this: mv /var/lib/mysql /newdir/mysql cd /var/lib ln -s /newdir/mysql mysql [/snip] This should work. You could also change the datadir variable in your my.cnf file instead of creating the link. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: doing SELECTS and keeping the array intact with php
http://us2.php.net/manual/en/function.mysql-data-seek.php
This lets you set the internal row pointer of the mysql result. In your case
you would want mysql_data_seek($result,0).
---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa
-Original Message-
From: julian haffegee
Sent: Monday, January 03, 2005 12:31 PM
To: MySQL General List
Subject: doing SELECTS and keeping the array intact with php
Hi all,
this has been bugging me for some time now.
I want to do a mysql select
then using php
while ($a_row = mysql_fetch_array($result))
{
do something to get a small portion of the data }
and that works fine. Then later I need to access $result as a complete array
again. Is there a way I can keep $result array intact and still take a
couple values out beforehand. I can get it to work, by doing the select
twice, but thats less than ideal.
Thanks enormously if you can help
Jules
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
