Re: Using MySQL to store/archive code/html etc?
On Fri, 27 May 2005 15:39:36 -0400, wrote: news [EMAIL PROTECTED] wrote on 05/27/2005 02:46:09 PM: It is possible to do just what you propose. You could even FT index your code so that you could find certain key phrases (assuming that the keyphrase you want is at least 4 (or 3) characters long and does not appear in over 50% of the documents. HOWEVER, it is more proper to store files in a file system and just store the files path information in a database (along with other information like who it was for, who wrote it, etc.) Once you start doing that, though, you might as well take that last itty-bitty step and move into a full version-control system. There are several out there, many of them are free. Not only do they keep your code well organized (and protected, usually) but it allows you to do all sorts of interesting things (like reverting to previous versions). That way if you modified the same file 5 times for 5 different clients, you wouldn't have to have 5 copies floating around. Your VCS software would allow you to regress your current version to any prior version. In my humble opinion, that's the appropriate class of tool for the task you mention. Some sample names of VCS software: BitKeeper, Visual Source Safe. Shawn Green Shawn, I maintain, update dozens of websites, and many more sites that have been archived. Many of them have hundreds of pages but I'm only involved in a few of them etc. What I'm looking for is a way of storing,identifying code nuggets. I currently do this in flat text files but these are better suited to recording say a cute mysql query etc than a wodge of html. and already use Googles Desktop Search, plus my own Intranet Search grep,find, etc. Now amongst other problems these pages have non-unique names index.html etc. I feel the question of whether I should be using VCS is another issue, probably should be! I 'm searching for another level of retrieval sophistication. Sad that a database does not appear ideal. Welcome any lateral ideas -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Could not initialize master info structure, check permisions on master.info
Hi Everybody, I am new to the list,infact this is my first mail to the list. Ok here goes the query :- While restoring the database from backup, I am getting the following error. ERROR 1201 at line 1: Could not initialize master info structure, check permisions on master.info . I am doing change master to and immediately after it start slave; is issued. I think I am getting this error from one of these two commands. Can anybody tell me why I am getting this error and how severe is this problem. Any help regarding this will be deeply appreciated. Thanks, Anurag Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
Re: 4.0 - 4.1 undocumented incompatible change w/float?
Hello.
In my opinion MySQL follows the documentation. From:
http://dev.mysql.com/doc/mysql/en/numeric-types.html
When a floating-point or fixed-point column is assigned a value that
exceeds the range implied by the specified (or default) precision and
scale, MySQL stores the value representing the corresponding end point
of that range.
Don MacAskill [EMAIL PROTECTED] wrote:
[-- text/plain, encoding 7bit, charset: ISO-8859-1, 75 lines --]
I've already opened a support ticket with MySQL on this issue, but
thought someone here might know the answer, too:
Hi there,
I'm trying to make sure I'm not doing something stupid. I'm trying to
migrate from 4.0 to 4.1, but I'm hitting a snag. I can't find anything
in the docs to describe what I'm seeing.
MySQL's docs seem to suggest that a column with 'float(5,2)' specifies
as display width of 5 and a decimal precision of 2. I assumed that the
'display width' worked like the other numeric types and would allow
storing larger values.
On 3.32 and 4.0, this was the case. I was able to store large numbers in
this float:
mysql explain testFloat;
+--+-+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+--+-+--+-+-+---+
| Floating | float(5,2) unsigned | | | 0.00 | |
+--+-+--+-+-+---+
1 row in set (0.02 sec)
mysql insert into testFloat values ('134567.12344321');
Query OK, 1 row affected (0.00 sec)
mysql select * from testFloat;
+---+
| Floating |
+---+
| 134567.12 |
+---+
1 row in set (0.00 sec)
After upgrading to 4.1, however, this functionality seems partially broken:
mysql explain testFloat;
+--+-+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+--+-+--+-+-+---+
| Floating | float(5,2) unsigned | | | 0.00 | |
+--+-+--+-+-+---+
1 row in set (0.00 sec)
mysql insert into testFloat values ('134567.12344321');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql select * from testFloat;
+--+
| Floating |
+--+
| 999.99 |
+--+
1 row in set (0.00 sec)
I say partially broken, because my existing 4.0 data is fine. In fact, I
can change an InnoDB table to MyISAM and then back to InnoDB using
'alter table' and it retains the proper values.
FYI, this is on both 4.1.11 RPM distro from MySQL and 4.1.10a-1 from Red
Hat Enterprise 4. Occurs on both x86 and x86_64, RH9 and RHEL4
respectively.
Any ideas? Is this really an incompatible change that just hasn't been
documented? Am I missing something obvious? I have a lot of tables I
expected to behave the same way as they did in prior releases...
Don
[-- application/x-pkcs7-signature, encoding base64, 60 lines, name: smime.p7s
--]
[-- Description: S/MIME Cryptographic Signature --]
--
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: how to print warnings from mysqlimport...debug_options ??
Hello. Are you sure that you have compiled in debugging support? You have an old version of MySQL, and there was a bug when MySQL programs were showing that they have debugging, but really they were without it. Bomb Diggy [EMAIL PROTECTED] wrote: I can't seem to get ahold of the 6 warnings being generated when I import a csv file. My ~/.my.cnf file says this: [client] fields-terminated-by=, fields-enclosed-by=\ #debug=log.txt #debug=d,info,error,query,general,where:1,load.trace debug=warning,load.trace Nothing I do in the 'debug' param seems to actually output any information. Here's my command: mysqlimport -v -h [host] [database] [table_and_file_name].csv -u [username] -p Here's my version info: mysqlimport Ver 3.4 Distrib 4.0.18, for pc-linux (i686) Here's my output to STDOUT/STDERR: Connecting to [hostname] Selecting database [database] Loading data from LOCAL file: [table_and_file_name.csv into [table_name] [database].[table_name]: Records: 3 Deleted: 0 Skipped: 0 Warnings: 6 Disconnecting from [hostname] I want to see those 6 warnings. I've tried command-line '--debug=foo' as well. Thanks. __ Do you Yahoo!? Yahoo! Small Business - Try our new Resources site http://smallbusiness.yahoo.com/resources/ -- 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]
RENAME TABLE with CONCAT string fails
I'm trying to rename some tables for archival, but the table renaming is failing when I use CONCAT() to form the table string name: mysql RENAME TABLE flows TO flows_tmp, flows_new TO flows, flows_tmp TO CONCAT(flows_, DATE_SUB(CURDATE(), interval 1 day)); ERROR 1064: You have an error in your SQL syntax near 'CONCAT(flows_, DATE_SUB(CURDATE(), interval 1 day))' at line 1 If I run the CONCAT() command by itself, everything works as expected: ++ | CONCAT(flows_, DATE_SUB(CURDATE(), interval 1 day)) | ++ | flows_2005-05-27 | ++ 1 row in set (0.00 sec) Is there some magic pixie dust I need to throw around the CONCAT() to have it interpolate into the RENAME TABLE command correctly? This is on mysql-server 3.23.58 on a RHEL 3 clone. Thanks, -- Jason Dixon DixonGroup Consulting http://www.dixongroup.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RENAME TABLE with CONCAT string fails
At 8:12 -0400 5/28/05, Jason Dixon wrote: I'm trying to rename some tables for archival, but the table renaming is failing when I use CONCAT() to form the table string name: CONCAT() produces a string, not an identifier. mysql RENAME TABLE flows TO flows_tmp, flows_new TO flows, flows_tmp TO CONCAT(flows_, DATE_SUB(CURDATE(), interval 1 day)); ERROR 1064: You have an error in your SQL syntax near 'CONCAT(flows_, DATE_SUB(CURDATE(), interval 1 day))' at line 1 If I run the CONCAT() command by itself, everything works as expected: ++ | CONCAT(flows_, DATE_SUB(CURDATE(), interval 1 day)) | ++ | flows_2005-05-27 | ++ 1 row in set (0.00 sec) Is there some magic pixie dust I need to throw around the CONCAT() to have it interpolate into the RENAME TABLE command correctly? This is on mysql-server 3.23.58 on a RHEL 3 clone. -- 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]
Challenging: Store and Query Music Lesson Management System
I am about to begin development on a website which manages the availability of a cello teacher and lessons that students have signed up for. Currently, I have two tables: Availability (date, startTime, endTime) - This table stores time spans that people can sign up for lessons Lessons (date, startTime, endTime) - This table stores each lesson. A lesson can begin at times of 5 minute intervals (5:30, 5:35, etc) and will vary in length between 30 and 60 minutes. I will be accessing the database with PHP. I want to get a list of all available time slots (spots at least 30 minutes long which fall within a time span in Availability and are not occupied by Lessons.) I will need to determine open slots for 2-4 weeks in one page call. Ideally I'd like to keep the processing within mySQL because if I have to load all the lessons into arrays in PHP to determine openings, it will slow it down considerably. You may assume that I will be using mySQL 4.1.x (in case you need subselects for the solution). Thanks so much! -Greg
'ERROR 1045 (28000): Access denied' when trying to LOAD DATA INFILE.
Hi all, I connect via ssh from a Linux RedHat 9 box to a remote FreeBSD server. There I can run MySQL and successfully add/remove tables etc. However, when I tried to upload a comma separated value file, I got an access denied error, although all privileges are granted to my account. What should I do/am I doing wrong? [EMAIL PROTECTED] jeroen]$ uname -rs Linux 2.4.21-4.EL [EMAIL PROTECTED] jeroen]$ ssh [EMAIL PROTECTED] Password: [EMAIL PROTECTED] ~ uname -rs FreeBSD 5.3-RELEASE [EMAIL PROTECTED] ~ mysql -V mysql Ver 14.7 Distrib 4.1.10, for portbld-freebsd5.3 (i386) [EMAIL PROTECTED] ~ mysql -ubiogeek -p Enter password: mysql use biogeek; Database changed mysql LOAD DATA INFILE 'zipcode.data' INTO TABLE zipcodes FIELDS TERMINATED BY ','; ERROR 1045 (28000): Access denied for user 'biogeek'@'localhost' (using password: YES) mysql show grants for 'biogeek'@'localhost'; +-+ | Grants for [EMAIL PROTECTED] | +-+ | GRANT USAGE ON *.* TO 'biogeek'@'localhost' IDENTIFIED BY PASSWORD '*66...' | | GRANT ALL PRIVILEGES ON `biogeek`.* TO biogeek'@'localhost' | +-+ 2 rows in set (0.00 sec) PS: I do not have root access. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RENAME TABLE with CONCAT string fails
On May 28, 2005, at 8:51 AM, Paul DuBois wrote: At 8:12 -0400 5/28/05, Jason Dixon wrote: I'm trying to rename some tables for archival, but the table renaming is failing when I use CONCAT() to form the table string name: CONCAT() produces a string, not an identifier. Fine. Is there any way to do this in MySQL or do I need to fall back on my Perl? It's not a big deal, I'm just curious now. Thanks, -- Jason Dixon DixonGroup Consulting http://www.dixongroup.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Inner workings of a JOIN
Meant to send this to the list. Christopher J. Noyes - Original Message - From: [EMAIL PROTECTED] To: Roger Baklund [EMAIL PROTECTED] Sent: Friday, May 27, 2005 9:55 PM Subject: Re: Inner workings of a JOIN Does it first create some sort of CROSS JOIN with the two tables (resulting in a 5,000,000,000 row table) this is what is called a cartesian join, which only results when the two tables are not correctly linked. First you need to understand that most databases frequently use a btree type index which will allow any piece of data to be found with four or less seeks. What happens is parts of your query that the optimizer thinks can narrow the query down, get applied to the index which identifies the rows that match, and the next terms get applied to those rows, when it comes to the join, it does a lookup on the index of the second table based on the columns in the first table that were found and brings back the rows that match, it it winds up being a one to many, the number of rows grows. That is a reason why you need well designed indexes on your tables for the queries that you commonly run. If there are no indexes on important columns, particularly where there are joins, it can cause a full table scan which is very slow, rather than an index range scan, which is fast. Christopher J. Noyes - Original Message - From: Roger Baklund [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: James Tu [EMAIL PROTECTED] Sent: Thursday, May 26, 2005 8:32 AM Subject: Re: Inner workings of a JOIN James Tu wrote: What does MySQL do internally when you perform a LEFT JOIN? You can read about it here: URL: http://dev.mysql.com/doc/mysql/en/left-join-optimization.html If you need more details, you could read the source... Let's say you have two tables: Table A has 1,000,000 rows Table B has 5,000 rows When you perform the following LEFT JOIN: Select A.*, B.* FROM A, B WHERE A.lastname = 'doe' AND A.id http://A.id = B.id http://B.id eh... that's not a LEFT JOIN...? What does MySQL do internally? Does it first create some sort of CROSS JOIN with the two tables (resulting in a 5,000,000,000 row table) and then finding the matching rows based on the WHERE clause? If there is no index on A.lastname and B.id, probably yes... Use the EXPLAIN SELECT command to see how MySQL plan to solve the query. URL: http://dev.mysql.com/doc/mysql/en/explain.html -- Roger -- 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: RENAME TABLE with CONCAT string fails
At 11:00 -0400 5/28/05, Jason Dixon wrote: On May 28, 2005, at 8:51 AM, Paul DuBois wrote: At 8:12 -0400 5/28/05, Jason Dixon wrote: I'm trying to rename some tables for archival, but the table renaming is failing when I use CONCAT() to form the table string name: CONCAT() produces a string, not an identifier. Fine. Is there any way to do this in MySQL or do I need to fall back on my Perl? It's not a big deal, I'm just curious now. Perl. Construct the table identifier and place the result into your SQL statement, then execute the statement. -- 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: RENAME TABLE with CONCAT string fails
Hi,
As Paul said, since concat gives a string, you can use this fact in preparing
statement (v4.1). This works fine for me :
But use replace to change '-' to '_' in the table_name.
set @tt:=concat('rename table flows_2005_05_27 to ',CONCAT(flows_,
replace(DATE_SUB(CURDATE(), interval 2 day),'-','_')),';');
select @tt;
mysql show tables like 'flow%';
+-+
| Tables_in_world (flow%) |
+-+
| flows_2005_05_27|
+-+
1 row in set (0.00 sec)
mysql
mysql set @tt:=concat('rename table flows_2005_05_27 to ',CONCAT(flows_,
replace(DATE_SUB(CURDATE(), interval 2 day),'-','_')),';');
mysql prepare stmt from @tt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql execute stmt ;
mysql deallocate prepare stmt;
mysql show tables like 'flow%';
+-+
| Tables_in_world (flow%) |
+-+
| flows_2005_05_26|
+-+
1 row in set (0.00 sec)
Mathias
Selon Paul DuBois [EMAIL PROTECTED]:
At 11:00 -0400 5/28/05, Jason Dixon wrote:
On May 28, 2005, at 8:51 AM, Paul DuBois wrote:
At 8:12 -0400 5/28/05, Jason Dixon wrote:
I'm trying to rename some tables for archival, but the table
renaming is failing when I use CONCAT() to form the table string
name:
CONCAT() produces a string, not an identifier.
Fine. Is there any way to do this in MySQL or do I need to fall
back on my Perl? It's not a big deal, I'm just curious now.
Perl. Construct the table identifier and place the result into your
SQL statement, then execute the statement.
--
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 General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RENAME TABLE with CONCAT string fails
At 17:50 +0200 5/28/05, [EMAIL PROTECTED] wrote:
Hi,
As Paul said, since concat gives a string, you can use this fact in preparing
statement (v4.1). This works fine for me :
Ah, yes. This'll work. I forgot about prepared statements. :-)
But use replace to change '-' to '_' in the table_name.
set @tt:=concat('rename table flows_2005_05_27 to ',CONCAT(flows_,
replace(DATE_SUB(CURDATE(), interval 2 day),'-','_')),';');
select @tt;
mysql show tables like 'flow%';
+-+
| Tables_in_world (flow%) |
+-+
| flows_2005_05_27|
+-+
1 row in set (0.00 sec)
mysql
mysql set @tt:=concat('rename table flows_2005_05_27 to ',CONCAT(flows_,
replace(DATE_SUB(CURDATE(), interval 2 day),'-','_')),';');
mysql prepare stmt from @tt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql execute stmt ;
mysql deallocate prepare stmt;
mysql show tables like 'flow%';
+-+
| Tables_in_world (flow%) |
+-+
| flows_2005_05_26|
+-+
1 row in set (0.00 sec)
Mathias
Selon Paul DuBois [EMAIL PROTECTED]:
At 11:00 -0400 5/28/05, Jason Dixon wrote:
On May 28, 2005, at 8:51 AM, Paul DuBois wrote:
At 8:12 -0400 5/28/05, Jason Dixon wrote:
I'm trying to rename some tables for archival, but the table
renaming is failing when I use CONCAT() to form the table string
name:
CONCAT() produces a string, not an identifier.
Fine. Is there any way to do this in MySQL or do I need to fall
back on my Perl? It's not a big deal, I'm just curious now.
Perl. Construct the table identifier and place the result into your
SQL statement, then execute the statement.
--
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]
--
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]
error 150 / FOREIGN KEY constraint
This is a script I'm trying to execute. My mysql version is 4.1.10. SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS owner; DROP TABLE IF EXISTS folder; DROP TABLE IF EXISTS account; DROP TABLE IF EXISTS role; CREATE TABLE role ( role_id INTEGER NOT NULL, rolename VARCHAR(25) NOT NULL, PRIMARY KEY (role_id)) TYPE = InnoDB; CREATE TABLE account ( username VARCHAR(25) NOT NULL, password VARCHAR(80) NOT NULL, email VARCHAR(80) NOT NULL, first_name VARCHAR(80) NOT NULL, last_name VARCHAR(80) NOT NULL, role_id INTEGER NOT NULL, PRIMARY KEY (username)) TYPE = InnoDB; CREATE TABLE folder ( folder_id INTEGER NOT NULL AUTO_INCREMENT, parent_id INTEGER, foldername VARCHAR(80), PRIMARY KEY (folder_id)) TYPE = InnoDB; CREATE TABLE owner ( parent_id INTEGER NOT NULL, owner VARCHAR(25) NOT NULL, PRIMARY KEY (parent_id), FOREIGN KEY (parent_id) REFERENCES folder(folder_id) ON DELETE CASCADE, FOREIGN KEY (owner) REFERENCES account(username) ON DELETE CASCADE) TYPE = InnoDB; And this is the output: Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) ERROR 1005 (0): Can't create table './lddekeyz/owner.frm' (errno: 150) It seems like the last FOREIGN KEY of the 'owner' table is not correctly formed: $perror 150 MySQL error: 150 = Foreign key constraint is incorrectly formed But I can't see anything wrong with it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error 150 / FOREIGN KEY constraint
Lieven De Keyzer wrote: This is a script I'm trying to execute. My mysql version is 4.1.10. SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS owner; DROP TABLE IF EXISTS folder; DROP TABLE IF EXISTS account; DROP TABLE IF EXISTS role; CREATE TABLE role ( role_id INTEGER NOT NULL, rolename VARCHAR(25) NOT NULL, PRIMARY KEY (role_id)) TYPE = InnoDB; CREATE TABLE account ( username VARCHAR(25) NOT NULL, password VARCHAR(80) NOT NULL, email VARCHAR(80) NOT NULL, first_name VARCHAR(80) NOT NULL, last_name VARCHAR(80) NOT NULL, role_id INTEGER NOT NULL, PRIMARY KEY (username)) TYPE = InnoDB; CREATE TABLE folder ( folder_id INTEGER NOT NULL AUTO_INCREMENT, parent_id INTEGER, foldername VARCHAR(80), PRIMARY KEY (folder_id)) TYPE = InnoDB; CREATE TABLE owner ( parent_id INTEGER NOT NULL, owner VARCHAR(25) NOT NULL, PRIMARY KEY (parent_id), FOREIGN KEY (parent_id) REFERENCES folder(folder_id) ON DELETE CASCADE, FOREIGN KEY (owner) REFERENCES account(username) ON DELETE CASCADE) TYPE = InnoDB; And this is the output: Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) ERROR 1005 (0): Can't create table './lddekeyz/owner.frm' (errno: 150) It seems like the last FOREIGN KEY of the 'owner' table is not correctly formed: $perror 150 MySQL error: 150 = Foreign key constraint is incorrectly formed But I can't see anything wrong with it. Foregin keys have to be indexed in both tables, as explained in the manual http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html. You need an index on owner.owner before you can create the foreign key constraint. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error 150 / FOREIGN KEY constraint
Thanks for the reply, but I just found out about it :) This is my table now, and it is accepted without errors: CREATE TABLE owner ( parent_id INTEGER NOT NULL, owner VARCHAR(25) NOT NULL, PRIMARY KEY (parent_id), INDEX (parent_id), FOREIGN KEY (parent_id) REFERENCES folder(folder_id) ON DELETE CASCADE, INDEX (owner), FOREIGN KEY (owner) REFERENCES account(username) ON DELETE CASCADE) TYPE = InnoDB; From: Stijn Verholen [EMAIL PROTECTED] To: Lieven De Keyzer [EMAIL PROTECTED] Subject: Re: error 150 / FOREIGN KEY constraint Date: Sat, 28 May 2005 23:03:28 +0200 Lieven, try: CREATE TABLE owner ( id INT NOT NULL AUTO_INCREMENT parent_id INTEGER, owner VARCHAR(25), PRIMARY KEY (id), INDEX parent_fk (parent_id), FOREIGN KEY (parent_id) REFERENCES folder(folder_id) ON UPDATE CASCADE ON DELETE CASCADE, INDEX owner_fk (owner), FOREIGN KEY (owner) REFERENCES account(username) ON UPDATE CASCADE ON DELETE CASCADE ) TYPE=INNODB; I can't say if there are any other errors, but you have to create an index for each foreign key constraint you create (in the order they appear in your create table statement). The extra column (id) is there because i'm not sure if a primary key can be foreign at the same time in mysql. greetz, Stijn Verholen Lieven De Keyzer wrote: This is a script I'm trying to execute. My mysql version is 4.1.10. SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS owner; DROP TABLE IF EXISTS folder; DROP TABLE IF EXISTS account; DROP TABLE IF EXISTS role; CREATE TABLE role ( role_id INTEGER NOT NULL, rolename VARCHAR(25) NOT NULL, PRIMARY KEY (role_id)) TYPE = InnoDB; CREATE TABLE account ( username VARCHAR(25) NOT NULL, password VARCHAR(80) NOT NULL, email VARCHAR(80) NOT NULL, first_name VARCHAR(80) NOT NULL, last_name VARCHAR(80) NOT NULL, role_id INTEGER NOT NULL, PRIMARY KEY (username)) TYPE = InnoDB; CREATE TABLE folder ( folder_id INTEGER NOT NULL AUTO_INCREMENT, parent_id INTEGER, foldername VARCHAR(80), PRIMARY KEY (folder_id)) TYPE = InnoDB; CREATE TABLE owner ( parent_id INTEGER NOT NULL, owner VARCHAR(25) NOT NULL, PRIMARY KEY (parent_id), FOREIGN KEY (parent_id) REFERENCES folder(folder_id) ON DELETE CASCADE, FOREIGN KEY (owner) REFERENCES account(username) ON DELETE CASCADE) TYPE = InnoDB; And this is the output: Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) ERROR 1005 (0): Can't create table './lddekeyz/owner.frm' (errno: 150) It seems like the last FOREIGN KEY of the 'owner' table is not correctly formed: $perror 150 MySQL error: 150 = Foreign key constraint is incorrectly formed But I can't see anything wrong with it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
running a system command from mysql 3.23
hi.. is there a way to run system commands from inside mysql if i'm using mysql3.23 (linux). in mysql-4, i simply do a 'system'... this doesn't work in 3.23... thanks bruce [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Complex Query
Hello,
I have a search query to build on two table below is the table structure, and I
need to build a query to search on specific cities(or all cities),specific
subjects(or all subjects) and specific levels(or all levels)
# Table structure for tt_jobs and tt_jobbids
# tt_jobs
pid int(6) unsigned NOT NULL auto_increment,
ttid int(6) unsigned NOT NULL default '0',
jobid varchar(10) NOT NULL default '',
title varchar(60) NOT NULL default '',
subjects smallint(4) NOT NULL default '0',
levels smallint(4) NOT NULL default '0',
cities smallint(4) NOT NULL default '0',
location varchar(250) NOT NULL default '',
day_required varchar(4) NOT NULL default '',
tuition_place tinyint(2) NOT NULL default '0',
duration smallint(4) NOT NULL default '0',
students varchar(6) NOT NULL default '',
tuition_description text,
status enum('open','closed','tempclosed') NOT NULL default 'open',
PRIMARY KEY (pid),
KEY ttid (ttid),
KEY searchidx (cities,levels,subjects)
# tt_jobbids
pid int(6) unsigned NOT NULL default '0',
ttid int(6) unsigned NOT NULL default '0',
bidamt decimal(4,2) NOT NULL default '0.00',
bidtime varchar(10) NOT NULL default '',
bidstatus enum('pending','tutoraccept','tuteeaccept','reject') NOT NULL
default 'pending',
bidexpires varchar(20) NOT NULL default '',
KEY pid (pid),
KEY ttid (ttid),
KEY bidexpires (bidexpires)
Now the search can be on all cities,
levels and subjects which is no problem, it just extracts all open jobs.
# Query to display all open jobs and works fine
SELECT js.pid,js.title,js.subjects,js.levels,js.cities,COUNT(jb.ttid) AS
totalbid FROM tt_jobs js LEFT JOIN tt_jobbids jb ON js.pid = jb.pid
WHERE js.status = 'open' GROUP BY js.pid ORDER BY js.pid DESC
Now I need to be able to perform a search on these two tables but with
specific queries on cities, and subjects and levels. The cities,subjects and
levels columns data are all numerical values(IE 1 - 100).
Now when the search is submitted it can be performed in various ways
# (X = a numerical value)
all cities and subjects = X and levels = X
all subjects and all cites and subject = X
all cities and level = X and subject = X
all levels and all subjects and cities = X
subject = X and cities = X and all levels
subject = X and cities = X and levels = X
And so on, in various combinations. And I haven't come up with
a workable solution to do this. And was hoping someone could suggest
a feasible query to do this or do I need to restructure the tables that
are being searched on??
Hope this is clear :)
TIA,
--
Mike(mickalo)Blezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://thunder-rain.com/
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
proper varchar size for uuid storage
i see a lot of variation out there on this subject. some people are using varchar(64), some varchar(32), and some other non-base2 widths. what is the proper width? i count 36 characters below, including the four dashes. mysql select uuid(); +--+ | uuid() | +--+ | 8ce826a8-2122-1028-88b7-09e76b61a517 | +--+ 1 row in set (0.00 sec) are there times when the uuid() function produces output of a longer or shorter length than 36 characters? thanks. - philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
exporting table structure/data in mysql?
what's the sql syntax (in mysql) for exporting a table's structure and and data (the one that reiterates the original CREATE TABLE statement)? ..so that i'll end up with something that can be put in a .sql script for later imports if needed. thanks. - philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: exporting table structure/data in mysql?
At 20:19 -0500 5/28/05, Philip George wrote: what's the sql syntax (in mysql) for exporting a table's structure and and data (the one that reiterates the original CREATE TABLE statement)? ..so that i'll end up with something that can be put in a .sql script for later imports if needed. SHOW CREATE TABLE tbl_name From the command line: mysqldump --no-data db_name [tbl_name] ... -- 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: RESOLVED : exporting table structure/data in mysql?
this is what i was looking for: SHOW CREATE TABLE tablename; for some reason it isn't on the main doc page. - philip On May 28, 2005, at 8:19 PM, Philip George wrote: what's the sql syntax (in mysql) for exporting a table's structure and and data (the one that reiterates the original CREATE TABLE statement)? ..so that i'll end up with something that can be put in a .sql script for later imports if needed. thanks. - philip -- 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: exporting table structure/data in mysql?
thanks. - philip On May 28, 2005, at 8:25 PM, Paul DuBois wrote: At 20:19 -0500 5/28/05, Philip George wrote: what's the sql syntax (in mysql) for exporting a table's structure and and data (the one that reiterates the original CREATE TABLE statement)? ..so that i'll end up with something that can be put in a .sql script for later imports if needed. SHOW CREATE TABLE tbl_name From the command line: mysqldump --no-data db_name [tbl_name] ... -- 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql and php
I'm on Mac OSX and Mysql is built-in, so is php. My php has built-in support for mysql. Both php and mysql are up and running. I have tested both. All this is running on this machine, so is Apache. I can log into mysql at the CLI with my superuser and access the 'mysql' db -the only one I currently have. I have a good php test connect script but cannot connect to mysql. I have checked the mysql variables and entered the socket location into the script, no help. What else can possibly be wrong? Is there something in a mysql config file or something that is off? This is an upgrade install of Mac OSX 10.4 Server over 10.3 Server (I'm not a wiz) but have used it long enough. I have changed nothing and it worked fine before. Any suggestions? Again, I did not build this it's all built-in and working fine. I simply can't connect with php. Separately, mysql and php both work. Thanks, Gil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RESOLVED : exporting table structure/data in mysql?
At 20:26 -0500 5/28/05, Philip George wrote: this is what i was looking for: SHOW CREATE TABLE tablename; for some reason it isn't on the main doc page. - philip http://dev.mysql.com/doc/mysql/en/show-create-table.html On May 28, 2005, at 8:19 PM, Philip George wrote: what's the sql syntax (in mysql) for exporting a table's structure and and data (the one that reiterates the original CREATE TABLE statement)? ..so that i'll end up with something that can be put in a .sql script for later imports if needed. thanks. - philip -- 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: RESOLVED : exporting table structure/data in mysql?
ya, i just meant when you go to the main table of contents page for the docs: http://dev.mysql.com/doc/mysql/en/index.html ... there's no mention of it. there's SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION and SHOW CREATE VIEW, but no SHOW CREATE TABLE. - philip On May 28, 2005, at 8:38 PM, Paul DuBois wrote: At 20:26 -0500 5/28/05, Philip George wrote: this is what i was looking for: SHOW CREATE TABLE tablename; for some reason it isn't on the main doc page. - philip http://dev.mysql.com/doc/mysql/en/show-create-table.html On May 28, 2005, at 8:19 PM, Philip George wrote: what's the sql syntax (in mysql) for exporting a table's structure and and data (the one that reiterates the original CREATE TABLE statement)? ..so that i'll end up with something that can be put in a .sql script for later imports if needed. thanks. - philip -- 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RESOLVED : exporting table structure/data in mysql?
At 20:50 -0500 5/28/05, Philip George wrote: ya, i just meant when you go to the main table of contents page for the docs: http://dev.mysql.com/doc/mysql/en/index.html ... there's no mention of it. there's SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION and SHOW CREATE VIEW, but no SHOW CREATE TABLE. It appears deeper in the TOC than the other statements. The main page TOC goes only to three levels. (Otherwise it'd be a mile long.) - philip On May 28, 2005, at 8:38 PM, Paul DuBois wrote: At 20:26 -0500 5/28/05, Philip George wrote: this is what i was looking for: SHOW CREATE TABLE tablename; for some reason it isn't on the main doc page. - philip http://dev.mysql.com/doc/mysql/en/show-create-table.html On May 28, 2005, at 8:19 PM, Philip George wrote: what's the sql syntax (in mysql) for exporting a table's structure and and data (the one that reiterates the original CREATE TABLE statement)? ..so that i'll end up with something that can be put in a .sql script for later imports if needed. thanks. - philip -- 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: performance on single column index with few distinct values
Is there a composite index on (master_id, ticket_id)? Since your queries are selecting on a particular master_id, and ordering by ticket_id, along with the limit I think MySQL would be able to use such an index in an optimization. -Daniel -Original Message- From: Terence [mailto:[EMAIL PROTECTED] Sent: Friday, May 27, 2005 10:52 PM To: mysql@lists.mysql.com Subject: performance on single column index with few distinct values Hi list, I have run into problems on a master table for our helpdesk. We have the following table: ticket_id (int) - autoincrement (indexed) master_id (int) (indexed) Master ID is used to distinguish multiple helpdesks. In this table there are 100k records, but only 10 distinct master_id's. For example: ticket_id master_id 1 1 2 1 3 2 4 2 5 3 ... ... When trying to do pagination I use the following SQL: SELECT ticket_id FROM my_table WHERE master_id = '1' ORDER BY ticket_id DESC LIMIT 0,10 The problem is that there are 20k records where master_id = 1, so the lookup is pretty slow especially when I start joining other tables. When joining other tables the query gets slower and slower, I guess because the lookups on joining tables result in fewer rows being joined when using EXPLAIN. SELECT * FROM helpdesk_tickets ht, helpdesk_category_master hcm, helpdesk_sub_category_master hscm WHERE ht.master_id = '1' AND ht.category_id = hcm.category_id AND ht.sub_category_id = hscm.sub_category_id ORDER BY ticket_id DESC LIMIT 0,10 I have thought of options such as using temporary tables to just grab the last 10 tickets and then do an IN query, however I need to display totals, so that would require me to run the query again. My questions are: 1) Is there any point to having an index on a column with so few unique values? 2) Would it make more sense to have multiple master tables for each helpdesk? Such as: helpdesk_tickets_1 helpdesk_tickets_2 helpdesk_tickets_3 etc. and then using a session value to query the table? 3) Any other tips or advice? (I notice my query time doubles from 100k rows to 150k rows) Thanks for any help... Terence -- 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: RESOLVED : exporting table structure/data in mysql?
ahhh... thanks. good to know. - philip On May 28, 2005, at 8:57 PM, Paul DuBois wrote: At 20:50 -0500 5/28/05, Philip George wrote: ya, i just meant when you go to the main table of contents page for the docs: http://dev.mysql.com/doc/mysql/en/index.html ... there's no mention of it. there's SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION and SHOW CREATE VIEW, but no SHOW CREATE TABLE. It appears deeper in the TOC than the other statements. The main page TOC goes only to three levels. (Otherwise it'd be a mile long.) - philip On May 28, 2005, at 8:38 PM, Paul DuBois wrote: At 20:26 -0500 5/28/05, Philip George wrote: this is what i was looking for: SHOW CREATE TABLE tablename; for some reason it isn't on the main doc page. - philip http://dev.mysql.com/doc/mysql/en/show-create-table.html On May 28, 2005, at 8:19 PM, Philip George wrote: what's the sql syntax (in mysql) for exporting a table's structure and and data (the one that reiterates the original CREATE TABLE statement)? ..so that i'll end up with something that can be put in a .sql script for later imports if needed. thanks. - philip -- 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
char(x) showing up as varchar(x)...
when i create a table with: CREATE TABLE person ( id char(36) NOT NULL PRIMARY KEY, firstname varchar(50) DEFAULT NULL, lastname varchar(50) DEFAULT NULL ); ...and then use describe to show what i've got: mysql describe person; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | id| varchar(36) | | PRI | | | | firstname | varchar(50) | YES | | NULL| | | lastname | varchar(50) | YES | | NULL| | +---+-+--+-+-+---+ 3 rows in set (0.00 sec) ...`id`, which is supposed to be char(36), is actually varchar(36). SHOW CREATE TABLE shows the same thing. it's not a big problem. just wondering what's going on here. i checked http://dev.mysql.com/doc/mysql/en/char.html , but found nothing related to this. i'm running version 4.1.11. thanks. - philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: char(x) showing up as varchar(x)...
http://dev.mysql.com/doc/mysql/en/silent-column-changes.html At 21:29 -0500 5/28/05, Philip George wrote: when i create a table with: CREATE TABLE person ( id char(36) NOT NULL PRIMARY KEY, firstname varchar(50) DEFAULT NULL, lastname varchar(50) DEFAULT NULL ); ...and then use describe to show what i've got: mysql describe person; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | id| varchar(36) | | PRI | | | | firstname | varchar(50) | YES | | NULL| | | lastname | varchar(50) | YES | | NULL| | +---+-+--+-+-+---+ 3 rows in set (0.00 sec) ...`id`, which is supposed to be char(36), is actually varchar(36). SHOW CREATE TABLE shows the same thing. it's not a big problem. just wondering what's going on here. i checked http://dev.mysql.com/doc/mysql/en/char.html , but found nothing related to this. i'm running version 4.1.11. -- 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: char(x) showing up as varchar(x)...
thanks. - philip On May 28, 2005, at 10:08 PM, Paul DuBois wrote: http://dev.mysql.com/doc/mysql/en/silent-column-changes.html At 21:29 -0500 5/28/05, Philip George wrote: when i create a table with: CREATE TABLE person ( id char(36) NOT NULL PRIMARY KEY, firstname varchar(50) DEFAULT NULL, lastname varchar(50) DEFAULT NULL ); ...and then use describe to show what i've got: mysql describe person; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | id| varchar(36) | | PRI | | | | firstname | varchar(50) | YES | | NULL| | | lastname | varchar(50) | YES | | NULL| | +---+-+--+-+-+---+ 3 rows in set (0.00 sec) ...`id`, which is supposed to be char(36), is actually varchar(36). SHOW CREATE TABLE shows the same thing. it's not a big problem. just wondering what's going on here. i checked http://dev.mysql.com/doc/mysql/en/char.html , but found nothing related to this. i'm running version 4.1.11. -- 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql and php
From your first message below, are you suggesting a setting in Apache needs to be ticked? I can do it if I know where to look. I am no expert, just one who uses tis setup, i.e., it just works. I have used the previous version for years and it just worked. That's why I have no idea what could possibly be different. Not to say I haven't built these before, but this should be working! :( What's led me to this problem is that I have phpmyadmin installed and it can't access mysql as it could just fine before. I have had help from the guys at phpmyadmin who have no sent me here. No one gets it. So, any specific ideas where to look??? Thanks, Gil p.s. I don't know what you mean by bounce the MYSQL DB. On May 28, 2005, at 9:47 PM, sol beach wrote: I don't do MACs, but here is a shot in the dark. In some/many cases Apache gets invoke as user nobody; which would be the the OS user that needs to be granted access to MYSQL. I'm willing to bet that the failure to connect into MYSQL via PHP from Apache is due to a permissions/login/authorization issue. You also wrote: If you enable LOGGING bounce the MYSQL DB, some clue about what is or is not happening to MYSQL will be written to the logfile. Discern what the clues are reporting and fix the problem. HTH YMMV On 5/28/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I'm on Mac OSX and Mysql is built-in, so is php. My php has built-in support for mysql. Both php and mysql are up and running. I have tested both. All this is running on this machine, so is Apache. I can log into mysql at the CLI with my superuser and access the 'mysql' db -the only one I currently have. I have a good php test connect script but cannot connect to mysql. I have checked the mysql variables and entered the socket location into the script, no help. What else can possibly be wrong? Is there something in a mysql config file or something that is off? This is an upgrade install of Mac OSX 10.4 Server over 10.3 Server (I'm not a wiz) but have used it long enough. I have changed nothing and it worked fine before. Any suggestions? Again, I did not build this it's all built-in and working fine. I simply can't connect with php. Separately, mysql and php both work. Thanks, Gil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
basic sql join question...
is it okay to post a basic sql join question to this list? if not, please point me to a list for these types of questions. thanks. - philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
udpate / subquery
UPDATE account SET role_id = (SELECT role_id FROM role WHERE rolename = admin) WHERE username = test This gives me an: ERROR 1064 (0): 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 'SELECT role_id from role WHERE rolename=admin)' at line 1 I can't see anything wrong with the syntax? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic sql join question...
guess i'll just ask: here are the 2 tables of interest: mysql select * from ticket_details; +-- +--+--+ | ticket | product | quantity | +-- +--+--+ | 9f2d7b86-213d-1028-88b7-09e76b61a517 | 85d0d5bc-213c-1028-88b7-09e76b61a517 |1 | | 9f2d7b86-213d-1028-88b7-09e76b61a517 | ad67557e-213c-1028-88b7-09e76b61a517 |3 | | ec04c91e-2142-1028-88b7-09e76b61a517 | 60e766f8-213c-1028-88b7-09e76b61a517 |7 | | ec04c91e-2142-1028-88b7-09e76b61a517 | a4341a8c-213c-1028-88b7-09e76b61a517 |2 | +-- +--+--+ mysql select * from product; +--++---+ | id | name | price | +--++---+ | 60e766f8-213c-1028-88b7-09e76b61a517 | banana | 1.98 | | 85d0d5bc-213c-1028-88b7-09e76b61a517 | orange | 0.97 | | a4341a8c-213c-1028-88b7-09e76b61a517 | apple | 0.89 | | ad67557e-213c-1028-88b7-09e76b61a517 | pear | 1.09 | +--++---+ here's a functional join that retrieves some specifics from a given ticket #: select ticket_details.quantity, product.name, product.price, (product.price * ticket_details.quantity) as subtotal fromproduct, ticket_details where ticket_details.ticket = '9f2d7b86-213d-1028-88b7-09e76b61a517' AND ticket_details.product = product.id ; +--++---+--+ | quantity | name | price | subtotal | +--++---+--+ |1 | orange | 0.97 | 0.97 | |3 | pear | 1.09 | 3.27 | +--++---+--+ how can i also show a grand total for the ticket (without changing the table structure)? i've tried with no success to use SUM() to do this. would i even use SUM()? if i could refer to the resultant table above in a subsequent select, that would be ideal. is there a way to do that (something like select SUM(@@result.subtotal);)? thanks. - philip On May 28, 2005, at 11:36 PM, Philip George wrote: is it okay to post a basic sql join question to this list? if not, please point me to a list for these types of questions. thanks. - philip -- 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: udpate / subquery
Syntax looks fine to me. Sub-queries are only supported in 4.1.x+ , I'm guessing that's your problem. Chris Lieven De Keyzer wrote: UPDATE account SET role_id = (SELECT role_id FROM role WHERE rolename = admin) WHERE username = test This gives me an: ERROR 1064 (0): 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 'SELECT role_id from role WHERE rolename=admin)' at line 1 I can't see anything wrong with the syntax? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update / subquery
I've got mysql-4.1.10 installed. From: Chris [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: udpate / subquery Date: Sat, 28 May 2005 22:31:19 -0700 Syntax looks fine to me. Sub-queries are only supported in 4.1.x+ , I'm guessing that's your problem. Chris Lieven De Keyzer wrote: UPDATE account SET role_id = (SELECT role_id FROM role WHERE rolename = admin) WHERE username = test This gives me an: ERROR 1064 (0): 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 'SELECT role_id from role WHERE rolename=admin)' at line 1 I can't see anything wrong with the syntax? -- 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]
