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

hi,

I am studying MariaDB trigger:

this is  a trigger:

CREATE DEFINER=`root`@`%` TRIGGER `HR`.`UPDATE_JOB_HISTORY` AFTER UPDATE ON HR.EMPLOYEES FOR EACH ROW 
Begin 
   IF (NEW.job_id <> OLD.job_id) AND(NEW.department_id <> OLD.department_id) then 
      CALL HR.ADD_JOB_HISTORY(OLD.EMPLOYEE_ID,OLD.HIRE_DATE,CURRENT_TIMESTAMP,OLD.JOB_ID,OLD.DEPARTMENT_ID); 
   end if; 
end; 

Open in new window


it will call a SP called ADD_JOB_HISTORY and what this SP does is:

DROP PROCEDURE IF EXISTS HR.ADD_JOB_HISTORY; 
CREATE PROCEDURE HR.`ADD_JOB_HISTORY`(P_EMP_ID          MEDIUMINT 
   , P_START_DATE      DATETIME 
   , P_END_DATE        DATETIME 
   , P_JOB_ID          VARCHAR(10) 
   , P_DEPARTMENT_ID   SMALLINT) 
BEGIN 


   INSERT INTO JOB_HISTORY(EMPLOYEE_ID, START_DATE, END_DATE, 
                           JOB_ID, DEPARTMENT_ID) 
    VALUES(P_EMP_ID, P_START_DATE, P_END_DATE, P_JOB_ID, P_DEPARTMENT_ID); 
END; 

Open in new window


when when I insert a record in EMPLOYEES  tables nothing created in JOB_HISTORY table, what is going on ?
0
Exploring SQL Server 2016: Fundamentals
LVL 13
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

I'm trying to convert the following query that worked in MySQL 5.5 to MySQL 8.0. I am getting the following error: "Error Code: 1109. Unknown table 'GLOBAL_STATUS' in information_schema"

If this table no longer exists in version 8.0, is there another way to get the slave status?

DELIMITER ;;
drop procedure if exists aspDoCustDbCheck;;
CREATE  PROCEDURE `aspDoCustDbCheck`()
begin
 declare vSlaveSts varchar(12);
 
 SELECT variable_value into vSlaveSts
 FROM information_schema.global_status 
 WHERE variable_name='SLAVE_RUNNING';
 
 Select vSlaveSts as SlaveSts
  ,Now() as Time
 ; 
end ;;
DELIMITER ;

call aspDoCustDbCheck();

Open in new window

0
hi,

As I am testing Oracle to MariaDB converison and when I migrate to MariaDB some table may fail to create when there are foreign key prevent me from creating tables .

from your point of view, how can I check quickly which MySQL constraint I have to remove before dropping that table and recreate again .

it is a very time consuming task if I have to check tables by tables .. ( 3xxx tables!)

or I should look at Oracle constraint instead to give hints  ?
0
hi,

as I am tried to migrate from Oracle to MariaDB/MySQL and we are try to tools Ispirer, once the tools convert the following object for us how can we verify the converted objects can works without any problem:

1) sequence,

by running Select NEXTVAL(<sequence name>); ?

what should be the output?

2) Function.

3) trigger

4) package,

5) synonyms.

any idea?
0
I had my 10.2.15-MariaDB server crashed after a select count(*) from a 18.6 Gb table.

 It's not a HeidiSQL error because it also happens if i do it using MariaDB's command prompt.

After this crash, i can't do a select count(*) over 60 seconds that i get the same error again.

It doesn't seem like a configuration issue as this weren't happening before the first crash.

I used to run several hours querys without a problem.

My interactive_timeout variable is set to 28800:

"Variable_name";"Value" "connect_timeout";"36000" "delayed_insert_timeout";"300" "innodb_flush_log_at_timeout";"1" "innodb_lock_wait_timeout";"50" "innodb_rollback_on_timeout";"OFF" "interactive_timeout";"28800" "lock_wait_timeout";"86400" "net_read_timeout";"30" "net_write_timeout";"60" "slave_net_timeout";"60" "thread_pool_idle_timeout";"60" "wait_timeout";"28800"

I still have 62 Gb free in my hard drive (SSD) and my PC has 32 Gb. of RAM memory.

Thanks!
0
hello

i'm working on a mariadb cluster with high performance requirements in terms of number of queries.

i can work with read-uncommited. actually, most of the app would not require more than eventual consistency. i'm well aware that app should probably be using cassandra or a similar tool, but moving from mysql is simply not currently an option. mariadb using a cassandra store neither. i'll be probably moving part of the load to memcache/redis for parts that do not need to be in an SQL store. little by little, though.

