Tag Info

New answers tagged

0

You can use fastbit bitmap index, from mysql, using fastbit UDF. Check this link https://github.com/greenlion/FastBit_UDF About these UDF Functions and FastBit FastBit is a data store which implements WAH (word aligned hybrid) bitmap indexes. These UDF create, modify and query FastBit tables. The UDF treats a single directory on the filesystem as ...


0

The compound INDEX(field2, field1) would be a "covering" index. This would avoid a huge number of lookups, thereby making it much faster. Note: This will not work if you also SELECT field3. (InnoDB and MyISAM would both benefit in this case.) Keep your UNIQUE(field2) because of the uniqueness constraint.


0

I have discussed situations like this before in these posts: May 10, 2013 : In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table Sep 22, 2013 : How to create root user after running mysql_install_db? In your case do this STEP #1 Add these lines just under the [mysqld] group header in my.ini [mysqld] skip-grant-tables ...


0

Lennart's answer explains why your query fails and how to fix it so it is syntactically correct. About the additional problem that you "want to display the output regardless if one of the joined sub queries doesn't have any rows", you could rewrite the query and simplify it: SELECT AVG(CASE WHEN es.DateID = 'Jan2015' THEN sf.score ELSE NULL END) AS ...


1

In a way, it does not matter since you should purge the binlogs. But first, there are two questions -- why do you have the binlog, and are you using SBR or RBR? If the binlog is for incremental backups, then you need it (them) to be big enough to last until you take the next backup. This might be a few GB per day for a "busy" system. If you have Slave(s) ...


0

Mr. jynus said correct the general_log should not be enabled on production server,because it logged all the operations which is occuring into the MySQL database due to that it required a more device space so follow the instructions which is provided by the Mr.jynus your problem will be short it out.


1

Unfortunately, MySQL does not allow for filtered indexes (otherwise you can just filter on open tickets only). The next best thing is to do something similar to the two table approach you describe: Partitioned tables.


1

Below is what you need CREATE TABLE customers ([id 1] int, [status] varchar(4), [id 2] int, [name] varchar(4), [category] varchar(6)) ; INSERT INTO customers ([id 1], [status], [id 2], [name], [category]) VALUES (001, 'open', 011, 'john', 'person'), (001, 'open', 011, 'john', 'male') ; select * from dbo.customers ---- You have to use ...


1

A version of JD Schmidt's answer, but without the awkwardness of an extra column: CREATE TABLE foo ( FieldA INT, FieldB INT ); DELIMITER // CREATE TRIGGER InsertFieldABNotNull BEFORE INSERT ON foo FOR EACH ROW BEGIN IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '\'FieldA\' and \'FieldB\' cannot ...


2

You need to specify whether you want an left, right or full outer join. A left outer join is the same as left join. Assuming this is what you want: [...] FROM (SELECT subfactor.score, dateID, EmployeeID FROM employeescore LEFT JOIN subfactor ON employeescore.SubFactorID = subfactor.SubFactorID LEFT JOIN factor ON ...


0

This can be a one-liner: RETURN EXISTS ( SELECT * FROM tbl WHERE ... ). You get 0 or 1, not NULL. At which point, why even create a function? The query to run without the function: SELECT EXISTS ( SELECT * FROM tbl WHERE ... )


-4

Remove the keyword OUTER. MySQL uses the ON/WHERE clause to decide between INNER and OUTER functionality. There is another problem... If Employee#1 was not there one of the months, the result will be the empty. I suggest that you use a 4th subquery (first) to find all employes, then LEFT JOIN to each of the existing subqueries. But then you may have ...


0

You can use ssh to securely forward the MySQL port remotely. For example, if you execute: ssh -L 3307:localhost:3306 <ssh-user>@<remote-server> You will be able to connect to the local port 3307 as if MySQL was running there. All connection information will be tunneled though ssh, so it will be sent securely. That way you can connect using ...


1

The general log should never be enabled on a production server (under normal circumstances), as it logs absolutely all queries (reads and writes) performed on the server, taking a lot of disk space and query performance with it. The general log is only useful for debugging issues on the server. You should turn it off on a live server by executing: SET ...


1

All statements that hit the server are stored inside the general log. So general log is duplicating your dump. The general log should be used only for debugging only, and it should be activated for the time required to spot a problem. Try to disactivate general log.


1

I've realised that using an alternative query structure helps to speed this up. UPDATE table1 SET is_active = 1 WHERE id IN (1,2,3,4,...) Works much quicker and reduces the script file size to 11kb from 114kb and also reduces running time from 40 secs to just under 2 secs


0

Any way you do this, you're probably going to have a password saved somewhere. Even MySQL 5.6 login-path is easily decryptable by anyone with the motivation. That warning said, this would be an easy solution. In your environment script (eg ~/.profile or ~/.bashrc), set alias mysql='mysql -uUser -pPasswd -hHostname' (putting in your desired User, Passwd, ...


0

The Nawaz answer is correct. I have little to add. I suggest you do some attempts with the environment variable MYSQL_TEST_LOGIN_FILE to crate a new configuration file, just in case the default file is corrupt. Use also --no-defaults to skip .cnf files. If the password contains the char #, mysql login-path doesn't work. $ export ...


1

You can set it to 1 with this command: mysql> ALTER TABLE tbl AUTO_INCREMENT = 1; More info in the mysql manuals, here: https://dev.mysql.com/doc/refman/5.6/en/example-auto-increment.html


0

write table names in a file and use following command to take backups for i in `cat table_name_file` do mysqldump -uroot -p -T $i done Hope it helps


0

You are missing the single quote closure after motherboard in both of your queries AND value != 'Motherboard AND AND a.value != 'Motherboard The first query can be more organized as SELECT id_product FROM Atr_basic WHERE attribute = 'Product Type' AND category = 140 AND value NOT IN ( 'Motherboard', 'Intel Motherboard')


0

IM passing input parameter in_branchId into pivot Mysql Query DROP PROCEDURE IF EXISTS leavemanagement.SP_ViewAttendance $$ CREATE DEFINER=root@localhost PROCEDURE SP_ViewAttendance( IN in_attendance_start_date DATETIME, IN in_attendance_end_date DATETIME, In in_branchId varchar(100) ) BEGIN SET SESSION group_concat_max_len = ...


1

I've seen it done both ways. If you want to bulk up your database size just start using BLOBS. Pro's for storing documents in your database you're already storing your data there so why not a little more? (Only it's a lot more) a certain simplicity of access. Any request to read only has to go one place to access the document or thing Cons for storing ...


