New to Dates - Plain English Please

I haven't worked with dates yet and wondered if someone could give me an overview in plain English. At the moment, I'm working on a table with a column of dates in this format: March 2, 2003 July 7, 2004 If I understand the Manual, the correct format for these dates in a MySQL table would be

RE: Group Query

Thanks for your reply, This works great, but when I add a constraint such as WHERE B.Project_ID = '10' I dont get the NULL values from the User table which I need: SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN Bookings B ON U.User_ID = B.Rep_ID WHERE B.Project_ID =

RE: New to Dates - Plain English Please

Hi There are two aspects to dates, and your questions include parts of both. When MySql stores dates you have a choice of column types http://dev.mysql.com/doc/mysql/en/DATETIME.html has the details If you just need to store dates then the DATE type will suit your needs. You just specify it

Re: Group Query

Shaun, when you add WHERE B.Project_ID = '10' you, in a way, change your LEFT JOIN to an INNER JOIN. You need to do it like: LEFT OUTER JOIN Bookings B ON U.User_ID = B.Rep_ID AND B.Project_ID = '8' /Johan shaun thornburgh wrote: Thanks for your reply, This works great, but when I add a

R: R: why CPU is high while disks are idle in a table scan???

First: thank you for sparing so much time with me. There are 3 major factors determining your performance: 1) the speed of the CPU, 2) the size if your RAM, and 3) the data transfer speed of your disks. I thought that the size of tables in the db would have made a difference. From the

Re: Group Query

Hi, Thanks for your reply Johan, this works however I actually onley wanted the rows where Project_ID = '10'. SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN Bookings B ON U.User_ID = B.Rep_ID AND B.Project_ID = '10' GROUP BY(U.User_Location); This query is returning

LIBMYSQL.dll Not found

Unable to run mysqladmin.exe Error: This application has failed to start because LIBMYSQL.dll was not found. Re-installing the application may fix this problem. Re-installing does not fix this problem. Regards, Yarrokon -- MySQL General Mailing List For list archives:

RE: New to Dates - Plain English Please

--- Peter Lovatt [EMAIL PROTECTED] wrote: Hi There are two aspects to dates, and your questions include parts of both. When MySql stores dates you have a choice of column types http://dev.mysql.com/doc/mysql/en/DATETIME.html has the details If you just need to store dates then

RE: New to Dates - Plain English Please

