[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

MySQL Server

47K

Solutions

23K

Contributors

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hello Experts,
I have a CSV file with 12 million lines. I am trying to upload it to a mysql table with the following command

LOAD DATA INFILE
 'c:\\_ms\\padron_reducido_ruc.txt'
 INTO TABLE tbl_padron_reducido
 FIELDS TERMINATED BY '|'
 LINES TERMINATED BY 'rn'
 IGNORE 1 LINES;

but I always get the message:

Error Code: 2013. Lost connection to MySQL server during query      30.000 sec

Why is this?

just in case he following variables, have those values.
SET GLOBAL interactive_timeout = 28800;
SET GLOBAL wait_timeout = 28800;
0
Acronis True Image 2019 just released!
LVL 1
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

hi,

I heard that to use MySQL InnoDB cluster to implement a cross site cluster solution (primary and DR site), we need to setup 2 x InnodB cluster and setup replication between them, so MySQL InnoDB cluster DO NOT support cross site failover, can only setup replication between 2 x MySQL InnoDB cluster group, am I right?

but when failover, how can we do it ? using VIP, Virtual IP ?
0
I need a calculated min column using datediff & min functions but not exactly the min value but the 2nd min value or date, below is the query for your reference:

Select r.rfq_Id, datediff(min(q.published_at),r.published_at) as "RFQ to 1st Quote TAT in days"

from quotes q
Left join rfqs r on r.id=q.rfq_id

This gives or groups me the rfq id column and date difference. But i want it use the 2nd min value for calculation rather than min value. Please suggest.
0
hi,

I am converting oracle table structure and data to mariaDB V10.3, using tools https://www.convert-in.com/oracle-to-mysql.htm, and the log say there are table can't be convert, let's see what it is:

1)
Table 'AQ$_QUEUES': column 'SUBSCRIBERS' has unsupported type 'AQ$_SUBSCRIBERS', skip it

2)
Table 'ROLLING$STATISTICS': column 'VALUEINT' has unsupported type 'INTERVAL DAY(3) TO SECOND(2)', skip it

3)
Table 'SCHEDULER_JOB_ARGS_TBL': column 'ANYDATA_VALUE' has unsupported type 'ANYDATA', skip it

4)
Table 'SCHEDULER_PROGRAM_ARGS_TBL': column 'DEFAULT_ANYDATA_VALUE' has unsupported type 'ANYDATA', skip it

is ANYDATA a customer defined type ? what type should it be when port to MariaDB ?

what type should this be : INTERVAL DAY(3) TO SECOND(2) when port to MariaDB ?
0
Hi,
If someone with experience using the MYSQL fo Excel version, can you please advise how to create a table from Excel.
My own effort failed. I had  selected 469 columns and 20 rows and it came up
on the screen, so far so good. I then clicked on Export data. Then the following message appeared.
What keys are they ??? There was a primary key which was automatically created.
Thanks for any help
Ian
 
"The MySQL Table `ukh_ireahcp` could not be created because of the following error(s):
MySQL Error 1069:
Too many keys specified; max 64 keys allowed"
0
I'm looking for a sql query to pull any woocommerce products or wordpress pages edited by a certain Wordpress user.  Ideally I'd get the post and the post revision along with the user that I'm targeting.

I did some Google'ing but couldn't find what I was looking for.  Any ideas?

Thanks!
0
Hello
EDITED 12th Oct 18
  • Summary
  • We have attempted to run Microsoft Azure Site Recovery (ASR) - Hyper-V Replication without success.
  • We are running Windows Server 2016 with 3 VMs
  • The ASR service has been installed by an external service company
  • We have been able to successfully complete the initial backup of the servers to the cloud
  • However the system  cannot keep up to date with the amount of changes on the server
  • All the upload bandwidth is being saturated (80 Mbps) and not keeping up with changes
  • (See attached screen shot of message from ASR)

