Re: Large table delete from ...
Dear all, I have some large MySQL tables and II want to delete for example month 2-2002 entirelly from an sql table. I use now ... delete from table_name where year(data)=2002 and month(data)=2; I have almost 25.000.000 row and something like 7-8 mil is from month 2. Seems to make it for 2 hours and it's very much as time, I need other tables as well to clean up and then need to stop too much from clients the database. Can be do this in other way? Also if there a possibilities to export and delete this records?? Regards, Valics Lehel - 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 except
On Sun, 19 May 2002, Jule wrote: Hey guys and gals, is there a query that selects all comuns from a table, except the one's i have defined? Jule No, there isn't. It is safter if you name the columns you want. If you use a select * in your code instead of naming the particular columns you want, you could be in for some unpleasent surprises if the table is altered in the future. Naming the columns also makes your code easier to understand. Regards, Jason [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
HELP - Insight into dates, please
Excuse this old ACCESS/ CLARION hack for asking such a basic question,
but the mySQL docs have given me a head-ache :-)
I want to move an ACCESS database to mySQL. I am writing the Code in Delphi.
My problem is assigning the date values in the SQL INSERT INTO string.
In the system I'm using dates are returned as REAL numbers and generally
I would format it to suit the occasion, ie
formatdatetime('-mm-dd',myDate.value).
So, in mySQL
sql := 'INSERT INTO myTable SET myDate = ' + formatdatetime(datemask
,myDate.value);
Looking at the mySQL docs AFAI can work out datemask should be
-mm-dd but this doesn't work.
what is the expected 'datemask'? Or have I got wrong?
More importantly are there functions in mySQL I can call to convert a
standard date, ie mmdd to the expected mySQL value?
Any help to get me over this confusing hump is appreciated.
--
Pierre du Parté
Final Filer Software
349 Worrigee Road
Worrigee, NSW, Australia 2540
http://www.finalfiler.com
Phone 61 2 44216374
Mobile 0413 483 066
If it feels good, do it!
-
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: RE: ECS Error with 4.0.07 opening DB (still) again -EMEA2490
Hi Monty,
venu == venu [EMAIL PROTECTED] writes:
cut
venu MySQL/MyODBC does accept connection timeout. Here is the code
venu snippet from driver:
venu case SQL_ATTR_CONNECTION_TIMEOUT:
venu DBUG_RETURN(mysql_options(dbc-mysql,
MYSQL_OPT_CONNECT_TIMEOUT,
venu (const char *)((SQLUINTEGER)ValuePtr)));
venu break;
venu So, driver does set correctly. Make a note that, the
value '0' means
venu default in ODBC, and that means no timedout.
Venu, the above code is slightly wrong:
To be 100 % correct, it should be:
case SQL_ATTR_CONNECTION_TIMEOUT:
{
uint timeout_argument= *(SQLUINTEGER*) ValuePtr;
DBUG_RETURN(mysql_options(dbc-mysql, MYSQL_OPT_CONNECT_TIMEOUT,
(const char *) timeout_argument));
}
Small update ..
This is true if ValuePtr is a SQLUINTEGER *, but it is
SQLUINTEGER.
Here is the spec contents for this one ..
An SQLUINTEGER value corresponding to the number of seconds to
wait for any request on the connection to complete before returning
to the application. The driver should return SQLSTATE HYT00 (Timeout
expired) anytime that it is possible to time out in a situation not
associated with query execution or login.
If ValuePtr is equal to 0 (the default), there is no timeout.
The SQLSetConnectAttr will get the SQLUINTEGER value as:
retcode = SQLSetConnectAttr(hdbc, SQL_ATTR_CONNECTION_TIMEOUT,
(void*)5, 0);
So, the possible option could be the caller(DM/App) is not passing
the correct data to the driver.
Richard, can you please send us the log file for this, so that
it will enable what is happening. I just even tested the behaviour
and it just works fine.
Regards, Venu
--
For technical support contracts, go to https://order.mysql.com
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Mr. Venu [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer
/_/ /_/\_, /___/\___\_\___/ California, USA
___/ www.mysql.com
-
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: random order by id
Jule [EMAIL PROTECTED] writes: Hey guys, i have a db and a table with id and questions now i want these questions to be listed in a random order, is there a way to format my SQL query or do i need some PHP work to? SELECT foo FROM bar ORDER BY rand(); Just to pick one: SELECT foo FROM bar ORDER BY rand() LIMIT 1; -- Per Andreas Buer - 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
Possible Bug in UPdATE in MySQL 4.0.1 alpha
Description: Possible Bug in UPDATE in MySQL 4.0.1 The following is the message in the error log... Number of processes running now: 0 020519 04:55:30 mysqld restarted 020519 4:55:30 InnoDB: Started /usr/local/mysql/bin/mysqld: ready for connections mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402649088 record_buffer=268431360 sort_buffer=268435448 max_used_connections=0 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 2489963 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Stack range sanity check OK, backtrace follows: 0x807db7f 0x823d64a 0x8204447 0x821bbd6 0x820bb41 0x80d082f 0x80b0479 0x8086de7 0x808a262 0x8084e57 0x808a694 0x8084296 Stack trace seems successful - bottom reached Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow instr uctions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x8423db0 = UPDATE `Physical Examination Report` SET `History of P resent Illness`='moo\r\nfoo\r\nboo.' WHERE `ID Code of Appointment`=27 thd-thread_id=7 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 7 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid. The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 020519 05:14:17 mysqld restarted 020519 5:14:17 InnoDB: Started /usr/local/mysql/bin/mysqld: ready for connections How-To-Repeat: Did this query on a database called 'medical' below text UPDATE `Physical Examination Report` SET `History of Present Illness`='moo\r\nfoo\r\nboo.' WHERE `ID Code of Appointment`=27 DUMP of medical database schema: # phpMyAdmin MySQL-Dump # version 2.2.6 # http://phpwizard.net/phpMyAdmin/ # http://www.phpmyadmin.net/ (download page) # # Host: localhost # Generation Time: May 19, 2002 at 05:50 AM # Server version: 4.00.01 # PHP Version: 4.0.5 # Database : `medical` # # # Table structure for table `Appointment Types` # CREATE TABLE Appointment Types ( ID Code int(11) NOT NULL auto_increment, Description mediumtext NOT NULL, PRIMARY KEY (ID Code) ) TYPE=MyISAM COMMENT='Keeps Information unique to appointment type'; # # # Table structure for table `Appointments` # CREATE TABLE Appointments ( ID Code int(11) NOT NULL auto_increment, Date of Appointment date default NULL, ID Code of Patient int(11) default NULL, ID Code of Doctor int(11) default NULL, ID Code of Referer int(11) default NULL, ID Code of Second Referer int(11) default NULL, ID Code of Third Referer int(11) default NULL, ID Code of Fourth Referer int(11) default NULL, Type of Appointment int(11) default NULL, Complete int(11) default NULL, PRIMARY KEY (ID Code) ) TYPE=MyISAM COMMENT='Keeps track of what''s involved in the appointment'; # # # Table structure for table `Doctors` # CREATE TABLE Doctors ( ID Code int(11) NOT NULL auto_increment, First Name tinytext, Middle Name tinytext, Last Name tinytext, Initials tinytext, SSN tinytext, Business Street tinytext, Business City tinytext, Business State tinytext, Business Zip Code tinytext, Business Phone Number tinytext, Business Fax Number tinytext, Business Email Address tinytext, Home Street tinytext, Home City tinytext, Home State tinytext, Home Zip Code tinytext, Home Phone Number tinytext, Home Fax Number tinytext, Home Email Address tinytext, PRIMARY KEY (ID Code) ) TYPE=MyISAM COMMENT='Keeps important data concerning the physicians that work her'; # # # Table structure for table `Follow Up Report` # CREATE TABLE Follow Up Report ( Report Number int(11) NOT NULL auto_increment, ID Code of Appointment int(11) default NULL, ID Code of Patient int(11) default NULL, Date of Exam date default NULL, Date of Report
Re: HELP - Insight into dates, please
Many thanks for the, Richard... You wrote: I'm not so sure what you are asking here but from this statement, More importantly are there functions in mySQL I can call to convert a standard date, ie mmdd to the expected mySQL value?. Yeah, I was a bit vague :-) But your example provided the clue- I had neglected the string delimiters :-( I had coded SET myDate = mmdd I should have coded SET mydate = 'mmdd' which worked. Again, many thanks Pierre - 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: 4.0.1 Bugs
Richard, the assertion failure below is very probably caused by the SHOW CREATE TABLE memory corruption bug which was fixed in 3.23.48, but not yet in 4.0.1. It is usually caused by mysqldump. The regularity of the crashes suggests they might be connected to mysqldumps. If the memory consumption of mysqld does not increase linearly over many days (does it?), then it is probably not a memory leak. If the crashes are connected to memory consumption you could try making the InnoDB buffer pool slightly smaller and test if the crashes occur less frequently. I will look at the UNION problem later. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Original Message - From: Richard Clarke [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Sent: Sunday, May 19, 2002 12:02 PM Subject: Re: 4.0.1 Bugs Heikki, As for my crashes. This one is a little hard, see, we have two machines that do this. Both however, display little to no information in the log. One server just says mysql restarted followed by mysql was shut down incorrectly. The second server once gave an error like this, 020426 12:26:31 InnoDB: Started /usr/local/mysql-4.0.1-alpha/libexec/mysqld: ready for connections 020511 1:09:25 read_key: Got error 146 when reading table './counter/br_type' 020516 2:27:31 read_key: Got error 146 when reading table './counter/br_type' 020518 1:25:36 read_key: Got error 146 when reading table './counter/br_type' InnoDB: Error: undo-id is 136712960 InnoDB: Assertion failure in thread 869069824 in file trx0undo.c line 1316 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16773120 record_buffer=1044480 sort_buffer=1048568 max_used_connections=190 max_connections=500 threads_connected=90 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 1038376 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 020518 01:30:27 mysqld restarted InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 589 1379690513 InnoDB: Doing recovery: scanned up to log sequence number 589 1379756032 InnoDB: Doing recovery: scanned up to log sequence number 589 1379821568 InnoDB: Doing recovery: scanned up to log sequence number 589 1379887104 InnoDB: Doing recovery: scanned up to log sequence number 589 1379952640 InnoDB: Doing recovery: scanned up to log sequence number 589 1380018176 InnoDB: Doing recovery: scanned up to log sequence number 589 1380083712 The 146 errors can be ignore, they were deadlocks ocurring in a select/insert being run simultaneously. This server only gave this signal 11 once. The other times it just did its restart/recover routine as described above. One thing I have noticed, though not something I have monitored specifically is the memory usage of mysql. When the daemon first starts it has a size of about 800megs and a res(ources) of about 700/800. Over time however the size can grow to 1gig and the res drop to around 200. We have a third mysql box which doesn't seem to crash, currently it is reporting. last pid: 47508; load averages: 0.60, 0.40, 0.25 up 47+19:17:15 08:49:53 116 processes: 3 running, 111 sleeping, 2 zombie CPU states: 21.4% user, 0.0% nice, 4.5% system, 0.0% interrupt, 74.1% idle Mem: 244M Active, 246M Inact, 107M Wired, 33M Cache, 112M Buf, 373M Free Swap: 1012M Total, 713M Used, 299M Free, 70% Inuse PID USERNAME PRI NICE SIZERES STATE C TIME WCPUCPU COMMAND 44577 mysql 31 0 1085M 79544K RUN1 34.6H 5.37% 5.37% mysql This server is also 4.0.1, though its not crashing. It does however play a different role in our system and hence it doesn't perform the same queries as the other two. I have iteratively developed the queries on the other server, all the while monitoring innodb monitor output amongst other things. I am certain no transactions are stuck or otherwise. One of the crashing systems crashed yesterday infact, the log output just said restared/shutdown incorrectly etc (no caught signals). Whether its any help or not, I don't know but here is some innodb_monitor output. - Before
Re: 4.0.1 Bugs
Richard, the assertion failure below is very probably caused by the SHOW CREATE TABLE memory corruption bug which was fixed in 3.23.48, but not yet in 4.0.1. It is usually caused by mysqldump. The regularity of the crashes suggests they might be connected to mysqldumps. If the memory consumption of mysqld does not increase linearly over many days (does it?), then it is probably not a memory leak. If the crashes are connected to memory consumption you could try making the InnoDB buffer pool slightly smaller and test if the crashes occur less frequently. I will look at the UNION problem later. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Original Message - From: Richard Clarke [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Sent: Sunday, May 19, 2002 12:02 PM Subject: Re: 4.0.1 Bugs Heikki, As for my crashes. This one is a little hard, see, we have two machines that do this. Both however, display little to no information in the log. One server just says mysql restarted followed by mysql was shut down incorrectly. The second server once gave an error like this, 020426 12:26:31 InnoDB: Started /usr/local/mysql-4.0.1-alpha/libexec/mysqld: ready for connections 020511 1:09:25 read_key: Got error 146 when reading table './counter/br_type' 020516 2:27:31 read_key: Got error 146 when reading table './counter/br_type' 020518 1:25:36 read_key: Got error 146 when reading table './counter/br_type' InnoDB: Error: undo-id is 136712960 InnoDB: Assertion failure in thread 869069824 in file trx0undo.c line 1316 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16773120 record_buffer=1044480 sort_buffer=1048568 max_used_connections=190 max_connections=500 threads_connected=90 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 1038376 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 020518 01:30:27 mysqld restarted InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 589 1379690513 InnoDB: Doing recovery: scanned up to log sequence number 589 1379756032 InnoDB: Doing recovery: scanned up to log sequence number 589 1379821568 InnoDB: Doing recovery: scanned up to log sequence number 589 1379887104 InnoDB: Doing recovery: scanned up to log sequence number 589 1379952640 InnoDB: Doing recovery: scanned up to log sequence number 589 1380018176 InnoDB: Doing recovery: scanned up to log sequence number 589 1380083712 The 146 errors can be ignore, they were deadlocks ocurring in a select/insert being run simultaneously. This server only gave this signal 11 once. The other times it just did its restart/recover routine as described above. One thing I have noticed, though not something I have monitored specifically is the memory usage of mysql. When the daemon first starts it has a size of about 800megs and a res(ources) of about 700/800. Over time however the size can grow to 1gig and the res drop to around 200. We have a third mysql box which doesn't seem to crash, currently it is reporting. last pid: 47508; load averages: 0.60, 0.40, 0.25 up 47+19:17:15 08:49:53 116 processes: 3 running, 111 sleeping, 2 zombie CPU states: 21.4% user, 0.0% nice, 4.5% system, 0.0% interrupt, 74.1% idle Mem: 244M Active, 246M Inact, 107M Wired, 33M Cache, 112M Buf, 373M Free Swap: 1012M Total, 713M Used, 299M Free, 70% Inuse PID USERNAME PRI NICE SIZERES STATE C TIME WCPUCPU COMMAND 44577 mysql 31 0 1085M 79544K RUN1 34.6H 5.37% 5.37% mysql This server is also 4.0.1, though its not crashing. It does however play a different role in our system and hence it doesn't perform the same queries as the other two. I have iteratively developed the queries on the other server, all the while monitoring innodb monitor output amongst other things. I am certain no transactions are stuck or otherwise. One of the crashing systems crashed yesterday infact, the log output just said restared/shutdown incorrectly etc (no caught signals). Whether its any help or not, I don't know but here is some innodb_monitor output. - Before
Re: 4.0.1 Bugs
Heikki, My set of queries performs about 4/5 show create table queries each hour. The purpose being to drop it then recreate it automatically. Last time I tried truncate it was as slow as delete, but that was near the begining of my development and never saw reason to retry it after show create/drop/create ... worked fine. Even though these show creates happen 4/5 hours could they be the cause of it somehow only crashing every 4/5 days? Or is it more likely that the crashes I experience are a set of bugs. Maybe one day the assertion failure crashes it, then a few days after another bug crashes it?. Near the beginning of the development I experienced problems where Mysql would crash coz it bugged out on virtual memory. Doubling the virtual memory stopped this. I don't think the error is related to this since it used to singal 11 every time when that was happening. As for the memory consumption, PID USERNAME PRI NICE SIZERES STATE C TIME WCPUCPU COMMAND 44577 mysql 31 0 1085M 79544K RUN1 34.6H 5.37% 5.37% mysql This would suggest that memory consumption has decreased (to 79megs?) yet virtual memory has bloated to 1085-79?. This is on the other server mind you. The two servers that are crashing, crashed very recently, but I think they also will begin to exhibit the virtual memory increase, real memory decrease syndrome over the next couple of days. Ric. - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: Richard Clarke [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, May 19, 2002 1:01 PM Subject: Re: 4.0.1 Bugs Richard, the assertion failure below is very probably caused by the SHOW CREATE TABLE memory corruption bug which was fixed in 3.23.48, but not yet in 4.0.1. It is usually caused by mysqldump. The regularity of the crashes suggests they might be connected to mysqldumps. If the memory consumption of mysqld does not increase linearly over many days (does it?), then it is probably not a memory leak. If the crashes are connected to memory consumption you could try making the InnoDB buffer pool slightly smaller and test if the crashes occur less frequently. I will look at the UNION problem later. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Original Message - From: Richard Clarke [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Sent: Sunday, May 19, 2002 12:02 PM Subject: Re: 4.0.1 Bugs Heikki, As for my crashes. This one is a little hard, see, we have two machines that do this. Both however, display little to no information in the log. One server just says mysql restarted followed by mysql was shut down incorrectly. The second server once gave an error like this, 020426 12:26:31 InnoDB: Started /usr/local/mysql-4.0.1-alpha/libexec/mysqld: ready for connections 020511 1:09:25 read_key: Got error 146 when reading table './counter/br_type' 020516 2:27:31 read_key: Got error 146 when reading table './counter/br_type' 020518 1:25:36 read_key: Got error 146 when reading table './counter/br_type' InnoDB: Error: undo-id is 136712960 InnoDB: Assertion failure in thread 869069824 in file trx0undo.c line 1316 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16773120 record_buffer=1044480 sort_buffer=1048568 max_used_connections=190 max_connections=500 threads_connected=90 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 1038376 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 020518 01:30:27 mysqld restarted InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 589 1379690513 InnoDB: Doing recovery: scanned up to log sequence number 589 1379756032 InnoDB: Doing recovery: scanned up to log sequence number 589 1379821568 InnoDB: Doing recovery: scanned up to log sequence number 589 1379887104 InnoDB: Doing recovery: scanned up to log sequence number 589 1379952640 InnoDB: Doing recovery: scanned up to log sequence number 589 1380018176 InnoDB: Doing recovery: scanned up to log sequence number 589 1380083712 The 146 errors can be ignore, they were deadlocks ocurring in
Re: 4.0.1 Bugs
Richard, then it is possible that all the crashes are caused by SHOW CREATE TABLE. The memory corruption bug can crash InnoDB at any place, it is not always caught by an assertion like in the error log. Since the bug is nondeterministic, it is well possible it appears only under some specific database load. Lenz of MySQL AB is working hard to get good Linux builds of 3.23.51 and 4.0.2 done. Let us hope we finally get the new releases soon. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Original Message - From: Richard Clarke [EMAIL PROTECTED] To: MYSQL [EMAIL PROTECTED]; Heikki Tuuri [EMAIL PROTECTED] Sent: Sunday, May 19, 2002 3:25 PM Subject: Re: 4.0.1 Bugs Heikki, My set of queries performs about 4/5 show create table queries each hour. The purpose being to drop it then recreate it automatically. Last time I tried truncate it was as slow as delete, but that was near the begining of my development and never saw reason to retry it after show create/drop/create ... worked fine. Even though these show creates happen 4/5 hours could they be the cause of it somehow only crashing every 4/5 days? Or is it more likely that the crashes I experience are a set of bugs. Maybe one day the assertion failure crashes it, then a few days after another bug crashes it?. Near the beginning of the development I experienced problems where Mysql would crash coz it bugged out on virtual memory. Doubling the virtual memory stopped this. I don't think the error is related to this since it used to singal 11 every time when that was happening. As for the memory consumption, PID USERNAME PRI NICE SIZERES STATE C TIME WCPUCPU COMMAND 44577 mysql 31 0 1085M 79544K RUN1 34.6H 5.37% 5.37% mysql This would suggest that memory consumption has decreased (to 79megs?) yet virtual memory has bloated to 1085-79?. This is on the other server mind you. The two servers that are crashing, crashed very recently, but I think they also will begin to exhibit the virtual memory increase, real memory decrease syndrome over the next couple of days. Ric. - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: Richard Clarke [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, May 19, 2002 1:01 PM Subject: Re: 4.0.1 Bugs Richard, the assertion failure below is very probably caused by the SHOW CREATE TABLE memory corruption bug which was fixed in 3.23.48, but not yet in 4.0.1. It is usually caused by mysqldump. The regularity of the crashes suggests they might be connected to mysqldumps. If the memory consumption of mysqld does not increase linearly over many days (does it?), then it is probably not a memory leak. If the crashes are connected to memory consumption you could try making the InnoDB buffer pool slightly smaller and test if the crashes occur less frequently. I will look at the UNION problem later. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Original Message - From: Richard Clarke [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Sent: Sunday, May 19, 2002 12:02 PM Subject: Re: 4.0.1 Bugs Heikki, As for my crashes. This one is a little hard, see, we have two machines that do this. Both however, display little to no information in the log. One server just says mysql restarted followed by mysql was shut down incorrectly. The second server once gave an error like this, 020426 12:26:31 InnoDB: Started /usr/local/mysql-4.0.1-alpha/libexec/mysqld: ready for connections 020511 1:09:25 read_key: Got error 146 when reading table './counter/br_type' 020516 2:27:31 read_key: Got error 146 when reading table './counter/br_type' 020518 1:25:36 read_key: Got error 146 when reading table './counter/br_type' InnoDB: Error: undo-id is 136712960 InnoDB: Assertion failure in thread 869069824 in file trx0undo.c line 1316 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16773120 record_buffer=1044480 sort_buffer=1048568 max_used_connections=190 max_connections=500 threads_connected=90 It is possible that mysqld
A new GUI for mySQL
greetings... Webyog has released mySQLyog, a win32 based Query analyzer. Its FREE and gives all the features that a mysql developer can ask for by a query analyzer. please visit www.webyog.com you can execute query of results of more than 10 records, supports multiple query execution and you can export your data into XML, HTML and CSV and can also import form a text file. It also allows you to excute last query and lets you add your favourite query in a personal folder with one click of mouse. So you dont have to save it and open it. Please take a look at it and feel free to send your suggestions to [EMAIL PROTECTED] Ritesh __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com - 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 fixed length tables MyISAM vs. ISAM
Hello All, I recently moved my database from mysql 3.22.29 to 3.23.41, and udated all my tables to MyISAM tables. Under ISAM, I could take the filesize of the ISD table and divide by the record length to get the exact number of records returned by select count(*) from that table (or vise-versa - it always worked). Now with the MyISAM tables, this does not seem to work - the files are always a little larger than the record count multiplied by the record length. Since these are all fixed length tables, where is the extra space comming from? It's not much - about .1% or between .5 and 1.5 megabytes out of a 20 million record fixed-length table. I don't think it is a deleted record space, since (a) very few records are ever deleted, and new inserts happen every 3 seconds and (b) the overage amount is never divisable by the record length. Is there a forumula that will accurately calculate the size of MyISAM fixed-length files? Is there a new overhead associated with myisam tables? Thanks, -Hank __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com - 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: A new GUI for mySQL
Sounds great! I'm all for GUI's for MySQL! It would help if you put some screen shots on your site so we can see what its interface is like before we download and install. Speaking for myself, my hard drive is full of stuff I've downloaded just to find out it isn't what I was looking for. Dennis - Original Message - From: Ritesh Nadhani [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, May 19, 2002 9:25 AM Subject: A new GUI for mySQL greetings... Webyog has released mySQLyog, a win32 based Query analyzer. Its FREE and gives all the features that a mysql developer can ask for by a query analyzer. please visit www.webyog.com you can execute query of results of more than 10 records, supports multiple query execution and you can export your data into XML, HTML and CSV and can also import form a text file. It also allows you to excute last query and lets you add your favourite query in a personal folder with one click of mouse. So you dont have to save it and open it. Please take a look at it and feel free to send your suggestions to [EMAIL PROTECTED] Ritesh __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com - 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 with InnoDB + indexes
Hello, I've just switched to InnoDB table from myISAM and it's been running pretty smoothly except on this SQL statement it doesn't use any indexes when there are, and therefore is very slow. mysql explain select pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from newsentries10 ne, pnews pn where ne.playerid=28575 and ne.type=2 and pn.newsid=ne.newsid; +---++---+-+-+---+++ | table | type | possible_keys | key | key_len | ref | rows | |Extra | +---++---+-+-+---+++ | ne| ALL| list_news,delete_news | NULL|NULL | NULL | 734023 | |where used | | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | 1 | || +---++---+-+-+---+++ 2 rows in set (0.00 sec) (Notice that key for ne is NULL when there is obviously an index it could use, but doesn't, why?) the table structures for the two tables are: mysql desc newsentries10; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | newsid| int(10) unsigned| | MUL | 0 | | | playerid | int(10) unsigned| | MUL | 0 | | | hidestamp | int(10) unsigned| | | 0 | | | viewpoint | tinyint(3) unsigned | | | 0 | | | type | tinyint(3) unsigned | | | 0 | | | delmarker | tinyint(3) unsigned | | MUL | 0 | | +---+-+--+-+-+---+ 6 rows in set (0.00 sec) mysql desc pnews; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | newsid| int(10) unsigned| | PRI | NULL| auto_increment | | type | tinyint(3) unsigned | | | 0 || | id| int(10) unsigned| | MUL | 0 || | timestamp | int(10) unsigned| | | 0 || +---+-+--+-+-++ 4 rows in set (0.00 sec) the indexs are: mysql show index from newsentries10; +---++-+--+-+---+-+--++-+ | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | |Cardinality | Sub_part | Packed | Comment | +---++-+--+-+---+-+--++-+ | newsentries10 | 1 | delmarker |1 | delmarker | A | | 0 | NULL | NULL | | | newsentries10 | 1 | list_news |1 | playerid| A | | 0 | NULL | NULL | | | newsentries10 | 1 | list_news |2 | type| A | | 0 | NULL | NULL | | | newsentries10 | 1 | delete_news |1 | newsid | A | | 76379 | NULL | NULL | | +---++-+--+-+---+-+--++-+ 4 rows in set (0.20 sec) mysql show index from pnews; +---++--+--+-+---+-+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality || Sub_part | Packed | Comment | +---++--+--+-+---+-+--++-+ | pnews | 0 | PRIMARY |1 | newsid | A | 139047 || NULL | NULL | | | pnews | 1 | id |1 | id | A | 139047 || NULL | NULL | | +---++--+--+-+---+-+--++-+ 2 rows in set (0.07 sec) Now I have an index on the two columns that are used in the query on the newsentries10 table and it doesn't use them at all. Could someone please explain this to me and give me any advice on how to fix it? Thanks for your help in advance. Andrei Cojocaru [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
GROUP BY on multiple fields apparently broken
Description: mysql describe DLSummary; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | DownloadID | int(10) unsigned | YES | MUL | NULL| | | Year | int(10) unsigned | YES | MUL | NULL| | | YDay | int(10) unsigned | YES | | NULL| | | Count | int(10) unsigned | YES | | NULL| | ++--+--+-+-+---+ 4 rows in set (0.00 sec) mysql show keys from DLSummary; +---+++--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | |Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---+++--+-+---+-+--++--++-+ | DLSummary | 1 | Date |1 | Year| A | |1 | NULL | NULL | YES | BTREE | | | DLSummary | 1 | Date |2 | YDay| A | | 377 | NULL | NULL | YES | BTREE | | | DLSummary | 1 | DownloadID |1 | DownloadID | A | |15378 | NULL | NULL | YES | BTREE | | +---+++--+-+---+-+--++--++-+ 3 rows in set (0.01 sec) BUG mysql select Year,YDay,sum(Count) from DLSummary group by Year,YDay; ERROR 1062: Duplicate entry '2001-125' for key 1 BUG mysql select Year,YDay,sum(Count) from DLSummary group by concat(Year,YDay); +--+--++ | Year | YDay | sum(Count) | +--+--++ | 2001 | 124 | 2140 | | 2001 | 125 | 10302 | . This seems rather odd. GROUP BY Year,YDay is documented to be topologically equivalent to GROUP BY CONCAT(Year,YDay) (well, more or less) but has functional difference. Bug tested in 4.0.2 @ BK 1.1272 (May 17th) and also on 4.0.2 as of around March 2nd, so it's nothing recent. Table REPAIRED successfully, so I'm convinced that the table is good. Repeatable with cleanly created tables. How-To-Repeat: create table test (One int unsigned, Two int unsigned, Three int unsigned, Four int unsigned); insert into test values (1,2,1,4); insert into test values (1,2,2,4); insert into test values (1,2,3,4); insert into test values (1,2,4,4); insert into test values (1,1,1,4); insert into test values (1,1,2,4); insert into test values (1,1,3,4); insert into test values (1,1,4,4); insert into test values (1,3,1,4); insert into test values (1,3,2,4); insert into test values (1,3,3,4); insert into test values (1,3,4,4); select One, Two, sum(Four) from test group by One,Two; -- fails select One, Two, sum(Four) from test group by concat(One,Two); -- succeeds Fix: Use concat() to create a single group by element, but this isn't the right way to do it, surely! Submitter-Id: submitter ID Originator:Matt Johnson Organization: AVSIM Online http://www.avsim.com MySQL support: none Synopsis: GROUP BY on multiple fields apparently broken Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-4.0.2-alpha (Source distribution) Server: /usr/local/bin/mysqladmin Ver 8.32 Distrib 4.0.2-alpha, 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 4.0.2-alpha Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 6 hours 37 min 58 sec Threads: 2 Questions: 166 Slow queries: 11 Opens: 26 Flush tables: 1 Open tables: 5 Queries per second avg: 0.007 Environment: System: Linux guysfield 2.4.18-k7 #1 Sun Apr 14 13:19:11 EST 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs gcc version 2.95.4 20011002 (Debian prerelease) Compilation info: CC='gcc-3.0' CFLAGS='-Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=athlon -O3 -fno-omit-frame-pointer' CXX='g++-3.0' CXXFLAGS='-Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual
indexing text fields possible?
Hi there, I am wondering if it would be anyhow possible to make a search on a mysql textfield faster. Right now the table contains 294000 entries and takes about 40 MB of space. Is there a way to apply a index with a resonable amount of disk space? I am also not so sure if I should use text or smalltext columns as the info may vary between 1 and 1000 chars. A query for LIKE 'test%' takes about 20 s right now. Thanx for any help on that, Andy - 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
Reaching the unique id after inserting with auto increment
Hi ! This is a basic problem. I need an unique id in a table, so I choosed integer with auto increment. (it is so simple...) After inserting a record, I have to display the created unique id to identify that record later. But how can I select it ? If I select the last record, I should get another one, inserted by a concurrent user. I have some stupid ideas but they are complex, I hope there is an easy way. The life is getting more complicated without the record pointers of the dBase based systems ? :) Please let me know how it should be handle under sql ? Waiting for your answer, Thank you, GyG - 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: Reaching the unique id after inserting with auto increment
Hi, On Sun, May 19, 2002 at 05:14:49PM +0200, Gyulay Gabor wrote: This is a basic problem. I need an unique id in a table, so I choosed integer with auto increment. (it is so simple...) After inserting a record, I have to display the created unique id to identify that record later. But how can I select it ? If I select the last record, I should get another one, inserted by a concurrent user. Use SELECT LAST_INSERT_ID() like described in the manual. LAST_INSERT_ID is kept per database connection, so concurrent users don't create any problems. Regards, Fred. The stupid filter needs sql,query -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - 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 crashed and created huge .MYI files. All data lost?
Hi there, during a wrong select query on a table containing over 2 mill. records (involving other tables) mysql and the whole linux server crashed. After rebooting I did run myisamchk and this reported that some tables are damaged but still can be read. So I did shut down the server und run myisamchk --recover --quick *.MYI Right now mysql is recovering a table named #sql-98a_1b.MYI which I do not know where it comes from. I did definatelly not create this table. Recovery is in process now for more than 30 minutes on this table and it is counting a valueup (right now 15) Does anybody know what happened here? I hope I do not need to role out my backup tapes :-( Thanx for any help, Andy - 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
license question on libmysql.dll and C/C++ API
Hi all, I have a question on license of MySQL C API or MySQL++ API, and libmysql. What I want to do is to make a MS Windows program (C++) that accesses MySQL database, and to distribute it on internet without publishing its source code. It uses database server as its backend resource store. It currently uses MS SQL server or its free version MSDE, or MS Access, but I hope migration to MySQL. I have no intention to distribute MySQL itself and libmysql.dll itself with my program. It's non-commercial software, but not freeware (in GNU sense). Since C API seems fast I thought it's nice to use but after extraction of .zip of MySQL4 source code and moving to libmysql.dll source directory, I found that all libmysql.dll source code is in GPL. All those source code have the header that states they are under GPL. That means I can't distribute resulting executable that is linked to libmysql without reacting requests of open source. Preamble and Section 5 of LGPL(http://www.gnu.org/copyleft/lesser.html) (not GPL) says: citation Preamble --snip-- When a program is linked with a library, whether statically or using a shared library, the combination of the two is legally speaking a combined work, a derivative of the original library. The ordinary General Public License therefore permits such linking only if the entire combination fits its criteria of freedom. The Lesser General Public License permits more lax criteria for linking other code with the library. --snip-- 5. A program that contains no derivative of any portion of the Library, but is designed to work with the Library by being compiled or linked with it, is called a work that uses the Library. Such a work, in isolation, is not a derivative work of the Library, and therefore falls outside the scope of this License. However, linking a work that uses the Library with the Library creates an executable that is a derivative of the Library (because it contains portions of the Library), rather than a work that uses the library. The executable is therefore covered by this License. Section 6 states terms for distribution of such executables. /citation Though LGPL Section 5 deals with LGPL, not GPL, it's also clear that I can't distribute a closed-source executable that links dynamically to GPLed library, because resulting executable(not object code) is covered by GPL too. Now, for MySQL++, it's LGPL. Since LGPL Section 6 forces users of LGPL library to permit reverse engineering of their executable, but it's not so hard to accept. (Though I think most of commercial applications for Linux, which are linked to L/GPL libraries including libc, infringe Section 6 by prohibiting reverse engineering in their license aggreement) But the problem is, when I use MySQL++, the executable is linked to libmysql.dll that is GPLed. So LGPL of MySQL++ is overwritten by more powerful GPL and there is no meaning of LGPL of MySQL++ in this case. I searched how actual commercial appliactions for MySQL deal with license. For example, EMS MySQL Manager http://ems-hitech.com/mymanager/ is linked to libmysql.dll, but it's not open-source, so it's not GPLed. EMS HiTech is an official MySQL AB partner, then there may be special license between them. I looked in commercial license store at mysql.com but can't find special licence. There is only per-server license and it doesn't look like developer license. I'm not sure how other closed-source applications that are not official partner of MySQL AB manage this license problem. Anyway I can't be official partner of MySQL AB just to distribute non-commercial proggy on my homepage. I checked MyODBC, though I don't like ODBC, but it's GPL too. Is there any good way, or any misconception in my understanding of libmysql license? Any idea is welcome, but please don't suggest embracing entire GPL... if it's not possible, then I'll quit touching MySQL and will start to look PostgreSQL. -- Linsey KISANJANI - 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
Search Query input works, but gives a blank page for results?
The following script, with some edits, has allowed access to a LOCAL mysql
database. I have reworked it some to access a MySQL on another server I
suppose I could transfer the script to that server for loading/execution, but
I'd like to learn how one accesses a remote mySQL database (to facilitate
building a shopping cart system.
Anyhow, when it executes, I can select the field I want to search on from a
popup_menu, select is or contains and then enter the text I wat to search
for. That part works. What doesn't work is that I receive simply a blank
page for a display (all this beautiful color of 738C8C).
Have I got the syntax for access to a remote database correct? TIA for any
help...
-
#!/usr/bin/perl -w
# CGI to select data from a SQL database
use CGI;
use strict;
use DBI;
my $q = new CGI;
#Definitions -unique to each table, user, etc.
my $db = the_plant_database;
my $table = da_table;
my $host = 201.201.201.21;
my $user = mysql;
my $password = mypassword;
# This is the main branch. The first time through
# display the form to the user; when the user submits
# the form then we process the input
if ($q-param('field') eq ) {printform()} else {results()}
sub printform {
print Content-Type: text/html\n\n;
# print $q-header;
print $q-start_html(-title='MySQL Plant Database at Plants.com',
-BGCOLOR='#738C8C',
-TEXT='white');
print CENTERH1MySQL Plant Search - Da_List of Plants/H1/CENTERBR;
print $q-startform;
print H2Plants that match selection/H2BR;
print $q-popup_menu(-name = 'field',
-values = [ML_ID, Cultivar,
Reg_Status,Section,Class,ML_ITG_ID,Registrant],
-default= Cultivar);
print $q-popup_menu(-name = 'searchtype',
-values = [is, contains],
-default= is);
print $q-textfield(-name =text,
-size = 16), BR;
print $q- submit;
print $q-endform;
print $q-end_html;
}
sub results {
print $q-header();
my $field = $q-param('field');
my $searchtype = $q-param('searchtype');
my $text = $q-param('text');
my @table=();
print $q-start_html(-title='Database Results',
-BGCOLOR='#738C8C',
-TEXT='white');
# Establish a connection with the database
my $dbh = $drh-connect($host, $db, $table, $user, $password);
# A simple check to see if we connected
if (!$dbh) {
print Cannot connect: $DBI::errstrBR;
print $q-end_html;
die;
}
# Build and execute the SQL statement
my ($SQLstatement);
if ($searchtype eq contains) {
$SQLstatement = select ML_ID, Cultivar, Reg_Status, Section, Class,
ML_ITG_ID, Registrant from $table
where $field like \%$text%\;
} else {
$SQLstatement = select ML_ID, Cultivar, Reg_Status, Section, Class,
ML_ITG_ID, Registrant from $table
where $field = \$text\;
}
my $sth = $dbh-prepare($SQLstatement);
my $howmany = $sth-execute;
# Display an error message if we can't find any matches
if ($howmany eq OEO) {
print $q-h3(font color='red' Couldn't match $text in database/font);
die;
}
print $howmany matches found in databasebrbr;
push (@table,$q-th([ML_ID, Cultivar, Reg_Status,
Section,Class,ML_ITG_ID,Registrant]));
# Loop through all the matches and store them in @table
for (my $i = 0; $i $howmany; $i++) {
my ($SQLML_ID, $SQLCultivar, $SQLReg_Status, $SQLSection, $SQLClass,
$SQLML_ITG_ID, $SQLRegistrant) = $sth-fetchrow_array;
push (@table,$q-td([$SQLML_ID, $SQLCultivar, $SQLReg_Status, $SQLSection,
$SQLClass, $SQLML_ITG_ID, $SQLRegistrant]));
}
# Print the table of data we received from the mySQL database.
print $q-table({-border = 1, -align = center},$q-TR(\@table));
print $q-end_html;
}
--
Andrew Lietzow
The ACL Group, Inc.
-
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
job payment.
Hey guys, I got an offer to do some php/mysql design for a local company, basically what it's going to be is to take the current Access DB and make it acessible through a webpage (that's the basic info they gave me). How much should i except to get paid for this, and what is an acceptable amount, for the completion of this project, or per hour? Jule -- |\/\__/\/| | Jule Slootbeek | | [EMAIL PROTECTED]| | http://blindtheory.cjb.net | | __ | |/\/ \/\| - 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: Remote admin of MySQL from Win 2K
YES! I was missing something: Telnet!! I can log in *BUT* I am unable to change the MySQL password. mysqladmin -h www.myhost.org -u root -p password 'new_password' asks me for the password and when I use the root password (su password), I get Access Denied. Todd -- Todd Cary Ariste Software 2200 D Street Extension Petaluma, CA 94952 707-773-4523 [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
Resorting entire Database
Hello Everyone. I backed up my entire db to data.sql file and now I want to restore and I don't know the right procedure could someone 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: job payment.
Hi How long is a piece of string..? Hourly rate for independent work in the UK is £20-35 ish, roughly US$35-60. I think US programmers are forbidden to discuss rates, as it is considered price fixing! You could access Access through ODBC, using php. This will not support a busy site but will be a quick fix for a small/quiet site. More than this and you would need to move to a sql based database, MySql being a good choice, although SQL server might be easier to migrate to, but perhaps more expensive. So it all depends on how much data there is, the complexity of the database and how much traffic they are expecting.. More information might enable us to give a better answer. HTH Peter --- Excellence in internet and open source software --- Sunmaia www.sunmaia.net [EMAIL PROTECTED] tel. 0121-242-1473 --- -Original Message- From: Jule [mailto:[EMAIL PROTECTED]] Sent: 19 May 2002 21:38 To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: job payment. Hey guys, I got an offer to do some php/mysql design for a local company, basically what it's going to be is to take the current Access DB and make it acessible through a webpage (that's the basic info they gave me). How much should i except to get paid for this, and what is an acceptable amount, for the completion of this project, or per hour? Jule -- |\/\__/\/| | Jule Slootbeek | | [EMAIL PROTECTED] | | http://blindtheory.cjb.net | | __ | |/\/\/\| - 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: Can't connect to ....
problem in permission; change rights to group an user mysql Jaan Jrgenson Hi everybody. Just installed Red Hat Linux on a machine. I included the MySQL packages but cant get it up working. When starting from shell it looks like this: $ mysql ERROR 2002: Can't connect to local MySQL server through socket 'var/lib/mysql/mysql.sock' (2) What have I done wrong? best regards, Jaan - 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 -- registered linux user #123707 - 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: Socket problem connecting
problem with permission; change rights to group and user mysql to mysql data directory Todd Cary I am getting this message when I try to establish a connect to the DB. Any suggestions on where I can find an answer? Warning: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) in /var/www/html/mysql/php/connect.php on line 2 Todd -- Todd Cary Ariste Software 2200 D Street Extension Petaluma, CA 94952 707-773-4523 [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 -- registered linux user #123707 - 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: random order by id
To pick one item at random what you want is Select foo from bar Where ID = rand() * scale To order them randomly, I seem to recall that there was an example of how to do it in the mySQL manual. -- Erik Perrohe - Original Message - From: Per Andreas Buer [EMAIL PROTECTED] To: Jule [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, May 19, 2002 2:55 AM Subject: Re: random order by id Jule [EMAIL PROTECTED] writes: Hey guys, i have a db and a table with id and questions now i want these questions to be listed in a random order, is there a way to format my SQL query or do i need some PHP work to? SELECT foo FROM bar ORDER BY rand(); Just to pick one: SELECT foo FROM bar ORDER BY rand() LIMIT 1; -- Per Andreas Buer - 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: Database and all backups lost at same time
myisamchk -q Does a Quick check of the files... See the manual for the options to do a full repair / recovery of the files. - ??) Perhaps the backup files were already corrupt before the crash??? ??) Perhaps you have some issues with your hard disk? You can buy a CDRW drive for about $120 US well worth the investment. Good Luck, -- Erik - Original Message - From: kibserv-FtS Setiqueue [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Saturday, May 18, 2002 1:44 PM Subject: Database and all backups lost at same time This is an odd one. I seem to have lost all databases and backups simultaneously. I keep daily backups for upto 7 days. This morning after a power outage I noted I was unable to login. Every user and password I tried was denied. I checked the files in the mysql data folder and they were all there and seemed fine. They matched exactly to those in the past 7 days of backups. I even tried to restore from each of the 7 days of backups with no result. I removed all the files in the data/mysql/ folder and restarted mysql again. I was able to login as root with no password but found every other database I tried to use came back with 'ERROR 1049: Unknown database'. I also checked these files and they are all there. I am now able to 'use mysql' since it is empty. Any ideas on what may have happened. Since it seems to affect all databases and all backups I have to believe there is some corruption within the mysql server itself and not the data files. -Jason - 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
FW: Resorting entire Database
Hello Everyone. I backed up my entire db to data.sql file and now I want to restore and I don't know the right procedure could someone 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: mysql crashed and created huge .MYI files. All data lost?
Andy, I believe that that file is an on disk temporary file that was created during the query. I would suggest you wait for the recover to finish as I think (hope) your data will be returned to a fairly normal state when it does. Ric. - Original Message - From: andy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, May 19, 2002 6:28 PM Subject: mysql crashed and created huge .MYI files. All data lost? Hi there, during a wrong select query on a table containing over 2 mill. records (involving other tables) mysql and the whole linux server crashed. After rebooting I did run myisamchk and this reported that some tables are damaged but still can be read. So I did shut down the server und run myisamchk --recover --quick *.MYI Right now mysql is recovering a table named #sql-98a_1b.MYI which I do not know where it comes from. I did definatelly not create this table. Recovery is in process now for more than 30 minutes on this table and it is counting a valueup (right now 15) Does anybody know what happened here? I hope I do not need to role out my backup tapes :-( Thanx for any help, Andy - 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: newbie mysql install
Just a guess, could it be that the Mac does not support sockets...?? I dunno, I'm just fishing since I have not yet had an opportunity to play with the OS X. What about permissions? non-the-less... Try changing your config file to use TCP and see if that works. The other thing I ran into when setting up my Linux server is that something was screwy such that it was creating the socket in one location and looking for it in a different location. I solved this problem by overridding and explicitly setting the fully qualified path to the socket. -- Erik - Original Message - From: Jeffrey Camiel [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, May 18, 2002 7:12 AM Subject: newbie mysql install Installed MySQL using Fink to the Mac X OS. When attempting to run, I am getting this error. ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' Help in explaining this error and how to fix the installation would be appreciated. Thanks in advance. Jeffrey - 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: MySql Client fails to connect
I had the same problem, it drove me up the friggin wall until I finally found the answer... Read the manual very carefully on how to set up user permissions. Pay particular attention to the description of how wild card permissions are applied. That's what was killing me... Also pay particular attention to the section on security... making the server directly accessible is going to expose you to possible hacker attack, make sure you know what you are doing... Ciao, -- Erik Perrohe - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, May 18, 2002 4:10 AM Subject: Re: MySql Client fails to connect Federico, Friday, May 17, 2002, 8:23:35 PM, you wrote: FC After installing MySql Client Version 1.75 on my Win98 Pc, I tried to FC connect to mysql database on a Linux Box. FC this is the error message FC Host not allowed to connect to this MySQL server. FC This is my configuration file [skip] FC I don't know if I have to configure something else on windows or the linux FC server to connect? You should allow to connect from your host for your user. Look at: http://www.mysql.com/doc/A/c/Access_denied.html FC TIA. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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: Resorting entire Database
The normal way to do it is to pipe it to the mysql command line client which will make it enter batch mode and run the .sql file. The basic syntax will look something like: mysql -p database file.sql This should work in windows or in any unix environment. This is also assuming the file is in SQL statement format, like mysqldump gives, and not in a tab-delimited file or anything similar to that. Hope that helps you out. Harrison - Original Message - From: Bensin Joseph [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, May 19, 2002 6:04 PM Subject: FW: Resorting entire Database Hello Everyone. I backed up my entire db to data.sql file and now I want to restore and I don't know the right procedure could someone 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 - 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
LOCK REPAIR
If I want to repair a table, with the REPAIR TABLE syntax, can I do a FLUSH TABLES WITH READ LOCK beforehand, or will that lock REPAIR out from writing too? Thanks! - Mark P.S. Keeping the bot happy: mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
please help a newbie!
Dear All , I believe this question is very simple and hope u ppl can help me out. Well i'm trying to develop a site using chinese characters where my users can login. I'm suppose to store their username which will be in chinese character in mysql . I tried looking for info abt it but in vain. Does mysql support chinese char ? if not wht shd i do... i can only use mysql. Mr Son nguyen can you please tell me how u did the magic of using viet char? thank you very much for your help and comprehension. regards, ally __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com - 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 connection handshaking
Hello list, I am planing to write a script to telnet mysql server(port:3306) and try to verify the server status. When I try something like this: home:~ #telnet localhost 3306 Trying 127.0.0.1... Connected to localhost. Escape character is '^]'. , 3.23.49-logúm|zn:u`^,Connection closed by foreign host. home:~ # Well, it seems that I have no idea complete the handshaking. Is there any way that I can type something and expect something to return? -- Patrick Hsieh [EMAIL PROTECTED] GPG public key http://pahud.net/pubkeys/pahudatpahud.gpg - 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
Concurrency control
How does MySQL handle concurrency control between two or more concurrent quires (not transactions), on their website they say they use multi-versioned concurrency control, but i guess this between transactions, not queries. Consider the case we have two queries Q1: select * from table1 EXCEPT select * from table1 where id50; Q2:update table1 set id = id+1; Concurrent execution of these two queries could result in incorrect results, if no appropriate concurrency control is used, specifically if the update query is executed between the first half of Q1 and the second half. there are many other examples to this problem, so i wonder how MySQL handles it. Thanks, Khaled. - 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
Insert into Table
Hi, I have a table in which i have a field of VARCHAR(255), i am picking up URLs from a file and INSERTING into mysql table, i am using MySQL C API, the problem is that file contains 6700 records and it is just inserting few records like 100 or so, what may be the probnlem Please help Sameer -- http://www.dypatil.edu For Better tomorrow - 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: Concurrency control
mysql doesn't write transaction mysql-max does write transaction for details, you can check out at mysql web site or mysql mysql-max manual if your application doesn't need transaction, I think mysql is better for better performance - Original Message - 寄件者: Khaled Elmeleegy [EMAIL PROTECTED] 收件者: [EMAIL PROTECTED] 傳送日期: 2002年5月20日 PM 12:36 主旨: Concurrency control How does MySQL handle concurrency control between two or more concurrent quires (not transactions), on their website they say they use multi-versioned concurrency control, but i guess this between transactions, not queries. Consider the case we have two queries Q1: select * from table1 EXCEPT select * from table1 where id50; Q2:update table1 set id = id+1; Concurrent execution of these two queries could result in incorrect results, if no appropriate concurrency control is used, specifically if the update query is executed between the first half of Q1 and the second half. there are many other examples to this problem, so i wonder how MySQL handles it. Thanks, Khaled. - 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
LOAD DATA FROM FLAT FILE (InnoDB Table)
Background: I tried to load data from a flat file. The flat file contains one line of heading data. It is comma delimited and line separate by line feed. I log on as root to do the load. Here is my result. [ldemeester@bidshiftdev ldemeester]$ mysql -u root -p test USER_TABLE.txt Enter password: ERROR 2013 at line 1: Lost connection to MySQL server during query The following is my USER_TABLE.txt file:[ldemeester@bidshiftdev ldemeester]$ cat USER_TABLE.txt LOAD DATA LOCAL INFILE '/home/ldemeester/USER_TABLE.csv' INTO TABLE USER_TABLE FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES ( ID, USERNAME, PASSWORD, EMAIL, PASSWORD_HINT, PRINCIPAL_ID, UNIT_LOCATION_ID, POSITION_TYPE_ID ); Questions: 1) Where can I find the error messages? My server is Linux. I bought a book called core MYSQL - The serious developer's Guide. I don't think this is for the DBA. Are there better books for the mysql DBA? 2) What did I do wrong? Since root has File_priv it should be able to read the file. I also would appreciate anyone send me a cron job backup script to do the backup on Innodb. Thanks very much. Lucia DeMeester New Media Merchants Senior Oracle DBA 858-882-8500 ext. 2314 - 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: Concurrency control
In the last episode (May 19), Khaled Elmeleegy said: How does MySQL handle concurrency control between two or more concurrent quires (not transactions), on their website they say they use multi-versioned concurrency control, but i guess this between transactions, not queries. Consider the case we have two queries Q1: select * from table1 EXCEPT select * from table1 where id50; Q2:update table1 set id = id+1; Concurrent execution of these two queries could result in incorrect results, if no appropriate concurrency control is used, specifically if the update query is executed between the first half of Q1 and the second half. there are many other examples to this problem, so i wonder how MySQL handles it. Please see http://www.mysql.com/doc/L/o/Locking_methods.html and http://www.mysql.com/doc/L/o/Locking_Issues.html . Mysql will always return the correct value for your query. How it accomplishes that depends on the table type. For MyISAM tables, the entire table is read-locked during queries and read+write locked during updates. Writers wait for readers to finish before locking the table, then readers queue up until the writer finishes. For BDB tables, only affected pages are locked. For InnoDB, records are write-locked and multiple versions of records are kept to keep queries from blocking each other unnecessarily. -- Dan Nelson [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
error message....somewhat lost!
Hi All, The other day, I tried connecting to mysql via the good old mysql -u me -p but I got a message saying that my mysql.sock is missing from the tmp folder. After checking the mailing list archive, I decided (well, guessed is the more appropriate word) that I could try to re-start the daemon: bin/safe_mysqld --log But i get this error message: The file /usr/local/mysql/libexec/mysqld doesn't exist or is not executable Please do a cd to the mysql installation directory and restart this script from there as follows: ./bin/safe_mysqld. Now I'm really confused!! I tried entering a) ./bin/safe_mysqld and b) ./bin/safe_mysqld. (thinking that the period is part of the command...) but that didn't work. a) just gets me the same error message and b) isn't even a command! :-( Comments? Many thanks! __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com - 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
[7241] Indexes on columns that are declared differently?
The MySQL online manual says that MySQL cannot use indexes efficiently unless the columns have the same type and the same length. I can see no release notes that this problem has been fixed. My question: Is this still a problem? Here is the extract from the latest manual: 7.29 EXPLAIN Syntax (Get Information About a SELECT) -- One problem here is that MySQL can't (yet) use indexes on columns efficiently if they are declared differently. In this context, VARCHAR and CHAR are the same unless they are declared as different lengths. Because tt.ActualPC is declared as CHAR(10) and et.EMPLOYID is declared as CHAR(15), there is a length mismatch. To fix this disparity between column lengths, use ALTER TABLE to lengthen ActualPC from 10 characters to 15 characters: mysql ALTER TABLE tt MODIFY ActualPC VARCHAR(15); - Thanks, Stephen - 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
error message....somewhat lost
Hi All, The other day, I tried connecting to mysql via the good old mysql -u me -p but I got a message saying that my mysql.sock is missing from the tmp folder. After checking the mailing list archive, I decided (well, guessed is the more appropriate word) that I could try to re-start the daemon: bin/safe_mysqld --log But i get this error message: The file /usr/local/mysql/libexec/mysqld doesn't exist or is not executable Please do a cd to the mysql installation directory and restart this script from there as follows: ./bin/safe_mysqld. Now I'm really confused!! I tried entering a) ./bin/safe_mysqld and b) ./bin/safe_mysqld. (thinking that the period is part of the command...) but that didn't work. a) just gets me the same error message and b) isn't even a command! :-( Comments? Many thanks! simon __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com = # Warmest Regards, Simon K. Chan - [EMAIL PROTECTED] Great spirits have always encountered violent opposition from mediocre minds. - Albert Einstein __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com - 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
Please help - Inserting
Hi, I have a table in which i have a field of VARCHAR(255), i am picking up URLs from a file and INSERTING into mysql table, i am using MySQL C API, the problem is that file contains 6700 records and it is just inserting few records like 100 or so, what may be the probnlem Please help Sameer -- http://www.dypatil.edu For Better tomorrow - 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: [7241] Indexes on columns that are declared differently?
In the last episode (May 20), Stephen Brownlow said: The MySQL online manual says that MySQL cannot use indexes efficiently unless the columns have the same type and the same length. I can see no release notes that this problem has been fixed. My question: Is this still a problem? Probably. I doubt anyone's even trying to fix it, since a field-length mismatch between related fields means that one or the other fields has the wrong length. You'll never get a 20-character string to match if the other table's field definition is char(10); either make both 10, or both 20 :) -- Dan Nelson [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
query - primary key
Hi, I have a table with four colums id date time addr - varchar (used my sql C API) I want that when i add an entry, the PRIMARY KEY IS ALL OF THEM means that no entry should repeat. I used ALTER TABLE sss ADD PRIMARY KEY (all four field) but not when i add my things, after 104 items, it gives an error, DUPLICATE ENTRY I have checked the file, they are not duplicate What do i do Please help Sameer -- http://www.dypatil.edu For Better tomorrow - 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: query - primary key
In the last episode (May 20), Sameer Maggon said: Hi, I have a table with four colums id date time addr - varchar (used my sql C API) I want that when i add an entry, the PRIMARY KEY IS ALL OF THEM means that no entry should repeat. I used ALTER TABLE sss ADD PRIMARY KEY (all four field) but not when i add my things, after 104 items, it gives an error, DUPLICATE ENTRY Paste in the full error message, and if you made 'id' a TINYINT AUTO_INCREMENT, think very very carefully about the rage of a TINYINT. -- Dan Nelson [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: query - primary key
Hi, Here are the errors i am getting You have an error in your SQL syntax near 'http://www.indianrail.gov.in/frame.html') file://C:\\IIK\\for_you.html ')' at line 1 You have an error in your SQL syntax near 'http://www.cultureholidays.com/')file://C:\\IIK\\Tour_del.html ')' at line 1 You have an error in your SQL syntax near 'http://mapsofindia.com/maps/delhi/h3s0902.htm') file://C:\\IIK\\Map_del.html ')' at line 1 Duplicate entry 'k6m1-2001-08-14-05:02:00- http://media.admonitor.net/creatives.cgi? F2183|1017|1|iframe|C18744|||_admonitor|10930|58872 ' for key 1 Duplicate entry 'k6m1-2001-08-14-04:56:00-http://eshop.msn.com ' for key 1 Duplicate entry 'k6m1-2001-08-14-05:02:00- http://www.radiofreevirgin.com/asx/indierock_28.asx ' for key 1 Duplicate entry 'k6m1-2001-08-16-10:00:00- http://windowsmedia.com/mediaguide/default.asp ' for key 1 Duplicate entry 'k6m1-2001-08-13-08:00:00- http://64.14.197.173/Lang/Main.asp?S=123456SR=238977R=[direct] lang=EN ' for key 1 Thanx Sameer -- you wrote: paste in the full error message, and if you made 'id' a TINYINT AUTO_INCREMENT, think very very carefully about the rage of a TINYINT. -- Dan Nelson [EMAIL PROTECTED] -- http://www.dypatil.edu For Better tomorrow - 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: query - primary key
In the last episode (May 20), Sameer Maggon said: Hi, Here are the errors i am getting You have an error in your SQL syntax near 'http://www.indianrail.gov.in/frame.html') file://C:\\IIK\\for_you.html ')' at line 1 I'd take a look at this error first; you seem to be passing bad query text to mysql. -- Dan Nelson [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: query - primary key
Hi, Firstly, how do i correct that error of BAD text coz i actually have a text file which has a HISTORY dump from IE. Secondly what about the Duplicate Entry stuff, its comming for all the URLs why?? Regards Sameer -- you wrote: I'd take a look at this error first; you seem to be passing bad query text to mysql. -- Dan Nelson [EMAIL PROTECTED] -- http://www.dypatil.edu For Better tomorrow - 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
store JPG in MySQL DB
Hi, Can i have a table in which i can store my JPG file somehow in MYSQL database. like i can do it in Oracle Sameer -- http://www.dypatil.edu For Better tomorrow - 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: store JPG in MySQL DB
Hi Hi, Can i have a table in which i can store my JPG file somehow in MYSQL database. Absolutely. just declare a BLOB column, and load your images rightinto the columsn Sameer -- http://www.dypatil.edu For Better tomorrow - 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: store JPG in MySQL DB
Hi, Can you please put some light on it.. How i can do it, or is it described in manual Sameer -- you wrote: Hi Hi, Can i have a table in which i can store my JPG file somehow in MYSQL database. Absolutely. just declare a BLOB column, and load your images rightinto the columsn Sameer -- http://www.dypatil.edu For Better tomorrow - 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