hi SELECT DATE_FORMAT(date_field ,%M %D %Y) FROM table1 ORDER BY date_field will order in true chronological order Peter -Original Message- From: David Blomstrom [mailto:[EMAIL PROTECTED] Sent: 22 June 2004 10:17 To: [EMAIL PROTECTED] Subject: RE: New to Dates -

MySQL InnoDB tables dump [CASCADE]

Hi, I'd ask you : Can I use in MySQL 4.x on InnoDB tables something like : create dump from this InnoDB tables with cascade INSERT sql statements based foreign keys ? And if yes, how ? thank you very much regards Milan Svrlo -=x=- Skontrolované antivírovým programom NOD32 -- MySQL General

Re: LIBMYSQL.dll Not found

Hi, Perhaps, you should copy mysql/lib/opt/libmysql.dll to mysql/bin directory. Refer to the following thread of the MySQL-Win mailing list. http://lists.mysql.com/win32/14799 -- Sumito_Oda mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives:

Re: load data into 2 tables and set id

Shawn, Thanks for your reply below. I found it extremely useful. I have followed your instructions and got good results up to the url_visits table. I have a perl script to parse the values out of the log. The log has 3,770,246 lines and is gzipped. I then applied your SQL statements with the

Re: load data into 2 tables and set id

Did you mean there to be duplicates in the url_visits? Do I need to use IGNORE in the following SQL? INSERT url_visit (url_server_ID, url_path_ID, querystring, category) SELECT us.ID, up.ID, if(bt.path_split 0, SUBSTRING(bt.url,path), NULL), bt.category FROM bulk_table bt INNER JOIN url_servers

Mysql and RHAS

Hello: I have just installed a Linux RHAS 3.0 with mysql-3.23.58-1, mysql-devel-3.23.58-1 and mysql-server-3.23.58-1.9. When mysql starts, there is just listening one mysql-daemon. When I had this configuration running in Redhat 7.3, mysql started 4 daemons. Could someone explain me this

Re: load data into 2 tables and set id

I think I fixed it! INSERT IGNORE url_visit (url_server_ID, url_path_ID, querystring, category) SELECT DISTINCT us.ID, up.ID, if(bt.path_split 0, SUBSTRING(bt.url,path), NULL), bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server = bt.server INNER JOIN url_paths up on

Re: MySQL InnoDB tables dump [CASCADE]

Milan Svrlo [EMAIL PROTECTED] wrote: Hi, I'd ask you : Can I use in MySQL 4.x on InnoDB tables something like : create dump from this InnoDB tables with cascade INSERT sql statements based foreign keys ? And if yes, how ? No. Probably you need mysqldump and SET FOREIGN_KEY_CHECKS = 0

Re: how to randomnly select just 1 record from the table?

Binay [EMAIL PROTECTED] wrote: well i have only 4 records in the table. When i use below mentioned query it always return the same record irrespective of time delay. Now wht can i do? What version of MySQL do you use? Thanks Binay Binay [EMAIL PROTECTED] wrote on 21/06/2004 15:03:34:

Logging Admin Activity

Is there a way to log all activity for an Admin account only? Thank you

Re: Mysql and RHAS

Oscar Hernández Hernández wrote: Hello: I have just installed a Linux RHAS 3.0 with mysql-3.23.58-1, mysql-devel-3.23.58-1 and mysql-server-3.23.58-1.9. When mysql starts, there is just listening one mysql-daemon. When I had this configuration running in Redhat 7.3, mysql started 4 No, 4

mysql action history

Dear Sirs, I have mysql database on the web server. Is there any way to see mysql actions from any log? I want to see what my visitors did on the sql databases. Any idea Best Regards __ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB

Re: R: R: why CPU is high while disks are idle in a table scan???

Well, this is a very unique question. How can we force MySQL to IGNORE an index. Is this so you can test your disk systems data throughput? I am quite interested in why you need to see the disk activity in order to understand if it's working or not. I have concentrated so hard and so long on

Re: load data into 2 tables and set id

Great catch! I believe you found the problem. Sorry about that! ;-D So... just curious... from the original 60GB of text data, how much space did the final data require (including indices)? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine

R: R: R: why CPU is high while disks are idle in a table scan???

Don't worry, I found: now my table has 64,000,000 rows... there is no way it can cache it in memory... Now the problem I have is that connectorJ crashes (hasn't crashed before, when the table had 16,000,000 rows) I'm going to write another email... thank you anyway -Messaggio

Issuing Command Line iInstructions from Web Application

Is it possible to use the mysqldump function from within a web application on a Windows based server using PERL or ASP or do I have to be logged in to the actual command line MySQL Client with Root Privileges? Thanks in advance, Chris

A Complicated Group Query

Hi, I have three tables (among others!) in my database; Bookings, Users and Projects. Users have location codes and can make bookings which are for a particular project. Using the following query I can get a list of all Locations (Each user has a Location code) and the number of Bookings that

connectorJ huge table problem

I have a large table (64,000,000 rows). Everything was fine when the table was 16,000,000 rows, now connectorJ crashes: java.sql.SQLException: java.lang.ArrayIndexOutOfBoundsException: 6 at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStateme nt.java:908)

RE: connectorJ huge table problem

Are you using a streaming connection? -Original Message- From: Leonardo Francalanci To: Mysql Sent: 6/22/04 7:43 AM Subject: connectorJ huge table problem I have a large table (64,000,000 rows). Everything was fine when the table was 16,000,000 rows, now connectorJ crashes:

R: connectorJ huge table problem

Are you using a streaming connection? ehm... how do I know? My conn string is jdbc:mysql://192.168.0.253:3000/LETSPARTY1?autoReconnect=yes where 192.168.0.253 is a machine on my LAN... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:

