Re: Versioned Manual (was: MySQL 5.0.x)
Hi! Jochem van Dieten wrote: On 7/14/05, Joerg Bruehe wrote: However, the online manual is not cloned, so while we are building 5.0.9 there can also be new text for 5.0.10 changes that gets integrated into the online manual, and this may become visible earlier than 5.0.9 gets published. Why are the online manuals not cloned and versioned? That is a question to the Docs team. IMHO, a versioned _online_ manual would make things more complicated for the (Web) visitor. One of the things I like about the documentation of most other databases compared to MySQL is that it is tightly coupled to a specific version of the software. [[...]] AFAIK, exactly this will happen - but with the manual that is available for download. IMO, users can/will download the manual for the version they are using, but the online manual is also intended for those who want to get the overview before they decide / select a version. Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on Indices
Manoj [EMAIL PROTECTED] wrote on 07/14/2005 06:09:24 AM: Greetings, I am trying to get a feel of how MySQL would handle certain types of situation, mainly concerning the usage of indices. Say I have two exactly identical table structures namely table A B. For table A, I just have one composite primary key on (Code, Date1 Date2). For table B, I just have composite primary key on (Code, Date1 Date2) and two non-unique keys on Date1 Date2. Question is, Will I find any performance improvement with table B, If all my queries start with Code field ? In other words : Will there be any noticiable difference in speed for query Select * from tableB where code = cd and dt1 $one_year_ago over the same query on tableA ? Kindly note that we might have 1000 or more records for each code. When I tried to do an explain, it suggested in both the cases (on tableA on tableB) that It will use composite primary key (mainly because left most field in the primary index is avaliable)suggesting that the non-unique indices are pretty much a waste of space...Is that really the case? Woudln't it be optimal to use primary key + non-unique key combination to narrow down the search? I would greatly appreciate your insight in this issue. TIA Manoj First, I must comment that you double-posted. Bad form. Second, if you RTFM, you will quickly discover that MySQL only uses ONE (1) index per table participating in any query. Which index will be used (if one is used at all) is based on the probability of retrieving less than roughly thirty percent (30%) of the rows from any table. The 30% statistic is a rough figure as the exact threshold is determined at run time using parameters like table size, index cardinality, other columns in the SELECT clause, and what other actions need to be done to this table (like GROUP BY or ORDER BY). It is entirely conceivable that an index (even though several may be available) will not be used to get data from a particular table in a particular query. Please READ THE FINE MANUAL for more information about optimizing MySQL queries: http://dev.mysql.com/doc/mysql/en/mysql-optimization.html If English is not your primary language, some translations are also online. http://dev.mysql.com/doc/mysql/fr/mysql-optimization.html http://dev.mysql.com/doc/mysql/pt/mysql-optimization.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Split a table?
What if: Mysqldump TABLE --where=id@middle first_part.sql Mysqldump TABLE --where=id[EMAIL PROTECTED] second_part.sql @middle can be calculated in another place And then: You will need to edit each file to change table name :: ISC Edwin Cruz Garcia :: IT Factory Systems - Systems Department Texas Instruments de Mexico (449)9105194 Direct Line (449) 9105100 Switchboard, Ext. 5194 (449) 9105124 Fax E-mail: [EMAIL PROTECTED] -Original Message- From: Atle Veka [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 13, 2005 10:33 PM To: Brian Dunning Cc: mysql@lists.mysql.com Subject: Re: Split a table? Depends on how your table is designed. You could do an 'INSERT INTO .. SELECT FROM ..' with a WHERE/ORDER BY/LIMIT combo (switch the ORDER BY for each new table). It would be probably easiest if you have an AUTO_INCREMENT field.. Atle - Flying Crocodile Inc, Unix Systems Administrator On Tue, 12 Jul 2005, Brian Dunning wrote: If I have a table with 200K records, is there an easy way to split it into two separate tables with 100K records each? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.x
Hi! Sujay Koduri wrote (in personal mail): Hi jorg, DO you have any idea when MySQL 5.0 is going to get into production. Sujay, please 1) mail such questions to the list, not just to me personally 2) do not post above a full quote, it wastes readers' time, bandwidth, and disk space. Regarding your question: Get into production strictly speaking means will be used by customers for production purposes. Obviously, I cannot answer this. If you mean: ... will be recommended by MySQL AB for production purposes, the answer is: MySQL AB has published criteria for the various levels (alpha, beta, release candidate, production), and these take precedence over any intended schedule. Currently, 5.0 versions are labeled beta, so they have to pass through the release candidate (former: gamma) level before they are qualified production. Nobody can tell in advance how soon that is going to happen, but obviously MySQL is concentrating on that goal. The decisions to label it release candidate and production also depend on the feedback MySQL is getting: the more reports there are, the greater is the certainty that customers are using it, and any errors would have shown up and been reported. So a greater feedback about positive experiences might help to shorten the beta and release candidate phases. Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Versioned Manual (was: MySQL 5.0.x)
Joerg Bruehe [EMAIL PROTECTED] wrote on 07/14/2005 09:35:50 AM: Hi! Jochem van Dieten wrote: On 7/14/05, Joerg Bruehe wrote: However, the online manual is not cloned, so while we are building 5.0.9 there can also be new text for 5.0.10 changes that gets integrated into the online manual, and this may become visible earlier than 5.0.9 gets published. Why are the online manuals not cloned and versioned? That is a question to the Docs team. IMHO, a versioned _online_ manual would make things more complicated for the (Web) visitor. One of the things I like about the documentation of most other databases compared to MySQL is that it is tightly coupled to a specific version of the software. [[...]] AFAIK, exactly this will happen - but with the manual that is available for download. IMO, users can/will download the manual for the version they are using, but the online manual is also intended for those who want to get the overview before they decide / select a version. Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com I know (by lurking on other lists) that the documentation team is currently forking the manual into 4.1 and below and 5.0 and above versions and cleaning up the text to be less confusing in each. I know (from past experience) that many version-specific manuals have been made as PDF files. One has been available for all recent releases. I do not know when this practice started so some older releases may not have PDF extracts of the manual availale. I am not sure where to find copies of them for the older versions. I concede that it is not difficult to build a web site that serves different content based on some kind of key value (like version). While it would be possible to make the online manual version-sensitive but it would not be practical with the tools they are using today. Basically, each user would request a version specific page as they browsed through the manual but that would either require separate version-specific copies of the content organized in different directory trees (for static serving) or scripted pages (for dynamic serving). Both have their advantages and drawbacks. However, I do not believe that the documentation team has either the funding or the time to spend managing either kind of site as both would require MUCH more maintenance than their current process. It could be nice to have a version-sensitive online site but I like the fact that I do not need to cross-browse when migrating or managing different server versions, it's all on the same pages. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
SQL 'clustering' query?
Hi, I remember reading about an SQL query type which did something like select all 'aircraft hangers' which contained exactly (or at least) some given set of aircraft. Or did it select the list of pilots qualified to fly all the airplanes in the hanger... Anyway, I forget the syntax (and the fancy name for this kind of query). What I want to do is the following, given this data... Table: ATTRIBUTE_LIST; ID ATTRIBUTE W A W B W C X A X B X C Y A Y B Y C Y D Z E -- SQL MAGIC -- Table: CLUSTERS G_IDID 1 W 1 X 2 Y 3 Z That is, to group together all ID's with the same 'set' of ATTRIBUTES. Currently I am doing this using 'GROUP_CONCAT', but my attribute list just went above the limit for the GROUP_CONCAT column... +-+--+--+ | Level | Code | Message | +-+--+--+ | Warning | 1260 | 7 line(s) were cut by GROUP_CONCAT() | +-+--+--+ My query looks roughly like this... SET @i:=0, @x:='', @row:=''; # DROPTABLE CLUSTERS; CREATE TABLE CLUSTERS (PRIMARY KEY (ID), INDEX (G_ID)) # SELECT ID, G_ID # FROM ( SELECT ID, # @x:= ATTR_LIST AS HIDDEN1, # IF(@row = @x, @i, @i:[EMAIL PROTECTED]) AS G_ID, # @row:= @x AS HIDDEN2 # FROM ( SELECT ID, GROUP_CONCAT(ATTRIBUTE) AS ATTR_LIST, FROM ATTRIBUTE_LIST GROUP BY ID # ) AS vt1 # ORDER BY -- This is very important for ATTR_LIST -- the overall query. # ) AS vt2; (And thats the highly simplified version!) I can't shake the feeling that this 'string based' approach (while quite speedy) is inherently messy, and that a proper 'set based' approach should exist, and shouldn't have the limitation in the number of attributes that the above method has. In general I would really like to (somehow) develop a suite of easy to use 'SQL CLUSTER' commands, as the data mining community needs that kind of thing in nice general (set based) abundance :) Anyway, thanks for any feedback on any of the above, Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to update a mysql table from access
wow thanks ! this i can do. was just downloading python for windows, will still keep it, may be usefull one day, but i would lot rather update via access. thanks again. nephish On Thu, 2005-07-14 at 09:18 -0400, [EMAIL PROTECTED] wrote: If you have your native, auto-updated table in MS Access and a different Linked table pointing to the MySQL copy of it in the same database, just build an Access query that will INSERT or UPDATE (as appropriate) your linked table with data from your native table. No scripting required, just the internal data manipulation of Access. Consult the MS Access help files or any number of online resources for instructions on how to build a query in access that copies data from one table to another. Shawn Green Database Administrator Unimin Corporation - Spruce Pine nephish [EMAIL PROTECTED] wrote on 07/13/2005 10:32:19 PM: you mean like in a script? the windows computer runs access, which i am not very familiar with and was able to accomplish what i have done so far by lots o' docs at the mysql.com site. sorry for the newbie-ness of this question. i am somewhat familliar with python, maybe there is a module i can use for this.. thanks, On Wed, 2005-07-13 at 22:10 -0500, mos wrote: At 08:51 PM 7/13/2005, you wrote: Hey there, thanks to some help i have received right here, i have been able to access a mysql database on a linux computer from MS access on a windows computer, i was able to connect and create the tables and export all rows correctly.. i used MyODBC from mysql. ok, here is the deal, the access database gets info from another program and adds new rows every 15 seconds or so, i need some automated way to sync the two databases together every oh,,, 5 minutes or so. there is lots of documentation on how to do this by linking a table to a mysql table, however, when i do this, the access table is the one that gets updated, not the mysql table (deletes any info received since last update) and still does not provide a way to do this automatically. I cant find way to do it anywhere, little or no docs.. or i am looking in the wrong place. thanks for reading this, hope someone can help Have you tried prefixing the MySQL table with the MySQL database name? Example: select * from MySqlDb1.Table1; insert into MySqlDb1.Table1 (cust_id, cust_name) values(1,John Smith); Mike -- 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 forgets user passwords
I have mysql 4.1.12 installed on OSX 10.4, and have run into the curious problem that mysql forgets my user password (but not my root password) when I restart the server. When I attempt to log in, I get: ERROR 1045 (28000): Access denied for user 'chris'@'localhost' (using password: YES) Yet, when I go in as root and re-grant permissions with the user password, access is restored. When I reboot, I get the error again. What could possibly be causing this? Thanks, Chris Fonnesbeck -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL 'clustering' query?
Dan, Hi, I remember reading about an SQL query type which did something like select all 'aircraft hangers' which contained exactly (or at least) some given set of aircraft. Or did it select the list of pilots qualified to fly all the airplanes in the hanger... Anyway, I forget the syntax (and the fancy name for this kind of query). I think the concept you're after is relational division. The aeroplane hangar was one of Celko's examples. Another from him is at http://www.artfulsoftware.com/queries.php#28, other examples at http://www.artfulsoftware.com/queries.php#22, http://www.artfulsoftware.com/queries.php#33, PB Dan Bolser wrote: Hi, I remember reading about an SQL query type which did something like select all 'aircraft hangers' which contained exactly (or at least) some given set of aircraft. Or did it select the list of pilots qualified to fly all the airplanes in the hanger... Anyway, I forget the syntax (and the fancy name for this kind of query). What I want to do is the following, given this data... Table: ATTRIBUTE_LIST; ID ATTRIBUTE W A W B W C X A X B X C Y A Y B Y C Y D Z E -- SQL MAGIC -- Table: CLUSTERS G_IDID 1 W 1 X 2 Y 3 Z That is, to group together all ID's with the same 'set' of ATTRIBUTES. Currently I am doing this using 'GROUP_CONCAT', but my attribute list just went above the limit for the GROUP_CONCAT column... +-+--+--+ | Level | Code | Message | +-+--+--+ | Warning | 1260 | 7 line(s) were cut by GROUP_CONCAT() | +-+--+--+ My query looks roughly like this... SET @i:=0, @x:='', @row:=''; # DROPTABLE CLUSTERS; CREATE TABLE CLUSTERS (PRIMARY KEY (ID), INDEX (G_ID)) # SELECT ID, G_ID # FROM ( SELECT ID, # @x:= ATTR_LIST AS HIDDEN1, # IF(@row = @x, @i, @i:[EMAIL PROTECTED]) AS G_ID, # @row:= @x AS HIDDEN2 # FROM ( SELECT ID, GROUP_CONCAT(ATTRIBUTE) AS ATTR_LIST, FROM ATTRIBUTE_LIST GROUP BY ID # ) AS vt1 # ORDER BY -- This is very important for ATTR_LIST -- the overall query. # ) AS vt2; (And thats the highly simplified version!) I can't shake the feeling that this 'string based' approach (while quite speedy) is inherently messy, and that a proper 'set based' approach should exist, and shouldn't have the limitation in the number of attributes that the above method has. In general I would really like to (somehow) develop a suite of easy to use 'SQL CLUSTER' commands, as the data mining community needs that kind of thing in nice general (set based) abundance :) Anyway, thanks for any feedback on any of the above, Dan. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.15/49 - Release Date: 7/14/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql forgets user passwords
Chris Fonnesbeck wrote: I have mysql 4.1.12 installed on OSX 10.4, and have run into the curious problem that mysql forgets my user password (but not my root password) when I restart the server. When I attempt to log in, I get: ERROR 1045 (28000): Access denied for user 'chris'@'localhost' (using password: YES) Yet, when I go in as root and re-grant permissions with the user password, access is restored. When I reboot, I get the error again. What could possibly be causing this? Thanks, Chris Fonnesbeck Sorry again ... forgot the mailing group ... Did you 'flush privileges'? Danny Stolle Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED applicable to MyISAM tables?
Thanks for your reply. Should we move this discussion to [EMAIL PROTECTED] Also, notice that unless your application is under _extreme_ load, none of these SET queries will are likely to have an impact on the performance of your application. I was hoping someone would reply saying that SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED would be ignored for MyISAM tables. I may open an official support call to verify. If you're using a newer version of our JDBC driver (3.1.x), you can always add useLocalSessionState=true to avoid having to do _some_ of these queries to the database. I installed the 3.1.10 driver and tried that but I can not tell a difference and don't know how to verify. I submitted it to the coldfusion database access forum for help. I noticed the MySQL Connector/J Documentation for useLocalSessionState says... Should the driver refer to the internal values of autocommit and transaction isolation that are set by Connection.setAutoCommit() and Connection.setTransactionIsolation(), rather than querying the database? Can you elaborate any more on this? From the description it makes it sound like it wouldn't set autocommit or session transaction isolation at all. Then again, I wonder if it is saying that yes it will set them both, it just will not query the database to check its setting before it does (getting rid of show variables?). Humm. Note-I also set the global TRANSACTION ISOLATION LEVEL READ COMMITTED thinking that if it was already set at the global level then it would not be set at the session level (after the show variables). But that didn't help. Thanks, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with float() fields during migration to MySql 5
Hello. This weird behavior is very similar to described at: http://bugs.mysql.com/bug.php?id=7361 Nico Alberti [EMAIL PROTECTED] wrote: Hi everybody. During the migration of our mysql test server to version 5 I noticed a problem when I tried to import a table that I dumped from our 4.1 production machine. The table has some fields defined as float(31,30) (they came from an old Access table converted with DBTools Manager). When I import the dump into the test machine, each field is either 10 or null. If the field is defined simply as float, the import goes smoothly. I confess I am not a great dba. I tried to look at the documentazion looking for some hint, but I had lo luck. Can anybody explain this strange (at least for me) behaviour? --=20 Ciao Nico -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on Indices
Hello. it be optimal to use primary key + non-unique key combination to narrow down the search? According to: http://dev.mysql.com/doc/mysql/en/mysql-indexes.html Suppose that you issue the following SELECT statement: mysql SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2; If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer tries to find the most restrictive index by deciding which index finds fewer rows and using that index to fetch the rows. The same, I think, could be applied to '' expressions. That means optimize could use only one index (or several leftmost prefixes from one composite index). So, probably, having one composite index is better choice. Manoj [EMAIL PROTECTED] wrote: Greetings, I am trying to get a feel of how MySQL would handle certain types of situation, mainly concerning the usage of indices. Say I have two exactly identical table structures namely table A B. For table A, I just have one composite primary key on (Code, Date1 Date2). For table B, I just have composite primary key on (Code, Date1 Date2) and two non-unique keys on Date1 Date2. Question is, Will I find any performance improvement with table B, If all my queries start with Code field ? In other words : Will there be any noticiable difference in speed for query Select * from tableB where code =3D cd and dt1 $one_year_ago ove= r the same query on tableA ? Kindly note that we might have 1000 or more records for each code. When I tried to do an explain, it suggested in both the cases (on tableA on tableB) that It will use composite primary key (mainly because left most field in the primary index is avaliable)suggesting that the non-unique indices are pretty much a waste of space...Is that really the case? Woudln'= t it be optimal to use primary key + non-unique key combination to narrow down the search? I would greatly appreciate your insight in this issue. TIA Manoj -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql forgets user passwords
Danny Stolle wrote: Chris Fonnesbeck wrote: I have mysql 4.1.12 installed on OSX 10.4, and have run into the curious problem that mysql forgets my user password (but not my root password) when I restart the server. When I attempt to log in, I get: ERROR 1045 (28000): Access denied for user 'chris'@'localhost' (using password: YES) Yet, when I go in as root and re-grant permissions with the user password, access is restored. When I reboot, I get the error again. What could possibly be causing this? Thanks, Chris Fonnesbeck Sorry again ... forgot the mailing group ... Did you 'flush privileges'? Danny Stolle Netherlands Danny, First, FLUSH PRIVILEGES is not needed with GRANT. Second, if he were editing the user table instead of using GRANT and failing to FLUSH PRIVILEGES, he would get the opposite behavior -- the login would not work before the restart, but would work after. Chris, One possibility is a startup script which is altering the user table. Another possibility is some error in granting permissions or restarting the server, or logging in. It is difficult to say without knowing more. Please show us * the GRANT command you use to create 'chris'@'localhost' (but don't show us the real password) * the output of SHOW GRANTS FOR 'chris'@'localhost' when it is working (before a restart). * the method you use to restart the server * the output of SHOW GRANTS FOR 'chris'@'localhost' when it isn't working (after the restart). Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 4.0 to 4.1 migration and charset problems
the version is 4.1.12. show variables like this, | 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 | /usr/share/mysql/charsets/ | collation_connection| latin1_swedish_ci | collation_database | latin1_swedish_ci | collation_server| latin1_swedish_ci If the table is like test(name char(30), id1 int(4), id2 int(4)) When I use mysqldump mysql version 3.23.??, l have the flat file row length is 30+4+4=38. When I use mysqldump mysql verson 4.1.12, I have the flat file row length is 30+11+11=52. I tried mysqldump --set-charset=latin2, I still got a row length=52. What I should do? Gleb Paharenko wrote: Hello. I've tested your solution. It doesn't work for users which have SUPER privilege. This mentioned at: http://dev.mysql.com/doc/mysql/en/server-system-variables.html However, it works with with ordinary users which don't have SUPER privilege. Here are pieces of my my.cnf (the init_connect is one big string without line breaks): [client] default_character_set=latin1 [mysqld] default_character_set=latin2 init_connect='SET @lchar = IF(@@session.character_set_client = _utf8latin1, @@global.character_set_client, @@session.character_set_client); set @@[EMAIL PROTECTED]; set @@[EMAIL PROTECTED]; set @@[EMAIL PROTECTED]; ' When root user connects init_connect doesn't execute and we see: mysql show variables like '%char%'; +--+---+ | Variable_name| Value | +--+---+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin2 | | character_set_results| latin1 | | character_set_server | latin2 | | character_set_system | utf8 | | When user without SUPER privilege connects we see: | Variable_name| Value | +--+---+ | character_set_client | latin2 | | character_set_connection | latin2 | | character_set_database | latin2 | | character_set_results| latin2 | | character_set_server | latin2 | | character_set_system | utf8 | So it works for me. http://dev.mysql.com/doc/mysql/en/mysqldump.html Dump restore was done properly. dump on 4.0, add set names latin2; load i= nto=20 4.1. The problem is that by default connections from client are as latin1, = db=20 is latin2 so servers needs to do conversion from latin2-latin1 which can't= =20 be done and thus I'm getting '?' characters instead of latin2 characters. The thing I need is how to force default latin2 in all client connections e= ven=20 if client won't request latin2 by using set names. Tried doing things like in mysqld.conf: init-connect =3D SET @lchar =3D IF(@@session.character_set_client =3D _utf8= latin1,=20 @@global.character_set_client, @@session.character_set_client); SET=20 character_set_client =3D @lchar; SET character_set_results =3D @lchar; SET= =20 character_set_connection =3D @lchar; but that doesn't work unfortunately from init-connect (works from mysql=20 cmdline client) ;-( =2D-=20 Arkadiusz Mi=B6kiewiczPLD/Linux Team http://www.t17.ds.pwr.wroc.pl/~misiek/ http://ftp.pld-linux.org/
Re: Problem with some querys
Roberto, The problem came when I want to show all computers and I g oto the id 1 for show all and any product have stored this CAT_ID, because all have the last subcategory id. You've coded tree-like relationships between 'cat_id' and 'relation' in your categories table, but raw SQL doesn't do recursion--outside stored routines, it doesn't have a construct for a loop which stops after a data-determined number of iterations. It seems to me you have two solutions. One, break out your recursive cat_id-relation relationship into multiple lookup tables, eg computertypes(1=notebooks,2=tablets,c), manufacturers(1=IBM,2=HP,c). This will simplify your queries enormously. Two, somewhat harder, possible only in 5.0.4 and later, but made more difficult by bugs remaining in 5.0.7, treat your products table as a nodes table, and your categories table as an edges table, and write stored procedures to traverse your relationship tree. If you feel you must go for this option, I can send you a copy of a recent chapter of ours on doing this in MySQL. PB - Roberto Rodrguez Garrido wrote: Hi, Im programming an online shop, but I have a big problem with categories, I detail the database structure: Categories: CAT_ID | CAT_NAME | RELATION 1| Computers | 0 2| Notebooks | 1 3| Tablets| 1 4| Notebooks IBM | 2 5| Notebooks HP | 2 PRODUCTS: PROD_ID | PROD_NAME | CAT_ID 1 | Notebook HP 102 | 5 2 | Notebook HP 103 | 5 Like you can see in the products table I store the ID of a category that have some relations in its table: Id 5 - Notebooks HP - Id 2 Notebooks - Id 1 Computers The problem came when I want to show all computers and I g oto the id 1 for show all and any product have stored this CAT_ID, because all have the last subcategory id. Can you help me to know how to do that? Thanks for all. No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.15/49 - Release Date: 7/14/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.15/49 - Release Date: 7/14/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Null alphabetic order
Mark Leith wrote: From: Michael Stassen [mailto:[EMAIL PROTECTED] snip Something like ORDER BY IF(col IS NULL, 1, 0), col Michael Or simply: ORDER BY col IS NULL, col Which will probably be *slightly* faster.. Good point. Михаил Монашёв wrote: Hello MS Something like MSORDER BY IF(col IS NULL, 1, 0), col it's very slowly. Maybe better create 'col', and make index after? Sincerely, Михаил Монашёв, I don't think an index will help here, as the first part of the ORDER BY, col IS NULL, causes a filesort. If there is an index on col, and if the query is one that would otherwise use it, this might be faster (SELECT col FROM mytable WHERE col IS NOT NULL ORDER BY col) UNION ALL (SELECT col FROM mytable WHERE col IS NULL); but it's a little silly. The real query no doubt selects other columns, and has WHERE conditions on other columns, so mysql will probably not use an index on col to order the results anyway. Perhaps a multi-column covering index would help, depending on the specifics of the real query. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql forgets user passwords
Hello. What does show grants for 'chris'@'localhost'; reports when you're logged as root? Chris Fonnesbeck [EMAIL PROTECTED] wrote: I have mysql 4.1.12 installed on OSX 10.4, and have run into the curious problem that mysql forgets my user password (but not my root password) when I restart the server. When I attempt to log in, I get: ERROR 1045 (28000): Access denied for user 'chris'@'localhost' (using password: YES) Yet, when I go in as root and re-grant permissions with the user password, access is restored. When I reboot, I get the error again. What could possibly be causing this? Thanks, Chris Fonnesbeck -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql forgets user passwords
On 7/14/05, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. What does show grants for 'chris'@'localhost'; reports when you're logged as root? I get the following: | GRANT ALL PRIVILEGES ON *.* TO 'chris'@'localhost' IDENTIFIED BY PASSWORD '3446cb892d3dffdd' WITH GRANT OPTION | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql forgets user passwords
Tried that. I get the following: Oliver:~/Research/Right Whale chris$ mysql mysql -u root -p Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 to server version: 4.1.12-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql flush privileges; Query OK, 0 rows affected (0.11 sec) mysql Bye Oliver:~/Research/Right Whale chris$ mysql mysql -p Enter password: ERROR 1045 (28000): Access denied for user 'chris'@'localhost' (using password: YES) On 7/14/05, Danny Stolle [EMAIL PROTECTED] wrote: Chris Fonnesbeck wrote: I have mysql 4.1.12 installed on OSX 10.4, and have run into the curious problem that mysql forgets my user password (but not my root password) when I restart the server. When I attempt to log in, I get: ERROR 1045 (28000): Access denied for user 'chris'@'localhost' (using password: YES) Yet, when I go in as root and re-grant permissions with the user password, access is restored. When I reboot, I get the error again. What could possibly be causing this? Thanks, Chris Fonnesbeck Sorry again ... forgot the mailing group ... Did you 'flush privileges'? Danny Stolle Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql forgets user passwords
Michael Stassen wrote: Danny Stolle wrote: Chris Fonnesbeck wrote: I have mysql 4.1.12 installed on OSX 10.4, and have run into the curious problem that mysql forgets my user password (but not my root password) when I restart the server. When I attempt to log in, I get: ERROR 1045 (28000): Access denied for user 'chris'@'localhost' (using password: YES) Yet, when I go in as root and re-grant permissions with the user password, access is restored. When I reboot, I get the error again. What could possibly be causing this? Thanks, Chris Fonnesbeck Sorry again ... forgot the mailing group ... Did you 'flush privileges'? Danny Stolle Netherlands Danny, First, FLUSH PRIVILEGES is not needed with GRANT. Second, if he were editing the user table instead of using GRANT and failing to FLUSH PRIVILEGES, he would get the opposite behavior -- the login would not work before the restart, but would work after. Chris, One possibility is a startup script which is altering the user table. Another possibility is some error in granting permissions or restarting the server, or logging in. It is difficult to say without knowing more. Please show us * the GRANT command you use to create 'chris'@'localhost' (but don't show us the real password) * the output of SHOW GRANTS FOR 'chris'@'localhost' when it is working (before a restart). * the method you use to restart the server * the output of SHOW GRANTS FOR 'chris'@'localhost' when it isn't working (after the restart). Michael Aah I get the picture, thanx Michael. Danny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql forgets user passwords
Here is the startup script:
#!/bin/sh
#
# /Library/StartupItems/MySQLCOM/MySQLCOM
#
# A script to automatically start up MySQL on system bootup
# for Mac OS X. This is actually just a wrapper script around
# the standard mysql.server init script, which is included in
# the binary distribution.
#
# (c) 2003 MySQL AB
# Written by Lenz Grimmer [EMAIL PROTECTED]
#
# Suppress the annoying $1: unbound variable error when no option
# was given
if [ -z $1 ] ; then
echo Usage: $0 [start|stop|restart]
exit 1
fi
# Source the common setup functions for startup scripts
test -r /etc/rc.common || exit 1
. /etc/rc.common
# The path to the mysql.server init script. The official MySQL
# Mac OS X packages are being installed into /usr/local/mysql.
SCRIPT=/usr/local/mysql/support-files/mysql.server
StartService ()
{
if [ ${MYSQLCOM:=-NO-} = -YES- ] ; then
ConsoleMessage Starting MySQL database server
$SCRIPT start /dev/null 21
fi
}
StopService ()
{
ConsoleMessage Stopping MySQL database server
$SCRIPT stop /dev/null 21
}
RestartService ()
{
ConsoleMessage Restarting MySQL database server
$SCRIPT restart /dev/null 21
}
if test -x $SCRIPT ; then
RunService $1
else
ConsoleMessage Could not find MySQL startup script!
fi
The grant command was:
grant all on *.* to [EMAIL PROTECTED] identified by 'my_password';
Thanks for the help,
C.
On 7/14/05, Danny Stolle [EMAIL PROTECTED] wrote:
Michael Stassen wrote:
Danny Stolle wrote:
Chris Fonnesbeck wrote:
I have mysql 4.1.12 installed on OSX 10.4, and have run into the
curious problem that mysql forgets my user password (but not my root
password) when I restart the server. When I attempt to log in, I get:
ERROR 1045 (28000): Access denied for user 'chris'@'localhost' (using
password: YES)
Yet, when I go in as root and re-grant permissions with the user
password, access is restored. When I reboot, I get the error again.
What could possibly be causing this?
Thanks,
Chris Fonnesbeck
Sorry again ... forgot the mailing group ...
Did you 'flush privileges'?
Danny Stolle
Netherlands
Danny,
First, FLUSH PRIVILEGES is not needed with GRANT. Second, if he were
editing the user table instead of using GRANT and failing to FLUSH
PRIVILEGES, he would get the opposite behavior -- the login would not
work before the restart, but would work after.
Chris,
One possibility is a startup script which is altering the user table.
Another possibility is some error in granting permissions or restarting
the server, or logging in. It is difficult to say without knowing
more. Please show us
* the GRANT command you use to create 'chris'@'localhost' (but don't
show us
the real password)
* the output of SHOW GRANTS FOR 'chris'@'localhost' when it is working
(before a restart).
* the method you use to restart the server
* the output of SHOW GRANTS FOR 'chris'@'localhost' when it isn't working
(after the restart).
Michael
Aah I get the picture, thanx Michael.
Danny
--
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 5.0.9-beta has been released
Hi, MySQL 5.0.9-beta, a new version of the popular Open Source Database Management System, has been released. It includes support for Stored Procedures, Triggers, Views and many other new enhancements. The Community Edition is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up-to-date at this point. If you cannot find this version on a particular mirror, please try again later or choose another download site. This is the fifth published Beta release in the 5.0 series. All attention will continue to be focused on fixing bugs and stabilizing 5.0 for later production release. Version 5.0.8-beta was not published, so its changes are included in this announcement. NOTE: This Beta release, as any other pre-production release, should not be installed on ``production'' level systems or systems with critical data. It is good practice to back up your data before installing any new version of software. Although MySQL has done its best to ensure a high level of quality, protect your data by making a backup as you would for any software beta release. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual open and resolved bugs in this version. Changes in release 5.0.9 Functionality added or changed: * InnoDB: When creating or extending an InnoDB data file, at most one megabyte at a time is allocated for initializing the file. Previously, InnoDB allocated and initialized 1 or 8 megabytes of memory, even if only a few 16-kilobyte pages were to be written. This improves the performance of CREATE TABLE in innodb_file_per_table mode. * InnoDB: Various optimizations. Removed unreachable debug code from non-debug builds. Added hints for the branch predictor in GCC. Made assertions occupy less space. * InnoDB: Make innodb_thread_concurrency=20 by default. Bypass the concurrency checking if the setting is greater than or equal to 20. * InnoDB: Make CHECK TABLEkillable.(Bug#9730 (http://bugs.mysql.com/9730)) * Recursion in stored routines is now disabled because it was crashing the server. We plan to modify stored routines to allow this to operate safely in a future release. (Bug #11394 (http://bugs.mysql.com/11394)) * The handling of BIT columns has been improved, and should now be much morereliableina number of cases. (Bug #10617 (http://bugs.mysql.com/10617),Bug#11091(http://bugs.mysql.com/11091), Bug #11572 (http://bugs.mysql.com/11572)) Bugs fixed: * When used in joins, SUBSTRING() failed to truncate to zero any string values that could not be converted to numbers. (Bug #10124 (http://bugs.mysql.com/10124)) * mysqldump --xml did not format NULL column values correctly. (Bug #9657 (http://bugs.mysql.com/9657)) * There was a compression algorithm issue with myisampack for very large datasets (where the total size of of all records in a single column was on the order of 3 GB or more) on 64-bit platforms. (A fix for other platforms was made in MySQL 5.0.6.) (Bug #8321 (http://bugs.mysql.com/8321)) * Temporary tables were created in the data directory instead of tmpdir. (Bug #11440 (http://bugs.mysql.com/11440)) * MySQL would not compile correctly on QNX due to missing rint() function. (Bug #11544 (http://bugs.mysql.com/11544)) * A SELECT DISTINCT col_name would work correctly with a MyISAM table only when there was an index on col_name. (Bug #11484 (http://bugs.mysql.com/11484)) * The server would lose table-level CREATE VIEW and SHOW VIEW privileges following a FLUSH PRIVILEGES or server restart. (Bug #9795 (http://bugs.mysql.com/9795)) * In strict mode, an INSERT into a view that did not include a value for a NOT NULL column but that did include a WHERE test on the same column would succeed, This happened even though the INSERT should have been prevented due to the failure to supply a value for the NOT NULL column. (Bug #6443 (http://bugs.mysql.com/6443)) * Running a CHECK TABLES on multiple views crashed the server. (Bug #11337 (http://bugs.mysql.com/11337)) * When a table had a primary key containing a BLOB column, creation of another index failed with the error BLOB/TEXT column used in key specification without keylength, even when the new index did not contain a BLOB column. (Bug #11657 (http://bugs.mysql.com/11657)) * NDB Cluster: When trying to open a table that could not be discovered or unpacked, cluster would return error codes which the MySQL server falsely interpreted as operating system errors. (Bug #10365 (http://bugs.mysql.com/10365)) * Manually inserting a row with host='' into mysql.tables_priv and performing a FLUSH PRIVILEGES would cause the server to crash. (Bug #11330
select based letter
i have a text field column and i want to select the rows based on the first letter in this column, eg: - | topics | - Motherboard Hard Drives Memory Video Cards Monitors i want to select all the rows that begin with the letter M (Motherboard, Memory, Monitors) any suggestions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select based letter
on 7/14/05 3:54 PM, Sebastian at [EMAIL PROTECTED] wrote: i have a text field column and i want to select the rows based on the first letter in this column, eg: SELECT field from table WHERE field LIKE 'M%' -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DROP FUNCTION doesn't work
I can successfully LOAD a UDF in mysql-4.1.10, but I can't DROP it. MySQL reports that the DROP FUNCTION was OK, but the function still shows up in the mysql.func table. The function no longer works, but I have to TRUNCATE the mysql.func table in order to be able to LOAD it again. I am doing this with the root account so I don't think it's a privileges problem. See below: mysql create function betatouni returns string soname 'libbeta2.so'; Query OK, 0 rows affected (0.12 sec) mysql drop function betatouni; Query OK, 0 rows affected (0.00 sec) mysql select * from func; +---+-+-+--+ | name | ret | dl | type | +---+-+-+--+ | betatouni | 0 | libbeta2.so | function | +---+-+-+--+ 1 row in set (0.00 sec) mysql create function betatouni returns string soname 'libbeta2.so'; ERROR 1026 (HY000): Error writing file 'mysql.func' (errno: 121) Thanks for any help you can provide, Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored procedures and multi-queries: bug?
When you have a stored procedure that returns a result set, it seems that the server returns its results the same way as with a multi-query. As a result, if you don't set the multi-query option when setting up the connection with the C API, the server refuses to return the result set. You get this error: PROCEDURE foo can't return a result set in the given context If you set this flag in the mysql_real_connect() call, the stored procedure works fine. But if you set it with mysql_set_server_option(), only regular multi-queries work fine; stored procedures returning result sets still fail. According to this manual page: http://dev.mysql.com/doc/mysql/en/c-api-multiple-queries.html the two invocations should be equivalent. Due to the way my program is structured, it is highly inconvenient to set this flag in the real_connect call. I would much rather set it on the connection after it is established. Is there a good reason why the server behaves differently than the manual indicates, or is it a bug? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Switching legacy tables from 4.0 to 4.1 -AND- from default to UTF-8 .... ADVICE ANYONE?
I've got some years-old MySQL databases mostly in 4.0, but one server running 3.23 that are all using the default encoding. I want to update all their data to 4.1 with UTF-8 encoding. Anyone done this kind of dump-and-update?Any advice to share or good URLs you've seen with others' advice about this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedures and multi-queries: bug?
At 21:24 -0600 7/14/05, Warren Young wrote: When you have a stored procedure that returns a result set, it seems that the server returns its results the same way as with a multi-query. As a result, if you don't set the multi-query option when setting up the connection with the C API, the server refuses to return the result set. You get this error: PROCEDURE foo can't return a result set in the given context If you set this flag in the mysql_real_connect() call, the stored procedure works fine. But if you set it with mysql_set_server_option(), only regular multi-queries work fine; stored procedures returning result sets still fail. According to this manual page: http://dev.mysql.com/doc/mysql/en/c-api-multiple-queries.html the two invocations should be equivalent. They are equivalent -- for executing statements. But to retrieve results, you also need the CLIENT_MULTI_RESULTS flag in mysql_real_connect(). The CLIENT_MULTI_STATEMENTS flag automatically enables CLIENT_MULTI_RESULTS, which is why you see the behavior that you do. Due to the way my program is structured, it is highly inconvenient to set this flag in the real_connect call. I would much rather set it on the connection after it is established. Is there a good reason why the server behaves differently than the manual indicates, or is it a bug? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Consultancy for MySQL database optimization against substantial financial remuneration
Hi , We are a company specialized in developing tools for Internet Marketing domain. We are developing a product for which we need consultancy services from professionals speacilized in Mysql server optimization and tuning. Adequate financial remuneration will not be problem for effective solution provider. Below the nature of our application and problems faced are discussed in detail. We are developing an application that needs to use a massive back-end database. The database will contain around 75 million rows with around 80 columns per row. We would prefer to use MySQL as the database platform as it is free. The MySQL database is hosted on a dedicated server that has been purchased from a web hosting company. This database would be used both by our customers and by our own employees. The first column will contain some text which will be unique in each row. 90% of the remaining columns will containing numbers and the other columns will contain text. The second column will contain numbers and it needs to be updated on a monthly basis. But, we also need to store historical data regarding the value of the second column for each row for the last 24 months, on a rolling basis. This can either be done by adding more columns to the same table, or by putting this historical data in a separate table, depending on your recommendations. Users will make 2 types of queries on this database: i) The first type of query is what can be called a mission-critical query - these queries will be made by our customers and the results of these queries must be returned within 30 seconds at the most; otherwise, customers are not going to want to use the application. This query would basically involve asking the customer for a search string, searching the FIRST column (and ONLY the first column) of the entire database to find out each row that contains that search string (either in whole or in part) and then returning all such rows to the user sorted in descending order of the SECOND column. Only the information in the first 2 columns will be returned to the customers - the information in the other 78 columns will not be returned to the customers. Customers will also have the option of specifying negative matches - i.e. if the first column of a particular row contains any one of a list of banned words or phrases, then that row will not be returned even if it contained the primary search string. ii) The second type of queries are non-mission-critical; these would be run by our employees and it is ok if these queries take as much as 10 minutes to return results. However, the queries that our employees will run are also much more complex - they will specify multiple search criteria - for instance, return all rows for which the 60th column has a value 2000 and the minimum value for the columns 40, 41, ... 50 for that row is 20 and the 35th column of that row is 5 etc. It is quite possible that as many as 20 - 30 users will be querying the database at the same time. Furthermore, there will be 5 - 6 different PHP scripts that are going to constantly update the different columns and rows of the database with the values. Now we have hired a server with the following configuration: Server: Dual Xeon 2.8 GHz Secondary Processor: Second Xeon Processor Primary HDD: 73 GB SCSI Secondary HDD: None Third HDD: None RAM: ECC Registered 1024MB RAM Number of ips: 10 IP Addresses Bandwidth: 2000 GB Bandwidth Uplink Port Speed: 100 Mbps Uplink Database: MySQL 4.1.11-standard Backup Service: Network Backup Operating System: Red Hat Enterprise Linux, Version 3 Drive Controller: SCSI Chassis Control: DRAC Card We are executing certain queries but they are taking too long a time. Could you help us in tuning the SQL queries and the MySQL database such that the query time reduces and we get a fatser and more efficient database? Do let us know what is the timeframe required for this and also please indicate your charges for the same. Please send us your profile and details of some assignments that you have carried out. Awaiting your response, Regards Suryya Ghosh
MySQL 5.0.x
Just a question on upcoming MySQL 5.0.x releases...what the heck is going on? I see doc notes for 5.0.8, 5.0.9, and 5.0.10 all concurrently. Is the plan to merge all these together for a single 5.0.10 release? Or will there be a 5.0.11 that merges all these? Just curious, as it seems that this trend of multiple concurrent branches has really picked up steam in 5.0.x. Thanks, R
Tabls access only through procedures
Hi List, I belive version 5 does not allow me to grant access to execute procedures, but deny updates to tables directly? I am attempting to ensure that all data modification is done through procedures. Any tips or ideas when this will be available or work-arounds? Thanks, Terence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mail System Error - Returned Mail
The original message was received at Thu, 14 Jul 2005 11:44:49 +0300 from [34.202.52.137] - The following addresses had permanent fatal errors - mysql@lists.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question on Indices
Greetings, I am trying to get a feel of how MySQL would handle certain types of situation, mainly concerning the usage of indices. Say I have two exactly identical table structures namely table A B. For table A, I just have one composite primary key on (Code, Date1 Date2). For table B, I just have composite primary key on (Code, Date1 Date2) and two non-unique keys on Date1 Date2. Question is, Will I find any performance improvement with table B, If all my queries start with Code field ? In other words : Will there be any noticiable difference in speed for query Select * from tableB where code = cd and dt1 $one_year_ago over the same query on tableA ? Kindly note that we might have 1000 or more records for each code. When I tried to do an explain, it suggested in both the cases (on tableA on tableB) that It will use composite primary key (mainly because left most field in the primary index is avaliable)suggesting that the non-unique indices are pretty much a waste of space...Is that really the case? Woudln't it be optimal to use primary key + non-unique key combination to narrow down the search? I would greatly appreciate your insight in this issue. TIA Manoj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question on Indices
Greetings, I am trying to get a feel of how MySQL would handle certain types of situation, mainly concerning the usage of indices. Say I have two exactly identical table structures namely table A B. For table A, I just have one composite primary key on (Code, Date1 Date2). For table B, I just have composite primary key on (Code, Date1 Date2) and two non-unique keys on Date1 Date2. Question is, Will I find any performance improvement with table B, If all my queries start with Code field ? In other words : Will there be any noticiable difference in speed for query Select * from tableB where code = cd and dt1 $one_year_ago over the same query on tableA ? Kindly note that we might have 1000 or more records for each code. When I tried to do an explain, it suggested in both the cases (on tableA on tableB) that It will use composite primary key (mainly because left most field in the primary index is avaliable)suggesting that the non-unique indices are pretty much a waste of space...Is that really the case? Woudln't it be optimal to use primary key + non-unique key combination to narrow down the search? I would greatly appreciate your insight in this issue. TIA Manoj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with float() fields during migration to MySql 5
Hi everybody. During the migration of our mysql test server to version 5 I noticed a problem when I tried to import a table that I dumped from our 4.1 production machine. The table has some fields defined as float(31,30) (they came from an old Access table converted with DBTools Manager). When I import the dump into the test machine, each field is either 10 or null. If the field is defined simply as float, the import goes smoothly. I confess I am not a great dba. I tried to look at the documentazion looking for some hint, but I had lo luck. Can anybody explain this strange (at least for me) behaviour? -- Ciao Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.x
Hi Rick, all! Rick Robinson wrote: Just a question on upcoming MySQL 5.0.x releases...what the heck is going on? I see doc notes for 5.0.8, 5.0.9, and 5.0.10 all concurrently. Is the plan to merge all these together for a single 5.0.10 release? Or will there be a 5.0.11 that merges all these? Version 5.0.7 was published on June 15 and is available for download. Version 5.0.8 was built for internal use and was not published. So there are changes associated with exacrly that version. Version 5.0.9 is to be published soon, will then be available for download. Version 5.0.10 will contain changes which were not yet finished when the build of 5.0.9 was started (as well as those of 5.0.9 and earlier). Sure there will also be 5.0.11 etc. later. To state it explicitly: Within a release family (like 5.0), all versions are cumulative; any later version is based on its predecessor and then contains some new changes (documented in the manual). Just curious, as it seems that this trend of multiple concurrent branches has really picked up steam in 5.0.x. That is an impression you may well get, but it is wrong. It is most likely caused by a change in the release build process within MySQL: We now clone the current source when we start a release build. In this way, developers can continue to push changes, while there is a stable code base for the release build which even allows to integrate specific (= selected) changes, should they prove necessary. (Of course, any such changes will also be pushed into the ongoing development, they will not get lost.) However, the online manual is not cloned, so while we are building 5.0.9 there can also be new text for 5.0.10 changes that gets integrated into the online manual, and this may become visible earlier than 5.0.9 gets published. So we do not have concurrent branches within 5.0, but we are basing release builds on snapshots taken from ongoing development, while the online manual is outside these snapshots. Regards, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Office: (+49 30) 417 01 487 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to update a mysql table from access
Hey thanks for the link, yep, i guess i am going to have to write something up in python. (or maybe be lucky enough to find something i can modify from hotscripts :) . i may check out that piece called Navicat. saw some reviews of it and it may have what i need also. thanks again nephish On Thu, 2005-07-14 at 00:25 -0500, mos wrote: At 09:32 PM 7/13/2005, you wrote: you mean like in a script? the windows computer runs access, which i am not very familiar with and was able to accomplish what i have done so far by lots o' docs at the mysql.com site. sorry for the newbie-ness of this question. i am somewhat familliar with python, maybe there is a module i can use for this.. thanks, You can use any language you like. I would have assumed you would use the same language that you used to access your Access database. Something like Visual Basic., Delphi, PHP, Perl, Python etc..You could even use MySQL.exe and execute an SQL script on that for something that is quick and dirty, but I wouldn't recommend it because you have no error checking capability (in other words you have no way of knowing if the script succeeded or not). http://sourceforge.net/projects/mysql-python Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.x
On 7/14/05, Joerg Bruehe wrote: Rick Robinson wrote: However, the online manual is not cloned, so while we are building 5.0.9 there can also be new text for 5.0.10 changes that gets integrated into the online manual, and this may become visible earlier than 5.0.9 gets published. Why are the online manuals not cloned and versioned? One of the things I like about the documentation of most other databases compared to MySQL is that it is tightly coupled to a specific version of the software. Instead of searching through the documentation and having to find out which part is valid for which version on each page again, I can at the first step select the version of the database I am using and after that I only get information about that version. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to update a mysql table from access
If you have your native, auto-updated table in MS Access and a different Linked table pointing to the MySQL copy of it in the same database, just build an Access query that will INSERT or UPDATE (as appropriate) your linked table with data from your native table. No scripting required, just the internal data manipulation of Access. Consult the MS Access help files or any number of online resources for instructions on how to build a query in access that copies data from one table to another. Shawn Green Database Administrator Unimin Corporation - Spruce Pine nephish [EMAIL PROTECTED] wrote on 07/13/2005 10:32:19 PM: you mean like in a script? the windows computer runs access, which i am not very familiar with and was able to accomplish what i have done so far by lots o' docs at the mysql.com site. sorry for the newbie-ness of this question. i am somewhat familliar with python, maybe there is a module i can use for this.. thanks, On Wed, 2005-07-13 at 22:10 -0500, mos wrote: At 08:51 PM 7/13/2005, you wrote: Hey there, thanks to some help i have received right here, i have been able to access a mysql database on a linux computer from MS access on a windows computer, i was able to connect and create the tables and export all rows correctly.. i used MyODBC from mysql. ok, here is the deal, the access database gets info from another program and adds new rows every 15 seconds or so, i need some automated way to sync the two databases together every oh,,, 5 minutes or so. there is lots of documentation on how to do this by linking a table to a mysql table, however, when i do this, the access table is the one that gets updated, not the mysql table (deletes any info received since last update) and still does not provide a way to do this automatically. I cant find way to do it anywhere, little or no docs.. or i am looking in the wrong place. thanks for reading this, hope someone can help Have you tried prefixing the MySQL table with the MySQL database name? Example: select * from MySqlDb1.Table1; insert into MySqlDb1.Table1 (cust_id, cust_name) values(1,John Smith); Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
