Re: SQLyog can not insert/update Table w/o Primary Key
Hello I belive this is not the correct place to ask the question. You can try your discussion forums at http://www.webyog.com/forums for SQLyog specific questions. Karam --- William IT [EMAIL PROTECTED] wrote: Why SQLyog can not insert/update Table w/o Primary Key? Is there additional setting to enable this? __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQLyog can not insert/update Table w/o Primary Key
Out of interest is there any advantage of using a primary key?, or is it perfectly ok/normal not to specify one?. Im not quite sure what a primary does. I only used a primary key on one table which had auto_increment column only because it forced me to add it. ?? Hello I belive this is not the correct place to ask the question. You can try your discussion forums at http://www.webyog.com/forums for SQLyog specific questions. Karam to Outlook(TM). http://calendar.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql data between Linux and Windows
The character sets in both mysql severs in linux and windows are the same. When I query a table which has the same data in spanish with accents I can see the characters written are different. My goal is to extract data from one of the data bases and be able to get then to the other database between linux ands windows without any problem. Apache is used as web server in both Linux and windows. Regards. Carlos Díaz Stefan Hinz [EMAIL PROTECTED] wrote: Carlos, We have a hosting provider with a Linux box and an MySql database. We have a local Mysql database. In both machines I use phpMyAdmin. On the startpage (right frame) of phpMyAdmin you should see a link called Show MySQL system variables. Look what the values of the variables character_set and character_sets are. When I export from Windows to Linux then all the data in Linux is messed up with respect to special characters, we work in spanish and all accents are lost and converted to some extrange character. When I export from Linux to Windows the same thing happens. Of course these has to do with character sets. Most probably the two MySQL servers you are using have different character sets. ¿Any suggestion? You can find more detailed information here: http://www.mysql.com/doc/en/Character_sets.html And here: http://www.mysql.com/doc/en/SHOW_VARIABLES.html Regards, -- Stefan Hinz iConnect GmbH Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] - Do You Yahoo!? Todo lo que quieres saber de Estados Unidos, América Latina y el resto del Mundo. Visíta Yahoo! Noticias.
Re[2]: MySql data between Linux and Windows
Carlos, The character sets in both mysql severs in linux and windows are the same. When I query a table which has the same data in spanish with accents I can see the characters written are different. 1) So what are the values of the character_set and character_sets variables, anyway? 2) Which MySQL server versions do you use, for the local and the ISP server as well? 3) Which version of phpMyAdmin do you use? Do they differ on those two machines? My goal is to extract data from one of the data bases and be able to get then to the other database between linux ands windows without any problem. Apache is used as web server in both Linux and windows. Regards. Carlos Díaz Stefan Hinz [EMAIL PROTECTED] wrote: Carlos, We have a hosting provider with a Linux box and an MySql database. We have a local Mysql database. In both machines I use phpMyAdmin. On the startpage (right frame) of phpMyAdmin you should see a link called Show MySQL system variables. Look what the values of the variables character_set and character_sets are. When I export from Windows to Linux then all the data in Linux is messed up with respect to special characters, we work in spanish and all accents are lost and converted to some extrange character. When I export from Linux to Windows the same thing happens. Of course these has to do with character sets. Most probably the two MySQL servers you are using have different character sets. ¿Any suggestion? You can find more detailed information here: http://www.mysql.com/doc/en/Character_sets.html And here: http://www.mysql.com/doc/en/SHOW_VARIABLES.html Regards, -- Stefan Hinz iConnect GmbH Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] - Do You Yahoo!? Todo lo que quieres saber de Estados Unidos, América Latina y el resto del Mundo. Visíta Yahoo! Noticias. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQLyog can not insert/update Table w/o Primary Key
Probably this is what you want http://www.webyog.com/forums/index.php?act=STf=5t=351s=7cd9c97584811d72b9305d3e691ab28f Karam --- Daniel Crompton [EMAIL PROTECTED] wrote: Out of interest is there any advantage of using a primary key?, or is it perfectly ok/normal not to specify one?. Im not quite sure what a primary does. I only used a primary key on one table which had auto_increment column only because it forced me to add it. ?? Hello I belive this is not the correct place to ask the question. You can try your discussion forums at http://www.webyog.com/forums for SQLyog specific questions. Karam to Outlook(TM). http://calendar.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Meaning of error codes
Hi folks, A couple of my sites are intermittently getting the following error: 2014 - Commands out of sync; You can't run this command now select configuration_key as cfgKey, configuration_value as cfgValue from configuration On one site the error is being reported regularly by one particular user. Can anyone tell me what this means or direct me to the appropriate documentation. I searched it on the mysql.com site and came up with nothing. TIA Filter:mysql Andrew Hazen, O.C.P. E-Commerce Developer Jatech Solutions Inc. www.jatech.ca Specializing in OSCOMMERCE applications and PHP/MySQL programming.
Correct commandl line
hello all, sorry for the newbe question, but want is the correct command line sytax to restore table data from a file, table_name.sql each of our .sql files contains all the table structure and insert data and we need to restore a whole bunch of tables and data. TIA -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Web Hosting http://www.justlightening.net Tel: 1(985)902-8484 MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: efficient query or not?
Anthony Ward wrote: SELECT userid FROM place WHERE acos( cos($longitude) * cos($latitude) * cos(place.latitude) *cos(place.longitude) +cos($longitude) * sin($latitude) * cos(place.latitude) * sin(place.longitude) +sin($longitude) * sin($latitude) ) * 3963 = 1000); Would you consider this HORRIBLY inefficient or GOOD. if it is horrible how can i make it efficient? If it runs quickly enough for your purposes (I can't tell without knowing what your speed requirement, or at least the number of rows in your place table is), then it's good. If it's way too slow, then it's horribly inefficient. In your query, the mysql optimizer has no way of using any indexes you may have on longitude and latitude. So, if you have a large number of rows to sift through, it will not run quickly. To help it out, work out maximum bounding conditions on latitude and longitude in terms of $latitude and $longitude separately, and AND them into your WHERE. My trig is too rusty to do this, unfortunately. Anyway, if latitude and longitude are indexed (or at least one or the other), doing so ought to allow the optimizer to narrow down the places which must be scanned dramatically. Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Correct commandl line
The SOURCE command will allow you to execute files in batch mode: Mysql SOURCE table_name.sql; Check http://www.mysql.com/doc/en/Batch_mode.html For more info. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Mike Blezien [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 7:46 AM To: MySQL List Subject: Correct commandl line hello all, sorry for the newbe question, but want is the correct command line sytax to restore table data from a file, table_name.sql each of our .sql files contains all the table structure and insert data and we need to restore a whole bunch of tables and data. TIA -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Web Hosting http://www.justlightening.net Tel: 1(985)902-8484 MSN: [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: Meaning of error codes
http://www.mysql.com/doc/en/Commands_out_of_sync.html Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Andrew Hazen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, 03 June, 2003 09:40 Subject: Meaning of error codes Hi folks, A couple of my sites are intermittently getting the following error: 2014 - Commands out of sync; You can't run this command now select configuration_key as cfgKey, configuration_value as cfgValue from configuration On one site the error is being reported regularly by one particular user. Can anyone tell me what this means or direct me to the appropriate documentation. I searched it on the mysql.com site and came up with nothing. TIA Filter:mysql Andrew Hazen, O.C.P. E-Commerce Developer Jatech Solutions Inc. www.jatech.ca Specializing in OSCOMMERCE applications and PHP/MySQL programming. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Correct commandl line
Thanks, that exactly what I was looking for. been battle all nite with a crashed server and trying to get everything back up... appreciate the help. :) Mike Hillyer wrote: The SOURCE command will allow you to execute files in batch mode: Mysql SOURCE table_name.sql; Check http://www.mysql.com/doc/en/Batch_mode.html For more info. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Mike Blezien [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 7:46 AM To: MySQL List Subject: Correct commandl line hello all, sorry for the newbe question, but want is the correct command line sytax to restore table data from a file, table_name.sql each of our .sql files contains all the table structure and insert data and we need to restore a whole bunch of tables and data. TIA -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Web Hosting http://www.justlightening.net Tel: 1(985)902-8484 MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
copying
Greetings, in my last email nobody tackled the schema copy issue. Here is what I need to do: I have a live database that contains several tables. I need to create a history database with the same tables (without the data). I would like to do this without manually creating all the tables in the history db. Is there a more automated way to do this? thanks in advance, Darryl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: copying
Hi Darryl, Why not use mysqldump --no-data [database] [table1] [table2] ... John Griffin -Original Message- From: Darryl Hoar [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 10:49 AM To: [EMAIL PROTECTED] Subject: copying Greetings, in my last email nobody tackled the schema copy issue. Here is what I need to do: I have a live database that contains several tables. I need to create a history database with the same tables (without the data). I would like to do this without manually creating all the tables in the history db. Is there a more automated way to do this? thanks in advance, Darryl -- 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: copying
use mysqldump to just dump the schema. Then edit the script to change the database name and pump it back into mysql. =C= * Cal Evans * http://www.christianperformer.com * Stay plugged into your audience * The measure of a programmer is not the number of lines of code he writes but the number of lines he does not have to write. * - Original Message - From: Darryl Hoar [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 9:49 AM Subject: copying Greetings, in my last email nobody tackled the schema copy issue. Here is what I need to do: I have a live database that contains several tables. I need to create a history database with the same tables (without the data). I would like to do this without manually creating all the tables in the history db. Is there a more automated way to do this? thanks in advance, Darryl -- 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: SELECT problem with mysql 3.23.53-log
Stefan Schulte [EMAIL PROTECTED] wrote: i am analyzing a very strange behaviour of mysql-3.23-53-log on a Suse 8.1 system: I have created a table Customer with a column: customer_id int(11) Now i want to select all rows with customer_id=41: SELECT * from Customer WHERE customer_id=41; The result is: Empty set (0.13 sec) If i change the query to: SELECT * from Customer WHERE customer_id LIKE 41 then i get all results !? If I search for Customers with Ids 20 I also get results. It think, that my provider has updated the SuSe-Release or the mysql-Version of my Server. Is there any configuration or option of mysql that can cause this strange behaviour ??? No. Any other ideas ? Try to recreate indexes. If it doesn't help create a repeatable test case. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Not an Ad
Hi, I've been struggling for a couple of days trying to get phpMyAdmin to accept csv files for data uploads onto my hosted MySQL, and was having no success using v2.5, despite 'local file being switched On at both ends. My ISP just installed v2.5.1 and whooppe! it works. I wouldn't normally make this sort of statement, but I know there are a fair number of people out there having similar problems. If uploading from your machine *don't* use the DATA LOCAL, but the plain DATA option. If anyone wants to rap my knuckles over this, fair enough, but I struggled, and a solution has been found, so why not? Cheers Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
database copy
Hi there, I ´m using the Mysql control center with a database called dbONE in a PC, and I would like to copy it to another PC, Do you have any idea to how can I do it? I dont wanna create all the tables again... Thanks a lot
mysql query output get wrapped
mysql select * from Tickets limit 1; ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally Thanks Asif -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: copying
I tried mysqldump in past but it do come INSERT statement (with DATA). Is there any option if you just need schema. I might have missed it. Appreciate it, -Original Message- From: Cal Evans [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 9:51 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: copying use mysqldump to just dump the schema. Then edit the script to change the database name and pump it back into mysql. =C= * Cal Evans * http://www.christianperformer.com * Stay plugged into your audience * The measure of a programmer is not the number of lines of code he writes but the number of lines he does not have to write. * - Original Message - From: Darryl Hoar [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 9:49 AM Subject: copying Greetings, in my last email nobody tackled the schema copy issue. Here is what I need to do: I have a live database that contains several tables. I need to create a history database with the same tables (without the data). I would like to do this without manually creating all the tables in the history db. Is there a more automated way to do this? thanks in advance, Darryl -- 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: copying
Please ignore my first mail. I got the answer in next email. Regards, -Original Message- From: Cal Evans [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 9:51 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: copying use mysqldump to just dump the schema. Then edit the script to change the database name and pump it back into mysql. =C= * Cal Evans * http://www.christianperformer.com * Stay plugged into your audience * The measure of a programmer is not the number of lines of code he writes but the number of lines he does not have to write. * - Original Message - From: Darryl Hoar [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 9:49 AM Subject: copying Greetings, in my last email nobody tackled the schema copy issue. Here is what I need to do: I have a live database that contains several tables. I need to create a history database with the same tables (without the data). I would like to do this without manually creating all the tables in the history db. Is there a more automated way to do this? thanks in advance, Darryl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query output get wrapped
At 11:27 -0400 6/3/03, Asif Iqbal wrote: mysql select * from Tickets limit 1; ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally What would that look like? I'm having trouble understanding how your request isn't self-contradictory (it seems you want vertical output, but horizontal?). Thanks Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication of table conversions?
I currently have two databases set up with a master/slave replication environment. Right now, the tables are MyISAM, however I want to migrate to InnoDB so I can gain transactioins. I've worked through the processes and nearly everything seems straightforward, except: Does the conversion process replicate? That is, when I alter the table, will the slave also alter the table, or do I need to instead stop the replication, alter the master, then recreate the slave? Thanks for your help Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql query output get wrapped
Try -E option or put \g in end of your sql statement like below Mysql -u id -ppasswd -E -e show innodb status OR Mysql -u id -ppasswd -e show innodb status \g I am not sure it is little g or capital G. Try both way. -Original Message- From: Asif Iqbal [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 10:27 AM To: [EMAIL PROTECTED] Subject: mysql query output get wrapped mysql select * from Tickets limit 1; ++-+---++++---+- --+-+---+--+--+- ---+--+-++-+ -+--+---+-+-+--- --+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+- --+-+---+--+--+- ---+--+-++-+ -+--+---+-+-+--- --+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+- --+-+---+--+--+- ---+--+-++-+ -+--+---+-+-+--- --+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally Thanks Asif -- -- 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: Cannot find uppercase user from mysql.user table
At 18:30 -0500 6/2/03, Michael Franch wrote: To whom it may concern, I am using MySql v.4.0.13 and am trying to query the user table from Visual Basic using ADO. If the user has an entry, that is uppercase lowercase, e.g. Jim, and my query is:select user from mysql.user where user ='Jim' the recordset returned is empty. But, if the entry in the user field is all lowercase then, the recordset is returned with the appropriate values. I do not know MySql well enough to know if this is a bug or not, but if I run the query:select user from mysql.user where user ='Jim' in the Control center, it works fine. Please help me with this irritating problem. I also have the same problem if the entry in the user field is all uppercase. From your description of the problem (query doesn't work with ADO, same query works with MySQLCC), it appears that the problem may lie with ADO. Have you asked your question on the win32 list? The people there may know more about this problem. As a preliminary test, you might enable query logging in your server, then issue identical queries from ADO and from MySQLCC and see if identical queries actually arrive at the server. Thank you, Michael Franch Software Engineer UB Computer Services (630) 910-9066 - Phone (630) 910-0702 - Fax [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql query output get wrapped
mysql select * from Tickets limit 1 \g ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ 1 row in set (0.01 sec) That did not help . I not \G is not the one On Tue, 3 Jun 2003, Sagar, Sanjeev wrote: Try -E option or put \g in end of your sql statement like below Mysql -u id -ppasswd -E -e show innodb status OR Mysql -u id -ppasswd -e show innodb status \g I am not sure it is little g or capital G. Try both way. -Original Message- From: Asif Iqbal [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 10:27 AM To: [EMAIL PROTECTED] Subject: mysql query output get wrapped mysql select * from Tickets limit 1; ++-+---++++---+- --+-+---+--+--+- ---+--+-++-+ -+--+---+-+-+--- --+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+- --+-+---+--+--+- ---+--+-++-+ -+--+---+-+-+--- --+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+- --+-+---+--+--+- ---+--+-++-+ -+--+---+-+-+--- --+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally Thanks Asif -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query output get wrapped
At 11:39 -0400 6/3/03, Asif Iqbal wrote: I want the columns unwrapped to make it easy to read. Sorry if I confused you earlier I still don't know what you mean. What would this output *look like*? Please show an example. On Tue, 3 Jun 2003, Paul DuBois wrote: At 11:27 -0400 6/3/03, Asif Iqbal wrote: mysql select * from Tickets limit 1; ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally What would that look like? I'm having trouble understanding how your request isn't self-contradictory (it seems you want vertical output, but horizontal?). Thanks Asif -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- 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]
links...
I have a links page that has categories. I am attempting to place category links on one side, and the actual links on the other. However when I do this, I get the following result: category1 category1 category1 category2 Here is the code I am using: ?php // Read... $link_info = SELECT cat_link FROM links;; $response = mysql_query( $link_info, $dbh ); /* Printing results in HTML */ print table border =\0\ cell cellspacing=\0\ cellpadding=\3\\n; while ($table_data = mysql_fetch_array($response, MYSQL_ASSOC)) { foreach ( $table_data as $col_value ) { print \t\ttr class=\text\td$col_value/td/tr\n; } } print /table\n; ? Is there any way that I can output: category1 category2... Thanks, Steve Marquez Marquez Design [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query output get wrapped
something like this ++-+---++++---+---+-+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | ++-+---++++---+---+-+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 | ++-+---++++---+---+-+ *unwrapped* On Tue, 3 Jun 2003, Paul DuBois wrote: At 11:39 -0400 6/3/03, Asif Iqbal wrote: I want the columns unwrapped to make it easy to read. Sorry if I confused you earlier I still don't know what you mean. What would this output *look like*? Please show an example. On Tue, 3 Jun 2003, Paul DuBois wrote: At 11:27 -0400 6/3/03, Asif Iqbal wrote: mysql select * from Tickets limit 1; ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally What would that look like? I'm having trouble understanding how your request isn't self-contradictory (it seems you want vertical output, but horizontal?). Thanks Asif -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query output get wrapped
Asif, try this: prompt echo select * from Tickets limit 1; | mysql mydb result.file You get everything in ONE line. Cheers, Thomas On Tue, 3 Jun 2003, Asif Iqbal wrote: mysql select * from Tickets limit 1; ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally Thanks Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query output get wrapped
use a smaller font. This is really a function of your shell or client, not MySQL. humbly, =C= * Cal Evans * http://www.christianperformer.com * Stay plugged into your audience * The measure of a programmer is not the number of lines of code he writes but the number of lines he does not have to write. * - Original Message - From: Asif Iqbal [EMAIL PROTECTED] To: Paul DuBois [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 10:57 AM Subject: Re: mysql query output get wrapped something like this ++-+---++++---+- --+-+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | ++-+---++++---+- --+-+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 | ++-+---++++---+- --+-+ *unwrapped* On Tue, 3 Jun 2003, Paul DuBois wrote: At 11:39 -0400 6/3/03, Asif Iqbal wrote: I want the columns unwrapped to make it easy to read. Sorry if I confused you earlier I still don't know what you mean. What would this output *look like*? Please show an example. On Tue, 3 Jun 2003, Paul DuBois wrote: At 11:27 -0400 6/3/03, Asif Iqbal wrote: mysql select * from Tickets limit 1; ++-+---++++---+ ---+-+---+--+--+ +--+-++-+--- --+--+---+-+-+-- ---+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+ ---+-+---+--+--+ +--+-++-+--- --+--+---+-+-+-- ---+--+ | 5 | 5 | 4 | ticket | NULL | NULL | 16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+ ---+-+---+--+--+ +--+-++-+--- --+--+---+-+-+-- ---+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally What would that look like? I'm having trouble understanding how your request isn't self-contradictory (it seems you want vertical output, but horizontal?). Thanks Asif -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- 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:
Re: mysql query output get wrapped
Did not work prompt echo select * from Tickets limit 1; | mysql mydb res prompt cat res id EffectiveId Queue TypeIssueStatement Resolution Owner Subject InitialPriority FinalPriority PriorityStatus TimeWorked TimeLeftToldStarts Started Due Resolved LastUpdatedBy LastUpdated Creator Created Disabled 5 5 4 ticket NULLNULL16 RE: phonebook 10 80 10 resolved0 NULL1970-01-01 00:00:00 NULLNULL1970-01-01 00:00:00 NULL1 2001-04-17 18:38:02 1 2001-04-17 18:26:46 0 Still wrapped :-) On Tue, 3 Jun 2003, Thomas Spahni wrote: Asif, try this: prompt echo select * from Tickets limit 1; | mysql mydb result.file You get everything in ONE line. Cheers, Thomas On Tue, 3 Jun 2003, Asif Iqbal wrote: mysql select * from Tickets limit 1; ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally Thanks Asif -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query output get wrapped
I did that before with a switch , just forgot On Tue, 3 Jun 2003, Cal Evans wrote: use a smaller font. This is really a function of your shell or client, not MySQL. humbly, =C= * Cal Evans * http://www.christianperformer.com * Stay plugged into your audience * The measure of a programmer is not the number of lines of code he writes but the number of lines he does not have to write. * - Original Message - From: Asif Iqbal [EMAIL PROTECTED] To: Paul DuBois [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 10:57 AM Subject: Re: mysql query output get wrapped something like this ++-+---++++---+- --+-+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | ++-+---++++---+- --+-+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 | ++-+---++++---+- --+-+ *unwrapped* On Tue, 3 Jun 2003, Paul DuBois wrote: At 11:39 -0400 6/3/03, Asif Iqbal wrote: I want the columns unwrapped to make it easy to read. Sorry if I confused you earlier I still don't know what you mean. What would this output *look like*? Please show an example. On Tue, 3 Jun 2003, Paul DuBois wrote: At 11:27 -0400 6/3/03, Asif Iqbal wrote: mysql select * from Tickets limit 1; ++-+---++++---+ ---+-+---+--+--+ +--+-++-+--- --+--+---+-+-+-- ---+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+ ---+-+---+--+--+ +--+-++-+--- --+--+---+-+-+-- ---+--+ | 5 | 5 | 4 | ticket | NULL | NULL | 16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+ ---+-+---+--+--+ +--+-++-+--- --+--+---+-+-+-- ---+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally What would that look like? I'm having trouble understanding how your request isn't self-contradictory (it seems you want vertical output, but horizontal?). Thanks Asif -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place
Re: links...
At 10:52 -0500 6/3/03, Steve Marquez wrote: I have a links page that has categories. I am attempting to place category links on one side, and the actual links on the other. However when I do this, I get the following result: category1 category1 category1 category2 Here is the code I am using: ?php // Read... $link_info = SELECT cat_link FROM links;; $response = mysql_query( $link_info, $dbh ); /* Printing results in HTML */ print table border =\0\ cell cellspacing=\0\ cellpadding=\3\\n; while ($table_data = mysql_fetch_array($response, MYSQL_ASSOC)) { foreach ( $table_data as $col_value ) { print \t\ttr class=\text\td$col_value/td/tr\n; } } print /table\n; ? Is there any way that I can output: category1 category2... SELECT DISTINCT cat_link FROM links perhaps? Also, omit the semicolon from the query. You need that only in the mysql client. Thanks, Steve Marquez Marquez Design [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query output get wrapped
At 11:57 -0400 6/3/03, Asif Iqbal wrote: something like this ++-+---++++---+---+-+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | ++-+---++++---+---+-+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 | ++-+---++++---+---+-+ *unwrapped* That stilll looks wrapped to me. Anyway, no, there is no option for doing that. You'd need to perform this kind of formatting yourself. On Tue, 3 Jun 2003, Paul DuBois wrote: At 11:39 -0400 6/3/03, Asif Iqbal wrote: I want the columns unwrapped to make it easy to read. Sorry if I confused you earlier I still don't know what you mean. What would this output *look like*? Please show an example. On Tue, 3 Jun 2003, Paul DuBois wrote: At 11:27 -0400 6/3/03, Asif Iqbal wrote: mysql select * from Tickets limit 1; ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally What would that look like? I'm having trouble understanding how your request isn't self-contradictory (it seems you want vertical output, but horizontal?). Thanks Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql query output get wrapped
Asif, If I understand you correctly, you should just be able to increase the size of the DOS window (Of course this would have to be on Windwos). If so simply right click the Title Bar and Choose Properties, then on the Layout Tab increase the Window Size and Screen Buffer size Hope this Helps Kind Regards -Original Message- From: Asif Iqbal [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 4:57 PM To: Paul DuBois Cc: [EMAIL PROTECTED] Subject: Re: mysql query output get wrapped something like this ++-+---++++---+- --+-+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | ++-+---++++---+- --+-+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 | ++-+---++++---+- --+-+ *unwrapped* On Tue, 3 Jun 2003, Paul DuBois wrote: At 11:39 -0400 6/3/03, Asif Iqbal wrote: I want the columns unwrapped to make it easy to read. Sorry if I confused you earlier I still don't know what you mean. What would this output *look like*? Please show an example. On Tue, 3 Jun 2003, Paul DuBois wrote: At 11:27 -0400 6/3/03, Asif Iqbal wrote: mysql select * from Tickets limit 1; ++-+---++++---+ ---+-+---+--+--+ +--+-++-+--- --+--+---+-+-+-- ---+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+ ---+-+---+--+--+ +--+-++-+--- --+--+---+-+-+-- ---+--+ | 5 | 5 | 4 | ticket | NULL | NULL | 16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+ ---+-+---+--+--+ +--+-++-+--- --+--+---+-+-+-- ---+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally What would that look like? I'm having trouble understanding how your request isn't self-contradictory (it seems you want vertical output, but horizontal?). Thanks Asif -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- 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]
debuggine 1205 / LOCK wait timeout exceeded errors
Hi there, I have a problem with LOCKs. I use InnoDB tables and transactions. [EMAIL PROTECTED] ~ 1001:0 /usr/local/libexec/mysqld --version /usr/local/libexec/mysqld Ver 4.0.13 for portbld-freebsd4.8 on i386 [EMAIL PROTECTED] ~ 1002:0 uname -sr FreeBSD 4.8-RC [EMAIL PROTECTED] ~ 1003:0 httpd -v Server version: Apache/1.3.26 (Unix) Server built: Jul 23 2002 14:12:25 mod_php-4.3.1 linked with libmysqlclient 4.0.11a-gamma This is one such case (one PHP script run, taken from the query log): 030603 18:18:47 19 Init DB webed 19 Init DB webed 19 Query SELECT COUNT(*) FROM editor WHERE id = 1 19 Init DB webed 19 Query SELECT e.login, e.authorized, e.firstname, e.lastname, e.validfrom, e.validtill, e.createdby, e.createdon, e.changedby, e.changedon, e.caps, e.wysiwyg FROM editor e WHERE id = 1 030603 18:18:48 19 Init DB webed 19 Query SELECT COUNT(*) FROM server WHERE id = 4 19 Init DB webed 19 Query SELECT COUNT(*) FROM editor WHERE id = 1 19 Init DB webed 19 Query SELECT g.id AS gid, g.name FROM acl_group g, acl_member m WHERE g.id = m.gid AND uid = 1 19 Init DB webed 19 Query SELECT MAX(access) AS max, MIN(access) AS min FROM acl_access a WHERE a.objtype = 128 AND a.objid = 4 AND a.gid IN (1) GROUP BY objid 19 Init DB webed 19 Query DELETE FROM acl_access WHERE objtype = 128 AND objid = 4 Here the server emitted 1205. DELETE FROM acl_access WHERE objtype = 128 AND objid = 4 [nativecode = 1205 * * LOCK wait timeout exceeded; Try restarting TRANSACTION ] I don't see what should cause it to behave so. I'm the only user ATM, IOW the listing above is unaltered in any way, and represents exactly one run of the PHP script. Notice the lack of any transaction-related or LOCK commands (in most cases, however, the DML command is preceeded by a BEGIN, and immediatelly [that is, after the timeout] followed by a ROLLBACK). I'd love to see this resolved, and will happily provide any info requested. TIA HAND -- FreeBSD 4.8-RC 6:23PM up 14 days, 2:54, 16 users, load averages: 0.04, 0.04, 0.00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database copy
Fabio, I have had to do this quite a few times myself. If you have use of a server with MySQL on it then it can be done using a GUI (I use MySQL-Front, but most people seem to MySQL Control Center. All you do is use the built-in tools to transfer table structures + data to a database (any) on the external server and then transfer it from there to the other PC. If this doesn't make much sense to you, let me know! Best wishes, Paul Najman - Original Message - From: Fabio Bernardo [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 4:12 PM Subject: database copy Hi there, I ´m using the Mysql control center with a database called dbONE in a PC, and I would like to copy it to another PC, Do you have any idea to how can I do it? I dont wanna create all the tables again... Thanks a lot -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: cant grant privileges?!
Use it after the grant command: flush privileges; hi everybody... i just installed mysql 4.0.13 on a redhat linux 8.0 when i create a database as root and then grant privileges to another user like this: GRANT ALL ON somedb.* TO [EMAIL PROTECTED] IDENTIFIED BY passwd; Allann J. -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: links...
It worked. Thanks! -Steve At 10:52 -0500 6/3/03, Steve Marquez wrote: I have a links page that has categories. I am attempting to place category links on one side, and the actual links on the other. However when I do this, I get the following result: category1 category1 category1 category2 Here is the code I am using: ?php // Read... $link_info = SELECT cat_link FROM links;; $response = mysql_query( $link_info, $dbh ); /* Printing results in HTML */ print table border =\0\ cell cellspacing=\0\ cellpadding=\3\\n; while ($table_data = mysql_fetch_array($response, MYSQL_ASSOC)) { foreach ( $table_data as $col_value ) { print \t\ttr class=\text\td$col_value/td/tr\n; } } print /table\n; ? Is there any way that I can output: category1 category2... SELECT DISTINCT cat_link FROM links perhaps? Also, omit the semicolon from the query. You need that only in the mysql client. Thanks, Steve Marquez Marquez Design [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
C API
Hello, I'm trying to code a small C client, under windows, with bloddshed Dev-C++, and i always get an error. /* Code */ #include conio.h #include stdarg.h #include winsock.h #include stdio.h #include stdlib.h #include string.h #include mysql.h int main(void) { MYSQL* toto; toto = mysql_init(toto); } /* this is the compler log */ Compiler: Default compiler Executing gcc.exe... gcc.exe U:\mep\Dev\abr.c -o U:\mep\Dev\abr.exe -IC:\Dev-Cpp\include -IC:\mysql\include -LC:\Dev-Cpp\lib C:\DOCUME~1\badier1\LOCALS~1\Temp/ccW8.o(.text+0x1d2):abr.c: undefined reference to [EMAIL PROTECTED]' Execution terminated I really don't know where this can come from. I downloaded the latest production mysql server (to get the client and libraries), but nothing i tried correct the problem. Any idea? Thanks Vincent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql query output get wrapped
MySql returns data on a record basis and you are asking it to take a line's worth of data from two records (the column header output is like a record) and display it and then take the next line's worth of data from the same two records and display it and repeat until the data from those records has been exhausted. It is not set up to do that. If I was using Oracle I would make the line size the width of the data (~320 chars) and the page size = 1 record. I would save the results to a file and then write perl script to do the chopping as described above. I don't know if mysql has similar features. I didn't find them in the manual. Barry -Original Message- From: Asif Iqbal [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 12:10 PM To: Thomas Spahni Cc: [EMAIL PROTECTED] Subject: Re: mysql query output get wrapped Did not work prompt echo select * from Tickets limit 1; | mysql mydb res prompt cat res id EffectiveId Queue TypeIssueStatement Resolution Owner Subject InitialPriority FinalPriority PriorityStatus TimeWorked TimeLeftToldStarts Started Due Resolved LastUpdatedBy LastUpdated Creator Created Disabled 5 5 4 ticket NULLNULL16 RE: phonebook 10 80 10 resolved0 NULL1970-01-01 00:00:00 NULLNULL1970-01-01 00:00:00 NULL1 2001-04-17 18:38:02 1 2001-04-17 18:26:46 0 Still wrapped :-) On Tue, 3 Jun 2003, Thomas Spahni wrote: Asif, try this: prompt echo select * from Tickets limit 1; | mysql mydb result.file You get everything in ONE line. Cheers, Thomas On Tue, 3 Jun 2003, Asif Iqbal wrote: mysql select * from Tickets limit 1; ++-+---++++---+-- -+-+---+--+--+--- -+--+-++-+--- --+--+---+-+-+--- --+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+-- -+-+---+--+--+--- -+--+-++-+--- --+--+---+-+-+--- --+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+-- -+-+---+--+--+--- -+--+-++-+--- --+--+---+-+-+--- --+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally Thanks Asif -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- 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: Re[2]: MySql data between Linux and Windows
Character set In windows 2000 latin1 In Linux hosting latin1 Character sets In windows 2000 latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 In Linux latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 phpMyAdmin version Windows 2.5.0 Linux 2.4.0 MySql versions Windows MySQL 3.23.52-nt Linux MySQL 3.23.56-log Stefan Hinz [EMAIL PROTECTED] wrote: Carlos, The character sets in both mysql severs in linux and windows are the same. When I query a table which has the same data in spanish with accents I can see the characters written are different. 1) So what are the values of the character_set and character_sets variables, anyway? 2) Which MySQL server versions do you use, for the local and the ISP server as well? 3) Which version of phpMyAdmin do you use? Do they differ on those two machines? My goal is to extract data from one of the data bases and be able to get then to the other database between linux ands windows without any problem. Apache is used as web server in both Linux and windows. Regards. Carlos Díaz Stefan Hinz wrote: Carlos, We have a hosting provider with a Linux box and an MySql database. We have a local Mysql database. In both machines I use phpMyAdmin. On the startpage (right frame) of phpMyAdmin you should see a link called Show MySQL system variables. Look what the values of the variables character_set and character_sets are. When I export from Windows to Linux then all the data in Linux is messed up with respect to special characters, we work in spanish and all accents are lost and converted to some extrange character. When I export from Linux to Windows the same thing happens. Of course these has to do with character sets. Most probably the two MySQL servers you are using have different character sets. ¿Any suggestion? You can find more detailed information here: http://www.mysql.com/doc/en/Character_sets.html And here: http://www.mysql.com/doc/en/SHOW_VARIABLES.html Regards, -- Stefan Hinz iConnect GmbH Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] - Do You Yahoo!? Todo lo que quieres saber de Estados Unidos, América Latina y el resto del Mundo. Visíta Yahoo! Noticias. Regards, -- Stefan Hinz iConnect GmbH Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] - Do You Yahoo!? Todo lo que quieres saber de Estados Unidos, América Latina y el resto del Mundo. Visíta Yahoo! Noticias.
Re: efficient query or not?
Anthony, As Bruce noted, you can greatly improve efficiency by using a WHERE clause, but as you may be quoting a first formula I posted 'from first principles', but which gives inaccurate results, I ought to correct it. Here is a much more accurate formula for distance in miles... 3963 * acos(cos(radians(90-lat1)) * cos(radians(90-lat2)) + sin(radians(90-lat1)) * sin(radians(90-lat2)) * cos(radians(lon1-lon2))) (note: you can also find estimates of the earth's radius of 3959 and 3955) Given a table named locations with a bit of data ... CREATE TABLE locations ( id int(10) unsigned NOT NULL auto_increment, name char(20) NOT NULL default '', lat double NOT NULL default '0', lon double NOT NULL default '0', PRIMARY KEY (`id`) ); INSERT INTO locations VALUES (0,'New York', 40.7695, -73.9512), (0,'Boston', 42.3512, -71.0536), (0,'San Francisco', 37.775, -122.417), (0,'San Diego', 32.815, -117.136); (note: lat and lon are in decimal degrees) then this query, using the above formula SELECT loc1.name, loc1.lat, loc1.lon, loc2.name, loc2.lat, loc2.lon, 3963 * acos(cos(radians(90-loc1.lat)) * cos(radians(90-loc2.lat)) + sin(radians(90-loc1.lat)) * sin(radians(90-loc2.lat)) * cos(radians(loc1.lon-loc2.lon))) AS Miles FROM locations AS loc1 INNER JOIN locations AS loc2 ON loc1.id = 1 AND loc2.id = 2 gives pretty good results. HTH PB - Hi, I need to calculate distance from a point and for the select statment I have this SELECT userid FROM place WHERE acos( cos($longitude) * cos($latitude) * cos(place.latitude) *cos(place.longitude) +cos($longitude) * sin($latitude) * cos(place.latitude) * sin(place.longitude) +sin($longitude) * sin($latitude) ) * 3963 = 1000); Would you consider this HORRIBLY inefficient or GOOD. if it is horrible how can i make it efficient? Anthony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query output get wrapped
The pager option can be used to accomplish this (if you don't mind having things running through, e.g., less). When I need to do this I just do: mysql \P less -S mysql my query here You'll get unwrapped output (the -S option to less tells it to truncate rather than wrap over-long lines). You can use anything you like as your pager (as far as I can tell): all it has to do is take some input on the input stream and write its output out to the output stream. It is up to the pager how it does this: in the example of less it will paginate and not wrap. Other pagers can be used to achieve different results, processing the stream however you like, based on your needs. Cheers, Chris Asif Iqbal wrote: mysql select * from Tickets limit 1; ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally Thanks Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help creating foreign keys
the syntax for foreign key creation is different than what you have. You need an alter table statement. create table bar ( bar_idint unsigned auto_increment primary key, foo_idint unsigned, bar_value int, ) type=innodb; alter table bar add constraint foreign key (foo_id) references foo(foo_id) on delete cascade; Justin Scheiber wrote: Hello, I want to create the following tables - where a foriegn key references an auto_incremented primary key of another table. In my simple logic, it seem like such a thing should be possible -- after all, i just need the value of the referenced primary key. I know you can't have 2 auto_increment columns in a table, and I have read up on the errno: 150 but it still seems like this should be possible.Do I need to rethink the table structure? Or do I just not understand something here? create table foo ( foo_idint unsigned auto_increment, foo_value int, primary key(foo_id) ) type=innodb; create table bar ( bar_idint unsigned auto_increment, foo_idint unsigned, bar_value int, primary key (bar_id), foreign key(foo_id) references foo(foo_id), ) type=innodb; ERROR 1005: Can't create table './test/bar.frm' (errno: 150) -justin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query output get wrapped
That works with \G switch , but without the \G switch Istill get it wrapped wish I can remeber the switch On Tue, 3 Jun 2003, Chris Tucker wrote: The pager option can be used to accomplish this (if you don't mind having things running through, e.g., less). When I need to do this I just do: mysql \P less -S mysql my query here You'll get unwrapped output (the -S option to less tells it to truncate rather than wrap over-long lines). You can use anything you like as your pager (as far as I can tell): all it has to do is take some input on the input stream and write its output out to the output stream. It is up to the pager how it does this: in the example of less it will paginate and not wrap. Other pagers can be used to achieve different results, processing the stream however you like, based on your needs. Cheers, Chris Asif Iqbal wrote: mysql select * from Tickets limit 1; ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally Thanks Asif -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C API
# [EMAIL PROTECTED] / 2003-06-03 18:45:31 +0200: Executing gcc.exe... gcc.exe U:\mep\Dev\abr.c -o U:\mep\Dev\abr.exe -IC:\Dev-Cpp\include -IC:\mysql\include -LC:\Dev-Cpp\lib C:\DOCUME~1\badier1\LOCALS~1\Temp/ccW8.o(.text+0x1d2):abr.c: undefined reference to [EMAIL PROTECTED]' shouldn't you have -LC:\mysql\lib there as well? -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C API
At 18:45 +0200 6/3/03, [EMAIL PROTECTED] wrote: Hello, I'm trying to code a small C client, under windows, with bloddshed Dev-C++, and i always get an error. /* Code */ #include conio.h #include stdarg.h #include winsock.h #include stdio.h #include stdlib.h #include string.h #include mysql.h int main(void) { MYSQL* toto; toto = mysql_init(toto); } /* this is the compler log */ Compiler: Default compiler Executing gcc.exe... gcc.exe U:\mep\Dev\abr.c -o U:\mep\Dev\abr.exe -IC:\Dev-Cpp\include -IC:\mysql\include -LC:\Dev-Cpp\lib C:\DOCUME~1\badier1\LOCALS~1\Temp/ccW8.o(.text+0x1d2):abr.c: undefined reference to [EMAIL PROTECTED]' Execution terminated I really don't know where this can come from. I downloaded the latest production mysql server (to get the client and libraries), but nothing i tried correct the problem. Any idea? Does that command list the directory where the MySQL client library is located, so that the compiler can find it? -LC:\mysql\lib\opt or -LC:\mysql\lib\debug perhaps? Thanks Vincent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query output get wrapped
What platform are you on? If you're on windows you probably won't have less installed, in which case you'll need to either (a) install it or (b) use a different pager (not sure what you'll have with windows, you'd have to independently research it). I'm assuming that what you want is to have the list of column names as your first row, then each row thereafter on a single line in your display window (i.e. no wrapping of those lines on display). If you need to get column names into the output for each cell, then you'll need to do some post-processing of your own. Chris Asif Iqbal wrote: That works with \G switch , but without the \G switch Istill get it wrapped wish I can remeber the switch On Tue, 3 Jun 2003, Chris Tucker wrote: The pager option can be used to accomplish this (if you don't mind having things running through, e.g., less). When I need to do this I just do: mysql \P less -S mysql my query here You'll get unwrapped output (the -S option to less tells it to truncate rather than wrap over-long lines). You can use anything you like as your pager (as far as I can tell): all it has to do is take some input on the input stream and write its output out to the output stream. It is up to the pager how it does this: in the example of less it will paginate and not wrap. Other pagers can be used to achieve different results, processing the stream however you like, based on your needs. Cheers, Chris Asif Iqbal wrote: mysql select * from Tickets limit 1; ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally Thanks Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C API
From: [EMAIL PROTECTED] Date: 2003/06/03 Tue AM 11:45:31 CDT To: [EMAIL PROTECTED] Subject: C API Hello, I'm trying to code a small C client, under windows, with bloddshed Dev-C++, and i always get an error. /* Code */ #include conio.h #include stdarg.h #include winsock.h #include stdio.h #include stdlib.h #include string.h #include mysql.h My experience is that one should always include these files: #include my_global.h #include my_sys.h #include mysql.h This is just a stab in the dark, though. -S int main(void) { MYSQL* toto; toto = mysql_init(toto); } /* this is the compler log */ Compiler: Default compiler Executing gcc.exe... gcc.exe U:\mep\Dev\abr.c -o U:\mep\Dev\abr.exe -IC:\Dev-Cpp\include -IC:\mysql\include -LC:\Dev-Cpp\lib C:\DOCUME~1\badier1\LOCALS~1\Temp/ccW8.o(.text+0x1d2):abr.c: undefined reference to [EMAIL PROTECTED]' Execution terminated I really don't know where this can come from. I downloaded the latest production mysql server (to get the client and libraries), but nothing i tried correct the problem. Any idea? Thanks Vincent -- 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 query output get wrapped
I am on solaris 2.8 sparc. You less works fine if I use \G switch. But I want (as you explained) list of column names in one line in first row and the results are thereafter in single rows instead of getting wrapped. Like I said wish I remebered the swithc for that On Tue, 3 Jun 2003, Chris Tucker wrote: What platform are you on? If you're on windows you probably won't have less installed, in which case you'll need to either (a) install it or (b) use a different pager (not sure what you'll have with windows, you'd have to independently research it). I'm assuming that what you want is to have the list of column names as your first row, then each row thereafter on a single line in your display window (i.e. no wrapping of those lines on display). If you need to get column names into the output for each cell, then you'll need to do some post-processing of your own. Chris Asif Iqbal wrote: That works with \G switch , but without the \G switch Istill get it wrapped wish I can remeber the switch On Tue, 3 Jun 2003, Chris Tucker wrote: The pager option can be used to accomplish this (if you don't mind having things running through, e.g., less). When I need to do this I just do: mysql \P less -S mysql my query here You'll get unwrapped output (the -S option to less tells it to truncate rather than wrap over-long lines). You can use anything you like as your pager (as far as I can tell): all it has to do is take some input on the input stream and write its output out to the output stream. It is up to the pager how it does this: in the example of less it will paginate and not wrap. Other pagers can be used to achieve different results, processing the stream however you like, based on your needs. Cheers, Chris Asif Iqbal wrote: mysql select * from Tickets limit 1; ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally Thanks Asif -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table copying/replication
Hello, I have a need for fast copying of a specific table from a master mysql server to a number of slave servers (say 5). The database in question could potentially have up to 2000 tables, and at any time, one of those tables would need to be copied to all 5 of the slaves, upon command of the master. I've looked into mysql replication, and it looks like a good option, except for the need to be able to trigger the copy from the master (the table is only copied when requested). I also looked at doing table copies (insert into .. select * from ..), but these seem pretty slow. Has anyone solved this problem before? Any ideas that would help out? Thanks! Ross -- Ross Simpson [EMAIL PROTECTED] MapQuest.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query output get wrapped
Are you using the -S switch to less? Note that it's a capital S, not lower case. Does the output get properly sent through the less pager (i.e do you have to page through the results that get selected)? Check the manpage for less on your machine and find out what the truncate lines option is, if it is different (highly unlikely). Or try a different pager. Make sure you execute your SQL command using \g or ;. Make sure you don't have anything funky set in your client section of your my.cnf that may mess with pagers/output. See whether using tee (\T filename) tees the output into the specified output file without wrapping. Chris Asif Iqbal wrote: I am on solaris 2.8 sparc. You less works fine if I use \G switch. But I want (as you explained) list of column names in one line in first row and the results are thereafter in single rows instead of getting wrapped. Like I said wish I remebered the swithc for that On Tue, 3 Jun 2003, Chris Tucker wrote: What platform are you on? If you're on windows you probably won't have less installed, in which case you'll need to either (a) install it or (b) use a different pager (not sure what you'll have with windows, you'd have to independently research it). I'm assuming that what you want is to have the list of column names as your first row, then each row thereafter on a single line in your display window (i.e. no wrapping of those lines on display). If you need to get column names into the output for each cell, then you'll need to do some post-processing of your own. Chris Asif Iqbal wrote: That works with \G switch , but without the \G switch Istill get it wrapped wish I can remeber the switch On Tue, 3 Jun 2003, Chris Tucker wrote: The pager option can be used to accomplish this (if you don't mind having things running through, e.g., less). When I need to do this I just do: mysql \P less -S mysql my query here You'll get unwrapped output (the -S option to less tells it to truncate rather than wrap over-long lines). You can use anything you like as your pager (as far as I can tell): all it has to do is take some input on the input stream and write its output out to the output stream. It is up to the pager how it does this: in the example of less it will paginate and not wrap. Other pagers can be used to achieve different results, processing the stream however you like, based on your needs. Cheers, Chris Asif Iqbal wrote: mysql select * from Tickets limit 1; ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally Thanks Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL
Making MySQL 5.0 Source Tree
Hello, I gave the follow commands to make the binary form in cygwin: autoconf configure.in ./configure and the messages were: loading cache ... bla bla bla checking build system type... i686-pc-cygwin configure: 724: Syntax error: word unexpected (expecting )) Maybe the cause is the linefeeds conversion from UNIX to DOS. I need help to be able to compile MySQL 5.0 source tree. Sincerely Yours, Iulian Information System Officer/Economist Falticeni, jud. Suceava Romania, Europe
RE: table copying/replication
---Original Message- --From: Ross Simpson [mailto:[EMAIL PROTECTED] --Sent: Tuesday, June 03, 2003 10:31 AM --To: [EMAIL PROTECTED] --Subject: table copying/replication -- --Hello, -- --I have a need for fast copying of a specific table from a master mysql --server to a number of slave servers (say 5). Create the table on the master and if the master and slave config is working then the same table will be on the slave. -- --The database in question could potentially have up to 2000 tables, and --at any time, one of those tables would need to be copied to all 5 of the --slaves, upon command of the master. Make sure you have enough inodes that's 6000 files that will be opened. Also set your ulimit high enough to open all the files. Replication will perform the same action on the slaves as initiated by the master. There is no need for a copy. --I also looked at doing table copies (insert into .. select * from ..), --but these seem pretty slow. It's building the index on the fly as well, if there are indexes on the dst table. It does have to scan the src table and for every row insert it into the dst table. You can tweak you're my.cnf values to make that operation happen faster. -- --Has anyone solved this problem before? Any ideas that would help out? -- Yes, the mysql team with replication. -- --Ross Simpson [EMAIL PROTECTED] --MapQuest.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: mysql query output get wrapped
Since this in running in Windows, I don't think you will have much luck in a simple console display. While you can change size properties for Window and Screen Buffer, these sizes are ONLY for length and you can't really do much for horizontal size. What I'd suggest tying is to run mysql as a command line batch job with the results being redirected into a .txt file that you could open with an editor that will allow wider lines. Something like: C:\mysql -uuserid -pyourpassword yourcommandtext.sql yourcommandoutput.txt Without using a tool like EMS MySQL Manager, etc, I think this might be your best bet. -Original Message- From: Keith Stevenson [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 11:14 AM To: 'Asif Iqbal'; Paul DuBois Cc: [EMAIL PROTECTED] Subject: RE: mysql query output get wrapped Asif, If I understand you correctly, you should just be able to increase the size of the DOS window (Of course this would have to be on Windwos). If so simply right click the Title Bar and Choose Properties, then on the Layout Tab increase the Window Size and Screen Buffer size Hope this Helps Kind Regards -Original Message- From: Asif Iqbal [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 4:57 PM To: Paul DuBois Cc: [EMAIL PROTECTED] Subject: Re: mysql query output get wrapped something like this ++-+---++++---+- --+-+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | ++-+---++++---+- --+-+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 | ++-+---++++---+- --+-+ *unwrapped* On Tue, 3 Jun 2003, Paul DuBois wrote: At 11:39 -0400 6/3/03, Asif Iqbal wrote: I want the columns unwrapped to make it easy to read. Sorry if I confused you earlier I still don't know what you mean. What would this output *look like*? Please show an example. On Tue, 3 Jun 2003, Paul DuBois wrote: At 11:27 -0400 6/3/03, Asif Iqbal wrote: mysql select * from Tickets limit 1; ++-+---++++---+ ---+-+---+--+--+ +--+-++-+--- +--+-++-+ --+--+---+-+-+-- --+--+---+-+-+ ---+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+ ---+-+---+--+--+ +--+-++-+--- +--+-++-+ --+--+---+-+-+-- --+--+---+-+-+ ---+--+ | 5 | 5 | 4 | ticket | NULL | NULL | 16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+ ---+-+---+--+--+ +--+-++-+--- +--+-++-+ --+--+---+-+-+-- --+--+---+-+-+ ---+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00
full text searching question
can anybody explain this to me please! I search am searching through a database with first name and last names. BTW I use mysql 4.0.13 on RedHat Linux 9.0 in case that matters. I created a fulltext search on both fields together so I can search them like this: mysql SELECT first_name,last_name FROM names_table WHERE MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN BOOLEAN MODE); The problem is that for some reason the record with first_name=fran is displayed first even though the score is lower than the record where first_name=christopher mysql SELECT first_name,last_name,MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN BOOLEAN MODE) AS score FROM names_table WHERE MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN BOOLEAN MODE); +-+---+-+ | first_name | last_name | score | +-+---+-+ | Fran| Wilkinson | 1 | | Christopher | Wilkinson | 1.333730698 | +-+---+-+ 2 rows in set (0.01 sec) Can somebody please explain why this is and how I can get Christopher to show up before Fran! Thanks! -- Chris Wilkinson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database copy
Hello I use SQLyog at http://www.webyog.com/sqlyog. It has a great facility to do exactly what you want. Just connect to two differt servers and choose DB-Copy Database To Another Host/DB option...and wholla you are done. Karam --- Paul Najman [EMAIL PROTECTED] wrote: Fabio, I have had to do this quite a few times myself. If you have use of a server with MySQL on it then it can be done using a GUI (I use MySQL-Front, but most people seem to MySQL Control Center. All you do is use the built-in tools to transfer table structures + data to a database (any) on the external server and then transfer it from there to the other PC. If this doesn't make much sense to you, let me know! Best wishes, Paul Najman - Original Message - From: Fabio Bernardo [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 4:12 PM Subject: database copy Hi there, I ´m using the Mysql control center with a database called dbONE in a PC, and I would like to copy it to another PC, Do you have any idea to how can I do it? I dont wanna create all the tables again... Thanks a lot -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: database copy
If you check the documentation about mysqldump, you'll find the command line syntax for using a combination of mysqldump on the source machine and mysql pumping to the target machine. It's simple, fast and everything is there to work just fine. -Original Message- From: Paul Najman [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 11:33 AM To: Fabio Bernardo Cc: [EMAIL PROTECTED] Subject: Re: database copy Fabio, I have had to do this quite a few times myself. If you have use of a server with MySQL on it then it can be done using a GUI (I use MySQL-Front, but most people seem to MySQL Control Center. All you do is use the built-in tools to transfer table structures + data to a database (any) on the external server and then transfer it from there to the other PC. If this doesn't make much sense to you, let me know! Best wishes, Paul Najman - Original Message - From: Fabio Bernardo [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 4:12 PM Subject: database copy Hi there, I ´m using the Mysql control center with a database called dbONE in a PC, and I would like to copy it to another PC, Do you have any idea to how can I do it? I dont wanna create all the tables again... Thanks a lot -- 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: full text searching question
User ORDER BY First Name Chris Wilkinson wrote: can anybody explain this to me please! I search am searching through a database with first name and last names. BTW I use mysql 4.0.13 on RedHat Linux 9.0 in case that matters. I created a fulltext search on both fields together so I can search them like this: mysql SELECT first_name,last_name FROM names_table WHERE MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN BOOLEAN MODE); The problem is that for some reason the record with first_name=fran is displayed first even though the score is lower than the record where first_name=christopher mysql SELECT first_name,last_name,MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN BOOLEAN MODE) AS score FROM names_table WHERE MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN BOOLEAN MODE); +-+---+-+ | first_name | last_name | score | +-+---+-+ | Fran| Wilkinson | 1 | | Christopher | Wilkinson | 1.333730698 | +-+---+-+ 2 rows in set (0.01 sec) Can somebody please explain why this is and how I can get Christopher to show up before Fran! Thanks! -- Kevin J Citron Sr. Object Imagineer Optimized Objects, Inc. EL Paso, Texas 79930 (915) 565-5777/566-2403 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Red Hat linux database to Mac OS X
Hello, I was wondering if anyone has experienced moving a MySQL database from Linux/Intel X86 to Mac OS X? I plan to use mysqldump of course. I was only wondering if anyone has run into problems they might share. Thanks. Greg __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql / PHP image link problem.
I have a mysql table column called 'imagecolumn' The rows contain links i.e. image.gif image.gif null image.gif image.gif To display in my web page im using: img src=?php echo $row_Recordset1['imagecolumn']; ? This displays the above images in my html page The problem i have is where the field in my table is left blank (null), a web browser shows this as a broken image link icon. i.e. it doesn't ignore it. Does anyone know how I can get my page to ignore it if there is no link.
Re: full text searching question
At 13:11 +0100 6/3/03, Chris Wilkinson wrote: can anybody explain this to me please! I search am searching through a database with first name and last names. BTW I use mysql 4.0.13 on RedHat Linux 9.0 in case that matters. I created a fulltext search on both fields together so I can search them like this: mysql SELECT first_name,last_name FROM names_table WHERE MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN BOOLEAN MODE); The problem is that for some reason the record with first_name=fran is displayed first even though the score is lower than the record where first_name=christopher mysql SELECT first_name,last_name,MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN BOOLEAN MODE) AS score FROM names_table WHERE MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN BOOLEAN MODE); +-+---+-+ | first_name | last_name | score | +-+---+-+ | Fran| Wilkinson | 1 | | Christopher | Wilkinson | 1.333730698 | +-+---+-+ 2 rows in set (0.01 sec) Can somebody please explain why this is and how I can get Christopher to show up before Fran! Thanks! Add ORDER BY score DESC to the query. -- Chris Wilkinson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: full text searching question
Chris, You're nearly there - the way to do it is: SELECT first_name,last_name,MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN BOOLEAN MODE) AS score FROM names_table WHERE MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN BOOLEAN MODE) ORDER BY score DESC; The db engine won't execute the full text query more than once so this is not inefficient. Andy -Original Message- From: Chris Wilkinson [mailto:[EMAIL PROTECTED] Sent: 03 June 2003 13:12 To: [EMAIL PROTECTED] Subject: full text searching question can anybody explain this to me please! I search am searching through a database with first name and last names. BTW I use mysql 4.0.13 on RedHat Linux 9.0 in case that matters. I created a fulltext search on both fields together so I can search them like this: mysql SELECT first_name,last_name FROM names_table WHERE MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN BOOLEAN MODE); The problem is that for some reason the record with first_name=fran is displayed first even though the score is lower than the record where first_name=christopher mysql SELECT first_name,last_name,MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN BOOLEAN MODE) AS score FROM names_table WHERE MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN BOOLEAN MODE); +-+---+-+ | first_name | last_name | score | +-+---+-+ | Fran| Wilkinson | 1 | | Christopher | Wilkinson | 1.333730698 | +-+---+-+ 2 rows in set (0.01 sec) Can somebody please explain why this is and how I can get Christopher to show up before Fran! Thanks! -- Chris Wilkinson [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: full text searching question
Chris, I should have added that the explanation is that the full text query does not automatically sort on the score when boolean mode is selected. Andy -Original Message- From: Chris Wilkinson [mailto:[EMAIL PROTECTED] Sent: 03 June 2003 13:12 To: [EMAIL PROTECTED] Subject: full text searching question can anybody explain this to me please! I search am searching through a database with first name and last names. BTW I use mysql 4.0.13 on RedHat Linux 9.0 in case that matters. I created a fulltext search on both fields together so I can search them like this: mysql SELECT first_name,last_name FROM names_table WHERE MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN BOOLEAN MODE); The problem is that for some reason the record with first_name=fran is displayed first even though the score is lower than the record where first_name=christopher mysql SELECT first_name,last_name,MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN BOOLEAN MODE) AS score FROM names_table WHERE MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN BOOLEAN MODE); +-+---+-+ | first_name | last_name | score | +-+---+-+ | Fran| Wilkinson | 1 | | Christopher | Wilkinson | 1.333730698 | +-+---+-+ 2 rows in set (0.01 sec) Can somebody please explain why this is and how I can get Christopher to show up before Fran! Thanks! -- Chris Wilkinson [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: Red Hat linux database to Mac OS X
Not that particular move, but have used mysqldump/mysql combo to move between linux--windows and back. Works well. -Original Message- From: Greg Grasmehr [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 1:19 PM To: [EMAIL PROTECTED] Subject: Red Hat linux database to Mac OS X Hello, I was wondering if anyone has experienced moving a MySQL database from Linux/Intel X86 to Mac OS X? I plan to use mysqldump of course. I was only wondering if anyone has run into problems they might share. Thanks. Greg __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.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: Red Hat linux database to Mac OS X
Hello, I was wondering if anyone has experienced moving a MySQL database from Linux/Intel X86 to Mac OS X? I plan to use mysqldump of course. I was only wondering if anyone has run into problems they might share. You can't use BDB tables. Thanks. Greg -- Paul DuBois http://www.kitebird.com/ sql, query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql / PHP image link problem.
At 19:20 +0100 6/3/03, Daniel Crompton wrote: I have a mysql table column called 'imagecolumn' The rows contain links i.e. image.gif image.gif null image.gif image.gif To display in my web page im using: img src=?php echo $row_Recordset1['imagecolumn']; ? This displays the above images in my html page The problem i have is where the field in my table is left blank (null), a web browser shows this as a broken image link icon. i.e. it doesn't ignore it. Does anyone know how I can get my page to ignore it if there is no link. Add WHERE imagecolumn IS NOT NULL to your SELECT statement. Then you won't select the row at all. Or did you mean something else? -- Paul DuBois http://www.kitebird.com/ sql, query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: help creating foreign keys
In InnoDB you *must* put an index on foreign keys. It doesn't do this for you automatically. You can do it in one statement: create table bar ( bar_idint unsigned auto_increment, foo_idint unsigned, bar_value int, constraint bar_pk primary key (bar_id), index (foo_id), foreign key (foo_id) references foo (foo_id) ) type=innodb; -Original Message- From: Jeff Mathis [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 10:09 AM To: Justin Scheiber Cc: [EMAIL PROTECTED] Subject: Re: help creating foreign keys the syntax for foreign key creation is different than what you have. You need an alter table statement. create table bar ( bar_idint unsigned auto_increment primary key, foo_idint unsigned, bar_value int, ) type=innodb; alter table bar add constraint foreign key (foo_id) references foo(foo_id) on delete cascade; Justin Scheiber wrote: Hello, I want to create the following tables - where a foriegn key references an auto_incremented primary key of another table. In my simple logic, it seem like such a thing should be possible -- after all, i just need the value of the referenced primary key. I know you can't have 2 auto_increment columns in a table, and I have read up on the errno: 150 but it still seems like this should be possible.Do I need to rethink the table structure? Or do I just not understand something here? create table foo ( foo_idint unsigned auto_increment, foo_value int, primary key(foo_id) ) type=innodb; create table bar ( bar_idint unsigned auto_increment, foo_idint unsigned, bar_value int, primary key (bar_id), foreign key(foo_id) references foo(foo_id), ) type=innodb; ERROR 1005: Can't create table './test/bar.frm' (errno: 150) -justin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql / PHP image link problem.
keep the lines at or under 72 characters, please. # [EMAIL PROTECTED] / 2003-06-03 19:20:17 +0100: To display in my web page im using: img src=?php echo $row_Recordset1['imagecolumn']; ? This displays the above images in my html page The problem i have is where the field in my table is left blank (null), a web browser shows this as a broken image link icon. i.e. it doesn't ignore it. Does anyone know how I can get my page to ignore it if there is no link. like: don't output the tag if the image is null? -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[4]: MySql data between Linux and Windows
Carlos, Character set In windows 2000 latin1 In Linux hosting latin1 Character sets In windows 2000 latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 In Linux latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 phpMyAdmin version Windows 2.5.0 Linux 2.4.0 MySql versions Windows MySQL 3.23.52-nt Linux MySQL 3.23.56-log I'm giving up, sorry. I cannot see anything in the above that would cause problems with Spanish characters when exchanging the data between those two servers. Does anyone else on this list see more than I do? Stefan Hinz [EMAIL PROTECTED] wrote: Carlos, The character sets in both mysql severs in linux and windows are the same. When I query a table which has the same data in spanish with accents I can see the characters written are different. 1) So what are the values of the character_set and character_sets variables, anyway? 2) Which MySQL server versions do you use, for the local and the ISP server as well? 3) Which version of phpMyAdmin do you use? Do they differ on those two machines? My goal is to extract data from one of the data bases and be able to get then to the other database between linux ands windows without any problem. Apache is used as web server in both Linux and windows. Regards. Carlos Díaz Stefan Hinz wrote: Carlos, We have a hosting provider with a Linux box and an MySql database. We have a local Mysql database. In both machines I use phpMyAdmin. On the startpage (right frame) of phpMyAdmin you should see a link called Show MySQL system variables. Look what the values of the variables character_set and character_sets are. When I export from Windows to Linux then all the data in Linux is messed up with respect to special characters, we work in spanish and all accents are lost and converted to some extrange character. When I export from Linux to Windows the same thing happens. Of course these has to do with character sets. Most probably the two MySQL servers you are using have different character sets. ¿Any suggestion? You can find more detailed information here: http://www.mysql.com/doc/en/Character_sets.html And here: http://www.mysql.com/doc/en/SHOW_VARIABLES.html Regards, -- Stefan Hinz iConnect GmbH Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] - Do You Yahoo!? Todo lo que quieres saber de Estados Unidos, América Latina y el resto del Mundo. Visíta Yahoo! Noticias. Regards, -- Stefan Hinz iConnect GmbH Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] - Do You Yahoo!? Todo lo que quieres saber de Estados Unidos, América Latina y el resto del Mundo. Visíta Yahoo! Noticias. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql / PHP image link problem.
You'll have to check if the result is NULL or not. Try this ? echo ($row_Recordset1['imagecolumn'] != NULL || ($row_Recordset1['imagecolumn'] != ) ? img src=\.$row_Recordset1['imagecolumn'].\ : ; ? This is print out nothing if your result is equal to null. On another notes, how do you specify the width and height of this image? Or the alt tag, or set the border so that on older versions on Netscape it does not default to 1. Why don't you try saving the entire image tag in the db, for example img src=image.gif width=12 height=30 alt=Image border=0 and then you can use the same code from above --- Jonathan -Original Message- From: Daniel Crompton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 1:20 PM To: [EMAIL PROTECTED] Subject: Mysql / PHP image link problem. I have a mysql table column called 'imagecolumn' The rows contain links i.e. image.gif image.gif null image.gif image.gif To display in my web page im using: img src=?php echo $row_Recordset1['imagecolumn']; ? This displays the above images in my html page The problem i have is where the field in my table is left blank (null), a web browser shows this as a broken image link icon. i.e. it doesn't ignore it. Does anyone know how I can get my page to ignore it if there is no link. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with priv_db
Hello, Egor Egorov wrote: I even flushed the privileges and restarted the MySQL server. Any idea? Please, show me the structure of table 'user'. I found it. Create_tmp_table_priv was Y. thanks anyway, elian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
optimize entire db
Hi - Can anyone give the command line syntax for optimizing the entire db? thanks in advance. MS
MySQL stops working, process cannot be killed
I have been having a problem with MySQL lately. Specs are: RedHat Linux 7.3 MySQL 3.23.56, for pc-linux (i686) (same behavior with 4.0.12 as well) Apache 1.3.27 PHP 4.3.2 (most (99%) of the MySQL connections are made through PHP scripts running as an apache module) The problem happens as such: 1) MySQL will be running fine for a day or so, then any connections attempted to MySQL will start to 'hang' - no error messages or connection refused just sitting there trying to connect. 2) After around 10 minutes MySQL will start to return an error message thatthere are too many connections and the connection has been refused. 3) A look at 'mysqladmin processlist' shows what appears to be normal processes doing normal queries or trying to open tables. The last time this happened there were 60 processes all attempting to open different tables. There were no queries that were using any table that would have kept the other processes sitting. Nothing is logged to the slow query log which is set to log any queries running longer than a second. At this point, a look in 'top' will show a single MySQL process running at 99.9% of CPU. Any attempts to restart the MySQL server will result in an error saying a mysqld server is already running on 3306. There are no other errors listed in the error file for before or during the crash/incident. At this point, a 'kill -9 pid' on the pid of the MySQL process in top has no effect and does not kill the process. Other signals also have no effect. After killing the parents of the process, MySQL will restart; however, the process in top stays there at 99.9% CPU utilization until the server is rebooted. This happens about once or twice per day, with seemingly no predictability. If anyone has any steps I can take to trouble shoot this or I would be grateful. -Steven -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Secure connections
Hi, Please can someone help with advise on setting up a Secure MySQL Connection. The details are as follows. I have built and configured OpenSSL 0.9.7 and MySQL 4.0.12. MySQL was configured with the options ./configure \ --enable-local-infile \ --with-vio --with-openssl=/usr/sfw \ --enable-thread-safe-client --with-pthread \ --sysconfdir=/etc/sfw/mysql \ --prefix=/usr/sfw/mysql I have set up the certificates as described in http://www.mysql.com/doc/en/Secure_Create_Certs.html and ran the mysql daemon as follows . CONFF=/export/openssl/my.cnf /usr/sfw/mysql/bin/mysqld_safe --defaults-file=${CONFF} --user=mysql the contents of /export/openssl/my.cnf being ... [client] ssl-ca=/export/openssl/cacert.pem ssl-cert=/export/openssl/client-cert.pem ssl-key=/export/openssl/client-key.pem [mysqld] master-ssl-ca=/export/openssl/cacert.pem master-ssl-cert=/export/openssl/server-cert.pem master-ssl-key=/export/openssl/server-key.pem mysqld started okay after changing the [mysqld] entries and added the 'master-' bits. I then tried to run the 'mysql' client as follows ... /usr/sfw/mysql/bin/mysql --defaults-file=/export/openssl/my.cnf but this fails with the following /usr/sfw/mysql/bin/mysql: ERROR: unknown variable 'ssl-ca=/export/openssl/cacert.pem' The clients does not seem to like these options either in the my.cnf file or if put on the mysql command-line. Has anyone got any ideas where I am going wrong? Thanks Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: debuggine 1205 / LOCK wait timeout exceeded errors
Roman, you can use SHOW INNODB STATUS to look what active transactions you have and how many lock structs they have. Also innodb_lock_monitor helps. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Roman Neuhauser [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, June 03, 2003 7:51 PM Subject: debuggine 1205 / LOCK wait timeout exceeded errors Hi there, I have a problem with LOCKs. I use InnoDB tables and transactions. [EMAIL PROTECTED] ~ 1001:0 /usr/local/libexec/mysqld --version /usr/local/libexec/mysqld Ver 4.0.13 for portbld-freebsd4.8 on i386 [EMAIL PROTECTED] ~ 1002:0 uname -sr FreeBSD 4.8-RC [EMAIL PROTECTED] ~ 1003:0 httpd -v Server version: Apache/1.3.26 (Unix) Server built: Jul 23 2002 14:12:25 mod_php-4.3.1 linked with libmysqlclient 4.0.11a-gamma This is one such case (one PHP script run, taken from the query log): 030603 18:18:47 19 Init DB webed 19 Init DB webed 19 Query SELECT COUNT(*) FROM editor WHERE id = 1 19 Init DB webed 19 Query SELECT e.login, e.authorized, e.firstname, e.lastname, e.validfrom, e.validtill, e.createdby, e.createdon, e.changedby, e.changedon, e.caps, e.wysiwyg FROM editor e WHERE id = 1 030603 18:18:48 19 Init DB webed 19 Query SELECT COUNT(*) FROM server WHERE id = 4 19 Init DB webed 19 Query SELECT COUNT(*) FROM editor WHERE id = 1 19 Init DB webed 19 Query SELECT g.id AS gid, g.name FROM acl_group g, acl_member m WHERE g.id = m.gid AND uid = 1 19 Init DB webed 19 Query SELECT MAX(access) AS max, MIN(access) AS min FROM acl_access a WHERE a.objtype = 128 AND a.objid = 4 AND a.gid IN (1) GROUP BY objid 19 Init DB webed 19 Query DELETE FROM acl_access WHERE objtype = 128 AND objid = 4 Here the server emitted 1205. DELETE FROM acl_access WHERE objtype = 128 AND objid = 4 [nativecode = 1205 * * LOCK wait timeout exceeded; Try restarting TRANSACTION ] I don't see what should cause it to behave so. I'm the only user ATM, IOW the listing above is unaltered in any way, and represents exactly one run of the PHP script. Notice the lack of any transaction-related or LOCK commands (in most cases, however, the DML command is preceeded by a BEGIN, and immediatelly [that is, after the timeout] followed by a ROLLBACK). I'd love to see this resolved, and will happily provide any info requested. TIA HAND -- FreeBSD 4.8-RC 6:23PM up 14 days, 2:54, 16 users, load averages: 0.04, 0.04, 0.00 -- 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 / PHP image link problem.
FWIW, there is a PHP-DB mailing list that might prove more helpful to you. I know certain folk on both lists can get mighty annoyed when an off-topic question is posted, so I thought I'd give you a heads-up. [EMAIL PROTECTED] Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Daniel Crompton [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, 03 June, 2003 14:20 Subject: Mysql / PHP image link problem. I have a mysql table column called 'imagecolumn' The rows contain links i.e. image.gif image.gif null image.gif image.gif To display in my web page im using: img src=?php echo $row_Recordset1['imagecolumn']; ? This displays the above images in my html page The problem i have is where the field in my table is left blank (null), a web browser shows this as a broken image link icon. i.e. it doesn't ignore it. Does anyone know how I can get my page to ignore it if there is no link. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
strange SSH/libmysql.dll problem - this time with dbug infos
I think I need a MySQL code guru... I am using the 4.0.12 client library on a Win2k host to connect to a 4.0.13 linux server. If I connect directly to the server using mysql_real_connect, everything runs fine. However, I want to use SSH tunneling. So I set up a SSHv2 tunneling connection from localhost:3306 to server:3306. If I use the command line, i.e. mysql -u foo -pbar, I can connect to the remote server via SSH. The same is, if I use tools like SQLyog. I enable the dbug, here is the output. The program hangs at the last line... my_init | my_win_init | my_win_init | exit: home: '(null)' my_init libmysql_init libmysql_init my_malloc | my: Size: 544 MyFlags: 48 | exit: ptr: 4d144c8 my_malloc mysql_real_connect | enter: host: 127.0.0.1 db: rssvertrieb user: rss | info: Server name: '127.0.0.1'. Named Pipe: MySQL | error: host: '127.0.0.1' socket: '' named_pipe: 0 have_tcpip: 1 | info: Server name: '127.0.0.1'. TCP sock: 3306 | vio_new | | enter: sd=1140 | | my_malloc | | | my: Size: 84 MyFlags: 16 | | | exit: ptr: 4d124b0 | | my_malloc | | vio_reset | | | enter: type=1 sd=1140 localhost=0 | | vio_reset | vio_new | my_net_init | | my_malloc | | | my: Size: 8199 MyFlags: 16 | | | exit: ptr: 4d14720 | | my_malloc | | vio_fastsend | | | exit: 0 | | vio_fastsend | my_net_init | vio_keepalive | | enter: sd=1140, set_keep_alive=1 | vio_keepalive | vio_is_blocking | | exit: 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: optimize entire db
You can use this script that I wrote. Also you can change OPTIMIZE TABLE with ANAYLIZE TABLE #!/usr/bin/perl -w # If you use this give me CREDIT!! :) Dathan Vance Pattishall # use strict; use DBI; use lib '/site/lib'; my $USER = ; my $PASSWD = ; my ($host,$DB,$TABLE) = @ARGV; die must specify host if (!defined $host); my $dbh = DBI-connect(DBI:mysql:mysql:$host, $USER, $PASSWD); die can't connect to $host if (!defined $dbh); my $dblist; if (!$DB) { $dblist = $dbh-selectall_arrayref(SHOW DATABASES); } else { $dblist = [[$DB]]; } for my $dr (@$dblist) { my ($db) = @$dr; next if ($db eq 'mysql' or $db eq 'test' or ($db =~ /\./) or ($db =~ /\-/)); print checking $db\n; $dbh-do(USE $db); my $tablist; if ($TABLE) { $tablist = $dbh-selectall_arrayref(SHOW TABLES LIKE '$TABLE\%'); } else { $tablist = $dbh-selectall_arrayref(SHOW TABLES); } for my $tr (@$tablist) { my ($table) = @$tr; print ...$table...\n; my $rr = $dbh-selectall_arrayref(OPTIMIZE TABLE $table); print OK\n; } } $dbh-disconnect(); ---Original Message- --From: Mark Stringham [mailto:[EMAIL PROTECTED] --Sent: Tuesday, June 03, 2003 12:40 PM --To: [EMAIL PROTECTED] --Subject: optimize entire db -- --Hi - -- --Can anyone give the command line syntax for optimizing the entire db? --thanks in advance. -- --MS -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re[4]: MySql data between Linux and Windows
And you are completely wrigth. There is no MySql problem. I solved the problem but I did not gave you all the necessary information. In the middle of all the problem there is also a php application, apache and the OS so I was confused. The problem was with php. In php.ini there is the following configuration: ; As of 4.0b4, PHP always outputs a character encoding by default in ; the Content-type: header. To disable sending of the charset, simply ; set it to be empty. ; ; PHP's built-in default is text/html default_mimetype = text/html default_charset = UTF-8 I am testing in Linux and for testing purposes I have two scripts one for the database in Linux and another that have the windows data. I have only tested in Linux because wrigth now I cannot access the windows machine. With the above settings the Linux database will work fine. If I change the default_charset to iso-8859-1 the linux database will show strange chars but if I load the windows database with iso-8859-1 as default charset then it will show all characters OK. So what I will do is to put default_charset = UTF-8 in the windows machine (I have no access to the hosting of course!) and it will work fine when exporting to Linux. I will test tomorrow. If there is some problem I will mail, if all goes well as I suspect no mail. Since these migth look confusing these is the solution I found. Put in php.ini the same default charset as your hosting if you want to be able to emulate the application and export your data without any trouble. I apologize for taking some of your time and hope these will be helpfull to someone in the future. Best regards. Carlos Díaz. --- Stefan Hinz [EMAIL PROTECTED] escribió: Carlos, Character set In windows 2000 latin1 In Linux hosting latin1 Character sets In windows 2000 latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 In Linux latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 phpMyAdmin version Windows 2.5.0 Linux 2.4.0 MySql versions Windows MySQL 3.23.52-nt Linux MySQL 3.23.56-log I'm giving up, sorry. I cannot see anything in the above that would cause problems with Spanish characters when exchanging the data between those two servers. Does anyone else on this list see more than I do? Stefan Hinz [EMAIL PROTECTED] wrote: Carlos, The character sets in both mysql severs in linux and windows are the same. When I query a table which has the same data in spanish with accents I can see the characters written are different. 1) So what are the values of the character_set and character_sets variables, anyway? 2) Which MySQL server versions do you use, for the local and the ISP server as well? 3) Which version of phpMyAdmin do you use? Do they differ on those two machines? My goal is to extract data from one of the data bases and be able to get then to the other database between linux ands windows without any problem. Apache is used as web server in both Linux and windows. Regards. Carlos Díaz Stefan Hinz wrote: Carlos, We have a hosting provider with a Linux box and an MySql database. We have a local Mysql database. In both machines I use phpMyAdmin. On the startpage (right frame) of phpMyAdmin you should see a link called Show MySQL system variables. Look what the values of the variables character_set and character_sets are. When I export from Windows to Linux then all the data in Linux is messed up with respect to special characters, we work in spanish and all accents are lost and converted to some extrange character. When I export from Linux to Windows the same thing happens. Of course these has to do with character sets. Most probably the two MySQL servers you are using have different character sets. ¿Any suggestion? You can find more detailed information here: http://www.mysql.com/doc/en/Character_sets.html And here: http://www.mysql.com/doc/en/SHOW_VARIABLES.html Regards, -- Stefan Hinz iConnect GmbH Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] - Do You Yahoo!? Todo lo que quieres saber de Estados Unidos, América Latina y el resto del Mundo. Visíta Yahoo! Noticias. Regards, -- Stefan Hinz iConnect GmbH Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] - Do You Yahoo!? Todo lo que quieres saber de Estados Unidos, América Latina y el resto del Mundo. Visíta Yahoo! Noticias. Regards, -- Stefan
Re: Red Hat linux database to Mac OS X
You shouldn't have any problems if you are using mysqldump. I've used it plenty of times on OSX. On Tuesday, June 3, 2003, at 02:19 PM, Greg Grasmehr wrote: Hello, I was wondering if anyone has experienced moving a MySQL database from Linux/Intel X86 to Mac OS X? I plan to use mysqldump of course. I was only wondering if anyone has run into problems they might share. Thanks. Greg __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: optimize entire db
I've used a simply shell script in the past.. run from cron to do it... I just see someone posted a perl solution.. I've used a php one aswell.. #!/bin/sh # DB OPTIMIZE SCRIPT - !! WARNING, DOES TABLE LOCKING DURING OPTIMIZES user=root pass=secret host=10.1.1.1 db=mydb [EMAIL PROTECTED] mysql=/usr/local/bin/mysql ## BUILD TABLE LIST flag=0 for table in \ `echo show tables|$mysql --host=$host --user=$user --password=$pass $db | tail +2` ; \ do \ if test $flag == 1 then dblist=$dblist, fi dblist=$dblist $table flag=1 done ## RUN OPTIMIZE echo optimize table $dblist|$mysql --host=$host --user=$user --password=$pass $db /tmp/db_optreport ; \ mail -s Nightly DB Optimize $report /tmp/db_optreport rm /tmp/db_optreport On Tue, 3 Jun 2003, Mark Stringham wrote: Hi - Can anyone give the command line syntax for optimizing the entire db? thanks in advance. MS -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: optimize entire db
Mark, Can anyone give the command line syntax for optimizing the entire db? thanks in advance. mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3...] This program can be used to CHECK (-c,-m,-C), REPAIR (-r), ANALYZE (-a) or OPTIMIZE (-o) tables. Some of the options (like -e or -q) can be used at the same time. It works on MyISAM and in some cases on BDB tables. (The above is a copy of the output of mysqlcheck --help.) Note that you cannot use that program for InnoDB tables. To check all tables in a database mydb, you would issue: mysqlcheck --optimize --databases mydb Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql / PHP image link problem.
?php if($row_Recordset1['imagecolumn']) echo 'img src='.$row_Recordset1[imagecolumn].''; ? Peter -Original Message- From: Becoming Digital [mailto:[EMAIL PROTECTED] Sent: 03 June 2003 20:57 To: [EMAIL PROTECTED] Subject: Re: Mysql / PHP image link problem. FWIW, there is a PHP-DB mailing list that might prove more helpful to you. I know certain folk on both lists can get mighty annoyed when an off-topic question is posted, so I thought I'd give you a heads-up. [EMAIL PROTECTED] Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Daniel Crompton [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, 03 June, 2003 14:20 Subject: Mysql / PHP image link problem. I have a mysql table column called 'imagecolumn' The rows contain links i.e. image.gif image.gif null image.gif image.gif To display in my web page im using: img src=?php if($row_Recordset1['imagecolumn']) echo $row_Recordset1['imagecolumn']; ? This displays the above images in my html page The problem i have is where the field in my table is left blank (null), a web browser shows this as a broken image link icon. i.e. it doesn't ignore it. Does anyone know how I can get my page to ignore it if there is no link. -- 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: table copying/replication
Thanks for the reply. I didn't explain properly :) A diagram should help: || |-| 1 || 2 | || |stage| -- |prod| -- |-| || |-| || |-|replicas| || 'stage' is a staging mysql instance, where changes are made all the time. When the data is ready for production, it needs to be pushed to 'prod', at which time it will be replicated out to all the slaves. Step 2 is covered by the answer to my previous question. Step 1 is really my question. My need is that somehow a table already existing on stage can be copied/replicated/etc over to prod, but _only_ when requested, and then immediately. Any thoughts? Thanks, Ross On Tue, 2003-06-03 at 12:07, Dathan Vance Pattishall wrote: ---Original Message- --From: Ross Simpson [mailto:[EMAIL PROTECTED] --Sent: Tuesday, June 03, 2003 10:31 AM --To: [EMAIL PROTECTED] --Subject: table copying/replication -- --Hello, -- --I have a need for fast copying of a specific table from a master mysql --server to a number of slave servers (say 5). Create the table on the master and if the master and slave config is working then the same table will be on the slave. -- --The database in question could potentially have up to 2000 tables, and --at any time, one of those tables would need to be copied to all 5 of the --slaves, upon command of the master. Make sure you have enough inodes that's 6000 files that will be opened. Also set your ulimit high enough to open all the files. Replication will perform the same action on the slaves as initiated by the master. There is no need for a copy. --I also looked at doing table copies (insert into .. select * from ..), --but these seem pretty slow. It's building the index on the fly as well, if there are indexes on the dst table. It does have to scan the src table and for every row insert it into the dst table. You can tweak you're my.cnf values to make that operation happen faster. -- --Has anyone solved this problem before? Any ideas that would help out? -- Yes, the mysql team with replication. -- --Ross Simpson [EMAIL PROTECTED] --MapQuest.com -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- Ross Simpson [EMAIL PROTECTED] MapQuest.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help creating foreign keys
yes, quite right. thanks for pointing that out. jeff Kevin Fries wrote: In InnoDB you *must* put an index on foreign keys. It doesn't do this for you automatically. You can do it in one statement: create table bar ( bar_idint unsigned auto_increment, foo_idint unsigned, bar_value int, constraint bar_pk primary key (bar_id), index (foo_id), foreign key (foo_id) references foo (foo_id) ) type=innodb; -Original Message- From: Jeff Mathis [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 10:09 AM To: Justin Scheiber Cc: [EMAIL PROTECTED] Subject: Re: help creating foreign keys the syntax for foreign key creation is different than what you have. You need an alter table statement. create table bar ( bar_idint unsigned auto_increment primary key, foo_idint unsigned, bar_value int, ) type=innodb; alter table bar add constraint foreign key (foo_id) references foo(foo_id) on delete cascade; Justin Scheiber wrote: Hello, I want to create the following tables - where a foriegn key references an auto_incremented primary key of another table. In my simple logic, it seem like such a thing should be possible -- after all, i just need the value of the referenced primary key. I know you can't have 2 auto_increment columns in a table, and I have read up on the errno: 150 but it still seems like this should be possible.Do I need to rethink the table structure? Or do I just not understand something here? create table foo ( foo_idint unsigned auto_increment, foo_value int, primary key(foo_id) ) type=innodb; create table bar ( bar_idint unsigned auto_increment, foo_idint unsigned, bar_value int, primary key (bar_id), foreign key(foo_id) references foo(foo_id), ) type=innodb; ERROR 1005: Can't create table './test/bar.frm' (errno: 150) -justin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: table copying/replication
Hi, Assuming that this system runs on *nix and that prod is set up to replicate to all the replicas you could write a small bash script to push the data from the stage to the prod which would then replicate as normal. I would author the script something like this... [bash]# mysqldump -e -hstage -usomeuser somedatabasename | mysql -hprod -usomeuser This would dump the entire somedatabasename to the prod server which would then replicate it to all the slaves using the -e option for faster inserts: If you wanted to overwrite the existing data then use the --add-drop-table option to mysqldump like this: mysqldump --add-drop-table -e -hstage -usomeuser somedatabasename | mysql -hprod -usomeuser This will then replicate as fast as your network/hardware will allow. Hope this helps.. (I'm all for open source but it's a bit weird that I'm helping out our state-side mapping competitors here - at least it's not microsoft - I'll check tomorrow to make sure I don't get sacked :) Cheers, Andrew multimap.com -Original Message- From: Ross Simpson [mailto:[EMAIL PROTECTED] Sent: Tuesday 03 June 2003 22:44 To: Dathan Vance Pattishall Cc: [EMAIL PROTECTED] Subject: RE: table copying/replication Thanks for the reply. I didn't explain properly :) A diagram should help: || |-| 1 || 2 | || |stage| -- |prod| -- |-| || |-| || |-|replicas| || 'stage' is a staging mysql instance, where changes are made all the time. When the data is ready for production, it needs to be pushed to 'prod', at which time it will be replicated out to all the slaves. Step 2 is covered by the answer to my previous question. Step 1 is really my question. My need is that somehow a table already existing on stage can be copied/replicated/etc over to prod, but _only_ when requested, and then immediately. Any thoughts? Thanks, Ross On Tue, 2003-06-03 at 12:07, Dathan Vance Pattishall wrote: ---Original Message- --From: Ross Simpson [mailto:[EMAIL PROTECTED] --Sent: Tuesday, June 03, 2003 10:31 AM --To: [EMAIL PROTECTED] --Subject: table copying/replication -- --Hello, -- --I have a need for fast copying of a specific table from a master mysql --server to a number of slave servers (say 5). Create the table on the master and if the master and slave config is working then the same table will be on the slave. -- --The database in question could potentially have up to 2000 tables, and --at any time, one of those tables would need to be copied to all 5 --of the --slaves, upon command of the master. Make sure you have enough inodes that's 6000 files that will be opened. Also set your ulimit high enough to open all the files. Replication will perform the same action on the slaves as initiated by the master. There is no need for a copy. --I also looked at doing table copies (insert into .. select * from ..), --but these seem pretty slow. It's building the index on the fly as well, if there are indexes on the dst table. It does have to scan the src table and for every row insert it into the dst table. You can tweak you're my.cnf values to make that operation happen faster. -- --Has anyone solved this problem before? Any ideas that would help out? -- Yes, the mysql team with replication. -- --Ross Simpson [EMAIL PROTECTED] --MapQuest.com -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- Ross Simpson [EMAIL PROTECTED] MapQuest.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: MySQL Secure connections
I've just done a similar thing. Are you sure that you are running the mysql client that you have just compiled. I used the following build options CFLAGS=-O3 CXX=gcc CXXFLAGS=-O3 -felide-constructors -fno-exceptions \ -fno-rtti ./configure \ --enable-assembler \ --enable-local-infile \ --with-mysqld-user=mysql \ --with-unix-socket-path=/var/lib/mysql/mysql.sock \ --prefix=/ \ --with-extra-charsets=complex \ --exec-prefix=/usr \ --libexecdir=/usr/sbin \ --sysconfdir=/etc \ --datadir=/usr/share \ --localstatedir=/var/lib/mysql \ --infodir=/usr/share \ --includedir=/usr/include \ --mandir=/usr \ --with-embedded-server \ --enable-thread-safe-client \ --with-vio \ --with-openssl ; I nicked this out of the RPM build spec and added the last two options. Gareth. On Tue, 2003-06-03 at 16:04, Paul Cunningham wrote: Hi, Please can someone help with advise on setting up a Secure MySQL Connection. The details are as follows. I have built and configured OpenSSL 0.9.7 and MySQL 4.0.12. MySQL was configured with the options ./configure \ --enable-local-infile \ --with-vio --with-openssl=/usr/sfw \ --enable-thread-safe-client --with-pthread \ --sysconfdir=/etc/sfw/mysql \ --prefix=/usr/sfw/mysql I have set up the certificates as described in http://www.mysql.com/doc/en/Secure_Create_Certs.html and ran the mysql daemon as follows . CONFF=/export/openssl/my.cnf /usr/sfw/mysql/bin/mysqld_safe --defaults-file=${CONFF} --user=mysql the contents of /export/openssl/my.cnf being ... [client] ssl-ca=/export/openssl/cacert.pem ssl-cert=/export/openssl/client-cert.pem ssl-key=/export/openssl/client-key.pem [mysqld] master-ssl-ca=/export/openssl/cacert.pem master-ssl-cert=/export/openssl/server-cert.pem master-ssl-key=/export/openssl/server-key.pem mysqld started okay after changing the [mysqld] entries and added the 'master-' bits. I then tried to run the 'mysql' client as follows ... /usr/sfw/mysql/bin/mysql --defaults-file=/export/openssl/my.cnf but this fails with the following /usr/sfw/mysql/bin/mysql: ERROR: unknown variable 'ssl-ca=/export/openssl/cacert.pem' The clients does not seem to like these options either in the my.cnf file or if put on the mysql command-line. Has anyone got any ideas where I am going wrong? Thanks Paul -- Gareth Davis [EMAIL PROTECTED] Logical Practice Systems Limited -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: table copying/replication
I looked at that type of idea, and the reason I didn't mention it was the indexes -- each table involved has ~5 indexes, and on a large table, that equals a lot of work that's already done on 'stage' that must be redone on 'prod'. I'd really like to preserve the indexes from stage if at all possible. Maybe there's a way to dump and reload the indexes as well as the data.. Thanks for the help, and I hope you don't get fired.. I won't tell anyone ;) Ross On Tue, 2003-06-03 at 16:53, Andrew Braithwaite wrote: Hi, Assuming that this system runs on *nix and that prod is set up to replicate to all the replicas you could write a small bash script to push the data from the stage to the prod which would then replicate as normal. I would author the script something like this... [bash]# mysqldump -e -hstage -usomeuser somedatabasename | mysql -hprod -usomeuser This would dump the entire somedatabasename to the prod server which would then replicate it to all the slaves using the -e option for faster inserts: If you wanted to overwrite the existing data then use the --add-drop-table option to mysqldump like this: mysqldump --add-drop-table -e -hstage -usomeuser somedatabasename | mysql -hprod -usomeuser This will then replicate as fast as your network/hardware will allow. Hope this helps.. (I'm all for open source but it's a bit weird that I'm helping out our state-side mapping competitors here - at least it's not microsoft - I'll check tomorrow to make sure I don't get sacked :) Cheers, Andrew multimap.com -Original Message- From: Ross Simpson [mailto:[EMAIL PROTECTED] Sent: Tuesday 03 June 2003 22:44 To: Dathan Vance Pattishall Cc: [EMAIL PROTECTED] Subject: RE: table copying/replication Thanks for the reply. I didn't explain properly :) A diagram should help: || |-| 1 || 2 | || |stage| -- |prod| -- |-| || |-| || |-|replicas| || 'stage' is a staging mysql instance, where changes are made all the time. When the data is ready for production, it needs to be pushed to 'prod', at which time it will be replicated out to all the slaves. Step 2 is covered by the answer to my previous question. Step 1 is really my question. My need is that somehow a table already existing on stage can be copied/replicated/etc over to prod, but _only_ when requested, and then immediately. Any thoughts? Thanks, Ross On Tue, 2003-06-03 at 12:07, Dathan Vance Pattishall wrote: ---Original Message- --From: Ross Simpson [mailto:[EMAIL PROTECTED] --Sent: Tuesday, June 03, 2003 10:31 AM --To: [EMAIL PROTECTED] --Subject: table copying/replication -- --Hello, -- --I have a need for fast copying of a specific table from a master mysql --server to a number of slave servers (say 5). Create the table on the master and if the master and slave config is working then the same table will be on the slave. -- --The database in question could potentially have up to 2000 tables, and --at any time, one of those tables would need to be copied to all 5 --of the --slaves, upon command of the master. Make sure you have enough inodes that's 6000 files that will be opened. Also set your ulimit high enough to open all the files. Replication will perform the same action on the slaves as initiated by the master. There is no need for a copy. --I also looked at doing table copies (insert into .. select * from ..), --but these seem pretty slow. It's building the index on the fly as well, if there are indexes on the dst table. It does have to scan the src table and for every row insert it into the dst table. You can tweak you're my.cnf values to make that operation happen faster. -- --Has anyone solved this problem before? Any ideas that would help out? -- Yes, the mysql team with replication. -- --Ross Simpson [EMAIL PROTECTED] --MapQuest.com -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- Ross Simpson [EMAIL PROTECTED] MapQuest.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Ross Simpson [EMAIL PROTECTED] MapQuest.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: cant grant privileges?!
Hi Allann, Ajos, Mysql List members As far as I know the flush privileges is not necessary when using GRANT. flush privileges is only used after you updated the privilege database mysql manually using INSERT or UPDATE. Somebody please correct me if I am wrong. Best regards Nils Valentin Tokyo/Japan 2003 6 4 01:31ajos : Use it after the grant command: flush privileges; hi everybody... i just installed mysql 4.0.13 on a redhat linux 8.0 when i create a database as root and then grant privileges to another user like this: GRANT ALL ON somedb.* TO [EMAIL PROTECTED] IDENTIFIED BY passwd; Allann J. -- -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: table copying/replication
OK - in that case there's not much you can do with replication... But with mysql you could write some bash to copy the raw files over like: Run this from /datadir (on the server you want to copy from): tar cf - databasedirname | ssh server_you_want_it_to_go_to 'cd /datadir; tar xf -' And supply the password (I think you can do that with a ssh option (or set up some ssh keys)) then (if you have the right permissions set up) - issue a flush tables on all the slaves from the master that will do the job with a simple file transfer. If you don't have ssh then you could set up a nfs mount or something to let your scripts do the copying (I wouldn't recommend nfs for large amounts of fast data transfer personally...) Hope this helps... Andrew -Original Message- From: Ross Simpson [mailto:[EMAIL PROTECTED] Sent: Tuesday 03 June 2003 23:57 To: Andrew Braithwaite Cc: Dathan Vance Pattishall; [EMAIL PROTECTED] Subject: RE: table copying/replication I looked at that type of idea, and the reason I didn't mention it was the indexes -- each table involved has ~5 indexes, and on a large table, that equals a lot of work that's already done on 'stage' that must be redone on 'prod'. I'd really like to preserve the indexes from stage if at all possible. Maybe there's a way to dump and reload the indexes as well as the data.. Thanks for the help, and I hope you don't get fired.. I won't tell anyone ;) Ross On Tue, 2003-06-03 at 16:53, Andrew Braithwaite wrote: Hi, Assuming that this system runs on *nix and that prod is set up to replicate to all the replicas you could write a small bash script to push the data from the stage to the prod which would then replicate as normal. I would author the script something like this... [bash]# mysqldump -e -hstage -usomeuser somedatabasename | mysql -hprod -usomeuser This would dump the entire somedatabasename to the prod server which would then replicate it to all the slaves using the -e option for faster inserts: If you wanted to overwrite the existing data then use the --add-drop-table option to mysqldump like this: mysqldump --add-drop-table -e -hstage -usomeuser somedatabasename | mysql -hprod -usomeuser This will then replicate as fast as your network/hardware will allow. Hope this helps.. (I'm all for open source but it's a bit weird that I'm helping out our state-side mapping competitors here - at least it's not microsoft - I'll check tomorrow to make sure I don't get sacked :) Cheers, Andrew multimap.com -Original Message- From: Ross Simpson [mailto:[EMAIL PROTECTED] Sent: Tuesday 03 June 2003 22:44 To: Dathan Vance Pattishall Cc: [EMAIL PROTECTED] Subject: RE: table copying/replication Thanks for the reply. I didn't explain properly :) A diagram should help: || |-| 1 || 2 | || |stage| -- |prod| -- |-| || |-| || |-|replicas| || 'stage' is a staging mysql instance, where changes are made all the time. When the data is ready for production, it needs to be pushed to 'prod', at which time it will be replicated out to all the slaves. Step 2 is covered by the answer to my previous question. Step 1 is really my question. My need is that somehow a table already existing on stage can be copied/replicated/etc over to prod, but _only_ when requested, and then immediately. Any thoughts? Thanks, Ross On Tue, 2003-06-03 at 12:07, Dathan Vance Pattishall wrote: ---Original Message- --From: Ross Simpson [mailto:[EMAIL PROTECTED] --Sent: Tuesday, June 03, 2003 10:31 AM --To: [EMAIL PROTECTED] --Subject: table copying/replication -- --Hello, -- --I have a need for fast copying of a specific table from a master mysql --server to a number of slave servers (say 5). Create the table on the master and if the master and slave config is working then the same table will be on the slave. -- --The database in question could potentially have up to 2000 --tables, and --at any time, one of those tables would need to be copied to all 5 --of the --slaves, upon command of the master. Make sure you have enough inodes that's 6000 files that will be opened. Also set your ulimit high enough to open all the files. Replication will perform the same action on the slaves as initiated by the master. There is no need for a copy. --I also looked at doing table copies (insert into .. select * from ..), --but these seem pretty slow. It's building the index on the fly as well, if there are indexes on the dst table. It does have to scan the src table and for every row insert it into the dst table. You can tweak you're my.cnf values to make that operation happen faster. -- --Has anyone solved this problem before? Any ideas that would help out? -- Yes, the mysql team
MySQL Mirroring.
Hi, Please excuse me if this question has been discussed before. Is there a way to securely setup 2 MySQL Servers to be in sync with each other? Let's say that I have server A at home and Server B at my work and I want server be to mirror exactly what I do on Server A. Can this be done? Is there a commercial package or provider that does something like this? Your help will be appreciated. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Limiting Characters
I would like to limit the amount of characters that appear on a PHP page, using PHP. Here is the code that I am using: ?php echo b$title/b\nbr; echo $author\np; echo $article_contents['LimitChars']; ? This code works, however it limits the characters to one (1) character. I would like to have the freedom to limit the characters to any length I wish. Is there any code that does this? Thanks, Steve Marquez [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Mirroring.
Assuming that you speak english - you can do the following... http://www.mysql.com/doc/en/Replication.html Cheers, Andrew -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday 04 June 2003 00:24 To: [EMAIL PROTECTED] Subject: MySQL Mirroring. Hi, Please excuse me if this question has been discussed before. Is there a way to securely setup 2 MySQL Servers to be in sync with each other? Let's say that I have server A at home and Server B at my work and I want server be to mirror exactly what I do on Server A. Can this be done? Is there a commercial package or provider that does something like this? Your help will be appreciated. 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: MySQL Mirroring.
My Message was in English. Thank you for your help. On Wed, 4 Jun 2003 00:33:10 +0100 , Andrew Braithwaite [EMAIL PROTECTED] wrote: Assuming that you speak english - you can do the following... http://www.mysql.com/doc/en/Replication.html Cheers, Andrew -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday 04 June 2003 00:24 To: [EMAIL PROTECTED] Subject: MySQL Mirroring. Hi, Please excuse me if this question has been discussed before. Is there a way to securely setup 2 MySQL Servers to be in sync with each other? Let's say that I have server A at home and Server B at my work and I want server be to mirror exactly what I do on Server A. Can this be done? Is there a commercial package or provider that does something like this? Your help will be appreciated. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: More than 255 chars in column in heap table]
Hi, I want to make a HEAP table which must contain somewhat large text (at least larger than 255 chars), but I need to store a little more data. Is it somehow possible to have a column in a heap table which can contain more than 255 bytes? (I'm thinking of not pretty much more like 1024 chars). thanks in advanced, Frank de Bot -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Regarding inoperable symbolic links in Win2K Pro
Hi, I recently posted an email about 'Inoperable Symbolic Links in Win2K Pro ' and I subsequently subscribed to the belief that MySQL would fail when installed in a location that was different that the default location. I tried a myriad of things to get the database system to function correctly. I could not get it to work when it was installed as a service or when the data was located somewhere different that drive c. After a couple of days I gave up and removed MySQL. But since I am evaluating databases for use on my clients as well as my own platforms, I decided to try using MySQL one more time. But this time I went back through six months of mail list archives and found the answer to my problems with MySQL. I had access to the drive where I was installing the data restricted and when the MySQL attempted to access the drive an error was generated. The whole point of this story is two things, 1.) My setup was the problem and not the software and 2.) The mail list archive would benefit from some organizing. Maybe the mail threads could be arranged into sections of similar information. I would be willing to help. CLN
Re: Regarding inoperable symbolic links in Win2K Pro
Hi Charles, Having spoken to somebody from MySQL AB in person last month here in Tokyo I know that the problem seems to be adressed currently. What I dont know is when, how and which new mechanism will be applied to the mailing list. Best regards Nils Valentin Tokyo/Japan 2003 6 4 10:06Charles L. Nelson : Hi, I recently posted an email about 'Inoperable Symbolic Links in Win2K Pro ' and I subsequently subscribed to the belief that MySQL would fail when installed in a location that was different that the default location. I tried a myriad of things to get the database system to function correctly. I could not get it to work when it was installed as a service or when the data was located somewhere different that drive c. After a couple of days I gave up and removed MySQL. But since I am evaluating databases for use on my clients as well as my own platforms, I decided to try using MySQL one more time. But this time I went back through six months of mail list archives and found the answer to my problems with MySQL. I had access to the drive where I was installing the data restricted and when the MySQL attempted to access the drive an error was generated. The whole point of this story is two things, 1.) My setup was the problem and not the software and 2.) The mail list archive would benefit from some organizing. Maybe the mail threads could be arranged into sections of similar information. I would be willing to help. CLN -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Find out what version I am using?
At 10:10 +0900 6/4/03, Nils Valentin wrote: offlist Hi Paul, did I get something wrong or did you actually mean mysql SELECT VERSION(); instead of mysql SHOW VERSION(); ??? You're right, I'm wrong. SELECT VERSION(); (In case I am right, I didnt post as I thought is not so polite to overwrite your statement) I was incorrect, so I'll cc: this back to the list as well. Thanks for pointing it out. Best regards Nils Valentin Tokyo/Japan 2003îN 6åé 2ì åéójì 09:54ÅAPaul DuBois ÇÇÒÇÕèëÇ´ÇÐǵÇ: At 22:52 -0400 5/31/03, Mehrdad Ziaei wrote: That will give you version of mysql client you're running, That seems to have been what the original question was. For version of mysql server, after successful connection to mysql, use status command. mysql status Or telnet to port 3306 of server running mysql C:\telnet 192.168.1.2 3306 you'll see few garbage and mysql version number in there too. Re: that latter suggestion, it's easier to simply issue this query from the mysql client: mysql SHOW VERSION(); Then you don't have to ignore all the garbage characters. :-) Me - Original Message - From: Steven Kreuzer [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Mysql [EMAIL PROTECTED] Sent: Saturday, May 31, 2003 10:32 PM Subject: Re: Find out what version I am using? $ mysql --version On Sat, 2003-05-31 at 22:21, Robert Mark Bram wrote: Howdy all! How do I find out what version of mysql I am running from the command line? Is there a type of system select statement I can execute? Rob :) :- : :-} -- Paul DuBois http://www.kitebird.com/ sql, query -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]