Re: Character set issue ( maybe )
Hello. I've been able to insert Russian characters (utf8) through the QueryBrowser under Linux (FC4). Please, could you provide CREATE statement for you table and output of: show variables like '%char%'; As far as I know, QueryBrowser invokes SET NAMES 'utf8' by default. And the only way to force it to use your connection character sets is to run a transaction. Are characters that you're pasting into QueryBrowser displayed correctly in the query tab? Daniel Kasak wrote: OK then. Lets re-word the question ... Has anyone been able to successfully enter text of a non-standard character set ( Latin 1, UTF8 ) into Query Browser? How about upload via a Perl script? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question: listing open issues regardless of project
Hello. Please, I don't need comments telling me that I'm missing the semicolon or that comments such as doesn't work are worthless. I know there's something inherently wrong with this query (obviously . . . since it doesn't give me the results that I am looking for). Does anyone havea constructive comment as to how to do this? Please, could you answer what are you going to get in the last columns. You've said that you had problems with the last seven columns, but from the logic of your query I see that you just want a column, which changes its value depending on the value in other field. Am I correct? Please provide a sample output (what you want to see in the results) in case I'm wrong. Now I'm not talking about syntax, but rather about sense of the query. Kraer, Joseph wrote: I am sending this message to both Eventum and MySQL support lists. I am trying to write a select statement in the MySQL Query Browser (v. 1.1.10) on my Eventum (v. 1.4) database; MySQL version is 4.0.21 (PHP is 4.3.10). My goal is to list certain data from all open issues, across projects, as well as listing some issue details contained in custom fields. I don't have a problem getting the data for the first eight columns of my query. My problem lies with listing the next seven columns, which come from custom fields. I thought of using a PHP script but this is a one-time deal and my knowledge of PHP is as poor as that of MySQL. As Eventum users may know, headings for custom fields are extracted from cells in the eventum_custom_field_option table. I thought that IF statements would do the job, but I get a syntax error (1064). Obviously, they are not the way to go. Nevertheless, here's the complete query so you can get an idea of where I want to go: SELECT DISTINCT eventum_issue.iss_id AS Issue ID, eventum_project_priority.pri_title AS Priority, eventum_user.usr_full_name AS Assigned, eventum_project.prj_title AS Project Name, eventum_project_category.prc_title AS Category, eventum_status.sta_title AS Status, eventum_issue.iss_updated_date AS Last Update Date, eventum_issue.iss_summary AS Summary, IF eventum_custom_field.fld_id =3D 47 THEN eventum_custom_field_option.cfo_value AS Product Name ELSE IF eventum_custom_field.fld_id =3D 59 THEN eventum_custom_field_option.cfo_value AS Project Name ELSE IF eventum_custom_field.fld_id =3D 4 THEN eventum_custom_field_option.cfo_value AS Change Requester ELSE IF eventum_custom_field.fld_id =3D 1 THEN eventum_custom_field_option.cfo_value AS Change Type ELSE IF eventum_custom_field.fld_id =3D 2 THEN eventum_custom_field_option.cfo_value AS Requested Completion Date ELSE IF eventum_custom_field.fld_id =3D 46 THEN eventum_custom_field_option.cfo_value AS BSA Lead ELSE IF eventum_custom_field.fld_id =3D 37 THEN eventum_custom_field_option.cfo_value AS Developer FROM eventum_issue, eventum_custom_field, eventum_custom_field_option INNER JOIN eventum_project_priority, eventum_issue_user, eventum_user, eventum_project, eventum_project_category, eventum_status eventum_issue_custom_field WHERE eventum_issue.iss_pri_id =3D eventum_project_priority.pri_id AND eventum_issue.iss_id =3D eventum_issue_user.isu_iss_id AND eventum_issue_user.isu_usr_id =3D eventum_user.usr_id AND eventum_issue.iss_prj_id =3D eventum_project.prj_id AND eventum_issue.iss_prc_id =3D eventum_project_category.prc_id AND eventum_issue.iss_sta_id =3D eventum_status.sta_id AND (eventum_issue.iss_closed_date IS NULL OR (eventum_issue.iss_closed_date IS NOT NULL AND (eventum_issue.iss_sta_id !=3D 5 OR eventum_issue.iss_sta_id !=3D 6 OR eventum_issue.iss_sta_id !=3D 9))) ORDER BY eventum_issue.iss_id Please, I don't need comments telling me that I'm missing the semicolon or that comments such as doesn't work are worthless. I know there's something inherently wrong with this query (obviously . . . since it doesn't give me the results that I am looking for). Does anyone have a constructive comment as to how to do this? Thank you very much in advance, Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To
Re: max_connections in 3.23.49
Hello. For such an old version of MySQL use --set-variable syntax. See: http://dev.mysql.com/doc/refman/4.1/en/program-variables.html I'm not sure if it supports dynamic system variables through the SET syntax, according to the manual they have appeared in 4.0.3. In the future, instead of 'set @variable' syntax for system variables, use 'set @@global.variable' or 'set global variable=...'. See: http://dev.mysql.com/doc/refman/4.1/en/system-variables.html I strongly recommend you to upgrade. Douglas B. Jones wrote: How do you up max_connections on 3.23.29 mysql? I have tried in the my.cnf file, command line and in mysql (set @max_connections=200). Thanks for any help! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
restoring datewise data
hello, i have a database in mysql i want to restore the backup server every 15 minutes if i take a backup in sql its huge and my transfer cost of the data will be very high can anyone suggest how to restore the data in small size regards prao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: restoring datewise data
prathima rao wrote: hello, i have a database in mysql i want to restore the backup server every 15 minutes if i take a backup in sql its huge and my transfer cost of the data will be very high can anyone suggest how to restore the data in small size regards prao time to think about replication.. thank you -- anu bhaskar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: max_connections in 3.23.49
Thanks! I will try the -set again to see if it works. I seem to remember that was the first one I tried and had no luck. I might have had the wrong syntax. I will try again tomorrow morning and see how this works. I am pushing the users to upgrade to 4.1 for now and then 5.x when the dust settles. Thanks! -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 06, 2005 7:03 AM To: mysql@lists.mysql.com Subject: Re: max_connections in 3.23.49 Hello. For such an old version of MySQL use --set-variable syntax. See: http://dev.mysql.com/doc/refman/4.1/en/program-variables.html I'm not sure if it supports dynamic system variables through the SET syntax, according to the manual they have appeared in 4.0.3. In the future, instead of 'set @variable' syntax for system variables, use 'set @@global.variable' or 'set global variable=...'. See: http://dev.mysql.com/doc/refman/4.1/en/system-variables.html I strongly recommend you to upgrade. Douglas B. Jones wrote: How do you up max_connections on 3.23.29 mysql? I have tried in the my.cnf file, command line and in mysql (set @max_connections=200). Thanks for any help! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rewriting subquery for old MySQL - SOLVED - correction!
Alex Gemmell wrote: Thanks Micheal - yeah, I managed to figure out the CREATE_TMP_TABLE privilage problem myself. I should have used those die on errors too - will do next time! To recap here's the working final solution (after MySQL User has CREATE_TMP_TABLES and DROP privilages): [PHP] $query1 = CREATE TEMPORARY TABLE tmpSubquery (SELECT * FROM tblactivities WHERE Assignment_ID='.$row['Assignment_ID'].' ORDER BY Date DESC LIMIT 10);; mysql_query($query1); $query2 = SELECT * FROM tmpSubquery ORDER BY Date ASC;; $result_activities = mysql_query($query2); $query3 = DROP TEMPORARY TABLE tmpSubquery;; mysql_query($query3); [/PHP] Thanks all! Correction - the Query1 above failed due to a syntax error. I removed the brackets I (stupidly) added and also the trailing semi-colon (which some people recommeded I do). After doing that Query1 worked and is now an acceptable way of getting my orginal subquery to work on an old version 4.0 MySQL. [PHP] $query1 = CREATE TEMPORARY TABLE tmpSubquery SELECT * FROM tblactivities WHERE Assignment_ID='.$row['Assignment_ID'].' ORDER BY Date DESC LIMIT 10; mysql_query($query1); $query2 = SELECT * FROM tmpSubquery ORDER BY Date ASC;; $result_activities = mysql_query($query2); $query3 = DROP TEMPORARY TABLE tmpSubquery;; mysql_query($query3); [/PHP] Solved for real! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie Question: listing open issues regardless of project
Gleb, What I need to have is a total of 16 columns: issue ID, priority, assigned (to), project name, category, status, last update date, summary, product name, project name (not the same as before), change requester, change type, requested completion date, lead, developer, impacted dept. I have no problems generating the first eight or the last columns. The seven in between are the issue as they are not columns per se. They are cells in different tables. I need to extract the contents of certain cells, based on certain IDs, to be used as the column headings in the output to my query. Then, I need to look into other tables to fill those columns. This is what I need help with: how do I generate those columns? I guess they could be generated separately and then I could put both of my outputs together. Unfortunately, time is running out. TIA, Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 06, 2005 6:52 AM To: mysql@lists.mysql.com Subject: Re: Newbie Question: listing open issues regardless of project Hello. Please, I don't need comments telling me that I'm missing the semicolon or that comments such as doesn't work are worthless. I know there's something inherently wrong with this query (obviously . . . since it doesn't give me the results that I am looking for). Does anyone have a constructive comment as to how to do this? Please, could you answer what are you going to get in the last columns. You've said that you had problems with the last seven columns, but from the logic of your query I see that you just want a column, which changes its value depending on the value in other field. Am I correct? Please provide a sample output (what you want to see in the results) in case I'm wrong. Now I'm not talking about syntax, but rather about sense of the query. Kraer, Joseph wrote: I am sending this message to both Eventum and MySQL support lists. I am trying to write a select statement in the MySQL Query Browser (v. 1.1.10) on my Eventum (v. 1.4) database; MySQL version is 4.0.21 (PHP is 4.3.10). My goal is to list certain data from all open issues, across projects, as well as listing some issue details contained in custom fields. I don't have a problem getting the data for the first eight columns of my query. My problem lies with listing the next seven columns, which come from custom fields. I thought of using a PHP script but this is a one-time deal and my knowledge of PHP is as poor as that of MySQL. As Eventum users may know, headings for custom fields are extracted from cells in the eventum_custom_field_option table. I thought that IF statements would do the job, but I get a syntax error (1064). Obviously, they are not the way to go. Nevertheless, here's the complete query so you can get an idea of where I want to go: SELECT DISTINCT eventum_issue.iss_id AS Issue ID, eventum_project_priority.pri_title AS Priority, eventum_user.usr_full_name AS Assigned, eventum_project.prj_title AS Project Name, eventum_project_category.prc_title AS Category, eventum_status.sta_title AS Status, eventum_issue.iss_updated_date AS Last Update Date, eventum_issue.iss_summary AS Summary, IF eventum_custom_field.fld_id =3D 47 THEN eventum_custom_field_option.cfo_value AS Product Name ELSE IF eventum_custom_field.fld_id =3D 59 THEN eventum_custom_field_option.cfo_value AS Project Name ELSE IF eventum_custom_field.fld_id =3D 4 THEN eventum_custom_field_option.cfo_value AS Change Requester ELSE IF eventum_custom_field.fld_id =3D 1 THEN eventum_custom_field_option.cfo_value AS Change Type ELSE IF eventum_custom_field.fld_id =3D 2 THEN eventum_custom_field_option.cfo_value AS Requested Completion Date ELSE IF eventum_custom_field.fld_id =3D 46 THEN eventum_custom_field_option.cfo_value AS BSA Lead ELSE IF eventum_custom_field.fld_id =3D 37 THEN eventum_custom_field_option.cfo_value AS Developer FROM eventum_issue, eventum_custom_field, eventum_custom_field_option INNER JOIN eventum_project_priority, eventum_issue_user, eventum_user, eventum_project, eventum_project_category, eventum_status eventum_issue_custom_field WHERE eventum_issue.iss_pri_id =3D eventum_project_priority.pri_id AND eventum_issue.iss_id =3D eventum_issue_user.isu_iss_id AND eventum_issue_user.isu_usr_id =3D eventum_user.usr_id AND eventum_issue.iss_prj_id =3D eventum_project.prj_id AND eventum_issue.iss_prc_id =3D eventum_project_category.prc_id AND eventum_issue.iss_sta_id =3D eventum_status.sta_id AND (eventum_issue.iss_closed_date IS NULL OR (eventum_issue.iss_closed_date IS NOT NULL AND
RE: Newbie Question: listing open issues regardless of project
I see it! Tito, you have to pivot those fields out of your custom fields/custom field values table(s). One of the best places to do that is into a temporary table so that you can join your pivoted rows into the rest of the report. I am assuming that the `eventum_custom_field` table has a field something like `issue_id` that associates a field with an issue. I am also assuming that there is only one custom field of any one type per issue. CREATE TEMPORARY TABLE tmpCustFields SELECT cf.issue_id , MAX(if(fld_id=47,cfo.cfo_value,NULL)) as ProductName , MAX(if(fld_id=59,cfo.cfo_value,NULL)) as ProjectName , MAX(if(fld_id=4,cfo.cfo_value,NULL)) as ChangeRequester , MAX(if(fld_id=1,cfo.cfo_value,NULL)) as ChangeType , MAX(if(fld_id=2,cfo.cfo_value,NULL)) as ReqCompletionDate , MAX(if(fld_id=46,cfo.cfo_value,NULL)) as BSALead , MAX(if(fld_id=37,cfo.cfo_value,NULL)) as Developer FROM eventum_custom_fields cf LEFT JOIN eventum_custom_field_options cfo ON cf.cf_id = cfo.cfo_custom_field_id GROUP BY cf.issue_id; You will need to modify the column names in the ON clause to match the actual names of the columns that you need to relate an option to a field or an issue (whichever works). This query builds your middle columns into a table of their own. I hope that once you get them this far, JOINing this temp table to the rest of the tables you need to build your query will look pretty straight-forward. Look at the data to see what we did SELECT * from tmpCustFields limit 100; Hope that helps! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Kraer, Joseph [EMAIL PROTECTED] wrote on 12/06/2005 10:39:01 AM: Gleb, What I need to have is a total of 16 columns: issue ID, priority, assigned (to), project name, category, status, last update date, summary, product name, project name (not the same as before), change requester, change type, requested completion date, lead, developer, impacted dept. I have no problems generating the first eight or the last columns. The seven in between are the issue as they are not columns per se. They are cells in different tables. I need to extract the contents of certain cells, based on certain IDs, to be used as the column headings in the output to my query. Then, I need to look into other tables to fill those columns. This is what I need help with: how do I generate those columns? I guess they could be generated separately and then I could put both of my outputs together. Unfortunately, time is running out. TIA, Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 06, 2005 6:52 AM To: mysql@lists.mysql.com Subject: Re: Newbie Question: listing open issues regardless of project Hello. Please, I don't need comments telling me that I'm missing the semicolon or that comments such as doesn't work are worthless. I know there's something inherently wrong with this query (obviously . . . since it doesn't give me the results that I am looking for). Does anyone have a constructive comment as to how to do this? Please, could you answer what are you going to get in the last columns. You've said that you had problems with the last seven columns, but from the logic of your query I see that you just want a column, which changes its value depending on the value in other field. Am I correct? Please provide a sample output (what you want to see in the results) in case I'm wrong. Now I'm not talking about syntax, but rather about sense of the query. Kraer, Joseph wrote: I am sending this message to both Eventum and MySQL support lists. I am trying to write a select statement in the MySQL Query Browser (v. 1.1.10) on my Eventum (v. 1.4) database; MySQL version is 4.0.21 (PHP is 4.3.10). My goal is to list certain data from all open issues, across projects, as well as listing some issue details contained in custom fields. I don't have a problem getting the data for the first eight columns of my query. My problem lies with listing the next seven columns, which come from custom fields. I thought of using a PHP script but this is a one-time deal and my knowledge of PHP is as poor as that of MySQL. As Eventum users may know, headings for custom fields are extracted from cells in the eventum_custom_field_option table. I thought that IF statements would do the job, but I get a syntax error (1064). Obviously, they are not the way to go. Nevertheless, here's the complete query so you can get an idea of where I want to go: SELECT DISTINCT eventum_issue.iss_id AS Issue ID, eventum_project_priority.pri_title AS Priority, eventum_user.usr_full_name AS Assigned, eventum_project.prj_title AS Project Name, eventum_project_category.prc_title AS Category, eventum_status.sta_title AS Status,
Subquery strangeness when used in FROM clause
Hi, I'm having a problem with subqueries in MySQL 4.1.14 running on Fedore core 3. mysql create table day_6_12_2005 (f1 int(1), f2 char(4)); Query OK, 0 rows affected (0.04 sec) mysql insert into day_6_12_2005 values(1,'test'); Query OK, 1 row affected (0.00 sec) mysql select * from (select date_format(now(),'day_%e_%c_%Y')) as t1; +---+ | date_format(now(),'day_%e_%c_%Y') | +---+ | day_6_12_2005 | +---+ 1 row in set (0.04 sec) mysql select f1,f2 from (select date_format(now(),'day_%e_%c_%Y')) as t1; ERROR 1054 (42S22): Unknown column 'f1' in 'field list' Any one know what's going on? According to the docs, this should work fine... Any pointers or ideas will be much appreciated... Cheers, Andrew SQL, query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: restoring datewise data
Hello. will be very high can anyone suggest how to restore the data in small size Have you thought about using binary logs or compressing the sql file? prathima rao wrote: hello, i have a database in mysql i want to restore the backup server every 15 minutes if i take a backup in sql its huge and my transfer cost of the data will be very high can anyone suggest how to restore the data in small size regards prao -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question: listing open issues regardless of project
Hello. So it is clear now, that you should have 16 columns and to build dynamically the column headings. The usual way to do such things in SQL is prepared statements. See: http://dev.mysql.com/doc/refman/5.0/en/sqlps.html And good example of how to use them (though it is an article about stored routines) here: http://dev.mysql.com/tech-resources/articles/mysql-storedproc.html You will need to use user variables as well: http://dev.mysql.com/doc/refman/5.0/en/example-user-variables.html CONCAT function: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html Control flow functions: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html Correlated subqueries: http://dev.mysql.com/doc/refman/5.0/en/correlated-subqueries.html Hope that'll be enough to make your query work. Kraer, Joseph wrote: Gleb, What I need to have is a total of 16 columns: issue ID, priority, assigned (to), project name, category, status, last update date, summary, product name, project name (not the same as before), change requester, change type, requested completion date, lead, developer, impacted dept. I have no problems generating the first eight or the last columns. The seven in between are the issue as they are not columns per se. They are cells in different tables. I need to extract the contents of certain cells, based on certain IDs, to be used as the column headings in the output to my query. Then, I need to look into other tables to fill those columns. This is what I need help with: how do I generate those columns? I guess they could be generated separately and then I could put both of my outputs together. Unfortunately, time is running out. TIA, Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 06, 2005 6:52 AM To: mysql@lists.mysql.com Subject: Re: Newbie Question: listing open issues regardless of project -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subquery strangeness when used in FROM clause
Andrew Braithwaite [EMAIL PROTECTED] wrote on 12/06/2005 11:34:50 AM: Hi, I'm having a problem with subqueries in MySQL 4.1.14 running on Fedore core 3. mysql create table day_6_12_2005 (f1 int(1), f2 char(4)); Query OK, 0 rows affected (0.04 sec) mysql insert into day_6_12_2005 values(1,'test'); Query OK, 1 row affected (0.00 sec) mysql select * from (select date_format(now(),'day_%e_%c_%Y')) as t1; +---+ | date_format(now(),'day_%e_%c_%Y') | +---+ | day_6_12_2005 | +---+ 1 row in set (0.04 sec) mysql select f1,f2 from (select date_format(now(),'day_%e_%c_%Y')) as t1; ERROR 1054 (42S22): Unknown column 'f1' in 'field list' Any one know what's going on? According to the docs, this should work fine... Any pointers or ideas will be much appreciated... Cheers, Andrew SQL, query MySQL does not have fully-functional, dynamic SQL, yet. What you did is called an anonymous view or a derived table. There currently is no way (outside of a procedure or function and using prepared statements) to build a string and attempt to execute it as a SQL statement with just MySQL. You can do this in a programming language and submit the string just as you would any other query but you generally cannot build that string within MySQL and execute it from within MySQL (yet). How what you did normally works like this. Yes, there are other ways to solve this problem but this demonstrates the principle behind the anonymous view. __setup__ Imagine you have a table called `stat_totals` that has the columns `test_id`, `total_items`, and `run_count`. You have been asked to produce a count of how many tests average at least 10 items per run. __example solution__ It's trivial to find the average number of items per test with a query like this: SELECT test_id ,if(`run_count`0,`total_items`/`run_count`,NULL) as avg_per_run FROM `stat_totals`; That query produces two columns of output: `test_id` and `avg_per_run`. Wouldn't it be nice to be able to directly query the output of the previous statement? You can if you use it as an anonymous view. This will return all rows form the previous output where avg_per_run is greater than 10: SELECT test_id ,avg_per_run FROM ( SELECT test_id ,if(`run_count`0,`total_items`/`run_count`,NULL) as avg_per_run FROM `stat_totals`; ) as avg_table WHERE avg_table.avg_per_run 10; When you did this same query style within your original query, you only had one column in your one-row, dynamically declared table called `date_format(now(),'day_%e_%c_%Y')` and it had the single value of 'day_6_12_2005' (see your previous query) which is why the query engine could not find the column `f1` in that table (actually your query results) and threw the error. Anonymous views became possible with the UNION changes added to 4.0. __end example__ Make better sense? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Friendster with MySQL
*shrug* Livejournal uses MySQL just fine: http://www.danga.com/words/2004_mysqlcon/mysql-slides.pdf I dare say livejournal has more writes and is more real-time than friendster. MySQL Cluster isn't a bad solution, just remember that you need machines with LOTS of memory -- 'cause that's where all the data is stored. -Sheeri On 12/6/05, JM [EMAIL PROTECTED] wrote: Hi ALL, We are planning to create a social software similar to friendster and Im working on the requirements... I saw a site: http://philip.greenspun.com/teaching/6171/2003-fall/friendster and for some reasons its telling me not to use MySQL, the initial infrastructure is a scale out hmmm starting with 4 data nodes cluster.. tia, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie Question: listing open issues regardless of project
Shawn, Glen, and everyone else, Thank you very much! I do believe that I have enough material to work on it now! I appreciate your help very much! Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 06, 2005 11:03 AM To: mysql@lists.mysql.com Subject: Re: Newbie Question: listing open issues regardless of project Hello. So it is clear now, that you should have 16 columns and to build dynamically the column headings. The usual way to do such things in SQL is prepared statements. See: http://dev.mysql.com/doc/refman/5.0/en/sqlps.html And good example of how to use them (though it is an article about stored routines) here: http://dev.mysql.com/tech-resources/articles/mysql-storedproc.html You will need to use user variables as well: http://dev.mysql.com/doc/refman/5.0/en/example-user-variables.html CONCAT function: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html Control flow functions: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html Correlated subqueries: http://dev.mysql.com/doc/refman/5.0/en/correlated-subqueries.html Hope that'll be enough to make your query work. Kraer, Joseph wrote: Gleb, What I need to have is a total of 16 columns: issue ID, priority, assigned (to), project name, category, status, last update date, summary, product name, project name (not the same as before), change requester, change type, requested completion date, lead, developer, impacted dept. I have no problems generating the first eight or the last columns. The seven in between are the issue as they are not columns per se. They are cells in different tables. I need to extract the contents of certain cells, based on certain IDs, to be used as the column headings in the output to my query. Then, I need to look into other tables to fill those columns. This is what I need help with: how do I generate those columns? I guess they could be generated separately and then I could put both of my outputs together. Unfortunately, time is running out. TIA, Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 06, 2005 6:52 AM To: mysql@lists.mysql.com Subject: Re: Newbie Question: listing open issues regardless of project -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CocoaMySQL v0.7b2 Connection Help
[EMAIL PROTECTED] wrote: Responses intermixed. See below... untz [EMAIL PROTECTED] wrote on 12/02/2005 10:43:41 PM: snip 3. After logging into mysql, I created the following database: mysql create database music_development to 'untz'@'localhost' identified by 'paintball'; MySQL databases do not have the concept of ownership. They are all global. You only need to say: (@mysql CLI prompt) CREATE DATABASE music_development; and you can test to see if your create statement worked by running (@CLI) SHOW DATABASES; If your database is in that list, the command worked. You appear to have mixed a CREATE DATABASE with a GRANT statement. To create a MySQL user you need to use a GRANT statement. If you want to create a MySQL user account with all normal DB privileges (but still cannot grant permission to others), this is how I would create the account and grant access the new database: (@CLI) GRANT usage ON *.* to 'untz'@'localhost' IDENTIFIED BY 'xx'; (@CLI) GRANT ALL ON music_development to 'untz'@'localhost'; snip The db name is optional with GRANT, but the table name is not, so this statement would grant rights to the *table* named music_development in the currently selected db. (Even though I know why this is so, I've always found it a little counter-intuitive, which has led me to make the same mistake a few times.) What Shawn meant to say was GRANT ALL ON music_development.* to 'untz'@'localhost'; which grants rights on all tables in the music_development to [EMAIL PROTECTED] Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character set issue ( maybe )
Octavian Rasnita wrote: From: Daniel Kasak [EMAIL PROTECTED] OK then. Lets re-word the question ... Has anyone been able to successfully enter text of a non-standard character set ( Latin 1, UTF8 ) into Query Browser? How about upload via a Perl script? Yes you can insert those chars in MySQL using a perl program, but you cannot do it using Windows' copy and paste from a web page. I think this is because of Windows clipboard that doesn't copy the text correctly. I'm running a Linux desktop :) -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
question regarding compiling for libmysqlclient.so
hi there. 1st time posting to this list (and although i know linux, libraries linking is above my level slightly as yet) so apologies if this is a case of rtfm but i can't find a clear definition on this subject. I want to run Postfix with Mysql support for aliasing etc. I installed postfix with non-mysql support and it worked fine. installed Mysql 5 (latest version), recompiled postfix with mysql support and postfix didnt work. I was told there was a fair chance that the reason for this was that the mysql binary tarball i downloaded didnt contain libmysqlclient.so. Thus I want to install what would be the mysql-devel package for Centos onto my linux centos system without using the actual package. I want to compile mysql on my system with the libmysqlclient.so files. I'm told it's the dynamic package and the compile strings in the docs all seem to be all-static. can anyone help an idiot out and provide me the string for compilation of mysql 5 please to include the development libraries please? thanks very much and once more, apologies if it is a case of deeper rtfm. Daryn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character set issue ( maybe )
Gleb Paharenko wrote: Hello. I've been able to insert Russian characters (utf8) through the QueryBrowser under Linux (FC4). Please, could you provide CREATE statement for you table CREATE TABLE `WebNewsPostings` ( `PRID` mediumint(8) unsigned NOT NULL auto_increment, `MyStamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `DateDay` tinyint(3) unsigned NOT NULL default '0', `DateMonth` tinyint(3) unsigned NOT NULL default '0', `DateYear` mediumint(8) unsigned NOT NULL default '0', `CopyTitle` varchar(255) character set latin1 NOT NULL default '', `CopyMain` mediumtext character set latin1 NOT NULL, `Image` varchar(50) character set latin1 default '', `Language` tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (`PRID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 I don't know what those individual 'character set latin1' bits are. I haven't been messing with per-field character sets - I've just been using mysql-administrator to change the character set for the entire table. But anyway, the source *IS* latin1, so maybe this shouldn't be a problem anyway? and output of: show variables like '%char%'; mysql show variables like '%char%'; +--++ | Variable_name| Value | +--++ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results| latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--++ 7 rows in set (0.00 sec) As far as I know, QueryBrowser invokes SET NAMES 'utf8' by default. And the only way to force it to use your connection character sets is to run a transaction. Are characters that you're pasting into QueryBrowser displayed correctly in the query tab? Yes. They display correctly - both in query browser AND in my Perl Gtk2 app. What's more, when I inspect the binary query log, the same sequence of reverse-coloured characters is used to represent the characters that are giving problems. Which ever method I use to get the data in ( query browser / in-house Perl Gtk2 app ), the data *looks* perfect when I paste it in, but when I return to look at it later it's corrupted. Thanks for the reply, by the way :) Dan -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANN: Database Workbench 2.8.0 released!
Ladies, gentlemen, Upscene Productions is proud to announce the next version of the popular database development tool: Database Workbench 2.8.0 has been released today! Download a trial at: http://www.upscene.com What's new?: http://www.upscene.com/products/dbw/whatsnew.htm Full list of features and fixes: http://www.upscene.com/news/20051206a.htm Database Workbench supports: - Borland InterBase ( v4.x - v7.x ) - Firebird ( v1.x ) - MS SQL Server/MSDE ( v6.5, 7, 2000, 2005, MSDE 1 2, SQL Express ) - MySQL 4, 4.1, 5.0 - Oracle Database ( 8i, 9i, 10g ) If you experience any problems with this new version, don't hestitate and either go to the website and send a support email or email directly to [EMAIL PROTECTED] New - Microsoft SQL 2005 support - MySQL 5 support - Two-way Visual Query Builder - Increased Oracle support - New SQL Insight - Create INSERT script from ODBC datasource Enhancements - Code/SQL Editor enhancements - More complte Schema Compare/Migration - Automatic image-type recognition in BLOB Editor - many user interface improvements Thank you for your support, Martijn Tonies Database Workbench - the database developer tool for professionals Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Friendster with MySQL
At 11:23 PM 12/5/2005, you wrote: Hi ALL, We are planning to create a social software similar to friendster and Im working on the requirements... I saw a site: http://philip.greenspun.com/teaching/6171/2003-fall/friendster and for some reasons its telling me not to use MySQL, the initial infrastructure is a scale out hmmm starting with 4 data nodes cluster.. tia, Tia, Where did they say they don't recommend MySQL? They eventually recommended using MySQL with Master-Master clusters. After all they're getting up to 800 hits.second and have 60+ servers. As someone else said, clusters may be the way to go. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Friendster with MySQL
I'm sorry, I' just gotta interject. 800 hits/second is not that big of a deal. I've got a client running an affiliate tracking system across several high traffic domains. To properly scale this we had to do some testing. Using a Dell 2800 w/2GB of RAM and a single HD (not the greatest setup) I was able to get 1,3000 inserts per second. It sustained this level for 10 minutes before I got tired of watching it. The final application takes inserts from 20 web servers in a cluster as well as serving the affiliates their front end which does data mining (mainly reads) from yet another server. MySQL clusters are an interesting idea but IMHO unless you've got a serious MySQL guru on staff and on-call 24x7, I'd steer clear of them until you've exhausted every other option. (We've looked at them but we're just too scared to actually put one in production...) Have you calculated the aprox # of reads and writes you will be doing? (Estimate how much traffic you wan, computer your reads and writes based on that and them triple that in case you suddenly become very popular.) Then start playing around with MySQL. There are lots of knobs to twiddle. Also, make sure you select the right OS. As far as I can tell (and granted we only have 15 MySQL servers running) Linux is the best platform for MySQL. (Has to do with the threading.) One of the biggest things you can do is separate MySQL from everything else. I know, it sounds stupid but I'm still amazed at how many people still try to run everything on one box! MySQL loves lots of RAM to run in. Let it have as much as it wants. Hook your servers together with a back-end, private network. Keeps your mysql traffic off your public interface and your back interface will be less busy so your database traffic won't have to fight for bandwidth. (Also, unless you really HAVE to, don't let your MySQL daemon listen to your public interface. One more small security measure) Now if you are just trolling for someone to get pissed about this article's back-handed slap at MySQL, nobody's taking the bait. If it's good enough for finance.yahoo.com, I'm guessing your application just isn't gonna pose a challenge. All of this was said with a smile on my face...not trying to pick a fight or come across rude. Just trying to pass on what I've learned. | | Cal Evans | http://www.calevans.com | mos wrote: At 11:23 PM 12/5/2005, you wrote: Hi ALL, We are planning to create a social software similar to friendster and Im working on the requirements... I saw a site: http://philip.greenspun.com/teaching/6171/2003-fall/friendster and for some reasons its telling me not to use MySQL, the initial infrastructure is a scale out hmmm starting with 4 data nodes cluster.. tia, Tia, Where did they say they don't recommend MySQL? They eventually recommended using MySQL with Master-Master clusters. After all they're getting up to 800 hits.second and have 60+ servers. As someone else said, clusters may be the way to go. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
is this normal?
system freebsd 4.10 mysql is binary: mysql-standard-5.0.15-freebsd4.7-i386 after mysql installation and root password set up i can type: /usr/local/mysql/bin/mysql -u no_matter_what_user_here and i get right in. even if i type -p. i do get the password prompt but doesn't matter what i type there - even nothing i get straight in... is this normal?? thanks... -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