-1

If you want to exclude MySQL internal databases, SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema NOT IN ( 'information_schema', 'performance_schema', 'mysql' ); No need to add 'ORDER BY'


1

QUERY #1 (Separate Columns) SET @db = ''; SELECT db,tb FROM (SELECT @tb := IF(@db=table_schema,table_name,'') tb, IF(@db=table_schema,'',table_schema) db, (@db := table_schema) unused FROM information_schema.tables) A; QUERY #2 (One Column) SET @db = ''; SELECT CONCAT(db,' ',tb) DBTB FROM (SELECT @tb := IF(@db=table_schema,table_name,'') tb, ...


2

Use the information stored in INFORMATION_SCHEMA: SELECT table_schema, table_name FROM INFORMATION_SCHEMA.tables ORDER BY table_schema, table_name; table_schema is the database name. To show also the databases without any tables: SELECT s.schema_name, t.table_name FROM INFORMATION_SCHEMA.schemata AS s LEFT JOIN INFORMATION_SCHEMA.tables AS t ON ...


1

Perhaps doing a COUNT is better since it must be a number always DELIMITER $$ DROP PROCEDURE IF EXISTS `USER_EXISTS` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `USER_EXISTS` (IN `GIVEN_USERNAME` VARCHAR(64) CHARSET utf8mb4) BEGIN SET @User_exists = 0; SELECT COUNT(1) INTO @found FROM `dbname`.`tablename` WHERE `username` = ...


0

Try with union insert into table2(column1) select column1 from table1 union select column2 from table1 union select column3 from table1


2

Yes you can. Or no you can't. It doesn't really matter. If you think about how DBMS1 is going to have to deal with a commit and/or rollback after it has, via your UDF function in which you have hidden an update to another, external, DBMS2, then you should realize that this is a very bad idea no matter how you toss or turn it, because somwewhere somehow it ...


1

If you have a logical backup generated with mysqldump, you should load it into mysql 5.5 without issues. You could check that logical backup was generated with the options --triggers --events --comments --routines to be more complete.


0