Changing Innodb Variables without restarting the server

Hi, I'm running MySQL 4.0.17-nt, and for some reason the my.cnf file in my C:\ directory got renamed around the time we rebooted the server last. Now the global variable innodb_buffer_pool_size is set to a small value, and my queries are taking a long time to execute. I don't want to restart

R: connectorJ huge table problem

I have also weird results: I access data by ID, which is a primary key in the table. Some times (again, very random behaviour) I get 4 rows back (instead of just 1) and the data I get is always 1275749989 This happens with random IDs. the code to check this is: [..] ResultSet rs =

Re: A Complicated Group Query

Hi Shaun, You said: SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN Bookings B ON U.User_ID = B.User_ID AND B.Project_ID = '10' GROUP BY(U.User_Location); It returns all User Locations in the User table, regardless of the Project_ID of the Booking. That's exactly how

RE: connectorJ huge table problem

Dude. I wouldn't post your server ip here. ouch. God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -Original Message- From: Leonardo Francalanci [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 22, 2004

R: connectorJ huge table problem

Dude. I wouldn't post your server ip here. ouch. Well, it's a LAN address! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: connectorJ huge table problem

A non-streaming connection will place the resultset in memory. With large tables you can run out of memory and the application will stop. A streaming resultset does not place the entire dataset in memory and therefore you can retrieve `large` data sets. In order versions of connectorJ the default

R: connectorJ huge table problem

A non-streaming connection will place the resultset in memory. With large tables you can run out of memory and the application will stop. I get only 1 record per time (accessing by key), so I don't think it could be an issue. Thank you anyway! -- MySQL General Mailing List For list

R: connectorJ huge table problem

It only happens if I use the PreparedStatement.setInt(...) Using a statement like SELECT * FROM PARTITIONED_B WHERE ID= + m; instead of SELECT * FROM PARTITIONED_B WHERE ID=?; preparedStetement.setInt(1, m); (which is more correct) works. If nobody has a clue I will report a bug in a couple of

Re: connectorJ huge table problem

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Leonardo Francalanci wrote: I have a large table (64,000,000 rows). Everything was fine when the table was 16,000,000 rows, now connectorJ crashes: java.sql.SQLException: java.lang.ArrayIndexOutOfBoundsException: 6 at

Re: R: connectorJ huge table problem

Leonardo Francalanci [EMAIL PROTECTED] wrote on 22/06/2004 14:26:54: A non-streaming connection will place the resultset in memory. With large tables you can run out of memory and the application will stop. I get only 1 record per time (accessing by key), so I don't think it could be

R: R: connectorJ huge table problem

It looks as if you are being over-complex anyway. Isn't what you want SELECT * FROM PARTITIONED_B ORDER BY RAND() LIMIT 1 ; which picks a single random record. No, I have to know which is the ID I pick before doing the select (because I splitted my data, and when I access the splitted

R: connectorJ huge table problem

What happens if you use a nightly snapshot of Connector/J from http://downloads.mysql.com/snapshots.php? I tried, but I still have the same problem: java.sql.SQLException: java.lang.ArrayIndexOutOfBoundsException: 6 at

Newby to loading

