All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

learn more… | top users | synonyms

0
votes
1answer
82 views

How to connect to mysql on a server

My uncle gave me access to his server so I could play around with mysql and php. He installed php and mysql on it created a user as well folder for me to play around in. I was wondering how to access ...
0
votes
0answers
30 views

How can i update two tables

On my server, after insert on one table, I want to update two other tables. How can I do this using trigger?
0
votes
1answer
500 views

MYSQL REGEXP for text not between parentheses

I'm looking to search a large MYSQL table for a string and only return results that aren't between parentheses. If my table looks like this: | ID |Data | | 1 |Some search ...
0
votes
1answer
142 views

Slow MYSQL performance due to sub queries

Here is my query: SELECT BAL,NAME, (SELECT SUM(DR) FROM transaction WHERE NAME=TNAME AND DATE < '2000-05-01') AS DR , (SELECT SUM(CR) FROM transaction WHERE NAME=TNAME AND DATE < '2000-05-...
5
votes
1answer
23k views

Recover MySQL database from data folder without ibdata1 from ibd files

My WAMP directory accidentally get deleted by another user. Only data folder in MySQLis available. And, in that only database-folders (folders in "\bin\mysql\mysql5.6.12\data\" with name of databases) ...
-1
votes
1answer
275 views

MS Access + MS SQL Server for 1160+ users - Is it worth it? (Please Help!) [duplicate]

Our school in Delhi (India) wants to develop a professional and robust in-house database (SIS) that should handle vast amount of student information, including admissions, registration, re-enrollment, ...
2
votes
1answer
132 views

How safe is it to widen a column in MySQL?

Apologies for the simple question but SQL isn't really my forte, and I want to be sure I don't shoot myself in the foot. A MySQL database I volunteered to help maintain has a table that looks like ...
0
votes
0answers
457 views

load_file() doesn't work but load data local infile works

I'm trying to load a non-regular file with MySQL: select load_file ('/proc/cmdline') It returns NULL So I checked the documents here, the file is readable, because as mysql user, I can execute ...
0
votes
1answer
68 views

Select,From,Join,Where within 1 query MySQL [closed]

Wondering if its possible to use WHERE within such a big query string? I cannot seem to achieve what I want once I add Where into the query. customer +----+------+------+ | id | type | name |...
1
vote
1answer
81 views

Delete in big table data all data, where foreign_id in other table didn't exist [closed]

Let's say i have two tables: table_a: id | article_id | text article: id | text table_a for example contain data: 1 1 "" 2 1 "" 3 1 "" 4 2 "" 5 3 "" and table articles such data:...
1
vote
0answers
15 views

MySQL: index statistics dropped when table dropped?

When a table is dropped, are its index statistics also removed? The reason I ask is that I was hoping to 'prep' the stats by running log playback via the percona-playback tool prior to cutting over ...
1
vote
0answers
69 views

“Binlog has bad magic number” - recoverying InnoDB tables

A system crash raised the error in the title. I've tried the suggestion here, but then I can't access any of my InnoDB tables. They are still there, but data is missing. The provided solution ...
0
votes
1answer
56 views

Replication in mysql with many servers

There are 3 shops. In each of these is a mysql server. I want all servers to synchronize their date if at least one server changes the date. I read a bit of literature and realized that I need ...
0
votes
1answer
176 views

Poor SQL performance with raid10

Maybe I'll try my luck here. Server Fault site was quite hostile for non-professional server user. I recently bought HP ProLiant ML310e Gen8 v2 server with 8G's RAM and 4 HP 1TB 6G 7.2k rpm SATA (3....
0
votes
1answer
484 views

Annonymous user has no privileges, but still able to show databases?

Output of SHOW GRANTS FOR ''@'localhost'; GRANT USAGE ON *.* TO ''@'localhost' However, when I log in using my default user account, which is later defaulted to an annonymous account by mySQL as ...
0
votes
2answers
132 views

Unusual issue with localhost:3000 after watching ruby on rails 4 tutorial: Lynda.com

I'm totally new to programming, and I've started my training by watching the ruby on rails 4 essential training tutorial at lynda.com. I got to the section where I was supposed to create a web server ...
2
votes
1answer
3k views

Can I execute an external process via MySQL on table change?

When using triggers, if an update is done to a table then a trigger is executed. This is very convenient. But what I would need is to execute an external script. Is it possible to configure MySQL ...
0
votes
1answer
7k views

Couldn't start MySQL

I looked in the other threads, but this did not resolve my problem. I am trying to start MySQL, but I am getting error as below D:\>cd mysql\mysql5.6.15\program\bin\mysqld --console The system ...
0
votes
2answers
154 views

Show Users That Connect To DB

We are using Maria DB (v5.3 I believe) in our production environment. One issue that we have, is that historically, the dev user (which has full rights and privileges) is used in all applications ...
0
votes
1answer
526 views

Why are InnoDB's index keys limited to 767 bytes?

MySQL's official documentation states about InnoDB: An index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix. The meaning of this ...
1
vote
1answer
71 views

Is there a public repo where I can contribute to the MySQL .NET connector?

We have found ourselves making some performance and scale improvements for the MySQL .NET driver from here http://dev.mysql.com/downloads/connector/net/ We know where to get the source in a ZIP ...
3
votes
2answers
242 views

Best Indexing Strategy for Query with Equality[A], Range[B], Group By[C], AND Order By[count(P)]

I have a poorly performing query: SELECT user_id, count(item_id) as count FROM table items WHERE category = 'magazine' AND created_at > 1384754400 GROUP BY user_id ORDER BY count(item_id) desc ...
3
votes
1answer
810 views

MySQL/MariaDB stored procedure compiles with typos

I just asked a similar question to this, but that turned out to be because MySQL column names are case insenstive and I was creating an ambiguity. I took the advice given and prefixed all my params ...
1
vote
1answer
509 views

MySQL: Why am I getting this warning?

I am calling the stored procedure below, and get this warning, is this warning a false positive or is my query unsafe? 1 queries executed, 1 success, 0 errors, 1 warnings Query: call User_Signup('...
1
vote
1answer
548 views

MySQL/MariaDB stored procedure inserts even with syntax error

createdAt has not been defined, but the query still runs when I call this proc and inserts a "0" date. Why, shouldn't it fail? DELIMITER $$ USE `MobiFit_Dev` $$ DROP PROCEDURE IF EXISTS `...
0
votes
1answer
36 views

How can I set a field when original lookup is NULL or isn't in the list?

I am trying to merge our HR data. I had to create my own tables to give a "regions" table DROP TABLE IF EXISTS testgiver.ldap_karen; CREATE TABLE testgiver.ldap_karen (uid VARCHAR (10) NOT NULL ...
1
vote
1answer
162 views

Sorting a list of records alphabetically: is there any reason NOT to let the database do it

I have a list of records that contain a column of arbitrary strings. I would like the application to display them sorted in some order (preferably alphabetically, since humans have to read the data). ...
2
votes
2answers
1k views

Why is my RDS mysql slow query log full of server restarts?

One of my RDS mysql databases is giving me warnings about the slow query logs taking up significant space. The logs are rotated once an hour, and contain a dozen repetitions of: /rdsdbbin/mysql/bin/...
0
votes
1answer
125 views

Would it be better to do a sepparate query instead of a left JOIN?

Only a couple of rows is returned when not using LEFT since there isn't always data in the associated table - recipe_preparation. This is one of a couple of tables with the same "issue". SELECT r.id, ...
1
vote
1answer
65 views

SELECT showing two COUNTs

I had this table: tblA id | name | value 1 nameA 1 2 nameA 0 3 nameB 0 4 nameC 0 5 nameC 1 6 nameC 1 As you see, Value can be 1 or 0 I need to perform a query ...
0
votes
1answer
476 views

How to remove duplicates in col1 and get group MAX in col2? / mysql

Given the following table: col1 Col2 A 1 B 1 C 1 B 2 B 3 A 0 C 5 how do I write a SELECT query (I just assumed has to be SELECT) that returns the unique items of col1 and the ...
1
vote
1answer
172 views

Please suggest my.cnf configuration based upon mysqltuner findings

I have been facing a lot of slow browsing on my site nerdeky.com. My server administrator suggested me to use mysqltuner.pl to find and tune my databases. Can anyone select me what should be the good ...
0
votes
0answers
71 views

mysql slow optimization of small tables

I'm having a problems with mysql perfomance. Optimization of small tables takes too long time. I increased tmp_table_size variable: mysql> SHOW VARIABLES LIKE '%_table_size%'; +--------------------...
3
votes
2answers
188 views

MySQL is giving weird result

I don't have this kind of ID in my table, still MySQL is giving me a result. This same result is giving when i place in my php code.
1
vote
1answer
630 views

DB disk space requirements comparision between MySQL versions

I have 2 labs where MySQL is installed. DataBases are created and deleted dynamically by certain internal processes. InnoDB is the Engine used. In one lab, MySQL version 5.0.77 is installed. The DB ...
2
votes
1answer
311 views

Does MySQL do UPDATE in place on NOT NULL column?

Assume I have a table like this in MySQL: CREATE TABLE Foo ( Key INT NOT NULL , MyCol INT NOT NULL PRIMARY KEY (Key)) And I execute a statement like this on: UPDATE Foo SET MyCol = 13 WHERE Key = ...
0
votes
0answers
44 views

Trouble migrating a MySQL database to MS SQL 2012 using the migration assistant

I am trying to migrate a MySQL server database to Microsoft SQL Server 2012. I converted the schema and synchronized the databases, but when I try to do the migration, it doesn't work and I get this ...
0
votes
0answers
2k views

InnoDB: Tablespace is missing for table

One of our database table tblpages seems to be corrupted. When I select the table, its says show full fields from `favc`.`tblpages` Table 'tblpages' doesn't exist So I thought repairing it will be ...
2
votes
2answers
298 views

MYSQL joining 3 tables with left join

I have these tables: pupils: (id_pupil, name, surname, email, user, pass, level, class) incidents: (id_incidents, date, time, type_incident, comments, id_pupil, id_user, subject, id_trimester) ...
2
votes
2answers
3k views

What are definer and invoker rights in database, Oracle and MysQL?

What are definer and invoker rights in database, Oracle and MysQL? I just don't get it
0
votes
0answers
560 views

Alternative to TEMPORARY tables capable of re-opening in a query?

I use TEMPORARY table for calculation of temporary data, but TEMPORARY tables in mysql has a big limitation, which made it useless for me. A table cannot be re-opened in a query. This mean I cannot ...
0
votes
3answers
79 views

Should frequently accessed tables containing large blobs with one-to-one relationships be normalized and columns split into two tables?

I have a frequently accessed table containing 3 columns of blobs, and 4 columns of extra data that is not used in the query, but just sent as result to PHP. There are 6 small columns (big int, small ...
4
votes
2answers
4k views

I dropped MySQL's `performance_schema` database, how can I create it?

While fixing a ibdata/log problem, I accidentally dropped my performance_schema database, I'd like to create a new one. mysql> SHOW VARIABLES LIKE 'perf%'; +----------------------------------------...
1
vote
2answers
3k views

MySQL: How to drop all tables starting with a prefix?

In my database there is a lot of tables starting with _elgg , now I want to drop all tables with this prefix. Can anyone give me a solution ? Thanks in advance!
1
vote
2answers
829 views

How much RAM should you allocate a MySQL replication slave?

Our MySQL master is allocated 24GB of RAM (innodb_buffer_pool_size = 24GB), but was curious if there was some standard amount to allocate to a replication slave? Right now the slave is just used to ...
0
votes
1answer
2k views

SQL Server to MySQL sync, using Workbench, FreeTDS, and iODBC on 10.9

I've been struggling to get the MySQL Workbench to behave properly to complete the following: With Mavericks (OS X 10.9), connect to a SQL Server and create a migration job to a MySQL server. The ...
0
votes
2answers
92 views

Is my JOIN syntax not valid? And have I understood JOIN correctly?

I've been reading up a bit regarding JOINs. And i just noticed that I've been writing the syntax a little bit different. Although I don't think there's any problem with it, I would like to hear your ...
3
votes
2answers
4k views

MySql InnoDB tables are missing after restarting

I had a MySql database with a few InnoDB tables. After restarting the server, and starting MySql Server again, I connected to the MySql Server using MySql Workbench, and couldn't see the database at ...
0
votes
0answers
87 views

Order before Group

I have this query: SELECT HOTEL.hotelname AS NAME, HOTEL.`boardbasis` AS board, HOTEL.`engine`, HOTEL.`itemcode`, Group_concat(Round(((hotel.cheapestprice / 2) + flights.price)...
1
vote
1answer
589 views

Does MySQL load the entire row into memory, or just the columns used in WHERE, ORDER BY and GROUP BY?

I have a table that isn't very large row wise (in the 100ks range), but that contains a lot of raw data that is very large in size. Despite having a relatively little number of rows, it's around 1.5GB....