Using IF() For Choosing The Query Fails
The following code is for the Archive. (Where users can store special messages)
I want to receive one row for each message that has been sent OR that has been received.
With the code beneath I get this error: "#1241 - Operand should contain 1 column(s)". How can I fix this?
INFO AND CODE ......
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Query Fails In MySQL 5
I just migrated a Mambo CMS site from a server runing MySQL 4 something to one running MySQL 5.0 and am getting one problem (was expecting many more) with a query generated in the admin section: SELECT c . * , g.name AS groupname, cc.name, u.name AS editor, f.content_id AS frontpage, s.title AS section_name FROM mos_content AS c, mos_categories AS cc, mos_sections AS s LEFT JOIN mos_users AS u ON u.id = c.checked_out LEFT JOIN mos_groups AS g ON g.id = c.access LEFT JOIN mos_content_frontpage AS f ON f.content_id = c.id WHERE c.state >=0 AND c.catid = cc.id AND cc.section = s.id AND s.scope = 'content' AND c.sectionid = ཊ' ORDER BY cc.ordering, cc.title, c.ordering LIMIT 0 , 10 MySQL said: Documentation #1054 - Unknown column 'c.checked_out' in 'on clause' The problem appears to be with the fact that the table mos_content is aliased as c and the the SELECT uses a wilcard to select all its columns. For some reason, when the ON clause test the value in any of the colums using the alias, it says they are onknown.
Mysql Query Fails Due To A ' In Data.
I am having problems now with the following code. An example of my topic. "we'll meet again" But when the following line searches for it i get an erro saying that the code is wrong. I know that it is to do with the ' , but how do Iget round this problem i have. without having to remove all of them from the data. $topics = mysql_query("SELECT * FROM `forum_topics` WHERE `topic` = '" . $topic . "'") or die(mysql_error());
Cross Table Query Fails
I get this error which I can't quite understand....PHP Code: 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 x10_channel from devices where device = 'Lamp')' at line The line mentioned involves a cross table query:PHP Code: $sql = "select commandID from commands where x10_channel = (select x10_channel from devices where device = '$device')"; which works perfectly on a friends laptop. I'm guessing there is a configuration difference between our installations.
Insert Data Into 2 Tables With 1 Query Fails
I found 2 old threads on how to insert data into 2 tables with 1 query. Both of them said that i should seperate the inserts with a ;. But i must be doing something wrong, because it comes up with an error like this ".....right syntax to use near ' INSERT INTO eiland_details" What am i doing wrong? It's possible, right? To insert data into multiple tables with 1 query? $query = " INSERT INTO sub_pages (mainpage_id, intro_text) VALUES ('$main_data->mainpage_id','$sub_intro'); INSERT INTO eiland_details (inwoners, oppervlakte) VALUES ('$detail_inwoners',$detail_oppervlakte')";
Update Query Fails In Previous MySQL Version
The following query runs fine on my development server (v4.0.17): "update user inner join events on user.username = events.username set user.LastMessageDate = '$current_time' where events.text_sent=0 and events.to_mobile >0 and events.GMT_event_send_time<='$send_time'" but fails on my production server running v3.23.56. Anyone any ideas why it doesn't run and what I need to do to fix it?
Choosing A Primary Key
I have a table with a field called 'lote' containing unique values like TM07082004 TT07082004 TN07082004 TM08082004 TT08082004 I'd like to know whether I should use this field as primary key for my table, or create an autonumeric field and use that instead. What are the drawbacks/advantages of each approach ?
Choosing Fields
How do you choose fields in MySQL where their length is greater than 5?
Choosing Index Type
I use mysql 5.0.18. I need some advice in what type of index to prefer when using MEMORY tables - BTREE or HASH? I want to put in memory several tables with number of rows from 100 throuh 20000 in each. Not a lot I guess. My memory tables contain a kind of vocabulary with common table structure like create table: ( id int, name CHAR(200), primary key (id), {key | unique key} `name` (name) ) There can be duplicates in some columns but I feel it would not be many of them. Is it possible to gain meaningful time profit from choosing one index type and not the other when selecting from such tables? (selects may contain many joins with these tables)
Choosing A Linux Distribution
I'm building up a mysql server to run our lab's internal data base, and was wondering how to go about choosing a linux distribution. Anyone have any advice as what to take into consideration, or have any specific advice? Also, is there any big disadvantage to just running the server on an XP box? The server itself will run on an AMD 64 processor.
Choosing A Particular Row From Grouped Rows
I have a table which contains two strings and a date. page_views ----------------- sessionId page timestamp This is added to each time someone views a page on our site. I want to select the last page each user looked at, so I need to group on the sessionId whilst getting it to select the one with the highest date timestamp.
Choosing The Right Local Server
i seem to have a problem with connecting to the database on everything i try! At the moment I am trying to connect moodle to my MySQL database. I want the MySQL host to be pcol-webtest - as this is the server host - not locahost at all. How do I change the host, do I have to set the database data directory onto that host? Through MySQL Administrator its telling me the following: Connected to MySQL Server Instance Username: mooldeadmin (set this in command line) Hostname: localhost Port: 3306 Server Info MySQL version: MySQL 4.1.10-nt via TCP/IP Network Name: pcol-webtest IP: 212.219.110.235 OS: Windows 2k
Choosing Storage Engine
I am setting up a database and I was wondering what storage engine to use, MyIsam or InnoDB. First of all I am looking for the most trustworthy solution. I have been searching the forum, but I could not find a definitive answer. I did find that MyIsam is faster than InnoDB, but InnoDB provides better data integrity and would even be more robust. I do not need full-text searching. I foresee to access the database using PHP/Apache. InnoDB would be the best way to go then, right? Second question, if it was not for full-text searching, why would anyone choose for MyIsam, is it really that faster?
Comparing Dates And Choosing
if( date_add(cur_date(), interval 30 day)>='$thisDate','$thisDate',date_add(cur_date(),interval 30 day) So: if the current date plus 30 days is larger then thisDate, use thisDate else use the current date plus 30 days.
On Choosing The Right Type For A Column
The online MySql manual gives advice on column types for the most efficient use of storage, but I can't find any advice on column types for the most efficient use of computer cycles. I know on some machines it may be faster always to use (say) fixed length strings and unsigned integers, even if these types take up more storage than is strictly needed. Does any reader of this ng have an informed opinion on this matter?
Choosing Correct MYSQL
I am setting up tables to store data sent from FCKeditor. What type of column am I best to use for storing the html code?
Choosing A Version Of MySQL For Local Web Server
In the process of setting up a localhost web server to test things before I put them on my shared host server. Mostly I'm just dabbling - not making a living at it. My server is Apache 2.2.3 with PHP 5.2.0 on WinXP-Pro. What version of MySQL is most compatible with these versions of server software? Can someone point me to an installation how-to doc? Would also appreciate pointers to pages I should read to familiarize myself with MySQL. I've dabbled with dbs's as standalone apps but never used one on a server before.
Fails Normalization?
I have a massive table with around 10 million records. Probably 50% of these records have redundant information. For example maybe 1 column out of 15 is different between 2 or 3 different records. Note: the address is divided into pieces in the actual database, but I wrote it as one field in this example. ID...Name...Address....Cat....Lat..........Long... 123 ABC 123 Main St 45 3387287 -12077273 124 ABC 123 Main St 87 3387287 -12077273 125 ABC 123 Main St 64 3387287 -12077273 etc I'm thinking that if I could collect all these Cat values and put them into 1 single column, maybe seperated by semicolons or something, that I could improve the performance of queries on the data. ID...Name...Address.........Cat........Lat..........Long... 123 ABC 123 Main St 45;87;64 3387287 -12077273 etc The main problems I forsee with this method is if my user is searching for Category 64, I'll have to use a LIKE '%64%' statement or something inefficient. Another possible solution might be to separate the data into normalized tables such as this: AddressTable: ID...Name...Address.......Lat..........Long... 123 ABC 123 Main St 3387287 -12077273 CatsTable: ID...AddID...Cat 1 123 45 2 123 87 3 123 64 I'm afraid that if I have a 5 million row AddressTable and a 10 million row CatsTable that running constant joins would bog down the system.
Update Sql Fails For 4.0.26
I executed the following sql commands on 4.0.26(linux) drop table gtest; create table gtest(id int default 116, name varchar(20) default 'peter'); insert into gtest(id, name) values(100, default); select * from gtest; +------+-------+ | id | name | +------+-------+ | 100 | peter | +------+-------+ 1 row in set (0.00 sec) Then i tried the following update gtest set id=default; update gtest set name=default; I get the following error ERROR 1064: 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 'default' at line 1 I looked up the docs and its a SQL stmt parse error.
Update Fails
I'll put this very simply, will give my current code if necessary (first trying to explain it here I came to an conclusion I'd better erase it and only ask..) I have a script, which by using mysql_affected rows, checks if UPDATE query was used to update already existing data. If that fails, INSERT query will create that data. However Mysql only returns affected rows as true, if the old data is different than the new one. What I'm asking here is - Which do you think is the best way to update - or if nothing to update - insert new data to sql. I guess REPLACE wont work when it should be done by id fields?
Replication Fails
we want to set up a replication server to distinguish between reads and writes where one database server is for reads (m1), and the other is for writes (m2) m1 will replicate the data from m2 so that when we serve pages, users only connect to the reads database server now, what we need is a system whereby we are notified if replication stops for whatever reason so that we can switch reads to the write server temporarily or restart the slave does anyone know of an app or way to get alerted as soon as replication stops?
DNS Lookup Fails
I have a mysql server that is set up in a subdomain. I also have all my permissions set to restrict users from this domin (i.e.) 'user'@'%.sub.domain.com'. On ocassion the DNS lookup seems to fail, and the IP doesn't from which the user is connecting to doesn't get resolved to a DNS name. In such cases, users can not connect because the domain name is no longer being used as the host, but the ip. The mysql server is running on windows. I don't beleive this to be an error with mysql, but i'm wondering if there is some kind of mysql.hosts file in which i can enter the ip's and respective DNS entries for known connecting computers? I'm just fishing here, maybe there is another work around for this, but i want to avoid having to make duplicate entries for each user: one for the DNS and one for the IP. These occurences are few and far between, but most other windows apps don't seem to be to affected by this, i suppose because of the way it caches domain names, but that is just a guess.
Restore From Sql Dump Fails
I have just been given laptop to configure with mysql and I have installed my usual proven 5.0.27. When I try to re-install DBs from sql dumps, it only restores very small files and stops after a few thousands lines when attempting the bigger sql files. I have therefore updated to the latest 5.0.45 but I am still having the same issue. Has anybody experience something similar on windoz?
Insert Fails On Access
I have a database with about ten tables. I'm trying to fill one table with some dummy data (its a contact manager table holding names of people, addresses and whatever but I'm createing random values for the moment). The insert fails telling me that access is denied - however I use the exact same username/password/database name/host name on the other tables, and the insert and updates work just fine. I know this sounds odd - but is there any other reason as to why I might get the access denied message, other than what I assume should be just for an invalid username/password? Code:
Setting Up Password Fails
Having a problem setting up a password for MySQL The reported problem when attempting to set up a root password is; C:mysqlin>mysqladmin version mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to MySQL server on 'localhost' (10061)' Check that mysqld is running on localhost and that the port is 3306. You can check this by doing 'telnet localhost 3306' When checking this: C:mysqlin>telnet localhost 3306 Connecting To localhost...Could not open connection to the host, on port 3306: C onnect failed
ON UPDATE CASCADE Fails
I have a table which has a foreign key relationship with itself. I want and expect my updates to cascade (deletes definitely cascade as expected) but instead I just get error 1217: foriegn key error. I have written example code to use in reproducing the problem: Code:
Delete Foreign Key Fails
I have 2 tables (create is below). table 1 is a "person". A person has a "status". So, status table contains, for example, "dead", "alive", "mutant", etc etc... Now, the question is - why cant I delete from table "person"? I get this message: "Cannot delete or update a parent row: a foreign key constraint fails" Now, I think the "person" is the child table, and the "status" the parent. I only want to delete the child (a person record),and leave the "parent" or lookup value there. I hope you can help, many thanks! Here are the create tables: CREATE TABLE `status` ( `status_id` int(10) unsigned NOT NULL auto_increment, `description` varchar(40) default NULL, PRIMARY KEY (`status_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 3072 kB' CREATE TABLE `person` ( `person_id` int(10) unsigned NOT NULL auto_increment, `MembershipNumber` varchar(32) default NULL, `Title` varchar(10) default NULL code...
Setting Password Fails
The reported problem when attempting to set up a root password is; C:mysqlin>mysqladmin version mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to MySQL server on 'localhost' (10061)' Check that mysqld is running on localhost and that the port is 3306. You can check this by doing 'telnet localhost 3306' When checking this: C:mysqlin>telnet localhost 3306 Connecting To localhost...Could not open connection to the host, on port 3306: C onnect failed.
Database Connection Fails
I've been having problems with my CGI script, it overloads the server. I think it's because the database connection fails because the following error is logged whenever the server overloads. DBI->connect(server=localhost;database=Employee) failed: at db.pm line 53 db.cgi:: Could not connect to database db.cgi:: No database connection exists db.cgi:: No database connection exists And I use these settings to run mysqld. /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking I read some documentation that skip-external-locking should be used instead of skip-locking, but that seems like a minor thing. I'm not entirely sure it is the database connections, I'm just trying to eliminate possible causes.
Mysql Fulltext Search... Fails???
Something seems to be wrong. See, I make query: MySQL SELECT DISTINCT g.GameID, g.Title, p.FullName PlatformFROM game gINNER JOIN platform p ON p.PlatformID = g.PlatformIDWHERE g.`Title` LIKE '%age%' I get results like: res.txt That's OK. And when I make query: MySQL SELECT DISTINCT g.GameID, g.Title, p.FullName PlatformFROM game gINNER JOIN platform p ON p.PlatformID = g.PlatformIDWHERE MATCH(g.`Title`) AGAINST('age') OR MySQL SELECT DISTINCT g.GameID, g.Title, p.FullName PlatformFROM game gINNER JOIN platform p ON p.PlatformID = g.PlatformIDWHERE MATCH(g.`Title`) AGAINST('age' IN BOOLEAN MODE) It gives me NOTHING! Empty set and that's all... What's wrong???
PHP Mail() Function Fails Due To SQL Errors
I'm able to write to my database, but something is going wrong with SELECT queries. I'm trying to post MySQL data by email. I set an error message to display if mail() failed, and I got SQL error messages. I got a lot of messages like: Error 1054 : Unknown column 'address_name' in 'field list' Not knowing what else to do, I deleted each line of my SET list include file that had fields that yielded this message one by one. Finally, I got a null error message: Error 0:
Mysql 4.x Week Function Fails
I think there is a bug in mysql 4: select week("2003-08-15") from po.orders limit 1; produces: 32 after: set session default_week_format=3; it produces: 33 (which is correct in the Netherlands) The documentation states: set global default_week_format=3; (As root!) This command should set the default_week_format. This command fails: mysql> set global default_week_format=3; Query OK, 0 rows affected (0.00 sec) mysql> select week('2003-08-14'); +--------------------+ | week('2003-08-14') | +--------------------+ | 32 | +--------------------+ 1 row in set (0.00 sec) This result is incorrect. It should be 33. If I use: mysql> set session default_week_format=3; Query OK, 0 rows affected (0.00 sec) mysql> select week('2003-08-14'); +--------------------+ | week('2003-08-14') | +--------------------+ | 33 | +--------------------+ 1 row in set (0.00 sec) This is result is correct... I use mysql 4.0.14. I also did the same test on mysql 4.1 alpha, which has the same results. Harm de Laat Informatiefabriek The Netherlands
Insering Data Into Bit Field Fails
I know there's an issue with mySQL not being able to display BIT values, but that's not the issue, it's just not inserting them. Here's some info about server / table before i explain the problem. mysql Ver 14.12 Distrib 5.0.24a, for pc-linux-gnu (i486) using readline 5.1 The field causing problems is bit(3) Right, when I do... INSERT INTO waypoints (lat_long,date,type,marker) VALUES('12313123132,1231321','1174415400','2','1'); I get the line inserted and I get a warning, as follows. "Out of range value adjusted for column 'type' at row 1" At first I assumed this was because I was entering a decimal number, but when I do an update it inserts the decimal number correctly.
Mysql_fetch_array Fails Bit Does Not Send An Error
while($n<$rows) { echo "<tr>"; $query="SELECT * FROM `survey` WHERE `id`=$id"; $data=mysql_query($query) or die ('<b>ERROR 5: unable to retrieve data from database: '.mysql_error().'. Please try again later.</b>'); $row=mysql_fetch_row($data) or die ('<b>ERROR 6: unable to fit data into an array: '.mysql_error().'. Please try again later.</b>'); //...... //code //...... $n=$n+1; } I get "ERROR 6..." thrown at me, but there is no error message from mysql to display. If it helps, I'm using mysql v4 not v5. If you need more surrounding code, just ask.
Timezone Test Fails With 4.0.18 On Linux?
I recently installed MySQL 4.0.18 on my slackware 9.1 machine (kernel 2.4.25) by compiling the source. It works fine, and all tests pass except one. The timezone test. Can someone tell me why I'm getting this error? Is there something with the TZ variable I have to set for the shell? Code:
Remote MySQL Connection Fails
I have two XP computers, with the same version of MySQL on each. I have the same database structure and data on each. Each computer has the same user name, and each MySQL database has the same user names. Locally on each, I can connect and work with the data. I can ping each computer from the other computer. My problem is that when I try to connect from one computer to the other I get the following error: Host 'XXX.XXX.XX.XX' is not allowed to connect to this MySQL server. This happens on both computers. I can not find the reason for the error.
Remote MySQL Connection Fails
I have two XP computers, with the same version of MySQL on each. I have the same database structure and data on each. Each computer has the same user name, and each MySQL database has the same user names. Locally on each, I can connect and work with the data. I can ping each computer from the other computer. My problem is that when I try to connect from one computer to the other I get the following error: Host 'XXX.XXX.XX.XX' is not allowed to connect to this MySQL server. This happens on both computers. I can not find the reason for the error.
SQL Fails On MySQL4 To MySQL5 Upgrade
I have some SQL that works perfectly in mysql4, but fails when it's run in mysql5, on exactly the same schema. I'm not an expert and can't work out what's wrong. Can anyone spot what's going on? The error message is Unknown column 'head.id' in 'on clause' The SQL is SELECT head.*, types.*, count(review.headphoneId) as reviews FROM headphone head, headphonetypes types LEFT JOIN review ON review.headphoneId = head.id WHERE types.headphoneTypeId = head.typeId GROUP BY head.id ORDER BY head.model .
Datetime Field Default Now() Fails
I have a mySQL database table with a column field datatype of datetime that I set up in the original create table statement like this: create table nnet_usermetadata ( .... nnet_record_entered datetime default 'now()' ) However, upon execution, each time a record is entered into nnet_usermetadata the results are: 00-00-0000 00:00:00 Is there a way I can always ensure that the current date and time are always entered into the datetime field?
Deamon Fails To Start In Windows
I've experienced this a couple of times on different Windows workstations and it's driving me crazy. I've installed mysql-4.0 from the installer to c:mysql. Then just for kicks I try to start it by double clicking mysqld-nt.exe. It opens a window and hangs for about 15 seconds then closes. So i opened a command prompt and ran it, same thing. I've discovered that if I put anything on the command line as a parameter it will start. Also, installation as a service is failing because it sets the service up to run "c:mysqld-nt.exe MySQL" which is the wrong location. I setup the my.cnf from those provided in the install and it still doesn't work. I put garbage characters into the my.cnf file to see if it was even reading it. It did nothing. BUT then I ran it with "mysqld-nt.exe --" and it complained about the screwed up conf file. If I remove the my.cnf file and run it with "mysqld-nt.exe --", it works. So I can't do mysqld-nt.exe log-error" because it works right then. Can I get logging without that parameter?
Select On Float-column Fails
Tried executing the following 3 SQL statements: CREATE TABLE tab (floatval FLOAT(7, 3)); INSERT INTO tab VALUES (2.400); SELECT * FROM tab WHERE floatval='2.400'; The last select doesn't return any rows in MySQL ver 4.1.9-nt. Why? If I change the value to 2.500, a row is returned.
MySQL Fails To Launch On Mac OSX Command Error
I just bought kevin Yanks "build your own database driven website using php & MySQL" and I was going step by step on the installation process for Mac OSX users. After I installed MySQL and attempted to login using the: shell%sudo/usr/local/mysql/bin/mysqld_safe command i get this error: [Denise-Womacs-Computer:~] denisewo% shell%sudo/usr/local/mysql/bin/mysqld_safe shell%sudo/usr/local/mysql/bin/mysqld_safe: Command not found.
MySQL Fails To Start After Reboot In Win2K
I get this error (code 87). I install MySQL 5.0, everything is fine. I can work with it all day, no problem, all month, no problem. I reboot ONCE, it fails to start automatically. I manually start it... FAIL AGAIN. I reinstall, all ok. I catch too much info on google and I've read like 20 pages without any solution... just the same problem.....
UPDATE Fails On Somewhat Large Data Sets
I am running into a problem with an UPDATE statement. I am using MySQL 5.0 server, and the mysql command line client that came with it. update tableA a, tableB b set a.value = b.value where a.key1 = 'foo' and a.id = b.id and a.col2 = b.col2; The tables use InnoDB as the engine. With small data sets, this works fine. However, with larger data sets, the UPDATE runs for a long time, then I get an error saying that the number of locks exceeds lock table size. In my case, the data sets in the two tables are < 5 million rows. I kept monitoring the InnoDB status, and see the number of log entries go up until finally rolling back. I saw a bug on the mysql bugsite: http://bugs.mysql.com/bug.php?id=15667 which sounds like the problem I am facing - though my data sets are not as large as the bug states. How do I overcome this issue? I was thinking about splitting the query into smaller ones, using LIMIT and doing some sort of ordering to guarantee same order of rows retrieved. Any better ideas? Maybe configuring the InnoDB differently (I read that the lock table size cannot be changed, darn.)
Solaris 8 Daemon Fails Because Unknown Option?
I guess people did not see my post from the install thread... I am really really stuck now! Does anyone work on Solaris??? Whatever option I use to start the daemon I get this: 050310 15:27:35 mysqld started 050310 15:27:35 [ERROR] /opt/mysql/bin/mysqld: unknown option '--~' Please Help Note that I am using the binaries from this site.
Cannot Delete Or Update A Parent Row: A Foreign Key Constraint Fails
I am trying to delete a record through a user interface, but when i try to delete the record i get the following message: Cannot delete or update a parent row: a foreign key constraint fails The parent table is a products table and has four child tables linked through a product id foreigh key. The table is an innoDB type.
Machine Name & IP Address Fails For Remote Admin Login
I've just set up 5.0 on both a desktop server and a laptop with an essentially default install using WinXP (brand new, naive user here!). I find that when I use the Administrator Tool that I can only log into ServerHost: "localhost" using Username: "root" and the password I made. This is made worse as I can only hit the desktop from the laptop using Remote Desktop connection. What I can't do is either use the WinXP machine name, or the IP address for the Serverhost. This becomes an issue for the laptop especially as the IP address can change (although this is accessed through a VPN, so the WinXP machine name might be used). I am quite confused by the documentation regarding servernames, bindings, hostnames, etc.
|
TRACKER