These are the answers I received from the service company to my questions:
1. Could you tell me what the replication method we are using is called.
Azure Site Recovery - Hyper-V Replication
2. What the problem is. Is it the MySQL databases?
We don't know if it is the MySQL Databases for sure. As explained before the problem is too many changes are being made and the Azure servers cannot keep up with the constant changes.
3. Is the problem correctly described as being too much “data churn”?
I have forwarded the screenshot I had of the exact error message. (see uploaded image)

The service company cannot resolve the issue and say it is up to us to solve the data churn issue
They have asked Microsoft for help but they say they cannot because the issue is likely with …
0
I need the second to last item in the json response at the end of the code to include all the skus in each of the orders (so users can order all of that order's item's again all at once). What I have in there now only returns the sku in the last order. A preview of the current json response is embedded directly below. json
                // Organized Order Products into shipments //
                $shipments = array();
                foreach($order['order_details'] as $prod) {
            	   $grp = $dbAccessor->getShipmentGroup($prod['SKU']);
            	   if(!isset($shipments[$grp])) {
            	      $shipments[$grp] = array();  
            	   }

            	   $shipments[$grp][] = array('sku'             => $prod['SKU'],
                                              'name'            => $prod['Product'],
                                              'qty'             => $prod['qty'],
                                              'url'             => str_replace('http://www.mysite.com', '', $dbAccessor->getProductUrl($prod['SKU'])),
                                              'image_url'       => "/images/sku/small/".$dbAccessor->getSkuImage($prod['SKU']),
                                              'supplier_sku'    => $dbAccessor->getSupplierSku($prod['SKU']),
                                              'is_discontinued' => $dbAccessor->isDiscontinued($prod['SKU']),
                                              'supplier'       

Open in new window

0
Hi,

Before we migrate from oracle to MariaDB DB we will do PoC and for the PoC we need to prepare some information:

1) How many Tables, Views, Procedures, Functions, Packages, Triggers, Sequences, synonyms  are there need to migrate

2) how often items in 1) has been executed

3) do we need to modify SQL statements within the application?

For 1) and 2) any script helps to find out? the script should filter out the items NOT belongs to oracle system.

for 3) I have to check embedded SQL code within the application, right ?
0
The following is coded in Laravel framework:

QUERY

$shipping = DB::table('shipping')->where('country',$delivery->country)->first();
$data= json_decode(json_encode($shipping),true);

Open in new window


RESULT

array (
  'id' => 3,
  'carrier' => 'EN',
  'country' => 'AU',
  'rates_json' => '{"rates": [{"international": [{"zone4": [{"to_kg": "2", "total": "1", "from_kg": "1"}, {"to_kg": "4", "total": "2", "from_kg": "3"}]}]}]}',
)  

Open in new window


In MySQL database i stored rates_json in a json datatype column. The attribute from_kg and to_kg is a range.  

I intend to retrieve the total if a value is between the range. For instance, if value 1.5 is between  1 and 2 then the total is 1.

Your help is appreciated.

Thank You.
0
Build an E-Commerce Site with Angular 5
LVL 12
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

incMultiDBConnection.php:
<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);
 
$username = 'root';
$password = 'xxxxxx';
$dsn      = 'mysql:host=localhost;dbname=inventas;charset=utf8mb4';

$options  = [
    PDO::ATTR_EMULATE_PREPARES   => false,
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
];

try {
    $db = new PDO($dsn, $username, $password, $options);
    
if ($db->query($insert)) {
     echo "<script type= 'text/javascript'>alert('New Record Inserted Successfully');</script>";
} 
else{
     echo "<script type= 'text/javascript'>alert('Data not successfully Inserted.');</script>";
}
    
} catch(PDOException $e) {
    die( $e->getMessage() );
}

?>

Open in new window


<?php require_once('incMultiDBConnection.php'); ?>
<?php 


