Re: sql to duplicate records with modified value
Hi Voytek You could try some variation of: INSERT INTO inserttable (user, maildir) SELECT REPLACE(user, '@', 'spam@') as user, CONCAT(maildir,'.spam/') as maildir FROM selecttable [WHERE ..] the where bit is optional of course! let me know how you go - hope you are keeping well! ray At 03:17 PM 27/03/2010, Voytek Eymont wrote: I have Postfix virtual mailboxes in MySQL table like below: I'd like to duplicate all records whilst MODIFYING two fields like so: current record has format like: user 'usern...@domain.tld' maildir 'domain.tld/usern...@domain.tld/' add new record that has: user 'username+s...@domain.tld' maildir 'domain.tld/usern...@domain.tld/.spam/' so that I'll end up with two record, existing, plus new one field 'user' - insert '+spam' ahead of '@' field 'maildir' append '.spam/' what's the best way mysql Ver 14.7 Distrib 4.1.22, for pc-linux-gnu (i686) using readline 4.3 Server version: 4.1.22-standard mysql show tables; +---+ | Tables_in_postfix | +---+ | admin | | alias | | config| | domain| | domain_admins | | fetchmail | | log | | mailbox | | vacation | | vacation_notification | +---+ 10 rows in set (0.00 sec) -- Voytek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=st...@tweek.com.au ( T W E E K ! ) PO Box 15 Wentworth Falls NSW Australia 2782 | p:+61 2 4702 6377 (Sydney/Penrith/Blue Mountains) | p:+61 2 4915 8532 (Newcastle/Hunter) | f:+61 2 8456 5743 | m:0414 270 400 | e:ray at tweek dot com dot au | w:www dot tweek dot com dot au 'What is more immoral than war?' - Marquis de Sade
insert random number into table
Hello All, I am trying to insert a unique random number* with each row when inserting into a table. I can do it in the application layer (php) but just wondering if there is an easier way in the database layer. Thanks Ray * for example, when registering a person for a contest, I want to give them a unique, random identifier. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: insert random number into table
On October 16, 2009 10:57:48 am Jerry Schwartz wrote: There is a RAND function in MySQL, but if you need to guarantee that the identifiers are unique you should consider using an auto-increment field. If that isn't unique enough, you can use the UUID or UUID_SHORT functions. Theoretically, those should return values that are unique across the Internet (at least). Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -Original Message- From: Ray [mailto:r...@stilltech.net] Sent: Friday, October 16, 2009 11:43 AM To: mysql@lists.mysql.com Subject: insert random number into table Hello All, I am trying to insert a unique random number* with each row when inserting into a table. I can do it in the application layer (php) but just wondering if there is an easier way in the database layer. Thanks Ray * for example, when registering a person for a contest, I want to give them a unique, random identifier. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com Thanks for the replies. I think I'll use the php solution as it is the better match for what I am trying to do. I'l keep the UUID functions in mind for future use though. Ray -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: insert random number into table
On October 16, 2009 12:29:42 pm Jerry Schwartz wrote: -Original Message- From: Ray [mailto:r...@stilltech.net] Sent: Friday, October 16, 2009 1:10 PM To: mysql@lists.mysql.com Subject: Re: insert random number into table On October 16, 2009 10:57:48 am Jerry Schwartz wrote: There is a RAND function in MySQL, but if you need to guarantee that the identifiers are unique you should consider using an auto-increment field. If that isn't unique enough, you can use the UUID or UUID_SHORT functions. Theoretically, those should return values that are unique across the Internet (at least). Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -Original Message- From: Ray [mailto:r...@stilltech.net] Sent: Friday, October 16, 2009 11:43 AM To: mysql@lists.mysql.com Subject: insert random number into table Hello All, I am trying to insert a unique random number* with each row when inserting into a table. I can do it in the application layer (php) but just wondering if there is an easier way in the database layer. Thanks Ray * for example, when registering a person for a contest, I want to give them a unique, random identifier. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com Thanks for the replies. I think I'll use the php solution as it is the better match for what I am trying to do. I'l keep the UUID functions in mind for future use though. Ray [JS] Just remember that the pseudo-random numbers that come out of a computer, regardless of where or how they are generated, are not guaranteed to be unique. Although the odds of a collision are slim, my philosophy has always been if it's possible, it will happen - in the middle of your vacation. If you want to generate UUIDs in PHP, take a look at http://www.shapeshifter.se/2008/09/29/uuid-generator-for-php/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com Thanks for all the suggestions and points. There are definitely some I will keep in mind for future projects. auto increment won't work because I don't want the number to be based on the order of assignment. I probably should have been more clear. What I am trying to do is randomly assign one of a fixed pool of numbers uniquely to each row. What I ended up doing is (in PHP): SELECT all used numbers and put them in an array. create an array of all allowed numbers. combine the two arrays such that all available numbers are left in a third array with index numbers from 1 counting up sequentially. I then randomly choose one of those numbers and write it to the table. Thanks again for all your suggestions. Ray -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL GUI Tools
On July 13, 2009 12:03:49 pm Carlos Williams wrote: If I am looking for a application that will connect to MySQL and allow me to make database / table / user / permission modifications via a graphical tool, is there something specific out there you guys recommend? I have been doing everything via CLI only and would like to try a GUI option if available. Anyone know if this exist for Linux? PhpMyAdmin http://www.phpmyadmin.net/home_page/index.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Question about query - can this be done?
Hello, I've tried the manual and google, but I am not even sure what to call what I want to do. simplified data example: I have a table of start and end times for an event, and an id for that event in a table. each event may occur multiple times, but never more than 5 times and rarely more than 3. I want a query that will provide one record per event with all times included. feel free to answer RTFM or STFW as long as you provide the manual section or key words. ;) Thanks, Ray chart form follows: id | event_id | start | end --- 1 |4 | t1| t2 2 |4 | t3| t4 3 |4 | t5| t6 4 |5 | t1| t2 5 |5 | t3| t4 becomes id | event_id | start | end | start | end | start | end --- ? | 4| t1| t2 | t3| t4| t5| t6 ? | 5| t1| t2 | t3| t4 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Question about query - can this be done?
On June 2, 2009 10:44:48 am Peter Brawley wrote: Ray, I want a query that will provide one record per event with all times included. feel free to answer RTFM or STFW as long as you provide the manual section or key words. ;) Can be done with a pivot table. Examples under Pivot tables at http://www.artfulsoftware.com/infotree/queries.php. If you get stuck, pipe up. PB Thanks Peter and Brent. GROUP_CONCAT does exactly what I want. Brent, you're right, I don't really want to break up the times into separate fields that bad, the results are going into PHP so I can parse the combined fields there without much difficulty. The next problem is how do I use the results in a join. My first thought (that doesn't work) was: SELECT event_id, GROUP_CONCAT( CONCAT(start, '-', end) ) start_end FROM events GROUP BY event_id JOIN event_details WHERE events.event_id=event_details.event_id I have tried brackets, and a few other things, but I haven't got it yet. Thanks, Ray - Ray wrote: Hello, I've tried the manual and google, but I am not even sure what to call what I want to do. simplified data example: I have a table of start and end times for an event, and an id for that event in a table. each event may occur multiple times, but never more than 5 times and rarely more than 3. I want a query that will provide one record per event with all times included. feel free to answer RTFM or STFW as long as you provide the manual section or key words. ;) Thanks, Ray chart form follows: id | event_id | start | end --- 1 |4 | t1| t2 2 |4 | t3| t4 3 |4 | t5| t6 4 |5 | t1| t2 5 |5 | t3| t4 becomes id | event_id | start | end | start | end | start | end - -- ? | 4| t1| t2 | t3| t4| t5| t6 ? | 5| t1| t2 | t3| t4 No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.339 / Virus Database: 270.12.50/2150 - Release Date: 06/02/09 06:47:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Question about query - can this be done?
On June 2, 2009 03:14:36 pm Ray wrote: On June 2, 2009 10:44:48 am Peter Brawley wrote: Ray, I want a query that will provide one record per event with all times included. feel free to answer RTFM or STFW as long as you provide the manual section or key words. ;) Can be done with a pivot table. Examples under Pivot tables at http://www.artfulsoftware.com/infotree/queries.php. If you get stuck, pipe up. PB Thanks Peter and Brent. GROUP_CONCAT does exactly what I want. Brent, you're right, I don't really want to break up the times into separate fields that bad, the results are going into PHP so I can parse the combined fields there without much difficulty. The next problem is how do I use the results in a join. My first thought (that doesn't work) was: SELECT event_id, GROUP_CONCAT( CONCAT(start, '-', end) ) start_end FROM events GROUP BY event_id JOIN event_details WHERE not sure where this typo came from I meant ON events.event_id=event_details.event_id I have tried brackets, and a few other things, but I haven't got it yet. Thanks, Ray I found a solution, but not sure if it's a good idea. CREATE OR REPLACE VIEW v AS SELECT event_id, GROUP_CONCAT( CONCAT(start, '-', end) ) start_end FROM events GROUP BY event_id; SELECT * FROM event_details JOIN v ON events.event_id=event_details.event_id Thanks, Ray - Ray wrote: Hello, I've tried the manual and google, but I am not even sure what to call what I want to do. simplified data example: I have a table of start and end times for an event, and an id for that event in a table. each event may occur multiple times, but never more than 5 times and rarely more than 3. I want a query that will provide one record per event with all times included. feel free to answer RTFM or STFW as long as you provide the manual section or key words. ;) Thanks, Ray chart form follows: id | event_id | start | end --- 1 |4 | t1| t2 2 |4 | t3| t4 3 |4 | t5| t6 4 |5 | t1| t2 5 |5 | t3| t4 becomes id | event_id | start | end | start | end | start | end --- -- -- ? | 4| t1| t2 | t3| t4| t5| t6 ? | 5| t1| t2 | t3| t4 --- - No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.339 / Virus Database: 270.12.50/2150 - Release Date: 06/02/09 06:47:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
[solved]Re: Question about query - can this be done?
On June 2, 2009 04:13:31 pm Nathan Sullivan wrote: Ray, You can use the results of a query in a join with something like: select tmp.id, t1.id from (some_query_selecting_id) as tmp join t1 on t1.id=tmp.id Hope that helps. Regards, Nathan Sullivan Thanks Nathan, I think that completes the picture. Just what I was looking for. Ray -Original Message- From: Ray [mailto:r...@stilltech.net] Sent: Tuesday, June 02, 2009 4:58 PM To: mysql@lists.mysql.com Subject: Re: Question about query - can this be done? On June 2, 2009 03:14:36 pm Ray wrote: On June 2, 2009 10:44:48 am Peter Brawley wrote: Ray, I want a query that will provide one record per event with all times included. feel free to answer RTFM or STFW as long as you provide the manual section or key words. ;) Can be done with a pivot table. Examples under Pivot tables at http://www.artfulsoftware.com/infotree/queries.php. If you get stuck, pipe up. PB Thanks Peter and Brent. GROUP_CONCAT does exactly what I want. Brent, you're right, I don't really want to break up the times into separate fields that bad, the results are going into PHP so I can parse the combined fields there without much difficulty. The next problem is how do I use the results in a join. My first thought (that doesn't work) was: SELECT event_id, GROUP_CONCAT( CONCAT(start, '-', end) ) start_end FROM events GROUP BY event_id JOIN event_details WHERE not sure where this typo came from I meant ON events.event_id=event_details.event_id I have tried brackets, and a few other things, but I haven't got it yet. Thanks, Ray I found a solution, but not sure if it's a good idea. CREATE OR REPLACE VIEW v AS SELECT event_id, GROUP_CONCAT( CONCAT(start, '-', end) ) start_end FROM events GROUP BY event_id; SELECT * FROM event_details JOIN v ON events.event_id=event_details.event_id Thanks, Ray - Ray wrote: Hello, I've tried the manual and google, but I am not even sure what to call what I want to do. simplified data example: I have a table of start and end times for an event, and an id for that event in a table. each event may occur multiple times, but never more than 5 times and rarely more than 3. I want a query that will provide one record per event with all times included. feel free to answer RTFM or STFW as long as you provide the manual section or key words. ;) Thanks, Ray chart form follows: id | event_id | start | end --- 1 |4 | t1| t2 2 |4 | t3| t4 3 |4 | t5| t6 4 |5 | t1| t2 5 |5 | t3| t4 becomes id | event_id | start | end | start | end | start | end - -- -- -- ? | 4| t1| t2 | t3| t4| t5| t6 ? | 5| t1| t2 | t3| t4 - -- - No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.339 / Virus Database: 270.12.50/2150 - Release Date: 06/02/09 06:47:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=nsulli...@cappex.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt'
I wanted to respond with what I found: APPARMOR blocks mysql from writing to any other directory than the data dir and /tmp. I had to edit the /etc/apparmor.d/usr.sbin.mysqld file and add the following: /backups/mysql** rwk, to the bottom. Hope this helps someone. Ray -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: avoiding use of Nulls (was: The = operator)
snip I agree with most everything you said, as far as the use of a RDBMS, and especially: quoting you, theoretically you break up your data into tables so that there is never a non-applicable field for any given row. Here is where I disagree with you, and please DON'T take this as an insult (we're just haggling over good/bad practices ), No Insult or offense taken. Sorry for opening this can of worms ;) but I think it is lazy database design when you say: In the real world, this can lead to a lot of complexity, so many people usually cheat and have a few does not apply fields for some records. OK! Now here is my top 5 explanations why using Nulls is a bad idea: Explanation(5): The more you understand how the database is to be used, and the more complexity and thought you put into your database design, the less complex it will be to retrieve reliable information out of it. Furthermore, (and this is probably what makes me crazy when Nulls are evolved) after a ten year stretch of software development, where I and a team designed our own databases, I did a nine year stretch of statistical programming, using databases designed by other people, and Nulls in the data made the results unpredictable, and yeah, made me crazy! I had to write nightly processes to resolve inconsistencies in the data, if at least report inconsistencies. You know the old saying Garbage in = Garbage out, to me Nulls are garbage, and if there is a good reason for nulls to be a part of good clean data then someone please help me understand that. Explanation(4):I think that the brilliant computer scientists who developed the RDBMS database model, didn't want to support nulls initially, but they had to because null is a form of data, it means We don't know what this is, and so it must be stored knowing that Null should NOT be used as valid information. Explanation(3):I think it was back in the sixties at Berkley, when some smart guy on LSD found a use for the Null value, and now this use of Null is taught in Universities all over the world, and considered to be a blessed method to practice. Explanation(2): Using a Null column, and you have an index on that column, the index will not be used since NULL values do not get stored in an index and that is why the optimizer will need to go to the data pages in order to retrieve more information to suffice the requirements of the query. Explanation(1): I'm just in a argumentative mood today. I'm staying out of the debate on numbers 3,4 and 5, but I am watching with interest. Number 2 is very good to know and I will make note of that. as for number 1, well ...? Cheers and have a great weekend! Mike. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
avoiding use of Nulls (was: The = operator)
On Friday 13 March 2009 09:48:36 Michael wrote: I'm sorry for the rant, but nulls in databases make me see red! NULLS ARE GARBAGE! You are using a 'NULL-safe equal to operator', meaning, WHERE NOT group_id = 3, and oh, by the way, I DON'T WANT ANY NULLS TO QUALIFY AS 3. And yes this will be much slower, because now we can't use the index on group_id. Null is not an initial value, it is really saying that nothing has ever been put into this field, item, or what-ever. It is uninitialized and no one can say exactly what is in it, this is why it is marked as null. Using the null-safe equal to operator seems wrong, of course too me having any nulls in your data is wrong, or just plain dirty data, and I wouldn't put any faith into the results from a database that contains nulls, because the output is unpredictable if it is not very carefully coded. You (everyone in the SQL world) would be better off using the IS NULL and IS NOT NULL operators and the IFNULL() function to find all null values in their data, and initialize them to the appropriate initial value. This may require discussions with the application designers or project managers. Whom ever is responsible for these null values being your data all the frecking time? It is people that don't really understand what a null value is, and who also have the responsibility of designing a database application. So people like me who write the code need to add 'IS NOT NULL' to every conditional statement in our logic. What you want to do noted in your email below should be very simple, but only if you have CLEAN DATA. I would clean or what we call scrub your data first, then you can execute a simple and very fast select statement. Use a temporary table if you need to, just don't try to process dirty data, the end results will only make you look bad to anyone looking at the results. My $0.02, Mike. This is news to me, and I'd like to understand, so please don't take this as a flame or anything. I think I understand this part: When designing the database structure, you don't want just one table with a million columns, most of which are empty for any given row. What you do is break it up into several tables, with a relationship between the tables. Thus the term Relational database management system (RDBMS) theoretically you break up your data into tables so that there is never a non- applicable field for any given row. In the real world, this can lead to a lot of complexity, so many people usually cheat and have a few does not apply fields for some records. Now, what I've been doing is using NULL for does not apply as it seems to be more true to the data model than using some sort of fake data. I freely admit that Nulls increase the complexity of the code, but isn't it justified? I would be interested in your explanation. Ray -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: speedup mysql restore
Have you tried using the use db source /path/to/dumpfile.sql command via the mysql command line client? At 08:28 AM 5/20/2007, Vitaliy Okulov wrote: Çäðàâñòâóéòå, mysql. How i can speedup restore of mysql DB from file created by mysqldump? MySQL is quite fast at reloading data, but if you don't have enough memory or if key_buffer_size is not set high enough, then it can take days to re-index the data. You need to stuff as much RAM as possible in your machine and set key_buffer_size to at least 30% of your machine RAM. I've had indexed take 2+ days to rebuild and adding more RAM and tweaking key_buffer_size allowed the same keys to be rebuilt in under an hour. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to enable logging, MySQL 5.0.37 FreeBSD 6.2
I seem to have problems receiving from this list. I hadn't seen this answer come through until I was searching google again today. :) I just can't get my head around the mysql config system. If you can provide me with instructions, It would be greatly appreciated. Ray There is a sql log that you can enable in the config file. If you look at the config file now it will most likely only have one log file, Look at MSQL manual and logging. I dont have the setting infront of me at the moment. If you cannot find give a yell. On Wednesday 25 April 2007 10:06:19 Ray wrote: I am trying to debug another application and I need to enable logging of all queries (temporary only) to MySQL (5.0.37). OS is FreeBSD 6.2. MySQL installed from ports. -- Regards Michael Cole -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to enable logging, MySQL 5.0.37 FreeBSD 6.2
Hello, I am trying to debug another application and I need to enable logging of all queries (temporary only) to MySQL (5.0.37). OS is FreeBSD 6.2. MySQL installed from ports. any Instructions, or pointers to documentation would be appreciated. Thanks, Ray -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with upgrade
I upgraded a server from 4.0 to 4.1 and then to 5.0 but I've been
running into a problem. When I was running 4.0 the passwords with the
password('password') command where being created like this:
2a287c002f9773dc now after I upgraded to 4.1 when I add a new user the
passwords end up being like this: *A645B7228E54A58C02488027D2FBD96853E7BF8B
When I connect via the command line everything is peachy but when I
connect via PHP I kept getting this error:
*Warning*: mysql_connect() [function.mysql-connect
http://karma.detroitonline.com/function.mysql-connect]: Client does
not support authentication protocol requested by server; consider
upgrading MySQL client in */usr/local/vps/karma/apache/htdocs/test.php*
on line *3*
Client does not support authentication protocol requested by server;
consider upgrading MySQL client
So I upgraded to 5.0 and I'm still seeing long passwords and I'm still
getting this error with PHP...can anyone tell me what's going on here? I
upgraded another server to 5.0 and I didn't see this problem at all. I'm
a little screwed right now because this server is replacing one that had
to come out of service due to a bad harddrive and now clients can't make
connections anymore with their scripts. The old server was running
4.0it's looking like I might have to downgrade until this is fixed...
any help would be greatly appreciated
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Results Order Question
I'm running into a problem with a ratings script I'm writing. The overview is that a user can rate an item from 1-10. What I want it to do is display the highest rated item, with 10 being the highest rating. In case there are multiple items sharing the same score, the item that has been rated the most should be displayed. Here's a bit of the code: $albums=mysql_query(SELECT albumRating,totalRatings WHERE albumType='Album' ORDER BY albumRating,totalRatings DESC); The problem I'm running into is that it always displays the item with a 9 rating and not the 10. I'm figuring it's because mysql is seeing the order as 9,8,7,6,5,4,3,2,10,1 Am I going to have to store the numbers as 01,02,03,etc... in order to get this to work right or is there a better way to structure the query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Autoindexing
Well I believe I'll need to update mysql since I just realized this server is using 3.23. Gotta love taking something over from someone and finding out they weren't very good at the job to begin with. Karl Larsen wrote: Hi Remo, your method works fine on version 4.1 and the one shown for version 5 does not work here. Nice to know there is a SQL word AUTO_INCREMENT to do the job. Karl Remo Tex wrote: If you are using autoincrement filed you could try this: ALTER TABLE `my_database`.`my_table` AUTO_INCREMENT = 201; ...or else if it is some stored proc you should find and edit table where it sotres index/counter data.. Tom Ray [Lists] wrote: Hey, I have a really simple question (I hope)..I have a database that has a field in it that autoindexes the number. I screwed up and imported a bunch of wrong data. Up to row 200 it's right, beyond that it was wrong so I had delete it all. The problem now is the autoindexing is set to 1105, I want to change it so the next insert gets a number of 201 not 1105. I've tried to change it via phpMyAdmin but it keeps going back to 1105. Is there anyway I can do this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Autoindexing
Hey, I have a really simple question (I hope)..I have a database that has a field in it that autoindexes the number. I screwed up and imported a bunch of wrong data. Up to row 200 it's right, beyond that it was wrong so I had delete it all. The problem now is the autoindexing is set to 1105, I want to change it so the next insert gets a number of 201 not 1105. I've tried to change it via phpMyAdmin but it keeps going back to 1105. Is there anyway I can do this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my.cnf / mysqld logging
Hey there, I inherited a couple of servers that are in production but I noticed that there's not active logging for mysql on any of them. I looked and they don't have an active my.cnf file any where on the machines. One machine is running mysql 4.1.5 and the other is running mysql 5.0.18 When I ran a ps -ef on the machines I saw this for both /usr/local/mysql/bin/mysqld defaults-extra-file=/usr/local/mysql/data/my.cnf so I went and I put a my.cnf right where it was looking for it. Stopped/Started the mysql server and I'm still not seeing any logging happening for the mysql servers. Perhaps I'm messing this up? I want them to log everything to /var/log/mysqld.log and in the my.cnf I have the line: log = /var/log/mysqld.log Can anyone tell me where I'm messing this up or what is wrong? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf / mysqld logging
How would I do that? I'm still a novice when it comes to many aspects of mysql. [EMAIL PROTECTED] wrote: Hi, Try to enable query log. Thanks Regards Dilipkumar [EMAIL PROTECTED]: Hey there, I inherited a couple of servers that are in production but I noticed that there\'s not active logging for mysql on any of them. I looked and they don\'t have an active my.cnf file any where on the machines. One machine is running mysql 4.1.5 and the other is running mysql 5.0.18 When I ran a ps -ef on the machines I saw this for both /usr/local/mysql/bin/mysqld defaults-extra-file=/usr/local/mysql/data/my.cnf so I went and I put a my.cnf right where it was looking for it. Stopped/Started the mysql server and I\'m still not seeing any logging happening for the mysql servers. Perhaps I\'m messing this up? I want them to log everything to /var/log/mysqld.log and in the my.cnf I have the line: log = /var/log/mysqld.log Can anyone tell me where I\'m messing this up or what is wrong? -- 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]
denormalization/E-T-L help
Been searching for a while and can't seem to come up with any good answers to this - I have a normalized structure of about 5 tables that I need to denormalize into one big representation of the entire structure. Anyone know of a good tool or resource to 'flatten' my tables easily? I've got a query that removes all foreign key info and creates a column that holds the linked value, but my problem is that I have some columns that change with every row and others that should only be logged once per row (responses to polls for example). Here is a simplified structure: [data] id (int) nameid (int) valueid (int) [vars] id (int) name (varchar 255) [valuelookup] id (int) value (varchar 255) varid (int) Some variables, like 'browsertype', are consistent for every item, others like 'content' change for every row. The 'poll' variable contains multiple choice responses, but shouldn't be double counted when the table is de-normalized. I looked at some open source E-T-L tools, but they look pretty complicated, and I feel there is a way of doing this with sql somehow. Any idea on how to approach this? -- Thanks, Mathew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: denormalization/E-T-L help
Hi Shawn, I think I may have found a solution, but the query takes quite a while to run...here is what I have now paraphrased: SELECT vd.id AS id, vd.session AS session, vl.value AS content, vd2.varvalue AS browser, vl3.value AS bandwidth FROM vardata AS vd LEFT OUTER JOIN vars AS vn ON ( vn.name = 'content' AND vn.id = vd.nameid ) LEFT OUTER JOIN valuelookup AS vl ON ( vl.id = vd.valueid ) LEFT OUTER JOIN data AS vd2 ON ( vd2.session = vd.session ) LEFT OUTER JOIN vars AS vn2 ON ( vn2.id = vd2.nameid ) /* and so forth for each variable... */ WHERE vd.campaignId = 17 AND vn.name = 'content' AND vn2.name = 'browser' AND vn3.name = 'bandwidth' This would result in the following 'flattened' structure: [id][session] [content] [browser] [bandwidth] 22 55 intro MSIE 6.0hi 23 55 form1 MSIE 6.0hi 23 56 intro Firefox 1.0 lo Only problem is that the status of the thread stays in 'statistics' forever... 10 mins last time I checked before cancelling it... Seems like the time is increasing quite drastically as I pile on more variable comparisons... I am guessing because I am increasing that number of comparisons that must be done... Thanks, Mathew [EMAIL PROTECTED] wrote: Mathew Ray [EMAIL PROTECTED] wrote on 03/24/2005 10:42:51 AM: Been searching for a while and can't seem to come up with any good answers to this - I have a normalized structure of about 5 tables that I need to denormalize into one big representation of the entire structure. Anyone know of a good tool or resource to 'flatten' my tables easily? I've got a query that removes all foreign key info and creates a column that holds the linked value, but my problem is that I have some columns that change with every row and others that should only be logged once per row (responses to polls for example). Here is a simplified structure: [data] id (int) nameid (int) valueid (int) [vars] id (int) name (varchar 255) [valuelookup] id (int) value (varchar 255) varid (int) Some variables, like 'browsertype', are consistent for every item, others like 'content' change for every row. The 'poll' variable contains multiple choice responses, but shouldn't be double counted when the table is de-normalized. I looked at some open source E-T-L tools, but they look pretty complicated, and I feel there is a way of doing this with sql somehow. Any idea on how to approach this? -- Thanks, Mathew Show us some concrete examples of how you want your denormalized data to look and I am sure we can help. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql heartbeat
Hi Pete, Zabbix looks pretty nice... does it allow you to get insight into currently running apps, such as checking on the health of individual tables within a db? ~mathew Pete Moran wrote: A monitoring solution which can monitor mysql as well as pretty much any service on nix and win platforms is zabbix (http://www.zabbix.com), its very easy to setup and personally I think its excellent. It may be overkill if you just want to check a db is up though -Original Message- From: Mathew Ray [mailto:[EMAIL PROTECTED] Sent: Thursday, 17 February 2005 2:35 AM To: mysql@lists.mysql.com Subject: mysql heartbeat Has anyone used or built a db monitoring tool for MySQL? I have come across various system-level heartbeat tools, but I am specifically looking for a solution that will work on Win2K Server and RedHat boxes, as we have several servers that each run different instances of mysql. No replication or clustering here, just looking to get notification and execute some php if a specific db or table isn't accessible. I have a rough plan for making my own, but I thought I would check here to see if anyone had a suggestion... Thanks, ~mathew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select where multiple joined records match
I am guessing the long duration is caused by having to do complete table scans. How big is your dataset? What about creating another index in resource_goals that includes GoalNumber and ResourceID? Perhaps even Subject, Grade, and NumericGrade As I learned just a couple days ago, making sure your JOIN conditions and WHERE clause can refer to an index can speed up queries 1000x or more. Try tacking an EXPLAIN before your select and see how many rows MySQL things are being examined... optimally these should be very low, which indexes may be able to help with. Thanks, Mathew AM Thomas wrote: Hi there, I'll answer your questions below, but I should tell you that it looks like even three or four joins seem to make this too slow. Theoretically, the user could select up to 15 or so GoalNumber values, 12 for Grade, and about 20 possibilities for Subject - clearly it wouldn't be useful to specify that many items in a search, but that's the theoretical maximum; 4 or 5 values isn't unreasonable, though. Four ANDed goal numbers plus a subject and a grade slowed the search (on the shared commercial web host I'm using) into the 3 minute range, and that's with a regular join, not a left join. This is the SELECT that took about 3 minutes (3 trials, simplifying slightly each time, simplest given here): select r.id from resources as r join resource_goals as g0 on (r.id=g0.ResourceID) join resource_goals as g1 on (r.id=g1.ResourceID and g1.GoalNumber=1) join resource_goals as g2 on (r.id=g2.ResourceID and g2.GoalNumber=2) join resource_goals as g3 on (r.id=g3.ResourceID and g3.GoalNumber=3) join resource_goals as g4 on (r.id=g4.ResourceID and g4.GoalNumber=4) where ((g0.Subject='Social_Studies') and (g0.Grade='4th')) group by r.id; The fastest time was 2 min 48 sec. Last time (simplest query) was 3 min 2 sec. I'm really running out of time on this project, so I just went ahead and made the user interface such that users can only select one subject, grade, and/or goal number at a time. It's probably a sound decision from a usability perspective, so I'm not too sad. If I decide to make this work in the future, I'd probably have to just do a SELECT for each ANDed field, get the list of resource id's for each SELECT, then find the intersection of the lists in Perl. If I could speed this up with some kind of indexing, I'd love to know about it. The GROUP BY phrase is because I wanted just one row per resource. It seemed like I'd get a row for each condition/resource (didn't test it with the final ). I'm actually doing SELECT * FROM... in my code, and not using the resource_goals information in my output (that's a separate view at present, generated by different Perl code). It seems to work fine without the NOT NULL parts, you're right. I was wondering about that, but was sleepy enough at the time that I didn't trust my thinking. Sorry about not including my table defs :-( . I guess I was just hoping for a general approach, and didn't realize that anyone would be interested enough to read all that detail and provide and exact solution for me. Of course, now I realize that it would have simplified our discussion. Anyway, late but not never, and for help to whoever finds this in the list archives someday, here are my table defs (you're right - the goal number is a TINYINT): (Below is an abridged version of the resources table ; it also contains about 60 more TINYINT fields which are essentially used as booleans, some of which I hope to eliminate. Yes, I could have used SET or something, but I didn't for various reasons.) CREATE TABLE resources ( id INT UNSIGNED PRIMARY KEY, Title TEXT, ResourceType_THJHArticle TINYINT, ResourceType_NIEArticle TINYINT, DataEntryName TINYTEXT, Date DATETIME, Notes TEXT, Made_Keywords TEXT); CREATE TABLE resource_goals ( goal_id INT UNSIGNED PRIMARY KEY, ResourceID INT, Grade TINYTEXT, Subject TINYTEXT, GoalNumber TINYINT, NumericGrade TINYINT); Thanks a bunch for your help; I'm finding this more interesting than I thought I would. On Wed, 16 Feb 2005 11:08:20 -0500, Michael Stassen [EMAIL PROTECTED] wrote: AM Thomas wrote: Guarded exclamations of success! This seems to be working for me so far - thank you! Here's an actual example of a working query on my database; the field names are a little different (sorry, I was trying to make my earlier example more readable). The main change, though, is that I did plain 'join' instead of 'left join', which seems to make it much faster. It was pretty slow at first. Yes, LEFT JOIN does extra work, and it wasn't needed here. mysql select r.id, ga.Grade, ga.GoalNumber, gb.GoalNumber from resources as r join resource_goals as ga on r.id=ga.ResourceID and ga.Grade='4th' and ga.GoalNumber='1' join resource_goals as gb on r.id=gb.ResourceID and gb.Grade='4th' and
mysql heartbeat
Has anyone used or built a db monitoring tool for MySQL? I have come across various system-level heartbeat tools, but I am specifically looking for a solution that will work on Win2K Server and RedHat boxes, as we have several servers that each run different instances of mysql. No replication or clustering here, just looking to get notification and execute some php if a specific db or table isn't accessible. I have a rough plan for making my own, but I thought I would check here to see if anyone had a suggestion... Thanks, ~mathew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql heartbeat
Hi Michael, thanks for the info. I guess I will go ahead with my plan to make something a little bit more robust that will check the avialability of every table of every db on a box and send out notifications on an interval until they are corrected. Seems like a nice little sourceforge project though... ~mathew Michael Dykman wrote: On Wed, 2005-02-16 at 10:35, Mathew Ray wrote: Has anyone used or built a db monitoring tool for MySQL? I have come across various system-level heartbeat tools, but I am specifically looking for a solution that will work on Win2K Server and RedHat boxes, as we have several servers that each run different instances of mysql. No replication or clustering here, just looking to get notification and execute some php if a specific db or table isn't accessible. I have a rough plan for making my own, but I thought I would check here to see if anyone had a suggestion... Thanks, ~mathew I don't know of anything in open source or shrink wrapped that does it, least of all cross-platform but perl and Java each run with good transparency on both *nix and Win32.. a script/program to perform basic heartbeat check would take less than an hour and be completely portable. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: join speed vs. 2 queries
Many Thanks Peter, I appreciate your response. Played around with the indexes, and modified the query a bit more to match the campaignId of the value first and got a 2000x performance increase from the original query...now it takes .03 seconds on average where it used to take 60. One question though: is there a benefit to doing the INNER JOIN in the FROM clause rather than creating a join condition in the WHERE clause? I rewrote the query a bit using the WHERE join condition and noticed a slight performance hit on this particular query... Also, I am doing some pretty hefty multi-dimensional analysis on the data such as getting all the hits for each of m-variables with n-values each within a date range that also contain certain other varname/value combinations. Now if I am doing multiple different queries on this data, using different fields of the table in each one, is it preferable to create an index for each query, or make one uber-index that can be used as a swiss-army knife for at least a couple of them? I don't want to slow down INSERT calls if I can help it, but I don't want my queries to take 5 minutes each either... Perhaps the solution is two different databases, one without indexes for inserts, one with indexes for pulling the data back out? Then I can use indexes when I need to and don't have the performance hit when I need to insert data. The last question I have regards EXPLAIN results... Am I correct in believing that each row corresponds to a different comparison in the WHERE clause, and that each row analysis is done on each of the rows before it (associative)? So if rows is 10,10,10 for 3 joins, then in actuality 1000 rows will be examined? If this is the case, then I imagine it is better to winnow to the smallest possible set first and then go from there trying to get as few rows examined as theoretically possible given the constraints of the query. ~Mathew Peter Brawley wrote: I have a gut feeling that this kind of join should be able to be done with similar speed without having to use a temp table Yep but remember the query engine uses one index per table so without seeing your EXPLAIN output I'd try indexing ... the data table on name_id,value_id,campaign_id, the names table on id,name, the values table on id,value, then writing the query as ... SELECT COUNT(*) FROM data INNER JOIN names ON data.name_id=names.id INNER JOIN values ON data.value_id=values.id WHERE data.campaign_id = 22 AND names.name = 'content' AND values.value = 'index' PB - Mathew Ray wrote: Newbie on the list here having a bit of confusion at the moment why an INNER JOIN is taking so long... I have replaced a few column names to make it a bit more succinct: SELECT COUNT(*) FROM data, values, names WHERE data.campaign_id = 22 AND names.name = 'content' AND values.value = 'index' AND data.name_id = names.id AND data.value_id = value.id; This query should pull out all of the index content from the data table for campaign 22. name_id and value_id are indexed, as are the name and value fields of the names and values tables. campaign_id is also indexed in the data table and each name and value is unique per campaign. The vardata dataset for this campaign that has around 163000 entries and the above query takes nearly a minute to run. Total size of data table is around 3 million records. On the same machine, the following query takes roughly 2 seconds to run: CREATE TEMPORARY TABLE IF NOT EXISTS names_temp SELECT names.id as var_id, values.id as val_id FROM values, names WHERE names.campaign_id = 22 AND values.campaign_id = names.campaign_id AND names.name = 'content' AND values.value = 'index'; SELECT COUNT(*) FROM vardata, names_temp WHERE vardata.varNameId = names_temp.var_id AND vardata.varValueId = names_temp.val_id; After looking at EXPLAIN for both, I understand that the latter is faster because it is doing lookups based on constant values, but I have a gut feeling that this kind of join should be able to be done with similar speed without having to use a temp table... Is there any way to optimize the performance of the join query without having to go with the two-query option? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
join speed vs. 2 queries
Newbie on the list here having a bit of confusion at the moment why an INNER JOIN is taking so long... I have replaced a few column names to make it a bit more succinct: SELECT COUNT(*) FROM data, values, names WHERE data.campaign_id = 22 AND names.name = 'content' AND values.value = 'index' AND data.name_id = names.id AND data.value_id = value.id; This query should pull out all of the index content from the data table for campaign 22. name_id and value_id are indexed, as are the name and value fields of the names and values tables. campaign_id is also indexed in the data table and each name and value is unique per campaign. The vardata dataset for this campaign that has around 163000 entries and the above query takes nearly a minute to run. Total size of data table is around 3 million records. On the same machine, the following query takes roughly 2 seconds to run: CREATE TEMPORARY TABLE IF NOT EXISTS names_temp SELECT names.id as var_id, values.id as val_id FROM values, names WHERE names.campaign_id = 22 AND values.campaign_id = names.campaign_id AND names.name = 'content' AND values.value = 'index'; SELECT COUNT(*) FROM vardata, names_temp WHERE vardata.varNameId = names_temp.var_id AND vardata.varValueId = names_temp.val_id; After looking at EXPLAIN for both, I understand that the latter is faster because it is doing lookups based on constant values, but I have a gut feeling that this kind of join should be able to be done with similar speed without having to use a temp table... Is there any way to optimize the performance of the join query without having to go with the two-query option? -- Thanks, Mathew .. Mathew J. Ray Sr. Interactive Developer IQ Television Group .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GUID storage
Nowhere in this discussion was this question. Is it in the plans to have a 128 bit numeric column type for MySQL? If so, in what kind of time frame? If not, why not? Character arrays are obviously, but they are probably not the best way to get optimal performance. Native support for a 128 bit number, usable as a UUID, would be better. - ray On May 12, 2004, at 3:07 PM, Jeremy Zawodny wrote: On Wed, May 12, 2004 at 02:50:55PM -0700, Larry Lowry wrote: Well I'm trying to move to MySQL from the MS SQL Server world. Most data elements are easy except for the uniqueidentifier. In the MySQL world what is the preferred/best way to store a uniqueidentifier? The easiest would just be a char(36). If you have unique ids that are 36 characters, then use a char(36). That seems like the obvious thing to do. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- 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]
LOAD DATA from memory?
Hi MySQL Gurus, I am pretty green at SQL DB anything, so please bear with me. I would like to input new/pertinent data into my main table as fast/efficiently as possible. From reading Paul DuBois books, the Reference Manual and a few other sources I am convinced that LOAD DATA will be faster than the multitude of individual INSERTS that I would end up doing. So, assuming that doing something from memory is always faster than doing that something from a file - any filesystem caching aspects being a caveat - is it possible, (and if so how) to use LOAD DATA INFILE on something I already have in memory? (like my made-to-order array?) I am using perl - so maybe I can do all this inside an open filehandle and use the FH as my INFILE? Maybe access my array that way?? I apologize if this has been asked/beaten to death. Searching these mail lists yielded nothing. I looked, honestly. My environment: MySQL 4.0.18 perl 5.6 FreeBSD 5.1 Thanks for any help, Ray Spence __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
no create/update time for InnoDB from SHOW TABLE STATUS?
It looks as though, when I go into my databases, and use the command SHOW TABLE STATUS, that InnoDB tables do not have some information. I am on MySQL 4.1.0-alpha-debug. Has this been fixed in later versions? Example: mysql show table status; +--+++-++- +-+--+---+ +-+-++--- ++-+ | Name | Type | Row_format | Rows| Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Charset | Create_options | Comment | +--+++-++- +-+--+---+ +-+-++--- ++-+ | cur | InnoDB | Dynamic| 325214 | 2279 | 741294080 | NULL |114163712 | 0 | 337810 | NULL | NULL| NULL | latin1_swedish_ci | pack_keys=1| InnoDB free: 6601728 kB | | old | InnoDB | Dynamic| 1233005 | 10405 | 12830375936 | NULL |455147520 | 0 |1545059 | NULL | NULL| NULL | latin1_swedish_ci | pack_keys=1| InnoDB free: 6601728 kB | +--+++-++- +-+--+---+ +-+-++--- ++-+ 2 rows in set (2.78 sec) Notice the Create_time and Update_time data is NULL. Any reason? thanx - ray -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY RAND() performance
On Monday 08 March 2004 14:14, Neil Gunton wrote: [EMAIL PROTECTED] wrote: If your infact (sounds like) storing the pictures meta-data (name, size, owner, etc) and the data (blob of some kind) .. I would definately break up the design into 2 tables. That way when dealing with the meta-data table (your RAND() query) there is much less data that needs to be traversed to get your answer, which should result in a faster query. Thanks! This is definitely good advice, but unfortunately it doesn't solve the RAND() slowness. I have been testing with a separate table that ONLY contains the id of the pics, and as it grows toward 100,000 records this simple query does get noticeably slower: SELECT * FROM visible_pics ORDER BY RAND() LIMIT 30; Where visible_pics just has two numeric ID fields (pic_id and doc_id). It doesn't seem to matter if I make pic_id a primary key or not. I think I've reduced it to pretty much the minimal case, given that I want a random selection of ALL the records. I don't know the internals of how MySQL could optimize this sort of thing, but I was thinking that perhaps there was some kind of internal trickery it could do to select random record positions and then get those very quickly, without having to traverse the entire table. I think if the table has no varchar fields then it should be easy (at least in MyISAM) to calculate the record position based on the record number. So I think it *should* in theory be possible to optimize this, but I just don't know if anyone has realized that it's an issue, or if they are planning on doing anything about it. Any insights from MySQL internal developers? Or should I be posting this to the internals list? Thanks again, -Neil an alternative to the order by rand() with large record sets is to pick a random starting point limit $randPoint, 30 don't know if its a viable solution to your situation, but it limits you to 2 querys (row count, fetch) rather then the 30 (fetch 1 x 30) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
from the manual it appears that char(0) null default null can be
used as a boolean, will the values of either null or . haven't
tried it myself, but its documented.
http://www.mysql.com/documentation/mysql/bychapter/manual_Column_types.html#Column_types
[NATIONAL] CHAR(M) [BINARY | ASCII | UNICODE]
This is also quite nice when you need a column that only can
take 2 values: A CHAR(0), that is not defined as NOT NULL, will only
occupy one bit and can only take 2 values: NULL or . See section
6.2.3.1 The CHAR and VARCHAR Types.
On Wednesday 03 March 2004 11:43, Curtis Maurand wrote:
:-)
someflag enum('TRUE','FALSE');
Not quite boolean, but it works.
Curtis
On Wed, 3 Mar 2004, Mark Warner wrote:
The thing which bothers me most about MySQL is the lack of a
proper boolean. I don't like having to abstract a tinyint(1) into
true or false. As much of my work involves building applications
with Yes or No questions, I think I am switching to PostgreSQL.
[EMAIL PROTECTED] wrote:
What advantages, besides ease of setup, does MySQL hold over
PostgreSQL? It would seem, to me, that the two are close
competitors (both in quality, and performance).
Are you sure about quality? Check out:
http://sql-info.de/mysql/gotchas.html
You can check out postgresql's on the same site but they are
significally less critical. After reviewing this and talking
to some other people i'm switching over to postgresql. I'm
sure there is a place with mysql but I don't think i'd trust it
for anything critical unless you very confident your developers
know what they are doing.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mac: Trouble linking library
Hi everyone, I am trying to write an application on the Macintosh to use a mysql database. I tried posting this to the xcode reflector, but didn't get a response. I'm hoping that someone here is familiar with xcode and can help me solve my problem: Preface: I am fairly new to writing code and even newer to Xcode. I am trying to write an application to use MySQL. I finally figured out how to properly add a static library (libmysqlclient.a) to my project (thanks to Markus Hitter), but I get over 13,000 errors when I build. It's as if Xcode is trying to compile the library like it was source code. I haven't added any source code to my project yet, just an #include ... in 'main.m'. I just wanted to see if I can get the library to link before I start. I tried selecting 'libmysqlclient.a' , then choosing the info button and picking different types such as compiled and archive, but I still get errors. Anybody have any suggestions? Thanks. Marty Ray -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trouble starting mysql 4.1.1 on Mac
I just downloaded and installed mysql 4.1.1 on a Macintosh G5, but I cannot get it to start up. I first tried manual startup using the unix commands as detailed in the manual. I kept getting the message mysql shutting down before I could hit ctl-Z. Then when I tried to connect, it stated: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) I then tried installing the automatic startup routine, restarted the computer and running mysql and I get the same message. Any suggestions? Thanks! Marty Ray -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble starting mysql 4.1.1 on Mac
I tried what you suggested and I got: --- G5-Computer:/usr/local/mysql martyray$ sudo ./bin/mysqld Fatal error: Please read Security section of the manual to find out how to run mysqld as root! 040212 17:02:44 Aborting 040212 17:02:44 ./bin/mysqld: Shutdown Complete --- So I tried this: --- G5-Computer:/usr/local/mysql martyray$ sudo ./bin/mysqld -u root --- And received this response: --- 040212 17:03:07 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 040212 17:03:07 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43634. InnoDB: Doing recovery: scanned up to log sequence number 0 43634 040212 17:03:07 InnoDB: Flushing modified pages from the buffer pool... 040212 17:03:07 InnoDB: Started; log sequence number 0 43634 040212 17:03:07 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist G5-Computer:/usr/local/mysql martyray$ --- Hope I didn't screw up too bad... 8) Thanks for your help. Marty Ray On Feb 12, 2004, at 4:35 PM, Brent Baisley wrote: I assume when you tried to start MySQL from the command line you used safe_mysqld. You may not be seeing the error messages that would tell you why it's not starting. Try starting mysql directly. cd /usr/local/mysql sudo ./bin/mysqld You'll probably see an error message that is a bit more helpful. On Feb 12, 2004, at 4:09 PM, Marty Ray wrote: I just downloaded and installed mysql 4.1.1 on a Macintosh G5, but I cannot get it to start up. I first tried manual startup using the unix commands as detailed in the manual. I kept getting the message mysql shutting down before I could hit ctl-Z. Then when I tried to connect, it stated: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) I then tried installing the automatic startup routine, restarted the computer and running mysql and I get the same message. -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble starting mysql 4.1.1 on Mac
Okay, I tried all of that and I still couldn't connect. Here is what I got from the .err file: 040212 15:54:38 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 040212 15:54:38 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 040212 15:54:38 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 040212 15:54:39 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 040212 15:54:39 InnoDB: Started; log sequence number 0 0 040212 15:54:39 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 040212 15:54:39 mysqld ended Hmmm... Marty Ray On Feb 12, 2004, at 4:56 PM, Michael Stassen wrote: Marty Ray wrote: I just downloaded and installed mysql 4.1.1 on a Macintosh G5, but I cannot get it to start up. I first tried manual startup using the unix commands as detailed in the manual. I kept getting the message mysql shutting down before I could hit ctl-Z. Then when I tried to connect, it stated: First, you can avoid ctl-z like this cd /usr/local/mysql sudo -v sudo bin/mysqld_safe The reason mysqld failed to start should be in the error log. The error log should be in mysql's data directory (probably /usr/local/mysql/data) and should be named hostname.err (where hostname is the name of your computer). Most likely you have a permissions problem. Typically, this is either because mysql doesn't have permission to write to /tmp or because root instead of mysql owns the data directory and/or its contents. If so, you can fix these with sudo chmod 1777 /tmp and sudo chown -R mysql:mysql /usr/local/mysql/data respectively. ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) Right. The server (mysqld) didn't start, so there's nothing for the client (mysql) to talk to. I then tried installing the automatic startup routine, restarted the computer and running mysql and I get the same message. Right. The automatic startup routine effectively does the same thing as you running mysqld_safe. You need to find and fix the problem which keeps mysqld from starting, then both startup methods should work. Any suggestions? Thanks! Marty Ray Michael -- 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: Trouble starting mysql 4.1.1 on Mac
I really appreciate all the help! I tried running ./scripts/mysql_install_db and this is what I got: G5-Computer:/usr/local/mysql martyray$ ./scripts/mysql_install_db mkdir: ./data/mysql: Permission denied chmod: ./data/mysql: Permission denied mkdir: ./data/test: Permission denied chmod: ./data/test: Permission denied Installing all prepared tables ./bin/mysqld: Can't change dir to '/usr/local/mysql-standard-4.1.1-alpha-apple-darwin6.8-powerpc/data/' (Errcode: 13) 040212 17:44:13 Aborting 040212 17:44:13 ./bin/mysqld: Shutdown Complete ./scripts/mysql_install_db: line 274: 397 Broken pipe cat $fill_help_tables Installation of grant tables failed! I'm sure all this is boring and routine for all of you, but unix is still mostly Greek to me, although I am learning! Thanks Again, Marty Ray On Feb 12, 2004, at 5:37 PM, J.R. Bullington wrote: You have to re-run the ./scripts/mysql_install_db I had this exact error and it comes from deleting (or not installing) the 'mysql' database, which is a required component for MySQL to run. It also installs a db called 'test' which is not required. J.R. Bullington Innovatim Technical Support [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble starting mysql 4.1.1 on Mac
Okay! Thank you all for the help! 'j.e.b.' suggested looking at the help info at the link below. That did the trick. I was able to go through that info an troubleshoot my problem. You folks are great. A lot of experts are less than tolerant of beginners. I feel welcome here and I appreciate that! Regards and thanks to all, Marty Ray On Feb 12, 2004, at 4:43 PM, j.e.b. wrote: Marty Ray This should help you out - http://www.entropy.ch/software/macosx/mysql/ I just downloaded and installed mysql 4.1.1 on a Macintosh G5, but I cannot get it to start up. I first tried manual startup using the unix commands as detailed in the manual. I kept getting the message mysql shutting down before I could hit ctl-Z. Then when I tried to connect, it stated: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) -- 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]
Another beginner question
Brace yourself for another beginner question... When I try to use the SHOW command (logged in as root) , I get an error: mysql show databases; ERROR: No query specified Any suggestions? Thanks! Marty Ray -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Macintosh: Trouble with grant tables?
I am new to mysql and I am having trouble getting started. I am using a Macintosh G5 running OS 10.3.2. I got mysql installed and it would run, however when I tried to create a database, I got an error saying permission denied. I tried running the scripts/install_mysql_db, and I had to change permissions on ./data. Once I solved that one I now have a problem that has me stumped. When I run the script it now says: ERROR: 1 Can't create/write to file './mysql/db.frm' (errcode: 13) I have fixed the permissions on the ./data directory, but I can't figure out what else to do. Any suggestions? (take it easy on me, I'm fairly new to Unix) Thanks! Marty Ray -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug in WinMySQLadmin 1.4
My SQL Team, When launching WinMySQLadmin 1.4 on multi-displayed systems, the admin application launches center between both displays, not display 1, as it should. Thanks, Ray -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Check for data before inserting
actually it will act like insert in eather case. if its there, delete it first, if not just insert. http://www.mysql.com/doc/en/REPLACE.html as far as using shell to do the insert/update, you could look at command line php, that way you get the logic and you don't have to learn a new language. #!/usr/bin/php4 -q ?php phpinfo(); ? or if your always updating all columns and you don't use an auto_increment, you could get away with replace. On Thursday 16 October 2003 13:29, Brent Baisley wrote: It sound like you want to use REPLACE instead of the SELECT and INSERT/UPDATE combo. Replace will insert if the record doesn't exist and update if it does. On Thursday, October 16, 2003, at 01:27 PM, Mike Tuller wrote: I have a shell script that I have data entered into a database, and instead of just entering in the data blindly, I want it to check to see if the item it is entering exists already, and if it does, update the information rather than inserting it. So I want to run a select statement, and if results come back, have the data updated, and if not have it inserted. I know how to do this in PHP with $query_total_rows. Is there some way in SQL to do this, or do I need to figure out a way to do it in the shell script? Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: some connector/j tests fail
At 07:31 PM 9/27/03 -0500, Mark Matthews wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ray Tayek wrote: hi, just donwloaded mysql and connector/j. the driver seems to work fine for simple stuff. but i can not get all of the tests to run ... I actually get all of the tests to run (on every commit to the source repository, in fact). What error messages are the tests themselves producing? looks like they are failing for a variety of reasons. maybe i forgot to do some basic part of the install? (my java code can connect and get some type info from the metadata). thanks failures: ../build-mysql-jdbc/junit/TEST-testsuite.perf.BasePerfTest.txt: Testsuite: testsuite.perf.BasePerfTest Tests run: 1, Failures: 1, Errors: 0, Time elapsed: 4.085 sec Testcase: warning took 0.039 sec FAILED No tests found in testsuite.perf.BasePerfTest junit.framework.AssertionFailedError: No tests found in testsuite.perf.BasePerfTest Testcase: warning ../build-mysql-jdbc/junit/TEST-testsuite.perf.LoadStorePerfTest.txt: Testsuite: testsuite.perf.LoadStorePerfTest Tests run: 1, Failures: 0, Errors: 1, Time elapsed: 741.556 sec - Standard Output --- jdbc:mysql:///test?user=rootpassword=opencms Warm-up period (10 iterations).. Warm-up period ends 23801.636363636364 mean value, 1256.2030288081528 stddev, , Margin Of Error=833.6495808772103 confidence level=7.3468818978610395 24082.8332 mean value, 1521.9498367408682 stddev, , Margin Of Error=957.3416321756698 confidence level=8.303929274664474 24118.153846153848 mean value, 1467.35218603777 stddev, , Margin Of Error=879.0557891688037 confidence level=7.587233084336467 24173.928571428572 mean value, 1428.2046904260965 stddev, , Margin Of Error=818.7545636307784 confidence level=7.029571239243855 24107.3332 mean value, 1402.095939022085 stddev, , Margin Of Error=771.4637990844715 confidence level=6.624879541838586 24054.5625 mean value, 1372.8719427148878 stddev, , Margin Of Error=727.6221296388906 confidence level=6.248169378369745 24009.352941176472 mean value, 1344.1021469050925 stddev, , Margin Of Error=687.8445005989606 confidence level=5.906147360401785 23970.6668 mean value, 1315.9354087492288 stddev, , Margin Of Error=651.6649647414264 confidence level=5.594819084218729 23936.947368421053 mean value, 1288.8019517808495 stddev, , Margin Of Error=618.8403356023222 confidence level=5.312273075418013 23901.75 mean value, 1265.5031361083227 stddev, , Margin Of Error=590.2860665429411 confidence level=5.067584807058515 23872.714285714286 mean value, 1241.812313970695 stddev, , Margin Of Error=563.6501795054226 confidence level=4.838742314335374 23842.68181818182 mean value, 1221.0419316142795 stddev, , Margin Of Error=539.9182281153151 confidence level=4.635585283555991 23816.869565217392 mean value, 1200.3240252512915 stddev, , Margin Of Error=517.8393770032742 confidence level=4.446240666817372 23794.5418 mean value, 1179.9201943057656 stddev, , Margin Of Error=497.11481507980966 confidence level=4.268261911173509 23780.36 mean value, 1158.1666850673957 stddev, , Margin Of Error=477.16467424776704 confidence level=4.095851840161131 23759.30769230769 mean value, 1140.5435873109861 stddev, , Margin Of Error=459.8840246169451 confidence level=3.947855124197291 23740.074074074073 mean value, 1123.5117539125695 stddev, , Margin Of Error=443.6833124744453 confidence level=3.809046098360691 23725.321428571428 mean value, 1105.9265686541319 stddev, , Margin Of Error=428.03297557326016 confidence level=3.6744492426832407 23710.655172413793 mean value, 1089.4592228878118 stddev, , Margin Of Error=413.7188350956124 confidence level=3.55149917035769 23696.8665 mean value, 1073.7182663788278 stddev, , Margin Of Error=400.2998726092975 confidence level=3.436261062761972 - --- Testcase: test1000Transactions took 737.301 sec Caused an ERROR 30 java.lang.ArrayIndexOutOfBoundsException: 30 at testsuite.perf.BasePerfTest.getConfidenceLookup(Unknown Source) at testsuite.perf.BasePerfTest.addResult(Unknown Source) at testsuite.perf.LoadStorePerfTest.doOneIteration(Unknown Source) at testsuite.perf.BasePerfTest.doIterations(Unknown Source) at testsuite.perf.LoadStorePerfTest.test1000Transactions(Unknown Source) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) Testcase: test1000Transactions ../build-mysql-jdbc/junit/TEST-testsuite.perf.RetrievalPerfTest.txt: Testsuite: testsuite.perf.RetrievalPerfTest Tests run: 3, Failures: 0, Errors: 0, Time elapsed: 136.476 sec - Standard Output --- jdbc:mysql:///test?user=rootpassword=opencms
some connector/j tests fail
, Errors: 0, Time elapsed: 134.941 sec BUILD SUCCESSFUL Total time: 22 minutes 24 seconds [EMAIL PROTECTED]:/usr/src/mysql-connector-java-3.0.8-stable$ Script done on Fri Sep 26 20:46:01 2003 --- ray tayek http://tayek.com/ actively seeking mentoring or telecommuting work vice chair orange county java users group http://www.ocjug.org/ hate spam? http://samspade.org/ssw/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem compiling a small C program on Redhat-7.3
At 02:55 AM 9/25/03 -0400, you wrote: Does anybody have any idea why this program won't compile, it compiles fine on my slak box with includes from 4.0.15 so the program is probably ok. ... It starts screaming about mysql.h immediately,... gcc -c -I/usr/include/mysql mysql1.c make sure the mysql.h and other .h files are in this directory. i am not familiar with red had rpm, but he or someone may not have put the right include files in the right place or may have left some old ones hanging around . hth --- ray tayek http://tayek.com/ actively seeking mentoring or telecommuting work vice chair orange county java users group http://www.ocjug.org/ hate spam? http://samspade.org/ssw/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie gets access denied/invalid authorization (answered)
At 06:04 PM 9/21/03 -0700, Ray Tayek wrote: hi, trying out opnecms. so i installed mysql on slak 8.0. added a root password (something like ... root password opencms) like the doc says, so i can do a: use mysql and create databases and tables if am root on the slak box. but only if i am root. trying to create a database (db2) as a normal use gets a: ... turns out that the password (for mysql) did not take for some reason. i was able (as root) to manually delete the entries with empty user fields and change the password for the remaining two fields, so it works fine (got opencms to work). thanks --- ray tayek http://tayek.com/ actively seeking mentoring or telecommuting work vice chair orange county java users group http://www.ocjug.org/ hate spam? http://samspade.org/ssw/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_info not very informative?
What is annoying about this is that if you use mysqlimport to put this data into the table, I get: testdb.testtable: Records: 3 Deleted: 0 Skipped: 0 Warnings: 2 So, where are the warnings coming from? Is there some public API, below the C API layer, that one can use to obtain this information? If I do an insert and lose information, and get absolutely no warning about the problem, and no way to determine that there was an information loss, then I could call that a bug. I keep looking for a mysql_warnings function, or maybe a mysql_really_info, and I am not seeing anything else to work with. One can do the data validation at the application level by re-fetching the data, but isn't this the most complicated and ugly way to deal with the problem? The database obviously has the facts. Hence the warning from mysqlimport. Is there a reason it must keep the information to itself? - ray On Saturday, August 2, 2003, at 5:24AM, Adam Fortuno wrote: Actually, MySQL doesn't normally give overrun cut-off information (best I know). Use MySQL 4.0.x on 10.2.6, and MySQL has always performed that way. Regards, A$ On Friday, August 1, 2003, at 05:37 PM, Ray Kiddy wrote: I am trying to insert data using the C API. Particulars: OS: Mac OS X 10.3 (7A179) MySQL: MySQL 4.1.0-alpha table type: tried both MyISAM and InnoDB I have a table: mysql describe testtable; ++-+---+--+-+- +---+ | Field | Type| Collation | Null | Key | Default | Extra | ++-+---+--+-+- +---+ | _PK| int(11) | binary| | PRI | 0 | | | first | char(3) | latin1_swedish_ci | YES | | NULL| | | second | char(3) | latin1_swedish_ci | YES | | NULL| | | third | char(3) | latin1_swedish_ci | YES | | NULL| | ++-+---+--+-+- +---+ 4 rows in set (0.00 sec) Note that the first, second, and third columns have 3 characters of space. When I do: printf(insert: %s\n, insert); int result = mysql_query(one, insert); printf(info: %s\n, mysql_info(one)); I get: insert: INSERT INTO testtable (_PK,first,second,third) VALUES (1,'AAAXXX','BBB','CCC') info: (null) insert: INSERT INTO testtable (_PK,first,second,third) VALUES (2,'DDD','EEE','FFF') info: (null) insert: INSERT INTO testtable (_PK,first,second,third) VALUES (3,'GGG','HHHXXX','III') info: (null) Note that the value in the first insert, 'AAAXXX', is too long to fit. As is 'HHHXXX' in the third insert. And indeed, I see: mysql select * from testtable; +-+---++---+ | _PK | first | second | third | +-+---++---+ | 1 | AAA | BBB| CCC | | 2 | DDD | EEE| FFF | | 3 | GGG | HHH| III | +-+---++---+ 3 rows in set (0.00 sec) So, why is mysql_info not giving me any information about the data loss that is going on here? Is that not information that might be of interest? Is there some other call I have to make that will prepare for the mysql_info call? The doc does not seem to indicate this, but one never knows. thanx - ray -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_info not very informative?
I am trying to insert data using the C API. Particulars: OS: Mac OS X 10.3 (7A179) MySQL: MySQL 4.1.0-alpha table type: tried both MyISAM and InnoDB I have a table: mysql describe testtable; ++-+---+--+-+-+---+ | Field | Type| Collation | Null | Key | Default | Extra | ++-+---+--+-+-+---+ | _PK| int(11) | binary| | PRI | 0 | | | first | char(3) | latin1_swedish_ci | YES | | NULL| | | second | char(3) | latin1_swedish_ci | YES | | NULL| | | third | char(3) | latin1_swedish_ci | YES | | NULL| | ++-+---+--+-+-+---+ 4 rows in set (0.00 sec) Note that the first, second, and third columns have 3 characters of space. When I do: printf(insert: %s\n, insert); int result = mysql_query(one, insert); printf(info: %s\n, mysql_info(one)); I get: insert: INSERT INTO testtable (_PK,first,second,third) VALUES (1,'AAAXXX','BBB','CCC') info: (null) insert: INSERT INTO testtable (_PK,first,second,third) VALUES (2,'DDD','EEE','FFF') info: (null) insert: INSERT INTO testtable (_PK,first,second,third) VALUES (3,'GGG','HHHXXX','III') info: (null) Note that the value in the first insert, 'AAAXXX', is too long to fit. As is 'HHHXXX' in the third insert. And indeed, I see: mysql select * from testtable; +-+---++---+ | _PK | first | second | third | +-+---++---+ | 1 | AAA | BBB| CCC | | 2 | DDD | EEE| FFF | | 3 | GGG | HHH| III | +-+---++---+ 3 rows in set (0.00 sec) So, why is mysql_info not giving me any information about the data loss that is going on here? Is that not information that might be of interest? Is there some other call I have to make that will prepare for the mysql_info call? The doc does not seem to indicate this, but one never knows. thanx - ray -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Could someone please help in choosing a manual...
O'Reilly's Managing Using MySQL is a good intro. -- Ray Thompson As a true novice starting out, can someone tell me which of the two would be best suited for a true newbie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: doskey feature in mysql !!
Hmmm. Do F1 or F3 produce the last command given? -- Ray Thompson I remember in WIN NT 4 if we type cmd at the run prompt, then command prompt use to come up and we can repeat the commands i.e. doskey feature is available. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie question -- PHP document
There might be an error in the code. Some Tech books will have a website dedicated to any corrections of it's contents. The site location might be mentioned in the intro or you can search the publisher's web site for it. Ok so the mysql_send.php loads great. ( Apache 2 is the web server the file is in the htdocs folder. ) But when I try to run any command I get no results. -- Ray Thompson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY with Date Format
probably one of the many replys you'll get on this, but ORDER BY month(start_date) assuming the column that has the date is start_date On Thursday 10 July 2003 14:51, Mike At Spy wrote: I am trying to create an order by in a select statement that takes a standard 'date' column in a table and sorts the returned data by the month of that date. Off hand, I tried ORDER BY start_date(m) And I know that is wrong. Anyone have any suggestions? Thanks, -Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
auto_incement foobar
have a table CREATE TABLE `pics2003` ( `ID` int(11) NOT NULL auto_increment, `Title` varchar(255) NOT NULL default '', `ShortDesc` text, `LongDesc` text, `Image` varchar(255) default NULL, PRIMARY KEY (`ID`) ) TYPE=MyISAM; and after a while someone wanted something that would always be first in the table when ordered, so i added the entry to it and edited the id to be -1. now whenever anything is added to the table it gets an id of 2147483647 how do i fix it so that it start the auto_increment at the correct spot again? (i am probably going to dump an re add the table for now, but in case i foobar it again at another time) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User Variables on Startup
I would like to be able to set a user variable on startup of MySQL. I have review the documentation and searched the archives and I haven't seen anything about being able to do, or not do this. Can this be done? Since I'm unable to find reference to it, my guess is no, but I thought I'd give the list a try to see if could get some input on this. We are currently running version 4.0.7 in production. Thanks, Ray Elenteny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wierd sort query, how do you do it? (sort by ip proximity guess)
select 8 ^ 5; ERROR 1064: You have an error in your SQL syntax near '^ 5' at line 1 i'm guessing that this means mysql 3.23 doesn't have the xor. On Thursday 19 June 2003 11:52, Nicholas Elliott wrote: Remember that this is a binary XOR, not a logical XOR. Mysql does have a binary XOR operator, the '^' operator. So 'ipAddress1 ^ ipAddress2' is the binary XOR between the two values. The binary AND is the '' operator and the binary OR is the '|'. Just don't confuse them with the AND operator and/or the OR operator. Got all that? =) Nick Elliott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
wierd sort query, how do you do it? (sort by ip proximity guess)
currently i have a table with an ip coloumn in text 123.45.67.89 though it shouldn't be a big deal to convert it to binary, but was wondering if there is an easy way to sort by closeness to a given ip address? ie records that come out sorted as same class sub-c same class c same class c same class b same class a other addresses other addresses the only thing i can think of at this point is a really long order by if same c,if same b,if same a -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wierd sort query, how do you do it? (sort by ip proximity guess)
great! that was the trick i was looking for. thank you. i had the feeling i was doing it the hard way :-) On Thursday 19 June 2003 09:51, you wrote: Hi, If you convert the IP address into integers, then do an xor (exclusive or), you could call the resultant value the approximate 'distance' between the values. (think of the IP address in binary, any identical bits will result in 0, any differing will be 1. This means that the most significant '1' will be the first dissimilar bit between the two IP addresses. This is really what you are after. However, including any lesser significant bits may well be OK for your application.) OK, so how in SQL You need to create, on the fly ipAddress1 ^ ipAddress2 (order does not matter) where they are both integers (mysql will use 64bit integer arithmetic). You just need to ORDER BY this value. Regards Matthew -Original Message- From: Ray [mailto:[EMAIL PROTECTED] Sent: 19 June 2003 15:33 To: [EMAIL PROTECTED] Subject: wierd sort query, how do you do it? (sort by ip proximity guess) currently i have a table with an ip coloumn in text 123.45.67.89 though it shouldn't be a big deal to convert it to binary, but was wondering if there is an easy way to sort by closeness to a given ip address? ie records that come out sorted as same class sub-c same class c same class c same class b same class a other addresses other addresses the only thing i can think of at this point is a really long order by if same c,if same b,if same a -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wierd sort query, how do you do it? (sort by ip proximity guess)
hehe, guess there is 1 problem with this solution. mysql doesn't do xor. but found a ref saying a XOR b is mathematically equal to (a AND (NOT b)) OR ((NOT a) and b) On Thursday 19 June 2003 09:51, Matthew Smith wrote: Hi, If you convert the IP address into integers, then do an xor (exclusive or), you could call the resultant value the approximate 'distance' between the values. (think of the IP address in binary, any identical bits will result in 0, any differing will be 1. This means that the most significant '1' will be the first dissimilar bit between the two IP addresses. This is really what you are after. However, including any lesser significant bits may well be OK for your application.) OK, so how in SQL You need to create, on the fly ipAddress1 ^ ipAddress2 (order does not matter) where they are both integers (mysql will use 64bit integer arithmetic). You just need to ORDER BY this value. Regards Matthew -Original Message- From: Ray [mailto:[EMAIL PROTECTED] Sent: 19 June 2003 15:33 To: [EMAIL PROTECTED] Subject: wierd sort query, how do you do it? (sort by ip proximity guess) currently i have a table with an ip coloumn in text 123.45.67.89 though it shouldn't be a big deal to convert it to binary, but was wondering if there is an easy way to sort by closeness to a given ip address? ie records that come out sorted as same class sub-c same class c same class c same class b same class a other addresses other addresses the only thing i can think of at this point is a really long order by if same c,if same b,if same a -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reply-All (RE: mySQL GUIs)
I understand what you are saying, but all I did is was a standard all reply. So there is nothing I can do from my side ;-), thats either the way you have setup your e-mail client (reply to adress) and /or how the mailing list works. I've noticed that some messages from the list have a reply-to of just [EMAIL PROTECTED] and others have both the MySQL list and the address of the sender. When I hit reply-all on this message three addresses are in the To field. Nils, Jim, and the list. Delete the redundant ones... -- Ray -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Autoincrement/last_insert_id race safe?
On Wednesday 26 March 2003 08:25, you wrote: A programmer just asked me about a possible race condition, and I didn't know what to answer: If I insert a line using autoincrement, then ask for last_insert_id() am I guaranteed to get the same ID I just inserted? Yes It seems that another program could be inserting at almost the same time, and could increment the counter again before my last_insert_id() checks it. In that case, I would not be dealing with the same line I just inserted. last_insert_id is stored on a per-connection basis, and frecords the last insert done by that connection so, if your using a connection pooling system then a race condition can happen, but its only due to the connections being shared. anyone happen to know if coldfusion's use of odbc locks the connection per page request or does it just do it on queries and unlocks it as soon as the query is done? -- sql sql sql mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DATE function question
I have a small question about the DATE function. This is what I want to do: - User enters information into form and submits it to database - When info is added to database the current date is stored ala 2003-03-26 - Now I want to take that date 2003-03-26 and tack on either 15 or 30 days on to it so the date in another column reads either 2003-04-10 or 2003-04-25 I just can't seem to figure out how to do that math right so I get the result I'm looking for. Any thoughts or ideas? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql under unix in batch mode
the other thing is make sure you don't have a space after -p and before your password mysql -h localhost -u root -p mysql did.query did.out says ask for the password and start in the database mysql mysql -h localhost -u root -pmysql did.query did.out says the password is mysql and don't start in any database On Wednesday 26 March 2003 09:12, you wrote: Create a .my.cnf file in your home directory with permissions set to 600. In that file put the lines [mysql] user=root password=mysql now you can enter: mysql did.query did.out Marianadin, Didier wrote: Hi, I want to execute a simple query in batch mode in a mysql database. my OS : Unix (solaris 2.6) when I run this command : mysql -h localhost -u root -p mysql did.query did.out I'm always asked password even if I've already specified it in the previous command line (mysql) Enter password: How to avoid it ? Didier ([EMAIL PROTECTED]). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Column comment
Hi, I am aware of the fact that with 4.1.0, you can specify comments on a column by column by basis. I was wondering if this functionality could somehow be simulated in 4.0.x? It is high priority for me, but I cannot deploy the alpha stage 4.1.0 in a production environment. Basically, what I would like to do is associate a human readable label with each column (in the comment field) so that I can automatically generate web-based forms for any given table. The script would look at the column's metadata, display the appropriate HTML element, and display the corresponding label read from the column field. Is there any way this can be accomplished with MySQL 4.0.12? Thanks, Ray - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Warning suppression?
this is more a php question then mysql, but if you put a @ before the commands that are giving you warnings, it should cause it to not show them @mysql_connect(...) and you might also be looking for the php function error_reporting( [ int level ] ) On Tuesday 04 March 2003 13:55, you wrote: Folk, I'm not having a lot of success with my provider, so I'll ask here. Is there any way to turn _show warnings_ off from script? I cant seem to find anything relevant. I'm getting a warning that, Supplied argument is not a valid MySQL result resource . . .. I don't see any way to test for this. Have I overlooked something? Or can I disable the warnings from PHP? Or am I just stuck with this? Make a good day . . . . . . barn ~ If you can't beat them, arrange to have them beaten. -George Carlin ~ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- mysql, sql, query, sql, sql, sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Selecting from mySql database regarding dates
try 's arround the dates $query = SELECT * FROM designs, appliquetable, puffyfoamtable WHERE designs.puffyfoam = puffyfoamtable.puffyfoam AND designs.applique = appliquetable.applique AND (designs.designadddate = '$today' AND designs.designadddate = '$twoweeksago')GROUP BY catcode ORDER BY designfile; otherwise your getting 2003 minus 3 minus 3 (2003-3-3) On Monday 03 March 2003 12:01, you wrote: I have a column in my mySql database that holds a date that I've added each record called designadddate I'm trying to create a filter to only pull up the records that have been added over the last two weeks. This is my code ... I added the echo for the $today and $twoweeksago variables to make sure something was being calculated, they are showing up correct as 2003-03-03 and 2003-02-17 ... but it isn't pulling up any records -- it's not bombing out and giving me the Couldn't execute query message - but I know there's three records that I made sure had dates in between this range. I also tried using BETWEEN and it didn't seem to work either. $today = date (Y-m-d); $twoweeksago = date(Y-m-d,mktime(0,0,0,date(m), date(d)-14, date(Y))); echo$today\n; echo$twoweeksago\n; /* Select designs of the given type */ $query = SELECT * FROM designs, appliquetable, puffyfoamtable WHERE designs.puffyfoam = puffyfoamtable.puffyfoam AND designs.applique = appliquetable.applique AND (designs.designadddate = $today AND designs.designadddate = $twoweeksago)GROUP BY catcode ORDER BY designfile; $result = mysql_query($query) or die (Couldn't execute query.); Any help would me much appreciated! TIA Renee Toth - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- mysql, sql, query, sql, sql, sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Little help please
I'm looking for a webbased interface that will allow me to managae mysql databases. However, phpmyadmin has been ruled out due to the fact it requires the username and password to be stored in the config file and that it doesn't have any security to protect the average joe from stumbling across it. So can anyone point me in the direction of a utility that requires a login that checks of the users database then throws that information into the config for the mysql management? I would be greatful for any help. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Column Type help
Thanks for the help everyone, I'm gonna try the longtext approach and see how that works for me. -Original Message- From: Tore Bostrup [EMAIL PROTECTED] To: [EMAIL PROTECTED] Date: Wed, 26 Feb 2003 12:06:34 -0500 Subject: Re: Column Type help The only two considerations I can think of to choose one type of TEXT column over another would be: 1: The added storage required by a LONGTEXT over a TINYTEXT is only 3 bytes per row. Compared to the anticipated average size of the data, this would be neglibible. 2: If you want to impose somewhat of an upper limit on the amount of space used by one resume, you *could* use a specific text type to do so. However, I don't see a good reason to use a TINYTEXT where a varchar(255) should be fine, and I believe practical considerations would kick in for anything beyond TEXT (max 65535). So I'd probably go with the LONGTEXT, too. HTH, Tore. - Original Message - From: Tom Ray [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 11:28 AM Subject: Column Type help Hey there, I'm kinda new to using mysql to its full potential and I was wondering something. I want to store resume information, but I just want the user to cut and paste the resume in the Resume field and then store all that in one column in the table. Which table type should I use for this? Longtext? TIA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
additions to mysqlimport?
Hello - I am curious what extensions to mysqlimport people might be interested in seeing. I am importing some data with inconsistencies and and I find that mysqlimport helps one not at all. It seems to me that there are things it could do. For example: -rejected_lines=filename : would put all lines that generated errors or warnings in a separate file. --verbose : it could actually be verbose and list out all warnings and errors. I know this is a shocking suggestion, but hey, let's be bold. --no_insert : this would allow one to pre-flight data without actually causing the insert to happen, as -n does for make. --lines-commented-out-by=... : pretty obvious --terminate-on=word : deleted, skipped, or warning, for example, if you want to stop if there is even one warning. Any other ideas? I am thinking of trying to extend it myself and am open to suggestions. thanx - ray - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: insert after delete is not atomic
You are inserting 6 values into 7 fields, so it fails.
looks like a 6 field table to me
create table if not exists MSFT (date DATE not null, close
DECIMAL(10,2), high DECIMAL(10,2), low DECIMAL(10,2), volume MEDIUMINT,
yest DECIMAL(10,2), PRIMARY KEY date (date));
1) date
2) close
3) high
4) low
5) volume
6) yest
7) PRIMARY KEY -- Not a field, unless they REALLY changed things in MySQL 4
You are also inserting numbers with 4 digits right of the decimal, where
you have specified 2.
inserting numbers that are too big just get trimmed, again unless its another
MySQL 4 change.
running the script on MySQL 3 get the expected results.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 34 to server version: 3.23.49-log
mysql create table if not exists MSFT (date DATE not null, close
DECIMAL(10,2), high DECIMAL(10,2), low DECIMAL(10,2),- volume
MEDIUMINT, yest DECIMAL(10,2), PRIMARY KEY date (date));
Query OK, 0 rows affected (0.10 sec)
mysql delete from MSFT where date='2003-02-04';
Query OK, 0 rows affected (0.04 sec)
mysql insert ignore into MSFT
Values('2003-02-04','47.3200','47.9400','46.8800','406440','47.8000');
Query OK, 1 row affected (0.06 sec)
mysql select * from MSFT;
++---+---+---++---+
| date | close | high | low | volume | yest |
++---+---+---++---+
| 2003-02-04 | 47.32 | 47.94 | 46.88 | 406440 | 47.80 |
++---+---+---++---+
1 row in set (0.02 sec)
--
mysql, sql, query, sql, sql, sql
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
can someone help me optimize this?
i have a select that seem to be taking too long for what it is, and after looking over the documents i can't seem to find a better way. probably some things that would help: -on a datetime field, is there a way to look at everything from a month that still uses the index. -is there a way to cause a select to work more in the background and not kill the server while its working (it may take longer, but atleast other queries will keep working) select logon_id from customers where Limited_Access = 1; 2100 rows in set (0.41 sec) select Username, sum(AcctSessionTime)/3600 as hrs from customers left join radacct on (customers.logon_id = radacct.username) where Limited_Access=1 and AcctStopTime '2002-12-31' and AcctStopTime '2003-02-01' group by Username having hrs 65 order by Username; after about 8 minutes, killed the process, but it should have about 2100 records as well, and getting a few complaints about the data server not responding from one of the main programs that is using it. explain says: | table | type | possible_keys | key| key_len | ref | | rows | Extra | +---+--+++-+- ---+--+-+ | customers | ref | Limited_Access | Limited_Access | 2 | const | | 2169 | where used; Using temporary | | | radacct | ref | UserName | UserName | 64 | customers.LOGON_ID |3 | where used CREATE TABLE customers ( ### trimmed the extra fields out of this one ID int(11) NOT NULL auto_increment, LOGON_ID varchar(20) default NULL, Limited_Access tinyint(4) default NULL, PRIMARY KEY (ID), KEY Limited_Access (Limited_Access) ) TYPE=MyISAM; CREATE TABLE radacct ( RadAcctId bigint(21) NOT NULL auto_increment, AcctSessionId varchar(32) NOT NULL default '', AcctUniqueId varchar(32) NOT NULL default '', UserName varchar(64) NOT NULL default '', Realm varchar(64) default '', NASIPAddress varchar(15) NOT NULL default '', NASPortId int(12) default NULL, NASPortType varchar(32) default NULL, AcctStartTime datetime NOT NULL default '-00-00 00:00:00', AcctStopTime datetime NOT NULL default '-00-00 00:00:00', AcctSessionTime int(12) default NULL, AcctAuthentic varchar(32) default NULL, ConnectInfo_start varchar(32) default NULL, ConnectInfo_stop varchar(32) default NULL, AcctInputOctets int(12) default NULL, AcctOutputOctets int(12) default NULL, CalledStationId varchar(10) NOT NULL default '', CallingStationId varchar(10) NOT NULL default '', AcctTerminateCause varchar(32) NOT NULL default '', ServiceType varchar(32) default NULL, FramedProtocol varchar(32) default NULL, FramedIPAddress varchar(15) NOT NULL default '', AcctStartDelay int(12) default NULL, AcctStopDelay int(12) default NULL, PRIMARY KEY (RadAcctId), KEY UserName (UserName), KEY FramedIPAddress (FramedIPAddress), KEY AcctSessionId (AcctSessionId), KEY AcctUniqueId (AcctUniqueId), KEY AcctStartTime (AcctStartTime), KEY AcctStopTime (AcctStopTime), KEY NASIPAddress (NASIPAddress) ) TYPE=MyISAM; there are about 780,000 records in the radacct table, about 1/2 of that is this month -- mysql, sql, query, sql, sql, sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
datetime field question
is there a way to use the date part of a datetime field that still uses the index on the datetime field? i've tried a few different things and it keeps saying in the explain it says that its a possible_key, but NULL for the key -- mysql, sql, query, sql, sql, sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqldump, exclude table?
is they a way to have mysqldump ignore a list of tables, but get everything else? something like: mysqldump -ume -psecret -hserver --all-databases --exclude-tables=server.acctlog mysql 3.23.49-8.2 debian -- mysql, sql, query, sql, sql, sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: simple problem, no tcp port openning - Solved
after digging around more, i found Debian's MySQL has 'skip-networking' in the /etc/mysql/my.cnf further down On Monday 27 January 2003 17:08, you wrote: installed mysql onto debian using a deb file. it is running locally fine, but it isn't openning a TCP port for remote connections to it. a few lines from /etc/mysql/my.cnf [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 /var/log/mysql.log /usr/sbin/mysqld, Version: 3.23.49-log, started with: Tcp port: 0 Unix socket: /var/run/mysqld/mysqld.sock status: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.49, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version3.23.49-log Protocol version 10 ConnectionLocalhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 7 min 21 sec mysqladmin variables -p | port| 0 -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
simple problem, no tcp port openning
installed mysql onto debian using a deb file. it is running locally fine, but it isn't openning a TCP port for remote connections to it. a few lines from /etc/mysql/my.cnf [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 /var/log/mysql.log /usr/sbin/mysqld, Version: 3.23.49-log, started with: Tcp port: 0 Unix socket: /var/run/mysqld/mysqld.sock status: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.49, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.49-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 7 min 21 sec mysqladmin variables -p | port| 0 -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
simple problem, no tcp port openning
-- this is a resend, its been over an hour, and the 1st hasn't shown up on the list yet. i'm assuming my mail server dropped it -- installed mysql onto debian using a deb file. it is running locally fine, but it isn't openning a TCP port for remote connections to it. a few lines from /etc/mysql/my.cnf [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 /var/log/mysql.log /usr/sbin/mysqld, Version: 3.23.49-log, started with: Tcp port: 0 Unix socket: /var/run/mysqld/mysqld.sock status: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.49, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.49-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 7 min 21 sec mysqladmin variables -p | port| 0 -- [EMAIL PROTECTED] --- -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL 4.0.7 is released
What does it mean when you say: MySQL 4.0.7 is released and then you give a URL? When one goes to the URL, one sees text which says the latest version is 4.0.5. So, is 4.0.7 released or is it not? If so, why do you not point to pages on the web site which actually include the release which you are announcing? For example, since http://www.mysql.com/downloads/ does not point to the 4.0.7 version, which page does? thanx - ray On Friday, December 27, 2002, at 12:10 PM, Lenz Grimmer wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL 4.0.7, a new version of the popular Open Source Database, has been released. It is now available in source and binary form for a number of platforms from our download pages at http://www.mysql.com/downloads/ and mirror sites. snip - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
read-only... even after myisamchk -ru
I had used MySQL's myisampack utility to pack my tables, which makes them read-only, but now I want them to be write-able again. I have used myisamchk --recover --unpack (tried -ru as well) and the table is still read-only. What is the actual incantation one must use here? I can backup and re-create the table, but that seems unnecessary, if the documentation on myisamchk is correct thanx - ray - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
backing up
is there a way to remotely grab the databases (ie mysqldump) and have it place everything in a different file for every database? or has anyone written a script to do something like this? dbserver having databases mysql, db1, db2 and creating backup files mysql.sql db1.sql db2.sql and at different times, databases can be added and removed from the servers, and it would be nice to have those backed up as well. (ie later in the week db3 is added and the nightly backup starts making a db3.sql file without having to edit the backup script) -- mysql, sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: take one database offline
another trick that might work is just to deny access to everyone to the database. if no one has access to it, then its as good as gone. i don't know if MySQL would allow just grant blah on *.* to user; revoke all on disabled_db.* from user; and alot of it depends on how the security is setup and possibly why you want just that database temporaraly gone. On Thursday 05 December 2002 8:05, you wrote: Richardson, Thursday, December 05, 2002, 12:32:27 AM, you wrote: RDE On a single Linux box w/mysql 3.23.52 I have mysqld running and there are RDE about 20 databases live in the environment. I want to take one of the RDE databases offline but leave mysqld running with the other db's - without RDE interrupting service. RDE I want mysqld to gracefully refresh itself that the database is offline RDE and not require a restart. I have the luxury of time if that's helpful to RDE the solution. RDE How do I disable a database in a running server and leave the rest of the RDE db's in production? What do you mean disable database? You can't turn off the database.. You can make backup of the database and then drop the database.. Why do you want to do it? -- mysql, sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
tools for characterizing performance?
It seems strange to me that the tools in the MySQL distribution that let you run automatic performance tests rely on DBI and perl. If I want to test the performance of MySQL, why is it a good idea to do this while relying on another technology, one that is distributed by someone else, one that may have integration issues with pre-release versions of MySQL? I imagine that the perl part of this makes it easier to generate those nice little reports. So, why would one not use MySQL tools to run the performance tests and dump out to a raw format, which would then be fed into a completely separate application, written in perl for example, that would then pretty-print the data? This would seem to be a much less fragile solution. It would also have the benefit that one could test the performance of MySQL without polluting the results with perl and DBI cruft. Would anybody care to defend the current approach? thanx - ray - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Difference between 4.0.5 and 4.0.5a
Hi, I've been trying to determine the difference between MySQL 4.0.5 and 4.0.5a. I can't seem to find any documentation relating to the change. Can someone point me to the documentation or tell me what has changed? Thanks, Ray - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Difference between 4.0.5 and 4.0.5a
Thanks for the response! On Wed, 2002-11-27 at 09:44, Jocelyn Fournier wrote: Hi, MySQL-4.0.5a is compiled again a new patched glibc library, to prevent from MySQL having any load issues (which was the case on some systems). Features are exactly the same between 4.0.5 and 4.0.5a. Regards, Jocelyn - Original Message - From: Ray Elenteny [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 27, 2002 2:47 PM Subject: Difference between 4.0.5 and 4.0.5a Hi, I've been trying to determine the difference between MySQL 4.0.5 and 4.0.5a. I can't seem to find any documentation relating to the change. Can someone point me to the documentation or tell me what has changed? Thanks, Ray - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LIMIT in MySQL
its not LIMIT from, to, its LIMIT start, count SELECT * FROM table ORDER BY somefield LIMIT 30,30 On Tuesday 26 November 2002 11:56, you wrote: I must not be awake yet. Why is this query sending me back 60 records? Shouldn't it only send back records 30 through 60 (i.e. 30 records)? SELECT * FROM table ORDER BY somefield LIMIT 30,60 Thanks, -Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- mysql, sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT Statement Question
When I run SELECT * FROM POSTCODE; in mysql prompt I get garbled display of data and some of my data is not shown. the garbled display is probably due to the results being wider then the screen/terminal, and since the mysql client doesn't implement a horizontal scrollbar like a gui/web mysql client does, it makes wide output hard to understand or use. Is this a bug or a limitation of mysql? Or am I doing something wrong? that could be considered a limit of the text mysql client or of the terminal your using. -- mysql, sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Time comparisons
your probably looking for curtime() manual snip CURTIME() CURRENT_TIME Returns the current time as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context: mysql select CURTIME(); - '23:50:26' mysql select CURTIME() + 0; - 235026 /manual snip On Thursday 14 November 2002 8:36, you wrote: Can anyone help with (what I hope is) a pretty simple time function query? I need to extract a set of records from a table based on separate date and time columns, where both the date and time are older or equal to now. For example, my sql query is going to look a bit like this: select ID, Live_Time, Live_Date from schedule where TO_DAYS(NOW()) = Live_Date and SOMETHING = Live_Time and it's the SOMETHING that I need to know! (Before you ask, there are reasons why the date and time are separate columns rather than being a single datetime column, and I don't want to rewrite the structure unless I have to) Thanks Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- mysql, sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Load (messy) data file
is there a way to have mysql skip columns from the data file when loading the data? i am getting a text delimited data file from an outside source that is updated daily. however the data is defined as having 10 blank fields in it, and in the first 9000 records 7 more fields are just not used, and 1 field that is encrypted and useless to me. there are about 140 fields total in this table. i already could write an awk/sed script to clean up stuff, and its possible to use a temp_table as well and do a insert select as well. but i'm wondering if there is a way in MySQL 3.23 to do this already. (if its built in, why hack it together with scripts?) -- mysql, sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Q about replication timing
I'm looking into options to improve performance of mysql in our app and have a question about replication. I need the following guarantee, which I believe is not possible with replication ... If a client successfully updates the master, any subsequent read from a slave must reflect that update. Or to put it negatively, it must be impossible for a client to update the master and subsequently read an old (pre-update) value from the slave. Am I correct in assuming that replication can not make this guarantee? -- Ray Zimmerman / e-mail: [EMAIL PROTECTED] / 428-B Phillips Hall Sr Research / phone: (607) 255-9645 / Cornell University Associate / FAX: (815) 377-3932 / Ithaca, NY 14853 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
why so late with Mac OS X versions?
Can someone tell me what the holdup is in getting binaries built for Mac OS X? I notice on the www.mysql.com web page that the latest version of the binaries available of the is 4.0.3. Why is this? If somebody can point to the cause of the logjam, there might be something I can do about it. Is it a technical, resource or a personnel problem? thanx - ray - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
[Bug] compile problem on Mac OS X 10.2
A number of people have asked about this, and some have figured it out and posted info somewhere, but not in the www.mysql.com docs. Perhaps, I need to report it on this list for it to get into a distro, yes? In configure, there is a bit that starts at line 7514: *darwin*) if test $ac_cv_c_compiler_gnu = yes then CFLAGS=$CFLAGS -traditional-cpp -DHAVE_DARWIN_THREADS -D_P1003_1B_VISIBLE -DSIGNAL_WITH_VIO_CLOSE -DSIGNALS_DONT_ BREAK_READ -DHAVE_BROKEN_REALPATH CXXFLAGS=$CXXFLAGS -traditional-cpp -DHAVE_DARWIN_THREADS -D_P1003_1B_VISIBLE -DSIGNAL_WITH_VIO_CLOSE -DSIGNALS_D ONT_BREAK_READ MAX_C_OPTIMIZE=-O with_named_curses= fi ;; Change: with_named_curses= to: with_named_curses=-ltermcap ... and then it builds. Voila! Note, this is for Mac OS X 10.2, and not earlier Mac OS X systems. thanx - ray - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
New to mySQL
Hi all (details of database at then end of this message) I hope someone can give me some advice. I am trying to create a database and access via PHP for a friend of mine that has a caravan park. What I want him to be able to do is to add bookings for the caravans via a PHP page and for clients to be able to search to see if a caravan is available for rent. I have created 2 tables and have put data into it via command prompt and also retrieved the data from it and carried out a join linking the 2 tables together. Which all seems to work well. The one thing I cannot get into my head is how can you tell the database that all the days between the start and end dates are booked. Also when people search for a caravan on a specific date and say they want it for 7 day the database/PHP checks to see if the entire period is totally free for them and does not colide with another booking. I am not sure whether I should be in list list of the PHP list so sorry if I have got it wrong. Any advice or places to visit would be greatly appreciated. Thanks for all your help Ray Details of what i have done already: mysql use matrix Database changed mysqlCREATE TABLE bookings ( - booking_id SMALLINT (6) NOT NULL AUTO_INCREMENT, - booking_start DATE NOT NULL DEFAULT '-00-00', - booking_end DATE NOT NULL DEFAULT '-00-00', - villa_id SMALLINT (6) NOT NULL DEFAULT '0', - PRIMARY KEY (booking_id) - ); mysqlINSERT INTO bookings VALUES (1, '2002-04-01', '2002-04-15', 3); mysqlINSERT INTO bookings VALUES (2, '2002-03-23', '2002-04-04', 1); mysqlCREATE TABLE villas ( - villa_id SMALLINT (6) NOT NULL AUTO_INCREMENT, - vill_name VARCHAR (25) NOT NULL DEFAULT '', - PRIMARY KEY (villa_id) - ); mysqlINSERT INTO villas VALUES (1, 'Gandy'); mysqlINSERT INTO villas VALUES (2, 'Hathaway'); mysqlINSERT INTO villas VALUES (3, 'Healy'); mysqlINSERT INTO villas VALUES (4, 'Mcleod'); mysql SELECT * FROM bookings; ++---+-+-+ | booking_id | booking_start | booking_end | villa_id | ++---+-+-+ | 1 | 2002-04-01 | 2002-04-15 |3 | | 2 | 2002-03-23 | 2002-04-04 |1 | ++---+-+-+ 2 rows in set (0.17 sec) mysql SELECT * FROM villas; +--++ | villa_id | villa_name | +--++ |1| Gandy| |2| Hathaway | |3| Healy | |4| Mcleod | +--++ 4 rows in set (0.00 sec) mysql SELECT villa_name, booking_start, booking_end FROM bookings LEFT JOIN villas ON bookings.villa_id = villas.villa_id; ++---++ | villa_name | booking_start | booking_end | ++---++ | Healy | 2002-04-01 | 2002-04-15 | | Gandy | 2002-03-23 | 2002-04-04 | ++---++ 2 rows in set (0.00 sec) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Won't compile on Mac OS X 10.2
I got MySQL 4.0.2-alpha compiled on Mac OS X 10.2 (final version - Jaguar6C115). I am having problems with getting any client to connect to it, so perhaps I am doing something else wrong, which is possible since I am new to MySQL First I tried: ./configure --with-named-curses-libs=/usr/lib/libncurses.dylib This did not work. Then I manually executed the one compile that was failing, adding the libncurses.dylib to the list of libraries to use, as such: g++ -O3 -DDBUG_OFF -fno-implicit-templates -fno-exceptions -fno-rtti -traditional-cpp -DHAVE_DARWIN_THREADS -D_P1003_1B_ VISIBLE -DSIGNAL_WITH_VIO_CLOSE -DSIGNALS_DONT_BREAK_READ -o mysql mysql.o readline.o sql_string.o completion_hash.o ../ readline/libreadline.a ../libmysql/.libs/libmysqlclient.a /usr/lib/libncurses.dylib -lz -lm -lz -lm Then I did a make again and everything went along to the end. For some reason my client sub-directory did not get re-named to bin and, as I said, I cannot connect. Keep getting: % mysql ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) ... so I may have messed something up. Maybe someone else, with more experience with installing, can pick the ball up and move it along... thanx - ray On Sunday, August 25, 2002, at 05:39 PM, Mitch Leung wrote: Dear Sir, MySQL 3.23.52 won't compile on Mac OS X 10.2 based on Darwin 6.0 which comes pre-installed with gcc 3.1 I don't want to uninstall it as it would probably bring down other major functions of the new system. Regards, Mitch Leung - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
To share with MySql friends in net?
Oi, for gentility! Could inform which the procedure to share the MySql that is in my machine with the escritorio friends. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RES: Mysql database permissions questions
Expensive colleagues, all good! I am here in the Brazil! I need an aid to have access the MySql saw net local! Or either, I have the MySql in a machine and want to share the bank with my fellow workers, Has that to be he saw MyOdbc? Command of line? I am making in Java and using the JDBC. Ass.: RaydaCosta -Mensagem original- De: Paras pradhan [mailto:[EMAIL PROTECTED]] Enviada em: Sexta-feira, 16 de Agosto de 2002 09:08 Para: [EMAIL PROTECTED] Assunto: Mysql database permissions questions hi all when i connect to my mysql database server using mysql -h a.b.c.d -u xyz -pmypass It logs on and it's fine and in mysql shell when i do use mysql then it is allowing me to acess the mysql default mysql database. i dunn't want this. when i type use mysql then it sud say access denied to user [EMAIL PROTECTED] and xyz can only use the allowed database. help ! Thanks Paras. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql database of Net
Expensive colleagues, all good! I am here in the Brazil! I need an aid to have access the MySql saw net local! Or either, I have the MySql in a machine and want to share the bank with my fellow workers, Has that to be he saw MyOdbc? Command of line? I am making in Java and using the JDBC. Ass.: RaydaCosta -Mensagem original- De: Ray da Costa Enviada em: Sexta-feira, 16 de Agosto de 2002 09:18 Para: 'Paras pradhan'; [EMAIL PROTECTED] Assunto: RES: Mysql database permissions questions Expensive colleagues, all good! I am here in the Brazil! I need an aid to have access the MySql saw net local! Or either, I have the MySql in a machine and want to share the bank with my fellow workers, Has that to be he saw MyOdbc? Command of line? I am making in Java and using the JDBC. Ass.: RaydaCosta -Mensagem original- De: Paras pradhan [mailto:[EMAIL PROTECTED]] Enviada em: Sexta-feira, 16 de Agosto de 2002 09:08 Para: [EMAIL PROTECTED] Assunto: Mysql database permissions questions hi all when i connect to my mysql database server using mysql -h a.b.c.d -u xyz -pmypass It logs on and it's fine and in mysql shell when i do use mysql then it is allowing me to acess the mysql default mysql database. i dunn't want this. when i type use mysql then it sud say access denied to user [EMAIL PROTECTED] and xyz can only use the allowed database. help ! Thanks Paras. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
problem creating table with default date
I'm using the following DDL statement: create table if not exists dbname.tablename ( ..., the_datedate not null default current_date ); I receive the following error message: ERROR 1064: You have an error in your SQL syntax near 'current_date, I'm been looking around and can find no support on how to instruct the database to default to current date. All I've found was something like this: create table if not exists dbname.tablename ( , the_datedate not null default '-00-00' ); This second option is not really what I want. I want the let the database create the current date when I insert a row. Is this possible and if so, how can it be done? Thanks. Eric Ray [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
4.0.1 to 4.0.2 and Windows symbolic links
I just moved from 4.0.1 to 4.0.2 on Windows 2000 and my databases that had been accessed through Windows symbolic links could no longer be accessed. I had to add the use-symbolic-links directive back to my my.ini file to get them to work? The docs still state that in MySQL 4.0 this will no longer be required. Are the docs correct or the software? Thanks, Ray Elenteny - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
New to mySQL questions.
Hi Just a quick question. I was reading the manuals on the MySQL web regarding the initial setup of the program. I have installed the program and all is going well and have a user called root with full access and another user called mysql with limited rights (used mainly for PHPmyAdmin). There is a database called mysql which contains all the users, rights and privalages. The question I would like to ask is that there is another database called test whcih I belive from reading is a databse that users can connect to annoymously. Can I delete this database as I do not want anyone to connect without a user profile and password. Does it cause a problem deleting this database. Also while I am here can any one advise or tell me where to go in respects of accessing data to/from the database in a php form if the database users have a password. Or am I totally off track and you do not need a password or user in the php page as it is running locally. If anyone has a sample database and some php forms to interact with the databse they could let me see to learn from, it would be very much appreciated. Thanks for all your help Ray Healy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Data Entry for a Newbie
phpMyAdmin if you have a webserver with php you can use. or you can use MyODBC and us MS Access to make a front end for data entry. -- Forwarded Message -- Subject: Data Entry for a Newbie Date: 25 Jul 2002 11:12:35 -0400 From: William Bradley [EMAIL PROTECTED] To: Mysql [EMAIL PROTECTED] At the moment I have Mysql installed and understand command line entry of data, or entry via a text file. Either way, it is difficult, especially if you have been used to data entry screens. Is there a utility somewhere that would allow me to do this? Failing that, is it possible to write a html file to communicate with the server on my own computer? Any help is appreciated, Bill. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --- -- I have seen the future and it is just like the present, only longer. -- Kehlog Albran, The Profit - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
