xml to mySQL
Hi All, I wanted to know the possible ways in which an xml document can be stored in the mysql database. So the keys in an xml are the columns in the database and the key values in xml go as the values for columns. I dont want to use java if thats possible. Thanks in advance, Adhiraj Joshi.
Re: Ouch! ibdata files deleted. Why no catastrophe?
How do I recover them, and do you think this is wise? At this point, I still think it might be a better idea to do a complete reinstall / restore / transaction log run. There's no need to reinstall :-) It's not MS Windows, it's just InnoDB. As others have said, I'd try to do a global LOCK TABLES (I wouldn't do a FLUSH TABLES because I'm not sure how missing files might be handled -- it could crash) and a full dump. Then just shut down MySQL and delete *all* the InnoDB files and let it initialize with fresh files on restart, and import. You should be fine. But I'd do it ASAP because in the meantime you could have a crash. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
5.1 cuts selects in Logfile
In MySQL 5.1.20 (the version we're actually testing=) a sql-statement longer than 1024 byte is cut in the general_log.csv and in the old Logfile so it seems to be incomplete. Execution and replication of the statement is working right but I don't think it's a feature though it's really irritating when examining the logfile. Greetz from Germany Lutz Maibach
Re: Ouch! ibdata files deleted. Why no catastrophe?
Hi Baron, If the database is huge, the restoring from mysqldump would take lot of time. Is there any other alternative. egards anandkl On 9/26/07, Baron Schwartz [EMAIL PROTECTED] wrote: How do I recover them, and do you think this is wise? At this point, I still think it might be a better idea to do a complete reinstall / restore / transaction log run. There's no need to reinstall :-) It's not MS Windows, it's just InnoDB. As others have said, I'd try to do a global LOCK TABLES (I wouldn't do a FLUSH TABLES because I'm not sure how missing files might be handled -- it could crash) and a full dump. Then just shut down MySQL and delete *all* the InnoDB files and let it initialize with fresh files on restart, and import. You should be fine. But I'd do it ASAP because in the meantime you could have a crash. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ouch! ibdata files deleted. Why no catastrophe?
I cannot think of any safe alternative. Trying to recover files that are in memory strikes me as very unwise with InnoDB. Ananda Kumar wrote: Hi Baron, If the database is huge, the restoring from mysqldump would take lot of time. Is there any other alternative. egards anandkl On 9/26/07, Baron Schwartz [EMAIL PROTECTED] wrote: How do I recover them, and do you think this is wise? At this point, I still think it might be a better idea to do a complete reinstall / restore / transaction log run. There's no need to reinstall :-) It's not MS Windows, it's just InnoDB. As others have said, I'd try to do a global LOCK TABLES (I wouldn't do a FLUSH TABLES because I'm not sure how missing files might be handled -- it could crash) and a full dump. Then just shut down MySQL and delete *all* the InnoDB files and let it initialize with fresh files on restart, and import. You should be fine. But I'd do it ASAP because in the meantime you could have a crash. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Baron Schwartz Xaprb LLC http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ouch! ibdata files deleted. Why no catastrophe?
Hi Baron, If the database is huge, the restoring from mysqldump would take lot of time. Is there any other alternative. Well, not deleting the InnoDB files would be a good start. The files were deleted, what do you expect?? Just continue running and hoping no-one finds out? Re-creating them sure sounds the safest option to me! Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to use an hardcoded list of values
Hi all, I have a list of values I get from my application and I want to generate a record set based on this list (for an INSERT INTO ... SELECT). Today I have sth like: SELECT 54, item.item_id, mdv.valueInt FROM item i, metadata_value mdv WHERE mdv.item_id = 20202 AND mdv.field_id = 54 AND i.item_id IN (20203,20204,20205,20206,20223,20207,20208); Is it possible not to make a look-up in 'item' table ? -- Manuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Ouch! ibdata files deleted. Why no catastrophe?
I don't know Linux that well, but I know that HP-UX lets you do most anything to an open file, including deleting it. The file will continue to exist as long as it is open by at least one process. If I remember correctly, this is all about the inode use count vs. the directory entry, or something like that. The file will actually be safe so long as it is not closed by the last user. Those files will go away as soon as they are closed, so the database should be dumped before shutting down mysqld. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 26, 2007 10:07 AM To: Ananda Kumar; Baron Schwartz Cc: Gary Josack; Daniel Kasak; mysql@lists.mysql.com Subject: Re: Ouch! ibdata files deleted. Why no catastrophe? Hi Baron, If the database is huge, the restoring from mysqldump would take lot of time. Is there any other alternative. Well, not deleting the InnoDB files would be a good start. The files were deleted, what do you expect?? Just continue running and hoping no-one finds out? Re-creating them sure sounds the safest option to me! Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- 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: How to use an hardcoded list of values
Hi, Manuel Vacelet wrote: Hi all, I have a list of values I get from my application and I want to generate a record set based on this list (for an INSERT INTO ... SELECT). Today I have sth like: SELECT 54, item.item_id, mdv.valueInt FROM item i, metadata_value mdv WHERE mdv.item_id = 20202 AND mdv.field_id = 54 AND i.item_id IN (20203,20204,20205,20206,20223,20207,20208); Is it possible not to make a look-up in 'item' table ? I'm not sure I understand, but perhaps you want this: SELECT 54, x.item_id, (SELECT valueInt FROM metadata_value WHERE mdv.item_id = 20202) AS valueInt FROM ( SELECT 20203 AS item_id UNION ALL SELECT 20204 UNION ALL SELECT 20205 UNION ALL SELECT 20206 UNION ALL SELECT 20223 UNION ALL SELECT 20207 UNION ALL SELECT 20208 ) AS x; I don't think you're gaining anything by doing this though, unless it is extremely expensive to do a lookup in item. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Crystal Reports XI on W2k3 Server SP2 x64
Hi All, I'm trying to get Crystal Reports to connect to a MySQL server. Windows 2003 Server R2 x64 Crystal Reports XI Professional Disabled DEP for crw32.exe mysql-connector-odbc-noinstall-3.51.20-winx64.zip FreeBSD 6.2-RELEASE-p7 x64 mysql-client-5.0.45 mysql-scripts-5.0.45 mysql-server-5.0.45 GRANT SELECT ON db.* TO 'user'@'apps.domain.tld' identified by 'x'; (note, this is not an authentication problem) 1) I created a SystemDSN via the ODBC Panel and it tests okay -- Crystal Reports can't see it -- this is a seperate PR I need to file with them. 2) I created a UserDSN which Crystal Reports can see BUT IM002:[Microsoft][ODBC Driver Manger] Data source name not found and no default driver specified I've also tried the full odbc connect string only to get the same error message. I filed the following PR with Business Objects (owns Crysal Reports) http://support.businessobjects.com/case_management/viewcase.asp?caseid=302861271; The long and the short is they want me to use 3.51.11! The big question, how do I get this version which is so old its no longer distributed. From googling, I have a hunch they are right but I can't fathom why. -- Philip M. Gollucci ([EMAIL PROTECTED]) 323.219.4708 Senior System Admin - Riderway, Inc. http://riderway.com 1024D/EC88A0BF 0DE5 C55C 6BF3 B235 2DAB B89E 1324 9B4F EC88 A0BF Work like you don't need the money, love like you'll never get hurt, and dance like nobody's watching. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What is MYSQL's equivalent to Oracle's DBMS_OUTPUT
Oracle provides a stored procedure called DBMS_OUTPUT which primarily is used to write/print/display text string to StandardOut (a.k.a. the terminal). In V5 MYSQL is there a functional equivalent? If so, what is it called. I am willing to RTFM if somebody provides me a clue as to which manual contains the answer to my question. I have Guy Harrison's MYSQL Stored Procedures but could not find what I'm looking for in it. Since I am not sure if what I want exists or what it may be called, I just may be looking in the wrong places for the answer. TIA!
Re: xml to mySQL
Hi Adhiraj, I am going to assume that the keys and values are not static. If they are, then this is just a matter of parsing XML into key, value combinations. A simple, but often less than ideal way of solving a problem like this is to use several tables: # The entries table is the equivilant of something like the prymary key... CREATE TABLE `entries` ( `entry_id` int(10) NOT NULL auto_increment, `entry_name` varchar(255) NOT NULL default '', PRIMARY KEY (`entry_id`), KEY `entry_name` (`entry_name`) ) TYPE=MyISAM AUTO_INCREMENT=2 ; # The keys table holds collumn names... CREATE TABLE `keys` ( `key_id` int(10) NOT NULL auto_increment, `key` varchar(255) default NULL, PRIMARY KEY (`key_id`), KEY `key` (`key`) ) TYPE=MyISAM AUTO_INCREMENT=3 ; # The values table would hold the data... CREATE TABLE `values` ( `value_id` int(10) NOT NULL auto_increment, `value` varchar(255) default NULL, `key_id` int(10) default NULL, `entry_id` int(10) default NULL, PRIMARY KEY (`value_id`), KEY `value` (`value`),KEY `key_id` (`key_id`),KEY `entry_id` (`entry_id`) ) TYPE=MyISAM AUTO_INCREMENT=3 ; #Add in some data INSERT INTO `entries` (`entry_id`, `entry_name`) VALUES (1, 'Rob'); INSERT INTO `keys` (`key_id`, `key`) VALUES (1, 'age'), (2, 'nationality'); INSERT INTO `values` (`value_id`, `value`, `key_id`, `entry_id`) VALUES (1, '23', 1, 1), (2, 'American', 2, 1); # And now you can pull out information: SELECT `entry_name` ,`key`,`value` FROM `entries` INNER JOIN `values` USING(`entry_id`) INNER JOIN `keys` USING(`key_id`) WHERE `entry_name` = 'Rob' So, whenever you insert xml into the db you will first insert a single record into the entries table, and store the last insert id. You would then go through each key/value combination, first selecting ( and if no results are found, inserting) the relevant key from the keys table, and then inserting the key_id, entry_id and value into the values table. On 9/26/07, Adhiraj Joshi [EMAIL PROTECTED] wrote: Hi All, I wanted to know the possible ways in which an xml document can be stored in the mysql database. So the keys in an xml are the columns in the database and the key values in xml go as the values for columns. I dont want to use java if thats possible. Thanks in advance, Adhiraj Joshi. -- Rob Wultsch (480)223-2566 [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is MYSQL's equivalent to Oracle's DBMS_OUTPUT
Is this for debugging purposes? Olaf On 9/26/07 12:37 PM, sol beach [EMAIL PROTECTED] wrote: Oracle provides a stored procedure called DBMS_OUTPUT which primarily is used to write/print/display text string to StandardOut (a.k.a. the terminal). In V5 MYSQL is there a functional equivalent? If so, what is it called. I am willing to RTFM if somebody provides me a clue as to which manual contains the answer to my question. I have Guy Harrison's MYSQL Stored Procedures but could not find what I'm looking for in it. Since I am not sure if what I want exists or what it may be called, I just may be looking in the wrong places for the answer. TIA! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ayuda con query
Marcelo, puedes utilizar if anidados en el select. Te quedaria mas o menos asi: select Grupo_servicio, if((hora_asigancion – hora_registro =0) (hora_asigancion – hora_registro 5), Restriccion 1, -- si es cierto if((hora_asigancion – hora_registro =5) (hora_asigancion – hora_registro 6), -- si es falso Restriccion 2, -- si es cierto -- aca va anidadno las demas restricciones )) as restriccion, count(*) as total from tb_datos_planos where minute(hora_asignacion)0 en el último comentario colocas las demás restricciones respetando los paréntesis. Eso te va a dar ua consulta como : gruposervicio restriccion total gruposervicioX RestriccionN XX . . . Después puedes manipular esos datos con algún script y los manipulas como quieras. Saludos, Gilberto. Ing. Marcelo Rodriguez escribió: Buenos días Grupo a los tiempos vuelvo a interactuar pero es que mis neuronas no dan mas con este query, bien resumo tengo una tabla llamada tb_datos_planos y los siguientes campos con los cuales trabajo : Grupo_servicio Hora_asignacion Hora_registro Expediente Y las siguientes restricciones 1. hora_asigancion – hora_registro =0 hora_asigancion – hora_registro 5 2. hora_asigancion – hora_registro =5 hora_asigancion – hora_registro 6 3. hora_asigancion – hora_registro =6 hora_asigancion – hora_registro 7 4. hora_asigancion – hora_registro =7 hora_asigancion – hora_registro 8 5. hora_asigancion – hora_registro =8 Además debo de restringir que el conteo que debo de realizar solo sea con los registros que tengan minute(hora_asignacion)0 Con todas estas restricciones Necesito presentar o armar una matriz mas o menos asi 1 restriccion 2 restriccion 3 restrcion ……… n restricción grupo_servicio grupo_servicio grupo_servicio . . . .. n grupo_servicio En donde el nombre de cada columna es la restricción, en donde en cada posición se debe de colocar la cuenta de expedientes, count(expediente)) si cumple con la restricción en caso contrario debe de ser cero. Espero me puedan dar luces por favor les doy las gracias por todo Atentamente, Ing. Marcelo Rodriguez S. Jefe de Mejoras Continuas Costos __ Información de NOD32, revisión 2551 (20070926) __ Este mensaje ha sido analizado con NOD32 antivirus system http://www.nod32.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is MYSQL's equivalent to Oracle's DBMS_OUTPUT
Oracle provides a stored procedure called DBMS_OUTPUT which primarily is used to write/print/display text string to StandardOut (a.k.a. the terminal). In V5 MYSQL is there a functional equivalent? If so, what is it called. No, there isn't. I am willing to RTFM if somebody provides me a clue as to which manual contains the answer to my question. I have Guy Harrison's MYSQL Stored Procedures but could not find what I'm looking for in it. Since I am not sure if what I want exists or what it may be called, I just may be looking in the wrong places for the answer. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Ouch! ibdata files deleted. Why no catastrophe?
On Wed, 2007-09-26 at 10:55 -0400, Jerry Schwartz wrote: I don't know Linux that well, but I know that HP-UX lets you do most anything to an open file, including deleting it. The file will continue to exist as long as it is open by at least one process. If I remember correctly, this is all about the inode use count vs. the directory entry, or something like that. The file will actually be safe so long as it is not closed by the last user. It sounds like Linux is the same. Those files will go away as soon as they are closed, so the database should be dumped before shutting down mysqld. Done :) Thanks to all those who responded. I did a full backup last night and shut down mysql. At this point the innodb files were then completely deleted ( disappeared from the output of 'lsof' ) and the filesystem saw the extra space. I removed my /var/lib/mysql, reconfigured things, and imported, and everything went fine. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Assistance avoiding a full table scan
Brent, I tried this and it definitely boosted performance. On a test query that would take 2+ seconds to run with 20 id's - it ran in 0.002 seconds. Thanks everyone for your help and comments. Erik On Sep 21, 2007, at 2:01 PM, Brent Baisley wrote: As others have mentioned, mysql doesn't handle IN queries efficiently. You can try changing it to using derived tables/subqueries. I did some quick tests and the explain shows a different analysis. select comment, gid, date_posted from tbl JOIN (select max(id) as mid from tbl where gid in ( 1234,2345,3456 .. 7890 ) group by gid) as maxids ON tpl.id=maxids.mid; You're really just changing one of the IN statements to a join. My quick tests showed that a full table is still being done, but it's now on the derived table (maxids), which would only be as large as how many gids you are searching on (50?). I tested this on two related tables, 170K in one and 90K in the other. Your tables are currently much smaller, so mysql may come up with a different execution path. For me, the join syntax was far faster. On 9/21/07, Erik Giberti [EMAIL PROTECTED] wrote: Hello everyone, The app server in this case is PHP, and the database is MySQL 5.0.22 on RedHat linux I've got a database with about 7.5K records in it that I expect to start growing very quickly ~10-12K records per day. The storage engine is InnoDB. This table is growing quickly and will continue to grow for a long time. This table stores comments (as you can see from the structure) and is being used to display a list of comments based on a users affiliations. The structure is approximately this - I'm leaving out unrelated columns: id int - primary key - auto increment gid bigint - indexed comment varchar date_posted timestamp I run a query with the following form select comment, gid, date_posted from tbl where id in (select max(id) from tbl where gid in ( 1234,2345,3456 .. 7890 ) group by gid); I have an index on gid and id is the primary key When I describe the query with about 50 gid values inserted (where indicated above) I get the following: +++---+---+---+- +-+--+--+--+ | id | select_type| table | type | possible_keys | key | key_len | ref | rows | Extra| +++---+---+---+- +-+--+--+--+ | 1 | PRIMARY| tbl | ALL | NULL | NULL| NULL| NULL | 7533 | Using where | | 2 | DEPENDENT SUBQUERY | tbl | range | idx_gid | idx_gid | 9 | NULL | 58 | Using where; Using index | +++---+---+---+- +-+--+--+--+ Running the query on a production machine with sufficient memory and horsepower (box is only 20% utilized) it still takes 3 seconds to run - obviously not quick enough for web use. What I really need is the most recent comment from each group based on a variable set of gid's that change from user to user. Any thoughts on how to tweak this to avoid the full table scan? Thank you in advance for your assistance. Erik Giberti -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [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]
mysql user password funkyness
Everyone, So I have never seen this before. I set up a database yesterday. I added a user today in the normal manner.. ie grant usage on *.* to 'username'@'%' identified by 'password'; Here is the thing. You exit the console and try to log in with mysql -u username -ppassword and it won't let you in... BUT it log in with both mysql -u username and also mysql -u username -p (hit enter key). So the user account works without a password. executing the show grants command shows a password so this shouldn't work. Never had this problem before and kind of hard to google for mysql user login without password but has password.. show grants for 'user'@'%'; ++ | Grants for [EMAIL PROTECTED] | ++ | GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD 'password_hash' | ++ There are other privileges for the appropriate databases but they really aren't relevant. thanks, Keith -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 blog: http://blog.paragon-cs.com (o) 919-433-0786 (c) 850-637-3877