$insert = $db->prepare("INSERT INTO inventasequipment (equip_companyname, equip_name, equip_type, equip_sn, equip_productno, equip_website, equip_support, equip_use, equip_purchasedate, equip_warrenty, equip_performance, equip_comments, equip_cost, equip_datevisited, equip_timefrom, equip_timeto) VALUES (:company, :name, :type, :sn, :pn, :web, :support, :use, :purchase, :warrenty, :perf, :comments, :cost, :visitdate, :timefrom, :timeto)");

foreach ($_POST['equip'] as $equipment):
    $insert->execute([
        'company' => $equipment['companyname'],
        'name' => $equipment['equipname'],
        'type' => $equipment['equiptype'],
        'sn' => 

Open in new window

0
Hi,

Im curious if within a mysql stored procedure i can insert results from a query like so and then return the original select statement for further work in php

DELIMITER $$

CREATE PROCEDURE proc_text (
	IN var_plyr INT
)

BEGIN

	SET lmt = 0;
        SET cnt = 0;

       //original select statement
	SELECT
		p.first_name, p.email
	FROM players AS p 
       WHERE p.status = 1AND p.id = var_plyr
	GROUP BY p.id;
	
       SET cnt = @@ROWCOUNT;

	IF cnt > 0 THEN
	   
                plyr_loop: WHILE lmt < cnt

		INSERT INTO messages
                 ( first_name, email ) VALUES //-> ????

                  SET lmt = lmt +1;
                END LOOP plyr_loop;

//-> return original select statement

	ELSE
	
		RETURN SELECT "There are no players with that id" AS error;
	
	ENDIF;

END $$

DELIMITER ;

Open in new window


Thanks in advance!
0
Now i'm doing the monthly invoice system to calculate the late payment charges and the total amount , for the first month , the late payment charges is invoice sum * 10% then the total amount is invoice sum + late payment charges . For the second month , the late payment charges is total amount of first month * 10% then the total amount is total amount of first month + late payment charges .

This is what i do for the calculation but its manually
I have done the one like the picture above , but the amount overdue is manually input to get the late payment charges and total amount . Is another way to get the amount overdue from the total amount ? but the way i do to display the total amount is by loop method.

							<?php
							$query5 = $DBcon->query("SELECT * FROM owner_monthlyfees WHERE owner = '$owner' AND unit_name = '$unit_name' AND date2 BETWEEN '$newDateString' AND '$today' order by inv_no");      
							
								while ($row5=$query5->fetch_array()){
								
										$dunit=$row5['unit_name'];
										$downer=$row5['owner'];
										$ddate=$row5['date'];
										$dinv_no=$row5['inv_no'];
										$dinvoice_sum=$row5['invoice_sum'];
										$dpayment=$row5['payment'];
										$dlatepayment=$row5['late_payment_charges'];
										$damt_overdue=$row5['amount_overdue'];
										$dlatecharge=$row5['late_charge'];
										$dtotal=$row5['total'];											
								?>	
    							<form method='POST' action='' enctype='multipart/form-data'>																																				

Open in new window

0
MySQL - Percona Edition.

Full table scan is taking over 1 second and crashing our backend servers, the table has about 1.2 million records, if the IP is located then it returns very quickly and has no issues, eventually we hit max connections on the server if it can't find it as it does a full table scan. This server is beefy, 128GB of ram + 800GB SSD and dual hexacore processors

SELECT *
  FROM geoip_proxy_ipv4_09_2018
  WHERE INET_ATON('192.168.0.1') BETWEEN ip_from AND ip_to
  LIMIT 1

Open in new window


The table structure is this:
CREATE TABLE IF NOT EXISTS `geoip_proxy_ipv4_09_2018` (
  `ip_from` decimal(10,0) NOT NULL,
  `ip_to` decimal(10,0) NOT NULL,
  `proxy_type` varchar(3) NOT NULL,
  `country_code` varchar(2) NOT NULL,
  `country_name` varchar(128) NOT NULL,
  `region_name` varchar(128) NOT NULL,
  `city_name` varchar(128) NOT NULL,
  UNIQUE KEY `idx_ip_from_ip_to` (`ip_from`,`ip_to`),
  KEY `ip_to` (`ip_to`),
  KEY `ip_from` (`ip_from`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Open in new window


These are the indexes:
Indexes
Keyname	Type	Unique	Packed	Column	Cardinality	Collation	Null	Comment
idx_ip_from_ip_to	BTREE	Yes	No	ip_from	1254839	A	No	
ip_to	1254839	A	No
ip_to	BTREE	No	No	ip_to	1254839	A	No	
ip_from	BTREE	No	No	ip_from	1254839	A	No	

Open in new window


If we do 800 requests per second (lookups) our database server and our backend servers max out their connections waiting for response from backend server... thoughts?
0
I want to run between query for value filter like this:
SELECT * FROM `wm_products` WHERE pro_price BETWEEN 2000 AND 3000 OR pro_price BETWEEN 3000 AND 4000

So, my query is

$sql = "SELECT * FROM wm_products WHERE";
            if(!empty($price)){
                  $price =implode("'or pro_price between'",$price);
                  $sql  .= " and pro_price between $price";
            }
0
WordPress / WooCommerce Database is huge.  I'm helping with a store that over the years the database tables have become huge.  Literally millions of table entries.  Is there any way to get rid of data that is no longer needed in the postmeta table?  Is there old information that no longer needs to be stored?

I was told I can use the following in MyPHPadmin:
SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;

Open in new window


But if I do that, will I loos anything from my WooCommerce Store?
0
i have page to add new package with inputs and i have multi row dynamic create row for more details to this package inside this page

and also i have multi image uploader the same idea for invoice but add to this multi images

i have this photos to help you think my case
newpackage.php

<?php include "header.php"; ?>      
		<?php

			/// validate vars
										
			$num=" SELECT MAX( package_id ) as package_id FROM packages ";
			$query=mysqli_query($connect,$num);
			if($query){
				$row = mysqli_fetch_array($query);
				if($row){
					$max= $row['package_id'];
					if($max > 500){
						$package_id= $max + 1 ;
					}
					else{
						$first= 500 ;
						$package_id= $max + 501 ;
					}
					$filter_package_id=$package_id;
				}
			}
		?>

		<script src="../tinymce/tinymce.min.js"></script>
			<script>
			tinymce.init({
			selector: '.mytextarea'
			});
		</script>
      <!-- Right side column. Contains the navbar and content of the page -->
      <div class="content-wrapper">
        <!-- Content Header (Page header) -->
        <section class="content-header">
          <h1>
            Packages
            <small>Control panel</small>
          </h1>
          
        </section>

        <!-- Main content -->
        <section class="content">
          
          <!-- Main row -->
          <div class="row">
            <!-- Left col -->
            <section class="col-lg-12 connectedSortable">
              

              <!-- quick email widget -->
    

Open in new window

1
I have an application built using php/mariadb

i want to create a demo of the site that has standard data in it. A user can then play around with the data.

I do not want the new data entered by the demo user to exist for another user

what are my opions?

Thanks
0
Is it possible at all to include multiple fields included in crosstab query? For example in below code I now have 2 fields I need to look at as below:

SELECT
  IfNull(c.Adviser, 'GRAND TOTAL') AS Adviser,
  Sum(CASE WHEN Month(i.CompDate) = 1 THEN i.comms + i.broker_fee + i.legal_fees ELSE 0 END) Jan,

Open in new window



What I now need if possible:
Sum(CASE WHEN Month(i.CompDate) = 1 THEN i.comms ELSE 0 END) OR Sum(CASE WHEN Month(i.signedupdate) = 1 THEN i.legal_fees ELSE 0 END)  Jan,

Open in new window



Does that make any sense?
0
Why Diversity in Tech Matters
LVL 12
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Here is my configuration for protecting one of my directories using mod_authn_dbd...
<VirtualHost *:80>

DBDriver mysql

DBDParams host=127.0.0.1,port=3306,dbname=myDB,user=root,pass=*****

DBDMin  4
DBDKeep 8
DBDMax  20
DBDExptime 300

<Directory "/path/to/ProtectedDir">
AuthDBDUserPWQuery "SELECT encrypt(passwd) AS password FROM myTable WHERE user_name = %s AND DUE_DATE>=CURDATE()"
  AuthType Basic
 AuthName "CurrentIssue"
 Require valid-user
 AuthBasicProvider socache dbd
</Directory>


    ServerName www.mySite.com
    ServerAlias mySite.com
    DocumentRoot /Server/WebSites/mySite
    ErrorLog /Server/WebSites/mySite/error.log
    CustomLog /Server/WebSites/mySiterequests.log combined

<Directory "/Server/WebSites/mySite">
    AllowOverride None
    Require all granted
</Directory>

</VirtualHost>

Open in new window

My question is how do I protect another Directory using a different mysql DB?

I've tried adding
DBDParams host=127.0.0.1,port=3306,[u]dbname2[/u]=myDB,user=root,pass=*****

Open in new window

and
<Directory "/path/to/ProtectedDir2">
AuthDBDUserPWQuery "SELECT encrypt(passwd) AS password FROM myTable2 WHERE user_name = %s "
  AuthType Basic
 AuthName "Admin"
 Require valid-user
 AuthBasicProvider socache dbd
</Directory>

Open in new window

As expected this breaks both Directories.
Any ideas on how I would accomplish this?
Is there any way to use mod_authn_dbd with htaccess?
0
I have this query to check the last 3 months sales figures and another query to check the refund figures

       
           // sales
            SELECT DATE_FORMAT(order_date, '%b') as month, SUM(order_total) as totalSales FROM
            (
               SELECT order_date, order_total
               FROM order_summary
               WHERE order_date <= NOW()
               and order_date >= Date_add(Now(),interval - 3 month)
               AND `order_status` = 'Approved'
               
               UNION ALL
               SELECT  Date_add(Now(),interval - 2 month)  AS order_date, 0 as order_total
               UNION ALL
               SELECT  Date_add(Now(),interval - 1 month)   AS order_date, 0 as order_total
               UNION ALL
               SELECT  Now() AS order_date, 0 as order_total
            ) as test
            GROUP BY DATE_FORMAT(order_date, '%m-%Y')
            ORDER BY DATE_FORMAT(order_date, '%m-%Y') desc

Open in new window


           // refunds
        SELECT DATE_FORMAT(credit_date, '%b') as month, SUM(`credit_value`) as totalCredit FROM
            (
               SELECT credit_value, credit_date
               FROM credit
               WHERE credit_date <= NOW()
               and credit_date >= Date_add(Now(),interval - 3 month)
               AND `reason` = 'Refund'
               
               UNION ALL
               SELECT  Date_add(Now(),interval - 2 month)  AS credit_date, 0 as credit_value
               UNION ALL
               SELECT  Date_add(Now(),interval - 1 month)   AS credit_date, 0 as credit_value
               UNION ALL
               SELECT  Now() AS credit_date, 0 as credit_value
            ) as test
            GROUP BY DATE_FORMAT(credit_date, '%m-%Y')
            ORDER BY DATE_FORMAT(credit_date, '%m-%Y') desc
            ");

Open in new window


I want to show the data in a table like:

Month          Sales          Refunds

Sep                100                 0
Aug                100                20
Jul                   50                   5

I thought of trying to combine the queries into one but that seems very complex. And trying to nest the foreach loops creates 6 rows instead of 3. What would be the best way to achieve this?
0
how to bring common database to object structured JSON.
Hi. I have a database query that will result a list out of tokens for translation.

application.title = "Korpuskonfigurator"
application.dialogs.username = "Username"
application.dialogs.errors.missingfields = "Bitte alle Felder ausfüllen."
anschlagsart = "Anschlagsart"

And so forth. I have to bring this is an object structured JSON file for an external translation engine.
It have to look like s the following JSON sample. The token is splits by its points the structure have to be created.
I tried now a long time, but I complete fail. I hope anybody can give me a hint.

{
  "application": {
    "title": "Korpuskonfigurator",
    "cart": "Projektverwaltung",
    "nyi": "Noch nicht verfügbar!",
    "dialogs": {
      "username": "Username",
      "id": "ID",
      "name": "Korpusname",
      "errors": {
        "missingFields": "Bitte alle Felder ausfüllen.",
        "specialCharacters": "Keine Sonderzeichen erlaubt!"
      }
    },
    "inspector": {
      "stowageHeight": "Stauhöhe",
      "newValue": "Neuer Wert",
      "firstDrilling": "Erste Bohrung",
      "original": "Original",
      "openOPC": "Ändern",
      "positions": "Positionen",
      "finalPosition": "Final",
      "corrPosition": "Korrekturwert",
      "origPosition": "Originalwert",
      "suggestedPosition": "Vorschlagswert",
      "delta": "Delta"
    },
    "helpmenu": {
      "minz": "Min. Z",
      "maxz": "Max. Z",
      "fov": 

Open in new window

0
Dear Experts
We are having CRM application which is web-based runs on LAMP stack. the operation team reports between specific time period user get time out when they save the records or convert the records or retrieve the records, can you please suggest on how to capture log files which occurs during that time for example 5pm to 6pm, only during this time I would like to capture a following log files.
1) /var/log/mysql.log
2)/var/www/html/crmapp/crm.log
3) /var/log/slowquerylog
4)/var/log/httpd/error.log
please help with steps on above 1 to 4 log capture during specific time period , this will be helpful to investigate , thank you very much.
0
Having issues with resume upload parsing page:

http://ablcareers-dev.cloudaccess.host/search-abl-jobs/resume/013947 as an example

When a user clicks to upload their resume, and selects CONFIRM INFO, the page refreshes after a couple of seconds and it appears as if no data has been processed.

Code is below, simple file upload and script. Working with Joomla.

<form class="uk-form"  name="frmMain" method="post" id="frmMain" enctype="multipart/form-data">
	<input type="hidden" name="jobId" value="<?= $this->jobID ?>" />
	<input type="hidden" name="email" value="<?= $this->email ?>" />
	<input type="hidden" name="action" value="resume" />


           <input class="form-control  uk-form-large uk-width-7-10" id="filResume1" type="file" name="filResume1" /> <button class="uk-button-primary submit uk-button" >Confirm Info</button>
           <label class="error" for="filResume1"></label>
           <button class="noresume uk-button uk-button-primary" >I  don't have a resume</button>
		   <p style="font-size:12px">No Resume? No Problem you can still apply manually</p>

	
	
		<p id="spProcessing"></p>

	
</form>

<script>
jQuery('.submit').click(function() {
	jQuery('#spProcessing').text('Processing resume ............... ');
	jQuery('#frmMain').submit();
});
jQuery('.noresume').click(function() {
	jQuery('#frmMain').submit();


});
</script>

Open in new window


There are no errors in the console, and I cannot figure out what may be the cause of the issue.

Would the PHP / MySQL version possibly affect tthe functionality of this?

Any insight would be helpful,

Thank you!
0
Dear Experts.
i have a simple WINDOWS  webserver with an installation of MYSQL (5.6) that hosts some wordpress Databases
one of my dabases gives the following error

, , , 2018-09-11 08:39:27 183c InnoDB: cannot calculate statistics for table "wordpress941"."wp_posts" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html

i looked inside the Database folder and the file wp_posts.ibd is there but it looks apparently corrupted i have no idea how can i fix this on a windows server.
can someone please help.

restore from backup didnt help apparently the last 14 days have all been backed up with the corrupted files.
0

MySQL Server

47K

Solutions

23K

Contributors

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.