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: 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: 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 Paka programtor, 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: 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: 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: 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: 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: 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? lt;tablenamegt;.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: 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 that are under Excel limitations Regards, Mikhail Berman From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, April 28, 2005 11:41 AM To: Berman, Mikhail Cc: mysql@lists.mysql.com Subject: RE: Does anyone have experience? Berman, Mikhail [EMAIL PROTECTED] wrote on 04/28/2005 10:50:17 AM: Yes, I do test DSN connection itself. It returns Success. Connection was made. I am creating User DSN. That's
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: 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: 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: 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: 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 datadir_path/*.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: mysql@lists.mysql.com 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: 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: mysql@lists.mysql.com 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: 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: 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: 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: 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
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: 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: 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?
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: 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: 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
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, 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
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 snip 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: 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: 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: 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: 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]
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
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: 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: 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: 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: 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:\Mysqlmysql -u mathias world -X -e desc country
?xml version=1.0?
resultset statement=desc country
row
FieldCode/Field
Typechar(3)/Type
Null/Null
KeyPRI/Key
Default/Default
Extra/Extra
/row
row
FieldName/Field
Typechar(52)/Type
Null/Null
Key/Key
Default/Default
Extra/Extra
/row
row
FieldContinent/Field
...
...
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:\Mysqlperl describe.pl
?xml version=1.0?
DBI driver=DBI:mysql:database=world;host=localhost
RESULTSET statement=describe country
ROW
FieldCode/Field
Typechar(3)/Type
Null/Null
KeyPRI/Key
Default/Default
Extra/Extra
/ROW
ROW
FieldName/Field
Typechar(52)/Type
Null/Null
Key/Key
Default/Default
Extra/Extra
/ROW
ROW
...
...
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 \table name=\$table\\\n;
while (my @ref = $sth-fetchrow_array()) {
print \column name=\$ref[0]\ required=\true\
type=\$ref[1]\;
print primaryKey=\true\ if ($ref[3] eq PRI) ;
print /\\n;
}
$sth-finish();
print \/table\\n;
# Disconnect from the database.
$dbh-disconnect();
C:\Mysqlperl desc.pl country
table name=country
column name=Code required=true type=char(3) primaryKey=true/
column name=Name required=true type=char(52)/
column name=Continent required=true
type=enum('Asia','Europe','North
America','Africa','Oceania','Antarctica','South America')/
column name=Region required=true type=char(26)/
column name=SurfaceArea required=true type=float(10,2)/
column name=IndepYear required=true type=smallint(6)/
column name=Population required=true type=int(11)/
column name=LifeExpectancy required=true type=float(3,1)/
column name=GNP required=true type=float(10,2)/
column name=GNPOld required=true type=float(10,2)/
column name=LocalName required=true type=char(45)/
column name=GovernmentForm required=true type=char(45)/
column name=HeadOfState required=true type=char(60)/
column name=Capital required=true type=int(11)/
column name=Code2 required=true type=char(2)/
/table
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:
table name=ServiceType
column name=idTipoServicio primaryKey=true
required=true type=VARCHAR size=10/
column name=nombre required=true type=VARCHAR
size=255/
column name=costo required=true type=FLOAT size=9/
column name=idGrupo required=true type=INTEGER/
column name=activa required=true type=BOOLEANINT/
foreign-key foreignTable=Grupo onUpdate=none
onDelete=none
reference foreign=idGrupo local=idGrupo/
/foreign-key
/table
This XML is the structure of the ServiceType table, I'll hope that
youcan
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
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\datanet stop mysql41 Le service MySQL41 s'arrête. Le service MySQL41 a été arrêté. C:\Program Files\MySQL\MySQL Server 4.1\datanet 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: 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\datanet stop mysql41 Le service MySQL41 s'arrête. Le service MySQL41 a été arrêté. C:\Program Files\MySQL\MySQL Server 4.1\datanet 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
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\datanet stop mysql41 Le service MySQL41 s'arrête. Le service MySQL41 a été arrêté. C:\Program Files\MySQL\MySQL Server 4.1\datanet 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
