special char in full-text search
Hi All, I am using the MySQL full text search capability in the search workflow in my appplication. I found that MySQL treats special character like *./,* etc. as tokenizers if they are not specified within a phrase(inside double quotes). For ex. If the search string entered is M.B.A or 24/7 then it searches for M, B, and A sepearately. I can't include the quotes for all search strings as wild char(*) search would not work when specified within a phrase. Is there a way to avoid MySQL from tokenizing the search criteria? Any ideas? Thanks, Harini -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication stopped
Hi, recently, our slave stopped for a duplicate key error (which is a bug to me : http://bugs.mysql.com/bug.php?id=9929 ) How do you usually check automatically that slaves are up and running ? -- http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication stopped
How do you usually check automatically that slaves are up and running ? echo show slave status\G|mysql -u user -ppassword|grep -i slave.*running|grep -i no mail -s MySQL Slave stopped [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication stopped
echo show slave status\G|mysql -u user -ppassword|grep -i slave.*running|grep -i no mail -s MySQL Slave stopped [EMAIL PROTECTED] well, actually echo show slave status\G|mysql -u user -ppassword|grep -i slave.*running|grep -i no echo .|mail -s MySQL Slave stopped [EMAIL PROTECTED] sorry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
stack explanation ?
Hi, our server crashed yesterday with the following error : do_ypcall: clnt_call: RPC: Unable to receive; errno = Connection refused do_ypcall: clnt_call: RPC: Unable to receive; errno = Connection refused do_ypcall: clnt_call: RPC: Unable to receive; errno = Connection refused 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=8388608 read_buffer_size=520192 max_used_connections=392 max_connections=1000 threads_connected=8 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1540184 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x6bc2f258 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... Cannot determine thread, fp=0x45d4f88, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80f7893 0xc5ed96 0x82ab930 0x8158a17 0x82302c1 0x819f373 0x815d4e2 0x8105657 0x810868d 0x8103321 0x8102eb8 0x810280d 0xc59e51 0x50e06a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions 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 0x6c0095e8 is invalid pointer thd-thread_id=109709885 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 060706 1:26:24 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 165 358046223 InnoDB: Doing recovery: scanned up to log sequence number 165 363289088 InnoDB: Doing recovery: scanned up to log sequence number 165 368531968 InnoDB: Doing recovery: scanned up to log sequence number 165 373774848 InnoDB: Doing recovery: scanned up to log sequence number 165 374990041 060706 1:26:24 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 060706 1:26:28 InnoDB: Flushing modified pages from the buffer pool... 060706 1:26:28 InnoDB: Started /var/lib/mysql/MySQL4.0.18/libexec/mysqld: ready for connections. Version: '4.0.18' socket: '/var/lib/mysql/MySQL4.0.18/var/mysql4.0.18.sock' port: 3306 resolved stack gives : 0x80f7893 handle_segfault + 399 0xc5ed96 (?) 0x82ab930 strnmov + 24 0x8158a17 innobase_mysql_print_thd + 471 0x82302c1 lock_print_info + 1573 0x819f373 srv_sprintf_innodb_monitor + 507 0x815d4e2 _Z18innodb_show_statusP3THD + 138 0x8105657 _Z21mysql_execute_commandv + 5999 0x810868d _Z11mysql_parseP3THDPcj + 329 0x8103321 _Z16dispatch_command19enum_server_commandP3THDPcj + 1069 0x8102eb8 _Z10do_commandP3THD + 100 0x810280d handle_one_connection + 841 0xc59e51 (?) 0x50e06a (?) this is a Mysql 4.0.18 on a dedicated linux Red Hat Enterprise Linux ES release 3 ( kernel 2.4.21-20 ) with 3 Gig RAM could you tell me what happened please? -- Dilipan Sebastiampillai Systems - DBA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Spam-Probable]MySQL 5.0.22 and show columns bug?
Since a couple of recent stable versions back (and more recently, MySQL 5.0.22), MySQL has been returning the column length (C API) of the 'Type' column of a show columns from.. statement as being 196605 (almost 192KB), when this column only really contains data in the region of 10 bytes, if even. This is not only a bug, but extremely wasteful, as most client apps will therefore provide to allocate megs of ram for the potential data in this column to display even the simpliest of tables' column listing. Not only that, but many MySQL client apps will predetermine the correct manner of data display depending on the size of the data, i.e. use a normal text display vs a blob editor to display the subsequent information. I'm refering here to the st_mysql_field C API structure and specifically the 'length' member as defined in mysql.h. However, other columns seems fine with correct lengths, it just seems the person responsible for checking the result of the show columns into this structure has not done a very good job, as the column type text can not possibly take even 1KB of space, doesnt matter which charset you use, nevermind 192KB per column row displayed! Kind Regards SciBit MySQL Team http://www.scibit.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Character set problem
Hi. I just installed MySQL 4.1.20 for the UTF-8 support it offers. I'm trying to set the charcter set collation at the database level but can't get it to work. It works by setting it at the server level (in my.cnf) but I want to set it at the database level because I'll be hosting the web app on a third-party web hosting server, where likely I won't have access to server level settings via my.cnf (is there a way around this?). I perform the following query: ALTER DATABASE my schema name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; After the query a dump of the relevant MySQL variables is as follows: character_set_client: utf8 character_set_connection: utf8 character_set_database: utf8 character_set_results: utf8 character_set_server: latin1 character_set_system: utf8 collation_connection: utf8_general_ci collation_database: utf8_general_ci collation_server: latin1_swedish_ci The variables look right but the characters (chinese in this case) do not display correctly in the web page. As mentioned before setting the charset at server level results in the characters displaying just fine. Any thoughts/guesses? Thanks in advance. _ Consolidate your email! http://www.fusemail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: special char in full-text search
MySQL doesn't index the special characters, some of which are considered word separators, like the period. MySQL indexes words, not strings (sort of). I had a similar problem on one of my projects where I needed to index resumes for searching. Typical searches would include C++, C#, .NET, etc. None of which mysql would index. What I did was created a word conversion list and added a search words field to the database. The search words field would contain the problematic search words into strings that MySQL would index (CPlusPlus, CSharp, dotNET, etc). The full text index would then be a compound index of the original text plus the search words field. The front end would handle searching the text for conversion words before inserting/updating the database. It would then convert the problematic words that were present and populate the search words field with them. The front would also do a similar conversion when a user entered a search for one of those words. It's not perfect, but I always used boolean full text searching and MySQL does support proximity searches, so it worked well enough. Although I never did figure out how to search on C, J, or any other single character programming language. Hope that gives you some ideas. - Original Message - From: Harini Raghavan [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, July 06, 2006 2:06 AM Subject: special char in full-text search Hi All, I am using the MySQL full text search capability in the search workflow in my appplication. I found that MySQL treats special character like *./,* etc. as tokenizers if they are not specified within a phrase(inside double quotes). For ex. If the search string entered is M.B.A or 24/7 then it searches for M, B, and A sepearately. I can't include the quotes for all search strings as wild char(*) search would not work when specified within a phrase. Is there a way to avoid MySQL from tokenizing the search criteria? Any ideas? Thanks, Harini -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.22 and show columns bug?
Although I know nothing about C I imagine this is because the 'type' column can contain all the possible values from an ENUM or SET field. James Harvard At 10:30 am + 6/7/06, SciBit MySQL Team wrote: Since a couple of recent stable versions back (and more recently, MySQL 5.0.22), MySQL has been returning the column length (C API) of the 'Type' column of a show columns from.. statement as being 196605 (almost 192KB), when this column only really contains data in the region of 10 bytes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stack explanation ?
In the last episode (Jul 06), Dilipan Sebastiampillai said: our server crashed yesterday with the following error : do_ypcall: clnt_call: RPC: Unable to receive; errno = Connection refused do_ypcall: clnt_call: RPC: Unable to receive; errno = Connection refused do_ypcall: clnt_call: RPC: Unable to receive; errno = Connection refused 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. [..] resolved stack gives : 0x80f7893 handle_segfault + 399 0xc5ed96 (?) 0x82ab930 strnmov + 24 0x8158a17 innobase_mysql_print_thd + 471 0x82302c1 lock_print_info + 1573 0x819f373 srv_sprintf_innodb_monitor + 507 0x815d4e2 _Z18innodb_show_statusP3THD + 138 0x8105657 _Z21mysql_execute_commandv + 5999 0x810868d _Z11mysql_parseP3THDPcj + 329 0x8103321 _Z16dispatch_command19enum_server_commandP3THDPcj + 1069 0x8102eb8 _Z10do_commandP3THD + 100 0x810280d handle_one_connection + 841 0xc59e51 (?) 0x50e06a (?) this is a Mysql 4.0.18 on a dedicated linux Red Hat Enterprise Linux ES release 3 ( kernel 2.4.21-20 ) with 3 Gig RAM could you tell me what happened please? It looks like the innodb show status command crashed trying to print its list of locks. You might want to upgrade to 4.0.27 (or even 4.1.* or 5.0.*). I don't see any bugfixes specifically related to your problem, but 4.0.18 is 2 years old. http://downloads.mysql.com/archives.php?p=mysql-4.0 -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: automatic timestamp
Scott, I do not think that is how it works under revision 4.0.21. Thanks, Néstor :-) On 7/5/06, Scott Haneda [EMAIL PROTECTED] wrote: People, I have the following table: DROP TABLE IF EXISTS bid; CREATE TABLE bid ( bid_id int(11) NOT NULL auto_increment, bid_proj_name varchar(100) NOT NULL default '', bid_prop_name varchar(100) NOT NULL default '', bid_amount varchar(20) NULL default '', bid_sub_name varchar(100) NOT NULL default '', bid_sub_desc varchar(100) default '', bid_sub_amount varchar(20) NULL default '', bid_winner tinyint(1) NOT NULL default '0', bid_date TIMESTAMP, PRIMARY KEY (bid_id), UNIQUE KEY proj_prop (bid_proj_name,bid_prop_name) ) TYPE=MyISAM; and I am trying toload a file with the following data: ,Construction Management,RCG Consulting,,Orly-Belle ,Construction Management,RCG Consulting,,American Engineers I am using phpmyadmin and the date always gets enter as 00 What do I need to do to force the current time stamp? I am using mysql version 4.0.21-standard IIRC, you need two timestamp fields for auto to work, default timestamps for the first timestamp column will be ignored, but the first column will get auto timestamp in a table that has more than one timestamp column. I usually set up two, updated and added, and I always set the added field to NOW() It explains it pretty well in the docs here http://dev.mysql.com/doc/refman/4.1/en/timestamp-4-1.html -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- 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: automatic timestamp
Chris,
my situation is that I am reading a file using phpmyadmin and whne I do not add
the extra , then I get an invalid number of records, so I have to
have the extra ,
When I do an insert without the last comma, it does set the correct day.
I do not know that it will work if I add the fields that I am
importing via phpmyadmin
I guess I will just do it that way. When I am importing a file just
add the field names
that I am bringing in.
Thanks to all,
Néstor :-)
On 7/5/06, Chris [EMAIL PROTECTED] wrote:
Nestor wrote:
People,
I have the following table:
DROP TABLE IF EXISTS bid;
CREATE TABLE bid (
bid_id int(11) NOT NULL auto_increment,
bid_proj_name varchar(100) NOT NULL default '',
bid_prop_name varchar(100) NOT NULL default '',
bid_amount varchar(20) NULL default '',
bid_sub_name varchar(100) NOT NULL default '',
bid_sub_desc varchar(100) default '',
bid_sub_amount varchar(20) NULL default '',
bid_winner tinyint(1) NOT NULL default '0',
bid_date TIMESTAMP,
PRIMARY KEY (bid_id),
UNIQUE KEY proj_prop (bid_proj_name,bid_prop_name)
) TYPE=MyISAM;
and I am trying toload a file with the following data:
,Construction Management,RCG Consulting,,Orly-Belle
,Construction Management,RCG Consulting,,American Engineers
I am using phpmyadmin and the date always gets enter as 00
What do I need to do to force the current time stamp?
I am using mysql version 4.0.21-standard
Don't specify the bid_date as an empty string - ie take the last ,
from the end. The empty string is being converted to '00' because
it's an invalid date.
If you don't specify that column, it will default to 'now()'.
mysql insert into bid(bid_proj_name) values ('test');
Query OK, 1 row affected (0.00 sec)
mysql insert into bid(bid_proj_name, bid_date) values ('test 2', '');
Query OK, 1 row affected (0.01 sec)
mysql select bid_id, bid_proj_name, bid_date from bid;
++---++
| bid_id | bid_proj_name | bid_date |
++---++
| 1 | test | 20060706110203 |
| 2 | test 2| 00 |
++---++
2 rows in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Character set problem
From: Spiros Philopoulos Sent: 06 July 2006 13:32 Hi. I just installed MySQL 4.1.20 for the UTF-8 support it offers. I'm trying to set the charcter set collation at the database level but can't get it to work. It works by setting it at the server level (in my.cnf) but I want to set it at the database level because I'll be hosting the web app on a third-party web hosting server, where likely I won't have access to server level settings via my.cnf (is there a way around this?). I perform the following query: ALTER DATABASE my schema name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; After the query a dump of the relevant MySQL variables is as follows: character_set_client: utf8 character_set_connection: utf8 character_set_database: utf8 character_set_results: utf8 character_set_server: latin1 character_set_system: utf8 collation_connection: utf8_general_ci collation_database: utf8_general_ci collation_server: latin1_swedish_ci The variables look right but the characters (chinese in this case) do not display correctly in the web page. As mentioned before setting the charset at server level results in the characters displaying just fine. Any thoughts/guesses? The ALTER above only sets the character set for any future tables created, it won't convert any data already in tables. If you recreate the tables and data after setting the database char set, does it work? You can use 'SHOW CREATE table_name' to check what character set the tables are actually using. If you need to convert existing data see: http://dev.mysql.com/doc/refman/4.1/en/charset-conversion.html Mind you not sure how that fits with it working with the database char set set to utf8! hth, mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
remote monitoring of mySQL
What would be the best approach to remote monitoring of numerous mySQL databases remotely i.e. number of threads. number of open tables etc etc Can I do it with DBD::Perl Thanks Andy -- perl -e print qq^bIG VeRN ! ^^qq^#'#Yv#=D+ ^ Managed Services (in:control) Developer, Telindus, RG27 9HY DDI: +44 1256 709200, GSM: +44 7810 636652 This e-mail is private and may be confidential and is for the intended recipient only. If misdirected, please notify us by telephone and confirm that it has been deleted from your system and any copies destroyed. If you are not the intended recipient you are strictly prohibited from using, printing, copying, distributing or disseminating this e-mail or any information contained in it. We use reasonable endeavours to virus scan all e-mails leaving the Company but no warranty is given that this e-mail and any attachments are virus free. You should undertake your own virus checking. The right to monitor e-mail communications through our network is reserved by us. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show tables replacement
Anyone have an idea on this? Thanks Ed Reed [EMAIL PROTECTED] 7/5/06 1:54:25 PM Anyone have an idea on this? Thanks Ed Reed [EMAIL PROTECTED] 6/30/06 2:51:44 PM Opps! Thanks for the quick reply, Dan. But I forgot to mention I'm running MySQL 4.1.11 Any other suggestions? Thanks again. Dan Nelson [EMAIL PROTECTED] 6/30/06 2:48:57 PM In the last episode (Jun 30), Ed Reed said: Is there a Select statement I can use to get table names so that I could use other Select statement syntax on the results? What I'd like to do is this, SHOW Replace(TABLES, 'tbl','') Like 'tbl%'; But this doesn't work so I need a Select statement that can do the same thing. SELECT table_name FROM information_schema.tables; -- Dan Nelson [EMAIL PROTECTED]
Re: Show tables replacement
On 7/6/06, Ed Reed [EMAIL PROTECTED] wrote: Anyone have an idea on this? Upgrade. Or at least stop repeating the question. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: remote monitoring of mySQL
From: Andy Ford Sent: 06 July 2006 17:06 What would be the best approach to remote monitoring of numerous mySQL databases remotely i.e. number of threads. number of open tables etc etc Can I do it with DBD::Perl You could, run queries such as SHOW STATUS; SHOW TABLE STATUS; against each db and munge the results. mark Thanks Andy -- perl -e print qq^bIG VeRN ! ^^qq^#'#Yv#=D+ ^ Managed Services (in:control) Developer, Telindus, RG27 9HY DDI: +44 1256 709200, GSM: +44 7810 636652 This e-mail is private and may be confidential and is for the intended recipient only. If misdirected, please notify us by telephone and confirm that it has been deleted from your system and any copies destroyed. If you are not the intended recipient you are strictly prohibited from using, printing, copying, distributing or disseminating this e-mail or any information contained in it. We use reasonable endeavours to virus scan all e-mails leaving the Company but no warranty is given that this e-mail and any attachments are virus free. You should undertake your own virus checking. The right to monitor e-mail communications through our network is reserved by us. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error in statement
I've the following SQL statement which I created in MS Access. My table layout is the same in mySQL but it says I have an error and the query doesn't run SELECT AccommodationOverview.Title, AccommodationOverview.AccommodationOverviewID, AccommodationOverview.AccommodationID, Areas.Name FROM AccommodationOverview INNER JOIN (AccommodationAreas INNER JOIN Areas ON AccommodationAreas.AreasID = Areas.AreasID) ON AccommodationOverview.AccommodationID = AccommodationAreas.AccommodationID WHERE (((AccommodationAreas.AreasID)='58') AND ((AccommodationOverview.AccommodationOverviewID)='ENG') AND ((AccommodationOverview.AccommodationID)'1')) Any ideas ? Neil On 7/6/06, Ed Reed [EMAIL PROTECTED] wrote: Anyone have an idea on this? Upgrade. Or at least stop repeating the question. Jochem -- 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: MS Access gives error no. -7776.
Today I also carried out some thing same as you. I already have Primary Keys in all tables. Then I added a temporary timestamp field with default as '1999-11-11 11:11:11'. Then I updated all such columns with above value. There is no 'on_update current timestamp' settings, just defined default value. Then I relinked all tables and now it is working ok. Now I will try it on other open source databases. Note that there is no foreign key defined yet. It is working OK on subforms also. Only one thing is that it is giving error for 'Save Record' action now. I also got jet 3.0 manual from microsoft's site. There are clear definations for such errors. But this page is out dated and I could not find any manual for jet 4.0.Please check it. I will try to send it's link in next email. Regards, CPK On 7/6/06, Duzenbury, Rich [EMAIL PROTECTED] wrote: -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 04, 2006 5:45 PM To: C K; mysql@lists.mysql.com Subject: Re: MS Access gives error no. -7776. C K wrote: Thanks for your sugesstions. I tried to search this issue on microsoft's website. and found that this problem is related with timestamp fields. I have tried to connect from access 2k and 2003 to mysql database. It works well for all the things. but gives above error only when control jumps to subform with diff. table as it's recordsource. Strange thing is that on few PCs it gives error and on some it not gives any error. I have installed WinXP with SP2, Access 2003 with JET 4 sp 8. Also this error does not occurs when I used SQL server 2005 express edition I am tring to use seperate forms for dataentry. Thanks again CPK On 7/3/06, Duzenbury, Rich [EMAIL PROTECTED] wrote: Contrary to the advice given on working with MS Access, I've found that I can *only* get things to work with Access 2003 if I remove the timestamp field. Also, make sure your primary key column isn't larger than an int ( ie don't use int unsigned, and don't use bigint ). I recently converted an application with 1M+ records to use mysql as the backend. I had similar errors until I: Make sure every table haa a unique key field Convert all timestamp fields to datetime (via alter table) Add one (and only one) last_changed timestamp, and fill it with a valid stamp Null out datetime fields that have '-00-00 00:00:00' as their value. Refresh the ODBC links. Access likes to use the primary key field + the last_changed timestamp in it's update queries so that it can easily manage optimistic locking. It issues update queries like Update some_table ... where primary_key = 27 and last_changed = '2006-12-01 23:52:55'. If the query doesn't affect any rows, then Access knows that someone else changed the record since it was initially retrieved, and can then prompt the user accordingly. Access also throws errors when it finds that timestamp or datetime fields have '-00-00 00:00:00'. Immediately after I loaded the mysql tables with the access data, I ran an 'update $full_table set $full_column = NULL where $full_column = '-00-00 00:00:00' on every datetime column in the database. In the case of the special last_changed column, I set all records to '1980-01-01 00:00:00' immediately following the conversion. Since this column was added by the conversion, it is never referenced in any forms, which seems to help things. The bad news is that all of the automatic date fields that were in the original .mdb file now have to be filled in with 'now()' in all sorts of places like forms and insert queries. I also had some trouble where a data entry subform would not allow additions. The 'add record' part of the record navigator would be grayed out. Usually, this was because I had neglected to have a primary key + last_changed timestamp on the table in question. In some cases, it was because a data source for a subform was a query. What I did to get those to work was to change the datasource to the table, and supply a proper filter/link. That aside, I feel much better having the data backed by mysql. The application has now been running successfully for about three weeks and users do not notice any difference in performance. Our regular backup capability now encompasses backup and restore for the data, which is great for us. Regards, Rich -- 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: MS Access gives error no. -7776.
It's link is- http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccessdev/html/ODC_JetDatabaseEngineVersion30ODBCConnectivity.asp CPK * On 7/6/06, C K [EMAIL PROTECTED] wrote: Today I also carried out some thing same as you. I already have Primary Keys in all tables. Then I added a temporary timestamp field with default as '1999-11-11 11:11:11'. Then I updated all such columns with above value. There is no 'on_update current timestamp' settings, just defined default value. Then I relinked all tables and now it is working ok. Now I will try it on other open source databases. Note that there is no foreign key defined yet. It is working OK on subforms also. Only one thing is that it is giving error for 'Save Record' action now. I also got jet 3.0 manual from microsoft's site. There are clear definations for such errors. But this page is out dated and I could not find any manual for jet 4.0.Please check it. I will try to send it's link in next email. Regards, CPK On 7/6/06, Duzenbury, Rich [EMAIL PROTECTED] wrote: -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 04, 2006 5:45 PM To: C K; mysql@lists.mysql.com Subject: Re: MS Access gives error no. -7776. C K wrote: Thanks for your sugesstions. I tried to search this issue on microsoft's website. and found that this problem is related with timestamp fields. I have tried to connect from access 2k and 2003 to mysql database. It works well for all the things. but gives above error only when control jumps to subform with diff. table as it's recordsource. Strange thing is that on few PCs it gives error and on some it not gives any error. I have installed WinXP with SP2, Access 2003 with JET 4 sp 8. Also this error does not occurs when I used SQL server 2005 express edition I am tring to use seperate forms for dataentry. Thanks again CPK On 7/3/06, Duzenbury, Rich [EMAIL PROTECTED] wrote: Contrary to the advice given on working with MS Access, I've found that I can *only* get things to work with Access 2003 if I remove the timestamp field. Also, make sure your primary key column isn't larger than an int ( ie don't use int unsigned, and don't use bigint ). I recently converted an application with 1M+ records to use mysql as the backend. I had similar errors until I: Make sure every table haa a unique key field Convert all timestamp fields to datetime (via alter table) Add one (and only one) last_changed timestamp, and fill it with a valid stamp Null out datetime fields that have '-00-00 00:00:00' as their value. Refresh the ODBC links. Access likes to use the primary key field + the last_changed timestamp in it's update queries so that it can easily manage optimistic locking. It issues update queries like Update some_table ... where primary_key = 27 and last_changed = '2006-12-01 23:52:55'. If the query doesn't affect any rows, then Access knows that someone else changed the record since it was initially retrieved, and can then prompt the user accordingly. Access also throws errors when it finds that timestamp or datetime fields have '-00-00 00:00:00'. Immediately after I loaded the mysql tables with the access data, I ran an 'update $full_table set $full_column = NULL where $full_column = '-00-00 00:00:00' on every datetime column in the database. In the case of the special last_changed column, I set all records to '1980-01-01 00:00:00' immediately following the conversion. Since this column was added by the conversion, it is never referenced in any forms, which seems to help things. The bad news is that all of the automatic date fields that were in the original .mdb file now have to be filled in with 'now()' in all sorts of places like forms and insert queries. I also had some trouble where a data entry subform would not allow additions. The 'add record' part of the record navigator would be grayed out. Usually, this was because I had neglected to have a primary key + last_changed timestamp on the table in question. In some cases, it was because a data source for a subform was a query. What I did to get those to work was to change the datasource to the table, and supply a proper filter/link. That aside, I feel much better having the data backed by mysql. The application has now been running successfully for about three weeks and users do not notice any difference in performance. Our regular backup capability now encompasses backup and restore for the data, which is great for us. Regards, Rich -- 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]
See lock table
Friend, I need to see if a table is locked by some application or some user. Do you know some tools for this? (gpl license better). Or some sentence? When a table is locked, how can i unlock this table? My problem is that some applications cause an error and the user closes it with the task manager then the table remains locked. Regards -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: PBXT version 0.9.5 has been released
Your site has a bunch of JS errors (using IE) so I can't roll over ANY of the menus (left or upper right). I also cannot write to 'contact' because of this same error. Hence I send it here... To the list... *sigh* Can I also suggest not using a dark red hyperlink with black text. I didn't even realize those were links until I randomly rolled over one and it highlighted bright red. DÆVID -Original Message- From: Paul McCullagh [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 05, 2006 6:51 AM To: mysql@lists.mysql.com Subject: PBXT version 0.9.5 has been released Hi All, PBXT version 0.9.5 has been released and can be downloaded from http://www.primebase.com/xt. This version includes a number of major structural changes to the implementation in order to improve performance. In addition, all bugs reported so far have been fixed. For further information on this release, please read my blog, http://pbxt.blogspot.com, and the associated comments. Details of all changes made are given in the release notes below. PrimeBase XT (PBXT) is a new transactional database engine for MySQL. It has been designed for modern, web-based, high concurrency environments. The Beta release of PBXT is planned for September 2006. The current version is based on MySQL 4.1.16 and runs on Mac OS X and Linux. The next version will include support for the new 5.1 storage engine API and 64-bit processors. Any questions, comments and bug reports can be sent directly to me. Thanks for your support! Best regards, Paul McCullagh SNAP Innovation GmbH PBXT Release Notes == --- 0.9.5 - 2006-07-03 RN15: This version concludes the re-structuring of the PBXT implementation. I have made a number of major changes, including: - All files except the transaction logs are now associated with a particular table. All table related files begin with the name of the table. The extension indicates the function. - I have merged the handle and the fixed length row data for performance reasons. - Only the variable size component of a row is stored in the data log files. As a result the data logs can now be considered as a type of overflow area. - Memory mapped files are no longer used because it is not possible to flush changes to the disk. RN14: File names have the following forms: [table-name]-[table-id].xtr - These files contains the table row pointers. Each row pointer occupies 8 bytes and refers to a list of records. The file name also contains the table ID. This is a unique number which is used internally by XT to identify the table. [table-name].xtd - This file contains the fixed length data of a table. Each data item includes a handle and a record. The handle references a record in the data log file if the table contains variable length records. [table-name].xti - This file contains the index data of the table. [table-name]-[log-id].xtl - This is a data log file. It contains the variable length data of the table. A table may have any number of data log files, each with a unique ID. xtlog-[log-id].xt - These files are the transaction logs. Log entries that specify updates reference a data file record. Each active thread has its own transaction log in order to avoid contension. RN13: Fixed the bug Hang on DROP DATABASE. [RESOLVED: 0016] RN12: PBXT currently only supports the Serializable transaction isolation level. This is the highest isolation level possible and includes the repeatable-read functionality [RESOLVED: 0015]. This is implemented by giving every transaction a snapshot of the database at the point when the transaction is started. If the transaction tries to update a record that was updated by some other transaction after the snapshot was taken, a locked error is returned. A deadlock can occur if 2 transactions update the same record in a different order. PBXT can detect all deadlocks. RN11: I have implemented write buffering on the table data files. [RESOLVED: 0013] RN10: The unique constraint (UNIQUE INDEX/PRIMARY KEY) is now checked correctly. [RESOLVED: 0008] RN9: I have implemented a conventional B-tree algorithm for the indices (instead of the Lehman and Yoa B*-link tree). Although this reduces concurrency it improves the performance of queries significantly because of the simplicity of the algorithm. Deletion is also implemented in a very simple manner. [RESOLVED: 0007] RN8: PBXT now has only 2 caches [RESOLVED: 0006]: The Index Cache (pbxt_index_cache_size): This is the amount of memory the PBXT storage engine uses to cache index data and row pointers. This is all the data in the files with the extensions '.xti' and '.xtr'. This cache is managed in blocks of 2K. The Record Cache (pbxt_record_cache_size): This is the amount of memory the PBXT storage engine uses to cache table row data (handles and
PHP connects in Latin1 when it should do it in UTF-8
Hi, i'm having a bit of a headache with PHP and MySQL, i've some questions: 1) I've a database in UTF-8 and when i connect to it with mysql_connect, and exec a query with mysql_query, the results are in latin1. (i proved this with mysql_query(show variables like 'char%'); 2) Is there any way to force mysql to make connections in utf8? here is the mysql status command report: -[*code*]- mysql status -- mysql Ver 14.12 Distrib 5.0.22, for redhat-linux-gnu (x86_64) using readline 5. 0 Connection id: 304 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: stdout Using outfile: '' Using delimiter:; Server version: 5.0.22 Protocol version: 10 Connection: Localhost via UNIX socket Server characterset:utf8 Db characterset:utf8 Client characterset:utf8 Conn. characterset:utf8 UNIX socket:/var/lib/mysql/mysql.sock Uptime: 21 hours 12 min 22 sec Threads: 1 Questions: 63783 Slow queries: 0 Opens: 0 Flush tables: 1 Open t ables: 49 Queries per second avg: 0.835 -- -/[*code*]- -[*code*]- mysql show variables like 'char%'; +--++ | Variable_name| Value | +--++ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results| utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--++ -/[*code*]- I use FC5 and a fresh mysql install, the default encoding is in latin1. As you can see i setup all charsets are in UTF-8. And, even with this, PHP still connects to the DB in latin1. I don't know what else to do.. I don't want to do a mysql_query(set names utf8) at the beginning when somebody opens the page because it a site with 100k hits per second (that menas 200k qps). There must be a solution. Thanks! Santiago -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show tables replacement
Thanks for the helpful words. The fact of the matter is that I would love to upgrade but since 5.0x doesn't have all the capabilities of 4.1x, an upgrade is out of the question at this time. So I'm left to looking here for help. Thanks for your time. Anyone else have an idea on this? Jochem van Dieten [EMAIL PROTECTED] 7/6/06 9:47:26 AM On 7/6/06, Ed Reed [EMAIL PROTECTED] wrote: Anyone have an idea on this? Upgrade. Or at least stop repeating the question. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
auto_incrment seed number
Is there a way to set the auto_increment start number? I am trying to set up a development and test system and the application that is writing to the tables is confused as to which MySQL it is writing to. I don't have any contrtol over the other app but since the key value is taken back to the other app, I can determine which areas are having the problem if I start the dev instance at 1 and start the test instance at 100,000. Thanks - Sneak preview the all-new Yahoo.com. It's not radically different. Just radically better.
Re: auto_incrment seed number
On 7/6/06, Jake Peavy [EMAIL PROTECTED] wrote: On 7/6/06, Chuck Holzwarth [EMAIL PROTECTED] wrote: Is there a way to set the auto_increment start number? I am trying to set up a development and test system and the application that is writing to the tables is confused as to which MySQL it is writing to. I don't have any contrtol over the other app but since the key value is taken back to the other app, I can determine which areas are having the problem if I start the dev instance at 1 and start the test instance at 100,000. RTFM http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html: To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this: mysql *ALTER TABLE tbl AUTO_INCREMENT = 100;* forgot the stinkin' list. damn that's annoying... -- -jp Chuck Norris doesn't understand why you should consult your doctor if your erection lasts for more than 4 hours. His erections have been known to last for up to 15 days.
Re: Show tables replacement
On 7/6/06, Ed Reed [EMAIL PROTECTED] wrote: Thanks for the helpful words. The fact of the matter is that I would love to upgrade but since 5.0x doesn't have all the capabilities of 4.1x, an upgrade is out of the question at this time. So I'm left to looking here for help. What? An higher version not having some features of the lower version?! What feature you need that 5 don't have and that exist in 4.1? I'm asking because I plan an upgrade in the next weeks. Please, do not bump your question replying to yourself. We already heard, no one had time/knowledge to help you, if they do, they'll answer in your first mail. As to your question, if there was a way of knowing the table name and other info and getting a resultset out of it having access only to the table (restrict access to information_schema at version 5), then I guess SQL Injection attacks would ruin a LOT of sites. Table names are supposed to be static, so, most apps hard code their names. (but that IMHO). If someone point that out (I couldn't find it googling, nor searching manuals) it would be pretty intersting. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_incrment seed number
On Thu, 2006-07-06 at 13:41 -0700, Chuck Holzwarth wrote: Is there a way to set the auto_increment start number? I am trying to set up a development and test system and the application that is writing to the tables is confused as to which MySQL it is writing to. I don't have any contrtol over the other app but since the key value is taken back to the other app, I can determine which areas are having the problem if I start the dev instance at 1 and start the test instance at 100,000. http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: add users in mysql 5.0 ubuntu dapper drake
Always CC the list. You will get better / faster responses. CREATE USER user [IDENTIFIED BY [PASSWORD] 'password'] thats is the syntax it tells me to use so i typed CREATE USER dimitri [IDENTIFIED BY [PASSWORD] '**']; were the starts are my password, i hav tried fiddling with the syntax but nothing worked. for the abouve i get this error code, but i dont think the error code will help ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[IDENTIFIED BY [PASSWORD] '**']' at line 1 The stuff in []'s means it's optional. If you do create user blah; it will create user blah with no password If you do create user blah identified by 'xxx'; It will create user blah with password 'xxx' -- Postgresql php tutorials http://www.designmagick.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error in statement
Neil Tompkins wrote: I've the following SQL statement which I created in MS Access. My table layout is the same in mySQL but it says I have an error and the query doesn't run SELECT AccommodationOverview.Title, AccommodationOverview.AccommodationOverviewID, AccommodationOverview.AccommodationID, Areas.Name FROM AccommodationOverview INNER JOIN (AccommodationAreas INNER JOIN Areas ON AccommodationAreas.AreasID = Areas.AreasID) ON AccommodationOverview.AccommodationID = AccommodationAreas.AccommodationID WHERE (((AccommodationAreas.AreasID)='58') AND ((AccommodationOverview.AccommodationOverviewID)='ENG') AND ((AccommodationOverview.AccommodationID)'1')) First guess, remove the ()'s around the inner join and anything else unnecessary. You don't need them around each field in the where clause, it's too confusing. 2nd thing learn about table aliases :) SELECT o.Title, o.AccommodationOverviewID, o.AccommodationID, a.Name FROM AccommodationOverview o, AccommodationAreas aa, Areas a WHERE aa.AreasID=a.AreasID AND o.AccommodationID = aa.AccommodationID AND aa.AreasID=58 AND o.AccommodationOverviewID = 'ENG' AND o.AccommodationID '1' Much easier to read (and should fix the error) :) -- Postgresql php tutorials http://www.designmagick.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