i've worked quite extensively with mysql tuning in the old days, but not that much in the past 5 to 10 years and i feel i might be quite outdated and additionally working with both an environment and a workload i'm not used to on mysql.

i can obviously add more NDB members but i'd rather switch to maria if possible.

the overall quantity of data is rather small : around 10Go but we expect about *10 growth quite shortly. actually more, but i'll probably manage to optimise many things by then.

--

workload

the current NDB cluster has 2 members

it performs about 10k select queries per second on each member, and about 200 inserts/s. the rest is comparatively neglectible.

most of the select and inserts will be a single row.

there are a few bigger queries, but performance on those queries is not critical as long as running them does not produce contention.

--

hardware and OS requirements. i have no control over most of these.

0
I have a program that call to a php file for check the username and password of my program...

Any script or code in php or other for check if any username and password is used by more than one ip at the same time?

For check if the username and password is passed to other persons...

Thanks in advance for the answers...
0
I am having trouble with this code and cant get it to work. I only want to update the fields that have been updated, but what is happening is, someone updates say the approvedby_line_manager field and then the code updates approvedby_line_manager, approvedby_so & approvedby_coo too! it shouldnt do that but need some help?

function commission_approvals_before_update(&$data, $memberInfo, &$args){                           
                                
                                $_SESSION['U_approvedby_line_manager']=sqlValue("select approvedby_line_manager from commission_approvals where refnum='" . $data['refnum']. "'");
                                $_SESSION['U_approvedby_so']=sqlValue("select approvedby_so from commission_approvals where refnum='" . $data['refnum']. "'");;
                                $_SESSION['U_approvedby_coo']=sqlValue("select approvedby_coo from commission_approvals where refnum='" . $data['refnum']. "'");
                                $_SESSION['U_approvedby_ges_finance']=sqlValue("select approvedby_ges_finance from commission_approvals where refnum='" . $data['refnum']. "'");
                                
                                return TRUE;
                }
                                
                                function commission_approvals_after_update($data, $memberInfo, &$args){
                                
                                table_after_change($_SESSION ['dbase'], 

Open in new window

0
hi,

anyone know how to use Oracle SQL developer to show a report and export as an excel for number of record information of all table belongs to a schema ?


I want to compare the same tables in MariaDB once I migrate from Oracle.
0
hi,

right now we are analysis the need of data warehouse and data mart as well as ETL.

our oracle database has some ETL by batch process, we don't know what it is but in system diagram we have ETL.

and we use replicate technology to replicate data from OLTP to 2x other DB for reporting purpose but we do not use the oracle data warehouse builder to create a data warehouse.

question now is, why and when should we use mariaDB AX to create a separate data warehouse for reporting purpose as existing model already can handle loading, why need to redesign one using MariaDB AX to do reporting ?

data mart on MySQL/MariaDB is REALLY necessary ? can it be done on MySQL and MariaDB?

please share your thought.
0
Exploring SharePoint 2016
LVL 13
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Hi Experts
I have installed Mysql 5.7 package via chef.
Now when in try to create Database iam getting error,
Iam using the below to Create Database on Mysql:

mysql_database 'test' do
 connection mysql_server_connection
 action :create
end
mysql_database gives me error like method is undefined.
First i need to install the mysql2 gem

if i try to install mysql2_chef_gem i get error Package: No candidate version available for mysql-community-client, mysql-community-devel

I was unable to install mysql2_chef_gem which i have seen on the marketplace cookbooks but it does not work for me .
Any help or pointers will be very helpful for me.thanks
0
HI All,

Hope somebody can help. I need to grant remote root access to our MySQL database to a developer. I've done this in the past with out issue using the command line inputs below. However, this time it throws an error Error When Trying to Grant An IP Access to ROOT and I cannot figure out why or how to make this work. Any idea?

mysql> use mysql
mysql> GRANT ALL ON *.* to root@'IP Address' IDENTIFIED BY password';
mysql> FLUSH PRIVILEGES;
0
hi,

we planning to migrate from Oracle to MariaDB and there will be change on data type, will there be any impact on column type changing ?

If no impact, any reason for it?

and usually any impact on GUI and report design?
0
Hi,

We are doing impact analysis on the new type AFTER convert from Oracle 10g/12c to to MariaDB, any information on

1) Is there any impact on the change of type ( in terms of RAM, disk space and CPU) ?
2) What to do to get ride of the impact?
3) URL for the proof?

For the following type conversion:

