RE: I need 50.000 inserts / second
Hi !!
You could maybe buffer the data in Your application
and then run inserts later... like this.
struct oneRow{
double timestamp;
double data;
etc, etc
}
struct oneRow rows[num_of_rows];
for (int i = 1; i num_of_rows; i++)
{
// collect data
rows[i].timestamp = (double) i;
rows[i].data = i / 1000;
etc etc
}
// then you can loop through your
// data and do inserts delayed
i = 0;
while(rows[++i].timestamp]
{
// do inserts
}
I might be missunderstanding You since I don't get this together...
You wrote:
The following C++ code with mysql++ takes 5 seconds to
execute in my Athlon 1.33 machine:
And later on:
I'm shocked with the performance of MySQL, a similar query
to compute 1 million records takes 1.17 seconds in MySQL
So , if similar query makes 1 million in about a second, how
come You have problems with only 50 K/s with another similar
query
=d0Mi=
Hello,
I intend to use MySQL in a data acquisition software. The actual version
stores the acquired data straight in files. The sample rate can get up to 50
kHz. I would like to know if there is some way to improve MySQL insert rate.
The following C++ code with mysql++ takes 5 seconds to execute in my Athlon
1.33 machine:
sql_create_2 (teste1, 1, 2, double, datahora, double, valor1)
int main() {
try { // its in one big try block
Connection con(use_exceptions);
con.connect(cesar);
Query query = con.query();
teste1 row;
// create an empty stock object
for (int i=1;i5;i++)
{
row.datahora = (double) i;
row.valor1 = i / 1000;
query.insert(row);
query.execute();
}
As you can see there are only two fields: a double timestamp and a double
value. In the real application there are some more double values. I need to
decrease this time to less than 1 second. Is there any kind of buffered
inserts or maybe a way that I could pass a matrix?
I'm shocked with the performance of MySQL, a similar query to compute 1
million records takes 1.17 seconds in MySQL and around 6 seconds in the
current system. So if I can decrease the insert time I'll definetly use
MySQL!
Thank you for the attention.
Best regards,
Cesar
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Newbie question : Can't connect to database through a web interface
Hi,
I'm developing a web application using mysqld as a localhost but
accessed from anywhere. My problem is simple, after entering form
data, and execute the php script, always returns an error I wrote in
case of unsuccesful connection to the db.
I did everything , create a user with many privileges, then flushed
the privileges, if I use PhpMyAdmin to access the db I have no
problems, the error happens when I use my program, what can be wrong
here are the lines I use to connect to the db and display error
message:
@ $db = mysql_pconnect(localhost, testdb, test123);
if (!$db)
{
echo Error: could not connect to database. Please try again later.;
exit;
}
mysql_db(mydb);
...
What can be wrong ?
I'm starting to learn both php and mysql and I still can't understand
why this happened.
Regards,
Rick
AmpaSA Engineering
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Alternative to decode() in Oracle
Hi All Is there any function in mysql which acts similar like decode() in Oracle.. The decode function acts like a procedural case statement inside a single SQL statment to return different values or columns based on the the values of other columns in the select statement. It is extremely versatile and can get you out of many tight spots. It was a favorite hacker tool before PL/SQL and worth learning how to drive. Mind you, decode() is not ANSI, and I have not seen it in the other major RDBMS offerings. Its one of the really nice things Oracle provides. (OK, so MS-Access provides iif, but these have to be nested and it gets ugly). -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Alternative to decode() in Oracle
Have a look below:
--
If expr1 is TRUE (expr1 0 and expr1 NULL) then IF() returns expr2,
else it returns expr3. IF() returns a numeric or string value, depending on
the context in which it is used:
mysql select IF(12,2,3);
- 3
mysql select IF(12,'yes','no');
- 'yes'
mysql select IF(strcmp('test','test1'),'no','yes');
- 'no'
---
expr1 is evaluated as an integer value, which means that if you are testing
floating-point or string values, you should do so using a comparison
operation:
mysql select IF(0.1,1,0);
- 0
mysql select IF(0.10,1,0);
- 1
---
mysql SELECT CASE 1 WHEN 1 THEN one WHEN 2 THEN two ELSE more END;
- one
mysql SELECT CASE WHEN 10 THEN true ELSE false END;
- true
mysql SELECT CASE BINARY B when a then 1 when b then 2 END;
- NULL
---
mysql select 'Monty!' REGEXP 'm%y%%';
- 0
mysql select 'Monty!' REGEXP '.*';
- 1
mysql select 'new*\n*line' REGEXP 'new\\*.\\*line';
- 1
mysql select a REGEXP A, a REGEXP BINARY A;
- 1 0
mysql select a REGEXP ^[a-d];
- 1
-
Did that help?
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Newbie question : Can't connect to database through a web interface
Type this line of code before your line
$db = mysql_pconnect(localhost, testdb, test123) or
die(mysql_error());
The error u get will be pretty descriptive.
If not revert back to me.
nev L.
-Original Message-
From: Ricardo Fitzgerald [mailto:[EMAIL PROTECTED]]
@ $db = mysql_pconnect(localhost, testdb, test123);
if (!$db)
{
echo Error: could not connect to database. Please try again later.;
exit;
}
mysql_db(mydb);
...
What can be wrong ?
I'm starting to learn both php and mysql and I still can't understand
why this happened.
Regards,
Rick
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help with InnoDB configuration
Hi. I need help with InnoDB configuration. - The machine is a 2 Pentium III 1000GHz, Memory 1G, 30GB SCSSI. - The operating system is FreeBSD 4.5. - The mysql server is 3.23.49 (using FreeBSD port). I read carefully section InnoDB in MySQL manual and set the innodb parameters as: # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M innodb_data_home_dir = /mysql/ innodb_log_group_home_dir = /mysql/ innodb_log_arch_dir = /mysql/ set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=100M set-variable = innodb_log_buffer_size=16M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=350M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 Note: I use my-huge.cnf as my.cnf. I'm testing InnoDB against MyISAM. I have two databases with the same data one have MyISAM tables and the other InnoDB tables. The queries in MyISAM are faster than in InnoDB. The same query in MyISAM executes in 2:30 minutesand in InnoDB in 27:00 minutes. I think I must have some wrong in configuration. Thanks in advance. Iago. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
displaying errors in load data from...
Hi, I've seen this question asked before, but I'm hoping maybe this is now possible. When one does a LOAD DATA INFILE... or similar and get a result like this: Query OK, 428 rows affected (4.30 sec) Records: 428 Deleted: 0 Skipped: 0 Warnings: 431 ...is there any possible way to see the errors? Is there any debugging that can be turned on on the server side to track these warnings? The data looks good, but I'm quite curious what's going on. I've seen the suggestion to do a dump and diff, but the order of the records in the file I'm importing is so random, the diff is useless. Any ideas/tips appreciated! thanks, Charles - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mirroring / synchronizing two MySQL servers on different machines.
Hello! Can anyone help me in this situation - S1: One MySQL on FreeBSD with direct connection to internet; S2: One MySQL on FreeBSD without direct connection (just intranet); I can't connect to S2 from non-intranet but I can connect to S1. What I need is to synchronize S1 and S2 realtime or, at least, once in an hour (updating two tables in one database) automatically. Is it possible, and if is, how? _ Agris Pudans IT Specialist Metro Leo Burnett - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
large data design for mysql
Hi, Within MySql if you have a large amount of data of the same format and also know that there are distinct groups within this data which you need to run many comprehensive searches against; is it better to have a well indexed large table or many small tables / databases for each distinct group of data? Thanks Zac - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Mirroring / synchronizing two MySQL servers on different machines.
There are two ways to do this. One open a hole in the firewall. Two put a second Ethernet card in each system and use this for BIN updates. Simon -Original Message- From: Agris [mailto:[EMAIL PROTECTED]] Sent: 31 May 2002 09:17 To: [EMAIL PROTECTED] Subject: Mirroring / synchronizing two MySQL servers on different machines. Hello! Can anyone help me in this situation - S1: One MySQL on FreeBSD with direct connection to internet; S2: One MySQL on FreeBSD without direct connection (just intranet); I can't connect to S2 from non-intranet but I can connect to S1. What I need is to synchronize S1 and S2 realtime or, at least, once in an hour (updating two tables in one database) automatically. Is it possible, and if is, how? _ Agris Pudans IT Specialist Metro Leo Burnett - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB Delete On Cascade
Hello People, I was just designing my database and I was planning to finally use the new feature provided by InnoDB : ON DELETE CASCADE So I tried the example : CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) TYPE=INNODB; Created two records : insert into parent values(5); insert into child values(0,5); Ok this works all fine. Also geives me errors when I give it a parent ID of an unexisting record, so works like it should. Now isn't the goal of ON DELETE CASCADE is that when the parent record is deleted that the child records that reference the parent record id get deleted aswell? Because this doesn't seem to work. I get : mysql delete from parent where id=5; ERROR 1217: Cannot delete a parent row: a foreign key constraint fails Do I have the wrong idea baout the feature or what might be wrong? Using Version 3.23.50-max-nt and InnoDB. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re[2]: Mirroring / synchronizing two MySQL servers on different mach ines.
First way is unacceptable, for sure. Second, hmz. Still not what i need. The deeper problem is: I want to put and to get data to that S2 from outside LAN (using Windows application). Besides, both servers must be active all the time. Ok, thanks for help. It seems i have to code some tool to do this :/ Agris SG There are two ways to do this. SG One open a hole in the firewall. SG Two put a second Ethernet card in each system and use this for BIN updates. SG Simon SG Hello! SG Can anyone help me in this situation - SG S1: One MySQL on FreeBSD with direct connection to internet; SG S2: One MySQL on FreeBSD without direct connection (just intranet); SG I can't connect to S2 from non-intranet but I can connect to S1. SG What I need is to synchronize S1 and S2 realtime or, at least, once in SG an hour (updating two tables in one database) automatically. SG Is it possible, and if is, how? SG _ SG Agris Pudans SG IT Specialist SG Metro Leo Burnett - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Multiple Queries
Sorry If this is the wrong list for this topic, I hope someone can shed some light onto my problem. I am building a php/ Mysql web site for a client which is a picture gallery. The web site has 3 frames (required by the designer so that the whole thing doesnt refresh all the time). Frame 1- Navigation Frame 2- Info on selected artworks and other related projects Frame 3- The artworks/ jpegs I have one table, with a name and info about the project and upto 5 urls of where the jpeg lies. When a project is selected in the navigation I request the record from the database, I create an array which contains the location of the jpegs and then display them in Frame 3. Now, I then have a piece of javascript which tells frame 2 to update itself. So I have parsed the record ID to it and it then open a query to the database and outputs the relevant information about the artworks. Lastly a third query is also sent to the database to see if there are any other projects in the gallery by the same artist- and then create a list of related links. So I have 3 database queries over two pages. I'm wondering if there is a more efficient way of doing this? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fw: Home Grown E-Learning Solution on Mysql/Php
Hello, I am quite new to the world of databases, but need to know if I can implement an e-learning solution using Mysql database and PHP. What then happens to the LMS Accorn standard if I need to track my students' progress. Please advise. Olusola - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
index not hit ?
show index from gwarp; mysql +---+++--+-+---+ -+--++-+ - | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | - +---+++--+-+---+ -+--++-+ - | gwarp | 1 | ipindex|1 | ip | A | 165538 | NULL | NULL | | - | gwarp | 1 | macindex |1 | mac | A | 167902 | NULL | NULL | | - | gwarp | 1 | esnindex |1 | esn | A | 165538 | NULL | NULL | | - | gwarp | 1 | esnindex |2 | timerecord | A |11753202 | NULL | NULL | | - | gwarp | 1 | sitename |1 | sitename| A | 238 | NULL | NULL | | - | gwarp | 1 | timerecord |1 | timerecord | A | 132 | NULL | NULL | | - +---+++--+-+---+ -+--++-+ then i use explain to analyze mysql explain select * from gwarp where timerecord = '0205281' ; +---+--+---+--+-+--+--+- ---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+--+- ---+ | gwarp | ALL | timerecord| NULL |NULL | NULL | 11753202 | where used | +---+--+---+--+-+--+--+- ---+ 1 row in set (0.00 sec) Pls help timercord index is not hit !! why ? how to amend ? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
DB pagination: COUNT, LIMIT...
(been trying to answer this question for a long time; a recent post on the topic has convinced me to admit defeat and ask the list...) I have a large database and would like to output 'results' page by page. 'Results' refers to fairly general database computations, including groups, joins, logical subqueries (implemented with temporary tables), as well as raw row-by-row data. The database has appropriate indices to make all of these operations efficient, so the mammoth database size (tens of millions of rows at the least) does not slow anything down. Of course, the simplest strategy is just to use LIMIT on every query and provide 'previous' and 'next' buttons on every page. Clearly, you know that the first page needs no 'previous' button, but figuring out if there should be a 'next' is a problem. My simple hack is just to ask for one more row than I plan to display, and if I get it, ignore it but put in a 'next' button. Ugly... The concerns have already begun, since as our database grows the total number of rows returned by these queries will grow to the millions, while people will generally only care about the first few pages of results. Does MySQL guarantee that it will always try to use indices to implement ORDER...LIMIT clauses? I never want to ask my database to sort several million records on every query; if MySQL isn't smart enough to optimize this I need to start putting together an automated merge table architecture... Also, the simple LIMIT clause isn't a general solution in the case of sub-queries (using temporary tables) and GROUP clauses, because there is not necessarily a correlation between number of rows in intermediate results and output results. The specific queries I use have been structured so as to compute intermediate results in an order which eliminates the unnecessary work, but I won't always have the luxury of such easily-optimized queries. Further, an interface which provides only 'previous' and 'next' buttons is clearly mediocre at best; what I'd really like is to spit out page number buttons (probably with exponential backoff: 1, 2, 3, 4, 5 ...10, 20, 30, 40, 50 ...100, 200, 300, 400, 500...), and this requires knowing the total number of rows the queries return. For trivial raw-data queries, this is simple; run the query twice: once with COUNT(*) and once with the actual field list. It does worry me a bit that this may require MySQL to actually analyze millions and millions of rows for every page spit out; can this COUNT be computed purely from index ranges? And I would dearly love an equivalent to num_affected_rows which told you how many rows matched a query in spite of your LIMIT clause so that the database didn't have to run the query twice... For more complex queries, things get more complicated. Simply adding a single GROUP to a query means that COUNT(*) loses its 'how-many-rows-in-result' meaning, so I've got to store the result in a temporary table before running a count on it, and even if MySQL were smart enough to optimize normal COUNT and ORDER...LIMIT, unless temporary tables are *really* smart (some kind of spiffy lazy evaluation) this would completely defeat those optimizations. Is there an efficient way to count the number of rows in the total result of a grouped query? When sub-selects and joins enter the picture, things get even worse, as large and complex queries must be run against the entire database to compute these giant temporary tables where I could previously hand-optimize the execution sequences to operate on only some multiple (or small exponent) of the number of records per page. Again, I can simply work to hand-optimize independent queries to return the counts directly, but it's even less general, still not provably efficient, and provides a substantial opportunity for bugs relating to inconsistency between the number of results expected and the number that could actually be found. Sigh. I'd love for someone to provide a magic bullet that I can add to all my SQL to just make it work, but at least in the more complex cases I don't think efficent solutions exist. What I'm really hoping is that a few people with intimate knowledge of the MySQL optimizer could answer the specific questions I've posed here, as well as offer a few tutorials on exactly how the optimizer and execution engine interact with order, limit, and group clauses as well as temporary tables so that I can at least design my queries with my eyes open. Maybe we can even work to add such information to the large body of documentation on where clause optimization. Many many thanks in advance for your help, Rob - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: I need 50.000 inserts / second
Depending on your available RAM length of your sampling runs, you
could write records to heap (in-memory) tables -
http://www.mysql.com/doc/H/E/HEAP.html
- and then dump those to disk after the sample was done. You might
even be able to use heap tables as a buffer with one process
acquiring data to the heap table, another writing records to disk
deleting from the heap table. Never used heap tables myself, though.
-steve
At 1:49 AM -0300 5/31/02, Cesar Mello - Axi wrote:
Hello,
I intend to use MySQL in a data acquisition software. The actual version
stores the acquired data straight in files. The sample rate can get up to 50
kHz. I would like to know if there is some way to improve MySQL insert rate.
The following C++ code with mysql++ takes 5 seconds to execute in my Athlon
1.33 machine:
sql_create_2 (teste1, 1, 2, double, datahora, double, valor1)
int main() {
try { // its in one big try block
Connection con(use_exceptions);
con.connect(cesar);
Query query = con.query();
teste1 row;
// create an empty stock object
for (int i=1;i5;i++)
{
row.datahora = (double) i;
row.valor1 = i / 1000;
query.insert(row);
query.execute();
}
As you can see there are only two fields: a double timestamp and a double
value. In the real application there are some more double values. I need to
decrease this time to less than 1 second. Is there any kind of buffered
inserts or maybe a way that I could pass a matrix?
I'm shocked with the performance of MySQL, a similar query to compute 1
million records takes 1.17 seconds in MySQL and around 6 seconds in the
current system. So if I can decrease the insert time I'll definetly use
MySQL!
Thank you for the attention.
Best regards,
Cesar
--
++
| Steve Edberg [EMAIL PROTECTED] |
| University of California, Davis (530)754-9127 |
| Programming/Database/SysAdmin http://pgfsun.ucdavis.edu/ |
++
| If only life would imitate toys. |
| - Ted Raimi, March 2002 |
| - http://www.whoosh.org/issue67/friends67a.html#raimi |
++
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Trying to find source Tarball for 4.0.2
Dear List, since i am working on a project that uses extensively full text search i have to find a source distribution of 4.0.2 source code which has some very important features and bug fixes respect to 4.0.1 and try to compile it with different charset e.t.c. Unfortunately i don't have any experience with Bitkeeper in order to use the working tree. I downloaded from http://www.mysqldeveloper.com/snapshots/ a .tgz file but it is only 6.5 MB and trying to compile it i discovered that it is not the whole code included in this file. SO if anybody could point me a place where i can download the whole 4.0.2 code in .tgz format it will be a HUGE help for me. with thanks Georgiafentis Nikolaos Project Manager [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] iTEL LTD 409 Vouliagmenis Ave. Ilioupoli, 16346 Tel: +30 10 9790050 Fax: +30 10 9790051 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Cardinality and index using
Hi, all I am using mysql - 3.23.46, Intel, Solaris 8 Some questions: 1) Why cardinality value don't automaticly change when query change set of unique values in index ( insert or update ) ? Example: mysql create table a ( a int not null, index ( a ) ); Query OK, 0 rows affected (0.02 sec) mysql show index from a; +---++--+--+-+---+-- ---+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---++--+--+-+---+-- ---+--++-+ | a | 1 | a|1 | a | A | NULL | NULL | NULL | | +---++--+--+-+---+-- ---+--++-+ 1 row in set (0.00 sec) mysql insert into a values ( 1 ); Query OK, 1 row affected (0.00 sec) mysql show index from a; +---++--+--+-+---+-- ---+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---++--+--+-+---+-- ---+--++-+ | a | 1 | a|1 | a | A | NULL -??? | NULL | NULL | | +---++--+--+-+---+-- ---+--++-+ 1 row in set (0.00 sec) mysql optimize table a; +---+--+--+--+ | Table | Op | Msg_type | Msg_text | +---+--+--+--+ | tmp.a | optimize | status | OK | +---+--+--+--+ 1 row in set (0.04 sec) mysql show index from a; +---++--+--+-+---+-- ---+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---++--+--+-+---+-- ---+--++-+ | a | 1 | a|1 | a | A | 1 | NULL | NULL | | +---++--+--+-+---+-- ---+--++-+ 1 row in set (0.00 sec) 2) Why index using in different way when cardinality = 1 and cardinality 1 ? Example: index in calculated_offlc ( contract_id ) have cardinality = 1 mysql show index from contract; +--+++--+-+---+- +--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +--+++--+-+---+- +--++-+ | contract | 0 | PRIMARY|1 | id | A | 8520 | NULL | NULL | | | contract | 0 | deleted_id |1 | deleted_id | A | NULL | NULL | NULL | | | contract | 0 | deleted_id |2 | number | A | 8520 | NULL | NULL | | +--+++--+-+---+- +--++-+ 3 rows in set (0.00 sec) mysql show index from calculated_offlc; +--++-+--+-- -+---+-+--++-+ | Table| Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +--++-+--+-- -+---+-+--++-+ | calculated_offlc | 0 | PRIMARY |1 | processed_data_id | A | 46706 | NULL | NULL | | | calculated_offlc | 1 | contract_id |1 | contract_id | A | 1 - !!! | NULL | NULL | | +--++-+--+-- -+---+-+--++-+ 2 rows in set (0.00 sec) mysql explain SELECT c.id, IFNULL( SUM( c_offlc.value ), 0 ) FROM contract AS c LEFT JOIN calculated_offlc AS c_offlc ON c_offlc.contract_id = c.id GROUP BY c.id; +-+---+---+-+-+--+---+-- + | table | type | possible_keys | key | key_len | ref | rows | Extra |
replication problem
hello, i have major problem with setting up replication. both ends are running mysql 3.23.49 i have set up proper entries in my.cnf on both sides and have replicated the dbs. now, on master: show master status reads: ++--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | ++--+--+--+ | bdnotebook-bin.001 | 758 | | | ++--+--+--+ 1 row in set (0.00 sec) show processlist reads: | 4 | root | linux.compfort | NULL | Binlog Dump | 1212 | Slave connection: wa iting for binlog update | NULL | now on slave: show slave status reads: | Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos | Slave_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | +-+-+-+---+--+ -+---+-+-+ ++--+ | 10.0.0.111 | rep | 3306| 30| | 35 | Yes | | | 0 || 0| +-+-+-+---+--+ -+---+-+-+ ++--+ show processlist: | 1 | system user | none | NULL | Connect | 294 | Waiting to reconnect after a failed read | NULL | in slave's logfiles i see these errors: 020531 14:04:08 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 35 020531 14:04:08 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'FIRST' at position 35 020531 14:04:08 Slave: received 0 length packet from server, apparent master shutdown: (0) and the databases are not being updated. what am doing possibly wrong ? have i missed something ? please help : terry - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
how to deinstall old mysql version?
Hi there, I am just trying to configure my root server. The provider has preinstalled mysql on suse72. So I did try to get rid of this installation with yast, but this did not work out. After compiling php,apache and mysql, PHP is still trying to connect to the old server. The new server is running but how could I tell php to connect to the right server? Thanx for any help on that, Andy query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: I need 50.000 inserts / second
Hello, You could maybe buffer the data in Your application and then run inserts later... like this. This is not a solution for me as the data acquisition can take hours without any break. I might be missunderstanding You since I don't get this together... You wrote: The following C++ code with mysql++ takes 5 seconds to execute in my Athlon 1.33 machine: And later on: I'm shocked with the performance of MySQL, a similar query to compute 1 million records takes 1.17 seconds in MySQL So , if similar query makes 1 million in about a second, how come You have problems with only 50 K/s with another similar query The code shown INSERTs 50.000 records in the database and takes 5 seconds to run. My comment later about the query is about the SELECT with a GROUP BY clause, it takes 1.17 seconds to compute 1 million records. Thank you anyway, I'll be testing Jeremy's idea when I arrive home at night. Best regards, Cesar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
INSTALLATION HELP, PLEASE!!
im missing the mysql and mysqladmin binaries after my admin guy installed the most recent stable mysql rpm on a (linux) machine. are these included in the client rpm that he may have missed or is there something new that i missed? Luke - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replication problem
More than likely you have missed the step that the slave database has to be the exact one that the master has before the start of the binlog. Shutdown both servers, grab a copy of the master's data directory and tar that up, move that to the slave, delete the slave's current data directory, now untar the master's data dir in appropriate place on the slave (/usr/local/mysql in most cases), then make sure the permissions on the files a right (basically the mysql user must be able to read write to the files the directory), then restart the master, then finally restart the slave. Once that is done test replication by going to the master and creating a database, then check on the slave to ensure its there with a show databases. Scott Helms - Original Message - From: Bartomiej Dolata [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, May 31, 2002 7:59 AM Subject: replication problem hello, i have major problem with setting up replication. both ends are running mysql 3.23.49 i have set up proper entries in my.cnf on both sides and have replicated the dbs. now, on master: show master status reads: ++--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | ++--+--+--+ | bdnotebook-bin.001 | 758 | | | ++--+--+--+ 1 row in set (0.00 sec) show processlist reads: | 4 | root | linux.compfort | NULL | Binlog Dump | 1212 | Slave connection: wa iting for binlog update | NULL | now on slave: show slave status reads: | Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos | Slave_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | +-+-+-+---+--+ -+---+-+-+ ++--+ | 10.0.0.111 | rep | 3306| 30| | 35 | Yes | | | 0 || 0| +-+-+-+---+--+ -+---+-+-+ ++--+ show processlist: | 1 | system user | none | NULL | Connect | 294 | Waiting to reconnect after a failed read | NULL | in slave's logfiles i see these errors: 020531 14:04:08 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 35 020531 14:04:08 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'FIRST' at position 35 020531 14:04:08 Slave: received 0 length packet from server, apparent master shutdown: (0) and the databases are not being updated. what am doing possibly wrong ? have i missed something ? please help : terry - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: displaying errors in
Charles, Friday, May 31, 2002, 10:55:20 AM, you wrote: CS I've seen this question asked before, but I'm hoping maybe this is now CS possible. CS When one does a LOAD DATA INFILE... or similar and get a result CS like this: CS Query OK, 428 rows affected (4.30 sec) CS Records: 428 Deleted: 0 Skipped: 0 Warnings: 431 CS ...is there any possible way to see the errors? Is there any debugging CS that can be turned on on the server side to track these warnings? The CS data looks good, but I'm quite curious what's going on. CS I've seen the suggestion to do a dump and diff, but the order of the CS records in the file I'm importing is so random, the diff is useless. Warnings are not stored anywhere. It's only an indicator of the process went. Check the manual: http://www.mysql.com/doc/L/O/LOAD_DATA.html You can find some notes about possible causes of warning there. CS Any ideas/tips appreciated! CS thanks, CS Charles -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: index not hit ?
kmlau, Friday, May 31, 2002, 1:18:26 PM, you wrote: k show index from gwarp; k mysql k +---+++--+-+---+ k -+--++-+ k - | Table | Non_unique | Key_name | Seq_in_index | Column_name | k Collation | Cardinality | Sub_part | Packed | Comment | k - k +---+++--+-+---+ k -+--++-+ k - | gwarp | 1 | ipindex|1 | ip | A k | 165538 | NULL | NULL | | k - | gwarp | 1 | macindex |1 | mac | A k | 167902 | NULL | NULL | | k - | gwarp | 1 | esnindex |1 | esn | A k | 165538 | NULL | NULL | | k - | gwarp | 1 | esnindex |2 | timerecord | A k |11753202 | NULL | NULL | | k - | gwarp | 1 | sitename |1 | sitename| A k | 238 | NULL | NULL | | k - | gwarp | 1 | timerecord |1 | timerecord | A k | 132 | NULL | NULL | | k - k +---+++--+-+---+ k -+--++-+ k then k i use explain to analyze k mysql explain select * from gwarp where timerecord = '0205281' ; k +---+--+---+--+-+--+--+- k ---+ k | table | type | possible_keys | key | key_len | ref | rows | Extra k | k +---+--+---+--+-+--+--+- k ---+ k | gwarp | ALL | timerecord| NULL |NULL | NULL | 11753202 | where k used | k +---+--+---+--+-+--+--+- k ---+ k 1 row in set (0.00 sec) k Pls help timercord index is not hit !! why ? how to amend ? MySQL doesn't use indexes if query require to access more than 30% rows in the table. You can find some more info about how MySQL uses indexes at: http://www.mysql.com/doc/M/y/MySQL_indexes.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can't start server : Bind on unix socket ?
Chris, Friday, May 31, 2002, 4:51:37 AM, you wrote: CG Somewhat new to the list (and very new to mySQL), so thanks for you help CG in advance. I installed RedHat 7.1 (Seawolf) on a PC, and it came with CG mysql 3.23.36. I logged in as a regular admin user and typed mysql to CG get started... see what I could get accomplished just playing around. CG [chris@the_pc /] $ mysql CG ERROR 2002: Can't connect to local MySQL server through socket CG '/var/lib/mysql/mysql.sock' (111) CG [chris@the_pc /] $ mysqld CG 020530 17:43:26 Can't start server : Bind on unix socket: Address CG already in use CG 020530 17:43:26 Do you already have another mysqld server running on CG socket: /var/lib/mysql/mysql.sock ? CG 020530 17:43:26 Aborting CG [chris@the_pc /] $ cd /var/lib/mysql CG [chris@the_pc /] $ ls -F CG mysql/ mysql.sock= test/ the_pc.log CG OK, so mysql.sock is a socket... and it exists. I checked the setup CG program, and mysqld is set to run at system start, but disabling this CG did not help at all. CG Any ideas/tips? Thanks again Specify path to your socket file with --socket option. Also check the following link, it may help you: http://www.mysql.com/doc/C/a/Can_not_connect_to_server.html CG Chris Garaffa CG [EMAIL PROTECTED] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: I need 50.000 inserts / second
On Fri, 2002-05-31 at 20:54, Cesar Mello - Axi wrote: Hello, You could maybe buffer the data in Your application and then run inserts later... like this. This is not a solution for me as the data acquisition can take hours without any break. Whatever you do, with any SQL you get stuck behind speed of parser. More commands means more waiting. Making one INSERT for two records instead of two INSERTs can speed up your stuff twice easily. This is general problem of SQL. Tõnu - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
newbie question
I have data on CD ROM for the Marine Casualty and Pollution Database, comprised of fourty data tables, a data dictionary and a readme file. I would like to access the data using mysql, which I have on my Mandrake 8.1 linux box. The readme file says ... Analysis of data is conducted at Coast Guard Headquarters using a 4GL Relational Database Management System. My questions are 1) Do you thing mysql will serve to access this data? and 2) What is the command to open this data under mysql? Thanks, this is my first time using mysql. Phil Reardon - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: newbie question
[snip] The readme file says ... Analysis of data is conducted at Coast Guard Headquarters using a 4GL Relational Database Management System. [/snip] Which 4GL Relational Database Management System? It may use a proprietary file type for which you may have to have that database's engine to export the file. What are the file extensions on the data files? If they are unfamiliar you can check them here; http://extsearch.com/ You may be able to import them directly into MySQL (see http://www.mysql.com/doc/m/y/mysqlimport.html ) HTH! Jay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Using Row Id
Hi All How do i use RowId 's in MYSQL.. Can anyone throw some light on it Regards, -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SSL
Hi all, How is the best tool for acess Mysql By SSL protocol ? --- Marcel P. Tardelli DBA - DBLive Corp. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: newbie question
Which 4GL Relational Database Management System are they talking about? --Walt Weaver Bozeman, Montana -Original Message- From: Phil Reardon [mailto:[EMAIL PROTECTED]] Sent: Friday, May 31, 2002 7:57 AM To: [EMAIL PROTECTED] Subject: newbie question I have data on CD ROM for the Marine Casualty and Pollution Database, comprised of fourty data tables, a data dictionary and a readme file. I would like to access the data using mysql, which I have on my Mandrake 8.1 linux box. The readme file says ... Analysis of data is conducted at Coast Guard Headquarters using a 4GL Relational Database Management System. My questions are 1) Do you thing mysql will serve to access this data? and 2) What is the command to open this data under mysql? Thanks, this is my first time using mysql. Phil Reardon - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: newbie question
[snip] The readme file says ... Analysis of data is conducted at Coast Guard Headquarters using a 4GL Relational Database Management System. [/snip] Which 4GL Relational Database Management System? It may use a proprietary file type for which you may have to have that database's engine to export the file. What are the file extensions on the data files? If they are unfamiliar you can check them here; http://extsearch.com/ You may be able to import them directly into MySQL (see http://www.mysql.com/doc/m/y/mysqlimport.html ) The tables are all *.txt. Is there a way to point mysql to the cdrom rather than importing the data? [/snip] Nope, but at least the .txt files are easily importable! :) See info about mysqlimport above. HTH! Jay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
HELP: MySQL Dynamic Structure
Hello all... please read I've struggled with this for YEARS - THE PROBLEM: - MySQL alone cannot represent a complex data-structure. Re-modeling of MySQL data is required in Perl, PHP, Java, etc. * MySQL needs to have the ability to add properties to columns and tables. - Explanation: - I have numerous MySQL front-end and back-end clients written in Perl, PHP, and Java. In each language - I have had to re-model the entire MySQL data stucture in that programming language. A PAIN IN THE BUTT. If I change one column, add one field, play with one table... I have to go into all of that code in all of those languages and mess with either how the front-ends and/or how the back-ends are interpreting the data-schema. - OBJECTIVE - To get MySQL to be the one, central data-source for nearly everything used in your applications and data-interfaces. Not just data... -Dynamic data. No more manual SQL statements in your applications. -Dynamic SQL. - PICTURE THIS - bear with me... - EXAMPLE # 1 - Removing a single column from one of your MySQL reports would be as simple as doing this: * mysql UPDATE myTable.myColumn SET isVisible=FALSE; It doesn't matter what programming language the report is programmed in - that column would no longer show up in the report. - EXAMPLE # 2 - Or change what name is displayed for your column name in all your MySQL reports? * mysql UPDATE myTable.myColumn SET Alias='My Display Name'; That's right! Your column display names can have spaces! Programming your column names by hand is now optional. You can add and customize any column or table to have any property you wish. - OK, BUT WHY ? - You could do the same by just editing the SQL statements of your Perl, PHP, Java, etc. application, easy... RIGHT? * WRONG. * 1.) You've got to go into (for example) your Perl scripts and change the name of the fields that are being shown there, the SQL statement itself, and the variables in Perl that the SQL statement is returning. 2.) Then go into your Visual Basic application (you know, the one that all your accounting people use) and edit that code and SQL. Then you have to re-deploy the Visual Basic application on all those desktops. 3.) Or how about that PHP script that the sales people on-the-road use - it has the same SQL report! Gotta change that, too. 4.) What about that other Java program? 5.) the C++ one? - YOUR POINT ? - * -- MySQL needs to have the ability to add properties to columns and tables. The more flexible MySQL becomes, the more flexible your applications become... and the easier they are to develop and use. Instead of SQL code in your applications, automatically generated SQL. Instead of editing programming language code... a simple SQL statement from the MySQL shell could do a lot of what's needed to alter how your applications interpret the data schema. One SQL statement... one minute of time. - POSSIBLE SOLUTION #1 - -- Add properties/attributes to MySQL columns tables For columns... Currently we have: + | Field | Type | Null | Key | Default | Extra + Are these properties flexible enough? I do not believe so. With the new file formats in MySQL 4.x and more complex data tables such as InnoDB, is it possible to ADD
Trying to find source Tarball for 4.0.2
Dear List, since i am working on a project that uses extensively full text search i have to find a source distribution of 4.0.2 source code which has some very important features and bug fixes respect to 4.0.1 and try to compile it with different charset e.t.c. Unfortunately i don't have any experience with Bitkeeper in order to use the working tree. I downloaded from http://www.mysqldeveloper.com/snapshots/ a .tgz file but it is only 6.5 MB and trying to compile it i discovered that it is not the whole code included in this file. SO if anybody could point me a place where i can download the whole 4.0.2 code in .tgz format it will be a HUGE help for me. with thanks Georgiafentis Nikolaos Project Manager [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] iTEL LTD 409 Vouliagmenis Ave. Ilioupoli, 16346 Tel: +30 10 9790050 Fax: +30 10 9790051 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Using Joins
* Arul Is it possible that i can use 3 tables in a Join Query.. Assuming that i have 3 tables Sure! artist table contains artistid , artistname cd table contains cdid,artistid , Filmname artistaddress table contains artistid , City , phone no [...] If i execute this query i get only 3 rows...'D' who doesnt have an entry in CD table and artistaddress table is not coming in the result. This is because you are not LEFT JOIN'ing the artistaddress... mysql select artist.artistid,artistname,cdid,title,street,phoneno from artist left join cd on artist.artistid = cd.artistid,artistaddress where artist.artistid = artistaddress.artistid; Just replace ,artistaddress with left join artistaddress: select artist.artistid,artistname,cdid,title,street,phoneno from artist left join cd on artist.artistid = cd.artistid left join artistaddress on artist.artistid = artistaddress.artistid; -- Roger sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SSL
On Fri, 2002-05-31 at 17:52, Marcel wrote: Hi all, How is the best tool for acess Mysql By SSL protocol ? What do you need to do? MySQL C API has all neccessary stuff to use SSL and mysql command line tool can use it from 4.0.x versions. Tònu - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Trying to find source Tarball for 4.0.2
I've been trying to find it too. Haven't had any luck yet... --Walt Weaver Bozeman, Montana -Original Message- From: Nikolaos Georgiafentis [mailto:[EMAIL PROTECTED]] Sent: Friday, May 31, 2002 9:06 AM To: [EMAIL PROTECTED] Subject: Trying to find source Tarball for 4.0.2 Dear List, since i am working on a project that uses extensively full text search i have to find a source distribution of 4.0.2 source code which has some very important features and bug fixes respect to 4.0.1 and try to compile it with different charset e.t.c. Unfortunately i don't have any experience with Bitkeeper in order to use the working tree. I downloaded from http://www.mysqldeveloper.com/snapshots/ a .tgz file but it is only 6.5 MB and trying to compile it i discovered that it is not the whole code included in this file. SO if anybody could point me a place where i can download the whole 4.0.2 code in .tgz format it will be a HUGE help for me. with thanks Georgiafentis Nikolaos Project Manager [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] iTEL LTD 409 Vouliagmenis Ave. Ilioupoli, 16346 Tel: +30 10 9790050 Fax: +30 10 9790051 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SSH Replication
Does MySQL have any facility for using SSH to tunnel ports for use in replication? Has anyone implemented anything like this? We can't just assume if we create an SSH tunnel manually that the connection will stay up. Thanks, Mark R. Andrachek, Jr. Analyst I Federal Reserve Bank of Richmond [EMAIL PROTECTED] (804) 697-7971 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: HELP: MySQL Dynamic Structure
On Fri, May 31, 2002 at 10:01:34AM -0500, Ryan Hatch wrote: - THE PROBLEM: - MySQL alone cannot represent a complex data-structure. Re-modeling of MySQL data is required in Perl, PHP, Java, etc. One wonders why each of these applications has such intimate knowledge of your table schemes, or mysql, or even SQL in general. If your environment warrants it (and I'd say four different platforms is good enough) you might do good to hide the database behind an abstraction layer. This being the only layer that talks to mysql for the purpose of providing primitives that the rest of your business logic (all of the perl, java, php, vb scripts) depend on. When you update table structure, you simply need to change the abstraction layer instead of all of your scripts. If you want to pitch it to management, just say XMLRPC! until their ears bleed. -- Michael Bacarella | Netgraft Corporation | 545 Eighth Ave #401 Systems Analysis | New York, NY 10018 Technical Support | 212 946-1038 | 917 670-6982 Managed Services | [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Alternative to decode() in Oracle
Thanx Lewis..It worked
- Original Message -
From: Neville Lewis [EMAIL PROTECTED]
To: MySQL [EMAIL PROTECTED]
Sent: Friday, May 31, 2002 12:34 PM
Subject: RE: Alternative to decode() in Oracle
Have a look below:
--
If expr1 is TRUE (expr1 0 and expr1 NULL) then IF() returns expr2,
else it returns expr3. IF() returns a numeric or string value, depending
on
the context in which it is used:
mysql select IF(12,2,3);
- 3
mysql select IF(12,'yes','no');
- 'yes'
mysql select IF(strcmp('test','test1'),'no','yes');
- 'no'
---
expr1 is evaluated as an integer value, which means that if you are
testing
floating-point or string values, you should do so using a comparison
operation:
mysql select IF(0.1,1,0);
- 0
mysql select IF(0.10,1,0);
- 1
---
mysql SELECT CASE 1 WHEN 1 THEN one WHEN 2 THEN two ELSE more END;
- one
mysql SELECT CASE WHEN 10 THEN true ELSE false END;
- true
mysql SELECT CASE BINARY B when a then 1 when b then 2 END;
- NULL
---
mysql select 'Monty!' REGEXP 'm%y%%';
- 0
mysql select 'Monty!' REGEXP '.*';
- 1
mysql select 'new*\n*line' REGEXP 'new\\*.\\*line';
- 1
mysql select a REGEXP A, a REGEXP BINARY A;
- 1 0
mysql select a REGEXP ^[a-d];
- 1
-
Did that help?
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql newbie show table question
I can¹t seem to make any of the describe or show queries work when I apply a search statement: Query=show table status from demo; Works fine. Now I¹m trying to get info from only one table: Query=show table status from demo where name like user_access; Does no work. Thank You
Mysql newbie show table question
I can¹t seem to make any of the describe or show queries work when I apply a search statement: Query=show table status from demo; Works fine. Now I¹m trying to get info from only one table: Query=show table status from demo where name like user_access; Does no work. Thank You
Compiling On Tru64
I'm compiling on Tru64 ver 5.1 using GNU 3.1 and make 3.79. I am receiving the following error: Any help will be appreciated. Dean inition In file included from ../include/my_sys.h:35, from mysys_priv.h:19, from my_pthread.c:20: ../include/my_pthread.h:307:1: warning: pthread_cond_init redefined In file included from ../include/global.h:94, from mysys_priv.h:18, from my_pthread.c:20: /usr/include/pthread.h:1301:1: warning: this is the location of the previous definition my_pthread.c: In function `my_pthread_mutex_init': my_pthread.c:395: `pthread_mutexattr_default' undeclared (first use in this function) my_pthread.c:395: (Each undeclared identifier is reported only once my_pthread.c:395: for each function it appears in.) my_pthread.c: In function `my_pthread_cond_init': my_pthread.c:405: `pthread_condattr_default' undeclared (first use in this function) make[2]: *** [my_pthread.o] Error 1 make[2]: Leaving directory `/user01/dgrimes/cdev/mysql-3.23.49/mysys' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/user01/dgrimes/cdev/mysql-3.23.49' make: *** [all-recursive-am] Error 2 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
smaller mysql docs?
is there a place to get the manual that is split a bit more then the current manual_split. these are the 2 pages i end up reading the most even though its usually for just the grant, create, insert syntax (human in need of memory upgrade) 416k Sep 26 2001 manual_MySQL_Database_Administration.html 312k Sep 26 2001 manual_Reference.html i'm not sure what's wrong with my computer, but loading the pages takes a while from the local harddrive, or maybe its rendering the page... either way, the larger pages are annoyingly slow. or is there a cheat sheet page? (syntax, links into the manual, not much else) -- Bill Dickey is learning me his experience. -- Yogi Berra in his rookie season. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: HELP: MySQL Dynamic Structure (long)
[snip a lot...] MySQL alone cannot represent a complex data-structure. [/snip] Incorrect, and if it IS the case (from your POV) then no RDBMS can represent a complex data structure. [snip] Re-modeling of MySQL data is required in Perl, PHP, Java, etc. In each language - I have had to re-model the entire MySQL data stucture in that programming language. [/snip] Define re-modeling, succinctly. [snip] MySQL needs to have the ability to add properties to columns and tables. [/snip] No other RDBMS have this, do they? [snip] If I change one column, add one field, play with one table... I have to go into all of that code in all of those languages and mess with either how the front-ends and/or how the back-ends are interpreting the data-schema. [/snip] No you don't have to. The application will continue to work normally unless you do something funny to the database structure itself. To consider the additional data you have to make changes. Frequent changes of the underlying data model means that it is not designed for the purpose properly from the beginning due to a lack of proper analysis. [snip] - OBJECTIVE - To get MySQL to be the one, central data-source for nearly everything used in your applications and data-interfaces. Not just data...-Dynamic data. No more manual SQL statements in your applications.-Dynamic SQL. Removing a single column from one of your MySQL reports would be as simple as doing this: * mysql UPDATE myTable.myColumn SET isVisible=FALSE; It doesn't matter what programming language the report is programmed in - that column would no longer show up in the report. [/snip] I can programmatically make the report aware of column existence now, using other standard mechanisms that have been used for years. [snip] Or change what name is displayed for your column name in all your MySQL reports? * mysql UPDATE myTable.myColumn SET Alias='My Display Name'; [/snip] mysql SELECT myTable.myColumn AS My Display Name [snip] You could do the same by just editing the SQL statements of your Perl, PHP, Java, etc. application, easy... RIGHT? [/snip] You're right! [snip] - YOUR POINT ? - [/snip] [snip] Instead of SQL code in your applications, automatically generated SQL. [/snip] A mind-reading application? Automatically generated how? [snip] Instead of editing programming language code... a simple SQL statement from the MySQL shell [/snip] Do you mean a non-automatically generated SQL statement from the MySQL shell. [snip] Forcing for the existance of every database to contain two tables named TABLES and COLUMNS ? This solution is already being used by some. [/snip] And anyone with reasonable knowledge of a programming language can do this too. But I think it misses the point. Are you proposing something beyond Cobb's rules for RDBMS? If so it does not specifically affect MySQL, but you want it to because it is the database you use, right? [snip] The only possible downfall to this approach is that for any properties we wish to assign to a column - we must add to all columns. So, even though a property may only apply to a couple columns in a particular table, it would be a property for every column in every table, although it might not need to actually be defined (could be left NULL). [/snip] I love conundrums, don't you? [snip] * There needs to be a standard. I'm talking about every interface ever devised for MySQL, not just HTML formatting. After all, it's all about interfaces, is it not? MySQL holds the data - but the data means nothing unless it can be viewed, added, and edited -- hence an interface. [/snip] Given the depth and breadth of the proposed ideas I will have to say, as said before, that you are talking about RDBMS in general. Applying such a set of standards to one product is not a standard at all...just a manufacturer's feature set. [snip] * If we can make MySQL more flexible - we can make our data interfaces more flexible. [/snip] So the interfaces I design are not as flexible as I have thought? PHPMYADMIN is not flexible? Your interfaces aren't flexible? [snip] Am I missing something here that all other programmers are using? Am I embarassing myself as some intermediate programmer? Perhaps... but in my humble opinion and in light of what I've been exposed to, I feel there is a significant need for dynamic schema properties or something very similar. [/snip] Ryan, it is nice to see such youthful exhuberance! But I would like to know what your experience is with designing interfaces, what your seperate experience is using databases (design, extraction, etc.). While you have some nice points most (if not all) have been achieved at
Re: Multiple Queries
You can create another frame that's invisible by having a zero height or width. This can be your page that does all the queries and refreshes. You can then use the javascript command document.write() to update the other frames without having to hit the server again. Or, retrieve the data in the invisible frame and then refresh the other frames, which would get their data from the invisible frame instead of from the database. Sorry If this is the wrong list for this topic, I hope someone can shed some light onto my problem. I am building a php/ Mysql web site for a client which is a picture gallery. The web site has 3 frames (required by the designer so that the whole thing doesnt refresh all the time). Frame 1- Navigation Frame 2- Info on selected artworks and other related projects Frame 3- The artworks/ jpegs I have one table, with a name and info about the project and upto 5 urls of where the jpeg lies. When a project is selected in the navigation I request the record from the database, I create an array which contains the location of the jpegs and then display them in Frame 3. Now, I then have a piece of javascript which tells frame 2 to update itself. So I have parsed the record ID to it and it then open a query to the database and outputs the relevant information about the artworks. Lastly a third query is also sent to the database to see if there are any other projects in the gallery by the same artist- and then create a list of related links. So I have 3 database queries over two pages. I'm wondering if there is a more efficient way of doing this? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SSH Replication
Hi Mark, At 11:14 5/31/2002 -0400, [EMAIL PROTECTED] wrote: Does MySQL have any facility for using SSH to tunnel ports for use in replication? Has anyone implemented anything like this? We can't just assume if we create an SSH tunnel manually that the connection will stay up. There was an excellent piece in August 2001's Sysadmin, that described just that - for an intrusion detection system - but the principals remain the same. Unfortunately, it's not been published online: http://sysadminmag.com/articles/2001/0108/ Distributed Intrusion Detection with Open Source Tools Jason Chan Chan presents an example of an intrusion detection system comprising open source tools such as Snort, OpenSSL, Stunnel, and MySQL. I remember that Stunnel is used to provide the tunneling and looking at that site, there's a complete example available: http://www.stunnel.org/examples/mysql.html Hope this helps. Best regards, Melvyn Sopacua WebMaster IDG.nl _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ If it applies, where it applies - this email is a personal contribution and does not reflect the views of my employer IDG.nl. \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: I need 50.000 inserts / second
If HEAP table won't do because of memory constraints, why not use straight flat files like typical Unix or Web logs? Start a new file at set intervals, whether it be time intervals or record intervals. Then you can use the LOAD DATA command to load the files. Your situation seems similar to that of a web server, where you don't need to analyze the data in real time. So a setup similar to that of web server logs may be appropriate. Hello, You could maybe buffer the data in Your application and then run inserts later... like this. This is not a solution for me as the data acquisition can take hours without any break. I might be missunderstanding You since I don't get this together... You wrote: The following C++ code with mysql++ takes 5 seconds to execute in my Athlon 1.33 machine: And later on: I'm shocked with the performance of MySQL, a similar query to compute 1 million records takes 1.17 seconds in MySQL So , if similar query makes 1 million in about a second, how come You have problems with only 50 K/s with another similar query The code shown INSERTs 50.000 records in the database and takes 5 seconds to run. My comment later about the query is about the SELECT with a GROUP BY clause, it takes 1.17 seconds to compute 1 million records. Thank you anyway, I'll be testing Jeremy's idea when I arrive home at night. Best regards, Cesar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Constraints - Error or Bug????
Hi all, I just wonder. I have tried this example from www.mysql.org Doc. CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL ) TYPE=INNODB; But, the constraint does not work propperly. If i add 1 and 2 in the parent table, and 1,1 and 1,6 in the child table, it still works. Does anyone know if there is a Bug in the mysql, or im just getting it all wrong If you know howto make the constraints work please write a little example - Thanks :-) Regards Frank Jørgensen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Installation process
Hello! I am a newbie, to group and MySQL. I just downloaded MySQL for win 2000. The zip file, when opened and clicked on install, it created c:\mysql directory and did the install. Now what next? DOnt I have to start the server? How do I create instances? How do I get into the dbadmin to maintain tables etc,? What is the super user id? how do I set this up? I have a limited experience on Oracle. A DB2 or ACCESS like control center - is something like that available on MySQL? thanx for any response and any clue in tis direction. -Mary __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
incorrect column specifier
I'm getting an error -incorrect column specifier using MySQL 3.23.37 on SuSe 7.2 This line - building_idx decimal(8,0) unsigned zerofill DEFAULT '' not null auto_increment -is in a supplied perl script and causes the error message. I acknowledge this is just one line from a fairly long scrip, which is building several tables; this being one column from one table. I don't know which is the INcorrect specifier. Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Finding holes in autoinc sequences
Richard, Say, that's really good! I'm still not used to taking advantage of those variables. Clever. Also, I think you can remove all but the rows showing gaps by adding a group by and having clause... select id,@b:=IF(id=@a+1,id,concat(@a+1,..,id-1)) range,@a:=id, @flag:=IF(@b=id,id,MISSING) from seq group by id having id range; -Original Message- From: Richard Clarke [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 30, 2002 7:48 PM To: [EMAIL PROTECTED] Subject: Re: Finding holes in autoinc sequences Or another approach could be (using same example tables) mysql set @a:=0; set @b:=0; set @c:=0; mysql select id,@b:=IF(id=@a+1,id,concat(@a+1,..,id-1)),@a:=id,@flag:=IF( @b=id,id,MISS ING) from seq; ++---++--- -- --+ | id | @b:=IF(id=@a+1,id,concat(@a+1,..,id-1)) | @a:=id | @flag:=IF(@b=id,id,MISSING) | ++---++--- -- --+ | 1 | 1 | 1 | 1 | | 2 | 2 | 2 | 2 | | 3 | 3 | 3 | 3 | | 4 | 4 | 4 | 4 | | 5 | 5 | 5 | 5 | | 6 | 6 | 6 | 6 | | 7 | 7 | 7 | 7 | | 8 | 8 | 8 | 8 | | 9 | 9 | 9 | 9 | | 12 | 10..11| 12 | MISSING | | 13 | 13| 13 | 13 | | 14 | 14| 14 | 14 | | 15 | 15| 15 | 15 | | 16 | 16| 16 | 16 | | 17 | 17| 17 | 17 | | 18 | 18| 18 | 18 | | 19 | 19| 19 | 19 | | 22 | 20..21| 22 | MISSING | | 24 | 23..23| 24 | MISSING | | 25 | 25| 25 | 25 | | 26 | 26| 26 | 26 | | 27 | 27| 27 | 27 | | 28 | 28| 28 | 28 | | 29 | 29| 29 | 29 | ++---++--- -- --+ 24 rows in set (0.00 sec) To get the MISSING entries you would need to create a temporary table from that query then select where the @flag column = MISSING. Maybe this is more efficient than the join previously suggested. Maybe mysql team would let HAVING be applied to the 'variably' created rows you could then add having flag_alias = MISSING project out just the rows that are missing (saving the need for temporary tables). you could also plug the 10..11 20..21 etc statements into a perl foreach loop (if you were using perl) to automatically create the inner numbers. Ric p.s. maybe there is a way of tricking it into using a having statement to project out the MISSING rows. - Original Message - From: Kevin Fries [EMAIL PROTECTED] To: 'mos' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, May 30, 2002 10:56 PM Subject: RE: Finding holes in autoinc sequences A simple and fast way to find the gaps is to use a self LEFT JOIN, such as: select a.id from seq a left join seq b on a.id + 1 = b.id where b.id is null; The result set will show any row in seq where there was no record with ID one greater than that record's value. You'll get back the last row, and any rows which don't have a next-higher neighbor. To get my example above to work, use: create table seq ( id int auto_increment not null primary key); insert into seq values(); ** repeat to get rows 1..30. ** delete from seq where id between 10 and 11; delete from seq where id between 20 and 21; delete from seq where id = 23; select a.id from seq a left join seq b on a.id + 1 = b.id where b.id is null; ++--+ | id | id | ++--+ | 9 | NULL | | 19 | NULL | | 22 | NULL | | 30 | NULL | ++--+ Note that this doesn't show where the gaps end. You can further enhance the query by looking backward as well...getting fancy output: select a.id, case when c.id is null and b.id is null then '' else (case when c.id is null then '' else (case when b.id is null then '' else '' end) end) end gaps from seq a left join seq b on a.id + 1 = b.id left join seq c on a.id - 1 = c.id where b.id is null or c.id is null; ++--+ | id |
MS Access Queries
Hey guys, i'm still doing this web ODBC thing, and i was wondering since, all the wqueries i'm going to use are in a database, is there a way (using php/mysql/odbc) to access and call on those queries without typing them over in my php script? So basically i just want to connect to queries.mdb access the queries in that database, and use them to pull the data out of data/mdb.. any ideas? Thanks Jule - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Where can I get help?
Does anyone know where I can get help writing an MySQL update statement that contains a join in the where clause? Thank you. All the best, Joe Joseph W. Cherner, President SmokeFree Educational Services, Inc. http://www.smokefree.org Never doubt that a small group of thoughtful citizens can change the world. Indeed, it's the only thing that ever has. Margaret Mead - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: HELP: MySQL Dynamic Structure
Most of what you desire has already been done in products called FileMaker (Apple Computer) and Access (Microsoft). It's been a while since I used Access, but FileMaker will automatically change all references to a field/column if you change the field name. It will warn you of broken references if you try to remove a field/column. I could probably be considered an expert in FileMaker, but I truly find it a frustrating experience to develop in it. I constantly run into limitations and have to create workarounds. Mainly because of the dynamic interface that does so much for you, but also hides so much from you. Once MySQL gets stored procedures, you will be able to do a lot of what you are wishing for. You don't want to have your database schema exposed to client machines. Ideally, you would pass parameters to a stored procedure that would then execute the action, whether it be a query, insert, delete or whatever. Then everything is centralized. I for one would like to be able to choose my front end. PHP will probably soon have something similar to PERL DBI, which will help a lot for portability. While your suggestions and ideas would be a nice option, I wouldn't want to see the MySQL developers dedicating their resources to a front end. Just my 2 cents worth. Am I missing something here that all other programmers are using? Am I embarassing myself as some intermediate programmer? -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re[2]: [BUG] Excess Privileges required for SELECT INTO OUTFILE
Hello. On Fri, May 31, 2002 at 02:08:57AM +0100, [EMAIL PROTECTED] wrote: [...] BP So you may consider upgrading and check if you find the problems BP still reproducable. [...] I generally only update on a needs basis, most notably for security, as I like to keep things stable and conservative where I can :) Sure. [...] ...so, you're saying that grant/revoke changes may not be effective immediately in accordance with these reload points? That would explain a lot of it. That is how I understand the manual section in question. [...] I only had *NO* privileges in the 'db' table for the user. Like I mentioned, the privilege I granted was *just* for the table from which I wanted to select data, so no entry in 'db', but an entry in 'table_priv' with just 'select' privilege specified. My fault. Although I had read your description several times, I have overlooked that. With the according change, I can reproduce the error and get: ERROR 1044: Access denied for user: 'philemon@localhost' to database 'yasg' [...] Yes, I started with a repeatable test-case in a detailed transcript but I think my initial posting didn't catch people's attention g so Mentioning the initial post would have helped to avoid the confusion. ;-) maybe I would need to copy that to you again for a quick stab with a newer version... ...or wait until I upgrade and I'll re-try. I suggest that you post it to the [EMAIL PROTECTED] and mention that someone else (=me) was able to reproduce it with 3.23.46 at least. Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Trying to find source Tarball for 4.0.2
On Fri, May 31, 2002 at 06:06:02PM +0300, Nikolaos Georgiafentis wrote: SO if anybody could point me a place where i can download the whole 4.0.2 code in .tgz format it will be a HUGE help for me. Follow the instructions here: http://www.mysql.com/doc/I/n/Installing_source_tree.html Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.51: up 1 days, processed 43,820,635 queries (285/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: I need 50.000 inserts / second
Hi.
On Fri, May 31, 2002 at 03:24:52PM +0200, [EMAIL PROTECTED] wrote:
Cesar, you really should consider using placeholders and bind_param (if
available). Without using placeholders, the insert statement will contain
the literal values to be inserted and has to be re-prepared and re-executed
for each row. With placeholders, the insert statement only needs to be
prepared once. The bind values for each row can be given to the execute
method each time it's called. By avoiding the need to re-prepare the
statement for each row, the application typically runs many times faster.
The general idea is correct, but note that MySQL does not support this
(yet) and therefore one should not notice any significant speed
difference.
(Of course, I do not mean to not use prepared statements, because
without, you won't get the performance boost once they are supported).
Bye,
Benjamin.
BEFORE your loop, you prepare the query only once! Like this (in Perl),
$sth = $dbh-prepare (INSERT into mynames (my_num, my_id) values (?, ?));
Now, within your loop,
while (($name, $message_id) = each %posts) {
$sth-execute ($name, $message_id);
}
[...]
--
[EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: I need 50.000 inserts / second
- Original Message - From: Benjamin Pflugmann [EMAIL PROTECTED] To: Mark [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, May 31, 2002 8:42 PM Subject: Re: I need 50.000 inserts / second The general idea is correct, but note that MySQL does not support this (yet)... Really? Bummer. :( Sorry, I thought MySQL already did. I guess I have been optmizing my code all for nothing then. Well, not for nothing, as you point out. - Mark System Administrator Asarian-host.org --- If you were supposed to understand it, we wouldn't call it code. - FedEx - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: I need 50.000 inserts / second
In the last episode (May 31), Mark said: Cesar, you really should consider using placeholders and bind_param (if available). Without using placeholders, the insert statement will contain the literal values to be inserted and has to be re-prepared and re-executed for each row. With placeholders, the insert statement only needs to be prepared once. The bind values for each row can be given to the execute method each time it's called. By avoiding the need to re-prepare the statement for each row, the application typically runs many times faster. Mysql does not support bindings or preparing. Each statement is fully parsed. Whatever you think perl is doing, it isn't :) -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SSH Replication
Melvyn Sopacua wrote: - Does MySQL have any facility for using SSH to tunnel ports for use in replication? Has anyone implemented anything like this? We can't just assume if we create an SSH tunnel manually that the connection will stay up. There was an excellent piece in August 2001's Sysadmin, that described just that - for an intrusion detection system - but the principals remain the same. Unfortunately, it's not been published online: http://sysadminmag.com/articles/2001/0108/ Distributed Intrusion Detection with Open Source Tools Jason Chan Chan presents an example of an intrusion detection system comprising open source tools such as Snort, OpenSSL, Stunnel, and MySQL. I remember that Stunnel is used to provide the tunneling and looking at that site, there's a complete example available: http://www.stunnel.org/examples/mysql.html - Although I have not done this, couldn't you could use ssh port forwarding? ssh -L local port:mysql host:hostport then use mysql -h local host -P local port - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: I need 50.000 inserts / second
Hi Jeremy,
I've done some tests grouping the INSERTs in groups of 1000 and 1, and
it got very faster, in my Pentium III 700 from job I could get less than 3s.
But I'm still doing concatenation to get the INSERT clause in the form
values (x,x), (x,x) Is there a way to make a template query in
mysql++ that could take 1000 or more parameters??
Please tell me the alternative you were thinking about.
Best regards,
Cesar
- Original Message -
From: Jeremy Zawodny [EMAIL PROTECTED]
To: Cesar Mello - Axi [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Thursday, May 30, 2002 9:04 PM
Subject: Re: I need 50.000 inserts / second
On Fri, May 31, 2002 at 01:49:11AM -0300, Cesar Mello - Axi wrote:
Hello,
I intend to use MySQL in a data acquisition software. The actual
version stores the acquired data straight in files. The sample rate
can get up to 50 kHz. I would like to know if there is some way to
improve MySQL insert rate. The following C++ code with mysql++
takes 5 seconds to execute in my Athlon 1.33 machine:
Is the process disk bound or cpu bound? Put another way, does the cpu
max out during this process?
sql_create_2 (teste1, 1, 2, double, datahora, double, valor1)
int main() {
try { // its in one big try block
Connection con(use_exceptions);
con.connect(cesar);
Query query = con.query();
teste1 row;
// create an empty stock object
for (int i=1;i5;i++)
{
row.datahora = (double) i;
row.valor1 = i / 1000;
query.insert(row);
query.execute();
}
As you can see there are only two fields: a double timestamp and a
double value. In the real application there are some more double
values. I need to decrease this time to less than 1 second. Is there
any kind of buffered inserts or maybe a way that I could pass a
matrix?
You can boost the performance by inserting several records at the same
time in one querey using MySQL's extended insert syntax:
INSERT INTO my_table VALUES (foo, foo), (bar, bar), (z, z)...
Try batching them in groups of 100 or even 1000 to see how it
performs.
I'm shocked with the performance of MySQL, a similar query to
compute 1 million records takes 1.17 seconds in MySQL and around 6
seconds in the current system. So if I can decrease the insert time
I'll definetly use MySQL!
Welcome to MySQL. :-)
There's an alternative that I have in mind, but it'd be good to see
how much closer those get you before getting into it.
Jeremy
--
Jeremy D. Zawodny, [EMAIL PROTECTED]
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936
MySQL 3.23.51: up 1 days, processed 28,531,290 queries (274/sec. avg)
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Constraints - Error or Bug???? - Part II
Hi all, It seems that some is misunderstanding my problem, so i try to re-write my problem again. I just wonder. I have tried this example from www.mysql.org Doc. CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL ) TYPE=INNODB; But, the constraint does not work propperly. If i add 1 and 2 in the parent table like this: insert into parent values (1); insert into parent values (2); and 1,1 and 1,6 in the child table, like this: insert into child values (1,1); insert into child values (1,6); , it still works !! What i know, is that the constraint is voilated if i insert the last insert statement (insert into child values (1,6)), because 6 is NOT in the parent table BTW: Any value is accepted in the child table - Thats Strange!!! Does anyone know if there is a Bug in the mysql, or im just getting it all wrong If you know howto make the constraints work please write a little example - Thanks :-) Regards Frank Jørgensen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Trying to find source Tarball for 4.0.2
Hi. As 4.0.2 is not released yet, there is not tarball of it. Bitkeeper is the only method to retrieve a version of the current 4.0.2 tree. You do not have to have much experience with Bitkeeper to get a working tree. A step by step explaination can be found in the manual: http://www.mysql.com/doc/I/n/Installing_source_tree.html Bye, Benjamin. On Fri, May 31, 2002 at 01:40:03PM +0300, [EMAIL PROTECTED] wrote: Dear List, since i am working on a project that uses extensively full text search i have to find a source distribution of 4.0.2 source code which has some very important features and bug fixes respect to 4.0.1 and try to compile it with different charset e.t.c. Unfortunately i don't have any experience with Bitkeeper in order to use the working tree. I downloaded from http://www.mysqldeveloper.com/snapshots/ a .tgz file but it is only 6.5 MB and trying to compile it i discovered that it is not the whole code included in this file. SO if anybody could point me a place where i can download the whole 4.0.2 code in .tgz format it will be a HUGE help for me. with thanks Georgiafentis Nikolaos Project Manager [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: incorrect column specifier
Hi. I am not sure, but I guess that AUTO_INCREMENT is not allowed for DECIMAL. Bye, Benjamin. On Fri, May 31, 2002 at 11:56:36AM -0500, [EMAIL PROTECTED] wrote: I'm getting an error -incorrect column specifier using MySQL 3.23.37 on SuSe 7.2 This line - building_idx decimal(8,0) unsigned zerofill DEFAULT '' not null auto_increment -is in a supplied perl script and causes the error message. I acknowledge this is just one line from a fairly long scrip, which is building several tables; this being one column from one table. I don't know which is the INcorrect specifier. Thanks. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MS Access Queries
Hi. On Fri, May 31, 2002 at 01:26:34PM -0400, [EMAIL PROTECTED] wrote: Hey guys, i'm still doing this web ODBC thing, and i was wondering since, all the wqueries i'm going to use are in a database, is there a way (using php/mysql/odbc) to access and call on those queries without typing them over in my php script? So basically i just want to connect to queries.mdb access the queries in that database, and use them to pull the data out of data/mdb.. accessing the queries would be queries by themselves. So there is not really a point in doing so. Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: innodb rollbacks
Jeremy, - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, May 30, 2002 8:43 PM Subject: Re: innodb rollbacks On Thu, May 30, 2002 at 12:16:28PM +0300, Heikki Tuuri wrote: However, if there are lots of updates, and the user has a dangling uncommitted consistent read to the database for a long time, then the size of the undo logs may become significant also in InnoDB. I have to consider adding the size info to the InnoDB Monitor. Heikki, That reminds me of an InnoDB wish-list item I have. I like the level of detail provided in the InnoDB monitor output. However, I'd really like to be available via MySQL rather than just in the logs. That will make it a lot easier to collect the data remotely and write apps that can monitor and make use of the data. Have you given any thought to that? (I have no idea what the effort would be like, but it's can't hurt to ask...) it will take me 5 hours effective work time to add something like SHOW INNODB STATUS; I have to add it soon (June 2002), because the upcoming BMC Patrol Knowledge Module for InnoDB needs these stats to draw nice graphs. Thanks, Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.51: up 0 days, processed 18,745,524 queries (295/sec. avg) Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Where can I get help?
Hi. On Fri, May 31, 2002 at 11:39:12AM -0500, [EMAIL PROTECTED] wrote: Does anyone know where I can get help writing an MySQL update statement that contains a join in the where clause? Multi-table updates are not supported (yet) by MySQL. You have to construct the updates on the application side. Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: incorrect column specifier
On 31 May 2002, at 11:56, T. Dahms wrote: This line - building_idx decimal(8,0) unsigned zerofill DEFAULT '' not null auto_increment -is in a supplied perl script and causes the error message. An AUTO_INCREMENT column has to be an integer (see http://www.mysql.com/doc/C/R/CREATE_TABLE.html). Try INT or MEDIUMINT instead of DECIMAL(8,0). [Filter fodder: SQL] -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Constraints - Error or Bug???? - Part II
MySQL wrote: Hi all, It seems that some is misunderstanding my problem, so i try to re-write my problem again. I just wonder. I have tried this example from www.mysql.org Doc. CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL ) TYPE=INNODB; But, the constraint does not work propperly. If i add 1 and 2 in the parent table like this: insert into parent values (1); insert into parent values (2); and 1,1 and 1,6 in the child table, like this: insert into child values (1,1); insert into child values (1,6); , it still works !! What i know, is that the constraint is voilated if i insert the last insert statement (insert into child values (1,6)), because 6 is NOT in the parent table BTW: Any value is accepted in the child table - Thats Strange!!! Does anyone know if there is a Bug in the mysql, or im just getting it all wrong If you know howto make the constraints work please write a little example - Thanks :-) Regards Frank Jørgensen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Most likely your tables are not of the InnoDB type! If you haven't enabled InnoDB, MySQL will silently change them to the MyISAM type which doesn't support foreign keys. The table type that MySQL decided to use can be seen by issuing a show table status command. If they are not the InnoDB type, you will need to enable InnoDB as detailed in http://www.innodb.com/ibman.html#InnoDB_start -Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
my.cnf necessity
I currently am a MySQL dba and am having issues from a security/linux administrator that doesn't want me to have access to the /etc/my.cnf. What can't I do with local database .cnf files that I can only do with my.cnf? Any ideas? I have looked at the documentation and it doesn't really say what is only available through the global cnf file. I know that I will have to do everything at a database level for each file and that could get to be a slight management headache, but other than that, do I REALLY NEED access to change my.cnf for global options? Steve Spigarelli DBA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: my.cnf necessity
In the last episode (May 31), [EMAIL PROTECTED] said: I currently am a MySQL dba and am having issues from a security/linux administrator that doesn't want me to have access to the /etc/my.cnf. What can't I do with local database .cnf files that I can only do with my.cnf? There are no database-level .cnf files. There is /etc/my.cnf, DATADIR/my.cnf, and ~/my.cnf. They are all parsed the same way. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: HELP: MySQL Dynamic Structure
MySQL alone cannot represent a complex data-structure. not true at all, it's just not object oriented but don't you know that there is still no good (consistent and complete) theoretical model for OO-DBMS. Relational Model _has_ good underlying theory and I think this is the main reason of worlwide success of RDBMS. Re-modeling of MySQL data is required in Perl, PHP, Java, etc. This is called semantical impedance and best minds of mankind found no solution yet. * MySQL needs to have the ability to add properties to columns and tables. If I change one column, add one field, play with one table... I have to go into all of that code in all of those languages and mess with either how the front-ends and/or how the back-ends are interpreting the data-schema. 1.When you change semantics of one part of a system you have to reflect it in all other parts of the system. I'm afraid there could be no automation for most general kind of semantical modifications. 2.The more flexibility and dynamic behaviour (of the system) you want the more overhead you'll get (in general). -- Best regards, Yegor __ Yegor Bryukhov, PhD student at GC CUNY office: 4330 office phone: +1(212)817-8653 home phone: +1(718)842-4250 e-mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Constraints - Error or Bug????
Frank, please check with SHOW TABLE STATUS that the tables really are InnoDB type. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Original Message - From: MySQL [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Friday, May 31, 2002 7:45 PM Subject: Constraints - Error or Bug Hi all, I just wonder. I have tried this example from www.mysql.org Doc. CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL ) TYPE=INNODB; But, the constraint does not work propperly. If i add 1 and 2 in the parent table, and 1,1 and 1,6 in the child table, it still works. Does anyone know if there is a Bug in the mysql, or im just getting it all wrong If you know howto make the constraints work please write a little example - Thanks :-) Regards Frank Jørgensen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB Delete On Cascade
Hi! Please check that you are using = 3.23.50. I tested this on Win NT-4.0 with 3.23.52, and it worked: mysql CREATE TABLE parent(id INT NOT NULL, - PRIMARY KEY (id)) TYPE=INNODB; Query OK, 0 rows affected (0.14 sec) mysql CREATE TABLE child(id INT, parent_id INT, - INDEX par_ind (parent_id), - FOREIGN KEY (parent_id) REFERENCES parent(id) - ON DELETE CASCADE - ) TYPE=INNODB; Query OK, 0 rows affected (0.04 sec) mysql mysql insert into parent values(5); Query OK, 1 row affected (0.00 sec) mysql insert into child values(0,5); Query OK, 1 row affected (0.00 sec) mysql mysql delete from parent where id=5; Query OK, 1 row affected (0.01 sec) mysql mysql mysql select * from parent; Empty set (0.00 sec) mysql select * from child; Empty set (0.01 sec) Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Original Message - From: Me [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Friday, May 31, 2002 11:36 AM Subject: InnoDB Delete On Cascade Hello People, I was just designing my database and I was planning to finally use the new feature provided by InnoDB : ON DELETE CASCADE So I tried the example : CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) TYPE=INNODB; Created two records : insert into parent values(5); insert into child values(0,5); Ok this works all fine. Also geives me errors when I give it a parent ID of an unexisting record, so works like it should. Now isn't the goal of ON DELETE CASCADE is that when the parent record is deleted that the child records that reference the parent record id get deleted aswell? Because this doesn't seem to work. I get : mysql delete from parent where id=5; ERROR 1217: Cannot delete a parent row: a foreign key constraint fails Do I have the wrong idea baout the feature or what might be wrong? Using Version 3.23.50-max-nt and InnoDB. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
(newbieish)How do you query by indax?
Hi, I'm trying to make a query to my database based upon an index. Eg. we have last name,first name, eyecolour,age we search for all the records with the last name starting with 'smi' and I'm browsing forward through the records using mysql_use_result I get halfway through the last name 'smith' and now I want to browse backwards through the records organized by that key however if I say ='smith' it wont necessarily pull up the previous record. Help would be very much appreciated, -Andrew - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ncurses argument.
hi, I am installing mysql3.23.49. For that, I just installed ncurse In the ./configure --help, I found this switch --with-named-curses-libs=ARG what should I put inc the ARG when I do ./configure? Thanks, Wei - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Column names that contain %
I have a table that includes numerous column names of the form '%_Dry_Weight' -- that is, they start with a percent sign. I am unable to query these columns, as mysql returns a you have an error in your SQL syntax' message. When I try to escape the % character with a backslash, the error persists. If I put the column name in single quotes (e.g., select '%_Dry_Weight' from xxx) , the syntax is accepted, but the query returns the column name, rather than the values stored. According to the manual for 3.23.41 (which I am using), all characters are acceptable in column names. I am at an impasse - can anyone steer me in the proper direction? Many thanks! -- David Adam Quaternary Geologist Lake County, California - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqldump -A dump.txt
Hi, How do I deal with the import of this file? Every example I see involves a dump of a single database, or else uses a command line like my subject, but with no corresponding mysqlimport. If I use mysql dump.txt then I end up with errors that stop the process. With mysqlimport I can use --force, but I don't have that option with mysql dump.txt Thanks, Eric - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Finding holes in autoinc sequences
Genius. I knew there must be a way of tricking Mysql into applying a having statement and hence doing the whole query in a non join based statement. I have found the variable features of mysql most useful for a query of the type, select the top x rows per group. i.e. a limit per group. This is perfect if you are doing any statistical calculations which involves gathering the top 100 (or n) rows per id. Richard - Original Message - From: Kevin Fries [EMAIL PROTECTED] To: 'Richard Clarke' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, May 31, 2002 6:07 PM Subject: RE: Finding holes in autoinc sequences Richard, Say, that's really good! I'm still not used to taking advantage of those variables. Clever. Also, I think you can remove all but the rows showing gaps by adding a group by and having clause... select id,@b:=IF(id=@a+1,id,concat(@a+1,..,id-1)) range,@a:=id, @flag:=IF(@b=id,id,MISSING) from seq group by id having id range; -Original Message- From: Richard Clarke [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 30, 2002 7:48 PM To: [EMAIL PROTECTED] Subject: Re: Finding holes in autoinc sequences Or another approach could be (using same example tables) mysql set @a:=0; set @b:=0; set @c:=0; mysql select id,@b:=IF(id=@a+1,id,concat(@a+1,..,id-1)),@a:=id,@flag:=IF( @b=id,id,MISS ING) from seq; ++---++--- -- --+ | id | @b:=IF(id=@a+1,id,concat(@a+1,..,id-1)) | @a:=id | @flag:=IF(@b=id,id,MISSING) | ++---++--- -- --+ | 1 | 1 | 1 | 1 | | 2 | 2 | 2 | 2 | | 3 | 3 | 3 | 3 | | 4 | 4 | 4 | 4 | | 5 | 5 | 5 | 5 | | 6 | 6 | 6 | 6 | | 7 | 7 | 7 | 7 | | 8 | 8 | 8 | 8 | | 9 | 9 | 9 | 9 | | 12 | 10..11| 12 | MISSING | | 13 | 13| 13 | 13 | | 14 | 14| 14 | 14 | | 15 | 15| 15 | 15 | | 16 | 16| 16 | 16 | | 17 | 17| 17 | 17 | | 18 | 18| 18 | 18 | | 19 | 19| 19 | 19 | | 22 | 20..21| 22 | MISSING | | 24 | 23..23| 24 | MISSING | | 25 | 25| 25 | 25 | | 26 | 26| 26 | 26 | | 27 | 27| 27 | 27 | | 28 | 28| 28 | 28 | | 29 | 29| 29 | 29 | ++---++--- -- --+ 24 rows in set (0.00 sec) To get the MISSING entries you would need to create a temporary table from that query then select where the @flag column = MISSING. Maybe this is more efficient than the join previously suggested. Maybe mysql team would let HAVING be applied to the 'variably' created rows you could then add having flag_alias = MISSING project out just the rows that are missing (saving the need for temporary tables). you could also plug the 10..11 20..21 etc statements into a perl foreach loop (if you were using perl) to automatically create the inner numbers. Ric p.s. maybe there is a way of tricking it into using a having statement to project out the MISSING rows. - Original Message - From: Kevin Fries [EMAIL PROTECTED] To: 'mos' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, May 30, 2002 10:56 PM Subject: RE: Finding holes in autoinc sequences A simple and fast way to find the gaps is to use a self LEFT JOIN, such as: select a.id from seq a left join seq b on a.id + 1 = b.id where b.id is null; The result set will show any row in seq where there was no record with ID one greater than that record's value. You'll get back the last row, and any rows which don't have a next-higher neighbor. To get my example above to work, use: create table seq ( id int auto_increment not null primary key); insert into seq values(); ** repeat to get rows 1..30. ** delete from seq where id between 10 and 11;
Re: Column names that contain %
In the last episode (May 31), David Adam said: I have a table that includes numerous column names of the form '%_Dry_Weight' -- that is, they start with a percent sign. I am unable to query these columns, as mysql returns a you have an error in your SQL syntax' message. When I try to escape the % character with a backslash, the error persists. If I put the column name in single quotes (e.g., select '%_Dry_Weight' from xxx) , the syntax is accepted, but the query returns the column name, rather than the values stored. According to the manual for 3.23.41 (which I am using), all characters are acceptable in column names. I am at an impasse - can anyone steer me in the proper direction? Many thanks! backtics: `%_Dry_Weight` Or use a column name like Pct_Dry_Weight :) -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query Problem
I am having major troubles creating this query...ok here's some background info: I have three tables: members, which contains info about the member, such as city, state, zip, marital status, etc; interests, which stores just an interest_id and name; and member_interests, which stores just member_id and interest_id. What I need to do is create a query that selects certain members from the DB depending on certain demographics. For example, the query might need to select all members from the DB where their country is USA, Marital Status is Single, and have Golf selected as an interest. The first two are no problem...but, is there any way to create ONE query that would validate all the info stored in members table, as well as the member_interests table? And if not, can someone provide a sample solution method? TIA! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query Help (more)
As a follow up to my previous question, two possible solutions came to mind: 1) Query members table for all members matching criteria stored in that table (country, marital status, income, etc). Then, take all those member_ids, and query member_interests table for members who match there. i.e., SELECT member_id FROM member_interests WHERE interests_id = (XX) AND member_id IN (list of all member IDs here) 2) #1, reversed, which would make more sense, since I also need name and email. If there's any way to do this in one query, I should do it that way...otherwise, am I close on the best solution? Again, TIA! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql newbie show table question
Hrm. Closest I can think of is EXPLAIN SELECT * FROM demo WHERE name LIKE 'user_access'; I think this is what you mean. Adam Hooper [EMAIL PROTECTED] On Fri, 31 May 2002 08:44:49 -0700 marc malacarme [EMAIL PROTECTED] wrote: I can¹t seem to make any of the describe or show queries work when I apply a search statement: Query=show table status from demo; Works fine. Now I¹m trying to get info from only one table: Query=show table status from demo where name like user_access; Does no work. Thank You - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Setting Auto Increment
Hi All Is it that i can make a column auto increment only if its data type is integer. Can i not set auto increment value for a numeric datatype -Arul sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
