Re: How to use Like Clause in Perl? Works fine in MySQL control center!
At 07:03 PM 7/22/05, Siegfried Heintze wrote: I'm having trouble getting the like clause to work. It seems to work fine in the MySQL Control Center 9.4.beta. I'm using MySQL 4.0.23-debug. use DBH; my $sth = DBH-prepare(SELECT 'David!' LIKE '%D%v%'); $sth-execute(); my $row; print join(@$row,,).\n while ($row = $sth-fetch); This does not print a 1 in perl. It just prints a ,. join(,,@$row) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which Engine to use...
At 09:39 AM 7/21/05, Scott Hamm wrote: M$ SQL executed and brought up result in 2 seconds where MySQL took 801 seconds and where Batch datalength is around 18.5 MB, QAErrors is around 464KB and QA is around 3.5MB Did you create an index? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: offer a solution ?
At 01:49 PM 7/2/05, nephish wrote: i am writing a database to track what a bunch of electric monitors are doing. the status of the monitor changes almost daily. i need access to each monitor, when it changed, and i also need to track its history. Easy enough. but if i update a row in a table, i loose the old info. So i kinda cannot create a table referenced by a key of monitor number... i think that the easiest way, would be to create a seperate table for each monitor. Add a column for date changed instead. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: offer a solution ?
Actually, there should actually be two tables. One for columns that are static information about the monitor; the other with information that changes. should be in a separate table. OnlAt 12:30 PM 7/2/05, Jim McAtee wrote: No, you don't want a table for each monitor. One table for the data will be _much_ easier to work with. If you want a history then you need to insert a new record for each datapoint that you get, with, as Frank suggested, a date/time column. How large your table will get will depend on the number of monitors and how often you retrieve data. Depending on how much history you need to keep, you can periodically trim the table by deleting all data older than N days (or hours, months, years). - Original Message - From: nephish [EMAIL PROTECTED] To: mysql mysql@lists.mysql.com Sent: Saturday, July 02, 2005 11:49 AM Subject: offer a solution ? Hey there, i have been messing around with MySQL for a little bit now. I have a question about how i might could do something. i am writing a database to track what a bunch of electric monitors are doing. the status of the monitor changes almost daily. i need access to each monitor, when it changed, and i also need to track its history. Easy enough. but if i update a row in a table, i loose the old info. So i kinda cannot create a table referenced by a key of monitor number... i think that the easiest way, would be to create a seperate table for each monitor... but there are almost a thousand monitors... will that become a nightmare ? can MySQL handle that kind of thing? a thousand tables in one database ? i have to be able to access each change in its history. how should i set this up? thanks for any suggestions. -- 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: Need help
At 09:01 AM 6/18/05, madderla sreedhar wrote: Isn't MySql supports large amounts of data to be stored in databases. What is the maximum number of records that can be handled or stored in Mysql. Is there any limit. If i want to store large amounts of data then is it necessary to migrate to another database. Please reveal this. You could try reading the online manuals: http://dev.mysql.com/doc/mysql/en/index.html 1.4.4. How Big MySQL Tables Can Be 11.5. Column Type Storage Requirements The maximum number of rows is limited only by maximum table size and size of your hard disk. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a query..
At 03:09 AM 6/16/05, Cory Robin wrote:
I need to speed up a search, big time.
I have an application that searches for records on a date field. If it
doesn't find an exact date match, it keeps searching adjacent days until it
finds a certain amount of records.
The problem now is, I'm using my application to loop through and run
multiple queries and it's dog ass slow..I'm hoping that one of you SQL
gurus can point me in the right direction to create a query that will work
it out for me. Here's the logic the best I can explain..
I want to return a minimum of 15 records.. I'm searching for records on or
around 2005-10-01
Select * from table_x where row_date = '2005-10-01'
/* at this point if matched records are = 15 then simply return the records
on that date.. If not..*/
Select * from table_x where row_date = '2005-09-31'
Select * from table_x where row_date = '2005-10-02'
And so on until it finds = 15 records or it searches through 5 days (+- 3
on search date)
I hope this makes sense.. I'm new to all this stuff.
Eventually I'm going to do the same thing for times as well..
Thanks in advance for any help!
Plus/minus 3 days makes a total of 7 days, not 5 days. I would try
something like:
Select * from table_x where row_date between '2005-09-28' and '2004-10-04'
order by abs(datediff('2005-10-01',rowdate) LIMIT 15;
It is not clear if you are looking for
A) one day with 15 records
B) the 15 records closest to your target date.
C) the 15 records closest to target date, plus all records for dates in
that result of 15 records.
My example query does (B). If you want (A) or (C), lease out the LIMIT
clause and do that code in your application - might also be possible with
subselects, but let's make sure that;s what you want before going
there. Knowing what version you have is crucial, because availability of
date/time functions varies widely in currently available releases.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to load a remote db with lots of data?
At 10:00 AM 6/13/05, Brian Dunning wrote: I have to load my remote MySQL db's with about a gig of data - phpMyAdmin only allows me to upload a 15MB CSV file, so I have to painstakingly separate my data into 15MB chunks and upload them one at a time. It's a huge pain and takes about two entire days. Is there a better solution?? Can phpMyAdmin load a file that is *already* on the server? FTP the 1G CSV file directly to the hosting server, then use phpMyAdmin to load it into database. Otherwise use command line to load the file into database. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to load a remote db with lots of data?
At 10:36 AM 6/13/05, Brian Dunning wrote: On Jun 13, 2005, at 7:18 AM, Berman, Mikhail wrote: How about an FTP service on your remote server? No - I actually don't have any remote access directly to the MySQL server. My ISP has separate machines for the database servers and the web servers - I can do a lot with my web server, but I don't have remote access to the MySQL server. I can telnet into the web server, and from there I may be able to access the MySQL server Once you telnet into the MySQL server you might be able to use FTP to pull the file from another server; unzip it; then load it into database. - but frankly when you mention telnet or shell I'm already in over my head. :( Good time to learn a few basics... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE - still confused
Wrong path, you are referring to an uri, not a path. Way off topic to starting explaining basic file system stuff here. You should be the same path you used when your uploaded the file. Something like: /home/chris/datafile.txt Frank At 10:06 PM 6/7/05, Chris wrote: Well, in fact I have read the documentation several times before posting this note. My problem arises because I don't know what is meant by full file path. If you mean: 'http://www.mydomain.com/datafile.txt' that produces the error: Can't get stat of 'http:/www.mydomain.com/datafile.txt' (Errcode: 2) Also, using a php pre-defined variable such as $_SERVER['DOCUMENT_ROOT'] creates the same error. Oh, yes I do know about file permissions in the entire path. If I only knew how to find the path, life would be much easier. Frank Bax [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] At 03:59 PM 6/7/05, Chris wrote: I have a simple php script which runs the following query: LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' which generates the error: File './mydabasename/datafile.txt' not found (Errcode: 2) Didn't bother to read the manual did you? http://dev.mysql.com/doc/mysql/en/load-data.html file named as myfile.txt is read from the database directory of the default database Specify the full path to your input file. Life will be much easier. -- 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: LOAD DATA INFILE - still confused
At 03:59 PM 6/7/05, Chris wrote: I have a simple php script which runs the following query: LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' which generates the error: File './mydabasename/datafile.txt' not found (Errcode: 2) Didn't bother to read the manual did you? http://dev.mysql.com/doc/mysql/en/load-data.html file named as myfile.txt is read from the database directory of the default database Specify the full path to your input file. Life will be much easier. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem loading dates from txt file
At 05:23 AM 5/31/05, James M. Gonzalez wrote: Im loading rows from a txt tab separated fields file into a MySQL table. It mostly works, but the date format in one field is not the type that MySQL likes. So right now Im loading it as a char field. Yes, it is ugly and a pain to work with. I would like to some how automate it and change the time format. From - 30-May-05 To - 2005-05-30 The txt file is initially generated from an excel sheet. Im using: MySQL 4.1.11 on Red Hat 9 with kernel 2.4.20 Any ideas? Change the format of column in Excel before exporting the data. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using MySQL to store/archive code/html etc?
At 02:46 PM 5/27/05, zzapper wrote: Today I designed a fairly cute error404.php page, which I'd like to reuse in future. Currently I rely on remembering that I created such a page for say ACME Carpets. Now and again my memory fails to remember which site I developed a piece of HTML,PHP,Perl,Javascript for and I spend more time going thru CD backups etc than it would take to start again. I've postulated using MySql as a store for such pages. but is a database suitable for storing, searching for and retrieving such information.??? No. Store all the files on a hard disk and use 'grep' to search for what your looking for - it'll be much easier to maintain. If you can't remember to keep all you code in one place, what are the chances you are going to remember to insert it into a database? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficiently finding a random record
At 12:54 PM 5/13/05, Brian Dunning wrote: I have a db of about 300,000 records and when I try to find one random record like this: select * from table order by rand() limit 1; it can take several minutes. My Sherlock Holmes instincts tell me that what I'm doing is somehow inefficient. What is the primary culprit here? Sherlock Holmes would have checked the archives first! There was a discussion about this a couple of weeks ago - Apr.26 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Solution to slow queries
At 02:22 PM 5/10/05, Paul Halliday wrote: Now, as time progresses the queires are getting slower and slower. I know this is expected, I don't think so. I thought that if the number of rows returned does not change and an index is properly used, then query time should not change significantly as size of database grows. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hardware requirements
At 10:17 AM 5/6/05, Berta Alcala Larramendi wrote: I'm doing an University project and I need to buy a server for a business. I have to simulate an enterprise that sells by Internet. There are many clients and products in the Data Base and we use MySQL in a Linux OS. I need to find as much information as possible about the hardware requirements like number of processors, necessary memory, cache, HD... to use in a high performance MySQL server With an open-ended question like yours, your going to get open-ended answers. I am running MySQL on several machines. One of those machines has a single P2 processor, 64M and 4G hard drive - it provides excellent performance to its many clients. To get reasonable proposals, you need to provide info like how big the database is and how frequently it will be queried. It is interesting to note that you already decided on MySQL and Linux. This platform is not the answer to every environment; like those that require features only available in beta releases. Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
At 11:07 AM 4/27/05, Jigal van Hemert wrote: So, if we would define that the key entry 0-NULL-Whatever equals 0-NULL-Whatever (which MySQL is capable of if you look at the definition of UNIQUE indexes with BDB tables) then allowing NULLs as part of a key entry would not permit duplicate entries. At least not more than allowing other values. You cannot define that those keys are equal, because SQL standard states that 0-NULL-Whatever is *always* *not-equal* to 0-NULL-Whatever. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: why NOT NULL in PRIMARY key??
At 08:49 AM 4/26/05, Jay Blanchard wrote: [snip] http://dev.mysql.com/doc/mysql/en/silent-column-changes.html mentions that Columns that are part of a PRIMARY KEY are made NOT NULL even if not declared that way. And http://dev.mysql.com/doc/mysql/en/create-table.html tells me that A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). Why is this? I actually do need NULL values (they really are not equal to '0', etc.), but also need this column as part of the PRIMARY key in an InnoDB table... It can't have anything to do with the 'uniqueness' of the data, since I can have a lot of 'zero'-values in the column, as long as the combination of columns in the PRIMARY key results in unique values. [/snip] Since NULL is the absence of a value and PRIMARY keys must have a value a NULL column cannot be included as a portion of a PRIMARY key. AFAIK this is the case with every RDBMS out there. Asking the development team might get you a more informative response. I'm not on dev team, but my understanding of RDMS theory is that retrieving data via PK will always return a single row from the table. Since NULL values are never considered equal to each other, allowing them in a column that is part of PK would bypass this expected behaviour. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about chunking sql uploads
At 10:44 AM 4/25/05, Art.M (Wikki) wrote: I have a large .sql file to upload which is about 9 mb and I was wondering if anyone knew of a program that could break it up into chunks of 2 mb or under? So I can upload it to a shared web server. You can't upload a 9M file to webserver? But you can upload 9x1M files? Fix your webserver. You didn't google for this did you? You also didn't mention OS, so we'll assume unix based. You could try compressing file with zip, gzip, etc. Or simply use split, default is 1000 lines, but can be changed via command line. man split -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about chunking sql uploads
At 02:07 PM 4/25/05, Scott Gifford wrote: [EMAIL PROTECTED] writes: Frank Bax [EMAIL PROTECTED] wrote on 04/25/2005 11:47:12 AM: Or simply use split, default is 1000 lines, but can be changed via command line. That's a start, but the files each need to be a valid SQL statement, so that's not enough; a little fixup needs to happen at the beginning and end of each file. OP did not say that the SQL statements crossed over a newline; but if they do, it is a trivial sequence of shell commands to fix them so that they are one line each. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Write to a mysql table from Excel
At 03:12 PM 4/22/05, Jay Blanchard wrote: [snip] Thank you for your reply. Can you provide more details on how to write to a MySQL table from Excel? I am a newbie to MySQL. Thanks. [/snip] You must be new to mailing lists too... http://catb.org/~esr/faqs/smart-questions.html You can save the excel as a CSV file and import that into MySQL, you can write a program that will do it for you using any one of several programming or scripting languages, you can use an ODBC connection. Don't be so hasty to criticise. I took the original question to mean While I have an existing spreadsheet open in EXCEL, can I cause a selected row in a MySQL table to be updated?. This is quite a different question than How can I convert one sheet from an EXCEL file into a new MySQL table?. The later can easily be done several ways as you suggest, the former cannot. Can the former be done with ODBC? I didn't find any answers on google. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL to list table names
At 11:22 AM 4/21/05, Darryl Hoar wrote: I am running Mysql 3.23. Is there an sql statment that will allow me programmatically to retrieve the names of the tables in a database ? show tables; Yes, this is an SQL statement! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie trying the impossible?
At 03:20 PM 4/21/05, Scott Hamm wrote: However when I used left join (trying to learn it) I issued this command: SELECT QA.OperatorID, QA.QAID, QA.BrandID, QA.Batch, QA.KeyDate, Batch.[Order], Batch.Errors, Batch.Comments FROM QA Left Join Batch ON (Batch.QAID=QA.ID) WHERE ID='77363'; How do I get around to it with 2 different names that uses SAME table? QA.OperatorID (operator) QA.QAID (reviewer) Or am I asking for the impossible? It's not impossible, but I don't think you've provide enough info to provide an example with your data. The left join is not the problem. What you need is to join the same table twice - to do this properly you need to give each reference to that table an alias. So lets say your names are in a table called names with fields: id and name. Then add this to your SQL: join names QAO on QAO.id = QA.OperatorID join names QAR on QAR.id = QA.QAID -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: which one is faster
At 09:22 AM 4/15/05, DebugasRu wrote: taken from the manual 3.6.2 The Row Holding the Maximum of a Certain Column which of the two queries will in general be faster: 1) SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop); 2) SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1; or does it all depend on a given situation ? As mentioned in the manual, the two queries are not always going to return identical results. That should be a clue that results depend on given situation, but my guess is that second one will faster more often than the first. The first query is expected to get slower as number of rows returned increases. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Computer reboots during long table join
At 11:21 AM 4/15/05, Ian Gibbons wrote: On 15 Apr 2005 at 9:47, mos wrote: I'm running MySQL 4.1.10 on XP and I was doing a 2 table equi join join on a date field that was indexed. There are It was taking quite a long time so I let it run overnight. There are 200 million rows in the first table, but a Where clause on the second table would have reduced this to around 50 million rows. This morning I got in and the computer had rebooted itself sometime during the night. (This is usually not a good sign before the first cup of coffee.g) There were no errors in the MySQL.Err log file. Explain said they were using an index for both tables. 1) Can MySQL join two tables of this size? 2) Is there a problem using a Date join? I noticed before doing date joins was extremely slow, even when indexed. 3) Is throwing more RAM at the problem going to help? Is there any way of knowing how much RAM is enough? Hi, The first thing to do is turn off that anoying XP *feature* of rebooting when there is an error - most of the time you don't need it. I think the option is in My Computer Properties somewhere. Mysql may get a chance to print an error message then, or it will be displayed on screen. There is not enough evidence (except circumstantial) in the original post to blame Mysql for the reboot. Disabling System Failure - Automatic Restart will still help in problem determination. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot execute query - Can't find file: (error: 9)
It seems the bad file number error was caused by MySQL trying to have more than 128 files open at one time. This was fixed by changing MySQL startup, but the fix would not have been needed if MySQL were to close open files are some period of no activity to a table - does MySQL ever close a file? If I create a table and run a select on it, then there is no access to that table for months (and MySQL is not shutdown or restarted), does MySQL still have the file open? Yes, I ran myisamchk and there are no problems with database. Frank At 02:36 PM 4/6/05, V. M. Brasseur wrote: According to perror: perror 9 Error code 9: Bad file number This is an operating system error code: http://dev.mysql.com/doc/mysql/en/operating-system-error-codes.html Out of curiosity, have you tried running myisamchk or some CHECK TABLE commands yet? http://dev.mysql.com/doc/mysql/en/table-maintenance.html Cheers, --V Frank Bax wrote: At 10:07 AM 4/5/05, Frank Bax wrote: At 04:27 PM 4/4/05, Frank Bax wrote: Cannot execute query. snip my SQL statement Can't find file: './donor/list_lst.frm' (errno: 9) - - I got the same error last week on a different table. Today I notice that there is a table in another database on same system producing the same error. I attempted to access mysql cli, but it just locked up after entering password. Start/stop mysql and mysql cli at least started but issued errors about some tables even before I entered a command. I decided to reboot and the problem goes away (for a while). MySQL 4.0.20 - OpenBSD 3.6 Forgot to mention a couple of things: 1) The file that mysql complains about does exist. # ls -ltr donor/list*.frm -rw-rw 1 _mysql _mysql 8694 Jan 15 09:43 donor/list_lst.frm 2) When problem recurs (as it did on both databases this morning), the same file in each database is affected each time error appears. 3) In both databases (on same system) it is frm files in the error message. Since OpenBSD 2.8, there is a default limit of 128 open files for daemon processes. Add --open-files-limit=2048 to mysql startup. http://dev.mysql.com/doc/mysql/en/openbsd-2-8.html The suggested changes to /etc/login.conf were not necessary on my system. Does MySQL ever close the file(s) associated with table(s), or once open do they stay open until shutdown? Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot execute query - Can't find file: (error: 9)
At 10:07 AM 4/5/05, Frank Bax wrote: At 04:27 PM 4/4/05, Frank Bax wrote: Cannot execute query. snip my SQL statement Can't find file: './donor/list_lst.frm' (errno: 9) - - I got the same error last week on a different table. Today I notice that there is a table in another database on same system producing the same error. I attempted to access mysql cli, but it just locked up after entering password. Start/stop mysql and mysql cli at least started but issued errors about some tables even before I entered a command. I decided to reboot and the problem goes away (for a while). MySQL 4.0.20 - OpenBSD 3.6 Forgot to mention a couple of things: 1) The file that mysql complains about does exist. # ls -ltr donor/list*.frm -rw-rw 1 _mysql _mysql 8694 Jan 15 09:43 donor/list_lst.frm 2) When problem recurs (as it did on both databases this morning), the same file in each database is affected each time error appears. 3) In both databases (on same system) it is frm files in the error message. Since OpenBSD 2.8, there is a default limit of 128 open files for daemon processes. Add --open-files-limit=2048 to mysql startup. http://dev.mysql.com/doc/mysql/en/openbsd-2-8.html The suggested changes to /etc/login.conf were not necessary on my system. Does MySQL ever close the file(s) associated with table(s), or once open do they stay open until shutdown? Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot execute query - Can't find file: (error: 9)
At 04:27 PM 4/4/05, Frank Bax wrote: Cannot execute query. snip my SQL statement Can't find file: './donor/list_lst.frm' (errno: 9) - - I got the same error last week on a different table. Today I notice that there is a table in another database on same system producing the same error. I attempted to access mysql cli, but it just locked up after entering password. Start/stop mysql and mysql cli at least started but issued errors about some tables even before I entered a command. I decided to reboot and the problem goes away (for a while). MySQL 4.0.20 - OpenBSD 3.6 Forgot to mention a couple of things: 1) The file that mysql complains about does exist. # ls -ltr donor/list*.frm -rw-rw 1 _mysql _mysql 8694 Jan 15 09:43 donor/list_lst.frm 2) When problem recurs (as it did on both databases this morning), the same file in each database is affected each time error appears. 3) In both databases (on same system) it is frm files in the error message. Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cannot execute query - Can't find file: (error: 9)
Cannot execute query. snip my SQL statement Can't find file: './donor/list_lst.frm' (errno: 9) - - I got the same error last week on a different table. Today I notice that there is a table in another database on same system producing the same error. I attempted to access mysql cli, but it just locked up after entering password. Start/stop mysql and mysql cli at least started but issued errors about some tables even before I entered a command. I decided to reboot and the problem goes away (for a while). MySQL 4.0.20 - OpenBSD 3.6 Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: create sequence
You obviously do not understand auto-increment capability. You wouldn't get that value of the sequence, because you would always retrieve the same value. You would have to add a row to the 'extra' table for the auto-increment field to work as designed, then use the highest value of auto-increment field in your other tables. You end up with as many rows in your 'extra' table as you have sequences used in your other tables. There are probably plenty of other flaws with such an implementation. Of course, had you bothered to search for sequence mysql on google, you would have found the first hit is a page at dev.mysql.com that contains an example of how to simulate sequence in mysql. At 01:05 PM 1/13/05, Scott Purcell wrote: I understand the auto-increment capability, but how would one share it amount three different tables? Would one have to do a rig, and create an extra table with increment in it, then get that value to update table 1, then repeat process again to get new increment and put it into table 2, etc.? I am used to asking for the sequence.nextId, then using it in any table I need. Am I missing something here, or does someone know how to handle this in multiple tables? Scott -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Thursday, January 13, 2005 11:38 AM To: Scott Purcell Cc: mysql@lists.mysql.com Subject: Re: create sequence You want AUTO_INCREMENT, http://dev.mysql.com/doc/mysql/en/example-AUTO_INCREMENT.html. PB - Scott Purcell wrote: Hello, I would like to create a sequence object in mysql that I could use in multiple tables through a application I am developing. Could someone please send my the syntax of how to create a simple sequence that starts at 1 increments by 1. thanks, -- 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: Why DATETIME takes 8 bytes?
At 06:00 AM 1/8/05, Ehud Shapira wrote: I don't understand why DATETIME takes 8 bytes. It's just a waste, since DATE+TIME take 6 bytes. And in fact, while DATE and TIME are each rounded up to bytes on its own, a combined DATETIME should only take 5 bytes: 14 bits for year 04 bits for month 05 bits for day 05 bits for hour 06 bits for minutes 06 bits for seconds --- 40 bits datetime is displayed as -MM-DD HH:MM:SS - it is *not* stored that way. It is stored as a *nix timestamp - an integer number of seconds since 1970-01-01 00:00:00. A 4-byte integer field has historically been used on *nix systems for this purpose, but this has an upper limit of 2038. A larger *nix timestamp is now used to avoid the equivalent of Y2K in 2038. The 8-byte *nix timestamp accommodates micro-seconds. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why DATETIME takes 8 bytes?
At 12:15 PM 1/8/05, Tom Crimmins wrote: [snip] datetime is displayed as -MM-DD HH:MM:SS - it is *not* stored that way. It is stored as a *nix timestamp - an integer number of seconds since 1970-01-01 00:00:00. [/snip] Actually datetime is not stored as epoch time. It has a range from 1000-01-01 00:00:00 to -12-31 23:59:59 because it is a combination of a date and a time field as Neculai wrote. You may be thinking of a timestamp, which is tored as a 4 byte int. Thanks for correction. The lower limit on my 4.0.20 system appears to be 0001-01-01 00:00:00. Also, 4.0.20 appears to accept invalid dates, such as those with zero for year, month or day. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select datetime older than X weeks
I'm using OpenBSD 3.6 (latest version) which comes with binary packages for MySQL 4.0.20 - More recent binary packages are not yet available for this platform and installation from source is not an option. I have a table with datetime field and I would like to select all data older than X weeks, where X is a variable in my php script. DateDiff(datetime,now()) looks like its the right function for this purpose, but this function requires date arguments and date() isn't in 4.0.20 to convert my field from datetime to date. I have tried many different combinations, only to find a function isn't on my system or my data is not the proper type for functions that I do have. Can anyone suggest a workable SQL query for this situation? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using SQL query to populate a html form select field
At 07:52 PM 6/25/04, Kyle Texan wrote:
I want to take 1 field in a mysql table and use that
information to populate an html form select field
instead of writing the html code, that way when data
changes in that mysql field the form will always be in
sync with the table?
You need to use a programming language like PHP. You would code a SELECT
to retrieve your data and dump it into an array. Then use a PHP statement
like:
echo FormSelect( 'field', $array, '22' );
Where:
- 'field' is the name of field within form
- $array contains values from database
- 22 is default value, if any
function FormSelect( $name, $adata, $val ) {
$str = 'select name=' . $name . ' size=1 ';
foreach($adata as $k=$o) {
$str .= 'option value=' . $k . '';
if( $k==$val ) $str .= ' selected ';
$str .= '' . $o . '/option' . \n;
}
$str .= /select;
return( $str );
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Populating a SELECT from a database
Might I suggest separating the mysql calls and html code into separate
functions by storing selections in an array first. Then you can more
easily manipulate the array for 'none' between the two function calls. The
function generating html code could then be called with data not
originating from your database.
At 10:13 AM 6/26/04, Andrew Pattison wrote:
I use this method also, but I usually put a --NONE-- item in at the top of
the listbox so the user has the option of selecting none of the items in the
list. This may or may not be required for your application.
Also, you can get the select box to automatically have the value that the a
given field contains chosen from the list of items by using the 'selected'
HTML attribute. By way of an example, the function below will show a list of
filenames stored in my database and have the one that I have selected
elsewhere in the system automatically shown highlighted in the list. It also
shows how to add the '--NONE-- item a thte top of the list:
function filelist($file) {
$result=mysql_query(SELECT ID,title,type FROM files);
echo BR;
echo select name=\file\option value=\0\---NONE---/option;
if (mysql_num_rows($result)==0) {
//no files
} else {
while ($row=mysql_fetch_array($result)) {
$ID=$row[ID];
$title=$row[title];
$type=$row[type];
if ($row[ID]==$file) {
echo option selected value=\$ID\ . $type . - . $title .
/option;
} else {
echo option value=\$ID\ . $type . - . $title . /option;
}
}
}
echo /selectBR;
}
Note that unless one of the list items has the 'selected' attribute set,
then none of the items in the list will be shown as the chosen one in the
list - it will just be blank when the user opens the page.
Cheers
Andrew.
- Original Message -
From: David Rodman [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, June 26, 2004 6:44 AM
Subject: Populating a SELECT from a database
Here's a way to do it with PHP:
function field_select($table, $field)
{$result = mysql_query(SELECT $field FROM $table) or
die(mysql_error());
print SELECT NAME=\ . $field . \\n;
$limit = mysql_num_rows($result);
for($i = 0; $i $limit; ++$i)
{list($value) = mysql_fetch_array($result);
print OPTION VALUE=\ . $value . \$value/OPTION\n;
}
print /SELECT\n;
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN or UNION or both?
At 10:56 AM 6/24/04, Matthias Kritz wrote: I have the following table structure: tbl_speakers sid fname lname tbl_presentations pid name desc tbl_speakers_presentations sid pid I would like to display all presentations, (but each only once), and display the speakers' names for each presentation. With my current syntax (see below), if a presentation has, say, 3 speakers I receive 3 distinct records - I just want 1 with 3 speakers columns. What you are asking for is actually quite complex - my first reaction was to suggest a search through archives for pivot table which is the term for making rows into columns. A pivot table would give you three columns for three speakers but your SQL has to be coded for the maximum number of speakers you expect. Also, in this example, a pivot table would still produce three rows, each one with a different speaker in a different column. If you must have *one* row, then you'll need aggregate functions to consolidate the rows. Most examples I've seen of pivot tables are working with numeric fields, so SUM() works quite nicely. I'm not sure what to do about strings though. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fetching 12 columns or 1 TEXT field?
At 03:29 PM 6/23/04, Eamon Daly wrote: Which do you folks think is faster: randomly accessing a table with a primary key and a dozen CHAR columns or a table with a primary key and a single merged TEXT column? The data in the 11 extra columns will always be fetched as a single request. Both the same - with extremely minor variance. I rolled my own benchmarking program and 10,000 runs each came out just about equal, which surprised me a little. Wow. I'm right - what's the prize? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concatenating tables
At 03:14 PM 6/15/04, Tim Johnson wrote: We are importing data from 3 CSV files. Documentation regarding the originating database is for the most part, not available to us. Translating directly from CSV to .sql files, we can see that the three files are really the first, second and third parts of one huge table and could be merged into one. This would simplify queries for us. We realize that eventually, normalization should be applied, but for the interim, can we expect a performance penalty if we concatenate the the 3 tables into 1. Taken as a sum, we should have about 330 columns and 5000+ rows. Comments would be appreciated. Pointers to documentation on this subject are also quite welcome. Are you saying: each CSV contains about 110 columns for the same key values - or - each CSV contains 1700+ rows for the same 330 columns. - or - something else? In the first case, I would consider loading three tables, then using sql to join the data by key values and dump it into a new table with all 330 columns. Then delete the three temp tables. In the second case, I would concatenate the CSV files, then import them into a table of 330 columns. What performance penalty are you expecting with either of these scenarios? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
root password problem
According to the docs, one of the first things I'm supposed to do is give
root a password:
shell mysql -u root mysql
mysql SET PASSWORD FOR [EMAIL PROTECTED]('new_password');
I did that and now I get:
# mysql --user=root --password=new_password
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
#
I know I'm typing my password correctly, because I can see what I typed in
~/.mysql_history
Now what do I do?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: root password problem
At 08:25 PM 6/8/04, Frank Bax wrote:
According to the docs, one of the first things I'm supposed to do is give
root a password:
shell mysql -u root mysql
mysql SET PASSWORD FOR [EMAIL PROTECTED]('new_password');
I did that and now I get:
# mysql --user=root --password=new_password
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
#
I know I'm typing my password correctly, because I can see what I typed in
~/.mysql_history
Now what do I do?
insert a \ before special characters (like $) in password.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