NUMBER(10,0)  and NUMBER (9,0) convert to BIGINT
NUMBER(3,0) convert to SMALLINT
NUMBER(1,0) convert to TINYINT.
NUMBER without () (e.g. NUMBER) to DOUBLEe
      DATE  to DATETIME
      VARCHAR2() to VARCHAR()  (e.g. VARCHAR2(1 CHAR) will become VARCHAR(1). VARCHAR2(100 CHAR) will become VARCHAR(100)
0
hi,

right now planning the last stage of oracle to mariaDB migration and here are some BI related question, hope you all can give a hand.

On the change on the DB and data column type. (e.g. Oracle varchar2() to MariadB varchar()),  usually any impact on GUI and report design?
0
Hello experts,

I am working on an expert system which will run through a website I have in development.  The purpose is to provide step by step instructions based on Project by state, so no matter who is working the queue, we end up with a consistent build.  So this question is based in OOP theory and how to bring about a result (storing the data in a database...  Because if you can put it in there...  I can retrieve it... hopefully, lol.  So I am going to use a dumb made up example so I don't give up the secret sauce.  So lets go with cake.   Bob is our baker, and Karen is our master chef.  Karen has checked with each state law on how to make each specific kind of cake that we have seen, and we need to consider a default outcome for recipies she hasn't seen.

So Bob looks in his queue and sees a ticket to make a Strawberry Cake in Colorado.  Karen has predefined that Bob need to 1) pour batter A  into 2 baking pans, 2) bake the cakes 3) ice the middle using frosting C and then 4) send to shipping.  Bob see's the "preflight" instructions and follows them diligently.   Bob once again looks in his queue and sees a ticket for a Strawberry Cake in Hawaii  (State law says we have to use batter B (gluten free)).  Karen has predefined that Bob need to 1) pour batter B  into 2 baking pans, 2) bake the cakes 3) ice the middle using frosting C and then 4) send to shipping.  

A ticket can have up to 3 cakes on it ever,  And Karen needs to be notified whenever there is a request …
0
I am using WAMP on my Windows 10 machine.

I am having the same issue on my Ubuntu machine.

When I add to many columns to my database I get an error:

Rebuild database fault: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535.

Open in new window


I have gone to the my.ini file and added:
innodb_strict_mode=0

I have also tried increasing my log file size to 512 and the buffer size to 2048 just to see. Still no change.

Any time I try and add another column in mysql I get the error.
0
Can you look up a subsequent record like the below using a MySQL trigger and omitting the ID lookup SELECT statement in the original query?


INSERT INTO _skills_emp_link (skill_id, emp_id, region_id)
                         VALUES (‘2.’, ‘4’, (SELECT region_id FROM employees WHERE employee_id = ‘4’));

Open in new window


Basically I would like to issue a Query like:
 INSERT INTO _skills_emp_link (skill_id, emp_id) VALUES ('2', '4') 

Open in new window


and a BEFORE or AFTER INSERT trigger to take the emp_id 4 (entered in the initial query) and look up the attributed region_id and append the SQL query so that the FK constraints work properly.
0
This is related to MySQL.
I ran a command --> mysqldump -u -p DBNAME > [Path of .sql file],
After running this command , I ran --> mysql -u -p DBNAME > [Path of .sql file].
After this, my Table data got overwritten and my Table is blank. Only the table structure remains. Please suggest how to bring back the Table original Data.
0
HTML5 and CSS3 Fundamentals
LVL 13
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

