Assigment sign not work on Delphi
Using Delphi to with this query: SELECT TOTAL := PRICE * QTY FROM INV_PRODUCT will caused error Parameter object is improperly defined. Inconsistent or incomplete information was provided. because Delphi look it as Parameter (a parameter of query in Delphi using : at the beginning). Is it better for MySQL using = rather than := ? Or is there any setting to set MySQL to accept the = sign? __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: dynamic fieldname to assign to
Thanks, Roger You pointed me to the right direction. The only part I had to change, was using the SET term once, as below. UPDATE Data INNER JOIN Updates ON ... SET Data.Jan = IF(Updates.Month = 1,Updates.Cell,Data.Jan), Data.Feb = IF(Updates.Month = 2,Updates.Cell,Data.Feb), Data.Mar = IF(Updates.Month = 3,Updates.Cell,Data.Mar), etc. And it works much faster than 12 separate queries. Regards, Cor Vegelin - Original Message - From: Roger Baklund [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: C.R.Vegelin [EMAIL PROTECTED] Sent: Thursday, November 17, 2005 11:53 PM Subject: Re: dynamic fieldname to assign to C.R.Vegelin wrote: Hi All, I have a simple problem and hope you can help me. I have an input table Updates with various fields, incl. Month and MonthlyValue. The field Updates.Month ranges from 1 to 12. I also have a target Data table with 12 fields, called Jan, Feb, Mar, ... `Dec`. Depending on Updates.Month the MonthlyValue must be put in the proper Data field. Now I use 12 UPDATE queries, like: UPDATE Data INNER JOIN Updates ON ... SET Data.Jan = Updates.Cell WHERE Updates.Month = 1; ... UPDATE Data INNER JOIN Updates ON ... SET Data.Dec = Updates.Cell WHERE Updates.Month = 12; My question: can it be done in a single query ? Try something like this: UPDATE Data INNER JOIN Updates ON ... SET Data.Jan = IF(Updates.Month = 1,Updates.Cell,Data.Jan), SET Data.Feb = IF(Updates.Month = 2,Updates.Cell,Data.Feb), SET Data.Mar = IF(Updates.Month = 3,Updates.Cell,Data.Mar), ... -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIMIT in subquery or GROUP_CONCAT
On 17/11/2005, Peter Brodersen wrote: I would like to select top three from different parts in the same table, e.g. for the following data set: USE test; DROP TABLE IF EXISTS foo; CREATE TABLE foo ( fid INT NOT NULL, d INT NOT NULL ); INSERT INTO foo VALUES (1, 10), (1, 20), (1, 30), (1, 40), (2, 10), (2, 20), (2, 30), (3, 10), (4, 10), (4, 20), (4, 20), (4, 20), (4, 30), (5, 10), (5, 20), (5, 50), (5, 50), (5, 50), (5, 50); SELECT f1.fid, f1.d ,COUNT(f1.fid) AS Ties FROM foo f1 WHERE (SELECT COUNT(*) FROM foo f2 WHERE f2.fid = f1.fid AND f2.d f1.d ) 3 -- top 3 GROUP BY f1.fid, f1.d ORDER BY f1.fid, f1.d DESC; which also tries to handle ties. If you remove the 'COUNT() AS Ties' and the GROUP BY, you can have more than three results per fid when there are ties. -- felix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
dumping column password from table
Hi. I want to backup one table from a database, where one of the columns saves the passwords. If I use mysqldump I got the value in encrypted format, but I need to backup the table to load the table in mysql 4.1. The original table is a mysql 3.23 database. Iago.
Multiple Query Execution
Hello, Am I correct in thinking that if there are no dependencies between the multiple statements then the various statements are executed simultaneously by separate threads in the server? If so is there any way to turn this behaviour off and just use multiple query execution syntax to reduce the comms overhead between client and server? I am trying to do a comparatively large number of simple insertions into a particular table. [Currently about 500 per second.] MySQL 5.0.13 under Solaris. C API. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
termcap functions library... configure: error: No curses/termcap library found
I try to run ./configure --with-ndbcluster --prefix=/STEFAN/mysql-5.1.2-binary --with-partition --with-row-based-replication --with-innodb --without-berkeley-db --enable-thread-safe-client --enable-shared but it fails with checking for termcap functions library... configure: error: No curses/termcap library found I checked the /etc folder , there is a link to this library to lrwxrwxrwx 1 root root 23 Mar 23 2005 termcap - /usr/share/misc/termcap and the file is there : stefan:/usr/share/misc # ll ter* -rw-r--r-- 1 root root 923665 Apr 6 2004 termcap Do I need this somewhere else ? What can I do ? -- Jörg Nowak Marstallstr. 12 68723 Schwetzingen Telefon 06202 / 22416 Mobil 0172 / 7592 877 __ Erweitern Sie FreeMail zu einem noch leistungsstärkeren E-Mail-Postfach! Mehr Infos unter http://freemail.web.de/home/landingpad/?mc=021131 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to list priviledges
Is there a way to list the privilidges that a user has in MySQL? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
setup permission for stored procedure on mysql 5
If I defined I stored procedure as root I can't be able to call this procedure from a normal user. I tried to grant it the execute method but the sintax seems wrong For example suppose a simple store procedure like this: CREATE PROCEDURE `test_root`() BEGIN declare a int default 0; END if I try to grant the execute permission to other users I got an error like this: mysql grant execute on test_root to '%'@localhost; ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to s ee which privileges can be used The only way to get stored procedure working is to create it as normal users, but this is not a polite way. Someone can help ? Regards, Enzo [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to list priviledges
Jesse Castleberry wrote: Is there a way to list the privilidges that a user has in MySQL? Thanks, Jesse http://dev.mysql.com/doc/refman/5.0/en/show-grants.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to list priviledges
Jesse Castleberry wrote: Is there a way to list the privilidges that a user has in MySQL? Thanks, Jesse Yes. SHOW GRANTS FOR [EMAIL PROTECTED]; Did you even try the manual http://dev.mysql.com/doc/refman/5.0/en/show-grants.html? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to list priviledges
Is there a way to list the privilidges that a user has in MySQL? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A key question
Dear Jeremy, Thank you for your help. I do have an exact situation you have assume I have. Here is the output of SHOW CREATE TABLE +---+--- ---+ | Table | Create Table | +---+--- ---+ | TICKER_HISTORY_PRICE_DATA | CREATE TABLE `TICKER_HISTORY_PRICE_DATA` ( `price_data_ticker` char(8) NOT NULL default '', `price_data_date` date NOT NULL default '-00-00', `price_data_open` float default NULL, `price_data_high` float default NULL, `price_data_low` float default NULL, `price_data_close` float default NULL, `price_data_volume` float default NULL, UNIQUE KEY `tidadx` (`price_data_ticker`,`price_data_date`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +---+--- ---+ 1 row in set (0.01 sec) As you can see, Unique KEY is on two first fields, but most of the work, joins searches, will be done on the second field price_data_date. Here is my SHOW VARIABLES mysql show variables; +-+- -+ | Variable_name | Value | +-+- -+ | back_log| 50 | | basedir | /data/mysql/mysql-standard/reloc/mysql-standard-4.1.10a-sun-solaris2.9-s parc-64bit/ | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client| latin1 | | character_set_connection| latin1 | | character_set_database | latin1 | | character_set_results | latin1 | | character_set_server| latin1 | | character_set_system| utf8 | | character_sets_dir | /data/mysql/mysql-standard/reloc/mysql-standard-4.1.10a-sun-solaris2.9-s parc-64bit/share/mysql/charsets/ | | collation_connection| latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server| latin1_swedish_ci | | concurrent_insert | ON | | connect_timeout | 5 | | datadir | /raid5/mysqldata/ | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit| 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | expire_logs_days| 0 | | flush | OFF | | flush_time | 0 | | ft_boolean_syntax | + -()~*:| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit| 20 | | ft_stopword_file| (built-in) | | group_concat_max_len| 1024 | | have_archive| YES | | have_bdb| NO | | have_compress | YES | | have_crypt | YES | | have_csv| NO | | have_example_engine | NO | | have_geometry | YES | | have_innodb | YES | | have_isam | NO | | have_ndbcluster | NO | | have_openssl| NO | | have_query_cache| YES | | have_raid | NO | | have_rtree_keys | YES | | have_symlink| YES | |
RE: MySQL server has gone away
Thank you for your help, Shawn. I had to dig a little, but I found the way to set the global variable to a larger size and was able to complete my restore. I may yet get the hang of this. :-) Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 16, 2005 4:39 PM To: Kraer, Joseph Cc: mysql@lists.mysql.com Subject: Re: MySQL server has gone away Kraer, Joseph [EMAIL PROTECTED] wrote on 11/16/2005 04:17:17 PM: First let me say that I'm a newbie and that I've seen a few instances of this error in the list, but I haven't seen my case. I'm running WAMP5, v. 1.4.4. I'm trying to restore an Eventum v. 1.4 backup done with mysqldump in a LAMP machine. The restoration process invariably stops at line 517. I've split the 4.19GB backup file into 16MB portions for easy view and looked at the first file portion in WordPad. There's nothing esoteric in the neighborhood of line 517. I've tried to do this restoration through the following command line: shell mysql -uusername -ppassword eventum sourcefile.sql. The process also bombs out if I try to use MySQL Administrator. Any suggestions? Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp I saw that error frequently until I discovered what the max_allowed_packet setting was for. It sounds as though you have an extended insert command at that location of your file and that the length of the command exceeds the max_allowed_packet setting. SHOW VARIALBES LIKE 'max%'; Reset it to meet or exceed the largest INSERT command in your dump files. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
mysql_fix_privileges
hi, i just inherited a mysql server 4.1 and need to run mysql_fix_privileges on it. However one of the databases hosted on it is production and I'd rather avoid it to be down. I ran the script on an 4.0 server test that I had installed and which was running when I ran the script. I had no problem with it, and the server is still up without downtime. I hope everything will be fine as well for the production server, but in case of how can I back up my data to revert if any problem? If I do a dump of mysql database will it be enough?meaning if there is any problem during the execution of the script will it be enough to reload the mysql database? I also had no problem with the old_passwords on my test db, shall I expect any problem with the clients who would like to connect after I run the script? Last, one of the server on which I have no control (4.0.9-gamma) but that I have to use would need the update of the privileges tables as well: what are the consequences/risks in not running it? Thanks for your help. melanie _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a question of specifying Times for Recovery
Hello. If default-character-set can't set in [client]? The comprehensive explanations you will find at: http://bugs.mysql.com/bug.php?id=11673 wangxu [EMAIL PROTECTED] wrote: Follow is a part of my my.ini. --- [client] port=3306 default-character-set=utf8 --- If default-character-set can't set in [client]? - Original Message --- -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LIKE problem part II
I tried to investigate my previous problem with statements containing LIKE clause on a specific table. The problem was basically the following: mysql SELECT count(*) FROM user WHERE username LIKE 'o%'; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (0.00 sec) mysql SELECT count(*) FROM user WHERE username LIKE 'ors%'; +--+ | count(*) | +--+ | 91 | +--+ 1 row in set (0.01 sec) I checked the table: mysql CHECK TABLE user; +---+---+--+--+ | Table | Op| Msg_type | Msg_text | +---+---+--+--+ | database.user | check | status | OK | +---+---+--+--+ 1 row in set (0.77 sec) So the table seems to be healthy. I queried the same as above but with REGEXP instead of LIKE: mysql SELECT count(*) FROM user WHERE username REGEXP ^o.*; +--+ | count(*) | +--+ | 801 | +--+ 1 row in set (0.19 sec) mysql SELECT count(*) FROM user WHERE username REGEXP ^ors.*; +--+ | count(*) | +--+ | 91 | +--+ 1 row in set (0.23 sec) The result of the second query matches that of the corresponding LIKE query, but the first seems to be correct. Any ideas what the problem might be? Zoltan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A key question
Hi Mikhail, Thank you for your help. I do have an exact situation you have assume I have. Here is the output of SHOW CREATE TABLE `price_data_ticker` char(8) NOT NULL default '', `price_data_date` date NOT NULL default '-00-00', ... UNIQUE KEY `tidadx` (`price_data_ticker`,`price_data_date`) ... As you can see, Unique KEY is on two first fields, but most of the work, joins searches, will be done on the second field price_data_date. Could you provide some example queries? Likely the solution is to create another index on price_data_date, that could be used for searches by date that do not include ticker. As I mentioned before, an index on (a, b) can be used for (a) but not for (b) alone. However, it usually doesn't make sense to create an index on (b, a) as well, since if you have both columns in your query, usually the index on (a, b) would be fine. So I would suggest adding an index: ALTER TABLE `TICKER_HISTORY_PRICE_DATA` ADD INDEX (price_data_date); Keep in mind that will lock the table to add the index, and may take a few minutes (although I would expect less than two minutes for 32M rows) so it might not be a good idea to run while the market is open. :) If you could provide the exact query you were running, I could confirm that it would or would not help. :) Regards, Jeremy -- Jeremy Cole MySQL Geek, Yahoo! Inc. Desk: 408 349 5104 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't switch databases 5.0.15-0 RedHat ES4
Is this something new with mysql 5, a bug, or something I'm missing? I'm
not able to switch databases.
[EMAIL PROTECTED] 5.0.15-0-es4]# rpm -ivh
MySQL-client-standard-5.0.15-0.rhel4.i386.rpm
MySQL-devel-standard-5.0.15-0.rhel4.i386.rpm
MySQL-server-standard-5.0.15-0.rhel4.i386.rpm
[EMAIL PROTECTED] 5.0.15-0-es4]# mysql
mysql SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mysql');
Query OK, 0 rows affected (0.20 sec)
mysql FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.16 sec)
mysql exit
[EMAIL PROTECTED] 5.0.15-0-es4]# mysql -u root -p
mysql CREATE DATABASE FOO;
Query OK, 1 row affected (0.00 sec)
mysql GRANT ALL PRIVILEGES ON FOO.* TO 'bar'@'localhost' IDENTIFIED BY
'bar' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
mysql FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql use FOO;
ERROR 1049 (42000): Unknown database 'FOO;'
mysql show databases;
++
| Database |
++
| information_schema |
| FOO|
| mysql |
| test |
++
4 rows in set (0.06 sec)
mysql exit
[EMAIL PROTECTED] 5.0.15-0-es4]# ls /var/lib/mysql/FOO/
db.opt
[EMAIL PROTECTED] 5.0.15-0-es4]# mysql -u bar -p
mysql show tables;
ERROR 1046 (3D000): No database selected
mysql
mysql use FOO;
ERROR 1044 (42000): Access denied for user 'bar'@'localhost' to database
'FOO;'
mysql exit;
[EMAIL PROTECTED] 5.0.15-0-es4]# mysql -u bar FOO -p
mysql show tables;
Empty set (0.00 sec)
mysqlquit;
[EMAIL PROTECTED] 5.0.15-0-es4]# mysql -u root -p
mysql create database BLA;
Query OK, 1 row affected (0.00 sec)
mysql GRANT ALL PRIVILEGES ON BLA.* TO 'bar'@'localhost' IDENTIFIED BY
'bar' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql quit
[EMAIL PROTECTED] 5.0.15-0-es4]# mysql -u bar -p
mysql use FOO;
ERROR 1044 (42000): Access denied for user 'bar'@'localhost' to database
'FOO;'
mysql use BLA;
ERROR 1044 (42000): Access denied for user 'bar'@'localhost' to database
'BLA;'
mysql quit
[EMAIL PROTECTED] 5.0.15-0-es4]# mysql -u bar FOO -p
mysql show tables;
Empty set (0.00 sec)
mysql use BLA;
ERROR 1044 (42000): Access denied for user 'bar'@'localhost' to database
'BLA;'
mysql quit
[EMAIL PROTECTED] 5.0.15-0-es4]# mysql -u bar BLA -p
mysql show tables;
Empty set (0.00 sec)
mysql
Thanks !
walt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access to Stored Procedure Solved
I figured out a problem I was having, and just wanted to pass the solution along to anyone who may run into the same problem in the future. I've got a new problem now, but I'll deal with that in a separate message (if necessary). I was getting the error, #42000SELECT command denied to user [EMAIL PROTECTED] for table 'proc' when trying to execute a stored procedure from my ASP.NET application. I gave EXECUTE priviledges to the user, and could not understand why it didn't work. I finally ran across a message I found on Google that stated that you have to give SELECT privilidges to the user on mysql.proc. Once I did that, it's passed that problem. Just wanted to pass that along to anyone that may have a similar issue in the future. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A key question
Hi Jeremy, This is still work in progress but here are some samples of queries we will be running, that involved this table and this date field: == #this fails -- join on is horrible update COMPANY_NUMBERS left join TICKER_HISTORY_PRICE_DATA on ticker = price_data_ticker and date_sub(date_qtr, interval 0 day) = price_data_date and !isnull(price_data_ticker) and isnull(price_date) set price_date = price_data_date, price_open = price_data_open, price_close = price_data_close, price_high = price_data_high, price_low = price_data_low, price_date_volume = price_data_volume; #this succeeds -- putting the on clause in the where is fine -- using join on is horrible update COMPANY_NUMBERS, TICKER_HISTORY_PRICE_DATA set price_date = price_data_date, price_open = price_data_open, price_close = price_data_close, price_high = price_data_high, price_low = price_data_low, price_date_volume = price_data_volume where isnull(price_date) and ticker = price_data_ticker and date_sub(date_qtr, interval 0 day) = price_data_date and !isnull(price_data_ticker); mysql describe COMPANY_NUMBERS; +---+--- -+--+-++---+ | Field | Type | Null | Key | Default| Extra | +---+--- -+--+-++---+ | company_fkey | char(10) | | PRI || | | company_name | char(150) | | MUL || | | ticker| char(8)| | MUL || | | market| char(20) | | MUL || | | price_date| date | YES | | NULL | | | price_open| float | YES | | NULL | | | price_close | float | YES | | NULL | | | price_high| float | YES | | NULL | | | price_low | float | YES | | NULL | | | price_date_volume | float | YES | | NULL | | | total_shares_outstanding_date | date | YES | | NULL | | | total_shares_outstanding | bigint(20) | YES | | NULL | | | total_shares_outstanding_market_cap | bigint(20) | YES | | NULL | | | date_qtr | date | | PRI | -00-00 | | | date_ttm | date | YES | | NULL | | | filing_type_code_qtr | char(12) | | || | | filing_type_code_ttm | char(12) | | || | | annual_quarterly_indicator| char(1)| | || | | balsh_book_value | bigint(20) | YES | | NULL | | | balsh_total_assets| bigint(20) | YES | | NULL | | | balsh_cash_and_cash_equivalents | bigint(20) | YES | | NULL | | | incmst_revenue_qtr| bigint(20) | YES | | NULL | | | incmst_revenue_ttm| bigint(20) | YES | | NULL | | | incmst_net_income_qtr | bigint(20) | YES | | NULL | | | incmst_net_income_ttm | bigint(20) | YES | | NULL | | | incmst_extraordinary_items_qtr| bigint(20) | YES | | NULL | | | incmst_extraordinary_items_ttm| bigint(20) | YES | | NULL | | | incmst_ebitda_qtr | bigint(20) | YES | | NULL | | | incmst_ebitda_ttm | bigint(20) | YES | | NULL | | | incmst_effect_of_accounting_changes_qtr | bigint(20) | YES | | NULL | | | incmst_effect_of_accounting_changes_ttm | bigint(20) | YES | | NULL | | |
Function Does Not Exist
I'm using the following code in my ASP.NET application to add a new record to the database, and return the added ID: Cmd=New MySqlCommand(sp_InsertNewCamper,Conn) Cmd.CommandType = CommandType.StoredProcedure paramReqID = Cmd.Parameters.Add(return,SqlDbType.Int) paramReqID.Direction = ParameterDirection.ReturnValue Cmd.Parameters.Add(FirstName,FirstName.Text) Cmd.Parameters.Add(LastName,LastName.Text) Cmd.Parameters.Add(UserName,UserName.Text) Cmd.Parameters.Add(Password,Password.Text) Cmd.ExecuteNonQuery() cID = CStr(Cmd.Parameters(return).Value) When I execute this code, I get the error, #42000FUNCTION fccamp.sp_InsertNewCamper does not exist. However, it DOES exist. I can see it. It's named exactly the same, there are no misspellings or anything. One other difference is that this is calling it a function where it is actually a procedure. I could make this a function, I guess, however, I don't know how to write the code in ASP.NET to grab the return value. I've checked Google, and found very little on this error, and don't even know what to look for in the manual. Does anyone know what might be going on here? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A key question
Mikhail Berman wrote: Dear Jeremy, Thank you for your help. I do have an exact situation you have assume I have. Here is the output of SHOW CREATE TABLE CREATE TABLE `TICKER_HISTORY_PRICE_DATA` ( `price_data_ticker` char(8) NOT NULL default '', `price_data_date` date NOT NULL default '-00-00', `price_data_open` float default NULL, `price_data_high` float default NULL, `price_data_low` float default NULL, `price_data_close` float default NULL, `price_data_volume` float default NULL, UNIQUE KEY `tidadx` (`price_data_ticker`,`price_data_date`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | As you can see, Unique KEY is on two first fields, but most of the work, joins searches, will be done on the second field price_data_date. As others have pointed out, your UNIQUE KEY on (price_data_ticker,price_data_date) will serve as an index to speed queries which search for a specific value of price_data_ticker and queries which search for a specific combination of values of price_data_ticker and price_data_date, but it won't help queries which only search by price_data_date. Yet, most of the work, joins searches, will be done on the second field, price_data_date. In that case, you definitely need an index on price_data_date. Based on your description, I'd suggest you have your index backwards. What you need is an index on (price_data_date, price_data_ticker). This will satisfy searches on price_data_date and on combinations of the two. Hence, ALTER TABLE TICKER_HISTORY_PRICE_DATA DROP INDEX tidadx, ADD PRIMARY KEY (price_data_date, price_data_ticker); That will satisfy most of your queries. Then, the question becomes, do you need a separate, single-column index on price_data_ticker? That will depend on whether you run queries which select based on price_data_ticker without specifying price_data_date. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql 4.1 full table (nearly) - best practice to alter?
Simon, If your tables are too big, you want to archive/delete some information. I certainly cannot guide you on what to delete. What you describe below isn't going to do anything. From the documentation (http://dev.mysql.com/doc/refman/5.0/en/create-table.html): MAX_ROWS The maximum number of rows you plan to store in the table. This is not a hard limit, but rather an indicator that the table must be able to store at least this many rows. So you'll still hit up on your 4GB limit. What you need to do is delete data from your large table(s), or, if you need all the data accessible, split your larger table out into smaller tables. Seeing as I have no idea what kind of data you have, I can't really suggest anything other than that. -Sheeri On 11/17/05, Simon [EMAIL PROTECTED] wrote: Hi There, We are reaching 4GB with one of our tables (v4.1 on debian sarge) and are needing to run: ALTER TABLE tbl_name MAX_ROWS=10 AVG_ROW_LENGTH=nnn; as per the documentation.. I would be greatful for any input on best practices to do this, as the database needs to be offline for the absolute minimal amount of time so the fastest process that i can think of would be: 1). Backup everything! :) 2). mysqldump the table to a file 3). drop the table 4). recreate the table structure 5). do the alter 6). import the data back in Other questions are... can the alter be done to live data? how does this work? Simon -- 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: To upgrade, or not to upgrade...
Someone just asked this on the list, and another person answered -- MySQL recommends a 'gradual upgrade' -- http://dev.mysql.com/doc/refman/4.1/en/upgrade.html It all depends on what code is running against your database, etc. Read the upgrade section, it will help you devise a plan to figure out what you want to upgrade to. -Sheeri (who also advises you get a test and/or development box, for just this purpose) On 11/17/05, René Fournier [EMAIL PROTECTED] wrote: ...that is the question. I have some queries that would possibly benefit from subqueries, which means upgrading my stock Mac OS X Server 10.3.9 installation of MySQL (version 4.1.10a). The question is, and I would appreciate any comments, should I go just to 4.0.26 for the subqueries, or straight to 5.0.15? Also, the machine I am upgrading is a production box, so I am a little nervous about what can (and will) go wrong. Any good advice is much appreciated. Thanks. ...Rene -- 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: To upgrade, or not to upgrade...
René Fournier wrote: ...that is the question. I have some queries that would possibly benefit from subqueries, which means upgrading my stock Mac OS X Server 10.3.9 installation of MySQL (version 4.1.10a). The question is, and I would appreciate any comments, should I go just to 4.0.26 for the subqueries, or straight to 5.0.15? Also, the machine I am upgrading is a production box, so I am a little nervous about what can (and will) go wrong. Any good advice is much appreciated. Thanks. ...Rene Mysql supports subqueries starting with 4.1. 4.0 does not support subqueries. So, if you have 4.1.10a, you already have subquery support, and changing from 4.1.10a to 4.0.26 would be a downgrade, not an upgrade. On the other hand, as I understand it, a stock 10.3.9 server has mysql 4.0.16. 4.1.10a is the version on a stock 10.4.0 server (which is updated to 4.1.13 with one of the security updates to 10.4, if I recall correctly). Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A key question
Mikhail Berman wrote: Hi Jeremy, This is still work in progress but here are some samples of queries we will be running, that involved this table and this date field: == #this fails -- join on is horrible What do you mean by fails? Takes too long? Wrong results? Crashes client/server? In general, using ON for your JOIN conditions is better than putting them in the WHERE clause. It's never worse for equivalent queries. update COMPANY_NUMBERS left join TICKER_HISTORY_PRICE_DATA on ticker = price_data_ticker and date_sub(date_qtr, interval 0 day) = price_data_date Huh? Why not date_qtr = price_data_date? DATE_SUB is doing nothing useful here, but is taking time to execute on every row. Also, if there's an index on date_qtr, you just prevented its use by running date_qtr through a function. and !isnull(price_data_ticker) and isnull(price_date) set price_date = price_data_date, price_open = price_data_open, price_close = price_data_close, price_high = price_data_high, price_low = price_data_low, price_date_volume = price_data_volume; #this succeeds -- putting the on clause in the where is fine -- using join on is horrible But this is a different query! This is a JOIN, not a LEFT JOIN, so you cannot directly compare them. The difference between them is the type of JOIN, not the location of the join conditions. update COMPANY_NUMBERS, TICKER_HISTORY_PRICE_DATA set price_date = price_data_date, price_open = price_data_open, price_close = price_data_close, price_high = price_data_high, price_low = price_data_low, price_date_volume = price_data_volume where isnull(price_date) and ticker = price_data_ticker and date_sub(date_qtr, interval 0 day) = price_data_date and !isnull(price_data_ticker); In the first query, you give *all* your restrictions as join conditions, but in the second, they are all in the WHERE clause, so mysql will choose which to use as join conditions. These are not equivalent queries, so I expect they give different results. If you would describe exactly what you want this to do, I'm sure someone could help you get the right query. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Does Not Exist
Jesse,
I'm using the following code in my ASP.NET application to add a
new record
to the database, and return the added ID:
You may have persuaded dotNet that your sproc returns something, ie
that it is a function, but you apparently created it as a procedure. An
sproc returns nothing.
PB
-
Jesse Castleberry wrote:
I'm using the following code in my ASP.NET application to add a new record
to the database, and return the added ID:
Cmd=New MySqlCommand("sp_InsertNewCamper",Conn)
Cmd.CommandType = CommandType.StoredProcedure
paramReqID = Cmd.Parameters.Add("return",SqlDbType.Int)
paramReqID.Direction = ParameterDirection.ReturnValue
Cmd.Parameters.Add("FirstName",FirstName.Text)
Cmd.Parameters.Add("LastName",LastName.Text)
Cmd.Parameters.Add("UserName",UserName.Text)
Cmd.Parameters.Add("Password",Password.Text)
Cmd.ExecuteNonQuery()
cID = CStr(Cmd.Parameters("return").Value)
When I execute this code, I get the error, "#42000FUNCTION
fccamp.sp_InsertNewCamper does not exist". However, it DOES exist. I can
see it. It's named exactly the same, there are no misspellings or anything.
One other difference is that this is calling it a "function" where it is
actually a procedure. I could make this a function, I guess, however, I
don't know how to write the code in ASP.NET to grab the return value.
I've checked Google, and found very little on this error, and don't even
know what to look for in the manual. Does anyone know what might be going on
here?
Thanks,
Jesse
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.4/175 - Release Date: 11/18/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A key question
Michael, Thank you for your comments. This give me a new ideas how to work with this issues. And, no at this point we are not planning to work with price_data_ticker field itself. Regards, Mikhail Berman -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Friday, November 18, 2005 12:11 PM To: Mikhail Berman Cc: Jeremy Cole; Jasper Bryant-Greene; mysql@lists.mysql.com Subject: Re: A key question Mikhail Berman wrote: Dear Jeremy, Thank you for your help. I do have an exact situation you have assume I have. Here is the output of SHOW CREATE TABLE CREATE TABLE `TICKER_HISTORY_PRICE_DATA` ( `price_data_ticker` char(8) NOT NULL default '', `price_data_date` date NOT NULL default '-00-00', `price_data_open` float default NULL, `price_data_high` float default NULL, `price_data_low` float default NULL, `price_data_close` float default NULL, `price_data_volume` float default NULL, UNIQUE KEY `tidadx` (`price_data_ticker`,`price_data_date`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | As you can see, Unique KEY is on two first fields, but most of the work, joins searches, will be done on the second field price_data_date. As others have pointed out, your UNIQUE KEY on (price_data_ticker,price_data_date) will serve as an index to speed queries which search for a specific value of price_data_ticker and queries which search for a specific combination of values of price_data_ticker and price_data_date, but it won't help queries which only search by price_data_date. Yet, most of the work, joins searches, will be done on the second field, price_data_date. In that case, you definitely need an index on price_data_date. Based on your description, I'd suggest you have your index backwards. What you need is an index on (price_data_date, price_data_ticker). This will satisfy searches on price_data_date and on combinations of the two. Hence, ALTER TABLE TICKER_HISTORY_PRICE_DATA DROP INDEX tidadx, ADD PRIMARY KEY (price_data_date, price_data_ticker); That will satisfy most of your queries. Then, the question becomes, do you need a separate, single-column index on price_data_ticker? That will depend on whether you run queries which select based on price_data_ticker without specifying price_data_date. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A key question
MySQL can use the index on one of the columns in a multi-column index, with caveats. If this is your index, UNIQUE KEY `tidadx` (`price_data_ticker`,`price_data_date`) and you plan to use price_data_date in all your queries, but never price_data_ticker, then simply reverse the order of the columns in your index definition: UNIQUE KEY `tidadx` (`price_data_date`, `price_data_ticker`) If you have a composite index on columns a, b and c: create index a_b_c_idx ON table_name (a, b, c); and you query with a in the where clause, the composite index will be used. If you query with a and b in the where clause, the composite index will be used; ditto for a, b and c. But if you query with b (and only b) in the where clause, the index won't be used. If you use b and c in the where clause, the index won't be used. Look here for other examples: http://dev.mysql.com/doc/refman/4.1/en/multiple-column-indexes.html Don't add indexes you don't need - it slows down inserts (and updates where the indexed columns are being updated), uses up space in your database, and requires extra administration, etc. David Mikhail Berman wrote: Michael, Thank you for your comments. This give me a new ideas how to work with this issues. And, no at this point we are not planning to work with price_data_ticker field itself. Regards, Mikhail Berman -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Friday, November 18, 2005 12:11 PM To: Mikhail Berman Cc: Jeremy Cole; Jasper Bryant-Greene; mysql@lists.mysql.com Subject: Re: A key question Mikhail Berman wrote: Dear Jeremy, Thank you for your help. I do have an exact situation you have assume I have. Here is the output of SHOW CREATE TABLE CREATE TABLE `TICKER_HISTORY_PRICE_DATA` ( `price_data_ticker` char(8) NOT NULL default '', `price_data_date` date NOT NULL default '-00-00', `price_data_open` float default NULL, `price_data_high` float default NULL, `price_data_low` float default NULL, `price_data_close` float default NULL, `price_data_volume` float default NULL, UNIQUE KEY `tidadx` (`price_data_ticker`,`price_data_date`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | As you can see, Unique KEY is on two first fields, but most of the work, joins searches, will be done on the second field price_data_date. As others have pointed out, your UNIQUE KEY on (price_data_ticker,price_data_date) will serve as an index to speed queries which search for a specific value of price_data_ticker and queries which search for a specific combination of values of price_data_ticker and price_data_date, but it won't help queries which only search by price_data_date. Yet, most of the work, joins searches, will be done on the second field, price_data_date. In that case, you definitely need an index on price_data_date. Based on your description, I'd suggest you have your index backwards. What you need is an index on (price_data_date, price_data_ticker). This will satisfy searches on price_data_date and on combinations of the two. Hence, ALTER TABLE TICKER_HISTORY_PRICE_DATA DROP INDEX tidadx, ADD PRIMARY KEY (price_data_date, price_data_ticker); That will satisfy most of your queries. Then, the question becomes, do you need a separate, single-column index on price_data_ticker? That will depend on whether you run queries which select based on price_data_ticker without specifying price_data_date. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connection Problem
Hello all, Suddenly in the last hour or so the connection speed between our webserver and database server has skyrocketed to the point that our site is no longer usable! Just trying to connection via the mysql client takes a long time. Once the connection is established, however, queries seem to execute in a timely fashion. As far as we can tell the network connection between the two boxes is fine (at least testing by ping OK) and again, once connected everything seems fine. We've seen some errors in our logs that state: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'. Why would php mysql_connect be trying to connect to the local MySQL Server? Our connection string should point it to the db server. We're using php 4.3.9 and MySQL 4.0.20. Thanks for any help, Tripp __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Runing MySQL on boot
I'm running FreeBSD and MySQL version 4.1. How do I get FreeBSD to load MySQL on boot? I don't see a script file that came with the installation. Thank you. Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Runing MySQL on boot
If you´ve installed mysql from ports the startup sript is under /usr/local/etc/rc.d/ Change its permissions to be extecutable I hope that this help you Regards! ++ | ISC Edwin Cruz García | | IT Manager | | Transportes Medel Rogero SA de CV | | Desk: 01 (449) 910 30 90 x3054| | Movil: 01 (449) 111 29 03 | | email: [EMAIL PROTECTED] | ++ -Mensaje original- De: Andrew Kuebler [mailto:[EMAIL PROTECTED] Enviado el: Viernes, 18 de Noviembre de 2005 01:51 p.m. Para: mysql@lists.mysql.com Asunto: Runing MySQL on boot I'm running FreeBSD and MySQL version 4.1. How do I get FreeBSD to load MySQL on boot? I don't see a script file that came with the installation. Thank you. Andrew -- 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: Runing MySQL on boot
Andrew Kuebler wrote: I'm running FreeBSD and MySQL version 4.1. How do I get FreeBSD to load MySQL on boot? I don't see a script file that came with the installation. Thank you. Andrew Place a script called mysql-server.sh in /usr/local/etc/rc.d #!/bin/sh case $1 in start) /usr/local/server/mysql/bin/mysqld_safe --old-passwords ;; stop) /usr/bin/killall -TERM mysqld /usr/bin/killall -TERM mysqld_safe ;; *) echo Usage: `basename $0` (start|stop)2 ;; esac exit 0 And make a corresponding entry in /etc/rc.conf: mysql_enable=YES -- Erich Beyrent -- http://www.beyrent.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql 4.1 full table (nearly) - best practice to alter?
Hi, 0. stop the web server or avoid hitting the db ! 1). Backup everything! :) the mysqldump should suffice. 2). do the alter The alter does already a create table with new data and then exchance table. You need 2x the storage space though. 3. do the opposite of 0. -- Pooly Webzine Rock : 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: Connection Problem
Tripp Bishop wrote: Hello all, Suddenly in the last hour or so the connection speed between our webserver and database server has skyrocketed to the point that our site is no longer usable! Just trying to connection via the mysql client takes a long time. Once the connection is established, however, queries seem to execute in a timely fashion. As far as we can tell the network connection between the two boxes is fine (at least testing by ping OK) and again, once connected everything seems fine. It's hard to be sure without knowing more, but if the network is working, and mysql works fine once connected, it sounds like it might be a DNS problem. Often the solution is to list hosts by IP rather than hostname in the mysql user table, and run mysqld with --skip-name-resolve. See http://dev.mysql.com/doc/refman/5.0/en/access-denied.html, about a third of the way down, for some hints about DNS problems. We've seen some errors in our logs that state: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'. Why would php mysql_connect be trying to connect to the local MySQL Server? Our connection string should point it to the db server. Which logs? Are you sure this is the same problem? What is the connection string? Do you capture error messages from mysql in your php script? We're using php 4.3.9 and MySQL 4.0.20. Thanks for any help, Tripp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connection Problem
Michael, you hit the nail on the head. We added --skip-name-resolve and changed our connnection string to use the IP instead of FQDN and now things are working fine. It must have been something with our DNS/ISP since we changed nothing and that stuff is out of our control. The other thing was captured from our PHP logs. Not sure what that's on about. We'll just have to monitor it and see if pattern develops. Thanks for your help. Cheers, Tripp --- Michael Stassen [EMAIL PROTECTED] wrote: Tripp Bishop wrote: Hello all, Suddenly in the last hour or so the connection speed between our webserver and database server has skyrocketed to the point that our site is no longer usable! Just trying to connection via the mysql client takes a long time. Once the connection is established, however, queries seem to execute in a timely fashion. As far as we can tell the network connection between the two boxes is fine (at least testing by ping OK) and again, once connected everything seems fine. It's hard to be sure without knowing more, but if the network is working, and mysql works fine once connected, it sounds like it might be a DNS problem. Often the solution is to list hosts by IP rather than hostname in the mysql user table, and run mysqld with --skip-name-resolve. See http://dev.mysql.com/doc/refman/5.0/en/access-denied.html, about a third of the way down, for some hints about DNS problems. We've seen some errors in our logs that state: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'. Why would php mysql_connect be trying to connect to the local MySQL Server? Our connection string should point it to the db server. Which logs? Are you sure this is the same problem? What is the connection string? Do you capture error messages from mysql in your php script? We're using php 4.3.9 and MySQL 4.0.20. Thanks for any help, Tripp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql 4.1 full table (nearly) - best practice to alter?
sheeri kritzer wrote: Simon, If your tables are too big, you want to archive/delete some information. I certainly cannot guide you on what to delete. What you describe below isn't going to do anything. From the documentation (http://dev.mysql.com/doc/refman/5.0/en/create-table.html): MAX_ROWS The maximum number of rows you plan to store in the table. This is not a hard limit, but rather an indicator that the table must be able to store at least this many rows. So you'll still hit up on your 4GB limit. What you need to do is delete data from your large table(s), or, if you need all the data accessible, split your larger table out into smaller tables. Seeing as I have no idea what kind of data you have, I can't really suggest anything other than that. -Sheeri Sheeri, I think you've missed the point. The 4Gb limit is in mysql, not the filesystem. Mysql effectively doesn't limit tablesize, but the *default* pointer size limits you to a max of about 4Gb in a MyISAM table. To have a larger table, you need to tell mysql that it needs to use a larger pointer for that table, either at table creation, or with an ALTER TABLE such as the one Simon is proposing to run. See the last half of http://dev.mysql.com/doc/refman/5.0/en/table-size.html for more. Michael On 11/17/05, Simon [EMAIL PROTECTED] wrote: Hi There, We are reaching 4GB with one of our tables (v4.1 on debian sarge) and are needing to run: ALTER TABLE tbl_name MAX_ROWS=10 AVG_ROW_LENGTH=nnn; as per the documentation.. I would be greatful for any input on best practices to do this, as the database needs to be offline for the absolute minimal amount of time so the fastest process that i can think of would be: 1). Backup everything! :) 2). mysqldump the table to a file 3). drop the table 4). recreate the table structure 5). do the alter 6). import the data back in Other questions are... can the alter be done to live data? how does this work? Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Runing MySQL on boot
Andrew Kuebler wrote: I'm running FreeBSD and MySQL version 4.1. How do I get FreeBSD to load MySQL on boot? I don't see a script file that came with the installation. Thank you. Andrew Hi Andrew, You need edit your /etc/rc.conf file and add the following line: mysql_enable=YES Which basically tells the script that MySQL should be started when the server boots up. Regards --jm -- Jacques Marneweck http://www.powertrip.co.za/blog/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
moving the datadir
Hi, I'm on Solaris 9 with mysql 4.1. I've moved the datadir to a new location, and edited the mysql start script directly so that it now has the line: datadir=/export/www1/data The server restarts no problem, and my phpBB web app can talk to it no problem. However when I try to connect to it using the /usr/local/mysql/bin/mysql client I get: [EMAIL PROTECTED]:/usr/local/mysql: ./bin/mysql --user=mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 4.1.10a-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show databases; +--+ | Database | +--+ | test | +--+ 1 row in set (0.00 sec) mysql -- I'm not sure where it's getting the 'test' db from, but it's not showing the phpBB database - or other databases I have. I've tried creating a /etc/my.cnf file - but I can't get it to work. Thoughts? Thanks, -RO.
Re: mysqld crash with archive engine 2gb
Hello. It is strange for me that there isn't a stack trace. If you can - switch to the debug version of MySQL and run it under gdb to find out where it is crashing. See: http://dev.mysql.com/doc/refman/5.0/en/crashing.html http://dev.mysql.com/doc/refman/5.0/en/debugging-server.html When the mysqld process restarts, there is nothing in the logfile except the basic startup info. I have listed it below:0:33). 051116 10:06:33 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.0.15-standard' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) Number of processes running now: 0 051116 20:33:05 mysqld restarted 051116 20:33:05 InnoDB: Started; log sequence number 0 43665 051116 20:33:05 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.0.15-standard' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) As you can see from the log I am running 5.0.15. I installed using the provided RPMs on a RH ES3 box. Below is the tablestatus. mysql show table status like 'trade' \G *** 1. row *** Name: trade Engine: ARCHIVE Version: 10 Row_format: Compressed Rows: 0 Avg_row_length: 4137 Data_length: 2147483647 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2005-11-16 20:33:05 Update_time: 2005-11-16 20:33:05 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: max_rows=4294967295 avg_row_length=4137 Comment: 1 row in set (0.01 sec) Thanks for any assistance you can give. I am also looking at alternative solutions in which I use multiple ARCHIVE dbs with each being 2 gb. Marc -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Regarding SET FOREIGN_KEY_CHECKS=0;
Thank you Heikki! We had a problem where code in one of our program's EJB did not turn the FK CHECK back to 1. Another program that was started afterwards caused some bad data to be inserted in the database - as though the FK CHECK was = 0. It wasn't until we turned the FK CHECK back to 1 in the first program that the second one started to behave correctly. We use JBoss and MySQL 4.0.21. Any feedback on the nature of JBoss-MySQL transaction management will be most helpful to us. Thank you much in advance - Noga -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Friday, November 18, 2005 2:56 PM To: Noga Woronoff Cc: Heikki Tuuri Subject: Re: Regarding SET FOREIGN_KEY_CHECKS=0; Noga, please forward this communication to mysql@lists.mysql.com mysql -u root -p -e SET FOREIGN_KEY_CHECKS=0; /tmp/dump.sql FOREIGN_KEY_CHECKS is a per-session variable. When the above shell command returns, the session ends. Thus, no problem for others. Regards, Heikki Oracle/Innobase Noga Woronoff wrote: Hi Heikki - I don't know which user group to use and wonder whether you may answer a question for me? When you perform: mysql -u root -p -e SET FOREIGN_KEY_CHECKS=0; /tmp/dump.sql Does the InnoDB engine set the foreign key checks back to 1 again, automatically - once the database install is complete? Under what circumstances one can get into trouble if the FK check is not set back to 1 at the end of the transaction? Is there a white paper I can read on the subject since I cannot find anything? Is there a way to check whether the FK check is turned ON/OF? Thanks you MUCH in advance and GOOD LUCK in your new Oracle adventure! Noga Woronoff Interactive Constructs, Inc. 200 Boston Ave. Suite #1800 Medford, MA 02155 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
any alias for MySQL cluster question?
Hi, I'd ask some questions on MySQL cluster. Does anyone know if there is any email alias for the cluster questions? Thanks, Jenny
Re: any alias for MySQL cluster question?
On Fri, Nov 18, 2005 at 02:41:30PM -0800, Jenny Chen wrote: I'd ask some questions on MySQL cluster. Does anyone know if there is any email alias for the cluster questions? Yes, there is a mailing list for MySQL Cluster: http://lists.mysql.com/cluster Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
case insensitive search
Hi!
I have this code:
form method=post action=search.php
Search:
input type=Text name=SearchSalesperson size=35 value=?=
$_POST['SearchSalesperson'] ? maxlength=25
input type=Submit name=SubmitSearch value=Search
/form
and this
#searching for salesperson
if(isset($_POST['SubmitSearch']))
{
$SearchSalesperson =
mysql_real_escape_string($_POST['SearchSalesperson']);
$query = my_query(
SELECT FName, LName
FROM Salespersons
WHERE FName LIKE '%$SearchSalesperson%' OR LName LIKE
'%$SearchSalesperson%'
ORDER BY LName ASC, FName ASC
, 0);
while($result = mysql_fetch_array($query, MYSQL_ASSOC))
{
$SALESMEN[] = preg_replace(/$SearchSalesperson/, span
style=\background-color: gold;\$SearchSalesperson/span, $result);
}
}
but I'm getting case insensitive search?!? it's not the same I enter
afan or Afan or AFAN!??
Any ideas?
Thanks for any help.
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: case insensitive search
[EMAIL PROTECTED] wrote: but I'm getting case insensitive search?!? it's not the same I enter afan or Afan or AFAN!?? Your two statements contradict each other. Either you are getting case insensitive search, meaning that it *is* the same if you enter afan, Afan or AFAN, or you're getting case *sensitive* search, which means it isn't the same if you enter afan, Afan or AFAN. Which is it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
what function could detect a row locked by other client ?
Hi, I know select .. update could lock selected rows, if the connection not release the lock, others always wait. If I use get_lock() to get a logical lock by key xxxtable+xxxrow , but others may not update other database on the same server . What function could detect a row locked (by select ... update) by other client ? Best regard! Shuming Wang
Re: what function could detect a row locked by other client ?
I know a lot more about DB2, my main database, than MySQL. However, MySQL frequently does the same things in the same ways as DB2. _If_ MySQL behaves the same way as DB2 in this regard - and I do not know if it does - there is no function to determine if a given resource is locked. Instead, the program which is trying to get a lock but fails because another program has the necessary lock simply waits for a set amount of time. If the resource (table, row, or whatever) gets unlocked before the clock runs out, the waiting programs are permitted to try to get their own locks on the resource, basically on a first-come, first-served manner. If the clock runs out before the resource has been released by the first program, the waiting program(s) get return codes and messages that indicate that they timed out and what resource was not available. At that point, the program can decide to try again, as many times as it likes, or to give up. The timeout interval can be set/changed by the system administrator. Again, let me stress that this is how DB2 behaves. MySQL may very well behave differently. However, I have noticed a great many similarities between DB2 and MySQL so they may behave the same in this regard too. I'm looking forward to seeing other answers to your question from people who know exactly how MySQL behaves in this regard so that I can learn more about MySQL. Rhino - Original Message - From: wang shuming [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, November 18, 2005 11:06 PM Subject: what function could detect a row locked by other client ? Hi, I know select .. update could lock selected rows, if the connection not release the lock, others always wait. If I use get_lock() to get a logical lock by key xxxtable+xxxrow , but others may not update other database on the same server . What function could detect a row locked (by select ... update) by other client ? Best regard! Shuming Wang No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.4/175 - Release Date: 18/11/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.4/175 - Release Date: 18/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: case insensitive search
on 11/18/05 2:27 PM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: but I'm getting case insensitive search?!? it's not the same I enter afan or Afan or AFAN!?? Any ideas? Set the field type to binary, or use your php to compare the result to the entered result. -- - 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]
Re: LIKE problem part II
on 11/18/05 7:18 AM, Peczöli Zoltán at [EMAIL PROTECTED] wrote: The result of the second query matches that of the corresponding LIKE query, but the first seems to be correct. Any ideas what the problem might be? You are getting strange results. At this point I would suggest dumping the data, and looking at it in a editor to see if you can see what may be wrong. Also, reimport it back into a new test table and run your tests again. -- - 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]
about increment backup
Mysql increment backup technique is different from others.When a binlog file be created after enable binlong? thanks!
