Improve company productivity with a Business Account.Sign Up

x

MySQL Server

47K

Solutions

22K

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 Experts,
We have a php app written that posts information to a mysql database. Everything works fine until we try and post more than around 50 records to our mysql db? 50 and less works fine but when we try to start posting 55 or more then it gives an error: Database Exception (#42000). What could be the issue?
0
Free Tool: Port Scanner
LVL 12
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Hello Experts,
I have a win 10 virtual machine with a MYSQL database installed.
on my physical machine, I have an application that uses the Mysql in the virtual.

All was working just fine  for months
then after restarting my physical machine, I cant connect to the database

the ODBC drivers say " can't connect " and I can't find why

Both machines (virtual and physical) are on
both have internet
using  port 3306

Please your advice.
0
Hello guys,

I have enabled slowlog in Mysql 5.6 and also set the long_query_time = 10 ( restarted the server ), after few days I come back to examine the log which seem weird that  I still found log that  below this threshold being logged and output in the  slowlog. So will this long_query_time = 10  equal to 10 seconds ?

# User@Host: epf[KIT] @  [*****]  Id: 14003
# Query_time: 0.000000  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 5
SET timestamp=1524108859;

# Time: 180419 11:34:19
# User@Host: epf[KIT] @  [*********]  Id: 14007
# Query_time: 0.031225  Lock_time: 0.000000 Rows_sent: 3  Rows_examined: 7
SET timestamp=1524108859;
0
I have following information  from show engine innodb status output. I'm a bit puzzle how  to calculate the values ? for example the Buffer pool size   524288 in Buffer Pool 0, is this written in bytes ? or there is a way to calculate the value here ?


----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 70363643904; in additional pool allocated 0
Dictionary memory allocated 1177057
Buffer pool size   4194304
Free buffers       3537347
Database pages     614648
Old database pages 226967
Modified db pages  38
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 851, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 606746, created 7902, written 1493885
0.02 reads/s, 0.06 creates/s, 9.42 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: 614648, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   524288
Free buffers       442772
Database pages     76229
Old database pages 28128
Modified db pages  9
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 114, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 74824, created 1405, written 246681
0.00 reads/s, 0.00 creates/s, 1.98 writes/s
Buffer pool …
0
using mysql and pdo is it possible to insert into one table and then insert into the second table inserting the id generated from the first query in one transaction?

INSERT INTO item (creatorId, name) VALUES (:m, "this is  a test);
INSERT INTO details ( creatorId, itemId, term, content ) VALUES ( :m, LAST_INSERT_ID(), "owner", :m );
INSERT INTO colors( creatorId, itemId, colors ) VALUES ( :m, LAST_INSERT_ID(), "green");

Open in new window


How can i refer to the first insert and get that ID?
0
hi,

for user of MySQL and MariaDB, what tools and way you all to debug performance down to query level. e.g. which part of the query slow everything down?

please share.

if move to RDS by AWS, still easy to debug ?
0
I have a MySQL table w/ the following columns:

ip_address
timestamp
fan1
fan2
fan3
fan4
fan5
fan6
fan7
fan8

The value of fan1 to fan8 cols can either be "failed" or "operational". This is reporting data via snmp OIDs to this table/cols.

I need an SQL query that gets a count/total of the row that it's reading where cols fan1 to fan8 have value "failed". So the total can be 0 to 8.  Normally I would use select count(*) as total, but that counts rows not cols. So I need the total columns value for the current row where fans have failed for that device.

Example:

row1:
ip - 192.168.1.1
timestamp - blahhhhhh...
fan1 - operational
fan2 - failed
fan3 - failed
fan4 - operational
fan5 - operational
fan6 - operational
fan7 - failed
fan8 - operational

So in the example above after running the mysql query the total should be 3 in the output of the query.

I'm not sure how to approach this. I tried doing something using SUM w/ IF statement but couldn't get it working.

Thanks.
0
Hi,

I recently modified xamppfiles folder global permission privilege from read-only to read & write. I soon discovered MYSQL stopped working afterwards.  So i went back and changed the permission privilege on the folder and sub-folders back to READ-ONLY, but mysql still won't come on.

I saw this line of code $cfg['Servers'][$i]['socket'] = '/Applications/XAMPP/xamppfiles/var/mysql/mysql.sock'; from an article online and pasted it, no luck.

Please any help to get MYSQL back up would be appreciated.

Log:
2017-11-14 13:58:23 16129 mysqld_safe Starting mysqld daemon with databases from /Applications/XAMPP/xamppfiles/var/mysql
2017-11-14 13:58:24 140736090022848 [Note] Using unique option prefix 'key_buffer' is error-prone and can break in the future. Please use the full name 'key_buffer_size' instead.
2017-11-14 13:58:24 140736090022848 [Note] /Applications/XAMPP/xamppfiles/sbin/mysqld (mysqld 10.1.28-MariaDB) starting as process 16275 ...
2017-11-14 13:58:24 140736090022848 [Warning] Setting lower_case_table_names=2 because file system for /Applications/XAMPP/xamppfiles/var/mysql/ is case insensitive
2017-11-14 13:58:24 140736090022848 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up to 20MB.

2017-11-14 13:58:24 140736090022848 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2017-11-14 13:58:24 140736090022848 [Note] InnoDB: The 

Open in new window

0
Currently, i have one table for all activities (events)

The activities have two unique "ids" one for entityId and one for typeId

Types include
1- user
2- team (group table)
3- company (group table)

entityId is either the group_ip from the group table or the  user_id from the user table

Would it be better to create a table where user and group activities are kept separate?

The problem is i would use a UNION to pull all the results from the activities into one list
0
SQLError-com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The driver was unable to create a connection due to an inability to establish the client portion of a socket.

This is usually caused by a limit on the number of sockets imposed by the operating system. This limit is usually configurable.

For Unix-based platforms, see the manual page for the 'ulimit' command. Kernel or system reconfiguration may also be required.

For Windows-based platforms, see Microsoft Knowledge Base Article 196271 (Q196271).
0
Keep up with what's happening at Experts Exchange!
LVL 12
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

hi,

I read this page: https://www.mysql.com/products/cluster/availability.html

and file:///C:/Users/swmtong/Downloads/mysql_wp_cluster_scalability.pdf


it seems that MySQL cluster already can do long distance replication, then why we still need MySQL replication? or data sync between nodes in the MySQL cluster already a Sync mode replication ?  

The NDB cluster I knew is about single shared storage and therefore, storage is the single point of failure. is it still true ?

by this page: https://www.mysql.com/downloads/

it seem NDB cluster is gone ? I can't find chargeable NDB cluster. Or MySQL cluster is referring to NDB cluster ?

The downloadable enterprise edition seems do not have cluster component, is that right ?
0
I have two tables, one is users, and second is family_list,

Tables:

users > id, name,surname,dateofbirth,address,contact  (id is autoincrement and primary key)
family_list > id, name, dateofbirth, user_id

I made relation, id primary key with user_id foreign key.

Now, I need php form to store data to multiple tables, where I can add user to table users and for family_list table I need to can add more than one record for one user. So, one user can have more childern in family _list table. I am using bootstrap. How to do that, PHP and Jquery?

If there is some tutorial for beginner.

Thank you
0
Guys,
I'm running Mysql 5.6  on windows server and I would like to know how  to setup db maintenance and optimization on weekly or monthly basis.
0
I have access system and that is using the access data base as backend . Now i want to connect the database with MySQL Server database . Is it possible and how much i have to be do changes  queries and some others . Please share with  me your experience or point of view  regarding to this matter.
there is also aready many Access queries running on the Access Database and how i can handle these access database queries in MySQL Databse
0
Can anyone tell me if MYSQL and MYSQL FOR EXCEL is the same ?
I work extensively with Excel and yesterday downloaded and installed MYSQL
from the Community site using Installer.
If it is then how do I add MYSQL to the addins in Excel ?
Many thanks
Ian
0
I have a website on a self hosted centos server. When doing a page speed test through google I get a message that it takes 5.8 seconds for the website server to respond where it is supposed to respond within 200ms. How do I get to the bottom of this load time?

We staged this website on another URL on a shared hosting server with much less resources but it performs much better.

WHM Centos Version : 68.0.36
Apache : 2.4.27
SQL : 5.6.38

Problem URL : www.luckystar.africa ( 5.8 server response )
Staging Server : www.madproducitons.co.za ( 800ms server response )
0
I'm importing a SQL file from MySQL v5.5 Linux to a clean install of v5.5.7 on Windows 10 MySQL Workbench 6.3 with the following error:

MySql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

root @ localhost does have password assigned, but MySQL Workbench seems not be using a password.

I'm running of my laptop, so removing the PW is ok if thats the way to go, but any input on the correct way around this issue greatly appreciated..
0
Hi,
Trying to move Mysql data  to new drive  which after changed the path  My.ini file, services won't be able to start up the services and found in the log and error as following

2018-04-09 07:54:47 1412 [Note] IPv6 is available.
2018-04-09 07:54:47 1412 [Note]   - '::' resolves to '::';
2018-04-09 07:54:47 1412 [Note] Server socket created on IP: '::'.
2018-04-09 07:54:47 1412 Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

 I also have given network services full rights in the location below.
What else that I missed here to start up again Mysql with new data directory ?

# Path to the database root
datadir = "F:/ProgramData/MySQL//data"
0
I am unable to migrate a database from a share server from godaddy to my dedicated server Windows 2012 r2.  I have tried it from the plesk site by cloning it and I have tried with the migration wizard on Mysql workbench.  Both have stated that my user does not have super privileges.  How do I arrange that?
0
Free Tool: Subnet Calculator
LVL 12
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

I am trying to link views  through an inner join both  fields both are varchar(22) but the join only works on two records.  Yet when I use the value from o the field from one view to search in the other it finds it, no problem.  I checked the collation in both tables for the join fields and they are both the same" "SQL_Latin1_General_CP1_CI_AS"  .  One of the tables is brought over as a view through openv query from mysql.

here is the code fro the open query:

SELECT     leg_id, CONVERT(varchar(10), leg_date) AS Leg_Date, LTRIM(RTRIM(CONVERT(varchar(22), Link))) AS Link, LEFT(CONVERT(varchar(10), Sorian_Num), 7) 
                      AS Sorian
FROM         OPENQUERY(tsr, 'select * from `xxxxxxx`.`TS_Link2_Charts_Sorian`') AS TS_Notes

Open in new window


here is the actual join:
SELECT     dbo.MICU_Times_Analysis.PRID, dbo.MICU_Times_Analysis.Date_Line, dbo.MICU_Times_Analysis.Link, dbo.Sorian_Confirmed.Link AS Expr1
FROM         dbo.MICU_Times_Analysis
JOIN           dbo.Sorian_Confirmed ON dbo.MICU_Times_Analysis.Link = dbo.Sorian_Confirmed.Link
WHERE     (dbo.MICU_Times_Analysis.AtDest >= '3/1/2018')

Open in new window


I am stumped
0
I'm trying to set up an old website on my local windows 10 machine!
I've setup IIS and added the classic ASP module and If I use non database ASP pages
it works fine.  I've installed MySQL and setup ODBC with the proper driver and can access and test
the database fine!  
Its when I try access the database via ASP that it fails! The code I'm using worked fine on the web server.

This is the code that gives the error!
   
     Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
     Recordset1_cmd.ActiveConnection = "DSN=netcode;UID=peter;PSW=1234;"    *** This is the line that fails ***

I've also modified the read/write permissions on the INETPUB folder and all subfolders to full control!
And have modifier the users to full control as well!  

thanks

Pete
0
Guys,
I would need a script on Mysql database that can be use to collect all global setting and also database setting, the users etc. If anyone here expert with Mysql  system administration nad have those scripts, please I appreciate eif this could be share with me.
0
When install Mysql enterprise monitor in windows server 2012  and at the end of the install , I had the error message as in the screenshot. Can anyone elaborate to me why I keep getting the same error ? I had several times uninstall and do the installation again. The same error still pop up.

The browser also not displaying  due to v problems with website certificate security.
MYSQL_ENT_Monitor.PNG
MYSQL_ENT_Monitor_UI.PNG
0
This is going to be a tough one to provide examples for... I have a MySQL 5.0 query similar to this:

SELECT a.field1, a.field2, b.field3 - bfield4 AS 'Balance', c.field5, c.field6, d.field7, e.field8 
FROM tablea a 
LEFT JOIN tableb b ON b.field1 = a.field4 
LEFT JOIN tablec c ON a.field6 = CONCAT(c.field1, c.field2) 
INNER JOIN tabled d ON a.field8 = d.field1 
INNER JOIN tablee e ON a.field6 = e.field3
INNER JOIN tablef ON f.field7 = a.field1 
WHERE 1 = 1 AND b.datefield IS NOT NULL

Open in new window


(It's going to be tough because I obviously cannot provide the data or actual tables/fields).  

(the 1 = 1 is because the SQL statement is being built through a menu and this ensures the WHERE isn't messed up in how it's coded).

All joins are joined to the primary table (a).

Now running the above query works fine.  Results returned in .21 seconds.

BUT, add a simple ORDER BY and it takes minutes (IF EVER - I'm still waiting) for the query to finish.

ORDER BY b.datefield

Open in new window


I tried creating an index on the b table, datefield but that didn't help.  Any ideas why trying to order would cause such a massive delay?

Much as I'd love to, upgrading the database is simply not an option.
0
When using Linux native AIO , are MySQL settings innodb_read_io_threads & innodb_write_io_threads used?
If settings are used with Linux native AIO, how can I determine the setting are optimal?

We are running MySql 5.5 on Redhat 7 using SSD drives
0

MySQL Server

47K

Solutions

22K

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.