MySQL form POST to database.  When INSERT INTO query is performed on what seems are random basis (or perhaps it is based on content of the data POSTed from the text area on the from, the data saved to the field in the database sometimes has duplicate information.  For example, if there are multiple sentences, a portion of one of the sentences will be duplicated in the paragraph of text.  Not sure what would cause something like this.  When using the form we can edit and try to save the same text several times, usually it will eventually save correctly.  I have several other application/database pairs on the same web/mysql server and don't seem to experience this issue.  So I am assuming the problem is coming from this specific website.

Here are some code snippets:

from the PHP form:

<div id="workOrderForm">
                  
                                                   
                              <form name="workOrderForm" method="post"  action="formdata.php">
               
                                       <div class="formLable">Client Code:</div>
                                       <input name="clientCode" type="text" class="formFiller" onkeypress="return disableEnterKey(event)" option value="<?php echo $result['clientCode'];?> "  />
                                    
                                    <div id="emailLable">Email:</div>
                                       <input name="email" type="text" id="emailFiller" onkeypress="return disableEnterKey(event)" option value="<?php echo $result['email'];?>" />
                                                                        
                                    <div class="formLable">Company Name:</div>
                                       <input name="companyName" type="text" class="formFiller" onkeypress="return …
0
Hi,  I'm creating a PHP and MySQL web application for a client. I'm a fairly new web developer.
Is there a way to add page titles and meta tags dynamically? It's a small application. I'm not using a Framework.
I saw a couple of possible solutions on google, but am not sure what the easiest and most efficient way of doing it would be.

Possible Solution #1, add variables in my header.php page and then on each respective page for example about, contact etc... add those variables above the include statement.
Here is my header.php code:

?>

<!DOCTYPE html>
<html lang="en">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
	<meta charset="utf-8">
	
	<meta name="viewport" content="width=device-width, initial-scale=1">
	<meta name="description" content="<?php echo $meta_description; ?>">
	<meta name="author" content="Ryan Sacks">
	<link rel="icon" href="img/favicon.ico">
	
	<title><?php echo $page_title; ?></title>
	
	<!-- Bootstrap core CSS -->
	<link href="css/bootstrap.min.css" rel="stylesheet">
	
	<!-- Font Awesome Icons -->
	<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.3.1/css/all.css" integrity="sha384-mzrmE5qonljUremFsqc01SB46JvROS7bZs3IO2EmfFsd15uHvIt+Y8vEf7N7fWAU" crossorigin="anonymous">
	
	<!-- Custom CSS -->
	<link href="css/style.css?v=<?php echo time(); ?>" rel="stylesheet">
	
	<!-- Google Fonts -->
	<link href="https://fonts.googleapis.com/css?family=Oswald:300,400,500" rel="stylesheet">
	<link 

Open in new window

0
I have a problem updating a mysql table entry.  I copied and pasted the format I've used for years to update tables, but for some reason it is not working here.

My query reads, $queryUpdate1=mysql_query("UPDATE material SET _3Quan = '$_3Quan' WHERE file='$file'") or die('Query failed1: '.mysql_error());

The variable $_3Quan has a value of 100, but when the material table is updated, it returns a value of 0 to the _3Quan field.  Prior to update the value was 95.

Can you help me figure out what I'm doing wrong here?
1
I would like to update fields in an existing group the custom fields in WordPress more quickly  in a "BATCH form" did anyone here mange to do it without messing up.
for example I want to start with adding extra text fields to an existing group,
Maybe MYSQL request ?
Maybe JSON request.
has anyone done this ?
0
Hi,
I’m creating a web application using PHP and MySQL. I’m looking for a simple solution to be able to accept online payments. If I was using a CMS like WordPress or Joomla I know for WordPress there’s WooCommerce, I could just use a Plugin, But I’m not. Again, if I were using Magento or Presta Shop or something but again I’m not, I’ve coded everything from scratch. What’s unique about my clients situation is that he will only be selling one product, so it’s not like a big E-commerce store with thousands or tens of thousands of products.
From what I’m learning I believe I’m going to need to use either PayPal or Stripe and I might have to create my own checkout and cart pages but I’m not really sure. Anyone know of a simple solution?
0
I want make a display Candy name only display icon tick in the column Candy without duplicate in column melvin and angel . But right now my data is duplicate over in every name column .
 
question1.JPGThis is my table display right now

      <div class="card mb-3">
        <div class="card-header">
          <i class="fa fa-table"></i> Calendar</div>
        <div class="card-body">
          <div class="table-responsive">
            <table class="table table-bordered" id="dataTable" width="100%" cellspacing="0">
              <thead>
			  <tr>
			  <th>Time</th>
			  <?php 
			  $namearr = array(); 
			  $sql="select * from admin_staff";
			  $result3= mysqli_query($DBcon,$sql);
			  while ($row3= $result3->fetch_array()){ 
			  ?>
			  <th><?php echo $row3['name']; }?></th>
			  </tr>
			  </thead>
			  <tbody>
			  <?php 
				
				$open_time = strtotime("9:00");
				$close_time = strtotime("21:00");
				
			  for($i=$open_time; $i<$close_time; $i+=3600){
			  ?>
			  <tr>
			  <td><?php echo date("l - H:i",$i); ?></td>

			  <?php 
			  $sql_count="select count(*) as counter from admin_staff";
			  $result_count= mysqli_query($DBcon,$sql_count);
			  $rowcount= mysqli_fetch_assoc($result_count);
			  for($count=1;$count<=$rowcount['counter'];$count++)
			  {
			  ?>
			  <td>
			  <?php 
				$new_time= date("H:i:s",$i);
				
			  	$sql= "select * from admin_calendar where time='$new_time'";
				$result4= mysqli_query($DBcon,$sql);
				$row4= 

Open in new window

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.