IF((select REPLACE(name,'.','') from #tmpDots) = (select name from #tmpNoDots)) PRINT 'TRUE' ELSE PRINT 'FALSE' Replace the dots with the space of the characters that are worrying you using the Replace() function in SQL and this should do it. You can also embed this logic in some SQL function or in an SP. Let me know if more help needed.


1

You can try to use replace function: mysql> select replace("A.B C", ".", "") = "AB C"; +------------------------------------+ | replace("A.B C", ".", "") = "AB C" | +------------------------------------+ | 1 | +------------------------------------+ 1 row in set (0.00 sec)


1

I am adding an additional answer because Giovanni's one is completely right, but it only defines why scaling, how to do HA on the master and on the slaves, and how to split read queries among the slaves from an architectural point of view. I think OP question was more about how to do the read-write split itself. Then answer depends on the software stack ...


1

A good solution is to define two datasources for the applications. One datasource pointing to master is for writing. One datasouce pointing to slave node is for reading. The applications must be aware that data can be delayed with respect to the master. There are applications that could tolerate a slight delay on data, and other applications that must be ...


0

How about creating table with autoincrement option? create table t ( id int not null auto_increment, v varchar(100), primary key(id) ) auto_increment=101; mysql> insert into t (v) values ("abc"); Query OK, 1 row affected (0.00 sec) mysql> select * from t; +-----+------+ | id | v | +-----+------+ | 101 | abc | +-----+------+ 1 row in ...


0

Actually, yes you can start an empty table's auto_increment from 101. SAMPLE CODE use test drop table if exists fizzydrink; create table fizzydrink (id int not null auto_increment,num1 int,num2 int,primary key (id)); show create table fizzydrink\G alter table fizzydrink auto_increment = 101; show create table fizzydrink\G insert into fizzydrink (num1,num2) ...


0

You can just populate it with all NULL values except the fields which are declared as NOT NULL. For those NOT NULL fields you can use empty strings. If it's just 100 rows you can do it with an INSERT statement or just add those rows manually with MySQL Workbench GUI.


0

I thought about Master / Slave setup, but I'm not sure how can I prevent deletes from replicating to slave. You can't do that. Replication is intended to replicate as much as possible to keep the slaves as accurate copies of the master(s). You might want to replicate to another machine so that you can have a copy to run the copy-to-archive archive ...


1

1) Yes 2) it's not going to be a problem but of course it depends on the problem itself. Lets use the term - incident. Problem is already a problem by definition. Solve incidents, analyze the cause , prevent the problem!!! ;) 3) in case of an error, try to think what caused it. Nothing to worry about specifically in my opinion Try to strictly separate ...


0

Suggestion that may help you fix this issue: 1) take a backup from the data directory example copy data folder to data_old folder you may need it in-case something went wrong 2) take a backup from my.ini file 3) copy content of the default my.ini and past it inside the one you are using ( dont forget to edit basedir , datadir and port number OR edit the ...


0

SELECT `Label`, `Value`, ID FROM table1 LEFT JOIN table2 USING(Label, Value);


0

Assuming the system handles only a few actions per second and only millions of ids, either flavor of schema design should work well enough. I can think of no strong argument for, or against, splitting the confirmations off into a separate table -- performance, sparseness (or lack of) in the Confirmations table, 1:1 relationship, coding convenience, purging, ...


1

"Is it possible to do so? The trigger will insert some rows into a unique table that exists only on the slave." yes, you can A lot of warehousing architectures are based on the slave only. The title of your question is wrong. Change replica DB with SLAVE ONLY


0

For InnoDB on a RAM disk: innodb_flush_log_on_trx_commit = 1 innodb_doublewrite = OFF batch inserts (100-1000 per transaction) probably other settings Better yet, use ENGINE=MEMORY, not InnoDB -- Either way, your data will be lost in a power failure. If you need high speed, multi-threaded ingestion, see http://mysql.rjweb.org/doc.php/staging_table


0

All right, the config change that helped me was table_open_cache = 250 I've thrown in table_open_cache_instances = 10 to possibly improve performance because the cache size became smaller compared to default settings. And just in case, as I'm too lazy to go back and forth restarting the service there existed a couple of other deviations from the ...


0

Try this code SELECT users.id AS id, users.name AS name, users.surname AS surname, massages.textAS text, massages.date AS date FROM users left join massages on massages.id = ( SELECT MAX(id) FROM massages as m WHERE (m.recipient='?' AND m.sender = users.id) OR (m.recipient=uesrs.id AND m.sender = '?') ) ...


0

I would load all data into a single table. I find it easier to discover duplicates and such, relational analysis, creation of tables and normalizing. For the number of records you indicate, I would usually just leave as a single un-normalized table and remove duplicates while attempting to discern and define the appropriate table key and foriegn keys for ...


1

If you have a good reason to have multiple tables (i.e. the data has different columns), then of course multiple tables is best. But if the data all belongs in the one table and you're just debating splitting it up due to its size, don't.


0

Not an "answer", but steps for debugging... All of these need to agree: tag on HTML character_set_client, _connection, and _results the WP setting (I don't know what is) the CHARACTER SET on the columns of the relevant tables, or if nothing mentioned, then on the table, from which the columns inherit. And the connection user should not be root. If, ...



Top 50 recent answers are included