At some point my MAMP installation became corrupt and I decided to move to XAMPP instead. However I now can't get my databases across. Other people have said I simply need to copy the DB files to XAMPP but this only partially work. I can see the databases however all the tables have message "in use" and if I try to query a table I get ...
Ok here is the situation (using PHP/MySQL) you are getting results from a large mysql table, lets say your mysql query returns 10,000 matching results and you have a paging script to show 20 results per page, your query might look like this So page 1 query SELECT column FROM table_name WHERE userId=1 AND somethingelse=something else...
I am using the following MySQL query in a PHP script on a database that contains over 300,000,000 (yes, three hundred million) rows. I know that it is extremely resource intensive and it takes ages to run this one query. Does anyone know how I can either optimise the query or get the information in another way that's quicker? I need to ...
Hi, I have two queries to get related tags from a mysql database, one works, one does not, my question is: "why?" Problem: When executing the first query, the mysql server gets 100% cpu usage, and has to be restarted to function again. Query 1 (does not work): SELECT tags.*, COUNT(ct.company_id) AS count FROM company2tag ct, tags WHE...
I'm trying to add some tables and views from a MySQL database to a project in VS2005. After a lot of looking around I ended using the ADO.NET connector, which works pretty fine, except when trying to add a View with the Data source configuration wizard, where I get the "fatal error encountered during command execution" error message, and...
Hey everyone, I'm quite frustrated. I want to be able to insert into my database names with single quotes - for example, O'Connor. So, when inserting into the DB, I do: $lname = mysql_real_escape_string($_POST['lname']); And then I insert $lname into the DB. When it's in the DB, it appears as O\'Connor. So, if I were to recall th...
When changing the content of an InnoDB MySQL table the following files on the file system appears to be involved/changed: /path/to/mysql/data/[database]/[table].ibd (due to innodb_file_per_table) /path/to/mysql/data/data/ib_logfile0 /path/to/mysql/data/data/ib_logfile1 /path/to/mysql/data/data/ibdata1 For each of these files: When ...
How secure is this MySQL statement built in a PHP? Would it be vulnerable to an SQL injection? $sql = sprintf("INSERT IGNORE INTO my_table VALUES(%d, %d, 1, NOW())", mysql_escape_string($_SESSION['client']['id']), mysql_escape_string($_POST['id'])); ...
Hey all, I've got a query in need of optimizing. It works but its a dog, performance wise. It reads like this: SELECT * FROM ( SELECT * FROM views WHERE user_id = '1' ORDER BY page DESC ) v GROUP BY v.session I'm tracking views to different pages, and I want to...
Hi all, Here's what I'm trying to do: Update an arbitrary number of rows, changing several fields to the SAME value. $var = "1 3 5 7 9"; $query = "UPDATE tablename SET seen_before = 1 WHERE id = SET ($var);" My question is this: What is the exact syntax on the Where clause when I lookin in a set, and how do I pass in the Set ite...
I'm gathering a vector of time/value pairs, in this case I/O latency versus time for a server. I'd like to store them in a MySQL database that is queryable by date, server and other metadata. Examples: I want be able to query the I/O latencies from 1:00 PM to 3:00 PM for the server Atriedes on August 19th, 2007. I want to also be able...
In MySQL, is it generally faster/more efficient/scalable to return 100 rows with 3 columns, or 1 row with 100 columns? In other words, when storing many key => value pairs related to a record, is it better to store each key => value pair in a separate row with with the record_id as a key, or to have one row per record_id with a column f...
Hi folks! Can somebody give a hint on this one? : I have a table, let's say tblA, where I have id1 and id2 as columns and index(id1,id2). I want to select the id1´s where id2´s belong to several sets. So I would want to say select id1 from tblA where id2 in (val1,val2,val3 ...) union select id1 from tblA where id2 in (val4,val2,val3...
I would like to update a table in mySql with data from another table. I have two tables "people" and "business". The people table is linked to the business table by a column called "business_id". The necessary table structure, primary key is starred (Table: columns): People: *business_id, *sort_order, email Business: *business_id, em...
I have two tables in a MySQL database, courses and sessions. I'm trying to summarise the entries in sessions for each course each month. I can summarise the total sessions for each course, no problem using this query: SELECT courses.CourseID, SUM(IF( sessions.Duration IS NULL , 0, sessions.Duration)) AS Hrs FROM courses ...
Hey everyone, I am using MySQL 5.0 for a site that is hosted by GoDaddy (linux). I was doing some testing on my web app, and suddenly I noticed that the pages were refreshing really slowly. Finally, after a long wait, I got to a page that said something along the lines of "MySQL Error, Too many connections...", and it pointed to my co...
I am writing a simple bash script to install MySQL on Ubuntu. #!/bin/bash apt-get update # Install MySQL5 aptitude -y install mysql-server mysql-client libmysqlclient15-dev However MySQL prompts for a password and confirmation. How do I pass along a root password. Is there an echo I can use? Thank you, Cei ...
What's the equivalent of escaping strings for MySql database input in perl? Is Quote the best way? ...
What does adding ranked to a mysql query do? I'm trying code from this post SELECT * FROM ( SELECT @row := @row +1 AS rownum, [column name] FROM ( SELECT @row :=0) r, [table name] ) ranked WHERE rownum % [n] = 1 ...
The problem is this, when I add two or more doubles from a table to a view, instead of giving me the right results, it adds a about ten or so more digits. For example 0.5+1.5=1.99999999998 or 5.5+8.5=14.0000000001. Any ideas? (I know this is sort of n00b question and I remember having to deal with stuff like that in the exams at 9th grad...