I'm trying to load ACD call data into a table for the first time. I've saved my data file as a text file on my root directory (win xp c:\) When I try to run: LOAD DATA INFILE '/GRPO617' INTO TABLE helpdeskgrouptotal; I get the following error in mysql control center: [Chris

Re: R: R: connectorJ huge table problem

Next time you come over, maybe you can help hold him or take the shot. He's such a pain to take pictures of, he walks right up to the camera. On Tue, 2004-06-22 at 09:50, Leonardo Francalanci wrote: It looks as if you are being over-complex anyway. Isn't what you want SELECT * FROM

RE: Newby to loading

load data infile 'absolute-file-name' into table helpdeskgrouptotal; -Original Message- From: Chris Stevenson To: [EMAIL PROTECTED] Sent: 6/22/04 9:58 AM Subject: Newby to loading I'm trying to load ACD call data into a table for the first time. I've saved my data file as a text file

R: R: R: connectorJ huge table problem

Next time you come over, maybe you can help hold him or take the shot. He's such a pain to take pictures of, he walks right up to the camera. Mmmh... my English is very bad... I can't understand... is it a joke or just a mistake? -- MySQL General Mailing List For list archives:

Re: R: R: R: connectorJ huge table problem

I think a mistake. I did not understand it either Leonardo

Views Syntax for MySQL 5

Hi All, I have noticed a lack of comments re Views. Is it 5.0 or 5.1 that we will have Views capability? I had hoped it was in the most up to date public development release of 5. Unless I am getting the Views syntax wrong I assume its not there. If it is not already packaged in V5.0 then

mysql control center documentation

Is there a user guide available anywhere? I can't seem to find anything on mysql.com

RE: New to Dates - Plain English Please

One more question... Suppose you have a list of dates in the proper format, like this: 2004-02-04 2003-11-02 and you encounter a date with only the month and year, like May 2002. How would you insert that, something like this?: 2004-02-04 2003-11-02 2002-05---

Re: R: R: connectorJ huge table problem

Richard F. Rebel wrote: Next time you come over, maybe you can help hold him or take the shot. He's such a pain to take pictures of, he walks right up to the camera. Yeah, I also have problems taking snapshots of my cat... use FEL to pre-flash so he won't blink when you take the actual

Re: A Complicated Group Query

Hi Shawn, Thanks for your reply, I am still having problems here though! The first query produces this: +-+---+ | COUNT(B.Booking_ID) | User_Location | +-+---+ |1818 | NULL | | 1 | 204

Re: mysql control center documentation

The MySQL web site was recently reorganized. For some reason, when they did so, the buried the manual 4 clicks away in the Developer Zone. It's not clear to me how anyone new is supposed to find it there. In any case, the URL is http://dev.mysql.com/doc/mysql/en/index.html. Michael Chris

Re: New to Dates - Plain English Please

David Blomstrom wrote: Suppose you have a list of dates in the proper format, like this: 2004-02-04 2003-11-02 and you encounter a date with only the month and year, like May 2002. How would you insert that Not. Prompt the user for a full date. Jochem -- MySQL General Mailing List For list

Re: mysql control center documentation

Somehow I didn't notice mysql control center in the Subject until the exact moment I hit Send. Sorry about that. Please ignore my unhelpful reply. I seem to remember that your question has been asked before, and that the answer was that it's pretty self-explanatory. You might check the list

RE: mysql control center documentation

Had the same issue. I've bookmarked it, but its not clear why its so hard to find. Its good stuff man put it out front:-) -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 22, 2004 12:13 PM To: Chris Stevenson Cc: [EMAIL PROTECTED] Subject: Re: mysql

Re: Newby to loading

Victor Pendleton wrote: load data infile 'absolute-file-name' into table helpdeskgrouptotal; -Original Message- From: Chris Stevenson To: [EMAIL PROTECTED] Sent: 6/22/04 9:58 AM Subject: Newby to loading I'm trying to load ACD call data into a table for the first time. I've saved my

Re: mysql control center documentation

mysql.com (php.net and freshmeat.net) sites all have a search features where a if a requested file is not found it performs a search for it as a key word. Example finding the mysql control center manual is as easy as requesting http://www.mysql.com/mysql control center or depending on your

Re: load data into 2 tables and set id

Shawn, Here are the results: $ ls -l /sawmill/rawlog/SG_CSGL02_main_47060821.log.gz -rw-r- 1 bluecoat staff138510199 Jun 14 10:04 /sawmill/rawlog/SG_CSGL02_main_47060821.log.gz $ time ./logfile.pl /sawmill/rawlog/SG_CSGL02_main_47060821.log.gz Tue Jun 22 16:53:40 2004:

Re: Views Syntax for MySQL 5

On Tue, 22 Jun 2004 16:34:44 +0100 Nic Skitt [Real Productions] [EMAIL PROTECTED] wrote: Hi All, I have noticed a lack of comments re Views. Is it 5.0 or 5.1 that we will have Views capability? I had hoped it was in the most up to date public development release of 5. Unless I am

Re: mysql control center documentation

Eric Bergen wrote: mysql.com (php.net and freshmeat.net) sites all have a search features where if a requested file is not found it performs a search for it as a key word. Interesting. I didn't know that. That's nice, but it's hardly intuitive. I expect that's designed to help with broken

Performance issues

We have an internal SNMP monitoring system that is monitoring about 10,000 devices. Each device is pinged then pulled for about an average of 25-30 elements. Each of the ping results and elements are then stored in text file, then another system picks them up (NFS) and inserts them into a

Re: New to Dates - Plain English Please

Jochem van Dieten wrote: David Blomstrom wrote: Suppose you have a list of dates in the proper format, like this: 2004-02-04 2003-11-02 and you encounter a date with only the month and year, like May 2002. How would you insert that Not. Prompt the user for a full date. Jochem That is not strictly

Re: New to Dates - Plain English Please

At 23:40 -0700 6/21/04, David Blomstrom wrote: I haven't worked with dates yet and wondered if someone could give me an overview in plain English. At the moment, I'm working on a table with a column of dates in this format: March 2, 2003 July 7, 2004 If I understand the Manual, the correct format

RE: New to Dates - Plain English Please

At 9:03 -0700 6/22/04, David Blomstrom wrote: One more question... Suppose you have a list of dates in the proper format, like this: 2004-02-04 2003-11-02 and you encounter a date with only the month and year, like May 2002. How would you insert that, something like this?: 2004-02-04 2003-11-02

Re: A Complicated Group Query

OOPS! That's because my second query should have been a LEFT JOIN and not a RIGHT JOIN (I hate making silly cut-and-paste errors like that) 8-{ If I understand you correctly, you want to see a count of how many bookings you have had per project broken down by user location. Try this: SELECT

Using date ranges on a composite key degrades performance

Im finding that when I execute a query joining to a large fact table (~450 million rows) using a composite key of INT(11) DATE for a single Date i.e. AND dimension.id = factable.id AND factable.Date = '2004-06-22' the query returns exceptionally fast. However, when I change this to a range,

Re: New to Dates - Plain English Please

Michael Stassen wrote: Jochem van Dieten wrote: David Blomstrom wrote: Suppose you have a list of dates in the proper format, like this: 2004-02-04 2003-11-02 and you encounter a date with only the month and year, like May 2002. How would you insert that Not. Prompt the user for a full date. That

Re: Using date ranges on a composite key degrades performance

Are you using the JOIN ... ON... syntax or the comma syntax of joining tables? David Perron

Re: New to Dates - Plain English Please

Jochem van Dieten wrote: Michael Stassen wrote: Jochem van Dieten wrote: David Blomstrom wrote: Suppose you have a list of dates in the proper format, like this: 2004-02-04 2003-11-02 and you encounter a date with only the month and year, like May 2002. How would you insert that Not. Prompt the

Re: R: connectorJ huge table problem

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Leonardo Francalanci wrote: What happens if you use a nightly snapshot of Connector/J from http://downloads.mysql.com/snapshots.php? I tried, but I still have the same problem: java.sql.SQLException: java.lang.ArrayIndexOutOfBoundsException: 6

mysql crash when opening a connection

Hi, I have installed mysql 4.0.18 on my debian box. I am quite new to linux. mysql works fine with the command line (mysql) and with apache/phpmyadmin too. But when i try to connect from Windows or if i telnet from Debian, i got: Number of processes running now: 0 040622 21:27:33 mysqld

RE: Using date ranges on a composite key degrades performance

Ive tried both ways. The first way uses: INNER JOIN dimension USING (id) INNER JOIN fact_table USING (id) WHERE factable.Date = '2004-06-15' AND factable.Date '2004-06-22' The second way: dimension STRAIGHT_JOIN fact_table WHERE factable.Date = '2004-06-15' AND factable.Date '2004-06-22'

RE: Using date ranges on a composite key degrades performance

I worked up some additional variations on creating the same result set. Would you mind giving them a try to see if any one of them works better with your indexes? FROM dimension INNER JOIN factable ON dimension.id = factable.id AND factable.Date = '2004-06-15' AND

Re: A Complicated Group Query

Hi Shawn, A slight correction(!) Client_ID is contained in the Projects table as a Client can have many projects. Therefore c.Project_ID will cause an error... Here is a definition of the tables: mysql DESCRIBE Users;

Re: Performance issues

Hi! Can you give more details on the problematic inserts you're doing (table structure, indexes and insert command) ? Also, do you believe your queries would benefit from MySQL's query cache? Maybe it's worth upgrading to version 4 and use this feature, even if you allocate just a small

Re: New to Dates - Plain English Please

Another possible solution would be to store partial dates as, for example, 2004-05-01. In other words, simply make the partial date the same as the first day of that month. This has the advantage that range date range functions work, but you will not be able to tell the difference between a

AES_DECRYPT (PHP Script)

Hi, I have problem with fetching records to php script. table structure : --- id int(10) message text -- i place information into message field using following query INSERT INTO table (message) VALUES (AES_ENCRYPT('$message','password')); it works

Re: Views Syntax for MySQL 5

Un-named views are supposed to be there already. I know this is not the real thing, I just thought I would mention it. ;-) Cheers Andrew. - Original Message - From: Josh Trutwin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, June 22, 2004 5:53 PM Subject: Re: Views Syntax for

