Re: how to select total votes for each comment?
Patrick Aljord schrieb: Hey all, I have comments(id,content) and votes(comment_id,vote). vote is a tinyint. I would like to select total votes for each comment, I tried: select content, sum(v.votes) from comments c left join votes v on c.id=v.comment_id but it only returns first result obviously, any idea how I could do this? did you tried in your mysql console? please add the output here and add GROUP BY - this is required by SQL standard SELECT comments.content, SUM(votes.votes) FROM comments LEFT JOIN votes ON comments.id = votes.comment_id GROUP BY comments.id -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing and exporting from MySQL, escape slash problem
Dave M G schrieb: PHP List, MySQL List In my PHP environment, I have Magic Quotes turned off, and I use the mysql_real_escape_string() function clean strings of SQL syntax before inserting them into my database. So the data stored in my database does not have escape characters in it. Particularly, double and single quotes don't have slashes in front of them. This seems to work fine so long as I'm reading data into and out of the database from within my scripts. However, when I backup and import databases - I use the phpMyAdmin interface - they have escape slashes in front of every double and single quote characters. I'm not sure if it's on the export or import where they get added in. what version of phpMyAdmin? I've looked through the phpMyAdmin online documentation, and I can't see any option to control the presence of escape slashes. It seems to me that if it adds them in when exporting, it should take them out when importing. Or vice versa, but in either case be consistent. I just want my database to be exactly as it is before any export or import options. I'm a little muddled as to where I'm making the mistake. Can anyone advice on the best practice for preserving my database as is when backing up and restoring? this 'bug' is unknown to me, did you tried to reproduce on phpMyAdmin demo servers? http://pma.cihar.com/ http://wiki.cihar.com/pma/Getting_Help -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what is a schema? what is a database?
On Tue, 04 Mar 2008 09:48:03 -0600, Paul DuBois wrote: Apparently MySQL lacks this feature, but what feature is it lacking? There's no equivalent to: SELECT * FROM database.schema.table; In MySQL, the two are equivalent. The keyword DATABASE or DATABASES can be replaced with SCHEMA or SCHEMAS wherever it appears. Right, but that wasn't exactly what I was asking. I'm fairly familiar with MySQL but am trying to understand this criticism of it. Not being familiar with other databases I have no reference point. What are they getting at? Why would you want to do a query of: SELECT * FROM database.schema.table; Obviously, this is non-sense in MySQL, where database == schema. -Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using MySQL with its data files on a CD-R (recordable CD)
On Wed, 05 Mar 2008 00:13:56 -0500, Michael Hemer wrote: I have been researching to see if it's possible to have a MySQL database with it's data files on a cd-rom, but could use some help to determine if I have found out the full truth of what's possible. I would appreciate any additional info people have to offer. Would this be like an ldap server? -Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: password for system user
On Tue, 04 Mar 2008 08:44:47 -0500, Dan Rogart wrote: You can have a file called .my.cnf in your home directory that stores it. Ah, thanks. I don't have a .my.cnf file in my home directory, but I do have something in /etc which seems to be what I'm after. I can get it working for logging into MySQL as the root db admin but can't add the rails MySQL user so that user thufir can login to MySQL as rails passing the password from /etc/my.cnf (too many pronouns for that to make sense). Some success: [EMAIL PROTECTED] ~ $ [EMAIL PROTECTED] ~ $ [EMAIL PROTECTED] ~ $ mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 20 Server version: 5.0.44-log Gentoo Linux mysql-5.0.44 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql quit Bye [EMAIL PROTECTED] ~ $ head /etc/mysql/my.cnf -n 7 # /etc/mysql/my.cnf: The global mysql configuration file. # $Header: /var/cvsroot/gentoo-x86/dev-db/mysql/files/my.cnf-4.1,v 1.3 2006/05/05 19:51:40 chtekk Exp $ # The following options will be passed to all MySQL clients [client] user= root password= password [EMAIL PROTECTED] ~ $ do I need to create a local .my.cnf file? thanks, Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unnormalize db here is more efficient?
On Tue, 04 Mar 2008 15:04:30 +0100, Nacho Garcia wrote: or count the comments on elements_comments table. I guess the last option could be very slow with lot of rows because mysql has to scan all of them .. and the first option seems to be very fast. I would certainly want to be able to: SELECT e.element, c.comment FROM elements, comments AS e, c WHERE e.id=c.element_id; Surely elements.id should be a primary key? And, comments.element_id would be a foreign key? The only way to be able to get a result like: elementscomments = earth a comment on the earth winda comment of the wind firea comment on fire water a comment on water water another comment on water water yet another comment on water earth again, with the earth! and *not* have problems with redundant data is to decompose into multiple tables. Perhaps an index would speed things up? There's a calculation involved? I didn't follow what was be calculated. Perhaps GROUP BY and COUNT can be used to do the calculation you want. -Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: Re: what is a schema? what is a database?
On Tue, 04 Mar 2008 07:21:21 -0800, Garris, Nicole wrote: My experience (Oracle, PostgreSQL, MySQL, SQL Server) is that every DBMS is different in this regard. Microsoft's SQL Server works like this: A SQL Server instance (server) can have many databases. A database can have many schemas, schema simply being a grouping for objects in a database. In a SQL Server 2005 database, there can be two tables named Product if one is in the schema Sales and the other is in the schema Manufacture. The two tables are Sales.Product and Manufacture.Product. A fully qualified SQL Server object name is server.database.schema.object. In your two examples: SELECT * FROM server.sales.schema.product; SELECT * FROM server.product.schema.product; I'm a bit tired, so maybe I'm not seeing it, but what goes in the schema place holder? -Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Debugging mysql limits
On Tue, 04 Mar 2008 08:18:08 -0500, Phil wrote: Just inheritance from an old design that has passed it's limits. Just checking :) I was talking to someone about redundancy in a table and he was like that's good though, because there are multiple (blah, blah, blah)...but it does screw up some queries! when I asked what the primary key was going to be for the new table(s) he mentioned that when the db was initially designed that they didn't know about primary keys! As if PK's are a fad... -Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to select total votes for each comment?
On Wed, 05 Mar 2008 09:29:03 +0100, Sebastian Mendel wrote: SELECT comments.content, SUM(votes.votes) FROM comments LEFT JOIN votes ON comments.id = votes.comment_id GROUP BY comments.id Interesting :) -Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: password for system user
Hi, On 3/5/08 5:58 AM, Thufir [EMAIL PROTECTED] wrote: On Tue, 04 Mar 2008 08:44:47 -0500, Dan Rogart wrote: You can have a file called .my.cnf in your home directory that stores it. Ah, thanks. I don't have a .my.cnf file in my home directory, but I do have something in /etc which seems to be what I'm after. I can get it working for logging into MySQL as the root db admin but can't add the rails MySQL user so that user thufir can login to MySQL as rails passing the password from /etc/my.cnf (too many pronouns for that to make sense). Some success: [EMAIL PROTECTED] ~ $ [EMAIL PROTECTED] ~ $ [EMAIL PROTECTED] ~ $ mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 20 Server version: 5.0.44-log Gentoo Linux mysql-5.0.44 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql quit Bye [EMAIL PROTECTED] ~ $ head /etc/mysql/my.cnf -n 7 # /etc/mysql/my.cnf: The global mysql configuration file. # $Header: /var/cvsroot/gentoo-x86/dev-db/mysql/files/my.cnf-4.1,v 1.3 2006/05/05 19:51:40 chtekk Exp $ # The following options will be passed to all MySQL clients [client] user= root password= password [EMAIL PROTECTED] ~ $ do I need to create a local .my.cnf file? thanks, Thufir /etc/my.cnf sets things globally, so if you put your root password in there then anyone who logs on to that box can just type 'mysql' and log on to your database instance with root privileges. That may or may not be a problem for you. If you want to easily log in as the user 'rails' when you have logged in to the box as thufir, then yes, you should create a local .my.cnf file in ~/thufir with the rails credentials. I think that should do it for you. -Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what is a schema? what is a database?
Hi Thufir, all ! Thufir wrote: On Tue, 04 Mar 2008 09:48:03 -0600, Paul DuBois wrote: [[...]] In MySQL, the two are equivalent. The keyword DATABASE or DATABASES can be replaced with SCHEMA or SCHEMAS wherever it appears. Right, but that wasn't exactly what I was asking. I'm fairly familiar with MySQL but am trying to understand this criticism of it. Not being familiar with other databases I have no reference point. What are they getting at? Why would you want to do a query of: SELECT * FROM database.schema.table; Obviously, this is non-sense in MySQL, where database == schema. AFAIK, this all goes back to an ANSI standard for SQL in the mid-80s. That standard had a CREATE SCHEMA command, and it served to introduce multiple name spaces for table and view names. All tables and views were created within a schema. I do not know whether that version defined some cross-schema access to tables and views, but I assume it did. AFAIR, no product (at least back then) really implemented it, that whole concept was more theory than practice. OTOH, ISTR this version of the standard did not have the concept of a user or a CREATE USER command, so there were products that used the concept of a user (who then had his own name space for tables and views) to implement their equivalent of schema. This is an area where systems differ. As far as administration is concerned, this should not matter too much, because here you have differences anyway. As far as you look at application code, you only have to care about cases where one application accesses tables from multiple name spaces. AFAIK, all systems support a syntax name space.local identifier, and for this it should not matter whether the name space is that of a user, a schema, or a database. (I do not claim having done a complete research, so maybe there are systems which differ in this regard.) I have not heard of a three level naming scheme yet. Regards, 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]
Re: Using MySQL with its data files on a CD-R (recordable CD)
I believe that this situation could work with ldap, but it is not what I had imagined. I was picturing a database for retail. The database could hold a list of products for sale and details relating to those products. -Michael Thufir [EMAIL PROTECTED] wrote On Wed, 05 Mar 2008 00:13:56 -0500, Michael Hemer wrote: I have been researching to see if it's possible to have a MySQL database with it's data files on a cd-rom, but could use some help to determine if I have found out the full truth of what's possible. I would appreciate any additional info people have to offer. Would this be like an ldap server? -Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query runs very sloooow
I need help to optimize this following query. It runs very slow and I cant find any direct errors in it. SELECT 1 * t1.termfreq as viktatantal, t1.tag, t1.url FROM tag_keys t1 LEFT JOIN tag_ad_map tm1 ON t1.id = tm1.tag_id LEFT JOIN tag_ad_map tm2 ON tm1.ad_id = tm2.ad_id LEFT JOIN tag_keys t2 ON t2.id = tm2.tag_id WHERE t2.url = 'motor' AND t1.url != 'motor' GROUP BY t1.id ORDER BY viktatantal DESC, RAND() LIMIT 80 Any help would be very appriciated!
Re: Query runs very sloooow
Hi, On Wed, Mar 5, 2008 at 9:11 AM, Johan Thorvaldsson [EMAIL PROTECTED] wrote: I need help to optimize this following query. It runs very slow and I cant find any direct errors in it. SELECT 1 * t1.termfreq as viktatantal, t1.tag, t1.url FROM tag_keys t1 LEFT JOIN tag_ad_map tm1 ON t1.id = tm1.tag_id LEFT JOIN tag_ad_map tm2 ON tm1.ad_id = tm2.ad_id LEFT JOIN tag_keys t2 ON t2.id = tm2.tag_id WHERE t2.url = 'motor' AND t1.url != 'motor' GROUP BY t1.id ORDER BY viktatantal DESC, RAND() LIMIT 80 Any help would be very appriciated! You should use EXPLAIN. If you don't understand the output of EXPLAIN, post the output back to this list and we can help you understand it. Cheers Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query runs very sloooow
Thanks baron for you reply. Here is the result from the explain: 1 SIMPLE t2 ref PRIMARY,url url 194 const 1 Using where; Using temporary; Using filesort 1 SIMPLE tm1 index PRIMARY PRIMARY 8 NULL 149115 Using index 1 SIMPLE t1 eq_ref PRIMARY,url PRIMARY 4 rubbetdev.tm1.tag_id 1 Using where 1 SIMPLE tm2 eq_ref PRIMARY PRIMARY 8 rubbetdev.t2.id,rubbetdev.tm1.ad_id 1 Using where; Using index 2008/3/5, Baron Schwartz [EMAIL PROTECTED]: Hi, On Wed, Mar 5, 2008 at 9:11 AM, Johan Thorvaldsson [EMAIL PROTECTED] wrote: I need help to optimize this following query. It runs very slow and I cant find any direct errors in it. SELECT 1 * t1.termfreq as viktatantal, t1.tag, t1.url FROM tag_keys t1 LEFT JOIN tag_ad_map tm1 ON t1.id = tm1.tag_id LEFT JOIN tag_ad_map tm2 ON tm1.ad_id = tm2.ad_id LEFT JOIN tag_keys t2 ON t2.id = tm2.tag_id WHERE t2.url = 'motor' AND t1.url != 'motor' GROUP BY t1.id ORDER BY viktatantal DESC, RAND() LIMIT 80 Any help would be very appriciated! You should use EXPLAIN. If you don't understand the output of EXPLAIN, post the output back to this list and we can help you understand it. Cheers Baron
Re: what is a schema? what is a database?
Sorry. An example of a fully qualified SQL Server object name is: SELECT * FROM Server123.Database456.Sales.Product The object Server123.Database456.Manufacture.Product is a different table from Server123.Database456.Sales.Product. Joerg Bruehe in his post called a schema a namespace, I believe he is correct. -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of Thufir Sent: Wednesday, March 05, 2008 3:09 AM To: mysql@lists.mysql.com Subject: Re: FW: Re: what is a schema? what is a database? On Tue, 04 Mar 2008 07:21:21 -0800, Garris, Nicole wrote: My experience (Oracle, PostgreSQL, MySQL, SQL Server) is that every DBMS is different in this regard. Microsoft's SQL Server works like this: A SQL Server instance (server) can have many databases. A database can have many schemas, schema simply being a grouping for objects in a database. In a SQL Server 2005 database, there can be two tables named Product if one is in the schema Sales and the other is in the schema Manufacture. The two tables are Sales.Product and Manufacture.Product. A fully qualified SQL Server object name is server.database.schema.object. In your two examples: SELECT * FROM server.sales.schema.product; SELECT * FROM server.product.schema.product; I'm a bit tired, so maybe I'm not seeing it, but what goes in the schema place holder? -Thufir -- 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]
Select Statement
Hi, I have 2 tables as follows: table 1 contains code,order_qty table 2 contains code,stock_qty table 1: code1, 10 code2, 2 code3, 5 table 2: code1, 3 code3, 5 code1, 4 code3, 2 I need to see the following result: code | order_qty| stock_qty code1 | 10 | 7 code2 | 2| 0 The condition is : order_qty sum(stock_qty) and note that if code is not found in table2, stock_qty is 0. Can this be achieved with a single select query? or suggest the best option. Thanks. Veln -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query runs very sloooow
CREATE TABLE structure looks like this: CREATE TABLE `tag_ad_map` ( `ad_id` int(11) NOT NULL default '0', `tag_id` int(11) NOT NULL default '0', `termfreq` int(11) NOT NULL default '0', PRIMARY KEY (`tag_id`,`ad_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 CREATE TABLE `tag_keys` ( `id` int(11) NOT NULL auto_increment, `tag` varchar(32) collate utf8_swedish_ci NOT NULL default '', `idf` double NOT NULL default '0', `url` varchar(64) collate utf8_swedish_ci NOT NULL default '', `termfreq` int(11) NOT NULL default '0', PRIMARY KEY (`id`), KEY `url` (`url`) ) ENGINE=MyISAM AUTO_INCREMENT=11374 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci 2008/3/5, Baron Schwartz [EMAIL PROTECTED]: Hi, On Wed, Mar 5, 2008 at 9:11 AM, Johan Thorvaldsson [EMAIL PROTECTED] wrote: I need help to optimize this following query. It runs very slow and I cant find any direct errors in it. SELECT 1 * t1.termfreq as viktatantal, t1.tag, t1.url FROM tag_keys t1 LEFT JOIN tag_ad_map tm1 ON t1.id = tm1.tag_id LEFT JOIN tag_ad_map tm2 ON tm1.ad_id = tm2.ad_id LEFT JOIN tag_keys t2 ON t2.id = tm2.tag_id WHERE t2.url = 'motor' AND t1.url != 'motor' GROUP BY t1.id ORDER BY viktatantal DESC, RAND() LIMIT 80 Any help would be very appriciated! You should use EXPLAIN. If you don't understand the output of EXPLAIN, post the output back to this list and we can help you understand it. Cheers Baron
Re: Query runs very sloooow
Also you have a composite key on for the prymary key in tag_keys . ad_id should probably be a seperate index for LEFT JOIN tag_ad_map tm2 ON tm1.ad_id = tm2.ad_id to join well. The Index should be ignored because the left most portion of the the index is not used... On 3/5/08, Rob Wultsch [EMAIL PROTECTED] wrote: From a brief glance: 1 * seems odd to me. Is this an attempt at some sort of cast? ORDER BY viktatantal DESC, RAND() LIMIT 80 How many results would this return without the limit. The ORDER BY RAND() will never help a query. All the possible results have to be computed... Do you mean LEFT JOIN, or do you really mean INNER JOIN? If any of those LEFT joins fail then the result is not excluded by the join. until the where conditions come into play. I would rebuild the query with inner joins and the first table being t2, I would then rerun the explain and consider composite indexes. Mysql is not know for self join tables well. -- Rob Wultsch (480)223-2566 [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn) -- Rob Wultsch (480)223-2566 [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query runs very sloooow
From a brief glance: 1 * seems odd to me. Is this an attempt at some sort of cast? ORDER BY viktatantal DESC, RAND() LIMIT 80 How many results would this return without the limit. The ORDER BY RAND() will never help a query. All the possible results have to be computed... Do you mean LEFT JOIN, or do you really mean INNER JOIN? If any of those LEFT joins fail then the result is not excluded by the join. until the where conditions come into play. I would rebuild the query with inner joins and the first table being t2, I would then rerun the explain and consider composite indexes. Mysql is not know for self join tables well. -- Rob Wultsch (480)223-2566 [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what is a schema? what is a database?
my understanding is that Namespace is a defined grouping of classes http://m5.eecs.umich.edu/docs/namespaceMySQL.html where MySQL triggers a namespace must be unique within the schema (database). http://markmail.org/message/m5icpi2luv6baijt?q=Joerg+Bruehe+AND+namespace +AND+definitionpage=1refer=tpuhsicnt5h5helm Buena Suerte/Viel Gluck Martin - Original Message - From: Garris, Nicole [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, March 05, 2008 10:33 AM Subject: Re: what is a schema? what is a database? Sorry. An example of a fully qualified SQL Server object name is: SELECT * FROM Server123.Database456.Sales.Product The object Server123.Database456.Manufacture.Product is a different table from Server123.Database456.Sales.Product. Joerg Bruehe in his post called a schema a namespace, I believe he is correct. -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of Thufir Sent: Wednesday, March 05, 2008 3:09 AM To: mysql@lists.mysql.com Subject: Re: FW: Re: what is a schema? what is a database? On Tue, 04 Mar 2008 07:21:21 -0800, Garris, Nicole wrote: My experience (Oracle, PostgreSQL, MySQL, SQL Server) is that every DBMS is different in this regard. Microsoft's SQL Server works like this: A SQL Server instance (server) can have many databases. A database can have many schemas, schema simply being a grouping for objects in a database. In a SQL Server 2005 database, there can be two tables named Product if one is in the schema Sales and the other is in the schema Manufacture. The two tables are Sales.Product and Manufacture.Product. A fully qualified SQL Server object name is server.database.schema.object. In your two examples: SELECT * FROM server.sales.schema.product; SELECT * FROM server.product.schema.product; I'm a bit tired, so maybe I'm not seeing it, but what goes in the schema place holder? -Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using MySQL with its data files on a CD-R (recordable CD)
On Wed, Mar 5, 2008 at 12:13 AM, Michael Hemer [EMAIL PROTECTED] wrote:
Hi,
I have been researching to see if it's possible to have a MySQL database
with it's data files on a cd-rom, but could use some help to determine if I
have found out the full truth of what's possible. I would appreciate any
additional info people have to offer.
The simple answer is yes. Data is data, regardless of how and
where it's stored.
However, a more truthful answer uncovers some complexities,
including the speed at which the data can be transferred from the CD
(which will be slower than direct access from a hard drive), as well
as the version limitations. If the MySQL databases on the CD are, for
example, in MySQL 5.x format, but your end-user is still using MySQL
3.23, it's not going to work. Also, there will need to be some
configuration variables modified on each end-user system so that the
MySQL server knows where to locate the CD databases (and then the
server will need to be restarted, as well).
So to summarize, it can be done, but don't expect it to be a
point-click-send operation; there are going to be some bumps in the
road.
--
/Dan
Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a pivot-type issue
This may take a bit of explaining! I have a incoming table structure of PartRef AttribValue ABC0011 10.00 ABC0012 4 ABC0013 A ABC0021 12.00 ABC0022 6 ABC0023 B Etc Where Attrib 1 represents Length Attrib 2 represents Set Qty and Attrib 3 represents Head Shape My Query of SELECT PartRef GROUP_CONCAT(IF(attr_id=1, value,null)) AS Length, GROUP_CONCAT(IF(attr_id=1, value,null)) AS SetQty, GROUP_CONCAT(IF(attr_id=1, value,null)) AS HeadShape From myTable GROUP BY part_ref Gives me PartNo SetQty Length HeadShape ABC0014 10.00A ABC0026 12.00B Which is fine for part ref with only ONE entry... however The data CAN come with multiple entries for each PartRef which now gives me, Correctly PartNo SetQty LengthHeadShape ABC0014,5 10.00,12.00 A,B ABC002612.00B What I now need to do is to SPLIT out the doubled up fields and end up with .. PartNo SetQty LengthHeadShape ABC0014 10.00A ABC0015 12.00B ABC002612.00B Any pointers would be gratefully received Roger
Re: Using MySQL with its data files on a CD-R (recordable CD)
Daniel Brown [EMAIL PROTECTED] wrote
On Wed, Mar 5, 2008 at 12:13 AM, Michael Hemer [EMAIL PROTECTED] wrote:
Hi,
I have been researching to see if it's possible to have a MySQL database
with it's data files on a cd-rom, but could use some help to determine if
I have found out the full truth of what's possible. I would appreciate
any additional info people have to offer.
The simple answer is yes. Data is data, regardless of how and
where it's stored.
Even though I tend to agree that data is data, the how and where does seem
to be important. So far my testing indicates that the approach used by the
storage engine does make a difference. So far it appears that InnoDB, CSV,
and Archive require write access to either the data files, or the directory
in which the data files reside. It's possible that I've overlooked
configuration options that would make it work, but I have tried many
different configurations with no success. So far, MyISAM is the only one
that I've had success with reading the data from a CD.
However, a more truthful answer uncovers some complexities,
including the speed at which the data can be transferred from the CD
(which will be slower than direct access from a hard drive), as well
as the version limitations. If the MySQL databases on the CD are, for
example, in MySQL 5.x format, but your end-user is still using MySQL
3.23, it's not going to work. Also, there will need to be some
configuration variables modified on each end-user system so that the
MySQL server knows where to locate the CD databases (and then the
server will need to be restarted, as well).
I definitely agree that data on CD will be slower to read than on a hard
drive. I also agree that details like the version used will need to be kept
the same from computer to computer. For my scenario though, I am more
concerned with being able to leave the data on the CD and not needing to
copy it to the local hard drive, than I am about performance and
standardization issues.
So to summarize, it can be done, but don't expect it to be a
point-click-send operation; there are going to be some bumps in the
road.
I have found that it can be done using MyISAM, but so far it appears to me
that it is not possible with InnoDB, CSV, or Archive. I will continue to
experiment with any configuration options that I can find that I haven't
already tried, but so far it's not looking feasible for every storage
engine. If you think of any configuration options that you know will make
this work with InnoDB, CSV, or Archive, please let me know.
Thanks,
Michael
--
/Dan
Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what is a schema? what is a database?
Hi ! Martin Gainty wrote: my understanding is that Namespace is a defined grouping of classes http://m5.eecs.umich.edu/docs/namespaceMySQL.html My use of the term name space was much more generic, similar to how compilers use it: When you define a record type (C: struct, Pascal: record, ...), you create (and enter) a new name space, the field names are valid within that record type only, and different record types can have fields with identical names without conflict. Similar each function (procedure, subroutine, ...) opens a new name space for its own local variables. Within SQL, each CREATE TABLE opens a new name space: column names are valid within that table only, and different tables may use the same name for different columns. (Yes, I know you can omit table. in a SQL statement if the column name is unique among the tables in that statement - you get the idea.) And similar, a schema in that ANSI SQL standard opened a name space for tables and views, and AFAIR that was its only purpose. (No, I will not try to dig up that standard - its schema concept had no practical relevance in products back then.) 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]
Re: what is a schema? what is a database?
On Wed, 05 Mar 2008 15:01:12 +0100, Joerg Bruehe wrote: I have not heard of a three level naming scheme yet. Aha, thanks for the history, helps to put what I was reading into context. -Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4 Grant command
OK, what is wrong with the following statement? MySQL 4.1 doesn't like my syntax ... mysql grant all privileges on *.* to [EMAIL PROTECTED] identified by 'myownpassword';
Re: MySQL 4 Grant command
At 4:12 PM -0800 3/5/08, Garris, Nicole wrote: OK, what is wrong with the following statement? MySQL 4.1 doesn't like my syntax ... mysql grant all privileges on *.* to [EMAIL PROTECTED] identified by 'myownpassword'; % needs quotes around it. I recommend always quoting the username and hostname: 'login3'@'%' -- 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]
