Tag Info

New answers tagged

1

Your trigger tries to do something that cannot be done in MySQL. You cannot use an SQL statement (DELETE, in your case) on the table that is associated to the trigger. You will get an error like this: ERROR 1442 (HY000): Can't update table 't' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. ...


1

Good answer from Rolando. In addition -- Triggers should not be used for logic, because a couple of inter-relating triggers and things get confusing very fast. A nice set of instructions can get across the business logic than a bunch of hidden logic in the database. There are also limitations on triggers with respect to the table they get triggered from ...


2

The main configuration settings are in my memory blog. Most important is 20% of RAM for key_buffer_size. (However, it does not need to be more than the sum total of Index_length of all the SHOW TABLE STATUS. This will also do the arithmetic: SELECT ENGINE, ROUND(SUM(data_length) /1024/1024, 1) AS "Data MB", ROUND(SUM(index_length)/1024/1024, 1) ...


2

Asuming you have: CREATE TABLE `new_table` ( `id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT, `create` text, `proname` varchar(45) DEFAULT NULL, `prodb` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; Here is the query: select COLUMNS.* from information_schema.COLUMNS where ...


1

Query: SELECT COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME = 'sample' AND TABLE_SCHEMA = 'database_name' Limit 3; Note : Here 'sample' is table name and 'database_name' is database schema name.


0

In phpMyAdmin Version information: 4.3. upward. Click on the database in question. It shows the structure and should shows or list all the tables. Scroll down to the bottom and click 'CHECK ALL' to check all the tables in the database. Then click on the Object Creation Options, scroll to REPLACE TABLE PREFIX and click this. Then run the query by clicking ...


0

I will try to answer the question, but I suggest you listen to comments saying you should rethink your design a bit. By my guess it is "order by .. limit" optimization gone wild - when you check userId2, mysql sees low cardinality, so thinks that going by the defined order through primary key will find one matching item really fast, but because some skew on ...


0

Get 5.6.3 (or later). As I understand it, these need to be applied to the Replica: SET GLOBAL innodb_file_format=Barracuda; SET GLOBAL innodb_file_per_table=ON; ALTER TABLE tbl ROW_FORMAT=DYNAMIC; -- or COMPRESSED That should allow you to keep 255 (instead of 191), thereby avoiding the truncation.


1

Yes you can use innodb_file_per_table = 1 for your slaves. Additionally take below points in your consideration If you have same version on MySQL on both Master and Slave. You can directly take backup and setup replication from Master to Slave by adding innodb_file_per_table = 1 on slave in my.cnf Please have a look at How to set Master Slave ...


1

They do the same thing, but there is a difference and a grave danger. Here is the difference: OPTIMIZE TABLE is done in the mysql client or in a mysql session. myisamchk is a utility, not a client. Thus, there are no table locking safeguards. Therefore, you should never run myisamchk -r -a -S in a live system. Otherwise, you can quickly (in fact, ...


1

Since you have mysql.general_log already converted to MyISAM and indexed, I would recommend making snapshots of that table based on a timestamp range. What I mean by snapshot is a temp table that contains just the events you wish to mark. Suppose you want to log entries from the last 10 minutes. This Dynamic SQL should do it for you : SET sql_log_bin = 0; ...


0

What catches my eye here is the fact you are calling a DML statement (DELETE FROM tb_agenda WHERE dt_agenda < NOW() inside the AFTER INSERT trigger of the same table. That's actually unhealthy for MySQL although perfectly legal to do so. Years ago, I wrote about hazards of calling extra procedures inside a trigger Jan 13, 2012 : Call a stored procedure ...


-1

You need to use 1 variable because when you run the second NOW() could be a different value of the first NOW(). Try this: CREATE DEFINER = CURRENT_USER TRIGGER `DB`.`TABLE_INSERT_DELETE` AFTER INSERT ON `TABLE` FOR EACH ROW BEGIN SET @CR_DATETIME=NOW(); INSERT INTO tb_realizacao (dt_agenda, titulo, ...


1

In case of two same sums, if you had table like this ... +------------+---------+ | P_ID | Runs | +------------+---------+ | 1 | 100 | | 2 | 100 | | 3 | 35 | | 1 | 90 | ...


0

You could RUN this query: SELECT p.name as PlayerName, SUM(r.Runs) as TotalRuns FROM Players AS p LEFT JOIN Runs AS r ON (r.pid = p.pid) GROUP BY p.pid ORDER BY TotalRuns DESC LIMIT 0,1; You will get 1 row with the player who SUM(r.Runs) max. Otherwise you should think about add ORDER BY with the column that you want to bring first, because if ...


4

Creating sample tables DROP TABLE Runs; CREATE TABLE Runs(pid INT , Runs INT); INSERT INTO Runs VALUES(1, 100),(2, 45 ),(3, 35 ),(1, 90 ),(2, 85 ); DROP TABLE Players; CREATE TABLE Players(pid INT,Name CHAR(30)); INSERT INTO Players VALUES(1,'Dhoni'),(2,'Sehawag'),(3,'Sachin'),(4,'Dravid'),(5,'Kohli'); MariaDB [world]> SELECT * FROM Players; ...


4

From here: (My emphasis below) The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other ...


7

With the table of separate runs, making the sums will mean reading entire table each time so for performance it would be good to have the sums precomputed somewhere, maybe even in Player table, with updates either periodically (daily, each hour) or on insert/update in Runs table (trigger, ORM event or other ways). But that may be not possible for your case, ...


1

Here's your query: SELECT T.location_id, GROUP_CONCAT(T.service_id) AS serviceids FROM `Table` AS T GROUP BY T.location_id HAVING serviceids IN ('2,3'); You could use ORDER BY inside the GROUP_CONCAT(): SELECT T.location_id, GROUP_CONCAT(T.service_id ORDER BY service_id ASC) AS serviceids FROM `Table` AS T GROUP BY T.location_id HAVING ...


1

since your date will grow rapidly. I would suggest to separate all the 'character field' into dimension and link with numeric values in the fact table which will only contain numeric data. This approach will be extremely fast and save you tone of space. also, depending on the partitioning configuration you can make it more efficient for retrial create ...


1

You say 100 measurements once a day. I'd have two tables as follows: CREATE TABLE Measurement ( Measurement_ID INTEGER PRIMARY KEY, System_ID INTEGER, -- FK into the System table Measurement_Date DATE (or DATETIME depending), Measurement_1 M1_Datatype, .. .. 100 lines Measurement_100 M100_Datatype ); CREATE TABLE System ( System_ID INTEGER ...


1

Of the two options mentioned, without any doubt, the first is better. MySQL is fine at handling large tables. There is no reason whatsoever to break the data into separate "equivalent" tables. In fact, having multiple tables with the same layout is usually an indication of a poor database design. For performance, you can then add indexes on the system ...


0

Here's a kludgy way: SELECT location_id, GROUP_CONCAT(service_id) AS service_ids FROM tbl GROUP BY location_id HAVING service_ids = '2,3';


1

After much floundering around with the information_schema.tables table, I finally discovered the answer to your problem - update time doesn't work for InnoDB. See the points here by a.out and ajacian81. The only way you can do this is to implement logging tables (as suggested by Bill Karwin, a bit of a MySQL/database guru). The only other thing that I can ...


1

This error means you already have an BEFORE INSERT trigger on companies table. If it is the same trigger (meaning trig_companies_table_insert) that you created earlier and now you want to replace it then you need to drop it first: DROP TRIGGER trig_companies_table_insert; DELIMITER $$ CREATE TRIGGER trig_companies_table_insert BEFORE INSERT ON ...


1

It means you already have a trigger for BEFORE INSERT ON companies. (action time = BEFORE, event = INSERT) You can't have two of them for the same event and action time. You can fix it by editing the existing BEFORE INSERT trigger.


0

Look for Amazon instructions. With RDS, the instructions are roughly: Upgrade the Slave first. Replication works when the Master is 'older'. Failover, but do not turn on the Slave. Upgrade the Slave (was Master) Turn on replication (START SLAVE) Wait for replication to catch up before using the Slave.


0

I have the following solution, which gives the desired result, except the values for the days that there is no entries for: set @total=0; set @sku_name=''; SELECT date, sku, total, CATEGORY FROM ( SELECT date, sku, @total:=IF(@sku_name=sku, @total, 0)+qty AS total, @sku_name:=sku FROM ( SELECT date, sku, SUM(qty) qty FROM ...


2

It is not a MySQL thing - MySQLTuner itself cannot see more memory if it is running with 32bit perl interpreter.


0

I found this article http://d2-systems.blogspot.co.uk/2013/06/enabling-mysql-multi-source-replication.html which covers the process of aggregated synchronisation.


2

It's possible that a transaction may be partially committed leading to unresolvable corruption on reboot. This is especially the case if you use MyISAM tables - InnoDB tables have a double write buffer which is a form of Write Ahead Log (WAL). A good discussion of the concepts is available here and here. Note that from the Percona article we have "the ...


0

There's a response for this here: MySQL - mysqldump. preserve update_time and modify_time As @RolandoMySQLDBA said: There is no way to reload the INFORMATION_SCHEMA manually. All the tables are dynamic entities.


0

Social networks -- by definition -- involve real-time interaction between people. So traditional approach to federation and partitioning, that is storing all related data in the same partition and limiting the scope of transactions and JOINs to a single partition, only works in limited cases (e.g., a social network that only lets you interact directly with ...


2

Check Codd's rules - there's no mention of "ordering". You can order by a PRIMARY KEY (in the SELECT statement) but you aren't forced to. If you use ORDER BY in your SELECT for your INSERT and use MySQL's AUTO_INCREMENT to INSERT into your target table, then you should have the target table's PK be "ordered" by your criteria. You should be aware that the ...


5

A table is, by definition, an unordered bag of rows. There is no guarantee that if you say SELECT * FROM table you will get the rows back in the same order you inserted. Think about throwing a bunch of popsicle sticks on the ground while blindfolded; now take off the blindfold and tell me which one hit the floor last. No ORDER BY is essentially telling the ...


3

Use a composite index: INDEX(confirmada, codTipoTransaccion, idTransaccionOriginal, idTransaccion) The columns can be in any order, so I recommend shuffling them to meet other need(s) and/or facilitate removing some other index from t2. If you need to discuss this further, please provide SHOW CREATE TABLE for each table. Edit For JOIN ... ON ...


1

If you sort the records according to the PRIMARY KEY before loading them, it will run faster. If you are "replacing" an existing table, do this to avoid essentially all downtime: CREATE TABLE new LIKE real; -- with all PRIMARY & UNIQUE keys Load new with data ALTER TABLE new ADD INDEX ...; -- add all secondary indexes Optionally sanity check the data ...


2

Unfortunately, no there isn't because ALTER TABLE ... DISABLE KEYS disables nonunique indexes For MyISAM tables, key updating can be controlled explicitly. Use ALTER TABLE ... DISABLE KEYS to tell MySQL to stop updating nonunique indexes. Then use ALTER TABLE ... ENABLE KEYS to re-create missing indexes. MyISAM does this with a special algorithm that is ...


1

Well, what is your data retention policy? Do you have cheap storage to put them on? Can you test restore them and ask the business owner if they need to keep it? If they do, do they have cheap slow or available storage to put it on? If not, are they open to you putting it on Amazon Glacier or something like that? This is really a business question. ...


0

Your question is probably going to get locked soon but I would venture that you should look at the MySQL community and website to start. Most likely you'll need some of their official certs, then you'll need experience with the product. There's some trainers who don't have prod experience and just teach, but you'd probably be better off with some real ...


0

It is impossible to do this using permissions only . The only way is to create a stored procedure as described here If you want to avoid stored procedures, a workaround is: GRANT ALL PRIVILEGES ONtestuser_%. * TO 'testuser'@'%'; (as suggested here); however, this has the problem that the users must then be very careful in naming their databases. For ...


0

You are far better off maintaining a foreign key constraint if your data warrants it. MySQL will automatically create an index - the FK must reference a UNIQUE KEY (obviously, can be PRIMARY - from here). See here for a host of reasons to as to why applying data constraints in the database is a VERY good idea. The index on the T table will increase ...


0

That's not how it works. Do it correctly first, then optimize. Use a foreign key if you need a foreign key. Then measure performance. Find the hot spots. Optimize those. If the foreign key happens to be large performance drag (doubtful) then remove it. Also, you didn't ask "What will be the impact of having incorrect data?"


0

Why don't you make a third table called projects_payment and you use to make a relationship between projects and payments and you could have 1:N relation. projects_payment table should be like: projects_payment: project_id (PK) (FK to project.id) payment_id (PK) (FK to payment.id) modification_date (You can use for amount date) Any other field. ...


2

I've made a function where you just put the string and the output will be in Capital Letters: DELIMITER // CREATE DEFINER=`root`@`%` FUNCTION `LCAPITAL`(eCADENA VARCHAR(150)) RETURNS varchar(150) CHARSET latin1 DETERMINISTIC BEGIN DECLARE vPOSICION INT DEFAULT 0; DECLARE vTMP VARCHAR(150) DEFAULT ''; DECLARE vRESULTADO ...


1

Stored procedures are stored in database mysql. Innobackupex copies it by default. Make sure database mysql is restored with other databases.


0

You should check mysql user permission, specially the host field. Mysql documentation


5

You can create secondary indexes on slave whenever required. There is no issue in creating secondary indexes (Non Primary and Non Unique). If you create Primary and Unique on slave there will be chances that your replication will fail with duplicate key error on slave. Take a example I have a table testRepl on master without having any index on it and has ...


5

This is due to MySQL Master and Slave version mismatch The message below is clear, and confirms the master/slaves run different versions. The variable SERVER_UUID is not supported with MySQL 5.5 but is supported with MySQL 5.6 2015-06-29 11:01:01 8026 [Warning] Slave I/O: Unknown system variable 'SERVER_UUID' on master. A probable cause is that the ...


2

In order to take the backup of MySQL Server Objects i.e routines / triggers / events you can do like below mysqldump -h$MySQLHost -u$YourUser -p$YourPassword --no-data --no-create-info --routines --triggers --events $Database > MySQLStoredProcedures.sql The above will backup only the database objects , no data , no create table etc.



Top 50 recent answers are included