Re: Newby to loading

You probably wrote '\GRP0617' instead of '/GRP0617' At 07:58 AM 6/22/2004, you wrote: I'm trying to load ACD call data into a table for the first time. I've saved my data file as a text file on my root directory (win xp c:\) When I try to run: LOAD DATA INFILE '/GRPO617' INTO TABLE

Data spikes and buffering inserts...

Hi! My MyISAM database works perfectly for 98% of the cases that we encounter. However, there are a few cases during our experiment that we know will cause spikes in dataflow. We know both the why and when, so we are trying to plan appropriately for it. Is there a way that I can mix in memory

Remote Procedure Calls or DRDA

Does mysql support remote procedure calls or DRDA? I have partitioned my database between multiple servers and need to join two tables from two different servers (mysql instances) and I am wondering what the best method for doing this would be. Any help would be greatly appreciated! Thanks

Re: AUTO_INCREMENT problem... ER_DUP_ENTRY? (No, it's not a one byte index :)

On Jun 18, 2004, at 5:31 PM, Scott Haneda wrote: While I do not know why, I would suggest you simply drop the PK and recreate it, this should be a whole lot faster than the alter. This took the same amount of time as the alter table (a little longer actually). The documentation says that in

Re: How do you deal with URL's?

At 22:38 -0300 on 06/21/2004, Sergio Salvi wrote about Re: How do you deal with URL's?: Separate data from how it's displayed. I mean, create a table called states with the fields state_id, state_name and state_url. Put the data in the according field: state_id state_name state_url 1 Alabama

Re: MySQL Web Clustering...

Apparently OpenMosix won't work with MySQL because MySQL uses Shared Memory. There is apparently a component called MAASK which might help. Roy Nasser wrote: Hi All, We have recently acquired some new machines for our ASP service, and I am investigating different options and setups to optimize

Re: Performance issues

At 12:34 PM 6/22/2004, you wrote: We have an internal SNMP monitoring system that is monitoring about 10,000 devices. Each device is pinged then pulled for about an average of 25-30 elements. Each of the ping results and elements are then stored in text file, then another system picks them

Some BLOB help please.

Hello All, I have been reading hundreds of posts and sites for information regarding BLOBs in MySQL. So far I have not found an answer to my question, so I pose it to you fine people. :) A little background first. I have a web server that uses PHP to retrieve documents in PDF format for

Re: Some BLOB help please.

Justin Crone wrote: snip So as I said, I am rather pleased with the performance and the ease at which I was able to get this up and running. However The problem is I do have limits, and one of those being disk space. Those 10,000 files are taking up 21 GB of space in the database. However