MySQL Application Builder
Hi, I've been building my PHP/MySQL applications by hand for years. Now I am wondering after seeing a Flex demo if some sort of instant or super easy mysql application builder existings. I want something for rapid development with a graphical user interface. Maybe drag and drop table query creation etc. Something to dramatically reduce the amount of time it takes to build a simple web application that reads from MySQL tables. Any recommendations? Thank you for your help... Keith
Re: Two MySql servers, but very different performances for a SELECT JOIN
The 'STRAIGHT_FORWARD' + 'FORCE INDEX' worked, thanks a lot to all :) Tristan -- Tristan Marly 06.16.84.57.43 http://www.linkedin.com/in/tristanmarly -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query problem
Hi all, Iam having the one table name called AccessDetails and data inside that tables is following, DateTime UserName SiteName ScanType Status Virus_Category | 2008-04-16 | 13:05:31 | 172.16.1.22 | - | www.veer.com |C | A| unclassified | | 2008-04-16 | 13:05:31 | 172.16.1.52 | - | blogactiv.eu |C | O | unclassified | | 2008-04-16 | 13:05:32 | 172.16.1.22 | - | www.veer.com |V | A| Internet | | 2008-04-16 | 13:05:32 | 172.16.1.52 | - | www.verylowsodium.com |C | D| unclassified | | 2008-04-16 | 13:05:32 | 172.16.1.52 | - | blogactiv.eu |V | A | unclassified | In that , I need to calculate the number of total sites , number of total Accessed Sites,number of total Denied Sites and number of total Overriden Sites based on the particular Virus_Category,UserName,Date How can form the query to achieve that?? I have used the following query but the total site is not correctly displayed.. select count(a.UserName),sum(b.totalsites),sum(a.Allow),sum(a.Denied),sum(a.Over),sum(b.totalconn) from (select a.UserName,sum(a.Allow) as Allow,sum(a.Denied) as Denied,sum(a.Over) as Over from (select UserName,case Status when 'A' then count(distinct SiteName) else 0 END as Allow ,case Status when 'D' then count(distinct SiteName) else 0 END as Denied,case Status when 'O' then count(distinct SiteName) else 0 END as Over from AccessDetails where Virus_category = 'unclassified ' and Date='2008-04-16' and Date='2008-04-16' and ScanType='C' group by UserName, Status) a group by a.UserName) a left join (select UserName,count(distinct SiteName)as totalsites, count(Time)as totalconn from AccessDetails where Virus_category = 'unclassified ' and Date='2008-04-16' and Date='2008-04-16' and ScanType='C' and Virus_category '-' and UserName '-' group by UserName)b on a.UserName=b.UserName where b.totalsites is not null Thanks In Advance ,
Re: MySQL Application Builder
I have been doing a lot of development in Flex over the last six months. While it is a terrific presentation layer, connecting to a data manager (we use MySQL) is time consuming (we use Java based openAMF) and a little tempermental. We use Flex 2 Builder to help with the presentation layer and it is decent. However, in order to do the things that make a presentation smooth and easy to understand, you have to understand how Flex works which is as difficult and obtuse as Java Swing. You can do simple things quickly but to do nice things is quite time consuming. HTH. Carl - Original Message - From: Keith Spiller [EMAIL PROTECTED] To: [MySQL] mysql@lists.mysql.com Sent: Wednesday, April 16, 2008 2:05 AM Subject: MySQL Application Builder Hi, I've been building my PHP/MySQL applications by hand for years. Now I am wondering after seeing a Flex demo if some sort of instant or super easy mysql application builder existings. I want something for rapid development with a graphical user interface. Maybe drag and drop table query creation etc. Something to dramatically reduce the amount of time it takes to build a simple web application that reads from MySQL tables. Any recommendations? Thank you for your help... Keith No virus found in this incoming message. Checked by AVG. Version: 7.5.524 / Virus Database: 269.23.0/1379 - Release Date: 4/15/2008 6:10 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Application Builder
From what I understand, Flex Builder 3 has a code generation wizard that you can point to a database. Haven't used it myself though. Sanat. On Wed, 16 Apr 2008, Carl wrote: I have been doing a lot of development in Flex over the last six months. While it is a terrific presentation layer, connecting to a data manager (we use MySQL) is time consuming (we use Java based openAMF) and a little tempermental. We use Flex 2 Builder to help with the presentation layer and it is decent. However, in order to do the things that make a presentation smooth and easy to understand, you have to understand how Flex works which is as difficult and obtuse as Java Swing. You can do simple things quickly but to do nice things is quite time consuming. HTH. Carl - Original Message - From: Keith Spiller [EMAIL PROTECTED] To: [MySQL] mysql@lists.mysql.com Sent: Wednesday, April 16, 2008 2:05 AM Subject: MySQL Application Builder Hi, I've been building my PHP/MySQL applications by hand for years. Now I am wondering after seeing a Flex demo if some sort of instant or super easy mysql application builder existings. I want something for rapid development with a graphical user interface. Maybe drag and drop table query creation etc. Something to dramatically reduce the amount of time it takes to build a simple web application that reads from MySQL tables. Any recommendations? Thank you for your help... Keith No virus found in this incoming message. Checked by AVG. Version: 7.5.524 / Virus Database: 269.23.0/1379 - Release Date: 4/15/2008 6:10 PM -- 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 does too much work to find rows where primary key does not match
Hi Patrick, all ! Patrick J. McEvoy wrote: I have two MyISAM tables; each uses 'phone' as a primary key. Finding rows where the primary keys match is efficient: mysql explain select bar.phone from foo,bar where foo.phone=bar.phone; [[...]} Ok, let us take some simple example. Say tables foo and bar both have three rows each, with phone values 1, 2, and 3. Then your matching lines will be: foo.phone bar.phone 1 1 2 2 3 3 Column foo.phone is shown for explanation only, your select would not return it. Each individual value in bar.phone will be returned as often as there is an identical value in foo.phone. I trust that is close to what you expect. My sample data here do not show what will happen if values in foo.phone are not distinct - figure yourself. Finding rows in one table that do not match a row in the other table is wildly inefficient: mysql explain select bar.phone from foo,bar where foo.phone!=bar.phone; [[...]] Your resulting data from this select would be: foo.phone bar.phone 1 2 1 3 2 1 2 3 3 1 3 2 Again, foo.phone is shown for explanation only. (This is the same for 'NOT', '!=', or ''.) Correct. The amount of work should be identical in both cases: grab a row, look up by primary key in the other table, proceed. No, it isn't: A select ... from foo, bar where CONDITION effectively creates the cartesian product of both tables and then removes all lines (combinations) which do not meet the condition. Of course, the system uses better strategies if possible, evaluating indexes etc, but the resulting data will be the same. Assuming tables foo and bar each have a column num with the values 1 to 100, a condition ... where foo.num = bar.num will lead to a result with 100 rows. But ... where foo.num != bar.num will lead to a table of 9,900 rows: For each of the 100 values in foo.num, there will be 99 entries in bar.num that satisfy the inequality condition. My real goal is to delete rows in the smaller table if there is no match in the larger table: delete from bar using foo,bar where not bar.phone=foo.phone; See above - wrong approach. What you need is a subquery or an outer join, as proposed in the other replies. [[...]] HTH, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
best way to add a new column to a table with 60+ million records
hi, is there any other best way add a new column to an existing table having 60+ million records. alter is taking more than 1.5 hours.. what are the best practices around this. quick help will be appreciated. -- cheers, - a
[SOLVED] RE: Strange performance problem
It's possibly a DNS problem (reverse DNS exactly). You know, I'm feeling a bit stupid here... That was indeed the problem, as the new server hadn't been moved on DNS yet. I put the IP address into the windows hosts file on the DB server, and the problem cleared up immediately. Thanks! -Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JOIN / NOT JOIN differences
hi, i have this table *TABLE friends: *id_usr INT id_friend INT and i have a query in which i return friends from a given user and data related to each of them stored in some other tables. So i do this: SELECT F.id_friend, M.status, P.firstname, P.lastname, IF( UNIX_TIMESTAMP( ) - UNIX_TIMESTAMP( C.lastConnection ) 240, C.status, 'disconnected' ) AS 'connectionstatus' FROM friends F, user_connections C, user_personaldata P, user_statusmessages M WHERE F.id_usr = 1 AND C.id_usr = F.id_friend AND P.id_usr = F.id_friend AND M.id_usr = F.id_friend ORDER BY connectionstatus ASC but i have seen that if there is no rows matching a friend of the user in the other tables, mysql ignore index and scan all the table. this is not happening with joins. SELECT F.id_friend, M.status, P.firstname, P.lastname, IF( UNIX_TIMESTAMP( ) - UNIX_TIMESTAMP( C.lastConnection ) 240, C.status, 'disconnected' ) AS 'connectionstatus' FROM friends F LEFT JOIN user_connections C ON C.id_usr = F.id_friend LEFT JOIN user_personaldata P ON P.id_usr = F.id_friend LEFT JOIN user_statusmessages M ON M.id_usr = F.id_friend WHERE F.id_usr = 1 ORDER BY connectionstatus ASC why is the first query scanning all the table if a row is missing? , there is any performance different between those queries ? i've read that using index between larges tables is a bad choice, so i have doubts
mytop
Hi How can i monitor multiple MySQL Database using mytop or are there any other tools to monitor it. Thanks and Regards Kaushal
CHARACTER SET
When you create a table, you can specify a character set for a column. How can you tell what character set was used when the column was created? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com http://www.giiexpress.com www.giiexpress.com www.etudes-marche.com
Re: CHARACTER SET
When you create a table, you can specify a character set for a column. How can you tell what character set was used when the column was created? SHOW CREATE TABLE. If no character set is shown for the column, it uses the table default character set. Example: mysql create table t (c1 char(5) character set utf8, c2 char(5)); Query OK, 0 rows affected (0.16 sec) mysql show create table t\G *** 1. row *** Table: t Create Table: CREATE TABLE `t` ( `c1` char(5) CHARACTER SET utf8 DEFAULT NULL, `c2` char(5) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) The definition for c1 shows that utf8 is used The definition for c2 shows nothing, so the table character set (latin1) is used. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.1.24-rc has been released (part 1 of 2)
Dear MySQL users, We are proud to present to you the MySQL Server 5.1.24-rc release, a new release candidate version of the popular open source database. Bear in mind that this is still a candidate release, and as with any other pre-production release, caution should be taken when installing on production level systems or systems with critical data. For production level systems using 5.0, we would like to direct your attention to the product description of MySQL Enterprise at: http://mysql.com/products/enterprise/ The MySQL 5.1.24-rc release is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. Please also note that some of our mirrors are currently experiencing problems that may result in serving corrupted files. We are working with the mirror maintainers to resolve this. We welcome and appreciate your feedback, bug reports, bug fixes, patches etc.: http://forge.mysql.com/wiki/Contributing The description of the changes from version 5.1.23-rc to this 5.1.24-rc is some 1,800 lines long, that is about 96 kB. As some mail systems are bound to truncate long mail at 64 kB, I split the announcement into two parts - this is part 1 only. The following section lists the (first part of the) changes from version to version in the MySQL source code since the latest released version of MySQL 5.1, the MySQL 5.1.23-rc release. It can also be viewed online at http://dev.mysql.com/doc/refman/5.1/en/news-5-1-24.html Sincerely, Kent Boortz The MySQL build team at Sun Microsystems == Functionality added or changed: * Please note that the Federated engine is not built into the MySQL 5.1.24 RC release binaries, but is scheduled to return in the next release, which will be MySQL 5.1.25. The reasons for Federated's omission in 5.1.24 RC includes various quality and timing issues that unfortunately could not be avoided, and we apologize for any inconvenience this has caused. * Windows Installer: Important Change: The data directory now defaults to the Windows Common App Data Folder (on Windows XP, this is ...\All Users\Application Data; on Vista, it is ProgramData). (Bug#34593: http://bugs.mysql.com/34593) * Cluster API: Important Change: Because NDB_LE_MemoryUsage.page_size_kb shows memory page sizes in bytes rather than kilobytes, it has been renamed to page_size_bytes. The name page_size_kb is now deprecated and thus subject to removal in a future release, although it currently remains supported for reasons of backwards compatibility. See The Ndb_logevent_type Type (http://dev.mysql.com/doc/ndbapi/en/ndb-logevent-type.html), for more information about NDB_LE_MemoryUsage. (Bug#30271: http://bugs.mysql.com/30271) * Replication: Introduced the slave_exec_mode system variable to control whether idempotent or strict mode is used for replication conflict resolution. Idempotent mode suppresses duplicate-key, no-key-found, and some other errors, and is needed for circular replication, multi-master replication, and some other complex replication setups when using MySQL Cluster. Strict mode is the default. (Bug#31609: http://bugs.mysql.com/31609) * Replication: When running the server with --binlog-format=MIXED or --binlog-format=STATEMENT, a query that referred to a system variable used the slave's value when replayed on the slave. This meant that, if the value of a system variable was inserted into a table, the slave differed from the master. Now, statements that refer to a system variable are marked as unsafe, which means that: + When the server is using --binlog-format=MIXED, the row-based format is used automatically to replicate these statements. + When the server is using --binlog-format=STATEMENT, these statements produce a warning. (Bug#31168: http://bugs.mysql.com/31168) See also Bug#34732: http://bugs.mysql.com/34732 * The ndbd and ndb_mgmd manpages have been reclassified from volume 1 to volume 8. (Bug#34642: http://bugs.mysql.com/34642) * For binary .tar.gz packages, mysqld and other binaries now are compiled with debugging symbols included to enable easier use with a debugger. (Bug#33252: http://bugs.mysql.com/33252) * Formerly, when the MySQL server crashed, the generated stack dump was numeric and required external tools to properly resolve the names of functions. This is not very helpful to users having a limited knowledge of debugging techniques. In addition, the generated stack trace contained only the names of functions and was formatted differently for each platform due to
MySQL 5.1.24-rc has been released (part 2 of 2)
Bugs fixed (continued from part 1): * Creating a foreign key on an InnoDB table that was created with an explicit AUTO_INCREMENT value caused that value to be reset to 1. (Bug#34920: http://bugs.mysql.com/34920) * mysqldump failed to return an error code when using the --master-data option without binary logging being enabled on the server. (Bug#34909: http://bugs.mysql.com/34909) * Under some circumstances, the value of mysql_insert_id() following a SELECT ... INSERT statement could return an incorrect value. This could happen when the last SELECT ... INSERT did not involve an AUTO_INCREMENT column, but the value of mysql_insert_id() was changed by some previous statements. (Bug#34889: http://bugs.mysql.com/34889) * Table and database names were mixed up in some places of the subquery transformation procedure. This could affect debugging trace output and further extensions of that procedure. (Bug#34830: http://bugs.mysql.com/34830) * If fsync() returned ENOLCK, InnoDB could treat this as fatal and cause abnormal server termination. InnoDB now retries the operation. (Bug#34823: http://bugs.mysql.com/34823) * A malformed URL used for a FEDERATED table's CONNECTION option value in a CREATE TABLE statement was not handled correctly and could crash the server. (Bug#34788: http://bugs.mysql.com/34788) * Using NAME_CONST() with a negative number and an aggregate function caused MySQL to crash. This could also have a negative impact on replication. (Bug#34749: http://bugs.mysql.com/34749) * A memory-handling error associated with use of GROUP_CONCAT() in subqueries could result in a server crash. (Bug#34747: http://bugs.mysql.com/34747) * For an indexed integer column col_name and a value N that is one greater than the maximum value allowed for the data type of col_name, conditions of the form WHERE col_name N failed to return rows where the value of col_name is N - 1. (Bug#34731: http://bugs.mysql.com/34731) * Executing a TRUNCATE statement on a table having both a foreign key reference and a DELETE trigger crashed the server. (Bug#34643: http://bugs.mysql.com/34643) * Some subqueries using an expression that included an aggregate function could fail or in some cases lead to a crash of the server. (Bug#34620: http://bugs.mysql.com/34620) * Creating a view inside a stored procedure could lead to a crash of the MySQL Server. (Bug#34587: http://bugs.mysql.com/34587) * CAST(AVG(arg) AS DECIMAL) produced incorrect results for non-DECIMAL arguments. (Bug#34512: http://bugs.mysql.com/34512) * Executing an ALTER VIEW statement on a table crashed the server. (Bug#34337: http://bugs.mysql.com/34337) * InnoDB could crash if overflow occurred for an AUTO_INCREMENT column. (Bug#34335: http://bugs.mysql.com/34335) * For InnoDB, exporting and importing a table could corrupt TINYBLOB columns, and a subsequent ALTER TABLE could corrupt TINYTEXT columns as well. (Bug#34300: http://bugs.mysql.com/34300) * DEFAULT 0 was not allowed for the YEAR data type. (Bug#34274: http://bugs.mysql.com/34274) * Under some conditions, a SET GLOBAL innodb_commit_concurrency or SET GLOBAL innodb_autoextend_increment statement could fail. (Bug#34223: http://bugs.mysql.com/34223) * Use of stored functions in the WHERE clause for SHOW OPEN TABLES caused a server crash. (Bug#34166: http://bugs.mysql.com/34166) * Passing anything other than a integer to a LIMIT clause in a prepared statement would fail. (This limitation was introduced to avoid replication problems; for example, replicating the statement with a string argument would cause a parse failure in the slave). Now, arguments to the LIMIT clause are converted to integer values, and these converted values are used when logging the statement. (Bug#33851: http://bugs.mysql.com/33851) * An internal buffer in mysql was too short. Overextending it could cause stack problems or segmentation violations on some architectures. (This is not a problem that could be exploited to run arbitrary code.) (Bug#33841: http://bugs.mysql.com/33841) * A query using WHERE (column1='string1' AND column2=constant1) OR (column1='string2' AND column2=constant2), where col1 used a binary collation and string1 matched string2 except for case, failed to match any records even when matches were found by a query using the equivalent clause WHERE column2=constant1 OR column2=constant2. (Bug#33833: http://bugs.mysql.com/33833) * Large unsigned integers were improperly handled for prepared statements, resulting in truncation or conversion to negative numbers. (Bug#33798: http://bugs.mysql.com/33798) * Reuse of prepared statements could cause a memory leak in the embedded server. (Bug#33796:
RE: CHARACTER SET
Bingo! You get a cookie. Thanks, I knew there had to be a way. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 16, 2008 10:25 AM To: Jerry Schwartz; 'Mysql' Subject: Re: CHARACTER SET When you create a table, you can specify a character set for a column. How can you tell what character set was used when the column was created? SHOW CREATE TABLE. If no character set is shown for the column, it uses the table default character set. Example: mysql create table t (c1 char(5) character set utf8, c2 char(5)); Query OK, 0 rows affected (0.16 sec) mysql show create table t\G *** 1. row *** Table: t Create Table: CREATE TABLE `t` ( `c1` char(5) CHARACTER SET utf8 DEFAULT NULL, `c2` char(5) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) The definition for c1 shows that utf8 is used The definition for c2 shows nothing, so the table character set (latin1) is used. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: best way to add a new column to a table with 60+ million records
given that my table is in myisam, there are some hacky way of doing this (referred to this online) like creating table without keys, insert data from .myd, copy of .frm, .myi files for same table created with keys, and then doing a repair table on new table.. but i was wondering if there is an easy way to do it. On Wed, Apr 16, 2008 at 5:30 PM, Arun Kumar PG [EMAIL PROTECTED] wrote: hi, is there any other best way add a new column to an existing table having 60+ million records. alter is taking more than 1.5 hours.. what are the best practices around this. quick help will be appreciated. -- cheers, - a -- cheers, - a
Updating/Adding comments
Hello, I would like to add comments to existing tables in my database without having to re-create the tables themselves. I know I can add a comment using the alter table table change column col_name col_name col_def comment 'column comment'. My question is what performance impact will this alter statement have on my database. Will it re-create or mess up any of my indexes? Is there an easier way to add comments to a column? Thanks! Shaun McQuaker -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CHARACTER SET
On Wed, Apr 16, 2008 at 7:24 AM, Paul DuBois [EMAIL PROTECTED] wrote: When you create a table, you can specify a character set for a column. How can you tell what character set was used when the column was created? SHOW CREATE TABLE. If no character set is shown for the column, it uses the table default character set. Example: mysql create table t (c1 char(5) character set utf8, c2 char(5)); Query OK, 0 rows affected (0.16 sec) mysql show create table t\G *** 1. row *** Table: t Create Table: CREATE TABLE `t` ( `c1` char(5) CHARACTER SET utf8 DEFAULT NULL, `c2` char(5) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) The definition for c1 shows that utf8 is used The definition for c2 shows nothing, so the table character set (latin1) is used. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com Is there any reason that the information_schema would not be the preferred method of finding this information? mysql select table_collation from tables WHERE `table_name` = 'mytable' AND table_schema ='mydatabase'\G -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two MySql servers, but very different performances for a SELECT JOIN
On Wed, Apr 16, 2008 at 12:41 AM, [EMAIL PROTECTED] wrote: The 'STRAIGHT_FORWARD' + 'FORCE INDEX' worked, thanks a lot to all :) http://www.google.com/search?q=STRAIGHT_FORWARD+mysql yields buckus. Where is this documented? -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CHARACTER SET
The production system is running 4.1.22. Does it supports schemas? In any event, I'm not familiar with using them. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Rob Wultsch [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 16, 2008 11:50 AM To: Paul DuBois Cc: Jerry Schwartz; Mysql Subject: Re: CHARACTER SET On Wed, Apr 16, 2008 at 7:24 AM, Paul DuBois [EMAIL PROTECTED] wrote: When you create a table, you can specify a character set for a column. How can you tell what character set was used when the column was created? SHOW CREATE TABLE. If no character set is shown for the column, it uses the table default character set. Example: mysql create table t (c1 char(5) character set utf8, c2 char(5)); Query OK, 0 rows affected (0.16 sec) mysql show create table t\G *** 1. row *** Table: t Create Table: CREATE TABLE `t` ( `c1` char(5) CHARACTER SET utf8 DEFAULT NULL, `c2` char(5) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) The definition for c1 shows that utf8 is used The definition for c2 shows nothing, so the table character set (latin1) is used. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com Is there any reason that the information_schema would not be the preferred method of finding this information? mysql select table_collation from tables WHERE `table_name` = 'mytable' AND table_schema ='mydatabase'\G -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two MySql servers, but very different performances for a SELECT JOIN
Selon Rob Wultsch [EMAIL PROTECTED]: http://www.google.com/search?q=STRAIGHT_FORWARD+mysql yields buckus. Where is this documented? oops, I meant 'straight_join' :/ Tristan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
On Wed, Apr 16, 2008 at 4:35 AM, sivasakthi [EMAIL PROTECTED] wrote: Hi all, Iam having the one table name called AccessDetails and data inside that tables is following, [snip=schema] In that , I need to calculate the number of total sites , number of total Accessed Sites,number of total Denied Sites and number of total Overriden Sites based on the particular Virus_Category,UserName,Date How can form the query to achieve that?? I have used the following query but the total site is not correctly displayed.. You may want to look into the ROLLUP modifier. Here's the manual entry: http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
\x96 in column value?
Why is the character \x96 not allowed as a value in an INSERT query? I've SET
NAMES utf8, the table collation is utf8_general_ci, the default character set
for the table is utf8. I get this error message from my application:
ERROR: INSERT INTO prod
(prod_title,prod_published,prod_pub_prod_id,prod_content_info,prod_samp_doc,prod_toc_doc,prod_type,prod_ready,prod_discont,prod_info_type,prod_id,prod_num,pub_id,prod_added,prod_updated,prod_export,prod_changed)
VALUES ('Disposable Paper Products ? Uruguay','2007-12-01','DPP2007UY','Pages:
44','xa20\\d\\publishers\\eo\\fullreports\\DPP07_UY.pdf','xa20\\d\\publishers\\eo\\content\\DPP07_UY-LOC.doc','S',1,0,'E','Y8F1kqasoQVE90F',65642,'BG3g33kv5CDkim7',NOW(),NOW(),1,1);
Incorrect string value: '\x96 Urug...' for column 'prod_title' at row 1
That bizarre character in the middle of 'Disposable Paper Products ? Uruguay'
is, as you can see, \x96. MySQL certainly doesn't seem to like it. The data is
being read from a file. Curiously, if I copy the line directly from the input
file it looks like this:
Disposable Paper Products - Uruguay
You may not be able to see it, but that is actually an n-dash (\x96).
Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: grant user privileges
On Tue, Apr 15, 2008 at 9:03 AM, Hiep Nguyen [EMAIL PROTECTED] wrote: hi all, i have an existing database (internal) with a user named 'admin', everything works fine as far as privileges concern. i just created a new database (test) and want to grant admin's privileges on test as same as internal. how do i do this??? i tried (as root): grant all on test.* to 'admin'@'localhost'; grant all on test.* to 'admin'@'10.0.0.%'; GRANT ALL PRIVILEGES ON test.* TO 'admin'@'localhost' IDENTIFIED BY 'passwordString'; GRANT ALL PRIVILEGES ON test.* TO 'admin'@'%' IDENTIFIED BY 'passwordString'; FLUSH PRIVILEGES; The first query grants all privileges to admin when accessing the host locally. The second query allows you to connect from ANY host. The third query flushes the old privileges data and ensures that the updated mysql.users information is used. Be sure to replace passwordString with your password. If you only want access to be on the Class C private network subnet for 10.0.0.x (RFC 1918) as well as localhost, then adjust query #2 to: GRANT ALL PRIVILEGES ON test.* TO 'admin'@'10.0.0.%' IDENTIFIED BY 'passwordString'; and then FLUSH PRIVILEGES; again. -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.1.24-rc has been released (part 1 of 2)
On Wed, 16 Apr 2008 16:43:01 +0200 Kent Boortz [EMAIL PROTECTED] wrote: * Disk Data: Important Change: It is no longer possible on 32-bit systems to issue statements appearing to create Disk Data log files or data files greater than 4 GB in size. (Trying to create log files or data files larger than 4 GB on 32-bit systems led to unrecoverable data node failures; such statements now fail with NDB error 1515.) (Bug#29186: http://bugs.mysql.com/29186) According to the patches I would assume that this only affects people using the NDB cluster engine but from your notes I would think that even my old MyISAM tables are affected. I do have *.MYD files as big as 15GB on a 32bit system and guess that I'm not the only one. Could you clarify that on the next release notes? bye, -christian- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RV: Performance problem
Hi all, im new on the performance tuning of this database (MySQL 5.0.45, rpm-based installation), and i have one performance problem on our new installation: - The radius servers (that are written on perl) we have are writing the auth and acct log to one mysql database. The conn we have is an TCPIP conn. - We have two databases, one for auth data and another for acct data. - We have one table for each day on each database, on which we insert the auth and acct data. We also have three indexes on each table, that occupy almost 300M per day. - The volume of traffic is nearly 10 million rows per day. - The partition of the database is mounted on a LVM partition of a RAID1 disk. We are experiencing problems about the performance of the database, in the way that we are seeing that the radius clients are seeing the radius servers gone away for the acct service. The server of the database is a Dell Poweredge 1855 with 6 GB of RAM and RHEL4. We have modified the variables of the database with: SET GLOBAL thread_cache_size=8; SET GLOBAL table_cache=256; set GLOBAL max_connections=200; set GLOBAL key_buffer_size=1610416128; set GLOBAL read_buffer_size=524288; set GLOBAL read_rnd_buffer_size=1048576; SET GLOBAL delayed_insert_limit=400; SET GLOBAL delayed_queue_size=12000; SET GLOBAL net_buffer_length=32768; The queries that we are doing are: INSERT DELAYED IGNORE () VALUES (); Originally, the server has 2GB of RAM, but seeing this problems, we have installed another 4 GB of RAM. From the statistics of vmstat we see that we are suffering som IO bottleneck (i think): procs ---memory-- ---swap-- -io --system-- cpu r b swpd free buff cache si sobibo incs us sy id wa 0 4 0 4280956 40144 139245600 014 1853 1180 1 0 48 50 0 3 0 4279932 40152 139348800 010 1882 1258 2 0 42 56 0 3 0 4279908 40172 139450800 0 2052 1861 1202 2 1 45 52 0 4 0 4276452 40192 139552800 0 9179 1850 1164 2 1 66 31 1 3 0 4274748 40200 139630000 0 7 1957 1337 2 1 64 34 0 4 0 4272956 40212 139732800 024 1926 1283 2 1 41 56 0 3 0 4271484 40224 139861600 026 1906 1250 2 1 32 66 0 3 0 4270204 40228 139965200 0 9 1855 1154 2 0 24 74 0 3 0 4268924 40236 140016400 010 1852 1144 2 0 24 74 1 4 0 4267516 40248 140145200 013 2063 1480 2 1 27 71 0 3 0 4264476 40280 140272000 0 11134 1965 1363 2 1 49 48 0 4 0 4262772 40300 140374000 013 1971 1382 2 0 60 37 0 4 0 4261372 40316 140476400 015 1875 1213 2 1 46 52 0 3 0 4260028 40328 140553200 014 1831 1152 2 0 48 50 The wa column shows a quite large number, so we think that it is an IO bottleneck. The question is: ¿ has anybody have seesomething similar? ¿has anybody an idea about how to resolve this problem? Thanks. Antes de imprimir este e-mail piense bien si es necesario hacerlo. * Este mensaje es privado y CONFIDENCIAL y se dirige exclusivamente a su destinatario. Si usted ha recibido este mensaje por error, no debe revelar, copiar, distribuir o usarlo en ningún sentido. Le rogamos lo comunique al remitente y borre dicho mensaje y cualquier documento adjunto que pudiera contener. El correo electrónico via Internet no permite asegurar la confidencialidad de los mensajes que se transmiten ni su integridad o correcta recepción. JAZZTEL no asume responsabilidad por estas circunstancias. Si el destinatario de este mensaje no consintiera la utilización del correo electrónico via Internet y la grabación de los mensajes, rogamos lo ponga en nuestro conocimiento de forma inmediata.Cualquier opinión expresada en este mensaje pertenece únicamente al autor remitente, y no representa necesariamente la opinión de JAZZTEL, a no ser que expresamente se diga y el remitente esté autorizado para hacerlo. * This message is private and CONFIDENTIAL and it is intended exclusively for its addressee. If you receive this message in error, you should not disclose, copy, distribute this e-mail or use it in any other way. Please inform the sender and delete the message and attachments from your system.Internet e-mail neither guarantees the confidentiality nor the integrity or proper receipt of the messages sent. JAZZTEL does not assume any liability for those circumstances. If the addressee of this message does not consent to the use of Internet e-mail and message recording, please notify us immediately.Any views or opinions contained in this message are solely those of the author, and do not necessarily represent those of JAZZTEL, unless otherwise specifically stated and the sender is authorised to do so. *
Re: grant user privileges
On Wed, 16 Apr 2008, Daniel Brown wrote:
On Tue, Apr 15, 2008 at 9:03 AM, Hiep Nguyen [EMAIL PROTECTED] wrote:
hi all, i have an existing database (internal) with a user named 'admin',
everything works fine as far as privileges concern.
i just created a new database (test) and want to grant admin's privileges
on test as same as internal.
how do i do this???
i tried (as root):
grant all on test.* to 'admin'@'localhost';
grant all on test.* to 'admin'@'10.0.0.%';
GRANT ALL PRIVILEGES ON test.* TO 'admin'@'localhost' IDENTIFIED
BY 'passwordString';
GRANT ALL PRIVILEGES ON test.* TO 'admin'@'%' IDENTIFIED BY
'passwordString';
FLUSH PRIVILEGES;
The first query grants all privileges to admin when accessing the
host locally. The second query allows you to connect from ANY host.
The third query flushes the old privileges data and ensures that the
updated mysql.users information is used. Be sure to replace
passwordString with your password.
If you only want access to be on the Class C private network
subnet for 10.0.0.x (RFC 1918) as well as localhost, then adjust query
#2 to:
GRANT ALL PRIVILEGES ON test.* TO 'admin'@'10.0.0.%' IDENTIFIED BY
'passwordString';
and then FLUSH PRIVILEGES; again.
i understand all of these, but let me re-state my question:
assume user admin has a few privileges (which i don't know, but can find
out) on internal database.
let say that i create a new database and want to grant admin's privileges
on test so that admin's privileges on internal = admin's privileges on
test
instead of find out what are privileges of admin on internal (select,
update, insert,..) and then
grant select,update, insert,... on on test.* to 'admin'@'localhost'
identified by 'password';
is there any command that can set so that admin's privileges on internal =
admin's privileges on test???
what i'm trying to avoid is manually adjust admin's privileges on test if
admin's privileges on internal changed.
if it's not still clear, then stupid menever mind.
t. hiep
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CHARACTER SET
At 8:49 AM -0700 4/16/08, Rob Wultsch wrote: On Wed, Apr 16, 2008 at 7:24 AM, Paul DuBois [EMAIL PROTECTED] wrote: When you create a table, you can specify a character set for a column. How can you tell what character set was used when the column was created? SHOW CREATE TABLE. If no character set is shown for the column, it uses the table default character set. Example: mysql create table t (c1 char(5) character set utf8, c2 char(5)); Query OK, 0 rows affected (0.16 sec) mysql show create table t\G *** 1. row *** Table: t Create Table: CREATE TABLE `t` ( `c1` char(5) CHARACTER SET utf8 DEFAULT NULL, `c2` char(5) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) The definition for c1 shows that utf8 is used The definition for c2 shows nothing, so the table character set (latin1) is used. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com Is there any reason that the information_schema would not be the preferred method of finding this information? mysql select table_collation from tables WHERE `table_name` = 'mytable' AND table_schema ='mydatabase'\G You can do that, too, unless your version of MySQL is older than 5.0. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: grant user privileges
On Wed, Apr 16, 2008 at 1:18 PM, Hiep Nguyen [EMAIL PROTECTED] wrote: is there any command that can set so that admin's privileges on internal = admin's privileges on test??? what i'm trying to avoid is manually adjust admin's privileges on test if admin's privileges on internal changed. if it's not still clear, then stupid menever mind. If I'm understanding correctly, you want user 'admin' to be able to access multiple databases, using one password and one permission set. If that's the case, then yes, it can be done but as far as I know, only by selecting ALL databases. Such as: GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'passwordString'; FLUSH PRIVILEGES; Then, when updating privileges for 'admin', just include *.* instead of a particular database.table limit. Keep in mind, though, that 'admin' will then have access to EVERY database on the server. I'm not sure that there's a way to comma-delimit (or something of the type) a select few database.table configurations. A quick search of the web didn't show any different either. -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: \x96 in column value?
I'm running afoul of the UTF8 character set somehow:
mysql select convert(char(0x96) using utf8);
+--+
| convert(char(0x96) using utf8) |
+--+
| NULL |
+--+
1 row in set, 1 warning (0.00 sec)
mysql show warnings;
+---+--+-+
| Level | Code | Message |
+---+--+-+
| Error | 1300 | Invalid utf8 character string: '96' |
+---+--+-+
1 row in set (0.00 sec)
On top of my other problems, I've discovered that pasting the UTF8 character
represented by 0x96 into the MySQL CLI (Windows) somehow converts the
character to 0x2D (a normal dash); so a lot of my testing has been wasted.
Pasting it into a Windows-based editor preserves the character as 0x96.
Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
-Original Message-
From: Jerry Schwartz [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 16, 2008 12:49 PM
To: 'Mysql'
Subject: \x96 in column value?
Why is the character \x96 not allowed as a value in an INSERT query?
I've SET
NAMES utf8, the table collation is utf8_general_ci, the default
character set
for the table is utf8. I get this error message from my application:
ERROR: INSERT INTO prod
(prod_title,prod_published,prod_pub_prod_id,prod_content_info,prod_samp_
doc,prod_toc_doc,prod_type,prod_ready,prod_discont,prod_info_type,prod_i
d,prod_num,pub_id,prod_added,prod_updated,prod_export,prod_changed)
VALUES ('Disposable Paper Products ? Uruguay','2007-12-
01','DPP2007UY','Pages:
44','xa20\\d\\publishers\\eo\\fullreports\\DPP07_UY.pdf','xa20\\
d\\publishers\\eo\\content\\DPP07_UY-
LOC.doc','S',1,0,'E','Y8F1kqasoQVE90F',65642,'BG3g33kv5CDkim7',NOW(),NOW
(),1,1);
Incorrect string value: '\x96 Urug...' for column 'prod_title' at row 1
That bizarre character in the middle of 'Disposable Paper Products ?
Uruguay'
is, as you can see, \x96. MySQL certainly doesn't seem to like it. The
data is
being read from a file. Curiously, if I copy the line directly from the
input
file it looks like this:
Disposable Paper Products - Uruguay
You may not be able to see it, but that is actually an n-dash (\x96).
Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: \x96 in column value?
On Wed, 16 Apr 2008, Jerry Schwartz [EMAIL PROTECTED] wrote: I'm running afoul of the UTF8 character set somehow: mysql select convert(char(0x96) using utf8); +--+ | convert(char(0x96) using utf8) | +--+ | NULL | +--+ 1 row in set, 1 warning (0.00 sec) mysql show warnings; +---+--+-+ | Level | Code | Message | +---+--+-+ | Error | 1300 | Invalid utf8 character string: '96' | +---+--+-+ 1 row in set (0.00 sec) On top of my other problems, I've discovered that pasting the UTF8 character represented by 0x96 into the MySQL CLI (Windows) somehow converts the character to 0x2D (a normal dash); so a lot of my testing has been wasted. Pasting it into a Windows-based editor preserves the character as 0x96. In an earlier note, he wrote You may not be able to see it, but that is actually an n-dash (\x96). Actually, \x96 is not an en-dash. http://www.unicode.org/charts/PDF/U0080.pdf says that it's START OF GUARDED AREA. x96 is in the middle of a block of control characters from the unnamed control character at \x80 through APPLICATION PROGRAM COMMAND at \x9F (or arguably NO-BREAK SPACE at \xa0). Microsoft, in some of their Windows code pages, assigned meanings to those values that differ from the Unicode and ISO-8859-1 standards (quelle suprise), assigning many of them uses as printable characters. I think it's the Windows 1250 code page, at http://www.microsoft.com/globaldev/reference/sbcs/1250.mspx. As that page and http://www.microsoft.com/typography/developers/fdsspec/punc2.htm note, the Unicode standard value for an en-dash is U+2013 (which appears to be in hex). As to whether this affects the problem I don't know. Since x96 is a valid character, whether Microsoft or real Unicode, I would not expect it to be a problem per se. I just wanted to point out what it might not mean. -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.1.24-rc has been released (part 1 of 2)
Christian Hammers [EMAIL PROTECTED] writes: On Wed, 16 Apr 2008 16:43:01 +0200 Kent Boortz [EMAIL PROTECTED] wrote: * Disk Data: Important Change: It is no longer possible on 32-bit systems to issue statements appearing to create Disk Data log files or data files greater than 4 GB in size. (Trying to create log files or data files larger than 4 GB on 32-bit systems led to unrecoverable data node failures; such statements now fail with NDB error 1515.) (Bug#29186: http://bugs.mysql.com/29186) According to the patches I would assume that this only affects people using the NDB cluster engine but from your notes I would think that even my old MyISAM tables are affected. I do have *.MYD files as big as 15GB on a 32bit system and guess that I'm not the only one. Could you clarify that on the next release notes? Yes, this is only about NDB, but you are right this could have been made more clear. I have forwarded your comment, thanks, kent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.51a and SHOW ENGINES
On Fri, Apr 11, 2008 at 08:54:26AM +0200, Martijn Tonies wrote: Hello Jim, On Thu, Apr 10, 2008 at 09:32:43AM +0200, Martijn Tonies wrote: It seems that SHOW ENGINES fails on MySQL 5.0.51a (community edition checked). Instead of returning the full data, the first two columns are cut off at 3 characters, while the comment column is cut off at 26 characters. sounds like you are using the wrong value for the length of a utf8 field, where the number of characters is being divided by the max character length. (10 / 3 = 3, 80 / 3 = 26) or it could be the server returning the wrong length. use mysql --column-type-info to see what it is returning. That doesn't work with 5.0 as far as I can tell. sorry, i think --column-type-info is new in 5.1. you can get the same effect using -T with 5.0, i believe. jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: \x96 in column value?
-Original Message- From: Tim McDaniel [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 16, 2008 2:32 PM Cc: 'Mysql' Subject: RE: \x96 in column value? On Wed, 16 Apr 2008, Jerry Schwartz [EMAIL PROTECTED] wrote: I'm running afoul of the UTF8 character set somehow: mysql select convert(char(0x96) using utf8); +--+ | convert(char(0x96) using utf8) | +--+ | NULL | +--+ 1 row in set, 1 warning (0.00 sec) mysql show warnings; +---+--+-+ | Level | Code | Message | +---+--+-+ | Error | 1300 | Invalid utf8 character string: '96' | +---+--+-+ 1 row in set (0.00 sec) On top of my other problems, I've discovered that pasting the UTF8 character represented by 0x96 into the MySQL CLI (Windows) somehow converts the character to 0x2D (a normal dash); so a lot of my testing has been wasted. Pasting it into a Windows-based editor preserves the character as 0x96. In an earlier note, he wrote You may not be able to see it, but that is actually an n-dash (\x96). Actually, \x96 is not an en-dash. http://www.unicode.org/charts/PDF/U0080.pdf says that it's START OF GUARDED AREA. x96 is in the middle of a block of control characters from the unnamed control character at \x80 through APPLICATION PROGRAM COMMAND at \x9F (or arguably NO-BREAK SPACE at \xa0). [JS] Right you are. This whole business gives me an extreme headache. When working in PHP, I assume my Windows-generated input is cp1252 and I convert that to UTF-8. Aside from that, we always work in UTF-8 (database and web) because I have to handle Chinese. (I have no idea if I'm doing that right, I can't read the results. ;) In Microsoft's code page 1252, 0x96 is indeed an n-dash. I think this might be my clue. Although our web pages specify UTF-8, I found an article in MSDN that seems to say that IE interprets UTF-8 pages using a code page in the cp1200 family, whatever that means. That must be why our data looks correct going end-to-end. I also found http://effbot.org/zone/unicode-gremlins.htm, which gives a bit of Python code to translate some cp1252 bits to their Unicode equivalents. It also give you a nice list of the problem characters. There are also examples in the PHP documentation of the iconv() function, but there is also a comment that 0x96 breaks iconv. I need to chew on this some more. PHP doesn't really handle multi-byte characters until 6.x. Microsoft, in some of their Windows code pages, assigned meanings to those values that differ from the Unicode and ISO-8859-1 standards (quelle suprise), assigning many of them uses as printable characters. I think it's the Windows 1250 code page, at http://www.microsoft.com/globaldev/reference/sbcs/1250.mspx. As that page and http://www.microsoft.com/typography/developers/fdsspec/punc2.htm note, the Unicode standard value for an en-dash is U+2013 (which appears to be in hex). As to whether this affects the problem I don't know. Since x96 is a valid character, whether Microsoft or real Unicode, I would not expect it to be a problem per se. I just wanted to point out what it might not mean. -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.51a and SHOW ENGINES
At 2:57 PM -0400 4/16/08, Jim Winstead wrote: On Fri, Apr 11, 2008 at 08:54:26AM +0200, Martijn Tonies wrote: Hello Jim, On Thu, Apr 10, 2008 at 09:32:43AM +0200, Martijn Tonies wrote: It seems that SHOW ENGINES fails on MySQL 5.0.51a (community edition checked). Instead of returning the full data, the first two columns are cut off at 3 characters, while the comment column is cut off at 26 characters. sounds like you are using the wrong value for the length of a utf8 field, where the number of characters is being divided by the max character length. (10 / 3 = 3, 80 / 3 = 26) or it could be the server returning the wrong length. use mysql --column-type-info to see what it is returning. That doesn't work with 5.0 as far as I can tell. sorry, i think --column-type-info is new in 5.1. you can get the same effect using -T with 5.0, i believe. RIght: --column-type-info, -m Display result set metadata. This option was added in MySQL 5.1.14. (Before that, use --debug-info.) The -m short option was added in MySQL 5.1.21. --debug-info, -T Before MySQL 5.1.14, this option prints debugging information and memory and CPU usage statistics when the program exits, and also causes display of result set metadata during execution. As of MySQL 5.1.14, use --column-type-info to display result set metadata. http://dev.mysql.com/doc/refman/5.1/en/mysql-command-options.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query OK in localhost, error on ISP server
The following query run flawlessly in localhost but produces error on ISP server: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct sf_threads.views) as views, ((count(distinct sf_messages.' at line 6 The actual behaviour here: http://eduforums.us or http://wconti.com/schoolsforums/index.cfm I am using the exact same database both in local and server. Running MySQL 5 in localhost and supposedly versions 4 and 5 sopported by the ISP server. Thanks for helping SELECT #variables.tableprefix#conferences.id, #variables.tableprefix#conferences.name, count(distinct #variables.tableprefix#forums.id)-1 as schools, count(distinct #variables.tableprefix#threads.id) as topics, count(distinct #variables.tableprefix#messages.id) as msgs, sum(distinct #variables.tableprefix#threads.views) as views, ((count(distinct #variables.tableprefix#messages.id) *2) +sum(distinct #variables.tableprefix#threads.views) ) as activity, 0 as hBarLength FROM ((#variables.tableprefix#conferences left JOIN #variables.tableprefix#forums ON #variables.tableprefix#conferences.id = #variables.tableprefix#forums.conferenceidfk) left JOIN #variables.tableprefix#threads ON #variables.tableprefix#forums.id = #variables.tableprefix#threads.forumidfk and #variables.tableprefix#threads.author 'admin') left JOIN #variables.tableprefix#messages ON #variables.tableprefix#threads.id = #variables.tableprefix#messages.threadidfk and #variables.tableprefix#messages.author 'admin' GROUP BY #variables.tableprefix#conferences.id -- View this message in context: http://www.nabble.com/Query-OK--in-localhost%2C-error-on-ISP-server-tp16738784p16738784.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: grant user privileges
Sebastian Mendel schrieb: Hiep Nguyen schrieb: hi all, i have an existing database (internal) with a user named 'admin', everything works fine as far as privileges concern. i just created a new database (test) and want to grant admin's privileges on test as same as internal. how do i do this??? i tried (as root): grant all on test.* to 'admin'@'localhost'; grant all on test.* to 'admin'@'10.0.0.%'; but it seems not right. grant access to admin from 'foreign' hosts? 10.0.0.0.% is not the host admin connects from but _TO_! this must be the host of the MySQL server, the host that is specified when connecting _TO_ the database. sorry, bulls***, i was totally wrong, of course ... -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
