Re: Multi Level Transaction (InnoDB)
In the last episode (Dec 31), Harta Teo said: Just wondor how InnoDB handle Multi Level Transaction, For example, BEGIN DELETE FORM table1 WHERE id = 123 BEGIN New Transaction Level 2 DELETE FROM table1 WHERE id = 234 COMMIT --- Level2 ROLLBACK ---Level1 The manual ( http://www.mysql.com/doc/en/Implicit_commit.html ) says: The following commands implicitly end a transaction (as if you had done a COMMIT before executing the command): ... BEGIN ... If you want multiple rollback levels, take a look at the SAVEPOINT command: http://www.mysql.com/doc/en/Savepoints.html -- 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: is this possible?
To drop all tables, yes do a drop database. To drop all with something like a exclude, no. What you can do is an export of the tables you don't want to drop, then do a drop database + create database + import Marc. -Message d'origine- De : Nitin Mehta [mailto:[EMAIL PROTECTED] Envoyé : vendredi 16 janvier 2004 06:28 À : [EMAIL PROTECTED] Objet : is this possible? hello everybody, i was wondering, if there's a single command to drop all or selected tables from the database, something like, where i can give the table names (1-2-3) not to be deleted. Thanx in advance Nitin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How does key buffer work ?
Hi John, - Original Message - From: [EMAIL PROTECTED] Sent: Thursday, January 15, 2004 2:28 AM Subject: Re: How does key buffer work ? Matt, One last question and then I promise to drop the topic ... what would be the best way to force a complete load of an index into the key buffer ? It's no problem. :-) Sorry for the late reply. Off hand, to force an index to be loaded I would say run queries that scan each index. e.g. SELECT index_col FROM table; But remember the indexes will be unloaded from the key_buffer if the table is closed -- after things like FLUSH, ALTER, OPTIMIZE, and maybe more. BTW, MySQL 4.1.1 added a new key cache system that looks like it has more tunable stuff. From http://www.mysql.com/doc/en/News-4.1.x.html New key cache for MyISAM tables with many tunable parameters. You can have multiple key caches, preload index into caches for batches... But I don't see anything documented about it yet. :-( Thanks very much for your time. John You're welcome. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: is this possible?
thanx for reply but not a good idea. Though i did it through PHP script Nitin - Original Message - From: Mechain Marc [EMAIL PROTECTED] To: Nitin Mehta [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, January 16, 2004 1:38 PM Subject: RE: is this possible? To drop all tables, yes do a drop database. To drop all with something like a exclude, no. What you can do is an export of the tables you don't want to drop, then do a drop database + create database + import Marc. -Message d'origine- De : Nitin Mehta [mailto:[EMAIL PROTECTED] Envoyé : vendredi 16 janvier 2004 06:28 À : [EMAIL PROTECTED] Objet : is this possible? hello everybody, i was wondering, if there's a single command to drop all or selected tables from the database, something like, where i can give the table names (1-2-3) not to be deleted. Thanx in advance Nitin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DB layout - solutions needed
Hi all, i want to store an unfixed number of object-characteristics of an unfixed datatype for 1-8000 objects. Data is mostly read, rarely written. Is there an quite optimal table-structure for that? If i use one table for all characteristics i need as many columns as there are datatypes used. Only one column of those will be filled for each row. A lot of wasted space:( Casting the values to a fixed datatype will give the easiest structure at a high cost of performance:( Another way was to have one table for each datatype a characteristic may have. Then i will need almost one other table just for finding the table a value is stored in:( Are there any real solutions? Which would you prefer? TIA, TomH -- PROSOFT EDV-Lösungen GmbH Co. KG phone: +49 941 / 78 88 7 - 121 Ladehofstraße 28, D-93049 Regensburg cellphone: +49 174 / 41 94 97 0 Geschäftsführer: Axel-Wilhelm Wegmann[EMAIL PROTECTED] AG Regensburg HRA 6608 USt.183/68311http://www.proSoft-Edv.de -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SP features ...
Hi all, Will MySql 5.0 (and higher) allow to reference the result set of a stored procedure in the FROM clause of a query (... so as using Microsoft SqlServer, Sybase ASA, Oracle, etc) ? For example: SELECT * FROM sp_test (arg1, arg2, ) We are planning to migrate to MySql . but this is a primary feature ! Very Thanks. Nicola
Mysql 4.1.1 and PHP
Hi. I would like test mysql 4.1.1 with php but, i cann't connect to it. I have error when use password :( From mysql CC beta 0.9.4 i could connect to my 4.1.1 server to. I can connect to mysql 4.1.1 - only if i have no password set. What's going on ? Regards. Irens. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext creation on 4.1: ERROR 1034
Hi, Sergei Golubchik wrote: Hi! On Jan 13, Kurt Haegeman wrote: Hi, When trying to create a fulltext index on my large table, I get the following error: ERROR 1034 (HY000): 121 when fixing table Sorry, I still cannot repeat this :( Could you try to create a smaller test case ? I would expect that you need only a few rows from your table for this bug to appear. (of course, finding these exact rows in your gigabytes could be not easy :) Regards, Sergei A smaller test case (2.5M articles) failed also. mysql alter table articles2 - add fulltext( text ); ERROR 1034 (HY000): 121 when fixing table Table check was OK, diskspace check was OK. I'll try again with 1M records and let you know the result. Regards, Kurt.
Re: DB layout - solutions needed
MIME-Version: 1.0 Content-Type: text/plain Content-Transfer-Encoding: 8bit Hmm Will you have an object hierarchy of any type to speak of? If so, you should be able to factor quite a few things out, but then you're going to have the problem that objects further down the tree will take longer to store and retrieve, regardless of table type. Which language are you looking at building this funky thing in? If it's Java or C++, you might want to look at persistant object stores that are specifically for this purpose (for Java, you might consider Prevailer / Previlence, for C++ try ColdStore). Finally, XML may be what you need. As we all know, the longest-surviving transactional table-type in MySQL is BDB, built on top of the stalwart embedded key-value database technology from Sleepycat. Their XML product may be just what you're looking for. As a general rule, attempting to put this sort of data on top of the relational model rules out high performance. Even though the big three (Oracle 9i, MS SQL Server 2000, DB2 V8) all claim to handle XML data well, the performance is pathetic compared to what we're used to. Additionally, Object-Relational storage can be simplified (for many purposes) to having 1-to-1 relationships between classes (read tables). The best option (if you really want to use MySQL might look like this): CREATE TABLE funky_object_store ( object_serial INT AUTO_INCREMENT, object_classINT NOT NULL, object_data BLOB, PRIMARY KEY(object_serial), INDEX(object_class) ); Regarding object_class, if you've got less than 255 different classes, using an ENUM field may be more efficient (but may not be - MySQL currently has a shortcoming that means it compares ENUM values based on their string representations, not their index in the enumeration). Otherwise, using InnoDB tables with foreign keys might make you feel safe with the object_class specifiers being in a seperate table. Hope this helps! Regards, Chris On Fri, 2004-01-16 at 20:53, Tom Horstmann wrote: Hi all, i want to store an unfixed number of object-characteristics of an unfixed datatype for 1-8000 objects. Data is mostly read, rarely written. Is there an quite optimal table-structure for that? If i use one table for all characteristics i need as many columns as there are datatypes used. Only one column of those will be filled for each row. A lot of wasted space:( Casting the values to a fixed datatype will give the easiest structure at a high cost of performance:( Another way was to have one table for each datatype a characteristic may have. Then i will need almost one other table just for finding the table a value is stored in:( Are there any real solutions? Which would you prefer? TIA, TomH -- PROSOFT EDV-Lösungen GmbH Co. KG phone: +49 941 / 78 88 7 - 121 Ladehofstraße 28, D-93049 Regensburg cellphone: +49 174 / 41 94 97 0 Geschäftsführer: Axel-Wilhelm Wegmann[EMAIL PROTECTED] AG Regensburg HRA 6608 USt.183/68311http://www.proSoft-Edv.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql 4.1.1 and PHP
-Message d'origine- De : I.P. [mailto:[EMAIL PROTECTED] Envoy : Friday, January 16, 2004 6:41 AM A : [EMAIL PROTECTED] Objet : Mysql 4.1.1 and PHP Hi. I would like test mysql 4.1.1 with php but, i cann't connect to it. I have error when use password :( From mysql CC beta 0.9.4 i could connect to my 4.1.1 server to. I can connect to mysql 4.1.1 - only if i have no password set. What's going on ? Just connect without password, then set it one. Ugo Regards. Irens. -- 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: Multi Level Transaction (InnoDB)
Depending on your language, you might be able to fudge this up to work for yourself. In C/C++ I have a function that automagically decides whether I am beginning a new transaction when I am calling it or whether I want Oracle-style nested transaction functionality. My rollback function is aware of this also. The only thing that you can't do with this approach that Oracle can do with it's nested transaction functionality is (to my knowledge) alter the isolation level at each SAVEPOINT. For reasons unknown to me, Oracle allows this although I can not actually think of an application for it. Regards, Chris On Fri, 2004-01-16 at 18:18, Dan Nelson wrote: In the last episode (Dec 31), Harta Teo said: Just wondor how InnoDB handle Multi Level Transaction, For example, BEGIN DELETE FORM table1 WHERE id = 123 BEGIN New Transaction Level 2 DELETE FROM table1 WHERE id = 234 COMMIT --- Level2 ROLLBACK ---Level1 The manual ( http://www.mysql.com/doc/en/Implicit_commit.html ) says: The following commands implicitly end a transaction (as if you had done a COMMIT before executing the command): ... BEGIN ... If you want multiple rollback levels, take a look at the SAVEPOINT command: http://www.mysql.com/doc/en/Savepoints.html -- 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: setting a variable
-Message d'origine- De : Mikael Fridh [mailto:[EMAIL PROTECTED] Envoyé : Friday, January 16, 2004 4:19 AM À : Ugo Bellavance Objet : Re: setting a variable On Thursday 15 January 2004 19.51, Ugo Bellavance wrote: No problem, I thought I could change any variable at runtime, since 4.0. I've got no prob with doing it at a restart of the server. I tried putting set-variable = log=on at the end of /etc/my.cnf (redhat) and nothing changed. Thanks, Ugo I think it's not a normal variable, it needs to be passed as a command-line parameter to mysqld. in my.cnf put: log or if you need the logfile in some other dir than the datadir: log=/var/log/logfile Mikael. Thanks, I ried the latter, but I didn't have the right permissions on the file, so I tried with just log, it worked. I then changed my permissions and all went fine. I have another question: why so many people replied to me off-list, without even cc:'ing the list? Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql 4.1.1 and PHP
MIME-Version: 1.0 Content-Type: text/plain Content-Transfer-Encoding: 8bit You'll find that the problem is probably related to the new password format that the 4.1.x stream of MySQL uses. You can start the server with some switch (listed in the manual) that allows use of old passwords. Regards, Chris On Fri, 2004-01-16 at 23:21, Ugo Bellavance wrote: -Message d'origine- De : I.P. [mailto:[EMAIL PROTECTED] Envoyé : Friday, January 16, 2004 6:41 AM A : [EMAIL PROTECTED] Objet : Mysql 4.1.1 and PHP Hi. I would like test mysql 4.1.1 with php but, i cann't connect to it. I have error when use password :( From mysql CC beta 0.9.4 i could connect to my 4.1.1 server to. I can connect to mysql 4.1.1 - only if i have no password set. What's going on ? Just connect without password, then set it one. Ugo Regards. Irens. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting by date
I have created a sql statement that enables me to select all entries in to MySQL that are dated today, I am trying to do the same for all items that are now one day old and also two days old etc I am doing this: SELECT * FROM table WHERE category = 3 AND show = 1 AND date = NOW()-1 I have it working for todays records, it filters out any that don't match todays date or NOW(), but it still returns the same records for NOW()-1, or NOW()-2. I have tried DATE() but this gives me an error. What is the correct syntax for what I am trying to achieve? Assuming the field date is of type datetime: WHERE (TO_DAYS(CURDATE()) - TO_DAYS(date)) = 1 HTH -- Lowell Allen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB layout - solutions needed
Thanks Chris, Will you have an object hierarchy of any type to speak of? no hierarchy, they're all roots. Which language are you looking at building this funky thing in? If it's Java or C++, you might want to look at persistant object stores that are specifically for this purpose (for Java, you might consider Prevailer / Previlence, for C++ try ColdStore). Maybe i've miss-explained a bit: objects wasn't ment like what objects means in OOP. My objects are collective agreements in fact. They may be stored that way, but: Currently i have to store all data in dbase-DBFs accessing them with Visual Objects(http://cavo.com), but it will be stored in MySQL DBs in a few month. That's why for me any solution has to be relational. CREATE TABLE funky_object_store ( object_serial INT AUTO_INCREMENT, object_classINT NOT NULL, object_data BLOB, PRIMARY KEY(object_serial), INDEX(object_class) ); I need to be able to query the DB for a specific character- istic. So serializing the objects as OOP-objects into BLOBs isn't suitable for me. Regards, TomH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie - connect error
On Fri, 16 Jan 2004, tait sanders wrote: yep I've already done this. everything I do comes back with the same error: ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) I even deleted the mysql.sock and used 'mysql_config --socket' to recreate it. this produces a new mysql.sock but then trying to start mysql produces the error of before. can i uninstall mysql rather than deleting it. I've tried the 'rpm' utility but it's not on my os. thanks heaps for all your help. You can see if MySQL is actually running with ps. If it is running you can see what socket it's using with `netstat -a | grep mysql` You can try to connect through tcp/ip instead of the socket by using -h 127.0.0.1 at the commandline. If the server isn't up, the info should be in the error log why it doesn't like to start. cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5 problem with select and stored procs
Hello
I've installed MySQL 5 and I seem to have a couple of problems with it (or it does
with me).
After installing into a clean environment I created a new server (rm_development) in
the Control Center, and then created a new database.
The first problem/query is after having created a table ('mytable')and inserted a row,
when selecting from that table within CC I get:
1 row in set (0.01) sec
[RM_SERVER] ERROR 1146: Table 'rm_development.1' doesn't exist
Any ideas why it's looking for the table .1?
Secondly when I try and create a stored procedure :
create procedure sp_test()
BEGIN
select count(*) from mytable;
END
I get the following error message:
[RM_SERVER] ERROR 1064: 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 'END' at
line 1
Looks OK to me!
Is there anywhere I can get hold of the SQL:2003 syntax?
TIA
-
Email provided by http://www.ntlhome.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5 problem with select and stored procs
Hi, Any ideas why it's looking for the table .1? Could you post your query? Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data loses connection
On Thu, 15 Jan 2004, Jamie Murray wrote: Hi Guys, after waiting about a minute I get ERROR 2013 (HY000): Lost connection to MySQL server during query See if changing any of the SHOW VARIABLES LIKE 'net%timeout'; helps. Not sure why the crash popup comes up, however. cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[mysql 5.0, connection loss, procedure-related, easy to reproduce, detailed report] what you ever wanted to know about trees
Hi !
mysql 5.0 is _really_ cool.
this bug should be reproducable:
( cat table.sql ; ./populate.pl ; ./perfect_game.pl 32 ) | mysql test
after that executing the same procedure with same parameter again,
the connection is lost:
--
mysql call self_and_up(1);
+---+
| id|
+---+
| 1 |
| 1999 |
| 399 |
|79 |
|15 |
| 2 |
+---+
6 rows in set (0.20 sec)
Query OK, 0 rows affected (0.20 sec)
mysql call self_and_up(1);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql
--
use cut and paste:
==
table.sql
==
-- table.sql
drop table if exists node;
create table node ( id int unsigned not null,
rid int unsigned not null,
msg text not null,
primary key(id),
unique index(rid,id)
);
--
==
populate.pl
==
#!/usr/bin/perl
# populate.pl - [EMAIL PROTECTED]
our $level = 0;
our $parent = 0;
our @nodes;
sub rec() {
for my $i (1..$lim) {
my $node = $parent*$lim+$i;
$nodes[$level] = $node;
print insert into node (id, rid, msg) values ($node, $parent, \.join(-, 0,
@nodes[0..$level]).\);\n;
{
local $parent = $node;
local $level = $level+1;
rec() if $level $rec_depth;
}
}
}
sub populate_table(@) {
my %args = @_;
{
local $lim = $args{count} || 2;
local $rec_depth = $args{depth} || 2;
rec;
}
}
populate_table count = 5, depth = 6;
--
==
perfect_game.pl
==
#!/usr/bin/perl
# perfect_game.pl - [EMAIL PROTECTED]
sub query_parents_union(@) {
my %a = @_;
my $level = $a{max_level} || 0;
my $proc = $a{proc} || dummy;
my $s = select . join(,, map( { n0.$_ } @{$a{fields}}));
my $o = drop procedure if exists $proc;\n
.delimiter |\n
.create procedure $proc (IN v INT)\n
.begin\n;
for(my $x = 0; $x = $level; $x++) {
$o .= \n($s\n\tfrom .join(, , map( { $a{table} as n$_ } (
0..$x))).\n\twhere (
.\n\t\t(
.join( and , map( { n$_.$a{id} = .(($_ = $x) ? v :
n.($_+1)..$a{rid}) } (0..$x))).)
.\n\t\t)\n);
$o .=\nunion if $x != $level;
}
$o
.;\n
.end |\n
.delimiter ;\n
}
my $limit = $ARGV[0] || 0;
print query_parents_union(table = 'node', id = 'id', rid = 'rid', fields = [
'id' ], proc = self_and_up, max_level = $limit);
--
--
ciao -
Stefan
aclocal - emit a warning if -ac kernel is used.
Stefan TrabyLinux/ia32 office: +49-721-3523165
Mathystr. 18-20 V/8 Linux/alpha cell: +XX-XXX-XXX
76133 Karlsruhe Linux/sparc http://graz03.kwc.at
Germany Linux/arm mailto:[EMAIL PROTECTED]
Europe Linux/mips mailto:[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ELSEIF OR Equivalent
Hi there: I was wondering if it's possible to perform an elseif in SQL Using MySQL 3.23.49-log running on Debian Linux? My System: -- * MySQL 3.23.49-log * Debian Linux * PHP 4.2.1 The Logic: -- IF condition1 - THEN query column1. ELSEIF condition2 - THEN query column2. ELSE condition3 query column2. The SQL: (more pseudo code than anything else) -- SELECT a,b,c FROM companies WHERE IF(subscription AND no module , bitwise-data column1 ELSEIF no subscription AND no module , bitwise-data column2 , ELSE bitwise-data column2) ORDER BY a; The bitwise-data is an integer bit from a URL Query String, it needs to be compared with one of two columns (column1 or column2) but which column is used depends upon whether a company has a subscription or not. I really hope this makes sense. Are there any SQL gurus out there who might be able to help here? Many Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 3.23.58 seg faults occasionally
Michael, the crashes below happen in independent areas of code. The 2 first are inside InnoDB, and the third inside MySQL. This looks like random thread crashes, or random memory corruption. I assume that you have my.cnf set so that the memory usage cannot approach 2 GB. You are running a relatively new Linux kernel, 2.4.23. Did the crashes start when you upgraded Linux? Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL support from http://www.mysql.com/support/index.html . List:MySQL General Discussion« Previous MessageNext Message » From:Michael BacarellaDate:January 16 2004 12:32am Subject:MySQL 3.23.58 seg faults occasionally First we cut to the chase with a resolved stack trace from the most recent crash: 0x80c23d5 handle_segfault__Fi + 425 0x40022f54 _end + 935506260 0x822cdef btr_search_build_page_hash_index + 4771 0x82281c3 btr_search_info_update_slow + 919 0x8213f9e btr_cur_search_to_nth_level + 3154 0x81e9dce row_sel_get_clust_rec_for_mysql + 102 0x81ece61 row_search_for_mysql + 6769 0x852 general_fetch__11ha_innobasePcUiUi + 322 0x8111220 index_next_same__11ha_innobasePcPCcUi + 40 0x80e7d7d join_read_next__FP14st_read_record + 53 0x80e74d9 sub_select__FP4JOINP13st_join_tableb + 341 0x80e7190 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 412 0x80dff58 mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemP8st_orderT4T3T4UiP1 3select_result + 5576 0x80c8aba mysql_execute_command__Fv + 806 0x80cbd88 mysql_parse__FP3THDPcUi + 72 0x80c7c74 do_command__FP3THD + 1324 0x80c7127 handle_one_connection__FPv + 659 and the one before it: 0x80c23d5 handle_segfault__Fi + 425 0x40022f54 _end + 935506260 0x823d4a8 trx_rseg_get_on_id + 24 0x823952d trx_undo_get_undo_rec_low + 45 0x823977d trx_undo_get_undo_rec + 49 0x82399c0 trx_undo_prev_version_build + 548 0x81f3f35 row_vers_build_for_consistent_read + 641 0x81e9d5e row_sel_build_prev_vers_for_mysql + 226 0x81ecda4 row_search_for_mysql + 6580 0x852 general_fetch__11ha_innobasePcUiUi + 322 0x8111373 rnd_next__11ha_innobasePc + 83 0x8103da6 rr_sequential__FP14st_read_record + 150 0x80e74d9 sub_select__FP4JOINP13st_join_tableb + 341 0x80e7190 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 412 0x80dff58 mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemP8st_orderT4T3T4UiP1 3select_result + 5576 0x80c8aba mysql_execute_command__Fv + 806 0x80cbd88 mysql_parse__FP3THDPcUi + 72 0x80c7c74 do_command__FP3THD + 1324 0x80c7127 handle_one_connection__FPv + 659 and the one before that: 0x80c23d5 handle_segfault__Fi + 425 0x40022f54 _end + 935506260 0x401141b7 _end + 936494007 0x80f42f7 write_header__9Log_eventP11st_io_cache + 91 0x80f426c write__9Log_eventP11st_io_cache + 24 0x80f424d write__15Query_log_eventP11st_io_cache + 37 0x80f write__9MYSQL_LOGP15Query_log_event + 1507 0x80eceec mysql_insert__FP3THDP13st_table_listRt4List1Z4ItemRt4List1Zt4List1Z4Item15en um_duplicates13thr_lock_type + 1752 0x80c9dd0 mysql_execute_command__Fv + 5692 0x80cbd88 mysql_parse__FP3THDPcUi + 72 0x80c7c74 do_command__FP3THD + 1324 0x80c7127 handle_one_connection__FPv + 659 Typically this happens to me on a heavily loaded server where I'm querying against a not very memory resident table so it takes a few seconds to load. Afterwards, when the table is better cached I issue a few more queries and one of them eventually causes the seg fault. It doesn't really crash on itself during normal load, only if I go in and introduce non-typical queries. All tables are InnoDB. Data and log files are stored on independent Linux MD based RAID-1 arrays. Data is stored on a raw array, logs are stored on an ext3fs. Host OS is Debian stable branch. The machine survived 18 days of CTCS burn-in before we turned MySQL loose. *** mysqlbug output: Server version 3.23.58-max-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 11 min 12 sec Threads: 10 Questions: 652813 Slow queries: 301 Opens: 39775 Flush tables: 1 Open tables: 256 Queries per second avg: 971.448 System: Linux dbms3 2.4.23 #1 SMP Tue Dec 23 03:08:01 EST 2003 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs gcc version 2.95.4 20011002 (Debian prerelease) Compilation info: CC='gcc' CFLAGS='-O2 -mpentiumpro' CXX='gcc' CXXFLAGS='-O2 -mpentiumpro -felide-constructors' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Dec 23 10:41 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x1 root root 1153784 Apr 8 2003 /lib/libc-2.2.5.so -rw-r--r--1 root root 2391002 Apr 8 2003 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 8 2003
Re: ELSEIF OR Equivalent
* Russell Michell I was wondering if it's possible to perform an elseif in SQL Using MySQL 3.23.49-log running on Debian Linux? It sure is. See below. My System: -- * MySQL 3.23.49-log * Debian Linux * PHP 4.2.1 The Logic: -- IF condition1 - THEN query column1. ELSEIF condition2 - THEN query column2. ELSE condition3 query column2. The SQL: (more pseudo code than anything else) -- SELECT a,b,c FROM companies WHERE IF(subscription AND no module , bitwise-data column1 ELSEIF no subscription AND no module , bitwise-data column2 , ELSE bitwise-data column2) ORDER BY a; It seems condition 2 and 3 are the same? typo? If they are the same,you don't need ELSEIF, just a normal IF-ELSE: IF(subscription AND NOT module,bitwise-data column1,bitwise-data column2). The bitwise-data is an integer bit from a URL Query String, it needs to be compared with one of two columns (column1 or column2) but which column is used depends upon whether a company has a subscription or not. Making column d dependant on subscription and module: SELECT a,b,c, IF(subscription AND NOT module, bitwise-data column1, IF(NOT subscription AND NOT module, bitwise-data column2, bitwise-data column2)) AS d FROM companies WHERE whatever ORDER BY a The IF() function is described here: URL: http://www.mysql.com/doc/en/Control_flow_functions.html#IDX1161 -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Issues using Week() or Weekofyear() functions with php
I'm trying to use the week() or weekofyear() functions in a php script.
Here's a clipping of the code:
$sql = SELECT WEEK('2004/06/01');
echo $sql; //used for checking purposes
$week = mysql_query($sql, $link);//where $link is in an inc file
linking that file to this page
echo $week;
Here is the result I get:
SELECT WEEK('2004/06/01')
Resource id #3
Am I doing something wrong? Is this supposed to work? Please help!!! :P
Thanks,
Ben
MySQL Query
How can change the syntax of the query below to a LIKE criteria query? SELECT ORGANIZATION_ID, ORGANIZATION_NAME, MATCH (ORGANIZATION_NAME) AGAINST (houston) AS score FROM ORGANIZATION WHERE MATCH (ORGANIZATION_NAME) AGAINST (houston) ORDER BY score DESC, ORGANIZATION_NAME ASC __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Issues using Week() or Weekofyear() functions with php
Ben Whitesell wrote:
I'm trying to use the week() or weekofyear() functions in a php script.
Here's a clipping of the code:
$sql = SELECT WEEK('2004/06/01');
echo $sql; //used for checking purposes
$week = mysql_query($sql, $link);//where $link is in an inc file
linking that file to this page
echo $week;
You are echo'ing the compleet Recordset. that is why you get the value of
Resource id #3
You can try the following:
$sql = SELECT WEEK('2004/06/01') AS weeknummer;
$recordset = mysql_query($sql, $link);
$week = mysql_result($recordset, 0, 'weeknummer');
Alexander Croes
--
http://www.viganed.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Query
toni baker wrote: How can change the syntax of the query below to a LIKE criteria query? SELECT ORGANIZATION_ID, ORGANIZATION_NAME, MATCH (ORGANIZATION_NAME) AGAINST (houston) AS score FROM ORGANIZATION WHERE MATCH (ORGANIZATION_NAME) AGAINST (houston) ORDER BY score DESC, ORGANIZATION_NAME ASC SELECT ORGANIZATION_ID, ORGANIZATION_NAME AS score FROM ORGANIZATION WHERE ORGANIZATION_NAME LIKE 'houston' ORDER BY score DESC, ORGANIZATION_NAME ASC For more info about LIKE look at: http://www.mysql.com/documentation/mysql/bychapter/index.html#Pattern_matching Alexander Croes -- http://www.viganed.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow innodb replication
Mysqlians, Very Simple, Slave A is all MyISAM tables and replicates ~15writes/sec for ~200k/s with a top load of 0.00. I change 1 table(100mb data 1.5writes/sec) to innodb and the top load changes to .3 . I have set innodb_trx_commit=0 and set noatime on the filesystem ( ext3 redhat linux 9.0 x86 xeon 2.4). I understand innodb writes more data but something is amiss. The load pattern drops slowly and the jumps every 30 seconds or so. So it seems something is getting flushed to disk but I can not accept it is this much worse then MyISAM. Once I replicate the entire database my load is 0.5 add all I am doing is replication. Any advice/suggestions/stabs in the dark is much appreciated. Trevor
SQL Help
Hello, Hopefully you sql guru's can help me out a bit. :) Here is the short example of what I want to accomplish. I wish to have the difference between two different select queries. So if one query pulls records 1,2,3 and 4 and the second pulls records 1 and 4 I wish to have only the records 2 and 3. How can I accomplish this easily. In case that doesn't make sense here is the long version. I have two tables that are keyed together through an id field. However this is not a one to one relationship, it is a one to many relationship. The following is an example of the table Table 1 Table 2 -- ID relid rid vid Table 1 and table two are linked through the columns id and rid. There can be many links between id and rid so the link is further refined through a vid field. What I need is all records in table 1 that will not link to table 2 such that relid=rid and vid=46 Which means that I want to find the parents who don't have children 46. A great way to do this would be with a minus operator but that isn't supported. Or to add two queries to a temporary table then delete the duplicate records but I don't know how to do that either. I hope this is clear I know it's confusing. But it's really slowing me down. Joe
MySql xa support
I've just downloaded the J/Connector 3.0 but there is no MysqlXaDataSource in it (actually there is not com.mysql.jdbc.jdbc2.optional.xa package), even do in the connector documentation it's written about it. I would like to try using mysql 4 with j/connector 3.0 because something I got a lot of errors in JBoss, such as : could not enlist xa-resource ecc.. Where can I find MysqlXaDataSource class? Thanks in advance, sorry for bothering you... Angela Fogarolli. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Help
* sulewski [...] What I need is all records in table 1 that will not link to table 2 such that relid=rid and vid=46 Sounds like a job for LEFT JOIN...? Join to the rows you do NOT want with a left join, and put as a condition in the WHERE clause that a joined column IS NULL. Something like this: SELECT tab1.* FROM tab1 LEFT JOIN tab2 ON tab2.rid=tab1.id AND tab2.vid=46 WHERE tab2.rid IS NULL -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Help
Roger, Thank you for the feedback. But unfortunately this doesn't work. The problem is that rid will never be null. I'm trying to find the item in tab1 where a link cannot be created in tab2 where tab2.rid=tab1.id and tab2.vid=46 because there is no record in tab2. Not that the record may have null values. I did try what you said and it didn't work. But thanks, Joe On Friday, January 16, 2004, at 11:49 AM, Roger Baklund wrote: * sulewski [...] What I need is all records in table 1 that will not link to table 2 such that relid=rid and vid=46 Sounds like a job for LEFT JOIN...? Join to the rows you do NOT want with a left join, and put as a condition in the WHERE clause that a joined column IS NULL. Something like this: SELECT tab1.* FROM tab1 LEFT JOIN tab2 ON tab2.rid=tab1.id AND tab2.vid=46 WHERE tab2.rid IS NULL -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Help
Roger, In regards to my last e-mail what would be great is if I can get all the records in tab1 then subtract from there all the records that match the query tab1.id=tab2.rid and tab2.vid=46. The result would give me what I need but alas mysql doesn't support minus. Joe On Friday, January 16, 2004, at 11:49 AM, Roger Baklund wrote: * sulewski [...] What I need is all records in table 1 that will not link to table 2 such that relid=rid and vid=46 Sounds like a job for LEFT JOIN...? Join to the rows you do NOT want with a left join, and put as a condition in the WHERE clause that a joined column IS NULL. Something like this: SELECT tab1.* FROM tab1 LEFT JOIN tab2 ON tab2.rid=tab1.id AND tab2.vid=46 WHERE tab2.rid IS NULL -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 3.23.58 seg faults occasionally
the crashes below happen in independent areas of code. The 2 first are inside InnoDB, and the third inside MySQL. This looks like random thread crashes, or random memory corruption. My colleague tells me that the third stack trace (the one inside MySQL) is from a RESET MASTER. This server used to be a slave that was promoted to master. My apologies for not making this distinction. The ones inside InnoDB have been triggered by querying disk-resident low use tables. I assume that you have my.cnf set so that the memory usage cannot approach 2 GB. You are running a relatively new Linux kernel, 2.4.23. Did the crashes start when you upgraded Linux? Well, maybe. This is a new machine. The previous machine was Red Hat 8.0's default kernel, which is 2.4.18 + RH patches. We were also running 3.23.56 which didn't seg fault like this. From the log, MySQL says: It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 1884024 K bytes of memory I never remember if the above calculation is usable or not. basedir=/usr/local/mysql/ user=mysql server-id=3 log-bin=/dblog/dbms3-bin log-slow-queries = /usr/local/mysql/data/dbms3.slow_queries set-variable = long_query_time=5 innodb_data_home_dir = innodb_data_file_path = /dev/md0:76319Mraw set-variable = innodb_buffer_pool_size=800M set-variable = innodb_additional_mem_pool_size=50M innodb_log_group_home_dir = /dblog innodb_log_arch_dir = /dblog innodb_log_archive=0 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=1.5G set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 set-variable = wait_timeout=360 set-variable = tmp_table_size=800 set-variable = max_connections=415 set-variable = table_cache=256 set-variable = sort_buffer=4M set-variable = key_buffer=128M set-variable = thread_cache_size=415 set-variable = max_connect_errors=5 set-variable = thread_stack=256k -- Michael Bacarella24/7 phone: 1-646-641-8662 Netgraft Corporation http://netgraft.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Help
That is the whole point of a left join. It joins to a null record when the appropriate right record does not exist. sulewski wrote: Roger, Thank you for the feedback. But unfortunately this doesn't work. The problem is that rid will never be null. I'm trying to find the item in tab1 where a link cannot be created in tab2 where tab2.rid=tab1.id and tab2.vid=46 because there is no record in tab2. Not that the record may have null values. I did try what you said and it didn't work. But thanks, Joe On Friday, January 16, 2004, at 11:49 AM, Roger Baklund wrote: * sulewski [...] What I need is all records in table 1 that will not link to table 2 such that relid=rid and vid=46 Sounds like a job for LEFT JOIN...? Join to the rows you do NOT want with a left join, and put as a condition in the WHERE clause that a joined column IS NULL. Something like this: SELECT tab1.* FROM tab1 LEFT JOIN tab2 ON tab2.rid=tab1.id AND tab2.vid=46 WHERE tab2.rid IS NULL -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Help
Gerald, Your right. You and Roger hit it on the head. Stupid me miss read Roger's original post. Last night I was banging my head on the left and right joins but I didn't understand it until I read Gerald's last note. Plus I didn't realize you can put two conditions in the ON clause which is why I didn't get Roger's post. Thank you very much guys. You saved the day. Joe On Friday, January 16, 2004, at 12:31 PM, gerald_clark wrote: That is the whole point of a left join. It joins to a null record when the appropriate right record does not exist. sulewski wrote: Roger, Thank you for the feedback. But unfortunately this doesn't work. The problem is that rid will never be null. I'm trying to find the item in tab1 where a link cannot be created in tab2 where tab2.rid=tab1.id and tab2.vid=46 because there is no record in tab2. Not that the record may have null values. I did try what you said and it didn't work. But thanks, Joe On Friday, January 16, 2004, at 11:49 AM, Roger Baklund wrote: * sulewski [...] What I need is all records in table 1 that will not link to table 2 such that relid=rid and vid=46 Sounds like a job for LEFT JOIN...? Join to the rows you do NOT want with a left join, and put as a condition in the WHERE clause that a joined column IS NULL. Something like this: SELECT tab1.* FROM tab1 LEFT JOIN tab2 ON tab2.rid=tab1.id AND tab2.vid=46 WHERE tab2.rid IS NULL -- Roger -- 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]
Importing special characters via text file
Hello all. Any help on the following matter is greatly appreciated. I tried to import a text file containing numerous special characters (À, Æ, Ç , etc.) but they convert to incorrect characters. I can paste them and write them correctly using a form submission created with LP6. However, any import corrupts the characters. Do they require special encoding? The manual had very little info on the matter of special characters. We are running MySQL v4.0.13 on OS X (10.2.8). Thanks. Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table Size in Bytes
How do I figure out the physical disk storage size of a table and of the containing database? I'm using InnoDB. When I look at the directory through the OS, I see the Ibdata1 file is about 1GB and a MyServer-bin.06 file that is about 900MB. There are also other -bin and various log files that are much smaller. However, I have two databases on this server and I'd like to know the physical size of each of them separately. I'd also like to know the physical size of the individual tables. Thanks. Randolph Randy L. Chrismon [EMAIL PROTECTED] Ph. 732-452-7610 Confidentiality Note: This message and any accompanying attachments contain information from the law firm Fragomen, Del Rey, Bernsen Loewy, P.C. which is confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this e-mail in error, please notify our offices immediately, by telephone at 212-688-8555, or by e-mail at [EMAIL PROTECTED]
mysql load
I was wondering how many records A mysql table can comfortably handle.. and which table type supports the greatest amount of record capacity. Can I have a few million records in 1 table? Over 10 million? Thanks !
Can not connect remotely
I have a new install completed. I can connect locally and have created a database from within my Terminal program on a Mac. When attempting to create a remote database connection from my HTML design program, I get: 1130 Host '66.51.193.2' is not allowed to connect to this MySQL server The above IP is the hosting computer's that is running MySQL. Help please. -- Robert Ameeti The geek shall inherit the earth. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Too many connections
I'm using mysql 3.23.55 and Linux Mandrake 9.1. I have about 60 users connecting to mysql using our accounting system. The users get some times a Too many connections error. I have set max_connections at 200, and the system opens only one connection per user at start up. Can somebody indicate where to look to calculate this value and eliminate this error? Thank you. -- Alfredo J. Cole http://www.acyc.com http://www.clshonduras.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB layout - solutions needed
Hi Brad, create table entity ( ent_id number primary key, ent_namevarchar(50) ); create table nameval ( nv_id number primary key, nv_ent_id number, nv_name varchar(50), nv_val varchar(50) ); That is what i prefered if i knew i have to store only char-values. The question is, if type-casting all values to char is more or less performant than wasting space by lots of emtpy fields. Currently i think, having a third table and one for each datatype will lead to some more joins and selects for getting the data, but it may be the clearest solution: create table entity ( ent_id number primary key, ent_namevarchar(50) ); create table valtypes ( vt_id number primary key, vt_name varchar(50) vt_type enum( int, longint ) ); create table vals_int ( v_idnumber primary key, vt_id number v_val int ); create table vals_char ( v_idnumber primary key, vt_id number v_val varchar(100) ); ...and one more table for each other datatype used. It's just that each more select takes more time:( Thanks, TomH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql load
On Fri, Jan 16, 2004 at 10:14:08AM -0800, Andrew Kwiczola wrote: I was wondering how many records A mysql table can comfortably handle.. and which table type supports the greatest amount of record capacity. Can I have a few million records in 1 table? Over 10 million? Thanks ! Hi, Andrew. I posted yesterday explaining that my table seems fairly slow for queries. The list archive should have the message at http://lists.mysql.com My table has over 498,000,000 records of fixed-length: CREATE TABLE `inv0web02` ( `docid` int(10) unsigned NOT NULL default '0', `offset` smallint(5) unsigned NOT NULL default '0', `termid` int(10) unsigned NOT NULL default '0', `taglistid` smallint(5) unsigned NOT NULL default '0', `whichpara` tinyint(3) unsigned NOT NULL default '0', `weight_in_subdoc` float unsigned NOT NULL default '0', PRIMARY KEY (`docid`,`offset`), KEY `termid_index` (`termid`), KEY `whichpara_index` (`whichpara`), KEY `taglistid_index` (`taglistid`), KEY `weight_index` (`weight_in_subdoc`), KEY `docid_index` (`docid`), KEY `offset_index` (`offset`), KEY `termid_docid_whichpara_offset` (`termid`,`docid`,`whichpara`,`offset`) ) TYPE=MyISAM; mysql show table status; | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +---+++---++-+-+--+---++-+-+-++-+ | inv0web02 | MyISAM | Fixed | 498093481 | 18 | 8965682658 | 77309411327 | 33526264832 | 0 | NULL | 2004-01-15 13:54:28 | 2004-01-15 14:42:01 | 2004-01-15 23:16:29 || | This takes about 40GB on disk, from 20GB of raw input. (I used INNODB previously, it took about 120GB on disk with comparable performance otherwise). To allow so many records with MyISAM, you need to ALTER TABLE tablename MAX_ROWS=[very large value]; to allow for pointers to be big enough for all the rows. So, I'd say that yes, you can create quite large tables in MySQL. Certainly for most purposes a few million records should not be any problem. -- Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign key contraints, on delete cascade not working?
Victoria Reznichenko wrote: Andrew DeFaria [EMAIL PROTECTED] wrote: Victoria Reznichenko wrote: Andrew DeFaria [EMAIL PROTECTED] wrote: As you can see I when I delete from user (the parent table) the useropts (child table) entry remains. Shouldn't it be deleted? Heikki Tuuri asked me to look at my innodb variables and I found: mysql show variables like %innodb%; +---+---+ | Variable_name | Value | +---+---+ | have_innodb | NO | +---+---+ 1 row in set (0.00 sec) But I still must ask: Why is that? How do I turn it on? Do you use 3.23.xx version? I'm using 4.0.10-gamma as mysql monitor indicates. Also: $ mysqld --version mysqld Ver 4.0.10-gamma for mandrake-linux-gnu on i586 You should install MySQL-Max binary if you want to use InnoDB: http://www.mysql.com/doc/en/InnoDB_in_MySQL_3.23.html This doesn't apply as MySQL version is 4.0.10. Any other ideas? Your MySQL server is configured without support for InnoDB tables. Hmmm... Could swore I responded to this but I don't see my response. Perhaps that spamcop thingy messed up the post. I will try again. When prototyping my application I found foreign keys and its delete on cascade capability and decided to use that feature. I made sure that I used type=innodb with my table creates. I even tested the feature out and it worked fine. I did not change the configuration of the MySQL server process to turn off support for InnoDB tables. Then, for some reason, on cascade delete stopped working so I'm asking here how do I get it working again. I'm not sure how to configure it with support for InnoDB tables. How would I do that? If I need to rebuild MySQL then I am completely confused as to why it used to work then stopped working when the MySQL server software has not changed. Perhaps a couple of direct questions: * How do I verify that InnoDB support is not turned on? (Is that that show variable thing?) * What do I do to turn it on? Is it just a simple setting of the variable in my.cnf and restarting the server or do I need to rebuild? * If I need to rebuild MySQL, I now have active data. Perhaps a pointer as to how to best rebuild MySQL software while retaining my current data would be really appreciated. Thanks. -- Do witches run spell checkers? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB locking 'non-existence' of a row
Hi, I wanted to take advantage of the InnoDB 'gap' locking to lock 'non-existence' of a row, the way the manual recommends. I tried to do this by using 'select ... for update', using the 'mysql' client from two separate sessions as shown below: Session 1: set AUTOCOMMIT=0; begin; select * from T where A = 'NOT_THERE' for update; Session 2: set AUTOCOMMIT=0; begin; select * from T where A = 'NOT_THERE' for update; What I'd hoped to see was the 'select' statement in Session 2 block until either a commit or a rollback was performed in Session 1. Unfortunately, it didn't work that way. The 'select's in both sessions returned right away, and it was only the subsequent 'insert's, 'update's and 'delete's that blocked. I can understand the rationale behind this behavior, but unfortunately it doesn't help me with my problem. I'd like to be able to reliably check for existence of a record from two concurrent sessions and have the 'select' in the 'second' session block until the first session is either committed or rolled back. Is there a way to accomplish this somehow? I know I can just try to insert the record and check for duplicates, but is there a way to accomplish it with 'select's? Thanks in advance, Alex Zeltser -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Memory leaks using MySQL C Api
I have the following code:
//try the mysql connection
mysql_init(mysql_connection);
if(!mysql_real_connect(mysql_connection, db_host, db_user, db_pass,
db_db, 0, NULL, 0)) {
flockfile(stderr);
fprintf(stderr, %s: Failed to connect to database: Error:
%s\n, timestamp, mysql_error(mysql_connection));
funlockfile(stderr);
mysql_close(mysql_connection);
return(2);
}
mysql_close(mysql_connection);
This code is creating a memory leak. Am I missing some cleanup calls? I'm
under the impression all I should need to do is call mysql_close to clean up
the connection? I'm testing this using mtrace, if I place a return directly
above the code segment it reports no leaks, if I place it direcly below the
fragment there are several variables reported as not being freed. Any
ideas?
John A. McCaskey
Re: MySql xa support
Hi! On Jan 16, angela wrote: I've just downloaded the J/Connector 3.0 but there is no MysqlXaDataSource in it (actually there is not com.mysql.jdbc.jdbc2.optional.xa package), even do in the connector documentation it's written about it. I would like to try using mysql 4 with j/connector 3.0 because something I got a lot of errors in JBoss, such as : could not enlist xa-resource ecc.. Where can I find MysqlXaDataSource class? Thanks in advance, sorry for bothering you... Angela Fogarolli. See http://bugs.mysql.com/bug.php?id=1737 Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Live MySQL database
To those who want to try out a live MySQL on the Net can use the following from NAVICAT. You can choose client tool of your choice! Get more information about it on NAVICAT website: www.navicat.com IP/Server: server1.navicat.com User: navicat Password:navicatdemo Port: 4406 Enjoy! Hassan Shaikh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error at server startup
Hi! After some tries i finally managed to compile mysql 4.0.17 with ssl-support (openssl 0.9.7c, compiled with minGW 3.1.0.1) under windows2000. SHOW VARIABLES LIKE 'have_openssl' returns YES. when starting the server the following happens: $mysqld.exe --port=3306 --ssl-ca=cacert.pem --ssl-cert=server-cert.pem --ssl-key=server-key.pem --debug=trace --standalone my_load_path my_load_path my_load_path my_load_path my_load_path my_load_path my_load_path my_load_path _mymalloc _mymalloc _mymalloc _mymalloc set_default_charset_by_name | init_dynamic_array | | _mymalloc | | _mymalloc | init_dynamic_array | get_charsets_dir | get_charsets_dir | my_fopen | | _mymalloc | | _mymalloc | my_fopen | init_dynamic_array | | _mymalloc | | _mymalloc | init_dynamic_array | my_fclose | | _myfree | | _myfree | my_fclose | _myfree | _myfree set_default_charset_by_name init_dynamic_string | _mymalloc | _mymalloc init_dynamic_string get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat _mymalloc _mymalloc _myfree _myfree new_VioSSLAcceptorFd | _mymalloc | _mymalloc | kill_server Anyone who can help? Regards Wulle -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Selecting by date
From: Matthew Stuart [mailto:[EMAIL PROTECTED] I have created a sql statement that enables me to select all entries in to MySQL that are dated today, I am trying to do the same for all items that are now one day old and also two days old etc I am doing this: SELECT * FROM table WHERE category = 3 AND show = 1 AND date = NOW()-1 I have it working for todays records, it filters out any that don't match todays date or NOW(), but it still returns the same records for NOW()-1, or NOW()-2. I have tried DATE() but this gives me an error. What is the correct syntax for what I am trying to achieve? You probably want something like this: SELECT * FROM table WHERE category = 3 AND show = 1 AND date = NOW() - INTERVAL 1 DAY; Also note that if you're looking specifically at a date, you might want to use CURDATE() instead of NOW(), as NOW() returns the full -mm-dd hh:ii:ss stamp (whereas CURDATE() returns just -mm-dd). MySQL handles it silently just fine, but it's a good habit to get into. -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help: Error 1146: Table test.1 doesn't exist
Hi, When I enter this query select * from books, it gives me this error message: Error 1146: Table test.1 doesn't exist. What does that mean? This is MySQL 5.0 and I just installed today. How can I solve this problem? Thanks in advance. Jacklty The best thing to hit the internet in years - NetZero HiSpeed! Surf the web up to FIVE TIMES FASTER! Only $14.95/ month -visit www.netzero.com to sign up today! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Do I need Innodb?
Hi I have a web app that use's PHP/Mysql/MYISAM. I am starting to think I need INNODB table type. Scenario 1: I have noticed that if two users open a record to edit it in two different browsers and they edit the same field and then click update, the last user/browser gets written to the db, where the first users data is over written. I would hope it would write user ones info then write user twos info, without over writing user ones updates Will INNODB table type fix Scenario 1? If so will my current php code I have need to be edited to use innodb? I thought when reading about innodb that it will handle updates with out specifying begin; commit;? UPDATE ... SET ... WHERE ...: sets an exclusive next-key lock on every record the search encounters. Or in my php do I need to ; BEGIN WORK; update statement; COMMIT; Thanks Ron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
File_priv syntax?
Greetings all, I'm trying to grant a user the file privelege and am getting error messages. Here's my SQL statement mysql GRANT file ON bs.table1 TO [EMAIL PROTECTED] IDENTIFIED BY 'password123'; It gets the following error: ERROR 1144: Illegal GRANT/REVOKE command. Please consult the manual which privileges can be used. Thanks tons! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: File_priv syntax?
On Fri, 16 Jan 2004, Jough P wrote: Greetings all, I'm trying to grant a user the file privilege and am getting error messages. Here's my SQL statement mysql GRANT file ON bs.table1 TO [EMAIL PROTECTED] IDENTIFIED BY 'password123'; It gets the following error: ERROR 1144: Illegal GRANT/REVOKE command. Please consult the manual which privileges can be used. the FILE privilege is a global privilege, and as such needs to be in the *.* context. It does not work on a db-level. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL and Perl DBI Persistent Connection
Hello, I have a Perl CGI script which uses DBI to access MySQL. I noticed that each query through the Perl CGI script using DBI has a connect, query and then quit. I was wondering if there is a way of keeping the connection persistent to Mysql through the Perl CGI script. In my Apache httpd.conf I inserted PerlModule Apache::DBI before all LoadModule lines, but that does not solve the problem. I also downloaded the Apache::DBI module from CPAN and installed it. Perhaps I need to do something else? I also removed any disconnect statements in my Perl CGI script, but I still see the quit in mysql.query.log. Thank you... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ISP and users
Hello, I run an ISP where our web customers have access to the MySQL server. When they want a database, they request it through me and I add it. I was just curious if this is the common way it is done, or if there is a safe way they can add their own? Thanks, Bryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repeated 100% CPU problem in FreeBSD
Hi ! Anyone find a solution for this problem ? We have 2 mysql server with same problem. Happen like every 1-2 months. Regards, -- Arnaud Pignard ([EMAIL PROTECTED]) Frontier Online - Opérateur Internet -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: transaction support
I wanted to thank everyone for their responses and information regarding this. I apologize, I thought I had already replied. Anyhow, I proved my point to our software guy, enough that he is willing to look into it further. Although I have a feeling he isn't going to want to do it (but at least he sees what MySQL can do) so I'll probably be trying to do it myself. So if anyone knows of a way to get a full interface under windows (that can print nice invoices) and has a direct brain-input for learning, let me know :) Thanks again, Bryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB locking 'non-existence' of a row
Hi Alex! On Sat, 2004-01-17 at 05:50, Zeltser, Alex wrote: Hi, I wanted to take advantage of the InnoDB 'gap' locking to lock 'non-existence' of a row, the way the manual recommends. I tried to do this by using 'select ... for update', using the 'mysql' client from two separate sessions as shown below: Session 1: set AUTOCOMMIT=0; begin; select * from T where A = 'NOT_THERE' for update; Session 2: set AUTOCOMMIT=0; begin; select * from T where A = 'NOT_THERE' for update; What I'd hoped to see was the 'select' statement in Session 2 block until either a commit or a rollback was performed in Session 1. Unfortunately, it didn't work that way. The 'select's in both sessions returned right away, and it was only the subsequent 'insert's, 'update's and 'delete's that blocked. I can understand the rationale behind this behavior, but unfortunately it doesn't help me with my problem. I'd like to be able to reliably check for existence of a record from two concurrent sessions and have the 'select' in the 'second' session block until the first session is either committed or rolled back. Is there a way to accomplish this somehow? InnoDB's next-key locking is a bit different to this - it ensures that phantom rows do not appear. This is good for application writers and for MySQL itself, as phantom rows appearing would break MySQL's replication. Basically, InnoDB will place locks on the various index structures involved in your query around the rows that have been returned bt a SELECT ... FOR UPDATE. As your selects return nothing, InnoDB doesn't find any index sections to place any locks on. Perhaps you should look at using the SERIALIZABLE level of transaction isolation. Regards, Chris I know I can just try to insert the record and check for duplicates, but is there a way to accomplish it with 'select's? Thanks in advance, Alex Zeltser -- 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: transaction support
Hmm...have you looked at Rekall? www.total-rekall.co.uk Also, you might want to check out OpenOffice.org's database interface features Regards, Chris On Sat, 2004-01-17 at 11:12, Bryan Koschmann - GKT wrote: I wanted to thank everyone for their responses and information regarding this. I apologize, I thought I had already replied. Anyhow, I proved my point to our software guy, enough that he is willing to look into it further. Although I have a feeling he isn't going to want to do it (but at least he sees what MySQL can do) so I'll probably be trying to do it myself. So if anyone knows of a way to get a full interface under windows (that can print nice invoices) and has a direct brain-input for learning, let me know :) Thanks again, Bryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RPM upgrades
Hello, we are in the process of upgrading our machine and currently running the following RPM's MySQL-server-4.0.15-0 MySQL-bench-4.0.15-0 MySQL-embedded-4.0.15-0 MySQL-devel-4.0.15-0 MySQL-client-4.0.15-0 MySQL-shared-compat-4.0.15-0 MySQL-shared-4.0.15-0 this is on a RH/Linux 7.3(i686) do we need to update ALL these RPM's if we want to go with the MAX version ?? TIA ;) -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
truncating results?
Hello all, I'm trying to query a database of speed test results for display on a php page. The results are fully-qualified machine names, often very long. What I'd like to do is trim it down so only the main domain name is left for display: for example, '68-65-69-187.vnnyca.adelphia.com' becomes just 'adelphia.com'. The problem is there is a more or less random amount of stuff to the left of the goodies I actually want. So...is there an easy way to just grab the two right-most bits between the periods? Thanks bunches! Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: truncating results?
* dan
I'm trying to query a database of speed test results for display on a php
page. The results are fully-qualified machine names, often very
long. What I'd like to do is trim it down so only the main domain name
is left for display: for example, '68-65-69-187.vnnyca.adelphia.com'
becomes just 'adelphia.com'. The problem is there is a more or less
random amount of stuff to the left of the goodies I actually want.
So...is there an easy way to just grab the two right-most bits between
the periods?
See the SUBSTRING_INDEX() function:
mysql select substring_index('68-65-69-187.vnnyca.adelphia.com','.',-2);
++
| substring_index('68-65-69-187.vnnyca.adelphia.com','.',-2) |
++
| adelphia.com |
++
1 row in set (0.00 sec)
URL: http://www.mysql.com/doc/en/String_functions.html#IDX1210
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ISP and users
On Fri, 2004-01-16 at 17:05, Bryan Koschmann - GKT wrote: Hello, I run an ISP where our web customers have access to the MySQL server. When they want a database, they request it through me and I add it. I was just curious if this is the common way it is done, or if there is a safe way they can add their own? Thanks, Bryan That's the way my ISP does it. Of course, that doesn't make it right. :-) Don Henson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
