I'm trying to create what I would call an "inclusive" search and by that, I'm not referring to "between". I have a snippet of code and it does what I need it to do but it's hacky and inefficient so I'm wanting to know if I can possibly do it with just SQL. I'm using a third party library called MysqliDb but if you want to forego phrasing your answer in that format, it's fine. if ($this->data != '') { $searchParams = explode(' ', trim($this->data)); foreach ($searchParams as $s) { $this->db->orWhere('customer_name', '%'.$s.'%', 'like'); $this->db->orWhere('customer_address', '%'.$s.'%', 'like'); $this->db->orWhere('customer_city', '%'.$s.'%', 'like'); $this->db->orWhere('customer_zip', '%'.$s.'%', 'like'); $this->db->orWhere('customer_email1', '%'.$s.'%', 'like'); } $this->db->having('customer_status', 'Active'); $this->db->having('owner_id', $this->owner_id); $binaryArray = array_fill(0, sizeof($searchParams), 0); $results = $this->db->get('tblcustomers'); $filtered = []; foreach ($results as $r) { $binaryArray = array_fill(0, sizeof($searchParams), 0); foreach ($binaryArray as $key=>$b) { if (strpos(strtolower($r['customer_name']), strtolower($searchParams[$key])) !== false || strpos(strtolower($r['customer_address']), strtolower($searchParams[$key])) !== false || strpos($r['customer_city'], strtolower($searchParams[$key])) !== false || strpos($r['customer_zip'], strtolower($searchParams[$key])) !== false || strpos($r['customer_email1'], strtolower($searchParams[$key])) !== false ) { $binaryArray[$key] = 1; } } if (!in_array(0, $binaryArray)) { $filtered[] = $r; } } } else { $this->db->where('owner_id', $this->owner_id); $this->db->having('customer_status', 'Active'); $filtered = $this->db->get('tblcustomers'); } $this->data is an input box with possible search words separated by spaces so my intent is to split the value of the input box up based on that and perform a query that **has** to include all of the parameters. By that I mean, I can easily do a query where it retrieves every row with $this->data or $this->data[1] but I want to exclude rows that don't actually have have all of the words in $this->data in one column or another. What I have does that but it uses a lot of PHP and I'd rather keep the db querying to SQL exclusively.

