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

Howcome i am getting this error when trying to reverse engineer my schema with mysql workbench?

I cant find this table anywhere in my database -- informatin_schema.triggers -- its not a permission issue because ive tried with root.

I have to use FreeTDS driver and not native mssql driver because it wasnt even loading the schemas for me. this driver worked but im not sure if its the driver or this mysterious table?


ERROR: Reverse engineer selected schemas: ProgrammingError("('42S02', "[42S02] [FreeTDS][SQL Server]Invalid object name 'INFORMATION_SCHEMA.TRIGGERS'. (208) (SQLExecDirectW)")"): error calling Python module function DbSql92RE.reverseEngineer
Failed

Open in new window

0
Migrating from MSSQL to mysql using migration wizard (specifically from MSSQL Azure hosted DB).

WHen i connect with mysql workbench migration wizard, the schema is blank, and it cant find the related database and or tables to be imported to mysql.

 I used an alternative software called Data Loader -- https://www.dbload.com/
and it worked perfectly, found the schema on my first try.

I have to purchase this software -- 99 bucks, but i would prefer not too. Does anyone know why MySQL doesnt work finding the schema but this software that looks like its from 1990s works?

If the data loader app works it cant be permissions or user account because im using all the same.
0
I am thinking of implementing HA solution for MySQL. I found that InnoDB Clustering is one of the option but require 3 DB nodes. What is the advantage of InnoDB Clustering compare with other solution which require just 2 DB nodes such as MHH ?
0
I am using MySQL and require to have High Availability (HA) solution. I found there is a solution call InnoDB cluster but it require 3 DB nodes. Is there any other HA solution which just require 2 DB nodes ? as I know, MS SQL is able to setup this with 2 DB nodes
0
I had this question after viewing Kannel compilation.
am having issue on this step about mysql
 
trying install kannel 1.5.0 in centos 7   kannel at the end installed but due to mysql issue then i cant install sqlbox

[root@send-sms gateway-1.5.0]# ./configure --prefix=/usr/local/kannel  --with-mysql-dir=/usr/bin/mysql/ --enable-debug --enable-assertions --with-defaults=speed --disable-localtime --enable-start-stop-daemon --enable-pam

Configuring for Kannel gateway version 1.5.0 ...

Running system checks ...
checking build system type... x86_64-unknown-linux-gnu
checking host system type... x86_64-unknown-linux-gnu
checking for gcc... gcc
checking for C compiler default output file name... a.out
checking whether the C compiler works... yes
checking whether we are cross compiling... no
checking for suffix of executables...
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking for gcc option to accept ISO C99... -std=gnu99
checking for a BSD-compatible install... /usr/bin/install -c
checking for ranlib... ranlib
checking for bison... bison -y
checking for flex... flex
checking lex output file root... lex.yy
checking lex library... none needed
checking whether yytext is a pointer... no
checking for ar... ar
checking for convert... 

Open in new window

0
hi,

how to configure connection string for read-only round robin scale out operation for MariaDB ?


tks.
0
hi,

I read this :https://www.red-gate.com/simple-talk/cloud/cloud-data/designing-highly-scalable-database-architectures/

Other than MS SQL always on which is the MS version of Horizontal scale out solution, how can I implement shading with SQL always on read only routing ?

do any you all disagree that shading is the horizontal scale out for write operation?
0
So unfortunetly my data is not updating even though I get a status request of 200 ok below are my front end and back end code
and below I have a screen shot and my front end and back end code I do not know why it is not updating in the front end when I do it on postman it does update and the querry works

data-not-updating.png
app.put('/movielist/updateMovie/:id',(req,res) =>{
  let update = req.body;
  mysqlConnection.query("UPDATE movielist SET name = '', thumnail_path = ?, description = ?, year_released = ?, language_released = ? WHERE idmovielist = ?",
 [update.name,update.thumnail_path,update.description,update.year_released,update.language_released,req.params.id],function (err, results) {
    if (!err) {
      res.send("Movie list is updated");
    } else {
      console.log(err);
    }
  });
});

