very slow subselect on large innoDB table
please help! i have a fairly large innoDB table with 800mb (index 500 mb, data 300mb) and 1.8 million data sets. the server has 8 gig ram. the statement SELECT id FROM table1 WHERE cityname = 'bla' (cityname has been indexed) takes 0.0002 seconds and returns 0 rows, which was expected. the statement: SELECT id from table1 WHERE id IN ( SELECT id FROM table1 WHERE cityname = 'bla ) still returns 0 rows but takes 12.9 seconds!! does anyone know why it takes so long and if there is anything i can do? thank you for your help, i really appreciate it.
To split or not to split columns
I have an index char column in the format -bb-cc-dd where a,b,c and d are integers Most of the time, the whole column will be searched for. But it will sometimes be necessary to search for the substring in b or d (or a combination of both) Thus if I split them up, they will need to be indexed as well. My question is: in terms of read and write performance, is it better to split them up into 4 indexed columns of integer instead or to keep them in one indexed column of char? If they should be kept in one column, what is the quickest way to search? To use a regular expression? - Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi-Table Insert Strategy
Selon Don Parris <[EMAIL PROTECTED]>: > O.k., this question is more about the best way to run a multi-table insert > on a MySQL DB (4.0.18), given a console based interface (Python 2.3.x under > SUSE Linux 9.2). My guess is that this would be a bit easier with a GUI, > where I would have widgets to assign to various functions. However, I am > currently building a console app. The GUI will come later. > > When I add a person to the DB, the main table impacted is called 'person'. > However, a few other tables are affected as well - entity, address, > affiliation, and aux_mbr. For example, the person table uses the keys from > the others: > > person.affil_id = affil.affil_id, person.ent_id = entity.ent_id, etc. > > Somehow, the program will need to get the foreign keys from the other > tables, and insert that into the person table. Technically, the program > won't be very likely to know what the current primary_key is for each table. > > The 2 options I see so far are: > (1) run insert queries on each table, ignoring the foreign key fields, and > then running an update query on person to add the foreign keys from the > respective tables: > insert into person values (PK, 'blah', 'blab') > insert into entity values (PK, 'bleep', 'blip') > update person set ent_id=1 where person_id=1 #'1' should be a variable > > (2) run a query when the input form is launched to determine the current > max value of the foreign keys, then use that info to automatically add the > foreign keys into the person table: > select entity_id MAX from entity > ### run Python input form, ent_id is passed to the appropriate input > statement ### > insert into person tuple #(PK, ent_id, 'blah', 'blab') > > The commands here aren't intended to be precise, but rather to help paint > the picture. > > A link to a previous thread or documentation on this would be fine. I don't > mind doing the reading - but my Google search seems to turn up more info > about multi-table deletes than inserts. I realize that MySQL doesn't > support a single multi-table insert function, but are the two approaches I > see good, bad, common?? > > Thanks, > Don > -- > evangelinuxGNU Evangelist > http://matheteuo.org/ http://chaddb.sourceforge.net/ > "Free software is like God's love - you can share it with anyone anytime > anywhere." > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > Hi, here are some links : http://www.php-resource.de/forum/showthread/t-54709.html http://dev.mysql.com/doc/mysql/en/insert.html http://bugs.mysql.com/bug.php?id=8732 http://bugs.mysql.com/bug.php?id=1980 http://archives.neohapsis.com/archives/mysql/2004-q3/3604.html http://forums.devshed.com/archive/t-51965/Insert-Into http://lists.nyphp.org/pipermail/talk/2003-September/005768.html http://dev.mysql.com/doc/mysql/en/innodb-multi-versioning.html http://www.issociate.de/board/post/26176/Ref:_WCL302_Subject:_UPDATE_multi-table_current_column_value_error.html Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some query help
Selon Mathias <[EMAIL PROTECTED]>: > Selon [EMAIL PROTECTED]: > > > "Matt Babineau" <[EMAIL PROTECTED]> wrote on 07/01/2005 05:05:28 PM: > > > > > Hi Again - > > > > > > I need some more help with a query. I have a list of numbers (bandwidth > > > required)... 2200, 2200, 2200, 400, 320 > > > > > > My data looks like this: > > > > > > Bandwidth | Distance > > > > > > 2250 | 10km > > > 1125 | 10km > > > 622 | 10km > > > 2250 | 20km > > > 1125 | 20km > > > 622 | 20km > > > 2250 | 40km > > > 1125 | 40km > > > 622 | 40km > > > > > > I need the query to look at the list of numbers, and figure out that a > > > certain distance has Bandwidths that are greater than each of the > > numbers. > > > > > > SELECT DISTINCT(distance) FROM fiber_config WHERE fiber_type = 2 AND > > > bandwidth > (2200, 2200, 2200, 220) ORDER BY distance ASC > > > > > > So I hope you can see what I am trying to get after. Basically I need to > > > fins a distance that can fit each of the numbers in the list. So if 2200 > > is > > > in the list, 2250 works. If 400 is in the list, 622 works. Thanks for > > the > > > help on this! > > > > > > > > > > > > Thanks, > > > > > > Matt Babineau > > > Criticalcode > > > 858.733.0160 > > > [EMAIL PROTECTED] > > > http://www.criticalcode.com > > > > > Actually, no. I can't see what you are trying to get after. I don't have > > enough context to work from. > > > > I just cannot visualize your problem well enough to help. What is the > > tuple/list of numbers (2200,2200,2200,220) supposed to represent and how > > would you use this list to find the records you wanted if you were doing > > it "by hand"? > > > > Imagine for me that none of this information is in a computer but has been > > printed out on paper. Now describe for me the decision process you want to > > perform and how I would do it using the printed lists. What would I need > > to compare to come up with the correct choices? > > > > Make sure you respond to the list so that everyone else can help, too! > > > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > > Hi Matt, > SELECT DISTINCT(distance) FROM fiber_config WHERE fiber_type = 2 AND > bandwidth > min(2200, 2200, 2200, 220) ORDER BY distance ASC > > > > Hope that helps > :o) > Mathias > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > errata : Hi Matt, SELECT DISTINCT(distance) FROM fiber_config WHERE fiber_type = 2 AND bandwidth > least(2200, 2200, 2200, 220) ORDER BY distance ASC least not min coz it's a row data, not column one. Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some query help
Selon [EMAIL PROTECTED]: > "Matt Babineau" <[EMAIL PROTECTED]> wrote on 07/01/2005 05:05:28 PM: > > > Hi Again - > > > > I need some more help with a query. I have a list of numbers (bandwidth > > required)... 2200, 2200, 2200, 400, 320 > > > > My data looks like this: > > > > Bandwidth | Distance > > > > 2250 | 10km > > 1125 | 10km > > 622 | 10km > > 2250 | 20km > > 1125 | 20km > > 622 | 20km > > 2250 | 40km > > 1125 | 40km > > 622 | 40km > > > > I need the query to look at the list of numbers, and figure out that a > > certain distance has Bandwidths that are greater than each of the > numbers. > > > > SELECT DISTINCT(distance) FROM fiber_config WHERE fiber_type = 2 AND > > bandwidth > (2200, 2200, 2200, 220) ORDER BY distance ASC > > > > So I hope you can see what I am trying to get after. Basically I need to > > fins a distance that can fit each of the numbers in the list. So if 2200 > is > > in the list, 2250 works. If 400 is in the list, 622 works. Thanks for > the > > help on this! > > > > > > > > Thanks, > > > > Matt Babineau > > Criticalcode > > 858.733.0160 > > [EMAIL PROTECTED] > > http://www.criticalcode.com > > > Actually, no. I can't see what you are trying to get after. I don't have > enough context to work from. > > I just cannot visualize your problem well enough to help. What is the > tuple/list of numbers (2200,2200,2200,220) supposed to represent and how > would you use this list to find the records you wanted if you were doing > it "by hand"? > > Imagine for me that none of this information is in a computer but has been > printed out on paper. Now describe for me the decision process you want to > perform and how I would do it using the printed lists. What would I need > to compare to come up with the correct choices? > > Make sure you respond to the list so that everyone else can help, too! > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > Hi Matt, SELECT DISTINCT(distance) FROM fiber_config WHERE fiber_type = 2 AND bandwidth > min(2200, 2200, 2200, 220) ORDER BY distance ASC Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible to delay index writes until server is less busy?
Write to a memory table first then do a hotcopy on a scheduled basis. Do you mean converting the memory table into MyISAM on a scheduled basis? (mysqlhotcopy only works on MyISAM and ISAM tables). Or is there a faster way of storing the table to disk? - Mathias We've been benchmarking a database that in real-life will have a huge write load (max peak load 1 inserts/second) to the same table (MyISAM). We will need about 4 indexes for that table. However, from our benchmark tests, it is clear that writing indexes takes too many resources and impedes the speed of inserting new records. To overcome this, we are thinking of: 1 - using several smaller tables (instead of one big one) by creating and writing to a new table every x hours, 2 - wait with writing the indexes until a new table has been created where the next inserts will be (i.e, not write indexes until the table has been closed) The biggest problem now is if the indexes are created when the server is very busy. If there was a way of telling MySQL to delay creating the indexes when it is busy, then a big obstacle would be out of the way. Is this possible? We could not find anything in the MySQL documentation concerning this. Any suggestions would be greatly appreciated. Kind regards, Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible to delay index writes until server is less busy?
Dan Nelson wrote: In the last episode (Jun 30), Mathias said: We've been benchmarking a database that in real-life will have a huge write load (max peak load 1 inserts/second) to the same table (MyISAM). We will need about 4 indexes for that table. However, from our benchmark tests, it is clear that writing indexes takes too many resources and impedes the speed of inserting new records. To overcome this, we are thinking of: 1 - using several smaller tables (instead of one big one) by creating and writing to a new table every x hours, 2 - wait with writing the indexes until a new table has been created where the next inserts will be (i.e, not write indexes until the table has been closed) You want the delay_key_write flag. You can set it per-table, or globally. You can use the "FLUSH TABLE mytable" command to force mysql to update the on-disk copy of the indexes. http://dev.mysql.com/doc/mysql/en/create-table.html http://dev.mysql.com/doc/mysql/en/myisam-start.html http://dev.mysql.com/doc/mysql/en/flush.html Yes, that is something we are considering doing. Any suggestions though how to best decide when to do this? As far as we know, there is no way of determinining from within MySQL whether it is very busy or not. I guess we need to determine that externaly before running the queries - Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible to delay index writes until server is less busy?
[EMAIL PROTECTED] wrote: Write to a memory table first then do a hotcopy on a scheduled basis. I'll look into that. Thanks for your reply. - Mathias - Original Message - From: "Mathias" <[EMAIL PROTECTED]> To: Sent: Thursday, June 30, 2005 9:10 AM Subject: Possible to delay index writes until server is less busy? We've been benchmarking a database that in real-life will have a huge write load (max peak load 1 inserts/second) to the same table (MyISAM). We will need about 4 indexes for that table. However, from our benchmark tests, it is clear that writing indexes takes too many resources and impedes the speed of inserting new records. To overcome this, we are thinking of: 1 - using several smaller tables (instead of one big one) by creating and writing to a new table every x hours, 2 - wait with writing the indexes until a new table has been created where the next inserts will be (i.e, not write indexes until the table has been closed) The biggest problem now is if the indexes are created when the server is very busy. If there was a way of telling MySQL to delay creating the indexes when it is busy, then a big obstacle would be out of the way. Is this possible? We could not find anything in the MySQL documentation concerning this. Any suggestions would be greatly appreciated. Kind regards, Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible to delay index writes until server is less busy?
We've been benchmarking a database that in real-life will have a huge write load (max peak load 1 inserts/second) to the same table (MyISAM). We will need about 4 indexes for that table. However, from our benchmark tests, it is clear that writing indexes takes too many resources and impedes the speed of inserting new records. To overcome this, we are thinking of: 1 - using several smaller tables (instead of one big one) by creating and writing to a new table every x hours, 2 - wait with writing the indexes until a new table has been created where the next inserts will be (i.e, not write indexes until the table has been closed) The biggest problem now is if the indexes are created when the server is very busy. If there was a way of telling MySQL to delay creating the indexes when it is busy, then a big obstacle would be out of the way. Is this possible? We could not find anything in the MySQL documentation concerning this. Any suggestions would be greatly appreciated. Kind regards, Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ordinal number within a table
Selon Michael Stassen <[EMAIL PROTECTED]>:
> Kapoor, Nishikant wrote:
>
> > [Sorry for cross-posting.]
> >
> > This is in continuation with the above mentioned subject - I am trying to
> > find the 'display data order' for the returned resultset. The following
> > thread very well answers my question:
> >
> > http://lists.mysql.com/mysql/185626
> >
> > a) SET @row=0;
> > b) SELECT (@row:[EMAIL PROTECTED]) AS row, ename, empno FROM emp ORDER BY
> empno;
> >
> > +-++---+
> > | row | ename | empno |
> > +-++---+
> > | 1 | SMITH | 7369 |
> > | 2 | ALLEN | 7499 |
> > | 3 | WARD | 7521 |
> > | 4 | JONES | 7566 |
> > | 5 | MARTIN | 7654 |
> > +-++---+
> >
> > However, I am trying to use it in a perl script instead of from command
> > line, and I am not sure how exactly to do it. I need to execute both
> > statements a & b together ...
>
> You cannot execute them together. You must execute them one at a time, in
> the
> same connection.
>
> > ... or else I get
> >
> > +-++---+
> > | row | ename | empno |
> > +-++---+
> > |NULL | SMITH | 7369 |
> > |NULL | ALLEN | 7499 |
> > |NULL | WARD | 7521 |
> > |NULL | JONES | 7566 |
> > |NULL | MARTIN | 7654 |
> > +-++---+
>
> If you are getting this, you've made a mistake in your perl code. It's hard
> to say what, though, as you haven't shown us your code.
>
> Hmmm. User variables are connection specific. Are you making the mistake of
> opening and closing a connection for each query? That's unneccessary, and it
> adds a lot of overhead.
>
> > How can I execute both (a) and (b) in my perl script?
>
> The same way you would execute any two statements, one at a time. Something
> like:
>
>$conn->do('SET @row=0');
>my $sql = 'SELECT @row:[EMAIL PROTECTED] as row, FROM
> WHERE ...';
>my $sth = $conn->prepare($sql);
>$sth->execute();
>return $sth->fetchall_arrayref( {} );
>
> > Thanks for any help.
> > Nishi
>
> Mathias wrote:
>
> > Hi,
> > You don"t need to use @row in perl,
> > just use :
> >
> > $n=0;
> > while (fetch..) {
> > $n++;
> > print "$n"."$ename ...\n";
> >
> > }
>
> That would work.
>
> Kapoor, Nishikant wrote:
>
> > I could, but I am assigning the entire resultset in one shot to another
> > construct as follows:
> >
> > my $str = "SELECT [EMAIL PROTECTED]:[EMAIL PROTECTED] as row,
> FROM WHERE
> ...";
> > my $sth = $conn->prepare($st);
> > $sth->execute();
> > return $sth->fetchall_arrayref( {} );
> >
> > Thanks,
> > -Nishi
>
> You are returning an arrayref! One row in your results equals one row in
> your
> array -- in the same order! Arrays are indexed, so display data order is
> already built into your array. Display position = array position + 1. Why
> do
> you need a redundant field in each row?
>
> Harald Fuchs wrote:
>
> > Just change the last line to
> >
> > my $n = 0;
> > return [ map { [ ++$n, @$_ ] } @{$sth->fetchall_arrayref} ];
> >
> > What's the problem?
>
> Are you sure? I get "Can't coerce array into hash at...".
>
> Mathias wrote:
>
> > Then alter your table to add an auto_increment column, update it and play
> > your query without @row.
> >
> > Mathias
>
> No, no, no! This is what we call using a shotgun to kill a gnat. It also
> doesn't yield the order of the query results.
>
> Michael
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> No, no, no! This is what we call using a shotgun to kill a gnat. It also
> doesn't yield the order of the query results.
sure that ${$ligne[$row]}{$n}:=$n with n perl operations can be faster !:)
Hope that helps
:o)
Mathias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joining tables - restricting selected records
Selon Michael Stassen <[EMAIL PROTECTED]>: > Mathias wrote: > > > Selon Russell Horn <[EMAIL PROTECTED]>: > > > >>This must have come up before, but I've not found it using a google > >>search. > >> > >>I have two tables customer and purchases > >> > >>customer: > >> customerID > >> customerName > >> > >>purchases: > >> purchaseID > >> customerID > >> purchaseDate > >> purchaseValue > >> > >>Is it possible in MySQL to join the tables so I only get the value of > >>the latest purchase? Or is this something that's better done in PHP, say > >>select all my customers and then one at a time do a query to select the > >>value of their last purchase? > >> > >>Thanks! > >> > >>Russell. > > > > Hi , > > join the tables with max(purchase_date) in the select,and of course group > by > > customer_id > > > > > > Hope that helps > > :o) > > Mathias > > Join with MAX(purchase_date)? Do you mean in a subquery? If so, that > requires 4.1. > > This is a FAQ. Three solutions are given in the manual, a subquery solution > for 4.1+, a temporary table solution for all versions, and a trick. > <http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html> > > Michael > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > sorry if i wasn't clear. i mean not select puchase_date, but max(purshase_date), i.e. use having clause. The join field is certainly customerId, or There is not suffiscient info on tables. Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joining tables - restricting selected records
Selon Russell Horn <[EMAIL PROTECTED]>: > This must have come up before, but I've not found it using a google > search. > > I have two tables customer and purchases > > customer: > customerID > customerName > > purchases: > purchaseID > customerID > purchaseDate > purchaseValue > > Is it possible in MySQL to join the tables so I only get the value of > the latest purchase? Or is this something that's better done in PHP, say > select all my customers and then one at a time do a query to select the > value of their last purchase? > > Thanks! > > Russell. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > Hi , join the tables with max(purshase_date) in the select,an dof course group by customer_id Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to edit part of a field?
Selon Brian Dunning <[EMAIL PROTECTED]>: > Hi all - > > I have an urgent need to update several million records. There is a > URL stored in a MySQL 'text' field. Many of the records contain > "1234" like this: > > http://www.domain.com?etc=etc&arg=1234&etc=etc > > Any occurence of "1234" has to be changed to "5678" like this: > > http://www.domain.com?etc=etc&arg=5678&etc=etc > > ...without changing the rest of the string. I'm hoping it's possible > to make this update to the entire table with a single SQL > statement If so I have no idea how to create it. Any help > appreciated. :) > > - Brian > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > Hi, update TABLE set field=replace(field,'=1234','=5678'); that's it. Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A question about the select count(*) performance and the InnoDB engine
Selon [EMAIL PROTECTED]: > Mathias <[EMAIL PROTECTED]> wrote on 06/28/2005 01:11:59 PM: > > > Selon [EMAIL PROTECTED]: > > > > > > > Thanks Shawn, but i'm not speaking about data consistency during > > transaction and > > isolation levels. > > I spoke about what is seen in the data dictionary as num_rows an why > > it can not > > be used even it's quite faster. > > > > > > > > Hope that helps > > :o) > > Mathias > > And I was trying to explain why there is not a number IN the data > dictionary that represents "row count". Unless a separate dictionary is > maintained FOR EACH TRANSACTION, the record counts will be wrong. The > record counts determined by SELECT COUNT(*) are *per transaction* so the > only way to do a record count is by checking each row (pending or not) > against cross-transaction isolation. > > This has everything to do with the row-level locking built into InnoDB and > unless they enhance the engine to maintain a list of table statistics (I > think this is part of what you are calling the dictionary) for each > transaction, there can't be a rapid lookup of the row count. Right now I > don't see that as high on their priorities. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > What i call data dictionary is this : mysql> use information_schema; mysql> select table_name,table_rows from tables; +---++ | table_name| table_rows | +---++ | SCHEMATA | NULL | | TABLES| NULL | | COLUMNS | NULL | | CHARACTER_SETS| NULL | | COLLATIONS| NULL | | COLLATION_CHARACTER_SET_APPLICABILITY | NULL | | ROUTINES | NULL | | STATISTICS| NULL | | VIEWS | NULL | | USER_PRIVILEGES | NULL | | SCHEMA_PRIVILEGES | NULL | | TABLE_PRIVILEGES | NULL | | COLUMN_PRIVILEGES | NULL | | TABLE_CONSTRAINTS | NULL | | KEY_COLUMN_USAGE | NULL | | columns_priv | 0 | | db| 0 | | func | 0 | | help_category | 29 | | help_keyword |325 | | help_relation |548 | | help_topic|405 | | host | 0 | | proc | 0 | | procs_priv| 0 | | tables_priv | 0 | | time_zone | 0 | | time_zone_leap_second | 0 | | time_zone_name| 0 | | time_zone_transition | 0 | | time_zone_transition_type | 0 | | user | 1 | +---++ 32 rows in set (0.06 sec) mysql> create table test.test1(a int); Query OK, 0 rows affected (0.08 sec) mysql> insert into test.test1 values(1); Query OK, 1 row affected (0.01 sec) mysql> insert into test.test1 values(2); Query OK, 1 row affected (0.01 sec) mysql> select table_name,table_rows from tables; +---++ | table_name| table_rows | +---++ | SCHEMATA | NULL | | TABLES| NULL | | COLUMNS | NULL | | CHARACTER_SETS| NULL | | COLLATIONS| NULL | | COLLATION_CHARACTER_SET_APPLICABILITY | NULL | | ROUTINES | NULL | | STATISTICS| NULL | | VIEWS | NULL | | USER_PRIVILEGES | NULL | | SCHEMA_PRIVILEGES | NULL | | TABLE_PRIVILEGES | NULL | | COLUMN_PRIVILEGES | NULL | | TABLE_CONSTRAINTS | NULL | | KEY_COLUMN_USAGE | NULL | | columns_priv | 0 | | db| 0 | | func
Re: A question about the select count(*) performance and the InnoDB engine
Selon [EMAIL PROTECTED]: > Mathias <[EMAIL PROTECTED]> wrote on 06/28/2005 06:13:08 AM: > > > Selon Behrang Saeedzadeh <[EMAIL PROTECTED]>: > > > > > Mathias, > > > > > > Thanks a lot! > > > > > > > I will not explain the same thing for sqlserver, sybase ..., but > when > > > > your RDBMs > > > > have a data dictionnary, you don't need to execute count(*) :o) > WITH > > > > Updated > > > > statistics of course. > > > > > > I'm a little bit confused here. Why the count(*) is not transformed to > a > > > select from the data dictionary if this way is faster? And what's the > > > difference between updated statistics and statistics not updated? > > > > > > > With information_schema in 5.x and higher, innodb will act as it's > done > > > > in all > > > > the other RDBMS. > > > > > > > > Hope that helps > > > > > > Sure! It helped by orders of magnitured more than I thought it can > help ;-) > > > > > > > :o) > > > > Mathias > > > > > > > > > > > > -- > > > Behrang Saeedzadeh > > > http://www.jroller.com/page/behrangsa > > > > > > Using Opera's revolutionary e-mail client > > > > > > > Well, > > The information in data dictionnary are correct only just after updating > them. > > imagine at 12h, you update statistics, num_rows=2000. At 12h05, you > > insert 1000 > > lignes and delete 500. > > > > At 12h10, you ask the data dictinary num_rows, it will give you 2000, > even if > > they are 2500. > > > > > > Hope that helps > > :o) > > Mathias > > > Mathias, > > COUNT(*) is not slow in InnoDB due to a lack of statistics. It's due to > the fact that for any user the value of COUNT(*) can be completely > different than for any other user. Assume for a moment that there is a > table stored in InnoDB that has 1000 records in it. UserA starts a > transaction that adds 200 records and changes 50. UserB also starts a > transaction and adds 500 records of his own. For the rest of this example, > both transactions remain "pending". > > Physically, the database now contains 1000 (original) + 200 (added by > UserA) + 50 (changes pending from UserA) + 500 (added by UserB) = 1750 > total records. However, if UserA performs a COUNT(*) query, they would > only be able to see the 1200 records visible within their transaction. > UserB will only be able to count 1500 records for the same reason. > > The slowness of performing a COUNT(*) query is caused by the need to > individually evaluate all 1750 records to see if the user that asked to > "count" them should actually know about them. Unless the engine is changed > to maintain a separate set of table statistics for each user there won't > be any way to just "look up" the number because the record count can (and > usually will) be different for each user. > > After both transactions commit, the database will only have 1700 records > (total) as the 50 pending updates, from UserA's transaction, will have > overwritten the 50 original records. > > Does that help? > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine Thanks Shawn, but i'm not speaking about data consistency during transaction and isolation levels. I spoke about what is seen in the data dictionary as num_rows an why it can not be used even it's quite faster. Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A question about the select count(*) performance and the InnoDB engine
Selon Behrang Saeedzadeh <[EMAIL PROTECTED]>: > Mathias, > > Thanks a lot! > > > I will not explain the same thing for sqlserver, sybase ..., but when > > your RDBMs > > have a data dictionnary, you don't need to execute count(*) :o) WITH > > Updated > > statistics of course. > > I'm a little bit confused here. Why the count(*) is not transformed to a > select from the data dictionary if this way is faster? And what's the > difference between updated statistics and statistics not updated? > > > With information_schema in 5.x and higher, innodb will act as it's done > > in all > > the other RDBMS. > > > > Hope that helps > > Sure! It helped by orders of magnitured more than I thought it can help ;-) > > > :o) > > Mathias > > > > -- > Behrang Saeedzadeh > http://www.jroller.com/page/behrangsa > > Using Opera's revolutionary e-mail client > Well, The information in data dictionnary are correct only just after updating them. imagine at 12h, you update statistics, num_rows=2000. At 12h05, you insert 1000 lignes and delete 500. At 12h10, you ask the data dictinary num_rows, it will give you 2000, even if they are 2500. Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A question about the select count(*) performance and the InnoDB engine
onnary, you don't need to execute count(*) :o) WITH Updated statistics of course. With information_schema in 5.x and higher, innodb will act as it's done in all the other RDBMS. Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER by Question
Selon Hassan Schroeder <[EMAIL PROTECTED]>: > Mathias wrote: > > > This is the right structure including "The" in the middle : > > mysql> SELECT * FROM names ORDER BY case when substring(name,1,3)='The' > >then REPLACE(name,'The ','') > >else name end; > > ? all of which produces exactly the same result as: > > SELECT * FROM names ORDER BY TRIM(LEADING "The " FROM name); > > But I guess when simplicity just won't do... :-) > > -- > Hassan Schroeder - [EMAIL PROTECTED] > Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com > >dream. code. > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > yes, your method is simplier and i never said the opposite. We learn from each other :o) good ! Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER by Question
Right, i have all my attention on the " The Yeti" order, and didn't see the rest. This is the right structure including "The" in the middle : mysql> SELECT * FROM names ORDER BY case when substring(name,1,3)='The' then REPLACE(name,'The ','') -> else name end; ++ | name | ++ | | | | | The | | The | | | | | | The Yeti | | Xylophone | | Zyxel | | woohoo | | The | | The | | | +----+ 13 rows in set (0.02 sec) Hope that's better Mathias Selon Hassan Schroeder <[EMAIL PROTECTED]>: > Mathias wrote: > > > you didn't give an alternative, but i've forgotten just a '^' : > > > mysql> SELECT * FROM names ORDER BY REPLACE(name,'^The ',''); > > No, sorry -- that doesn't work at all; REPLACE takes a string, > not a regex. Look at your example below: 'The ' should be > after ''; '' should be before 'The '. And so on. > > ++ > > | name | > > ++ > > | | > > | | > > | | > > | | > > | The Yeti | < Rigth order > > | Xylophone | > > | Zyxel | > > | The | > > | The | > > | The | > > | The | > > | | > > ++ > > Quick test: SELECT REPLACE(name,'^The ','woohoo') FROM names; -- :-) > > -- > Hassan Schroeder ----- [EMAIL PROTECTED] > Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com > >dream. code. > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER by Question
Hi, you didn't give an alternative, but i've forgotten just a '^' : mysql> SELECT * FROM names ORDER BY REPLACE(name,'The ',''); ++ | name | ++ | | | The | | The | | | | | | | | Xylophone | | The Yeti | | Zyxel | | The | | | | The | ++ 12 rows in set (0.00 sec) mysql> mysql> mysql> mysql> SELECT * FROM names ORDER BY REPLACE(name,'^The ',''); ++ | name | ++ | | | | | | | | | The Yeti | < Rigth order | Xylophone | | Zyxel | | The | | The | | The | | The | | | ++ 12 rows in set (0.00 sec) Mathias Selon Sergey Spivak <[EMAIL PROTECTED]>: > Hi > > > this,among other answers, can be done : > > > > mysql> select * from names; > > +--+ > > | name | > > +--+ > > | | > > | The | > > | | > > | The | > > | | > > +--+ > > 5 rows in set (0.02 sec) > > > > mysql> select * from names order by replace(name,'The ',''); > > +--+ > > | name | > > +--+ > > | | > > | The | > > | | > > | | > > | The | > > +--+ > > 5 rows in set (0.00 sec) > > > > Hmm... > Disanvantage of such way is replacing of 'The ' substring > in *ANY* place of field, not just at the beginning of it. :( > > Look here: > > mysql> SELECT * FROM names; > ++ > | name | > ++ > | | > | The | > | | > | The Yeti | > | The | > | Xylophone | > | Zyxel | > ++ > 7 rows in set (0.00 sec) > > mysql> SELECT * FROM names ORDER BY REPLACE(name,'The ',''); > ++ > | name | > ++ > | | > | The | > | | > | Xylophone | > | The Yeti | <--- must be earlier :) > | Zyxel | > | The | > ++ > 7 rows in set (0.00 sec) > > -- > wbr, sergey v. spivak > sergey#spivak.kiev.ua > zlob-uanic/eunic/ripe > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.x with php-4.3.x
You have the choice between this : http://dev.mysql.com/doc/mysql/en/application-password-use.html and this : The optimal solution when migrating to MySQL 4.1+ from a previous version is to upgrade to PHP 5 (if you're not using it already) and rewrite any code accessing MySQL using the mysqli extension, which is more secure and provides a much better API. http://fr.php.net/mysql I hope someone else gives you an url for such dll. Mathias Selon Daniel Kasak <[EMAIL PROTECTED]>: > Mathias wrote: > > >>haven't try WAMP5 ? > >>http://www.wampserver.com/en/ > >> > >> > I didn't know of this site, no. I'm new to Windows, at least on the server. > > Unfortunately they seem to be sticking with Apache-1.3, and I'd really > rather go with Apache-2, since all my experience has been with it. I've > read a lot about Apache-1.3 vs 2.0 and I'm fine with 2.0, especially > considering all the work that's gone into Windows support. > > They've also made the interesting decision to move to PHP-5.0.x, whereas > I'd prefer to stay with 4.3.x. I've been bitten hard by php upgrades > before. I also don't really feel comfortable using such a new version of > php on a production server ... especially when it's someone else's server. > > It would be simply lovely if I could just grab some mysql dlls ( offical > if possible ), and then use them with the official MySQL & PHP binaries. > > -- > Daniel Kasak > IT Developer > NUS Consulting Group > Level 5, 77 Pacific Highway > North Sydney, NSW, Australia 2060 > T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 > email: [EMAIL PROTECTED] > website: http://www.nusconsulting.com.au > Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.x with php-4.3.x
Selon Daniel Kasak <[EMAIL PROTECTED]>: > I realise that this question may better be asked in a php list, but I > already did that and got *no* answers. > Does anyone know if there are some mysql dlls available for php-4.3.x ( > I'm running 4.3.11) that are compiled against 4.1.x so I don't have to > use the ugly hacks to get the old client to talk to the new server? > I'm not really up to compiling things on Windows. > > -- > Daniel Kasak > IT Developer > NUS Consulting Group > Level 5, 77 Pacific Highway > North Sydney, NSW, Australia 2060 > T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 > email: [EMAIL PROTECTED] > website: http://www.nusconsulting.com.au > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > haven't try WAMP5 ? http://www.wampserver.com/en/ Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER by Question
Selon Jack Lauman <[EMAIL PROTECTED]>: > I'm using a query similar to the following to get an ordered list. > > SELECT ORDER BY Subscriber ASC, Name ASC; > > How do I change this so that if the 'Name' field begins with "The " that > the sort begins on the second word? In other words I'd like to be able > to return the word "The" but have it sort on whatever the second word is. > > Thanks, > > Jack > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > Hi, this,among other answers, can be done : mysql> select * from names; +--+ | name | +--+ | | | The | | | | The | | | +--+ 5 rows in set (0.02 sec) mysql> select * from names order by replace(name,'The ',''); +--+ | name | +------+ | | | The | | | | | | The | +--+ 5 rows in set (0.00 sec) Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [SPAM] - Unable to install mysql - Bayesian Filter detected spam
Try : fujitsu:/home/yannick # mysql -u root -p Then ENTER fujitsu:/home/yannick # mysql -u root -h localhost -P 3306 -p Then ENTER Mathias Selon Yannick <[EMAIL PROTECTED]>: > Hey, > > Thanks for your help till so far but I'm still with my issue ! Is there any > other support possible? > > FYI, I just reinstalled it with a higher version and the same issue is > comming. > I can not access mysql as root ! Please have a close look below : > > fujitsu:/home/yannick # mysql -u root---> # means I am as root on > Linux. > ERROR 1045: Access denied for user 'root'@'localhost' (using password: NO) > fujitsu:/home/yannick # su yannick ---> Here I move to a local > user : yannick > [EMAIL PROTECTED]:~> mysql -u root > ERROR 1045: Access denied for user 'root'@'localhost' (using password: NO) > [EMAIL PROTECTED]:~> ---> Meaning I can not login > with root > > ---> The goal being to add a root password on mysql. as explained here : > http://dev.mysql.com/doc/mysql/en/default-privileges.html > > However, > > [EMAIL PROTECTED]:~> mysql ---> Now it works but with > anonymous account. Low privilidges. > Welcome to the MySQL monitor. Commands end with ; or \g. > Your MySQL connection id is 18 to server version: 4.1.12-standard > > Type 'help;' or '\h' for help. Type '\c' to clear the buffer. > > mysql> show databases; > +--+ > | Database | > +--+ > | test | > +--+ > 1 row in set (0.00 sec) > > mysql> > > This means that I just have an anonymous account. > > > Best regards. > > Yannick > > -Message d'origine- > De : Mathias [mailto:[EMAIL PROTECTED] > Envoyé : Sunday, June 26, 2005 6:25 PM > À : [EMAIL PROTECTED] > Cc : [EMAIL PROTECTED]; mysql@lists.mysql.com; > [EMAIL PROTECTED] > Objet : RE: [SPAM] - Unable to install mysql - Bayesian Filter detected > spam > > > hi Yannick, > This is my ultimate help. > See this url, you have all in it : > http://dev.mysql.com/doc/mysql/en/unix-post-installation.html > > And all you want is here : > http://www.mysql.com/search/?q=Installation > > > If you're root, install mysql as root. At the end, change the "mysql root" > password to a password different from the linux root. Then create other > users > and databases. > > > Mathias > > > > Selon Yannick <[EMAIL PROTECTED]>: > > > Hi, > > > > I'm not sure you understand the issue. I have the root in linux but not in > > mysql ! > > All I want is to install mysql on my linux suse 9.0. Of course, I have the > > root access one the server ! > > As discussed below, I can not add any password on my sql. > > All I want is to know how to do it ! I've followed the tutorial of the > > installation and reinstalled it 3 times at least. However, I can never > enter > > mysql and to the things requested mainly because there is a security > issue. > > > > Yannick > > > > > > > > > > -Message d'origine- > > De : Mathias [mailto:[EMAIL PROTECTED] > > Envoyé : Sunday, June 26, 2005 4:03 PM > > À : [EMAIL PROTECTED] > > Objet : RE: [SPAM] - Unable to install mysql - Bayesian Filter detected > > spam > > > > > > Hi, > > Sorry but you are not the administrator else you have the root password. > > So why search midday at 14 ? > > > > Just connect with root pass and see if mysql is here. > > > > Else mysql is like the other databases in /var/lib/mysql/mysql > > > > I really can't see how an administrator haven't the administrator password > > > > Mathias > > > > > > Selon Yannick <[EMAIL PROTECTED]>: > > > > > Mathias, > > > > > > How can I do this ? I am the admin of this pc. > > > We've tested it before I think and it did not work. I still think it is > > > because there is no user file. Where can I found this file ? Where is it > > on > > > the pc ? > > > > > > Yannick > > > > > > -Message d'origine- > > > De : Mathias [mailto:[EMAIL PROTECTED] > > > Envoyé : Sunday, June 26, 2005 10:38 AM > > > À : [EMAIL PROTECTED] > > > Cc : [EMAIL PROTECTED]; mysql@lists.mysql.com; > > > [EMAIL PROTECTED] > > > Objet : RE: [SPAM] - Unable to install mysql - Bayesian Filter detected > > > spam > > > > > > > > > You are not
RE: [SPAM] - Unable to install mysql - Bayesian Filter detected spam
hi Yannick, This is my ultimate help. See this url, you have all in it : http://dev.mysql.com/doc/mysql/en/unix-post-installation.html And all you want is here : http://www.mysql.com/search/?q=Installation If you're root, install mysql as root. At the end, change the "mysql root" password to a password different from the linux root. Then create other users and databases. Mathias Selon Yannick <[EMAIL PROTECTED]>: > Hi, > > I'm not sure you understand the issue. I have the root in linux but not in > mysql ! > All I want is to install mysql on my linux suse 9.0. Of course, I have the > root access one the server ! > As discussed below, I can not add any password on my sql. > All I want is to know how to do it ! I've followed the tutorial of the > installation and reinstalled it 3 times at least. However, I can never enter > mysql and to the things requested mainly because there is a security issue. > > Yannick > > > > > -Message d'origine- > De : Mathias [mailto:[EMAIL PROTECTED] > Envoyé : Sunday, June 26, 2005 4:03 PM > À : [EMAIL PROTECTED] > Objet : RE: [SPAM] - Unable to install mysql - Bayesian Filter detected > spam > > > Hi, > Sorry but you are not the administrator else you have the root password. > So why search midday at 14 ? > > Just connect with root pass and see if mysql is here. > > Else mysql is like the other databases in /var/lib/mysql/mysql > > I really can't see how an administrator haven't the administrator password > > Mathias > > > Selon Yannick <[EMAIL PROTECTED]>: > > > Mathias, > > > > How can I do this ? I am the admin of this pc. > > We've tested it before I think and it did not work. I still think it is > > because there is no user file. Where can I found this file ? Where is it > on > > the pc ? > > > > Yannick > > > > -Message d'origine- > > De : Mathias [mailto:[EMAIL PROTECTED] > > Envoyé : Sunday, June 26, 2005 10:38 AM > > À : [EMAIL PROTECTED] > > Cc : [EMAIL PROTECTED]; mysql@lists.mysql.com; > > [EMAIL PROTECTED] > > Objet : RE: [SPAM] - Unable to install mysql - Bayesian Filter detected > > spam > > > > > > You are not granted access to mysql. > > So ask your root user to modify your password. > > > > impossible else. > > > > Mathias > > > > Selon Yannick <[EMAIL PROTECTED]>: > > > > > Hey Mathias, > > > > > > See the results below. I just have 1 database called "test"; > > > There is no user database. > > > > > > mysql> > > > mysql> use mysql > > > ERROR 1044: Access denied for user: '@localhost' to database 'mysql' > > > mysql> show tables > > > -> ; > > > ERROR 1046: No Database Selected > > > mysql> select database > > > -> ; > > > ERROR 1064: You have an error in your SQL syntax. Check the manual that > > > corresponds to your MySQL server version for the right syntax to use > near > > '' > > > at line 1 > > > mysql> quir > > > -> ; > > > ERROR 1064: You have an error in your SQL syntax. Check the manual that > > > corresponds to your MySQL server version for the right syntax to use > near > > > 'quir' at line 1 > > > mysql> quit > > > Bye > > > [EMAIL PROTECTED]:~> mysql > > > Welcome to the MySQL monitor. Commands end with ; or \g. > > > Your MySQL connection id is 163 to server version: 4.0.15-Max > > > > > > Type 'help;' or '\h' for help. Type '\c' to clear the buffer. > > > > > > mysql> show tables > > > -> ; > > > ERROR 1046: No Database Selected > > > mysql> show databases; > > > +--+ > > > | Database | > > > +--+ > > > | test | > > > +--+ > > > 1 row in set (0.00 sec) > > > > > > mysql> use test > > > Database changed > > > mysql> show tables; > > > Empty set (0.00 sec) > > > > > > mysql> select host,user,password from mysql.user; > > > ERROR 1044: Access denied for user: '@localhost' to database 'mysql' > > > mysql> select host,user,password from mysql.test; > > > ERROR 1044: Access denied for user: '@localhost' to database 'mysql' > > > mysql> > > > > > > -Message d'origine
Re: upgrade mysql 3.23.58 to 4.1
Selon mm <[EMAIL PROTECTED]>:
> Hi,
> There is a lot a pain here.
> I downloaded the rpm packages
> MySQL-server-4.1.12-1.i386.rpm
> MySQL-shared-4.1.12-1.i386.rpm
> MySQL-bench-4.1.12-1.i386.rpm
> MySQL-shared-compat-4.1.11-0.i386.rpm
> MySQL-client-4.1.12-1.i386.rpm
> MySQL-devel-4.1.12-1.i386.rpm
> MySQL-embedded-4.1.12-1.i386.rpm
> MySQL-Max-4.1.12-1.i386.rpm
>
> Maximum RPM said:
> ---
> rpm -qp --queryformat "%{defaultprefix}\n"
> Just replace with the name of the package file you want to
> check out. If the package is not relocatable, you'll only see the word
> (none).
>
> This doesn't work.
> --
> [EMAIL PROTECTED] MySQL]$ rpm -qp --queryformat "%{defaultprefix}\n"
> MySQL-Max-4.1.12-1.i386.rpm
> warning: MySQL-Max-4.1.12-1.i386.rpm: V3 DSA signature: NOKEY, key ID
> 5072e1f5
> error: incorrect format: unknown tag
>
> The MD5 check sum was OK bat "Signature Checking Using RPM" was not.
> ---
>
> My Linux experience is short so I have to read a lot for every movement.
> If you can gave me more help ti will be easier for me.
> Thanks,
> MT
>
>
>
> Kishore Jalleda wrote:
>
> >Yes You can have both versions, infact this is the preferred way to
> >upgrade, but the only thing is that have the new version install in
> >/usr/local/mysql2/ or what ever you want and change the port that the
> >newer daemon listens on instead of the default 3306, here's a good
> >link from the doc's of mysql...
> >http://dev.mysql.com/doc/mysql/en/upgrading-from-3-23.html
> >
> >Hope this helps,
> >Kishore Jalleda
> >
> >On 6/24/05, mm <[EMAIL PROTECTED]> wrote:
> >
> >
> >>I have on my system, Fedora core 3, MySql 3.23.58
> >>Working with wikipedia database I get one error an one advise to upgrade
> >>to Mysql 4.xx
> >>Can I have old mySQL and the new one on my system?
> >>It is necessary to recreate databases and reload the data?
> >>What other kind of problem is suppose to find on my way during upgrade?
> >>Thanks,
> >>MT
> >>
> >>
> >>--
> >>MySQL General Mailing List
> >>For list archives: http://lists.mysql.com/mysql
> >>To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> >>
> >>
> >>
> >>
> >
> >
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
Hi, if you transfered them from windows to linux with ftp without binary, that
should be the reason.
just retransfer using binary mode.
ELse, i can't see.
have you tried an install of one of them ?
Hope that helps
:o)
Mathias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [SPAM] - Unable to install mysql - Bayesian Filter detected spam
You are not granted access to mysql.
So ask your root user to modify your password.
impossible else.
Mathias
Selon Yannick <[EMAIL PROTECTED]>:
> Hey Mathias,
>
> See the results below. I just have 1 database called "test";
> There is no user database.
>
> mysql>
> mysql> use mysql
> ERROR 1044: Access denied for user: '@localhost' to database 'mysql'
> mysql> show tables
> -> ;
> ERROR 1046: No Database Selected
> mysql> select database
> -> ;
> ERROR 1064: You have an error in your SQL syntax. Check the manual that
> corresponds to your MySQL server version for the right syntax to use near ''
> at line 1
> mysql> quir
> -> ;
> ERROR 1064: You have an error in your SQL syntax. Check the manual that
> corresponds to your MySQL server version for the right syntax to use near
> 'quir' at line 1
> mysql> quit
> Bye
> [EMAIL PROTECTED]:~> mysql
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 163 to server version: 4.0.15-Max
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql> show tables
> -> ;
> ERROR 1046: No Database Selected
> mysql> show databases;
> +--+
> | Database |
> +--+
> | test |
> +--+
> 1 row in set (0.00 sec)
>
> mysql> use test
> Database changed
> mysql> show tables;
> Empty set (0.00 sec)
>
> mysql> select host,user,password from mysql.user;
> ERROR 1044: Access denied for user: '@localhost' to database 'mysql'
> mysql> select host,user,password from mysql.test;
> ERROR 1044: Access denied for user: '@localhost' to database 'mysql'
> mysql>
>
> -Message d'origine-
> De : Mathias [mailto:[EMAIL PROTECTED]
> Envoyé : Sunday, June 26, 2005 8:35 AM
> À : [EMAIL PROTECTED]
> Cc : [EMAIL PROTECTED]; mysql@lists.mysql.com;
> [EMAIL PROTECTED]
> Objet : RE: [SPAM] - Unable to install mysql - Bayesian Filter detected
> spam
>
>
> Hi,
> you should have a list in the user table of mysql database :
> mysql> use mysql
> Database changed
> mysql> show tables;
> +---+
> | Tables_in_mysql |
> +---+
> | columns_priv |
> | db|
> | func |
> | help_category |
> | help_keyword |
> | help_relation |
> | help_topic|
> | host |
> | tables_priv |
> | time_zone |
> | time_zone_leap_second |
> | time_zone_name|
> | time_zone_transition |
> | time_zone_transition_type |
> | user |
> +---+
> 15 rows in set (0.00 sec)
>
> mysql> select host,user,password from mysql.user;
> +---+--+---+
> | host | user | password |
> +---+--+-------+
> | localhost | root | *Exxx5D8x37183xxx5EBADF2A |
> | localhost | yannick| |
> | localhost | xxx| *Ex |
> +---+--+---+
> 1 row in set (0.00 sec)
>
>
> have you tried an update ?
> mysql> UPDATE mysql.user SET Password=OLD_PASSWORD('newpass')
> WHERE User='yannick' AND Host='localhost';
> mysql> FLUSH PRIVILEGES;
>
>
> Mathias
>
>
> Selon Yannick <[EMAIL PROTECTED]>:
>
> > Mathias,
> >
> > I would love to do it but this is the answer :
> >
> > mysql> SET PASSWORD FOR 'yannick'@'localhost' = PASSWORD('test');
> > ERROR 1133: Can't find any matching row in the user table
> > mysql>
> >
> > I also succeeded to enter with mysqladministrator. (Without password !)
> > The strange thing is that when I go to user admin he tells me: "could not
> > retrieve user privilege info" . It looks like there is not file for
> username
> > and passwords. If I try to add a user, the program shut.
> > Where should I check if there is such user file file ?
> >
> > Yannick
> >
> > -Message d'origine-
> > De : Mathias [mailto:[EMAIL PROTECTED]
> > Envoyé : Sunday, June 26, 2005 12:36 AM
> > À : [EMAIL PROTECTED]
> > Cc : [EMAIL PROTECTED]; mysql@lists.mysql.com;
> > [EMAIL PROTECTED]
> > Objet : RE: [SPAM] - U
Re: CHECK constraint
Hi,
your enum canbe NULL and is not indexed. So you can insert values not in enum,
replaced by NULL.
a solution is to UNIQUE index the enum column, and insert a unique bad value in
it.
Any value not in enum can not then be inseted :
mysql> create table enum_test(id int, name enum('test1','test2') NOT NULL,
UNIQUE KEY(name));
Query OK, 0 rows affected (0.06 sec)
mysql> desc enum_test;
+---+---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+---+--+-+-+---+
| id| int(11) | YES | | NULL| |
| name | enum('test1','test2') | | PRI | test1 | |
+---+---+--+-+-+---+
2 rows in set (0.00 sec)
mysql> INSERT INTO enum_test VALUES (0,'test3');
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql> INSERT INTO enum_test(id) VALUES (1);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * from enum_test;
+--+---+
| id | name |
+--+---+
|0 | |
|1 | test1 |
+--+---+
2 rows in set (0.00 sec)
mysql> INSERT INTO enum_test VALUES (1,'test3');
ERROR 1062 (23000): Duplicate entry '' for key 1
Mathias
Selon Michael Kruckenberg <[EMAIL PROTECTED]>:
> > Hi,
> > Use enum with a default type and let mysql do the check for you.
>
> The problem with an enum is that if you insert a value that's not in
> the enum, MySQL doesn't stop the insert, it leaves the column empty.
> This doesn't enforce data integrity like I think Chris wanted.
>
> mysql> desc enum_test;
> +---+---+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+---+--+-+-+---+
> | id| int(11) | YES | | NULL| |
> | name | enum('test1','test2') | YES | | test2 | |
> +---+---+--+-+-+---+
> 2 rows in set (0.25 sec)
>
> mysql> INSERT INTO enum_test VALUES (1,'test3');
> Query OK, 1 row affected, 1 warning (0.29 sec)
>
> mysql> SELECT * from enum_test;
> +--+--+
> | id | name |
> +------+--+
> |1 | |
> +--+--+
> 1 row in set (0.00 sec)
>
> Mike Kruckenberg
> [EMAIL PROTECTED]
> "ProMySQL" Author
> http://www.amazon.com/exec/obidos/ASIN/159059505X
>
>
>
Hope that helps
:o)
Mathias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [SPAM] - Unable to install mysql - Bayesian Filter detected spam
Hi,
you should have a list in the user table of mysql database :
mysql> use mysql
Database changed
mysql> show tables;
+---+
| Tables_in_mysql |
+---+
| columns_priv |
| db|
| func |
| help_category |
| help_keyword |
| help_relation |
| help_topic|
| host |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name|
| time_zone_transition |
| time_zone_transition_type |
| user |
+---+
15 rows in set (0.00 sec)
mysql> select host,user,password from mysql.user;
+---+--+---+
| host | user | password |
+---+--+---+
| localhost | root | *Exxx5D8x37183xxx5EBADF2A |
| localhost | yannick| |
| localhost | xxx| *Ex |
+---+--+---+
1 row in set (0.00 sec)
have you tried an update ?
mysql> UPDATE mysql.user SET Password=OLD_PASSWORD('newpass')
WHERE User='yannick' AND Host='localhost';
mysql> FLUSH PRIVILEGES;
Mathias
Selon Yannick <[EMAIL PROTECTED]>:
> Mathias,
>
> I would love to do it but this is the answer :
>
> mysql> SET PASSWORD FOR 'yannick'@'localhost' = PASSWORD('test');
> ERROR 1133: Can't find any matching row in the user table
> mysql>
>
> I also succeeded to enter with mysqladministrator. (Without password !)
> The strange thing is that when I go to user admin he tells me: "could not
> retrieve user privilege info" . It looks like there is not file for username
> and passwords. If I try to add a user, the program shut.
> Where should I check if there is such user file file ?
>
> Yannick
>
> -Message d'origine-
> De : Mathias [mailto:[EMAIL PROTECTED]
> Envoyé : Sunday, June 26, 2005 12:36 AM
> À : [EMAIL PROTECTED]
> Cc : [EMAIL PROTECTED]; mysql@lists.mysql.com;
> [EMAIL PROTECTED]
> Objet : RE: [SPAM] - Unable to install mysql - Bayesian Filter detected
> spam
>
>
> Since you can connect as yannick without password, just set it after login :
>
> mysql> SET PASSWORD FOR 'yannick'@'localhost' = PASSWORD('newpass');
> see http://dev.mysql.com/doc/mysql/en/set-password.html for more details.
>
> I advice to set it to another password than your yannick linux one.
>
> Mathias
>
> Selon Yannick <[EMAIL PROTECTED]>:
>
> > Mathias,
> >
> > Did what you asked :
> >
> > [EMAIL PROTECTED]:~> mysql -u yannick -p
> > Enter password:
> > ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
> > YES)
> > [EMAIL PROTECTED]:~> mysql -u yannick -p
> > Enter password:
> > Welcome to the MySQL monitor. Commands end with ; or \g.
> > Your MySQL connection id is 134 to server version: 4.0.15-Max
> >
> > Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
> >
> > mysql>
> >
> > It works only if I put NO password. If I put the password of yannick it
> > doesn't work. But here as well, I must confuse the username yannick in
> mysql
> > and the user yannick in linux.
> >
> > I can not ask the administrator what password he put because I am the
> > administrator of this computer !! :))
> >
> > Also, I have just installed MysqlAdministrator and here as well, I can not
> > access the server... Same error messages.
> >
> > How can I add set the root password ? How can I flush the privilidges ?
> >
> > Thanks
> >
> > Yannick
> > -Message d'origine-
> > De : Mathias [mailto:[EMAIL PROTECTED]
> > Envoyé : Sunday, June 26, 2005 12:01 AM
> > À : Mathias
> > Cc : [EMAIL PROTECTED]; [EMAIL PROTECTED];
> > mysql@lists.mysql.com; [EMAIL PROTECTED]
> > Objet : RE: [SPAM] - Unable to install mysql - Bayesian Filter detected
> > spam
> >
> >
> > Another thing :
> > > fujitsu:/home/yannick # mysql
> > > ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
> YES)
> > > fujitsu:/home/yannick #
> >
> > is normal:
> >
> > try fujitsu:/home/yannick # mysql -u yannick -p
> >
> > Mathias
> >
> > Selon Mathias <[EMAIL PROTECTED]>:
> >
> > > Hi ,
> > >
Re: Searching "IN" a comma separated list
Vous être le bienvenue :o) Selon W Luke <[EMAIL PROTECTED]>: > On 25/06/05, Mathias <[EMAIL PROTECTED]> wrote: > > > I then write you this query which should give you the idea, and i think the > > solution : > > > > mysql> select gid,ugid,FIND_IN_SET(gid,ugid) from groups,groupsList > > -> where FIND_IN_SET(gid,ugid) > 0; > > +--+-+---+ > > | gid | ugid| FIND_IN_SET(gid,ugid) | > > +--+-+---+ > > |1 | 1,2,3,4 | 1 | > > |2 | 1,2,3,4 | 2 | > > |3 | 1,2,3,4 | 3 | > > |2 | 2,5,6 | 1 | > > |3 | 3,4,5 | 1 | > > |9 | 7,4,9 | 3 | > > +--+-+---+ > > 6 rows in set (0.00 sec) > > > > Et voilà. > > > > Hope that helps > > Brilliant - I wasn't aware of FIND_IN_SET, and that's exactly what I > needed. Amazing! Thank you very much indeed - merci beaucoup > Mathias, vraiment. > > -- > Will > -- The Corridor of Uncertainty -- > -- http://www.cricket.mailliw.com/ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [SPAM] - Unable to install mysql - Bayesian Filter detected spam
Since you can connect as yannick without password, just set it after login :
mysql> SET PASSWORD FOR 'yannick'@'localhost' = PASSWORD('newpass');
see http://dev.mysql.com/doc/mysql/en/set-password.html for more details.
I advice to set it to another password than your yannick linux one.
Mathias
Selon Yannick <[EMAIL PROTECTED]>:
> Mathias,
>
> Did what you asked :
>
> [EMAIL PROTECTED]:~> mysql -u yannick -p
> Enter password:
> ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
> YES)
> [EMAIL PROTECTED]:~> mysql -u yannick -p
> Enter password:
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 134 to server version: 4.0.15-Max
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql>
>
> It works only if I put NO password. If I put the password of yannick it
> doesn't work. But here as well, I must confuse the username yannick in mysql
> and the user yannick in linux.
>
> I can not ask the administrator what password he put because I am the
> administrator of this computer !! :))
>
> Also, I have just installed MysqlAdministrator and here as well, I can not
> access the server... Same error messages.
>
> How can I add set the root password ? How can I flush the privilidges ?
>
> Thanks
>
> Yannick
> -Message d'origine-
> De : Mathias [mailto:[EMAIL PROTECTED]
> Envoyé : Sunday, June 26, 2005 12:01 AM
> À : Mathias
> Cc : [EMAIL PROTECTED]; [EMAIL PROTECTED];
> mysql@lists.mysql.com; [EMAIL PROTECTED]
> Objet : RE: [SPAM] - Unable to install mysql - Bayesian Filter detected
> spam
>
>
> Another thing :
> > fujitsu:/home/yannick # mysql
> > ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
> > YES)
> > fujitsu:/home/yannick #
>
> is normal:
>
> try fujitsu:/home/yannick # mysql -u yannick -p
>
> Mathias
>
> Selon Mathias <[EMAIL PROTECTED]>:
>
> > Hi ,
> > You did a confusion between root for mysql (wihich is SA or DBA group) and
> > the
> > root of the OS.
> >
> > have you ever installed mysql in windows. there is a root user even in
> > windows.
> > What you must do is to ask the person who installed for the "Mysql root
> > password" and check the users created in the database. If the root
> password
> > haven't been set, do it and flush privileges.
> >
> >
> >
> > Selon Yannick <[EMAIL PROTECTED]>:
> >
> > > Nils,
> > >
> > > Thanks for your support but still not working.
> > > All I need is to have MYSQL installed with a root password.
> > > For exemple, I can never login when I am a root user. I always need to
> be a
> > > user like "yannick"
> > > I can also not make any request other the localhost as when I add -h
> > > fujitsu, he makes re request on fujitsu.local.
> > > How can I check the 4 databases to see what host has been used for which
> > > username ?
> > > If needed, I can also give you a telnet session to check what is going
> > > wrong. I have already installed it 3 times and always the same result...
> I
> > > don't beleive on an installation problem but on an authentification
> issue.
> > >
> > > Thanks for you support.
> > >
> > > Yannick
> > >
> > >
> > > See details below:
> > >
> > > [EMAIL PROTECTED]:~> mysql
> > > Welcome to the MySQL monitor. Commands end with ; or \g.
> > > Your MySQL connection id is 111 to server version: 4.0.15-Max
> > > Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
> > > mysql>
> > > fujitsu:/home/yannick # mysql
> > > ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
> YES)
> > > fujitsu:/home/yannick #
> > >
> > > => If I have no access with root, I can not create any new database
> > > That is the problem.
> > >
> > > -Message d'origine-
> > > De : Nils Valentin [mailto:[EMAIL PROTECTED]
> > > Envoyé : Saturday, June 18, 2005 2:57 PM
> > > À : mysql@lists.mysql.com; [EMAIL PROTECTED]
> > > Cc : [EMAIL PROTECTED]
> > > Objet : Re: [SPAM] - Unable to install mysql - Bayesian Filter detected
> > > spam
> > >
> > >
> > > Hi yannick,
> > >
> > > Not sure if you have found the problem yet, any way there ar
RE: [SPAM] - Unable to install mysql - Bayesian Filter detected spam
Another thing : > fujitsu:/home/yannick # mysql > ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) > fujitsu:/home/yannick # is normal: try fujitsu:/home/yannick # mysql -u yannick -p Mathias Selon Mathias <[EMAIL PROTECTED]>: > Hi , > You did a confusion between root for mysql (wihich is SA or DBA group) and > the > root of the OS. > > have you ever installed mysql in windows. there is a root user even in > windows. > What you must do is to ask the person who installed for the "Mysql root > password" and check the users created in the database. If the root password > haven't been set, do it and flush privileges. > > > > Selon Yannick <[EMAIL PROTECTED]>: > > > Nils, > > > > Thanks for your support but still not working. > > All I need is to have MYSQL installed with a root password. > > For exemple, I can never login when I am a root user. I always need to be a > > user like "yannick" > > I can also not make any request other the localhost as when I add -h > > fujitsu, he makes re request on fujitsu.local. > > How can I check the 4 databases to see what host has been used for which > > username ? > > If needed, I can also give you a telnet session to check what is going > > wrong. I have already installed it 3 times and always the same result... I > > don't beleive on an installation problem but on an authentification issue. > > > > Thanks for you support. > > > > Yannick > > > > > > See details below: > > > > [EMAIL PROTECTED]:~> mysql > > Welcome to the MySQL monitor. Commands end with ; or \g. > > Your MySQL connection id is 111 to server version: 4.0.15-Max > > Type 'help;' or '\h' for help. Type '\c' to clear the buffer. > > mysql> > > fujitsu:/home/yannick # mysql > > ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: > > YES) > > fujitsu:/home/yannick # > > > > => If I have no access with root, I can not create any new database > > That is the problem. > > > > -Message d'origine- > > De : Nils Valentin [mailto:[EMAIL PROTECTED] > > Envoyé : Saturday, June 18, 2005 2:57 PM > > À : mysql@lists.mysql.com; [EMAIL PROTECTED] > > Cc : [EMAIL PROTECTED] > > Objet : Re: [SPAM] - Unable to install mysql - Bayesian Filter detected > > spam > > > > > > Hi yannick, > > > > Not sure if you have found the problem yet, any way there are many > tutorials > > online which explain how to proberly setup user accounts. > > > > [EMAIL PROTECTED] > > [EMAIL PROTECTED] > > [EMAIL PROTECTED] > > yannick > > > > etc. are all separate users with different rights. So the combination of > > username, hostname and password is to be thought of as a set. > > > > have alook at below links, which I hope help you kichstart and troubleshoot > > any issues you are having. > > > > http://www.devshed.com/c/b/MySQL/ > > http://www.devshed.com/c/a/MySQL/The-MySQL-Grant-Tables/ > > http://www.php-mysql-tutorial.com/mysql-tutorial/add-new-mysql-user.php > > > > > > I hope that you find this info useful. > > > > Best regards > > > > Nils Valentin > > Tokyo / Japan > > http;//www.be-known-online.com > > > > > > > > > > On Tuesday 14 June 2005 06:58, Yannick wrote: > > > Kevin, > > > > > > In addition to that, the ZORUM database works because when I stop mysql, > > > the following site stops working : > > > http://www.wxy.nl/zorum_3_5/ with the database ZORUM > > > > > > > > > Here PHPadmin doesn't give me any privilege to create anything : > > > http://www.wxy.nl/phpMyAdmin/ > > > > > > > > > I beleive I'm not to far from having it working ut I still have this > > > priviledge issue. > > > > > > BEst regards > > > > > > Yannick > > > > > > > > > -Message d'origine- > > > De : Yannick [mailto:[EMAIL PROTECTED] > > > Envoyé : Monday, June 13, 2005 11:54 PM > > > À : [EMAIL PROTECTED] > > > Cc : mysql@lists.mysql.com > > > Objet : RE: [SPAM] - Unable to install mysql - Bayesian Filter detected > > > spam > > > > > > > > > Kevin, > > > > > > Thanks for your answer. See below the results : > > > * I' really wondering if there is not any missing files. >
RE: [SPAM] - Unable to install mysql - Bayesian Filter detected spam
t; > Office 818.253.3819 Fax 818.834.8843 > > [EMAIL PROTECTED] > > > > -Original Message- > > From: Yannick [mailto:[EMAIL PROTECTED] > > Sent: Monday, June 13, 2005 11:42 AM > > To: mysql@lists.mysql.com > > Subject: [SPAM] - Unable to install mysql - Bayesian Filter detected > > spam > > > > Hey guys, > > > > I am not able to install properly mysql. Please see below the technical > > details or the bug report. > > > > The installation goes well until I try to add the root user : > > > > [EMAIL PROTECTED]:/usr/bin> mysqladmin -u root -h fujitsu password x > > mysqladmin: connect to server at 'fujitsu' failed > > error: 'Host 'fujitsu.local' is not allowed to connect to this MySQL > > server' > > [EMAIL PROTECTED]:/usr/bin> mysqladmin -u root -h 192.168.234.2 password > > xx > > mysqladmin: connect to server at '192.168.234.2' failed > > error: 'Host '192.168.234.2' is not allowed to connect to this MySQL > > server' > > [EMAIL PROTECTED]:/usr/bin> > > > > > > Continuously, and I've tried lots of combinations, he is failing to > > connect > > to the server. > > > > Please give me so hints for me to be able to install it. > > > > Best regards > > > > Yannick > > > > [EMAIL PROTECTED] > > > > > > > > SEND-PR: -*- send-pr -*- > > SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as > > SEND-PR: will all comments (text enclosed in `<' and `>'). > > SEND-PR: > > From: yannick > > To: mysql@lists.mysql.com > > Subject: [50 character or so descriptive subject here (for reference)] > > > > >Description: > > > > > > > > >How-To-Repeat: > > > > > lines)> > > > > >Fix: > > > > > lines)> > > > > >Submitter-Id: > > >Originator:Yannick Vauloup > > >Organization: > > > > > > > > >MySQL support: [none | licence | email support | extended email support > > > > ] > > > > >Synopsis: > > >Severity: <[ non-critical | serious | critical ] (one line)> > > >Priority: <[ low | medium | high ] (one line)> > > >Category: mysql > > >Class: <[ sw-bug | doc-bug | change-request | support ] (one > > > > line)> > > > > >Release: mysql-4.0.15 (Source distribution) > > >Server: /usr/bin/mysqladmin Ver 8.40 Distrib 4.0.15, for suse-linux on > > > > i686 > > Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB > > This software comes with ABSOLUTELY NO WARRANTY. This is free software, > > and you are welcome to modify and redistribute it under the GPL license > > > > Server version 4.0.15-Max > > Protocol version10 > > Connection Localhost via UNIX socket > > UNIX socket /var/lib/mysql/mysql.sock > > Uptime: 5 min 8 sec > > > > Threads: 1 Questions: 14 Slow queries: 0 Opens: 6 Flush tables: 1 > > Open > > tables: 0 Queries per second avg: 0.045 > > > > >C compiler:gcc (GCC) 3.3.1 (SuSE Linux) > > >C++ compiler: g++ (GCC) 3.3.1 (SuSE Linux) > > >Environment: > > > > > > System: Linux fujitsu 2.4.21-286-default #1 Sat Apr 2 08:57:10 UTC 2005 > > i686 > > i686 i386 GNU/Linux > > Architecture: i686 > > > > Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc > > /usr/bin/cc > > GCC: Lecture des spécification à partir de > > /usr/lib/gcc-lib/i586-suse-linux/3.3.1/specs > > Configuré avec: > > ../configure --enable-threads=posix --prefix=/usr > > --with-local-prefix=/usr/l > > ocal --infodir=/usr/share/info --mandir=/usr/share/man --libdir=/usr/lib > > --e > > nable-languages=c,c++,f77,objc,java,ada --disable-checking > > --enable-libgcj - > > -with-gxx-include-dir=/usr/include/g++ --with-slibdir=/lib > > --with-system-zli > > b --enable-shared --enable-__cxa_atexit i586-suse-linux > > Modèle de thread: posix > > version gcc 3.3.1 (SuSE Linux) > > Compilation info: CC='gcc' > > CFLAGS='-O2 -march=i586 -mcpu=i686 -fmessage-length=0 -DPIC -fPIC' > > CXX='g++' > > FLAGS='-O2 -march=i586 -mcpu=i686 -fmessage-length=0 > > -felide-constructor > > s -fno-exceptions > > -fno-rtti -fPIC -DPIC' LDFLAGS='' > > ASFLAGS='' > > LIBC: > > -rwxr-xr-x1 root root 1469811 2003-09-24 01:05 > > /lib/libc.so.6 > > -rw-r--r--1 root root 13553180 2003-09-23 18:04 > > /usr/lib/libc.a > > -rw-r--r--1 root root 204 2003-09-23 18:04 > > /usr/lib/libc.so > > lrwxrwxrwx1 root root 20 2005-02-22 14:05 > > /usr/lib/libc-client.so -> libc-client.so.2002d > > -rwxr-xr-x1 root root 770436 2003-09-23 20:29 > > /usr/lib/libc-client.so.2002d > > Configure command: ./configure '--disable-shared' > > '--enable-thread-safe-client' '--with-mysqld-ldflags=-static' > > '--with-client-ldflags=-static' '--without-berkeley-db' > > '--with-extra-tools' > > '--without-innodb' '--enable-assembler' '--enable-large-files' > > '--infodir=/usr/share/info' '--libdir=/usr/lib' '--libexecdir=/usr/sbin' > > '--localstatedir=/var/lib/mysql' '--mandir=/usr/share/man' > > '--prefix=/usr' > > '--sysconfdir=/etc' '--with-mysqld-user=mysql' '--without-debug' > > '--datadir=/usr/share' '--includedir=/usr/include' > > '--with-extra-charsets=complex' > > '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--with-libwrap' > > 'CFLAGS=-O2 -march=i586 -mcpu=i686 -fmessage-length=0 -DPIC -fPIC' > > 'CXXFLAGS=-O2 -march=i586 -mcpu=i686 -fmessage-length=0 > > -felide-construc > > tors-fno-exceptions > > -fno-rtti -fPIC -DPIC' > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > -- > kind regards > > Nils Valentin > Tokyo/Japan > > http://www.be-known-online.com/mysql/ > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CHECK constraint
Selon Michael Kruckenberg <[EMAIL PROTECTED]>:
> A trigger is a good place to check the value, and change it, bit I
> don't believe you can actually generate a MySQL error within the
> trigger that will prevent the data from being inserted. Currently
> (unless there's been an update to triggers that's not yet in the
> docs), you can only change the value before it gets inserted.
>
> If you are looking to enforce the values going into your JobType
> column, you might be better off creating a JobType table, with a
> foreign key restraint between the tblJob.JobType and JobType.Name,
> and make sure that the only entries in the JobType.Name column are
> those you want to appear in the tblJob.JobType column.
>
> On Jun 25, 2005, at 10:28 AM, Chris Andrew wrote:
>
> > Dear List,
> >
> > My system is RedHat EL3 and MySQL 5.0.7-beta.
> >
> > I wanted to implement a check constraint (below), but after some
> > testing
> > and googling, it seems I can't do this with MySQL. I've read
> > suggestions
> > that check(s) should be done using triggers. Is a trigger a preferred
> > method of achieving the following:
> >
> > CREATE TABLE tblJob (
> > JobId SMALLINT UNSIGNED NOT NULL,
> > CustomerIdSMALLINT UNSIGNED NOT NULL,
> > JobType VARCHAR(20) NOT NULL DEFAULT 'DesignInstall',
> > Description VARCHAR(100) NOT NULL,
> > QuotationDate DATE NOT NULL,
> > OrderDate DATE,
> > CHECK (JobType IN ('DesignOnly', 'DesignInstall', 'InstallOnly')),
> > PRIMARY KEY (JobId, CustomerId)
> > ) TYPE=InnoDB;
> >
> > Regards,
> > Chris
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/mysql?
> > [EMAIL PROTECTED]
> >
>
> Mike Kruckenberg
> [EMAIL PROTECTED]
> "ProMySQL" Author
> http://www.amazon.com/exec/obidos/ASIN/159059505X
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
Hi,
Use enum with a default type and let mysql do the check for you.
Hope that helps
:o)
Mathias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching "IN" a comma separated list
Selon W Luke <[EMAIL PROTECTED]>: > Hi, > > Have struggled with this all day, and didn't know where else to ask. > If it's not appropriate to the list, I apologise - and advice or > pointers would be brilliant, as my head is now hurting! > > So here's the situation: > > 3 tables. f_u_groups f_groups and f_images > > f_u_groups contains the user's groups with the following columns: > fuid, fugids, funame > > fuid is the primary key (auto incremented), funame is the name of the > user-group and fuguids contains a comma-separated list of groups (eg > 12,13,14,15). > > This column (fuguids) corresponds to the column gid (primary key) in > f_groups. In plain English, fuguids contains a list of groups (for > each user), which are then listed in the table f_groups. > > When images are inserted into f_images (I'm using PHP, and mysql's > "unique" index to prevent duplicates), I need to check against the > other two tables to make sure that the *group* the image is labelled > as is "active." And by active, I mean whether a group is listed > inside *any* of the comma-separated lists in fugids. > > To insert images, a loop is performed over the f_groups table - so > this is where I need the "magic SQL" which I can't work out. Loop > over each row in the f_groups - check f_groups.gid *inside* > f_u_groups.fugids. > > I thought IN would do it: > > SELECT group_name, gid, fugids > FROM f_u_groups, f_groups > WHERE gid > IN (fugids) > > But that only cross-referenced the first number in the comma-list > (fugids) with the groups from f_groups. So it returned 2 group names. > > Sorry for the *MASSIVELY* and overtly-complicated message. I hope it > a) explains my problem and b) someone can decode it! > > PS incidentally, I could do this quite easily in PHP...using loops, > and querying on each number in the lists. But this is obviously > massively ineffecient - up to 40 queries for just one simple request! > -- > Will > -- The Corridor of Uncertainty -- > -- http://www.cricket.mailliw.com/ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > Hi Will, I think your're in a situtation like the example i give. The problem is work with a join-like between int and varchar. The idea is to split the ugid to a list of integers. mysql> select * from groups; +--+ | gid | +--+ |1 | |2 | |3 | | 15 | |9 | +--+ 5 rows in set (0.00 sec) mysql> select * from groupsList; +-+ | ugid| +-+ | 1,2,3,4 | | 2,5,6 | | 3,4,5 | | 7,4,9 | +-+ 4 rows in set (0.00 sec) mysql> SELECT * -> FROM groups -> WHERE gid -> IN (select ugid from groupsList); +--+ | gid | +--+ |1 | |2 | |3 | +--+ 3 rows in set (0.00 sec) You can see that 15 is not found, but 9 also. This is because gid is an int, and ugid is (certainly like yours) a varchar. what i thought to di is transforming the list of varchar to an array in which mysql must look for the gids. I then write you this query which should give you the idea, and i think the solution : mysql> select gid,ugid,FIND_IN_SET(gid,ugid) from groups,groupsList -> where FIND_IN_SET(gid,ugid) > 0; +--+-+---+ | gid | ugid| FIND_IN_SET(gid,ugid) | +--+-+---+ |1 | 1,2,3,4 | 1 | |2 | 1,2,3,4 | 2 | | 3 | 1,2,3,4 | 3 | |2 | 2,5,6 | 1 | |3 | 3,4,5 | 1 | |9 | 7,4,9 | 3 | +--+-+---+ 6 rows in set (0.00 sec) Et voilà. Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup and Maintenance Strategies
Selon James Tu <[EMAIL PROTECTED]>: > What have people done in the past regarding backup strategies? > > Is it adequate enough to rely on filesystem backups for mysql? Basically > such that we can restore MySQL to the last filesystem backup. Is there a > reason not to do this? > > I don't have any mission critical data and data that is lost since the last > backup is acceptable. > > -James > Hi, Filesystem backup for Mysql is similar to the one for all other RDBMS. You can't do it online, otherwise your database will not be coherent. the checkpoint times must be the same for all the datafiles. SO YOU CAN DO IT OFFLINE. you can also do a filesystem backup ONLINE if the RDBMs can put thetablespace sin Backup status. This is not done in pre-4 release of mysql. That's why replication master-slave is a backup solution. You can see innobackup which can do online backup since it can manage transactions during backup operation. If your data are not crtical (as you say), the database availability is not also. You can stop it and take an offline filesystem backup. Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why donn't work mysql_real_connect
Hi,
I don't use C APPI, but found this in docs for you :
*** connect sample :
MYSQL mysql;
mysql_init(&mysql);
mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"your_prog_name");
if (!mysql_real_connect(&mysql,"host","user","passwd","database",0,NULL,0))
{
fprintf(stderr, "Failed to connect to database: Error: %s\n",
mysql_error(&mysql));
}
They give port, unix_socket, client_flag the values 0,NULL,0. Try this.
All the rest is at http://dev.mysql.com/doc/mysql/en/mysql-real-connect.html
and http://dev.mysql.com/doc/mysql/en/c-api-functions.html
Mathias
Selon mm <[EMAIL PROTECTED]>:
> Dear friends,
> This is my first cpp module working with mySQL.
> It looks like the error is located in
> MYSQL *m=mysql_real_connect(myDB, host, user, passwd, ...
> There is a way to find out step by step which parameters is wrong?
> I am working with Fedora core 3
> and MySQL 3.23.58
> Thanks,
> MT
>
> ++ CPP module +++
> #include "/usr/include/mysql/mysql.h"
>
>
> int modulMySQL() {
> printf("modulMySQL 01\n"); fflush(stdout);
> // MYSQL *mysql_init(MYSQL *mysql)
> // ===
> MYSQL *myDB;
> MYSQL *mysql_init(myDB);
> printf("modulMySQL 02\n"); fflush(stdout);
>
>
> // MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char
> // *user, const char *passwd, const char *db, unsigned int port,
> // const char *unix_socket, unsigned long client_flag)
> //
> char *host=NULL, user[]="myname", passwd[]="mypass";
> char db[]="ro";
>
>
> unsigned int port=3306;
> const char *unix_socket=NULL;
> unsigned long client_flag=0;
> MYSQL *m=mysql_real_connect(myDB, host, user, passwd,
> db, port, unix_socket, client_flag);
>
> printf("modulMySQL 03\n"); fflush(stdout);
>
> // int mysql_ping(MYSQL *mysql)
> // ===
> int p=mysql_ping(myDB);
> printf("Return Values - Zero if the server is alive.p=[%i]\n", p);
>
> }
>
> + Compilation & Execution +++
> CFG=/usr/lib/mysql/mysql_config
> sh -c "gcc -o myC `$CFG --cflags` -lstdc++ 01.cpp `$CFG --libs`"
> ./myC > 01_out.txt
>
> ++ 01_out.txt ++
> modulMySQL 01
> modulMySQL 02
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
Hope that helps
:o)
Mathias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with mysqldump when there is a merge table
Hi, Have you tried '\\' ? Mathias Selon nyem <[EMAIL PROTECTED]>: > Hello, > > I have a database (on Win2k) with merge table. Mysqldump output includes > some path information on the merged tables such as DATA DIRECTORY and > INDEX DIRECTORY > > DROP TABLE IF EXISTS `rptpricing1996`; > CREATE TABLE `rptpricing1996` ( > `PricingId` int(11) NOT NULL default '0', > `commodity` char(22) NOT NULL default '', > `variables` char(7) NOT NULL default '', > `PricingDt` date NOT NULL default '-00-00', > `PricingHighPrice` decimal(12,2) default NULL, > `PricingLowPrice` decimal(12,2) default NULL, > `PricingAvgPrice` decimal(12,2) default NULL, > PRIMARY KEY (`PricingId`), > KEY `commodityDate` (`commodity`,`PricingDt`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 > DATA DIRECTORY='C:\SDVI\DB\mysql\data\dbsdvi\' > INDEX DIRECTORY='C:\SDVI\DB\mysql\data\dbsdvi\'; > > > When trying to load the database back (mysql dbsdvi < dbsdvi.dump) > the path information produces errors as mysql treats the \ character as > an escaped character. > > Removing the path information eliminates the error, but how do I tell > mysqldump not to output the path information? > > regards, > nyem > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do you think about PostgreSQL and mysql?
Hi Stone, if you ask me which is bettet your car or mine, i'll answer mine. Your question can't be answered here because it's not technical but political. Better close this thread. Mathias Selon "stone.wang" <[EMAIL PROTECTED]>: > How do you think about PostgreSQL and mysql? want to know which is good? How > to choose database for the web? Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Prevalidating queries?
Hi andy, Before starting your migration maake sur to stop mysql and copy all the datadir to another backup dir (just like directories). About single transaction, this will be difficult since you manage myisam tables. So what we can think to is : 1. execute one query 2. check log 3. if OK, continue with query n+1 4. else rexecute query n <--- But here data can become incohrent. A workaround seems to me to alter your table to innodb engine just for the upgrade. Then start transactions with n grouped queries. Then decide a commit or rollback. At the end of teh upgrade, you can come back to myisam. This is simplier. But you can also decide to take intermadiate backups when upgrading. Best Regards ---- Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Andy Pieters [mailto:[EMAIL PROTECTED] Sent: mardi 14 juin 2005 00:15 To: mysql@lists.mysql.com Subject: Prevalidating queries? Hi all As part of an automated patch system, I am facing the following problem: * A script will update the program from version x to version y * The script contains file actions, and database (mysql) actions * The actions are executed in order * For each action, a backup copy is created (if necessary) example if action is deletedir then the dir is moved to a temp directory * if an action (with status fail=abort) occurs, then the system must be restored to previous state. As far as file/directory operation is concerned, this is easy to implement, and that's why we are using backup copies. For the mysql part I don't really see how to do this. I am not using inodb but MyIsam tables. It is not that I need to know the result of the query in advance, only if mysql will accept it or will errormessage on the query. What I don't want is that query1, and 2 are already executed, and 3 fails because how could I do a rollback then? Anybody got any ideas? With kind regards Andy -- Registered Linux User Number 379093 -- --BEGIN GEEK CODE BLOCK- Version: 3.1 GAT/O/>E$ d-(---)>+ s:(+)>: a--(-)>? C$(+++) UL>$ P-(+)>++ L+++>$ E---(-)@ W+++>+++$ !N@ o? !K? W--(---) !O !M- V-- PS++(+++) PE--(-) Y+ PGP++(+++) t+(++) 5-- X++ R*(+)@ !tv b-() DI(+) D+(+++) G(+) e>$@ h++(*) r-->++ y--()> -- ---END GEEK CODE BLOCK-- -- Check out these few php utilities that I released under the GPL2 and that are meant for use with a php cli binary: http://www.vlaamse-kern.com/sas/ -- -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie Q - Re: Copying databases
http://dev.mysql.com/doc/mysql/en/backup.html It's better to backup with tools. You will be sure that tables are FULL-locked. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: samedi 30 avril 2005 00:30 To: mysql@lists.mysql.com Subject: Newbie Q - Re: Copying databases I am having to operating MySQL 4.0.18, I had heared that I could copy a db folder out of the mysql/data folder to the same folder on a different machine running its own local host server and that server would have a copy of the database. HOWEVER, o some occasions this seemed to work Ok, and on others I could use db, show tables etc, but on select I got a failure (which unfortunately I have not copied sorry) something to do with finding? <tablename>.innodb. Now as far as I know both servers were at the same version and both had innodb enabled and I THNK I quit both clients before copying. Is this a valid way to copy a db or only sometimes. Should I really always use mysqldump. Although I am suspecting given the amount of data this may make for a v large sql file? Can some-one advise this newbie, thanks, Andrew H -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Seeking advice on currency type
Here are datatypes and sizes. The problem can be storage. http://dev.mysql.com/doc/mysql/en/storage-requirements.html Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Hassan Schroeder [mailto:[EMAIL PROTECTED] Sent: samedi 30 avril 2005 00:25 To: mysql@lists.mysql.com Subject: Re: Seeking advice on currency type Harald Fuchs wrote: >>I can't imagine any reason to use a type other than DECIMAL for a >>currency value. > > A reason could be performance. Storing cent values in an INT field is > more efficient. Are you saying that storing and/or retrieving a DECIMAL value takes appreciably more time than an INTEGER? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ONLY_FULL_GROUP_BY too strict on MySQL 4.1.11
Hi, MySQL is a very fast, multi-threaded, multi-user and robust SQL (Structured Query Language) database server. What's New in This Release: Functionality added or changed: . ONLY_FULL_GROUP_BY no longer is included in the ANSI composite SQL mode. (Bug #8510) . mysqld_safe will create the directory where the UNIX socket file is to be located if the directory does not exist. T... [ read more about MySQL >> ] http://www.softpedia.com/progDownload/MySQL-for-Windows-Download-2668.ht ml Best Regards -------- Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of Harald Fuchs Sent: samedi 30 avril 2005 00:00 To: mysql@lists.mysql.com Subject: ONLY_FULL_GROUP_BY too strict on MySQL 4.1.11 I'm using MySQL 4.1.11 with sql-mode=ONLY_FULL_GROUP_BY set in my.cnf. This disallows things like SELECT col1, col2, sum(col1) FROM tbl GROUP BY col1 as it should, and it allows SELECT col1, sum(col1) FROM tbl GROUP BY col1 but it also disallows SELECT col1, sum(col1) + 1 FROM tbl GROUP BY col1 which is perfectly legal SQL AFAIK. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: IN giving me a fit
Hi, This is just what you're looking for : select item.id, item.name from item,item_cat_rel where item.id =item_cat_rel.id and item_cat_rel.cat_id = 5; +--+--+ | id | name | +--+--+ | 5000 | Triple Cage Hook | +--+--+ 1 row in set (0.01 sec) Joisn are better than subqueries. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Scott Purcell [mailto:[EMAIL PROTECTED] Sent: vendredi 29 avril 2005 20:14 To: mysql@lists.mysql.com Subject: IN giving me a fit Hello, I am in the docs trying to use the IN (13.1.8.3. Subqueries with ANY, IN, and SOME). Version: mysql Ver12.21 distrib 4.0.15 Win95/Win98(i32) I have two tables that are InnoDB types and I am trying to do simple IN but it argues with my syntax. mysql> select id, name -> from item where id IN (select id from item_cat_rel where cat_id = 5); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp onds to your MySQL server version for the right syntax to use near 'select id fr om item_cat_rel where cat_id = 5)' at line 2 mysql> Why? Here are the tables. CREATE TABLE ITEM ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, manufacturer_id varchar(50), name varchar(255), ) TYPE=InnoDB; insert into ITEM (id, manufacturer_id, name, description, short_desc, height, width, diameter, pounds, price, discount, quantity) values (5000, '9.90151', 'Triple Cage Hook', 'Solid wrought iron, is sculpted by hand into twisted cage hooks to hold your coats, bathrobes, towels and hats.', 'Triple Cage Hook', 9, 18.5, 4.5, 6, 35.00, 5, 1); CREATE TABLE ITEM_CAT_REL ( id INT, cat_id INT NOT NULL, key(id), FOREIGN KEY (id) references ITEM(id) on DELETE CASCADE ) TYPE=InnoDB; INSERT INTO ITEM_CAT_REL (id, cat_id) values (5000, 5); INSERT INTO ITEM_CAT_REL (id, cat_id) values (5000, 6); Scott K Purcell | Developer | VERTIS | 555 Washington Ave. 4th Floor | St. Louis, MO 63101 | 314.588.0720 Ext:1320 | [EMAIL PROTECTED] | http://www.vertisinc.com Vertis is the premier provider of targeted advertising, media, and marketing services that drive consumers to marketers more effectively. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Pessimistic Record Locking
Hi, Try to have optimistic locking in the database server (row level locking a.k.a. innodb storage), et let your transactions managed by the server. Any line of code like "lock table" will generate a very bad web application performances. You can add connection pooling if you want to manage total number of users. Best Regards -------- Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Scott Klarenbach [mailto:[EMAIL PROTECTED] Sent: vendredi 29 avril 2005 20:28 To: My SQL Subject: Pessimistic Record Locking Hello, I'm using MySQL 5.0.3 Beta, and I'm hoping for some general tips/experience you guys may have had regarding optomistic vs pessimistic locking in a web app (PHP 5.0.3) I'm more of a windows programmer, and I've always implemented pessimistic over optomistic as it's much more professional and attractive to the end user. The problem as you know, is that web development makes pessimistic locking much more difficult, because of the user closing the browser, and a bunch of other factors I can't control. Question: which type of locking do you usually implement in your web apps, and do you do it at a DB level or in your application layer? Any thoughts on a custom locking scheme (ie, a lock table that is written to with a user id and record id and timestamp)? Other solutions/suggestions are greatly appreciated. Thanks in advance. Scott. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: order by "version number"
Hi, select a from versions order by substring_index(a,'.',-2); Best Regards -------- Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Stano Paska [mailto:[EMAIL PROTECTED] Sent: vendredi 29 avril 2005 08:21 To: mysql@lists.mysql.com Subject: order by "version number" Hi, in my table I have one varchar(20) column where I store version number. Version looks like: 1.1.2 1.2.1 1.10.3 It is possible order this column in natural order (1.2 before 1.10)? Stano. -- Stanislav Paška programátor, www skupina KIOS s.r.o. tel: 033 / 794 00 18 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: using if in select statement
Hi, you can continue playing. It's a true game :o) Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: James Black [mailto:[EMAIL PROTECTED] Sent: vendredi 29 avril 2005 02:20 To: 'mysql@lists.mysql.com ' Subject: re: using if in select statement -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 My boss and I were playing with using select statements, and we can actually execute subqueries as an option if the result is true or false. Is this expected behavior, or is it something that may be fixed in a revision, before I begin to depend on it being acceptable behavior. Thanx. - -- Corruptisima republica plurimae leges. [The more corrupt a republic, the more laws.] Tacitus from Annals III, 116AD Blogs: http://jamesruminations.blogspot.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.0 (MingW32) iD8DBQFCcX2xJ/zyYkX46joRAgiVAJ9rw9BRPuT164/4wpYlHJbdj+x1agCcCbKG fM7SPPMIo6QSWijniegUM9A= =wK54 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: No error / warning when data is truncated on insertion into mysql
Hi, I think you shoul dcatch the "show warnings" command cause in mysql client you see the number of warnings. Data are even truncated according to the limit of the type (tinyint, int ...). Example : mysql> create table toto(a tinyint,b char(5)); Query OK, 0 rows affected (0.06 sec) mysql> insert into toto values (500,'Long text'); Query OK, 1 row affected, 2 warnings (0.02 sec) It's said here that i have 2 warnings. mysql> show warnings -> ; +-+--+-- + | Level | Code | Message | +-+--+-- + | Warning | 1264 | Data truncated; out of range for column 'a' at row 1 | | Warning | 1265 | Data truncated for column 'b' at row 1 | +-+--+-- + 2 rows in set (0.00 sec) mysql> select * from toto; +--+--+ | a| b| +--+--+ | 127 | Long | < my 500 is also truncated +--+--+ 1 row in set (0.00 sec) Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Anoop kumar V [mailto:[EMAIL PROTECTED] Sent: vendredi 29 avril 2005 00:21 To: mysql@lists.mysql.com Subject: No error / warning when data is truncated on insertion into mysql I am using MySQL and SQL server with Tomcat. Our application writes into both databases (mysql and ms sql server) at once based on some data collected from an end user. Now if the end user enters more data (characters) than the column can hold, the data obviously gets truncated. But the surprising thing is that although MS SQL server sends a warning message to tomcat (seen on the tomcat console) that "data may have been truncated" - MySQL does not show any warning message (I would have expected an error actually) as the data in the column is not what the data was intended to be. (Actually MS SQL shows the error and does not even insert the data...) Does MySQL not care or maybe I need to activate some option in MySQL like verbose or stict checking etc... It just truncated and inserted the data with no warning / error or any hassle!! how can i force mysql to check for such inconsistencies and report?? -- Thanks and best regards, Anoop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql top 2 rows for each group
For your query, just a where clause :
mysql> select * from seqs where id <3;
+---++
| seqno | id |
+---++
| 00122 | 1 |
| 00123 | 1 |
| 00123 | 2 |
| 00336 | 1 |
| 00346 | 1 |
| 00349 | 1 |
| 00427 | 1 |
| 00427 | 2 |
+---++
8 rows in set (0.00 sec)
Best Regards
----
Mathias FATENE
Hope that helps
*This not an official mysql support answer
-Original Message-
From: mathias fatene [mailto:[EMAIL PROTECTED]
Sent: jeudi 28 avril 2005 22:52
To: 'Jay Blanchard'; 'Vivian Wang'; mysql@lists.mysql.com
Subject: RE: mysql top 2 rows for each group
Hi ,
The table must be myisam. Innodb refused my solution which is here :
Beatifull
auto_increment
mysql> create table seqs(seqno varchar(10) NOT NULL , id int
auto_increment, primary key (seqno,id)) engine=myisam;
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> insert into seqs(seqno) values('00122'),
->('00123'),
-> ('00123'),
-> ('00123'),
-> ('00336'),
-> ('00346'),
-> ('00349'),
-> ('00427'),
-> ('00427'),
->('00427');
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from seqs;
+---++
| seqno | id |
+---++
| 00122 | 1 |
| 00123 | 1 |
| 00123 | 2 |
| 00123 | 3 |
| 00336 | 1 |
| 00346 | 1 |
| 00349 | 1 |
| 00427 | 1 |
| 00427 | 2 |
| 00427 | 3 |
+---++
10 rows in set (0.00 sec)
- I like this type
of auto_increment
Best Regards
Mathias FATENE
Hope that helps
*This not an official mysql support answer
-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED]
Sent: jeudi 28 avril 2005 22:24
To: Vivian Wang; mysql@lists.mysql.com
Subject: RE: mysql top 2 rows for each group
[snip]
I have question about how I can get top 2 rows for each group. like I
have table test
| seqno |
+---+
| 00122 |
| 00123 |
| 00123 |
| 00123 |
| 00336 |
| 00346 |
| 00349 |
| 00427 |
| 00427 |
| 00427 |
+---+--+
I like have
+---+--+
| seqno | item |
+---+--+
| 00122 | 1 |
| 00123 | 1 |
| 00123 | 2 |
| 00123 |3 |
| 00336 | 1 |
| 00346 | 1 |
| 00349 | 1 |
| 00427 | 1 |
| 00427 | 2 |
| 00427 |3 |
+---+--+
Then I can have select * from test where item <3 to find all top 2 rows.
[/snip]
I think you want ...
SELECT DISTINCT(seqno) FROM test ORDER BY seqno LIMIT 2
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql top 2 rows for each group
Hi ,
The table must be myisam. Innodb refused my solution which is here :
Beatifull
auto_increment
mysql> create table seqs(seqno varchar(10) NOT NULL , id int
auto_increment, primary key (seqno,id)) engine=myisam;
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> insert into seqs(seqno) values('00122'),
->('00123'),
-> ('00123'),
-> ('00123'),
-> ('00336'),
-> ('00346'),
-> ('00349'),
-> ('00427'),
-> ('00427'),
->('00427');
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from seqs;
+---++
| seqno | id |
+---++
| 00122 | 1 |
| 00123 | 1 |
| 00123 | 2 |
| 00123 | 3 |
| 00336 | 1 |
| 00346 | 1 |
| 00349 | 1 |
| 00427 | 1 |
| 00427 | 2 |
| 00427 | 3 |
+---++
10 rows in set (0.00 sec)
- I like this type
of auto_increment
Best Regards
Mathias FATENE
Hope that helps
*This not an official mysql support answer
-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED]
Sent: jeudi 28 avril 2005 22:24
To: Vivian Wang; mysql@lists.mysql.com
Subject: RE: mysql top 2 rows for each group
[snip]
I have question about how I can get top 2 rows for each group. like I
have table test
| seqno |
+---+
| 00122 |
| 00123 |
| 00123 |
| 00123 |
| 00336 |
| 00346 |
| 00349 |
| 00427 |
| 00427 |
| 00427 |
+---+--+
I like have
+---+--+
| seqno | item |
+---+--+
| 00122 | 1 |
| 00123 | 1 |
| 00123 | 2 |
| 00123 |3 |
| 00336 | 1 |
| 00346 | 1 |
| 00349 | 1 |
| 00427 | 1 |
| 00427 | 2 |
| 00427 |3 |
+---+--+
Then I can have select * from test where item <3 to find all top 2 rows.
[/snip]
I think you want ...
SELECT DISTINCT(seqno) FROM test ORDER BY seqno LIMIT 2
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Does anyone have experience?
Hi, Had you read http://dev.mysql.com/doc/mysql/en/odbc-connector.html One can't see the mysql ODBC driver in your snapshot. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: jeudi 28 avril 2005 21:02 To: Berman, Mikhail Cc: mysql@lists.mysql.com Subject: RE: Does anyone have experience? Thank you for being so patient with me. What options does SigmaPlot give you when selecting an ODBC data source? Have you verified that you are either using the default settings (as you defined when you set up the connection) or that you are using the same credentials you used to test your ODBC connection? Looking at this screen shot (http://www.systat.com/products/SigmaPlot/productinfo/pop_nf_odimp.html) I think the ODBC datasource interface gives you the choices to use a DSN you already created or to make another one from scratch. If selecting an already-tested and working DSN from this list continues to fail, then I think this is an issue you need to take up with the manufacturer of SigmaPlot. After further research I found out the using ODBC is *new* to v9.0. Some new features still have some bugs to work out and your problems may be caused by one of those. As a workaround, you may be able to use Access or Excel as crutches to get at your MySQL data then get the data from one of them into SigmaPlot. However, if you can use your MySQL server and you can use an ODBC connection with another program to get at your MySQL data then it seems very likely to me that the problem is going to be in SigmaPlot. Have you tried their online forums or their "contact a technician" links? (http://www.systat.com/products/SigmaPlot/resources/?sec=1019) Again, thank you for your patience and I am very sorry I couldn't be more helpful, Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 04/28/2005 02:13:29 PM: > See inserts below > > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Thursday, April 28, 2005 12:36 PM > To: Berman, Mikhail > Cc: mysql@lists.mysql.com > Subject: RE: Does anyone have experience? > > > > OK, I am still confused. Let's run down the list of what's working and > what isn't: > > a) In the ODBC manager, create a System DSN and click on the TEST > CONNECTION button. What happens? > > ODBC manager returns - "Success. Connection was made" > > b) In SigmaPlot, tell the software to use the connection you just > created and tested. What happens? > > SigmaPlot returns - "Cannot connect to data source" > > If we can't get the ODBC manager to connect, nothing else using that > DSN can possibly connect. The fact that your MySQL database is in a > different machine running a different OS is not important. What is > important is that you are using a user account to make your connection > (a MySQL user account, NOT an OS user account) that has privileges and > that you can connect to the server and authenticate with that > account's credentials. > > If for some reason there is a firewall between your XP machine and > your MySQL server, that can also cause a failure to connect. Can you > ping the server from your XP machine? Can you telnet from your XP > machine to your MySQL server on port 3660? (You cannot create a > normal telnet session with a MySQL server. However, if you can see the > version of the server surrounded by several lines of gibberish, this > telnet test was successful. ) > > I work freely with MySQL servers from XP machine as a part of my daily > routine. > > The reason I keep going back to ODBC is that I want to make absolutely > certain that this is not the weak link. If all ODBC tests are good > then we need to look at the connection between SigmaPlot and ODBC as > the problem. > > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > > > "Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 04/28/2005 11:59:27 > AM: > > > Hi, > > > > I do use Data Sources(ODBC) manager to create DSNs. > > > > Either User or System DSN failed to connect from SysPlot to UNIX > > databases, with the same error message "Cannot connect to data source" > > > > > > > Sorry I was not precise in description, I have mentioned MS-Access > > vs. Excel only to raised a point that ODBC should work similarly > > with both tools, but it does not. > > I am aware of row limitation for Excel so my test are ran against > > the tables th
RE: host info
That's it:o) Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Stanton, Brian [mailto:[EMAIL PROTECTED] Sent: mercredi 27 avril 2005 23:12 To: 'mysql@lists.mysql.com' Subject: RE: host info Along those lines, you could use "show variables like 'pid_file'" if the user needing to know the hostname has privileges for this. Thanks for the idea! Thanks, Brian Stanton -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 3:42 PM To: Stanton, Brian Cc: 'mysql@lists.mysql.com' Subject: RE: host info Hi all, Mysql server knows the OS server as localhost. the hostname you see in status is the OS server from which you connect (the client one), since it's defined in the grant. The only method i can see is : ls /*.pid its hostname.pid You can do it also with *.err Mathias Selon "Stanton, Brian" <[EMAIL PROTECTED]>: > The 'Connection' output from the 'status' command is actually what I > was looking for. However, most likely it will be a jdbc connection to > mysql, not the mysql client, so I'll have to see if it works that way > or not. > > Thanks, > Brian Stanton > > -Original Message- > From: Eamon Daly [mailto:[EMAIL PROTECTED] > Sent: Wednesday, April 27, 2005 2:04 PM > To: Stanton, Brian; mysql@lists.mysql.com > Subject: Re: host info > > I don't know if it's possible in MySQL. > > That said, in the mysql client, you can type '\s' for 'status'. Look > for 'Current user' in the output. > > > Eamon Daly > > > > - Original Message - > From: "Stanton, Brian" <[EMAIL PROTECTED]> > To: > Sent: Wednesday, April 27, 2005 10:03 AM > Subject: host info > > > > Does anyone know a function that will return the hostname of the > > mysql server you are connecting to? > > > > > > > > Just as: > > > > mysql> select database(); > > > > returns the database you're connected to, I need to display the host > > I'm connected to. > > > > > > > > Similar to the oracle statement: select host_name from v$instance; > > > > > > > > Thanks, > > > > Brian Stanton > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query question
Hi, If i understand : select month(entryDate) as monthPart, if (amount is nul,'',day(entryDate) ) as dayPart, amount from raindata order by dayPart, monthPart Best Regards -------- Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: mercredi 27 avril 2005 22:24 To: mysql@lists.mysql.com Subject: query question I have a table that the important parts look something like: keynum int, entryDate datetime, amount varchar(10) What I want to do is a query that gets me every day of the year and just has null values for the days that don't have anything in the amount column. Is something like that possible with sql? In fact, what I would really like is: select month(entryDate) as monthPart, day(entryDate) as dayPart, amount from raindata order by dayPart, monthPart just with the whole year filled in. it will make my later code simplier if I can not have to test for values as much. --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
Yes ten years and forgot mysql certified. I can offer i-am-a-dummy to you if you lack. I've never imagined find so bad people on the list. But i'll write to the moderator to see who is on. But i'm pleased to help people wihout naz mentality than yours. Best Regards -------- Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: mardi 26 avril 2005 12:29 To: mysql@lists.mysql.com Subject: Re: Query question > If my englsih is so bad, i'll try to explain and stop this thread now. That's not what was being said. > I'm not teaching, i'm answering questions. If someone wants to read > docs, he (she) doesn't ask a question on the list. So if i answer, i > answer the question, just the question. > > You want to know my level of knowledgne, 10 years, oracle, sybase, > sqlserver, db2. I can help for migration from or to... I said don't > use joins for the query given in the example or queries using just the > joining columns from the first table. Normal forms is bla bla here > ... 10 yrs? Time to read a book then. -- Martijn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Python related MySQL question
Look at db.use_result() and db.store_result() here :
http://www.birgerblixt.com/doc/packages/python-mysql/MySQLdb-2.html#ss2.
2
Best Regards
Mathias FATENE
Hope that helps
*This not an official mysql support answer
-Original Message-
From: Smelly Socks [mailto:[EMAIL PROTECTED]
Sent: mardi 26 avril 2005 06:20
To: mysql@lists.mysql.com
Subject: Re: Python related MySQL question
Hi!
I am porting a function library app I wrote in PHP to Python. At work
they only use Python. I've researched how to connect to a MySql
database using Python, and how to retrieve rows. However, I am
wondering how to do the
following:
I can do this in Python => $da=MYSQL_QUERY("select * from prefs where
user_name='$user_name' "); I can do this in Python =>
$peek=mysql_fetch_array($da);
I cannot do the following:
$title =$peek[4]; //title window
$logic =$peek[5]; //logic window
Can anyone shed light on how to get the pieces of the array and stick
them into variables?
Thanks very much!
Cheers!
-Warren
- Original Message -
From: "Spenser" <[EMAIL PROTECTED]>
To: "David Bailey" <[EMAIL PROTECTED]>
Cc:
Sent: Thursday, April 14, 2005 12:27 PM
Subject: Re: book advice
> Check out "MySQL Tutorial" by Luke Welling (MySQL Press). It's easy
> to understand and not overwhelming.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Index help ?
I think the second can be better (more different values). But it contains almost the same data than the table. Try : explain Select machine,count(*) from syslog WHERE date1 > (NOW() - INTERVAL 24 hour) AND message LIKE 'sshd%' GROUP BY machine; But an index with(date1, message, machine) sould be sufficient. Best Regards ---- Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Michael Gale [mailto:[EMAIL PROTECTED] Sent: mardi 26 avril 2005 05:49 To: mysql@lists.mysql.com Subject: Index help ? Hello, I have the following table setup: IDhostnamefacilityprioritydatemessage ID is auto incrementing. This is used to store all of the syslog messages, currently there are over 7 million: The following query takes forever: Select machine,count(*) from syslog WHERE date1 > (NOW() - INTERVAL 24 hour) AND message LIKE 'sshd%' GROUP BY machine; I have created the following indexs but when I use Explain it says that the query has to search all the rows: datehostfacility 1 date1 A 352489 datehostfacility 2 machine A 1409956 datehostfacility 3 facility A 1409956 datemesghost 1 date1 A 640889 datemesghost 2 message(15) A 7049783 datemesghost 3 machine A 7049783 datemesghost 4 facility A 7049783 datemesghost 5 priority A 7049783 What would the proper index be ? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Crosstab in Mysql
Hi, What do you obtain with : Select FK_partic, Sum(IF(insumo_or = "Animal1", cantidad_or, 0) ) AS "Animal1", Sum(IF (insumo_or = "Animal2", cantidad_or, 0) ) AS "Animal2", Sum(IF (insumo_or = "Animal3", cantidad_or, 0) ) AS "Animal3", Sum(IF (insumo_or = "Animal4", cantidad_or, 0) ) AS "Animal4", Sum(IF (insumo_or = "Animal5", cantidad_or, 0) ) AS "Animal5", Sum(IF (insumo_or = "Animal6", cantidad_or, 0) ) AS "Animal6" FROM tbl_ISv2CROriginal Group by FK_partic ? Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Alvaro Cobo [mailto:[EMAIL PROTECTED] Sent: mardi 26 avril 2005 04:38 To: mysql@lists.mysql.com Subject: Crosstab in Mysql Hi guys: I am quite new in SQL and I need to build a crosstab based in two tables using Mysql and PHP, but it is becoming quite dificult. I've got the next query, but it keeps giving the next error: "#1241 - Operand should contain 1 column(s)" /*GENERAL EXPLANATION OF THE QUERY I work in a project to give animals to farmers: I have two tables: tbl_ISv2CRfamilia with the families which are going to receive animals. (PK_partic, int(11), Autonumbering ID (PK); FK_IS; varchar(255); Foreign key which conects to the project table nombre_partic, varchar(255), Name of the family OB_familia, varchar(255), community of the family) tbl_ISv2CROriginal with the animales they actually have received. (FK_partic, int(11), Foreign key which conects to the family ID insumo_or, varchar(255), Animal given cantidad_or, int(11), number of animals given of this specie) And I need to have a table like this: Family, animal1, animal2, animal3, ..., animaln John Smith 34013... 0 */ The query and subquiery is as follows. SELECT tbl_ISv2CRfamilia.PK_partic, tbl_ISv2CRfamilia.FK_IS, tbl_ISv2CRfamilia.OB_familia, (SELECT Sum( IF ( insumo_or = "Animal1", cantidad_or, 0 ) ) AS "Animal1", Sum( IF ( insumo_or = "Animal2", cantidad_or, 0 ) ) AS "Animal2", Sum( IF ( insumo_or = "Animal3", cantidad_or, 0 ) ) AS "Animal3", Sum( IF ( insumo_or = "Animal4", cantidad_or, 0 ) ) AS "Animal4", Sum( IF ( insumo_or = "Animal5", cantidad_or, 0 ) ) AS "Animal5", Sum( IF ( insumo_or = "Animal6", cantidad_or, 0 ) ) AS "Animal6" FROM tbl_ISv2CROriginal GROUP BY FK_partic ) FROM tbl_ISv2CRfamilia INNER JOIN tbl_ISv2CROriginal ON tbl_ISv2CRfamilia.PK_partic = tbl_ISv2CROriginal.FK_partic GROUP BY FK_partic What is wrong with that? I have tried everything, and no solution. Thanks in advance. Alvaro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: database migration puzzle.
Hi, I hope that this link will help http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Kenneth Wagner [mailto:[EMAIL PROTECTED] Sent: mardi 26 avril 2005 01:03 To: mysql@lists.mysql.com Subject: database migration puzzle. Hi all, I have removed mysql 4_0_20d and installed 4.1. My puzzle is this: 1. I have prior databases in 4.0 (intact data directory with InnoDB files *.idb, etc.) data directory with sub directories. 2. I want to bring in some of the databases to the new 4.1 version. The 4.0 databases have not been dumped, unloaded or exported. How to go about it? Many thanks. Ken Wagner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
Hi, If my englsih is so bad, i'll try to explain and stop this thread now. I'm not teaching, i'm answering questions. If someone wants to read docs, he (she) doesn't ask a question on the list. So if i answer, i answer the question, just the question. You want to know my level of knowledgne, 10 years, oracle, sybase, sqlserver, db2. I can help for migration from or to... I said don't use joins for the query given in the example or queries using just the joining columns from the first table. Normal forms is bla bla here ... See also about covering indexes. That can help. This is the query given by Jeff : >>> So, if record 100 in table1 links to 5 corresponding records in table2, >>> I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' The only clause is about table2.parentid = 100 or child.id = 100. that's the same. All the other clauses are on table2. This is the exampel given by Jeff. If you want absolutely LEFT outer joins for that (without other columns from table1), i say you good luck, this can (also) do the trick. That's all. - If you give me real examples, i can help you to give you to find the right (if i can) query plan. Tuning is my first target when i think a query. I never suggest nested loops, but relationnal algebra. I'm not supposed speeking to students but DBAs, for specific question. Sorry if i run up against your sensitivity, but we are not speaking about the same thing. And please if you have to criticize or complete an answer, it's your right. The list is for that. If you want to speak to me as your student, this is enough. I never did it when i was teacher 11 years ago. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 23:02 To: mathias fatene Cc: 'Jeff McKeon'; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Query question "mathias fatene" <[EMAIL PROTECTED]> wrote on 04/25/2005 04:24:42 PM: > Hi, > Im sorry to disappoint you but this is an anti-performance solution. > Use joins rathers than subqueries, and don't use joins if you can (all > data in the mother table). > > Imagine that table2 has 30.000.000 records, and not good indexes. you > can wait for your answer a long time. > > Best Regards > > Mathias FATENE > > Hope that helps > *This not an official mysql support answer > Mathias, I do appreciate your energy and willingness to contribute to the list. I am not affiliated with MySQL or any of its subsidiaries and I have no special privileges to police what happens on this list. I am a fellow contributor just as you. With that said, I feel that I must seriously question your level of experience and ability to form useful responses. When you say "and don't use joins if you can (all data in the mother table)", It seems to me that you are proposing that in order to eliminate JOINs in queries that all data should be flattened into one single table. Not only is this incorrect advice but it undermines the many reasons for using a relational database system (RDBMS) in the first place. I would love to compare the performance of a properly normalized and indexed relational data structure against a single "flat" table for all but the most trivial of data sets. The nomalized data will not only take up less room on the disk but it will perform extremely well (especially for larger data sets). The single-table model you proposed will not scale to more than a few hundred thousand rows before the table's size becomes a bottleneck. Some queries will take "a long time" to finish against 30 million row tables, even with good indexes on them. Your extreme counter example was a non-starter. The original poster acknowledges that they are new (no offence intended) and I feel that your posts were hardly helpful at best and most likely counter-productive. Please, take the time to read your ansers from the perspective of the person you are responding to. Try to keep in mind not only their language skills (as this is a multi-national list) but their experience level and even sometimes their age (we have many students looking for help on here and some of them are still teenagers). Please be more accurate, thoughtful, and descriptive the next time you post, OK? With greatest humility, Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
Hi, Im sorry to disappoint you but this is an anti-performance solution. Use joins rathers than subqueries, and don't use joins if you can (all data in the mother table). Imagine that table2 has 30.000.000 records, and not good indexes. you can wait for your answer a long time. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 22:17 To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Then do it with 2 queries, SELECT @d := MAX( datestamp ) FROM table2 WHERE parentID = X; SELECT * FROM table2 WHERE parentID = X AND [EMAIL PROTECTED]; PB - Jeff McKeon wrote: Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
RE: Query question
Here we are Shawn, With empty tables : +++---+--+---+--+--- --+--+--+-+ | id | select_type| table | type | possible_keys | key | key_len | ref | rows | Extra | +++---+--+---+--+--- --+--+--+-+ | 1 | PRIMARY| a | ALL | NULL | NULL | NULL | NULL |0 | Using where | | 2 | DEPENDENT SUBQUERY | b | ALL | NULL | NULL | NULL | NULL |0 | Using where | +++---+--+---+--+--- --+--+--+-+ 2 rows in set (0.00 sec) mysql> mysql> explain select parentid,max(datestamp) from table2 -> group by parentid; ++-++--+---+--+-+--- ---+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++--+---+--+-+--- ---+--+-+ | 1 | SIMPLE | table2 | ALL | NULL | NULL |NULL | NULL |0 | Using temporary; Using filesort | ++-++--+---+--+-+--- ---+--+-+ One or two table scans ? Best Regards ---- Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 22:01 To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: >I have a table that contains records that link back to a main talbe in >a many to one configuration linked by table1.id = table2.parentid > >Table1 (one) >Table2 (many) > >I want to pull the latest records from table2 for each record in table1 >where certain criteria applie. > >So, if record 100 in table1 links to 5 corresponding records in table2, >I want to pull the latest record from table2 where table2.parentid = >100 and table2.user not like 'john' > >There is a datestamp field in table2. > >I just can't figure out how to do this. > >Thanks, > >Jeff > > > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
Hi, Why my answer doesn't answer his question. Did you heared about his comment. Let him do it. If you're confused, i can explain more one-to-many relashionships. If you think about joins and want absolutely add them, this is the error generating performance problems asked along all RDMBS, especially with mysql (DBMS till now). Best Regards -------- Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 21:44 To: mathias fatene Cc: 'Jeff McKeon'; mysql@lists.mysql.com Subject: RE: Query question "mathias fatene" <[EMAIL PROTECTED]> wrote on 04/25/2005 03:19:33 PM: > Hi, > You can do something like that : > > > mysql> select * from son; > +--+ > | a| > +--+ > |1 | > |2 | > |3 | > +--+ > 3 rows in set (0.02 sec) > mysql> select * from mother; > +--+--+ > | a| b| > +--+--+ > |1 | a| > |1 | b| > |2 | a| > |2 | c| > |3 | a| > |3 | b| > |3 | c| > |3 | d| > +--+--+ > 8 rows in set (0.00 sec) > > mysql> select a,max(b) from mother > -> group by a; > +--++ > | a| max(b) | > +--++ > |1 | b | > |2 | c | > |3 | d | > +--++ > 3 rows in set (0.00 sec) > > The max will be used with your datetime column. The "son" table can > not be used, or joined to the mother. > > > Best Regards > > Mathias FATENE > > Hope that helps > *This not an official mysql support answer > > > > -Original Message- > From: Jeff McKeon [mailto:[EMAIL PROTECTED] > Sent: lundi 25 avril 2005 21:01 > To: mysql@lists.mysql.com > Subject: Query question > > > I have a table that contains records that link back to a main talbe in > a many to one configuration linked by table1.id = table2.parentid > > Table1 (one) > Table2 (many) > > I want to pull the latest records from table2 for each record in > table1 where certain criteria applie. > > So, if record 100 in table1 links to 5 corresponding records in > table2, I want to pull the latest record from table2 where > table2.parentid = 100 and table2.user not like 'john' > > There is a datestamp field in table2. > > I just can't figure out how to do this. > > Thanks, > > Jeff > I think I am decent at what I do and that confused even me. I am totally baffled at what SQL concept you were trying to illustrate. How did you _help_ the OP? The question that started this thread is an example of a common class of SQL problems and several solutions exist. Your "solution" neither answered his query nor was it explained to the point that made it comprehendable. Please, please try to be less confusing (especially when responding to newbies). Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
Hi, You can do something like that : mysql> select * from son; +--+ | a| +--+ |1 | |2 | |3 | +--+ 3 rows in set (0.02 sec) mysql> select * from mother; +--+--+ | a| b| +--+--+ |1 | a| |1 | b| |2 | a| |2 | c| |3 | a| |3 | b| |3 | c| |3 | d| +--+--+ 8 rows in set (0.00 sec) mysql> select a,max(b) from mother -> group by a; +--++ | a| max(b) | +--++ |1 | b | |2 | c | |3 | d | +--++ 3 rows in set (0.00 sec) The max will be used with your datetime column. The "son" table can not be used, or joined to the mother. Best Regards ---- Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Jeff McKeon [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 21:01 To: mysql@lists.mysql.com Subject: Query question I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Converting to InnoDB?
Sorry, Alter table toto ENGINE=innodb. You don't must, you can. You can also have differents storage ENGINES in the same mysql database. With innodb, you will earn ROW level locking. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: mathias fatene [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 19:33 To: 'Carl Riches'; 'mysql@lists.mysql.com' Subject: RE: Converting to InnoDB? Yes, but your myIsam Tables stay myisam ones. After restarting, you must change them to innodb by : Alter table toto storage=innodb. For new tables, they will have innodb storage. Best Regards ---- Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Carl Riches [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 19:26 To: mysql@lists.mysql.com Cc: Carl Riches Subject: Converting to InnoDB? We are running MySQL on Red Hat Enterprise Linux 3, using the Red Hat- supplied RPM file mysql-server-3.23.58-2.3. Our current MySQL configuration has MyISAM as the default database file type. I would like to change this such that InnoDB is the default. My understanding of the documentation says that, after changing the configuration file and restarting the MySQL server, there will be no problems using the existing MyISAM databases. Is that correct? Thanks, Carl G. Riches Software Engineer Department of Mathematics Box 354350 voice: 206-543-5082 or 206-616-3636 University of Washingtonfax: 206-543-0397 Seattle, WA 98195-4350 internet: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Converting to InnoDB?
Yes, but your myIsam Tables stay myisam ones. After restarting, you must change them to innodb by : Alter table toto storage=innodb. For new tables, they will have innodb storage. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Carl Riches [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 19:26 To: mysql@lists.mysql.com Cc: Carl Riches Subject: Converting to InnoDB? We are running MySQL on Red Hat Enterprise Linux 3, using the Red Hat- supplied RPM file mysql-server-3.23.58-2.3. Our current MySQL configuration has MyISAM as the default database file type. I would like to change this such that InnoDB is the default. My understanding of the documentation says that, after changing the configuration file and restarting the MySQL server, there will be no problems using the existing MyISAM databases. Is that correct? Thanks, Carl G. Riches Software Engineer Department of Mathematics Box 354350 voice: 206-543-5082 or 206-616-3636 University of Washingtonfax: 206-543-0397 Seattle, WA 98195-4350 internet: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Ordering rows whit a select from where in ( exp )
Do that ,
SELECT field_name FROM meta WHERE id ='13'
Union
SELECT field_name FROM meta WHERE id ='11'
Union
SELECT field_name FROM meta WHERE id ='7'
Union
SELECT field_name FROM meta WHERE id ='8'
Union
SELECT field_name FROM meta WHERE id ='9'
Union
SELECT field_name FROM meta WHERE id ='10'
Union
SELECT field_name FROM meta WHERE id ='12'
Mathias
Best Regards
Mathias FATENE
Hope that helps
*This not an official mysql support answer
-Original Message-
From: Adrian [mailto:[EMAIL PROTECTED]
Sent: lundi 25 avril 2005 10:06
To: mysql@lists.mysql.com
Subject: Ordering rows whit a select from where in ( exp )
Hi everyone,
Here is my issue:
I have this Query : SELECT field_name FROM meta WHERE id
IN
('13','11','7','8','9','10','12')
I want the rows to be display in the same order as the in list
of ids.Any ideas? Should I use order by? Whit witch option ?
Thanks for your help.
Adrian.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: libCstd.so.1 not found while running mysql_install_db
Read 7xx Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: mathias fatene [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 10:18 To: 'Anirban Karmakar'; 'mysql@lists.mysql.com' Subject: RE: libCstd.so.1 not found while running mysql_install_db Do you have /cnem/server/bin/mysqld file ? Is it exec (6xx)? Best Regards ---- Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Anirban Karmakar [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 09:12 To: mysql@lists.mysql.com Subject: libCstd.so.1 not found while running mysql_install_db Hi, I installed mysql-standard-4.0.24-sun-solaris2.8-sparc on a Solaris 5.8 macine. However while i'm running the mysql_install_db script i'm getting the error ld.so.1: ./bin/mysqld: fatal: libCstd.so.1: open failed: No such file or directory Killed Installation of grant tables failed! I've my .my.cnf file as #mysql config file [client] port = 3306 socket= /tmp/mysql.sock [mysqld] port = 3306 socket= /tmp/mysql.sock user = mysqlc datadir = /cnem/data [mysql_server] basedir = /cnem/server [mysql.server] basedir = /cnem/server [mysqld_safe] err-log = /cnem/server/mysqld.log The same configuration worked on another Sun machine. Please suggest me how to fix it. Thanks Anirban -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: libCstd.so.1 not found while running mysql_install_db
Do you have /cnem/server/bin/mysqld file ? Is it exec (6xx)? Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Anirban Karmakar [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 09:12 To: mysql@lists.mysql.com Subject: libCstd.so.1 not found while running mysql_install_db Hi, I installed mysql-standard-4.0.24-sun-solaris2.8-sparc on a Solaris 5.8 macine. However while i'm running the mysql_install_db script i'm getting the error ld.so.1: ./bin/mysqld: fatal: libCstd.so.1: open failed: No such file or directory Killed Installation of grant tables failed! I've my .my.cnf file as #mysql config file [client] port = 3306 socket= /tmp/mysql.sock [mysqld] port = 3306 socket= /tmp/mysql.sock user = mysqlc datadir = /cnem/data [mysql_server] basedir = /cnem/server [mysql.server] basedir = /cnem/server [mysqld_safe] err-log = /cnem/server/mysqld.log The same configuration worked on another Sun machine. Please suggest me how to fix it. Thanks Anirban -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: joining six tables by mutual column
Hi, Select * from table1 T1, table2 T2, table3 T3, table4 T4, table5 T5, table6 T6 Where T1.col=T2.col and T2.col=T3.col and T3.col=T4.col and T4.col=T5.col and T5.col=T6.col and T1.col=T6.col [and col='val'] Doesn't this work ? Have you an example ? Best Regards -------- Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Schalk Neethling [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 00:52 To: mysql@lists.mysql.com Subject: joining six tables by mutual column Greetings everyone. Hope someone can give me some pointers here. I have six tables in the database and I need to JOIN them on a row that appears in all of the tables. How do I do this? I have so far done the normal 'cross-join' saying SELECT * FROM table1, table2, table3, table4, table5, table6 WHERE something = something; I have also added STRAIGHT_JOIN to force the order but, how do I JOIN six tables to/by one column? I have done some google searches as well as looked at MySQL 2nd edition by Paul DuBois, sorry if I missed something here Paul, and so far I have not found an answer. Any help or pointers will be appreciated. Thank you. -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Business.Solution.Developers emotionalize.conceptualize.visualize.realize Landlines Tel: +27125468436 Fax: +27125468436 Web email:[EMAIL PROTECTED] Global: www.volume4.com Messenger Yahoo!: v_olume4 AOL: v0lume4 MSN: [EMAIL PROTECTED] We support OpenSource Get Firefox!- The browser reloaded - http://www.mozilla.org/products/firefox/ This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication - is there a "server lag"?
Loo at : mysql> show master status; +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | binlog.03 | 79 | | | +---+--+--+--+ 1 row in set (0.02 sec) And show slave status; When reading from slave, data can be not synchronized. If you configured log-bin, you can use mysqlbinlog to read it. Best Regards ---- Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Fagyal Csongor [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 00:43 To: Mysql (E-mail) Subject: Replication - is there a "server lag"? Hi, I am new to replication so excuse me if my question is stupid. The manual recommends that a nice scenario to take advantage of replication in MySQL is to send all updating queries to the master server, and reading from the slave. I would like to use this setup (as usual, I have many more selects than inserts/updates) but I am a little concerned what happens if the slave is behind the master in updating its DB. Say I do like this: 1. update something set `a`=1 where c=d (using the master server) 2. update something set `a`=2 where c=d (using the master server) and then immediately 3. select `a` from something where c=d (using the slave) What if #3 fetches the value of `a` from the slave before `a`=2 takes place? Is it possible that I get `a`==1? Or does replication take care of that? Other than that: does anybody here have a Nagios script that checks if replication is running O.K.? :-) Thanks, - Csongor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: setting character sets "permanently"
2 other things :
1. what is your character set when you install the mysql server ?
2. what characater set you see with "show create database" ?
Best Regards
--------
Mathias FATENE
Hope that helps
*This not an official mysql support answer
-Original Message-----
From: mathias fatene [mailto:[EMAIL PROTECTED]
Sent: dimanche 24 avril 2005 22:59
To: 'mathias fatene'; 'Fagyal Csongor'
Cc: mysql@lists.mysql.com
Subject: RE: setting character sets "permanently"
And you can add all those variables to the ini file :
character_set_client=latin2 character_set_connection=latin2
character_set_database=latin2 character_set_results=latin2
character_set_server=latin2
Be sûr that OLD data in your database will not suffer (replication and
binlog for example). Export/import should be a good trick. But Latin2
seems more general that latin1.
Best Regards
Mathias FATENE
Hope that helps
*This not an official mysql support answer
-Original Message-
From: mathias fatene [mailto:[EMAIL PROTECTED]
Sent: dimanche 24 avril 2005 22:47
To: 'Fagyal Csongor'
Cc: mysql@lists.mysql.com
Subject: RE: setting character sets "permanently"
Hi,
Look at this :
mysql> show variables like 'character%';
+--+
-+
| Variable_name| Value
|
+--+
-+
| character_set_client | latin1
|
| character_set_connection | latin1
|
| character_set_database | latin1
|
| character_set_results| latin1
|
| character_set_server | latin1
|
| character_set_system | utf8
|
| character_sets_dir | C:\Program Files\MySQL\MySQL Server
4.1\share\charsets/ |
+--+
-+
I changed my.ini (.my.cnf) like this :
[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3306
log-bin = "C:/Program Files/MySQL/MySQL Server 4.1/Data/binlog"
#Path to installation directory. All paths are usually resolved relative
to this. basedir="C:/Program Files/MySQL/MySQL Server 4.1/"
#Path to the database root
datadir="C:/Program Files/MySQL/MySQL Server 4.1/Data/"
# The default character set that will be used when a new schema or table
is # created and no character set is defined ###
default-character-set=latin1 default-character-set=latin2
C:\Program Files\MySQL\MySQL Server 4.1\data>net stop mysql41 Le service
MySQL41 s'arrête. Le service MySQL41 a été arrêté.
C:\Program Files\MySQL\MySQL Server 4.1\data>net start mysql41 Le
service MySQL41 démarre. Le service MySQL41 a démarré.
mysql> show variables like 'character%';
+--+
-+
| Variable_name| Value
|
+--+
-+
| character_set_client | latin1
|
| character_set_connection | latin1
|
| character_set_database | latin1
|
| character_set_results| latin1
|
| character_set_server | latin2
|
| character_set_system | utf8
|
| character_sets_dir | C:\Program Files\MySQL\MySQL Server
4.1\share\charsets/ |
+--+
-+
That's all.
Mathias
-Original Message-
From: Fagyal Csongor [mailto:[EMAIL PROTECTED]
Sent: dimanche 24 avril 2005 22:33
To: mysql@lists.mysql.com
Subject: setting character sets "permanently"
HI,
I am using MySQL 4.1.11 on FC3, and I have trouble reading back latin2
characters. No wonder, as character_set_client, character_set_connection
and character_set_results are all set to latin1. The problem is that I
cannot set them to latin2 _permanently_, I mean every time I connect to
the database I have to issue either
$dbh->do("set collation_connection=latin2_general_ci");
$dbh->do("set character_set_client=latin2");
$dbh->do("set character_set_results=latin2");
OR
$dbh->do("SET NAMES 'latin2'");
which is basically the same.
Is there a way to tell the server that I want to use latin2 every time?
I mean something like: (in my.cnf):
please_always_use_this_character_set_or_i_go_crazy=latin2
or maybe
would_you_stop_doing_character_set_conversions_as_i_know_what_i_insert_j
ust_give_me_back_what_i_sent_to_you=1
?
I already set
character-set-server=latin2 collation-server=latin2_hungarian_ci
and they show up nicely in "show variables" (but this does not help me).
Also my database/tables/clumns are set to latin2 (both the character
set and the collaction).
Thank you,
- Csongor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EM
RE: setting character sets "permanently"
And you can add all those variables to the ini file :
character_set_client=latin2
character_set_connection=latin2
character_set_database=latin2
character_set_results=latin2
character_set_server=latin2
Be sûr that OLD data in your database will not suffer (replication and
binlog for example). Export/import should be a good trick.
But Latin2 seems more general that latin1.
Best Regards
Mathias FATENE
Hope that helps
*This not an official mysql support answer
-Original Message-
From: mathias fatene [mailto:[EMAIL PROTECTED]
Sent: dimanche 24 avril 2005 22:47
To: 'Fagyal Csongor'
Cc: mysql@lists.mysql.com
Subject: RE: setting character sets "permanently"
Hi,
Look at this :
mysql> show variables like 'character%';
+--+
-+
| Variable_name| Value
|
+--+
-+
| character_set_client | latin1
|
| character_set_connection | latin1
|
| character_set_database | latin1
|
| character_set_results| latin1
|
| character_set_server | latin1
|
| character_set_system | utf8
|
| character_sets_dir | C:\Program Files\MySQL\MySQL Server
4.1\share\charsets/ |
+--+
-+
I changed my.ini (.my.cnf) like this :
[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3306
log-bin = "C:/Program Files/MySQL/MySQL Server 4.1/Data/binlog"
#Path to installation directory. All paths are usually resolved relative
to this. basedir="C:/Program Files/MySQL/MySQL Server 4.1/"
#Path to the database root
datadir="C:/Program Files/MySQL/MySQL Server 4.1/Data/"
# The default character set that will be used when a new schema or table
is # created and no character set is defined ###
default-character-set=latin1 default-character-set=latin2
C:\Program Files\MySQL\MySQL Server 4.1\data>net stop mysql41 Le service
MySQL41 s'arrête. Le service MySQL41 a été arrêté.
C:\Program Files\MySQL\MySQL Server 4.1\data>net start mysql41 Le
service MySQL41 démarre. Le service MySQL41 a démarré.
mysql> show variables like 'character%';
+--+
-+
| Variable_name| Value
|
+--+
-+
| character_set_client | latin1
|
| character_set_connection | latin1
|
| character_set_database | latin1
|
| character_set_results| latin1
|
| character_set_server | latin2
|
| character_set_system | utf8
|
| character_sets_dir | C:\Program Files\MySQL\MySQL Server
4.1\share\charsets/ |
+------+
-+
That's all.
Mathias
-Original Message-
From: Fagyal Csongor [mailto:[EMAIL PROTECTED]
Sent: dimanche 24 avril 2005 22:33
To: mysql@lists.mysql.com
Subject: setting character sets "permanently"
HI,
I am using MySQL 4.1.11 on FC3, and I have trouble reading back latin2
characters. No wonder, as character_set_client, character_set_connection
and character_set_results are all set to latin1. The problem is that I
cannot set them to latin2 _permanently_, I mean every time I connect to
the database I have to issue either
$dbh->do("set collation_connection=latin2_general_ci");
$dbh->do("set character_set_client=latin2");
$dbh->do("set character_set_results=latin2");
OR
$dbh->do("SET NAMES 'latin2'");
which is basically the same.
Is there a way to tell the server that I want to use latin2 every time?
I mean something like: (in my.cnf):
please_always_use_this_character_set_or_i_go_crazy=latin2
or maybe
would_you_stop_doing_character_set_conversions_as_i_know_what_i_insert_j
ust_give_me_back_what_i_sent_to_you=1
?
I already set
character-set-server=latin2 collation-server=latin2_hungarian_ci
and they show up nicely in "show variables" (but this does not help me).
Also my database/tables/clumns are set to latin2 (both the character
set and the collaction).
Thank you,
- Csongor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: setting character sets "permanently"
Hi,
Look at this :
mysql> show variables like 'character%';
+--+
-+
| Variable_name| Value
|
+--+
-+
| character_set_client | latin1
|
| character_set_connection | latin1
|
| character_set_database | latin1
|
| character_set_results| latin1
|
| character_set_server | latin1
|
| character_set_system | utf8
|
| character_sets_dir | C:\Program Files\MySQL\MySQL Server
4.1\share\charsets/ |
+--+
-+
I changed my.ini (.my.cnf) like this :
[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3306
log-bin = "C:/Program Files/MySQL/MySQL Server 4.1/Data/binlog"
#Path to installation directory. All paths are usually resolved relative
to this.
basedir="C:/Program Files/MySQL/MySQL Server 4.1/"
#Path to the database root
datadir="C:/Program Files/MySQL/MySQL Server 4.1/Data/"
# The default character set that will be used when a new schema or table
is
# created and no character set is defined
### default-character-set=latin1
default-character-set=latin2
C:\Program Files\MySQL\MySQL Server 4.1\data>net stop mysql41
Le service MySQL41 s'arrête.
Le service MySQL41 a été arrêté.
C:\Program Files\MySQL\MySQL Server 4.1\data>net start mysql41
Le service MySQL41 démarre.
Le service MySQL41 a démarré.
mysql> show variables like 'character%';
+--+
-+
| Variable_name| Value
|
+--+
-+
| character_set_client | latin1
|
| character_set_connection | latin1
|
| character_set_database | latin1
|
| character_set_results| latin1
|
| character_set_server | latin2
|
| character_set_system | utf8
|
| character_sets_dir | C:\Program Files\MySQL\MySQL Server
4.1\share\charsets/ |
+--+--------
-+
That's all.
Mathias
-Original Message-
From: Fagyal Csongor [mailto:[EMAIL PROTECTED]
Sent: dimanche 24 avril 2005 22:33
To: mysql@lists.mysql.com
Subject: setting character sets "permanently"
HI,
I am using MySQL 4.1.11 on FC3, and I have trouble reading back latin2
characters. No wonder, as character_set_client, character_set_connection
and character_set_results are all set to latin1. The problem is that I
cannot set them to latin2 _permanently_, I mean every time I connect to
the database I have to issue either
$dbh->do("set collation_connection=latin2_general_ci");
$dbh->do("set character_set_client=latin2");
$dbh->do("set character_set_results=latin2");
OR
$dbh->do("SET NAMES 'latin2'");
which is basically the same.
Is there a way to tell the server that I want to use latin2 every time?
I mean something like:
(in my.cnf):
please_always_use_this_character_set_or_i_go_crazy=latin2
or maybe
would_you_stop_doing_character_set_conversions_as_i_know_what_i_insert_j
ust_give_me_back_what_i_sent_to_you=1
?
I already set
character-set-server=latin2
collation-server=latin2_hungarian_ci
and they show up nicely in "show variables" (but this does not help me).
Also my database/tables/clumns are set to latin2 (both the character
set and the collaction).
Thank you,
- Csongor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MYSQL to XML
Hi Mikel,
There are a lot of possibilities including commercial (:o)) products.
I suggest you those solutions. The output should be reparsed for your
needs :
1. the -X on client :
C:\Mysql>mysql -u mathias world -X -e "desc country"
Code
char(3)
PRI
Name
char(52)
Continent
...
...
2. install perl DBI and DBIx-XML_RDB modules :
#!perl -w
# ---
# Describe2xml
# Author : Mathias FATENE
# Date : 24 april 2005
# ---
use DBIx::XML_RDB;
my $userid='root';
my $password='**';
my $dbname='world';
my $dsn = "DBI:mysql:database=$dbname;host=localhost";
my $xmlout = DBIx::XML_RDB->new($dsn,'mysql',$userid, $password) || die
"Failed to make new xmlout";
$xmlout->DoSql("describe country");
print $xmlout->GetData;
C:\Mysql>perl describe.pl
Code
char(3)
PRI
Name
char(52)
...
...
3. install Perl DBI and DBD-Mysql and use my program (formatted for your
needs) :
#!perl -w
# ---
# Describe2xml
# Author : Mathias FATENE
# Date : April, 24 2005
# ---
use DBI;
my $userid='root';
my $password='';
my $dbname='world';
my $dsn = "DBI:mysql:database=$dbname;host=localhost";
my $dbh = DBI->connect($dsn,$userid, $password,{'RaiseError' => 1});
# ---
# describe country table and print it in XML format
# ---
my $table="country";
$sth = $dbh->prepare("describe $table");
$sth->execute();
print "\\n";
while (my @ref = $sth->fetchrow_array()) {
print "\\n";
}
$sth->finish();
print "\\n";
# Disconnect from the database.
$dbh->disconnect();
C:\Mysql>perl desc.pl country
is this beautifull ?
I will modify Describe2xml.pl to be more parametrized (user, db, pass,
FK, ...) as soon as possible.
Mathias
>> Hi list, does it possible for MySQL to generate XML in the followin
format:
>>
>>
>> > required="true" type="VARCHAR" size="10"/>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> This XML is the structure of the ServiceType table, I'll hope that
you >> can
>> help me
>>
>> Thnx in advanced
>>
>> Greetings
>>
>> P.S. Any suggestions (tools) will be appreciated
>>
>>
>>
>> Thread
>>
>> * MySQL to XML - Mikel -, April 23 2005 1:07am
>>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Performance issues when deleting and reading on large table
>> An index on 'gender' may have a cardinality of >> only two or three (male/female(/unknown)) for example. Never b-tree index such columns ! Oracle (db2 ...rdbms) has bitmap indexes which work fine fork such data. Look at BIN(myset+0) in http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html. Massive load is better without indexes, which are only good for selects. Mathias -Original Message- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] Sent: dimanche 24 avril 2005 19:46 To: Almar van Pel; mysql@lists.mysql.com Cc: 'mathias fatene' Subject: Re: Performance issues when deleting and reading on large table > > It's a probably a case of not having the cardinality of indexes right and > thus making wrong decisions for queries. > - Currently there is not a single query in the application that does not use > the correct index. We only have key-reads. Wich would mean that MySQL is > creating these incorrect indexes? The indexes are not necessarily incorrect, but MySQL also keeps a property called 'cardinality' for each index. It is an estimate of the number of different items in the index. An index on 'gender' may have a cardinality of only two or three (male/female(/unknown)) for example. I've noticed that the cardinality on MyISAM tables can be very wrong and will be updated to a correct value after an OPTIMIZE TABLE. InnoDB seems to estimate the cardinality constantly. > > Deleting a lot of records will have impact on the indexes, so it's quite a > job. The inserts/updates/deletes will also block the table for reading in > case of MyISAM. > - During deletion of records from the table there is no user interaction. > The only person manipulating the table/database is me. That's the reason why > i'm finding this 'strange'. It will still be a massive operation on indexes. If you have many indexes the task will be even harder... > Changing to Innodb would be a great risk I think. Maybe we should think this > over again, but the way the system is configured right now should in my > opion be sufficient enough. It's not a risk, but may take a while to complete (rebuilding the tables). Anyway, you should test it on a seperate database or even a different server. You may also need to redesign the index(es). Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi condition/table select
Hi, Don't you miss some relationnal definition in your table. Personnaly, I suggest in lnk : Lnk: catid=int11 primary key prodid=int 11 The key being (caid,prodid). And simplier (Normal form) : Categories: id=int 11 primary key title = varchar Products: id=int 11 primary key name=varchar catid int 11 The query will then be evident. Mathias -Original Message- From: Andy Pieters [mailto:[EMAIL PROTECTED] Sent: dimanche 24 avril 2005 19:35 To: mysql@lists.mysql.com Subject: Multi condition/table select Hi all I am trying to figure out how to do this in one query. Using MySQL 2.3.58 Tables (only relevant data shown) Categories: id=int 11 primary key title = varchar Products: id=int 11 primary key name=varchar Lnk: catid=int11 primary key lnk=int 11 (key: unique combo catid+lnk) Id's for categories are between 20001 and 25000 id's for products are <2 I want to select all products that do not have a link to category x in the table Lnk. Example Categories: idtitle 20001 Network 20002 Switches Products id name 1 10/100 Switch 5 port 2 10/100 Switch 8 port 3 10/100/1000 Switch 5 port Lnk catidlnk 20001 20002 20002 1 With this data, when using the category 20002, the query should return products with id 2, and 3. If used with category 20001, it should return products with id 1, 2, and 3 I was thinking on using three left joins but have been unable to make working code. Can anybody make sense to this? With kind regards Andy -- Registered Linux User Number 379093 -- Check out these few php utilities that I released under the GPL2 and that are meant for use with a php cli binary: http://www.vlaamse-kern.com/sas/ -- -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Table handler errors
Hi all, Sounds like a max heap table size reached : mysql> show variables like '%heap%'; +-+--+ | Variable_name | Value| +-+--+ | max_heap_table_size | 16777216 | +-+--+ 1 row in set (0.00 sec) if you redefine it, it may work better in memory. Else use temporary tables. Mathias -Original Message- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] Sent: dimanche 24 avril 2005 17:08 To: [EMAIL PROTECTED] Cc: MySQL List Subject: Re: Table handler errors > Yes, I've going through the docs and the manual i have hear locally, couldn't > really find a specific reason and/or causing for the error. The table type that > was causing the error was a HEAP table, changed it to a MyISAM table type and > the error disappeared, no more handler errors now... go figure! HEAP tables are stored in memory http://dev.mysql.com/doc/mysql/en/memory-storage-engine.html Error 12 may be Out of memory? Which would explain the error... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Performance issues when deleting and reading on large table
If you have no active transactions and want a cron delete, an example is : * Create table tmp as select * from your_table where ... <- here indexes are used * drop indexes * delete from you_table where ... * insert into your_table select * from tmp * create index on you_table. You must test it to unsure that index creation is not slow when you have a lot of indexes. You can also disable constraints when deleting and optimize your table at the end of the deletion. In myisam storage, since an update,insert or delete means lock table there is a big transactional problem. Innodb offers row loocking, but you seem having a problem using it. Unfortunaltly ! To simulate transaction, you must split your queries. I remember had worked on a load problem which take days to finish (or not) because the load operation was combined with a lot of select (verify) data. My solution was to do a lot of selects (using indexes), spool results to files, delete rows, and load data from files. It took 1.5 hour to finish a 650Mo data with all the checking operations. Mathias -Original Message- From: Almar van Pel [mailto:[EMAIL PROTECTED] Sent: dimanche 24 avril 2005 18:44 To: mysql@lists.mysql.com Cc: 'Jigal van Hemert'; 'mathias fatene' Subject: RE: Performance issues when deleting and reading on large table Hi Jigal, Mathias, Thanks the time you took to reply to my issue's! I would like to clear out some things. > It's a probably a case of not having the cardinality of indexes right and thus making wrong decisions for queries. - Currently there is not a single query in the application that does not use the correct index. We only have key-reads. Wich would mean that MySQL is creating these incorrect indexes? > Depending on the size of the resulting record sets, your system must have enough memory to handle it. Otherwise a lot of temporary tables will end up on disk (slow) and also indexes cannot be loaded in memory (slow). - The system runs with a key-buffer of 382 M, wich is most of the time not filled 100 %. Created temp. tables is very low. > Deleting a lot of records will have impact on the indexes, so it's quite a job. The inserts/updates/deletes will also block the table for reading in case of MyISAM. - During deletion of records from the table there is no user interaction. The only person manipulating the table/database is me. That's the reason why i'm finding this 'strange'. Changing to Innodb would be a great risk I think. Maybe we should think this over again, but the way the system is configured right now should in my opion be sufficient enough. Mathias, what do you mean by: > If you want to do a massive delete with a cron, it's better to : > * select the rows to delete (using indexes) > * delete indexes > * delete rows (already marked) > * recreate indexes I don't really understand how you 'mark' the records for deletion before deleting indexes. However I'm very interested. Regards, Almar van Pel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql_performance
Hi all,
I can see a cartesian product on the EMP table. Is this really what
you're looking for. There is no column joining T2 and T3 !
Also as Peter said, you should have a lack of indexes on your tables.
If you can send me your .frm,.myd and .myi files of the two tables as a
zip file, I may help you to execute your query in less than 20mn.
Question : count(*) from EMP = ?
Mathias
-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED]
Sent: dimanche 24 avril 2005 17:39
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: mysql_performance
Moemen,
You assign a string _position_ result from LOCATE to a SUBSTRNG _length_
argument. Is that what you mean?
Do you have indexes on persons.item_id, emp (item_id, item_type, f2, f6,
f7)? Once there are such indexes, try ordering the WHERE ... ANDs to
correspond to those keys so the optimiser can use the index
If it takes 20 mins, you likely have lots of rows, and the per-row
SUBSTRING(...LOCATE...) calls will likely slow it down. If the above
changes don;t help, you could try breaking out the substrings to
separate columns and index on them too.
Try running EXPLAIN on your query before & after such changes to see if
you're changing the query engine's plan.
HTH
Peter Brawley
http://www.artfulsoftware.com
-
moemen saad eldeen wrote:
>Dear all,
>
>I have a problem running this query
>
>
>"select distinct T1.item_id, T1.f2 from Persons as T1 , Emp as
>T2 , Emp as T3 where T1.item_type='6.' and T2.item_type='6.1.9.'
>and
>T3.item_type='6.1.' and T2.f2 like '1.1.16.%' and
>substring(T2.item_id,1,LOCATE('.',T2.item_id))=T1.item_id and
>substring(T3.item_id,1,LOCATE('.',T3.item_id))=T1.item_id and
>(T3.f2
>='4.1.1.') and (T3.f7 is null ) and (T2.f6 is null ) order by
>T1.f2
>"
>
>
>on my server the output come after about 20 mins i have tried all
>possible solutions for tunning my server using :
>
>1-server parameters like: increasing key_buffer,read buffer,setting
>result buffer, disable swapping
>2-high memory: 2GB RAM
>
>
>All my tables are MYISAM and with varchar type
>
>I don't know how to speeding output
>
>
>can anyone help
>
>
>
>
>
>
>
>
>
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Performance issues when deleting and reading on large table
Hi all, Know that indexes are good for select(s), but very bad for massive insert,update and delete. If you want to do a massive delete with a cron, it's better to : * select the rows to delete (using indexes) * delete indexes * delete rows (already marked) * recreate indexes Another way if you want to delete a big percentage of your table, is to copy the stating records, drop table and recreate it with those record. Then recreate indexes. I assume that you're not in a massive transactional situation, and maybe myisam storage. If not, show processlist may help you to track using or not of internal temporary tables, ... Mathias -Original Message- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] Sent: dimanche 24 avril 2005 17:05 To: Almar van Pel; mysql@lists.mysql.com Subject: Re: Performance issues when deleting and reading on large table From: "Almar van Pel" > After some time (sometimes a week sometimes a month) it appears that the > index of the table gets stuck. > It tries to read from the table but does not get response. This causes the > connectionqueue to fill up > and the load on the system increases dramatically. In other words, unless I > do an optimize table , the system > hangs. Most of the times you see that the index is getting 20 Mb off. > When I do check table (before optimizing) there are no errors. > > Is there any way to see this problem coming, so I can outrun it? (Without > having to schedule optimize, wich = downtime, every week..) You should run optimize table regularly (once a week or so) in some cases: http://dev.mysql.com/doc/mysql/en/optimize-table.html It's a probably a case of not having the cardinality of indexes right and thus making wrong decisions for queries. > Trying to get the previous table clean, I created some jobs deleting old > records. When I delete a lot of records at in one job, > the system also nearly hangs. (+/- 10 to 15.000 records) The load again > increases dramatically. I tried every trick in the book, but cannot > understand, > why this action is so heavy for the system. Deleting a lot of records will have impact on the indexes, so it's quite a job. The inserts/updates/deletes will also block the table for reading in case of MyISAM. Such a large table in a high concurrency situation (many writes and many reads) can be a job for InnoDB tables. They seem slow for small tables, but have the tendency to keep the same speed for large tables, while MyISAM will probably get slower the bigger the table is under these circumstances. If you can use the PRIMARY index in a query and keep the 'PRIMARY' index as short as possible, InnoDB can be a very fast table handler. Depending on the size of the resulting record sets, your system must have enough memory to handle it. Otherwise a lot of temporary tables will end up on disk (slow) and also indexes cannot be loaded in memory (slow). Running large databases is sometimes a bit of a challenge; finding the right queries, setting up the right index(es), etc. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need reference for a good book
Preferably one that is for begginers to medium and thats covers both mySQL and PHP Thanks
RE: local installation on XP
Marvin, All files exist in the WINDOWS directory. I did run the commands form the C:\mysql\bin\ directory. Then basically all that happened was that the cursor moved beneath and flashed for a bit then the directory path came up again. Still the same from mySQLCC: error1045. > [Original Message] > From: Marvin Cummings <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Date: 3/21/2004 3:32:03 PM > Subject: RE: local installation on XP > > Check you Windows directory for these files: > a. myodbc3.dll > b. myodbc3.lib > c. myodbc3_install.log > d. myodbc3d.dll > > Open a command prompt and attempt to start the service by navigating to the > mysql\bin directory and typing > mysqld --console - this runs some INNODB commands > mysqld --install - this starts the mysql service > > hth > > -Original Message- > From: A Mathias [mailto:[EMAIL PROTECTED] > Sent: Sunday, March 21, 2004 5:17 PM > To: Marvin Cummings > Subject: RE: local installation on XP > > Got it 3.51 installed, but still getting 1045 Error from mySQLcc > > > > [Original Message] > > From: Marvin Cummings <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Date: 3/21/2004 3:04:31 PM > > Subject: RE: local installation on XP > > > > Yes it is. MySQL.com --> Downloads > > > > -Original Message- > > From: A Mathias [mailto:[EMAIL PROTECTED] > > Sent: Sunday, March 21, 2004 4:58 PM > > To: Marvin Cummings > > Subject: RE: local installation on XP > > > > Yes, there is a Green light on the WinMySQL tool, but in the enviorment > TAB > > it show's that myODBC 3.51 driver "not found". How do I get this? is it on > > the mySQL site? > > > > > > > [Original Message] > > > From: Marvin Cummings <[EMAIL PROTECTED]> > > > To: <[EMAIL PROTECTED]> > > > Date: 3/21/2004 2:36:19 PM > > > Subject: RE: local installation on XP > > > > > > Do you see a green light in the WinMySQL Tool? > > > Have you insalled MySQLODBC ver3.51? If so pull up a command prompt and > > type > > > c:\mysql\bin> mysqld --console > > > Also type > > > c:\mysql\bin> mysqld --install > > > > > > Try this doc: > > > http://www.mysql.com/doc/en/Windows_post-installation.html > > > > > > HTH > > > > > > -Original Message- > > > From: A Mathias [mailto:[EMAIL PROTECTED] > > > Sent: Sunday, March 21, 2004 4:16 PM > > > To: mysql > > > Subject: local installation on XP > > > > > > I've just installed mySQL locally on XP and am getting the following > error > > > while trying to connect via mySQLCC. I have checked the username and > > > password and things are ok there. XP is running the mysql-nt.exe fine, > but > > > still no go when I try to connect, any ideas?? > > > > > > > > > #This File was made using the WinMySQLAdmin 1.4 Tool > > > #3/20/2004 6:28:50 PM > > > > > > #Uncomment or Add only the keys that you know how works. > > > #Read the MySQL Manual for instructions > > > > > > [mysqld] > > > basedir=C:/mysql > > > #bind-address=67.234.135.56 > > > datadir=C:/mysql/data > > > #language=C:/mysql/share/your language directory > > > #slow query log#= > > > #tmpdir#= > > > #port=3306 > > > #set-variable=key_buffer=16M > > > [WinMySQLadmin] > > > Server=C:/mysql/bin/mysqld-nt.exe > > > user=** > > > password=* > > > > > > > > > A Mathias > > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
local installation on XP
I've just installed mySQL locally on XP and am getting the following error while trying to connect via mySQLCC. I have checked the username and password and things are ok there. XP is running the mysql-nt.exe fine, but still no go when I try to connect, any ideas?? #This File was made using the WinMySQLAdmin 1.4 Tool #3/20/2004 6:28:50 PM #Uncomment or Add only the keys that you know how works. #Read the MySQL Manual for instructions [mysqld] basedir=C:/mysql #bind-address=67.234.135.56 datadir=C:/mysql/data #language=C:/mysql/share/your language directory #slow query log#= #tmpdir#= #port=3306 #set-variable=key_buffer=16M [WinMySQLadmin] Server=C:/mysql/bin/mysqld-nt.exe user=** password=* A Mathias
this newbies project :-)
Hey everyone :-) This is my first post to the list and I would like to thank everyone for this great resource. I'm relatively new to mySQL and a novice at PHP, but here is a description of what I am trying to do. My objective is to create a database that will provide variables for a series of forms. These variables would be conditional based on a few series of simple drop down menu based questions. Here is a particular sequence; 1st)" What era of military is this roster for?" a) Modern b) WWI/WWII Based on a)'s choice they would get this list of variables to choose from; Spanish Marines: Japanese Military: US Airforce: US Army: US Marines: US Navy: Based on b)'s choice they would get this list of variables to choose from; WW2 British Army: WW2 G-Kreigsmarine: WW2 G-Luftwaffe: WW2 G-Waffen-SS: WW2 G-Wehrmacht: WW2 Red Army AirForce: WW2 Royal Air Force: WW2 Royal Navy: WW2 US Army AirForce: WW2 US Army: WW2 US Navy: Now for any of these choices I have a list of associated ranks which depending on which choice is made could be as little as 20 but as many as 30 variables to choose from. For example, here is a list of equal world ranks based on the U.S. equivalent of (E-1) or standard "enlisted" men. ENLISTED PRIVATES (E-1) Private Spanish Marines:Soldado Japanese Military: Nitto Hei US Airforce:Airman Basic US Army:Private (E-1) US Marines: Private US Navy:Seaman Recruit WW2 British Army: Private or Sapper WW2 G-Kreigsmarine: Matrosengefrieter WW2 G-Luftwaffe:Flieger or Gefrieter WW2 G-Waffen-SS:SS-Schutze WW2 G-Wehrmacht:Schutze (after Nov. 1942: Grenadier) WW2 Red Army AirForce: Krasnoarmeyets WW2 Royal Air Force:Aircraftsman 2nd Class WW2 Royal Navy: Ordinary Seaman WW2 US Army AirForce: Private WW2 US Army:Private WW2 US Navy:Seaman Recruit So, as you can see that there is a lot of menu driven choices and this is only one tier of ranks of 25 or 30 that must be created. Plus I have an image directory with all the associated ribbons for each of these ranks on each level. For that though I will refer to the directory where the image resides rather then dragging the speed of the DB down to call on the images. Having never created a database of this size I am looking for some guidance/help on what might be a way to best approach or someway to get the info in by batching a CSV file or something like that. ENLISTED PRIVATES (E-1) Private Spanish Marines: Soldado Japanese Military: Nitto Hei US Airforce: Airman Basic US Army: Private (E-1) US Marines: Private US Navy: Seaman Recruit WW2 British Army: Private or Sapper WW2 G-Kreigsmarine: Matrosengefrieter WW2 G-Luftwaffe: Flieger or Gefrieter WW2 G-Waffen-SS: SS-Schutze WW2 G-Wehrmacht: Schutze (after Nov. 1942: Grenadier) WW2 Red Army AirForce: Krasnoarmeyets WW2 Royal Air Force: Aircraftsman 2nd Class WW2 Royal Navy: Ordinary Seaman WW2 US Army AirForce: Private WW2 US Army: Private WW2 US Navy: Seaman Recruit I have a few resellers accounts and would be willing trade some web-space/bandwidth for some direct help on this. Please email me direct if can give some time to this. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MATCH / AGAINST fatal bug
MATCH / AGAINST fatal bug
MySQL version: 4.0.11-gamma
OS : Windows 2000 SP3
RAM: 1GB Apacer
Free Disk Space: 32GB
HD Write Back Cache: Disabled
Under some circumstances the following query does never terminate. The
win32 service cannot be stopped either = FATAL.
SELECT f.id as id, fs.name as name, MATCH (f.title,f.body) AGAINST
('550') AS score FROM file as f, filesys as fs WHERE f.id = fs.fileid
AND MATCH (f.title,f.body) AGAINST ('550') > 0 ORDER by score DESC
Same behavior can be seen with "HAVING score > 0" or when using BOOLEAN
mode.
If you are not interested in fixing this bug any advice how to
circumvent this bug would be appreciated.
Thanks a lot.
-Mat
SmartFTP.com
mysqladmin -proc Output
===
| 25850 | kb | localhost | kb | Query | 17497 | Copying to tmp table
| SELECT f.id as id, fs.name as name, MATCH (f.title,f.body) AGAINST
('2147220991') AS score FROM file |
OR
| 37522 | kb | localhost | kb | Query | 1323 | Copying to tmp table
| SELECT f.id as id, fs.name as name, MATCH (f.title,f.body) AGAINST
('550') AS score FROM file as f, |
OR
| 56040 | kb | localhost | kb | Query | 13095 | Copying to tmp table
| SELECT f.id as id, fs.name as name, fs.id as fsid, MATCH
(f.title,f.body) AGAINST ('asteriks') AS sc |
Table Structure
===
#
# Table structure for table `file`
#
CREATE TABLE file (
id int(11) NOT NULL auto_increment,
title text,
version varchar(255) default NULL,
created int(11) NOT NULL default '0',
modified int(11) default NULL,
body text,
hits int(11) NOT NULL default '0',
PRIMARY KEY (id),
KEY modified (modified),
KEY hits (hits),
KEY created (created),
FULLTEXT KEY titlebody (title,body),
FULLTEXT KEY body (body)
) TYPE=MyISAM;
-
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: MATCH / AGAINST bug/problem
Hi ..
Free disk space: 32GB
Thats not the problem I guess.
-Mat
-Original Message-
From: gerald_clark [mailto:[EMAIL PROTECTED]
Sent: Freitag, 7. März 2003 22:03
To: Mathias Berchtold
Cc: [EMAIL PROTECTED]
Subject: Re: MATCH / AGAINST bug/problem
I you run low on temp filesystem space, mysqld will wait until disk
space becomes available.
Are you getting low when it appears to hang?
Mathias Berchtold wrote:
>MATCH / AGAINST problem/bug
>
>
>MySQL version: 4.0.11-gamma
>OS : Windows 2000 SP3
>
>Under some circumstances this query does never terminate. The win32
>service cannot be stopped either.
>
>SELECT f.id as id, fs.name as name, MATCH (f.title,f.body) AGAINST
>('550') AS score FROM file as f, filesys as fs WHERE f.id = fs.fileid
>HAVING score > 0 ORDER by score DESC
>
>mysqladmin -proc Output
>===
>
>| 25850 | kb | localhost | kb | Query | 17497 | Copying to tmp table
>| SELECT f.id as id, fs.name as name, MATCH (f.title,f.body) AGAINST
>('2147220991') AS score FROM file |
>OR
>| 37522 | kb | localhost | kb | Query | 1323 | Copying to tmp table
>| SELECT f.id as id, fs.name as name, MATCH (f.title,f.body) AGAINST
>('550') AS score FROM file as f, |
>
>
>Table Structure
>===
>
>#
># Table structure for table `file`
>#
>
>CREATE TABLE file (
> id int(11) NOT NULL auto_increment,
> title text,
> version varchar(255) default NULL,
> created int(11) NOT NULL default '0',
> modified int(11) default NULL,
> body text,
> hits int(11) NOT NULL default '0',
> PRIMARY KEY (id),
> KEY modified (modified),
> KEY hits (hits),
> KEY created (created),
> FULLTEXT KEY titlebody (title,body),
> FULLTEXT KEY body (body)
>) TYPE=MyISAM;
>
>-
>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
MATCH / AGAINST bug/problem
MATCH / AGAINST problem/bug
MySQL version: 4.0.11-gamma
OS : Windows 2000 SP3
Under some circumstances this query does never terminate. The win32
service cannot be stopped either.
SELECT f.id as id, fs.name as name, MATCH (f.title,f.body) AGAINST
('550') AS score FROM file as f, filesys as fs WHERE f.id = fs.fileid
HAVING score > 0 ORDER by score DESC
mysqladmin -proc Output
===
| 25850 | kb | localhost | kb | Query | 17497 | Copying to tmp table
| SELECT f.id as id, fs.name as name, MATCH (f.title,f.body) AGAINST
('2147220991') AS score FROM file |
OR
| 37522 | kb | localhost | kb | Query | 1323 | Copying to tmp table
| SELECT f.id as id, fs.name as name, MATCH (f.title,f.body) AGAINST
('550') AS score FROM file as f, |
Table Structure
===
#
# Table structure for table `file`
#
CREATE TABLE file (
id int(11) NOT NULL auto_increment,
title text,
version varchar(255) default NULL,
created int(11) NOT NULL default '0',
modified int(11) default NULL,
body text,
hits int(11) NOT NULL default '0',
PRIMARY KEY (id),
KEY modified (modified),
KEY hits (hits),
KEY created (created),
FULLTEXT KEY titlebody (title,body),
FULLTEXT KEY body (body)
) TYPE=MyISAM;
-
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 embedded?
Hi Thank you for the help. It looks fairly uncomplicated this embedded database. Luckily the application we are planning is also supposed to be Open Source and available on sourceforge.net, so there shouldn't be a problem with licensing. The idea is, that the user, on install, gets to choose between a purely client program connecting to a local or remote MySQL database with the benefits that entails (multiple users on the same data and from different places), or a client/server program with the MySQL database embedded in the applications, for the small solutions. Does anyone know if it complicates the code having these two options available for the user? Do the database have the same interface and features available (eg. transactions) in both these two options? /Mathias - Original Message - From: "Jan Peuker" <[EMAIL PROTECTED]> To: "Mathias Bertelsen" <[EMAIL PROTECTED]> Sent: Thursday, July 18, 2002 9:56 AM Subject: Re: MySQL embedded? > Hi Mathias, > > it's possible, have a look at: http://www.mysql.com/doc/l/i/libmysqld.html , > the problem is just a) licensing and b) you can't connect from an outside > process. It's no problem, too, to install a MySQL-Database before, that's a > question of your install procedure. The only problem will be, to modularize > the relevant parts of your application(e.g. database creation, rights > management). > But, at least, there is berkeleyDB(http://www.sleepycat.com/), too, if you > just want small tables like hashes and no relations. > regards, > > jan - 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 embedded?
Hello I have a question about MySQL and regular 'programs', as i am normally used to programming web-apps and the like, with that kind of programming and database interaction. I want to know if it is possible to somehow 'embed' the MySQL database in the program you are making? (on say, Java and windows...?) so the person installing the program doesn't have to install a MySQL database on his system And if so, is it possible (and easy from a developers point of view) to make the user in the beginning on install choose between having a regular MySQL-database or the 'embedded'? Sorry if it's a stupid question but i am really not used to making regular applications... :) and i didn't find an answer on mysql.com... /Mathias - 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 security
Hello I have a question for all you MySQL people out there We are a group of people planning to make a small open source ERP/accounting/finance program. We have earlier used MySQL to great satisfaction in other areas and would like to use it here. My question is: Do you think MySQL is secure enough to keep peoples bookkeeping in? is it safe enough to use without risk of losing important data? Is it necessary to do anything to make it secure? (eg. use of transactions/backup/power failure security) Any comments are welcome :) /Mathias - 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
Calculate HEAP-size
Hi, Does anyone got information regarding my question about calculating a HEAP table size in MySQL?: http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:98013 Thank you, Mathias ___ Spara filer på nätet. Lagra upp till 500 Mb på Passagen http://webbdrive.passagen.se - 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
HEAP table size
Hello, I am using HEAP tables and would like to see how much memory they use. I found this snipped of instruction in the MySQL-documentation: -- "The memory needed for one row in a HEAP table is: SUM_OVER_ALL_KEYS(max_length_of_key + sizeof(char*) * 2) + ALIGN(length_of_row+1, sizeof(char*)) sizeof(char*) is 4 on 32-bit machines and 8 on 64-bit machines." -- But frankly, I don't get it. Could someone please explain this to me? Lets say I have a heap-table that looks like this: CREATE TABLE get_mem( idINT NOT NULL, name CHAR(15) NOT NULL, nrMEDIUMINT NOT NULL, PRIMARY KEY(id) ) TYPE = HEAP; How would I calculate the memory used for 1 row? Thank you, Mathias ___ Spara filer på nätet. Lagra upp till 500 Mb på Passagen http://webbdrive.passagen.se - 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