Descending sort type does not work with this syntax, please help. prepare("SELECT * FROM informations ORDER BY gender ASC, first_name ASC"); $holdData->execute(); $getData = $holdData->fetchAll(PDO::FETCH_ASSOC); $newData = json_encode($getData);; print_r($newData); }elseif ($compare == "last_name") { $holdData = $conn->prepare("SELECT * FROM informations ORDER BY last_name ASC"); $holdData->execute(); $getData = $holdData->fetchAll(PDO::FETCH_ASSOC); $newData = json_encode($getData);; print_r($newData); }elseif ($compare == "first_name") { $holdData = $conn->prepare("SELECT * FROM informations ORDER BY first_name ASC"); $holdData->execute(); $getData = $holdData->fetchAll(PDO::FETCH_ASSOC); $newData = json_encode($getData);; print_r($newData); }elseif ($compare == "timestamp") { $holdData = $conn->prepare("SELECT * FROM informations ORDER BY timestamp ASC"); $holdData->execute(); $getData = $holdData->fetchAll(PDO::FETCH_ASSOC); $newData = json_encode($getData);; print_r($newData); }else{ } }elseif(isset($_GET["sorttype"]) == "desc"){ if($compare == "gender"){ $holdData = $conn->prepare("SELECT * FROM informations ORDER BY gender DESC, first_name DESC"); $holdData->execute(); $getData = $holdData->fetchAll(PDO::FETCH_ASSOC); $newData = json_encode($getData);; print_r($newData); }elseif ($compare == "last_name") { $holdData = $conn->prepare("SELECT * FROM informations ORDER BY last_name DESC"); $holdData->execute(); $getData = $holdData->fetchAll(PDO::FETCH_ASSOC); $newData = json_encode($getData);; print_r($newData); }elseif ($compare == "first_name") { $holdData = $conn->prepare("SELECT * FROM informations ORDER BY first_name DESC"); $holdData->execute(); $getData = $holdData->fetchAll(PDO::FETCH_ASSOC); $newData = json_encode($getData);; print_r($newData); }elseif ($compare == "timestamp") { $holdData = $conn->prepare("SELECT * FROM informations ORDER BY timestamp DESC"); $holdData->execute(); $getData = $holdData->fetchAll(PDO::FETCH_ASSOC); $newData = json_encode($getData);; print_r($newData); }else{ } }else{ } }else{ echo json_encode(["Message"=>"No Sortfield and Sort type found!"]); echo "
"; echo json_encode(["Message"=>"Please select the sortfield and sort type!"]); }

I've got a table similar to this data: | id | username | value | |-----------|----------|-------| | 1 | bob | 46 | | 483 | alice | 90 | | 176 | sue | 3001 | | 82 | harry | 0 | | 10493 | bill | 327 | I have this query that returns me the ranking of a user based on their id SELECT username, value, rank from ( SELECT tp.username, tp.value, tp.id, @curRank := @curRank + 1 AS rank FROM table tp, (SELECT @curRank := 0) r ORDER BY tp.value DESC ) as temp WHERE id = 483; So for the above query, I would get a ranking returned of **4** for the id 483. Let's say I want to insert the following row into the table: | id | username | value | |---------------------------| | 2 | sally | 2000 | Is there any way to know what rank this row _will_ have after it is inserted, **without** actually inserting it? I.e. sally would have a rank of 2 from the above query if inserted. The reason I am curious if this is possible is that I'm attempting to insert the row into the database and only have this one transaction, rather than having to insert the row, then re-run the rank query. Thanks!

First, let me say I'm not a DBA or network admin, but have moved large scale CMS websites from host to host. But, I have never moved a pure PHP-coded database driven website from one host to another. What I have done is moved the entire site including the level above 'html' with the include folder with config.php, database.php, main.php etc. I have imported database(s) and have proper credentials for access. But, when I try to load a file from the site (ex. accounts/index.html) I just get partial code returned with no css styles or anything that controls the site. Could someone point me in a direction to be able to start to sync this site back? Like I said, getting raw output with viewing files and can't even get CSS to load site colours and styles. I'm a newb when it comes to this aspect of things and appreciate any guidance.

I need to build a database to record form data from a custom tool. This form consists of a fairly basic set of checkbox/radio button fields so the data itself doesn't present much of a problem. User Table: - userID - firstName - lastName - phone - email Form Table currently contains: - userID - question1 - question2 - question3 I'm struggling with the fact that, due to the marketing nature of this tool, the form structure and fields are almost guaranteed to change sometime in the future. There will likely be fields added/subtracted in the future. Any changes in the future are completely unknown now but could become something to the extent of: Form Table could change to: - userID - question1 - question3 - questionA - questionB Where question2 was removed and two completely new fields were added. Should each "version" of the form have its own table or just add columns to the existing table as the form changes? Are there better ways to handle this?

I got this problem when I'm doing mysql that every time that I post pictures on the blog it will say to the database that I got array on image column. it is supposed to have the image that I inserted

My *vendor* directory from Composer is in the Home directory of my Linux user account. And I am very allergic to loading files into projects with something arbitrary as the user account name in the directory-link. Preferably, I'd like the *vendor* directory much closer to the web-directory (but not in public of course). What is making me very insecure is that in most examples I see from others, is that they do have the vendor directory at a much more convenient place and closer to the public-server-root. I googled all night on how to move my vendor folder, and I didn't find anything in plain English that I could understand. I guess I am not the smartest. And Linux is moslty headaches and anger for me. And I am surprised by the outcome of my *vendor* directory location as I did what most tutorials showed. cd /var/www php -r "copy('https://getcomposer.org/installer ', 'composer-setup.php');" php -r "if (hash_file('sha384', 'composer-setup.php') === 'dac665fdc30fdd8ec78b38b9800061b4150413ff2e3b6f88543c636f7cd84f6db9189d43a81e5503cda447da73c7e5b6') { echo 'Installer verified'.PHP_EOL; } else { echo 'Installer corrupt'.PHP_EOL; unlink('composer-setup.php'); exit(1); }" php composer-setup.php php -r "unlink('composer-setup.php');" *Most likely, you want to put the composer.phar into a directory on your PATH, so you can simply call composer from any directory (Global install), using for example:* sudo mv composer.phar /usr/local/bin/composer I did look at composer.json. But I found nothing on how to re-link a vendor directory in that file, as I do not care if that file stays in the User home dir. Anyone with words of wisdome, advice, or plain old telling me what I did wrong?

I was trying to get multiple rows into a single row with dynamic columns created as per the data. I have two tables combined with foreign key. Table 1: | id | name | Invoice value | invoice_date | |----|------|---------------|--------------| | 1 | A | 5000 | 30-01-2016 | | 2 | B | 8000 | 02-05-2016 | | 3 | C | 10000 | 03-05-2016 | Table 2: | id | invoice_id | duedate | amount | percentage | |----|------------|------------|--------|------------| | 1 | 1 | 15-01-2016 | 2500 | 50% | | 2 | 1 | 30-01-2016 | 2500 | 50% | | 3 | 2 | 15-02-2016 | 8000 | 100% | | 4 | 3 | 15-05-2016 | 5000 | 50% | | 5 | 3 | 19-05-2016 | 2500 | 25% | | 6 | 3 | 25-05-2016 | 2500 | 25% | Desired output: | name | invoice_value | invoice_date | due date1 | due amount1 | due date2 | due amount2 | due date3 | due amount3 | |------|---------------|--------------|------------|-------------|------------|-------------|------------|-------------| | A | 5000 | 30-01-2016 | 15-01-2016 | 2500 | 30-01-2016 | 04-11-1906 | null | null | | B | 8000 | 02-05-2016 | 15-02-2016 | 8000 | null | null | null | null | | C | 10000 | 03-05-2016 | 15-05-2016 | 5000 | 19-05-2016 | 2500 | 19-05-2016 | 2500 | When I tried have used group-concat for the multiple columns it's giving results with comma separated. But I want as desired output. Please somebody help to solve this issue how to write a query for this. I was using the following query but it's giving results as comma separated result: SELECT T1.name,T1.invoice_value,T1.invoice_date,T1.duedate,T1.dueamount FROM ( SELECT table1.name , table1.invoice_value, table1.invoice_date, group_concat(table2.duedate1) as duedate, group_concat(table2.dueamount1) as dueamount FROM table1 LEFT JOIN table2 ON table1.id=table2.invoice_id )T1 Group By T1.id

We have a server that connects to 40+ different remote computers which are running Sybase 11.0.1.2596. Our server is hosted on an aws ec2 AMI Linux instance, and the connections are set up using FreeTDS. The server has several different command-line PHP scripts that use PDO with dblib to connect and select data. 99+% of the time everything works, but sometimes I get these errors: SQLSTATE[HY000]: General error: PDO_DBLIB: dbresults() returned FAIL SQLSTATE[HY000]: General error: 20047 TDS: unexpected token 56 (severity 1) [(null)] SQLSTATE[HY000]: General error: 20047 DBPROCESS is dead or not enabled They seem to happen randomly, and I can't figure out what is causing them or how to fix it. If I just wait, it will start working again. It also seems if I use tsql to connect it will start responding: tsql -S -U Any advice is greatly appreciated.

Currently I have a small business where I have a main product (written in PHP and MySql), several users, several companies, etc. The software occupies around 20 tables in its operation. The problem is that I want to expand the business and create a different program, but I want to unify the data. A new program (different function) will use about 30 tables, but in reality there is a "core" that are the same tables, let's say 10. Here I have two options: * Create a larger database with 20 + 30 tables, (since some were repeated) = 40 tables. * Or create different and connected databases DB_core = 10 tables DB_1 = 10 tables DB_2 = 20 tables Why using different databases? I already want to develop this second software with a different team, but I don't want to give them access to some sensitive or valuable information, tables of people, companies, etc. This data will keep it in the "core" database and only in the production server. From what I understand, users can be created with permissions by tables, but I feel that it is much easier to keep us in separate databases. Are there any significant performance implications? Is the most extensive programming worth it? PD: Sometimes, I need the programmers team manage the production server.

I have two MySQL servers running on two different machines in a local network. I am using PHP and accessing the databases via IP. No problems there. The databases have identical schemas. One of them was an import from the other machine. I want to run a single (ONE) query that updates the same table on each server simultaneously. I already have existing queries and do not want to update them extensively. Is there a simple way to achieve this? I'm looking to keep the databases in sync when an update occurs. I am also open to any other suggestions regarding other ways to keep the databases in sync.

I'm suddenly having intermittent 2003 connection errors and I can't find anything that has changed that can explain it. These servers have been running smoothly for years and suddenly two days ago these errors started showing up in my logs. The application successfully connects to and queries the database almost all the time but sporadically hangs when trying to connect after several queries in quick succession. The connection timeouts always happen at random intervals. It worked just fine before two days ago and the code hasn't changed in 4 years. Things I've tried that didn't work: - Checked all InnoDB tables for corruption - Increased the back_log value in my.cnf from 50 to 1000 (and restarted MySQL) - Increased the max_connections value in my.cnf from 100 to 200 (and restarted MySQL) - Increased net.core.somaxconn from 128 to 1024 - Increased net.ipv4.tcp_max_syn_backlog from 1024 to 8192 - Verified there are no firewall rules blocking or throttling connections - Flushed the MySQL query cache - Cleared the MySQL query cache - Restarted the application server and database server (there are two separate servers) - Asked it nicely to simply start working again My servers are EC2 instances in AWS and nothing about their configuration has been changed. There's no unusual traffic and the CPU, memory, and disk usage are well below 80%. Please help!

In this method, first I have to get sundays dates between two dates, in this case about 1 year. Then I go through the dates in a for loop and set them to the query. I use prepared statements to make it faster. //Get the first day and last day $dateInitial = strtotime('2018-08-21'); $dateFinal = strtotime('2019-08-21'); $final = array(); $sql = "SELECT id_product, product, plant_sowing, plant_production, area_planting, CONCAT(id_product,'_', weeks) AS identity FROM ( SELECT sw_sowing.id_product, pr_products.product, sw_sowing.type, YEARWEEK(:dates,3) AS weeks, SUM(sw_sowing.quantity) AS plant_sowing, SUM(IF(ROUND(DATEDIFF(TIMESTAMPADD(DAY,(6-WEEKDAY(:dates)), :dates), sw_sowing.date)/7)>=sw_sowing.weeks_prod, sw_sowing.quantity,0)) AS plant_production, ((SUM(sw_sowing.quantity))/pr_products.plant_m2) AS area_planting FROM ( SELECT MAX(id) AS id FROM sw_sowing WHERE status != 0 AND id_tenant = :id_tenant AND date db->prepare($sql); //get the sunday dates between two dates and bind the variables for ($i = $dateInitial; $i date("Y-m-d", $i), ':id_tenant' => 1 ]; $types = [ ':dates' => Column::BIND_PARAM_STR, ':id_tenant' => Column::BIND_PARAM_INT ]; $result = $this->db->executePrepared($statement, $values, $types); $final[] = $result->fetchAll(Phalcon\Db::FETCH_ASSOC); } } return $final; But despite this it is not so fast. The query lasts 10 seconds and I would like it to be faster. I have also indexed the tables. I would like some opinion on how to best optimize this query or if the way I am doing the query is not adequate. This is a question that I did before about why I use GROUP BY and MAX(id) https://stackoverflow.com/questions/52209300/get-max-ids-by-group-mysql

I have made a Wordpress plugin that writes some data to a proprietary table. The query as shown in the log is: INSERT INTO berichten_devices (device_UUID, article_id) VALUES (%s, %s) If I take this line and enter it manually via adminer in the database, replacing the first %s with 'test' and the second %s with 1: INSERT INTO berichten_devices (device_UUID, article_id) VALUES ('test', 1) The this is stored in the database without a problem. There is also another table that is written to from the plugin which works fine. The php that is responsible for storing the data is as follows: function insert_artice_read($uuid, $article_id) { error_log('$uuid:'.$uuid, 0); error_log('$article_id:'.$article_id, 0); $query = "INSERT INTO berichten_devices (device_UUID, article_id) VALUES (%s, %s)"; error_log('$query', 0); error_log($query, 0); $query = $this->wpdb->prepare($uuid, $article_id); return $this->wpdb->query($query); } The parameter $this->wpdb is defined as $this->wpdb = $wpdb; The logging that is shown is this: dcr-wordpress | [Tue Jun 25 10:45:03.363771 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584] $uuid:ABCDEF01-2345-6789-ABCD-9876543210AA dcr-wordpress | [Tue Jun 25 10:45:03.363776 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584] $article_id:1 dcr-wordpress | [Tue Jun 25 10:45:03.363781 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584] $query dcr-wordpress | [Tue Jun 25 10:45:03.363785 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584] INSERT INTO berichten_devices (device_UUID, article_id) VALUES (%s, %s) dcr-wordpress | [Tue Jun 25 10:45:03.364844 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584] 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 'ABCDEF01-2345-6789-ABCD-9876543210AA' at line 1 bij query ABCDEF01-2345-6789-ABCD-9876543210AA gemaakt door require('wp-blog-header.php'), wp, WP->main, WP->parse_request, do_action_ref_array('parse_request'), WP_Hook->do_action, WP_Hook->apply_filters, rest_api_loaded, WP_REST_Server->serve_request, WP_REST_Server->dispatch, Prop\\Inc\\Core\\RestController->article_retrieved, Prop\\Inc\\Common\\Repository->insert_artice_read dcr-wordpress | 192.168.224.1 - - [25/Jun/2019:10:45:03 +0000] "POST /wp/wp-json/prop/v1/berichten-devices HTTP/1.1" 200 802 "-" "Mozilla/5.0 (iPhone; CPU iPhone OS 12_3_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15E148" From this logging it is obvious that there is something wrong with the sql syntax, but this line seems weird to me: for the right syntax to use near 'ABCDEF01-2345-6789-ABCD-9876543210AA' This is data from the prepared statement parameters, I would expect to see something like for the right syntax to use near %s I tried to use %i instead of %s for the second parameter, just be sure, but there was no difference. This is the table definition: CREATE TABLE IF NOT EXISTS berichten_devices ( device_UUID VARCHAR(255) NOT NULL, article_id int(10) NOT NULL, date timestamp(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4), KEY dev_uuid (device_UUID) My question is basically: "What am I doing wrong?"

I'm working on a layered navigation module within Prestashop e-Commerce solution. I'm trying to optimize a SQL query which creates a temp table during execution. I think this is creating a bottleneck in MySQL. This is the query: SELECT p.*, p.id_category_default, pl.available_later, pl.description_short, pl.link_rewrite, pl.name, i.id_image, il.legend, m.name manufacturer_name, DATEDIFF(p.date_add, DATE_SUB(NOW(), INTERVAL 30 DAY)) > 0 AS new FROM ps_category_product cp LEFT JOIN ps_category c ON (c.id_category = cp.id_category) LEFT JOIN ps_product p ON p.id_product = cp.id_product LEFT JOIN ps_product_lang pl ON (pl.id_product = p.id_product) LEFT JOIN ps_image i ON (i.id_product = p.id_product AND i.cover = 1) LEFT JOIN ps_image_lang il ON (i.id_image = il.id_image AND il.id_lang = 2) LEFT JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer) WHERE p.active = 1 AND c.nleft >= 2 AND c.nright <= 29 AND c.active = 1 AND pl.id_lang = 2 AND p.id_product IN (74, 78, 130, 146, 168, 169, 178, 195, ..., 297, 302, 1986, 1987, 1988, 1993, 1999, 2000, 2001) GROUP BY p.id_product ORDER BY p.date_upd desc LIMIT 0,48 I know the temp table is created because of the GROUP BY but I don't know how to remove it. If the same process could be done with PHP instead of SQL, that could be a solution.

Recently my wordpress site is having troubles and is stopping from working due to heavy load in the mysql processes. I need to know what is causing all this traffic and how to narrow the issue so I can troubleshoot the error. I ran some commands and I am getting this results show status like '%onn%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Aborted_connects | 143 | | Connections | 17736 | | Max_used_connections | 198 | | Ssl_client_connects | 0 | | Ssl_connect_renegotiates | 0 | | Ssl_finished_connects | 0 | | Threads_connected | 6 | +--------------------------+-------+ mysqladmin status Uptime: 3711 Threads: 3 Questions: 17872313 Slow queries: 13 Opens: 344 Flush tables: 1 Open tables: 335 Queries per second avg: 4816.036 and sometimes the cpu consumption by mysql reaches the 300%

I need a simple way to load a csv file from my computer to the database (which is on a remote server). I'm trying to load the file using a php page that executes this code:

$file = $_FILES['csv']['tmp_name']; 
    $handle = fopen($file,"r"); 
    $name = $_FILES['csv']['name'];

   $import = "LOAD DATA LOCAL INFILE '" . $name .
                "' INTO TABLE temporal_load
                  FIELDS TERMINATED BY ','  
                  optionally ENCLOSED BY '\"' 
                  LINES TERMINATED BY '\\n' 
				  IGNORE 1 LINES
                  (num,ticker,company,sector,industry) ";

mysql_query($import) or die(mysql_error());

But when I select a file on the php page and try to load it, shows the error message: File 'test11-14-15.csv' not found (Errcode: 2) I already reviewed that the variable **mysql.allow_local_infile** is ON and the database connection was made this way:

mysql_connect(HOST,USER,PASSWORD,false,128);

The file is not on the remote server, the file is on my computer, but it should work anyway because I'm using LOCAL. What am I doing wrong???

I am using "Zebra Session " as a replacement for PHP's built-in session management. Zebra Session stores sessions in a MySQL database, and uses "session locking" in order to ensure that data is correctly handled in a scenario with multiple concurrent AJAX requests. Session locking is implemented by using the SELECT GET_LOCK sql command within Zebra_Session's read function. Usually this works fine, but for the last few days these sql statements have been filling up my slow-log. Some of these statements have timed out at very inopportune moments. Here is an example statement from my slow log: # Query_time: 60.000150 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 SELECT GET_LOCK("session_p494bdabuh56tddmbv19g8i9d7", 60); I realize that if the session had been previously locked the SELECT GET LOCK command will hang until the lock is released or the command times out, but I can't think of a situation where this would actually occur. I am using InnoDB - and here is the output of SHOW ENGINE INNODB STATUS: ===================================== 150224 11:55:16 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 38 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 72097 1_second, 72077 sleeps, 7094 10_second, 1251 background, 1250 flush srv_master_thread log flush and writes: 74597 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 18608, signal count 79708 Mutex spin waits 238350, rounds 331918, OS waits 6599 RW-shared spins 33512, rounds 348663, OS waits 10791 RW-excl spins 15495, rounds 99335, OS waits 1173 Spin rounds per wait: 1.39 mutex, 10.40 RW-shared, 6.41 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 19BC0896 Purge done for trx's n:o < 19BC0836 undo n:o < 0 History list length 999 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 144709, OS thread handle 0x7fdac5fc5700, query id 2569619 localhost root show engine innodb status ---TRANSACTION 0, not started MySQL thread id 140098, OS thread handle 0x7fdac6c36700, query id 2510580 38.105.174.189 root -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 101050 OS file reads, 767519 OS file writes, 210806 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 9.37 writes/s, 3.21 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 82794, seg size 82796, 1782 merges merged operations: insert 1718, delete mark 123, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 21249871, node heap has 1239 buffer(s) 11479.22 hash searches/s, 1909.58 non-hash searches/s --- LOG --- Log sequence number 53832600944 Log flushed up to 53832600944 Last checkpoint at 53832546459 0 pending log writes, 0 pending chkp writes 159378 log i/o's done, 2.74 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 10989076480; in additional pool allocated 0 Dictionary memory allocated 1152378 Buffer pool size 655360 Free buffers 542707 Database pages 111414 Old database pages 41146 Modified db pages 58 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 65, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 100882, created 10532, written 591116 0.00 reads/s, 0.00 creates/s, 6.45 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 111414, unzip_LRU len: 0 I/O sum:cur, unzip sum:cur -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 22747, id 140577666729728, state: sleeping Number of rows inserted 415334, updated 148965, deleted 29844, read 1412355001 0.55 inserts/s, 2.05 updates/s, 0.03 deletes/s, 31796.27 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT Here is the MySQL configuration file (my.cnf): [mysql] # CLIENT # port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] # GENERAL # user = root default-storage-engine = InnoDB socket = /var/lib/mysql/mysql.sock pid-file = /var/lib/mysql/mysql.pid # SAFETY # max-allowed-packet = 16M max-connect-errors = 1000000 # DATA STORAGE # datadir = /var/lib/mysql/ # CACHES AND LIMITS # tmp-table-size = 32M max-heap-table-size = 32M query-cache-type = 1 query-cache-size = 2M query-cache-limit = 2M max-connections = 500 thread-cache-size = 50 open-files-limit = 65535 table-definition-cache = 4096 table-open-cache = 512 # INNODB # innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 256M innodb-flush-log-at-trx-commit = 1 innodb-file-per-table = 1 innodb-buffer-pool-size = 10G # LOGGING # log-error = /var/log/mysql-error.log log-queries-not-using-indexes = 0 long_query_time = 3 slow-query-log = 1 slow-query-log-file = /var/log/mysql-slow.log I would appreciate some help in getting to the bottom of this.

I am designing a website that will provide vouchers (serial codes). All voucher codes generated and saved in a table like this and not generated online: v_id voucher status (used?) ------------------------------------------------------- 1 abcdefgabcdefg1 0 2 abcdefgabcdefg2 0 3 abcdefgabcdefg3 0 4 abcdefgabcdefg4 0 I should display unused (status:0) voucher to end user after successful payment but I am not sure how can I do this because this site may have high traffic so more than one people may visit site in same milisecond of time, so for example I can not just use: SELECT * FROM table WHERE status=0 LIMIT 0,1; and then UPDATE it: UPDATE table SET status=1 WHERE v_id=.... because two people may visit in same time so we provide same serial code in SELECT before we UPDATE. what would be best way to ensure I display unique serial code?

I have this table and I can't change its format to yyyy-mm-dd because I have so many scripts that are related to this format dd-mm-yyyy. It would be a mess if I change its format. Id date 1 01-07-2014 2 02-07-2014 3 03-07-2014 4 05-07-2014 5 07-07-2014 6 14-07-2014 7 18-07-2014 8 19-07-2014 9 21-07-2014 10 01-08-2014 11 02-08-2014 12 03-08-2014 On the php file $from = '01-07-2014'; $to = '02-08-2014'; I need to update some values from all the dates that are between 01-07-2014 and 01-09-2014 to the format dd-mm-yyyy. I am using UPDATE successlog SET successlog.overtime ='00:00' WHERE date >= '$from' AND date <= '$to' It is not working. I tried using the key between $from and $to. This does not work either. When the format was yyyy-mm-dd it was working normally, but after I changed the format to dd-mm-yyyy, it is not working.