$(function() {
  //Fill the list when the page loaded
  renderMovieList('movies');

  $("#movieAdded").click(function(a) {
    a.preventDefault();

    let mydata = {
      idmovielist: $($("#newForm")[0].intNum).val(),
      name: $($("#newForm")[0].name).val(),
      thumnail_path: $($("#newForm")[0].thumnail_path).val(),
      description: $($("#newForm")[0].description).val(),
      year_released: $($("#newForm")[0].year_released).val(),
      language_released: $($("#newForm")[0].language_released).val(),
    }

    displayMovie(mydata);

    $("#newForm").trigger("reset");
    $("#newForm").toggle();
  });

  $("#updateMovie").on("click", function(a) {
    

Open in new window

0
I need a sql command which shows me all active transactions
0
select
	DATE(quotes.create_date) as create_date,
	quotes.year,
    quotes.make,
    quotes.model,
    quotes.type,
    quotes.city_org,
    quotes.prov_org,
    quotes.city_dest,
    quotes.prov_dest,
	quotes.quote_amount,
	quotes.origin_shipping_type,
    quotes.dest_shipping_type,
    quotes.order_note,
    notes.note_text
from notes
join quotes on notes.quote_no=quotes.quote_no
where 
(
	quotes.quote_type = "EN"
	OR notes.note_text like "%non runner%"
	OR notes.note_text like "%nr%"
    )
AND notes.note_text like "(by:%"
AND notes.note_text not like "(by: Roger) T%"
AND notes.note_text not like "(by: Doug) T%"
AND notes.note_text not like "(by: Larry) T%"
AND notes.note_text not like "%Quote Source%"
AND DATE(quotes.create_date) >= '2017-01-01'

Open in new window


How can I concatinate the note_text so there is only a single row with concatinated notes instead of duplicate rows with different note text?

Sample output attached, I'm trying to get the notes (last column in attached image)  concatinated and only 1 row returned instead of 2 and the notes column should show "(by: Steve) non runner, (by: Steve) 3300" all in the same string.

I tried using concat and group_concate and it didn't work so well... need help
sample.JPG
0
I've recovery my windows OS then i found mysql folder in Windows.old folder.  and MySQL unistalled or windows restored to previous version before MySQL Installed.
I try to make Solution as follows:
1- I've used New PC with Mysql .
2- stop Mysql Services.
3- replace Data folder.
4- When try to start MySQL again error 1067 is appear.

how can i recovery data from ibdata1 file and *.frm
0
I have a MySql question that is out of my league. I have a self reference hierarchical table. Every row is a node in the tree. The parent column points at the id of its parent, the master.

TABLE MyTable1
  id int(11) NOT NULL AUTO_INCREMENT,
  parent int(20) DEFAULT NULL,
  aData varchar(50) DEFAULT NULL

I also have a secondary table:

TABLE MyTable2
  id int(11) NOT NULL AUTO_INCREMENT,
  id_table1 int(20) DEFAULT NULL,
  aData varchar(50) DEFAULT NULL

The id_table1 points at the id column in table1 it belongs to.


Now i ned to make two select that from any node (identified by id column in MyTable1)

(1) get all of its branches (or children).
(2) make another select that gives me all the rows of the MyTable2 that is owned by any of the just selected rows in MyTable1.

Regards, Paer.
0
I have two tables in a MySQL database with almost identical structure.

I want to copy the "from" table to the "to table" without having to mention every column by name.

The "to table", purposely, has one more column at the end that is auto increment.

Is there a way to "copy corresponding", so to speak? Either in phpMysql or in php code.

Thanks
0
In mysql database i created "leave" table:

CREATE TABLE `leave` ( 
`ID_LEAVE` int(11) NOT NULL,
`ID_WORKER` int(11) NOT NULL,
`BEGIN_DATE` datetime DEFAULT NULL,
`END_DATE` datetime DEFAULT NULL
)

INSERT INTO `leave` 
(`ID_LEAVE`, `ID_WORKER`, `BEGIN_DATE`, `END_DATE`) VALUES
(3, 26, '2019-03-20 07:00:00', '2019-03-21 15:00:00'),
(4, 22, '2019-03-20 07:00:00', '2019-03-20 15:00:00');

Open in new window


"Workers" table:

CREATE TABLE `workers` (
`ID_WORKER` int(11) NOT NULL,
`FNAME` varchar(20) NOT NULL,
`LNAME` varchar(20) NOT NULL
)

INSERT INTO `workers` (`ID_WORKER`, `FNAME`, `LNAME`) VALUES
(22, 'ALAN', 'FAST'),
(23, 'LEON', 'SPEED'),
(24, 'ADAM', 'GREEN'),
(25, 'DAVID', 'BUCS'),
(26, 'JACK', 'FAR'),
(27, 'ADAM', 'GAX'),
(28, 'ANDREW', 'WORM');

Open in new window


"Orders" table:

CREATE TABLE `orders` (
`ID_ORDER` int(11) NOT NULL,
`DESC_ORDER` varchar(50) NOT NULL,
`NUMBER_ORDER` varchar(30) NOT NULL
)

INSERT INTO `orders` (`ID_ORDER`, `DESC_ORDER`, `NUMBER_ORDER`) VALUES
(19, 'TEST', 'TEST'),
(20, 'TEST2', 'TEST2'),
(21, 'TEST3', 'TEST3'),
(22, 'TEST4', 'TEST4');

Open in new window


"Order_status" table (I'm sorry for that's not in order):

CREATE TABLE `order_status` (
`ID_STATUS` int(11) NOT NULL,
`ID_ORDER` int(11) NOT NULL,
`ID_WORKER` int(11) NOT NULL,
`BEGIN_DATE` datetime NOT NULL,
`END_DATE` datetime NOT NULL,
`ORDER_DONE` tinyint(1) DEFAULT NULL
) 

INSERT INTO `order_status` (`ID_STATUS`, `ID_ORDER`, `ID_WORKER`, `BEGIN_DATE`, `END_DATE`, `ORDER_DONE`) 

Open in new window

0
Environment:
New laptop:  Win 10
Old laptop:  MacOS High Sierra + Bootcamp (Win 10) on dead MacBook Pro

How to transfer entire XAMPP directory from a Bootcamp partition of a Mac to a Windows PC?

I can attach old laptop's hard drive to the new Windows PC via a USB3 cable and read the bootcamp partition on it (using HFS+ installed on the new laptop).

If I try to simply copy the XAMPP directory over, the databases are partly useable by Joomla on the Windows PC but I'm unable to log in as administrator on the sites which were in htdocs on the bootcamp xampp install (I'm guessing there are a number of issues with a direct copy approach such as path references in the xampp/php files on the bootcamp xampp which are invalid once they're on the Windows PC).

I can no longer bootup the mac (motherboard fried) to access windows on it in bootcamp and export the databases (which would be the normal way to transfer a db from one xampp install to another).

I've tried installing virtualization software on the new laptop but so far haven't been able to create a bootable MacOS in VMWare or Virtual Box.

There should be a way for me to create a fresh xampp install on the new windows laptop and to copy over just the database and tables I need from the old laptop's bootcamp xampp directory but I'm not clear on which files to copy over.  Presumably the Joomla sites in directories in htdocs could all be copied over without an issue (guessing) but the databases which those …
0
I am using the following code to create league tables which works great.
select 
    team, 
    count(*) played, 
    count(case when Home > Away then 1 end) wins, 
    count(case when Away> Home then 1 end) lost, 
    count(case when Home = Away then 1 end) draws, 
    sum(Home) Home, 
    sum(Away) Away, 
    sum(Home) - sum(Away) goal_diff,
    sum(
          case when Home > Away then 3 else 0 end 
        + case when Home = Away then 1 else 0 end
    ) score 
from (
    select hometeam team, Home, Away from footballtest  INNER JOIN Leagues ON CASE WHEN LEFT(footballtest.code, 4) LIKE '0%'  THEN right(LEFT(footballtest.code, 4), LENGTH(LEFT(footballtest.code, 4))-1) ELSE LEFT(footballtest.code, 4)END = Leagues.code where Leagues.Code = '102' and   footballtest.season = '19' and thedate < CURDATE() 
  union all 
    select awayteam, Away, Home from footballtest  INNER JOIN Leagues ON CASE WHEN LEFT(footballtest.code, 4) LIKE '0%'  THEN right(LEFT(footballtest.code, 4), LENGTH(LEFT(footballtest.code, 4))-1) ELSE LEFT(footballtest.code, 4)END = Leagues.code where Leagues.Code = '102' and   footballtest.season = '19' and thedate < CURDATE() 
) a
group by team
order by score desc, goal_diff

Open in new window


What I would like to do is only pull each teams last 5 Games and build the table from that to show current form.I am not sure how I could use Limit 5 here?

I am using MYSQL version 5.8 (With a host).

The code that would need changing is the following:

select hometeam team, Home, Away from footballtest  INNER JOIN Leagues ON CASE WHEN LEFT(footballtest.code, 4) LIKE '0%'  THEN right(LEFT(footballtest.code, 4), LENGTH(LEFT(footballtest.code, 4))-1) ELSE LEFT(footballtest.code, 4)END = Leagues.code where Leagues.Code = '102' and   footballtest.season = '19' and thedate < CURDATE()

Open in new window



As this currently pulls all results from footballtest.season = '19' for each team when I need the last 5 games played by each team in the season.

I have tried the following code but I get the error "Syntax error at WITH table_a"


Open in new window

0
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
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
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

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.