New answers tagged

1

I finally figured it out. The main reason for the crashes and the mysql_upgrade error was related to missing and/or corrupted primary keys of the mysql table. There were also a couple missing records in the mysql.db table, which likely couldn't update previously due to the GRANT crashes and would have been related to Plesk "Database Servers" ...


0

I'll try and answer your question more fully here. This is what I do to start the mysql daemon manually. I've also included my my.cnf. By specifying the correct corresponding paths in your system, you should be able to do the same thing. I would also point out that this is a source install, but again, it should apply generally. ./bin/mysqld --defaults-...


0

Take a look at this diagram of the InnoDB architecture. If you have more specific questions, ask back here but in a new thread. I believe the image is from Peter Zaitsev at Percona but can't seem to follow the Google images on my tablet. There's more stuff in the link anyway. Update - found reference here and it was Vadim Tkachenko.


0

thanks for your answer. Ok, I did vmstat thing that's suggested in Oracle's site. Got this, I really don't know if there's something abnormal with these statistics. procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 0 0 39 280 43 ...


0

Try this code: DROP TABLE applicationsolution; CREATE TABLE applicationsolution (myid INTEGER AUTO_INCREMENT PRIMARY KEY, created DATETIME); Sample table. delimiter // CREATE TRIGGER applicationsolution_OnInsert BEFORE INSERT ON applicationsolution FOR EACH ROW BEGIN IF NEW.created IS NULL THEN SET NEW.created ...


0

How much RAM? key_buffer_size, if using only MyISAM, should be about 20% of available RAM. Switch to InnoDB and change some of the settings. Are you never using AUTO_INCREMENT? You seem to have commented out auto_increment* settings. slave-skip-errors=1062 -- Do you understand how fast your Masters can be corrupted? table_open_cache=10000 ...


0

I think the answer is no because of something I recently experienced (2 months ago in 5.6.21). I did something similar to you in a DDL script for a client. I spent 20 min watching the temp file grow with the new layout. I was expect the temp file to just get renamed automatically to the original table. Instead the temp file disappeared. No error was ...


0

To do it in one pass: UPDATE ... SET pattern = REPLACE( REPLACE( ... REPLACE(value 'a', '0') 'b', '1') ... 'z', '1'); Doesn't your table need PRIMARY KEY(value), INDEX(pattern) There is probably a way to convert those 0s and 1s into a bit string and ...


0

Depending on the distribution of data, it may be faster to run 3 queries and combine the results: ( SELECT ... WHERE field_id_56 LIKE 'AB%' ... ) UNION ALL ( SELECT ... WHERE field_id_56 LIKE 'G%' ... ) UNION ALL ( SELECT ... WHERE field_id_56 LIKE 'WO%' ... ) and have INDEX(channel_id, field_id_56) -- in that order (I am assuming that those two ...


0

Sort the data by uuid before loading it -- this will speed up the load and shrink the disk space some (because of less fragmentation) Use InnoDB -- this will speed up the SELECTs. Pack the uuids into BINARY(16) -- this will shrink the disk space by perhaps a factor of 2. If uuid_tags_codes is just tiny numbers, use TINYINT UNSIGNED -- 1 byte instead of 4. (...


0

The exact reason of why this happens, is because SESSION variables are replicated explicitly in the binary log. Here is an example of an entry header in the binary log: # at 368008153 #160624 9:15:24 server id xxxxx end_log_pos 368008235 Query thread_id=1097746 exec_time=0 error_code=0 SET TIMESTAMP=1466774124/*!*/; SET @@session.pseudo_thread_id=...


2

If you're running on Linux/Unix, you can do it with a bit of shell: for DB in `mysql -u root -r --silent -p -e 'show databases;' | egrep -v "_schema$|^mysql$|^sys$"` do mysql -uroot -p $DB < test.sql done Will obviously be easier if you temporarily set it up to be passwordless.


0

Hi all query will be merge like this SELECT prtner_name, count( id ) AS cnt, SUM( inscan ) , SUM( is_deliver ) , SUM( is_rto ) FROM `tbl_shipment` WHERE `awdno` != '' AND `prtner_name` != '' GROUP BY prtner_name


2

Since the FROM, WHERE and GROUP BY clause are identical, combine them as follows: SELECT prtner_name, COUNT(*) shipment_count, SUM(inscan) inscan_count, SUM(is_deliver) is_deliver_count, SUM(is_rto) is_rto_count FROM tbl_shipment WHERE `awdno` != '' AND `prtner_name` != '' GROUP BY prtner_name;


-1

You can do with subselects. Example, with your queries: SELECT (SELECT COUNT(*) FROM tbl_shipment WHERE `awdno` != '' AND `prtner_name` != '' GROUP BY prtner_name) AS total, (SELECT SUM( inscan ) FROM tbl_shipment WHERE `awdno` != '' AND `prtner_name` != '' GROUP BY prtner_name) AS inscan, (SELECT SUM( is_deliver ) FROM tbl_shipment WHERE `...


0

I do understand You are trying to convert to some date/time data type. All You need then is CASE expression in Your query. This should work then - assuming that those 3 variants are the only ones occurring, and that there are no additional leading or trailing whitespace characters. Improving this case expression to include other variants should be rather ...


0

One SQL sentence can't have variable fields per row; every row has same fields. But every field can have or haven't (null) value. If you want that in pure SQL you can group_concat the fields of wp_usermeta, like: SELECT use.name, use.email, group_concat(concat('{', meta_key, '##', meta_value, '}')) AS metadata FROM wp_users use INNER JOIN wp_usermeta met ...


0

Information_Schema are views and read-only. Yo can not update meta data about tables, only by modifying actual table with ALTER TABLE. If you do an ALTER TABLE you will have current timestamp in meta data. INFORMATION_SCHEMA is a database within each MySQL instance, the place that stores information about all the other databases that the MySQL server ...


1

You need 2 anti-joins. Written with either two LEFT JOIN / IS NULL: SELECT user.id FROM users AS user LEFT JOIN user_follows AS follower ON follower.users_id = user.id AND follower.shown_users_id = 2 LEFT JOIN user_follows AS followed ON followed.shown_users_id = user.id AND followed....


0

YOUR SAMPLE DATA DROP DATABASE IF EXIST johnc; CREATE DATABASE johnc; USE johnc CREATE TABLE scores ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(20) NOT NULL, city VARCHAR(20) NOT NULL, score INT NOT NULL, points INT NOT NULL, PRIMARY KEY (id), KEY (city) ); INSERT INTO scores (name,city,score,points) VALUES ('John', 'Boston', ...


2

Since you are using MySQL 5.1.73, you can blow away the ibdata1 file without worries. First, let's look inside ibdata1 (picture created by Percona CTO Vadim Tkachenko) STEP 01 : Make sure there is no InnoDB Run the following SELECT table_schema,table_name, (data_length+index_length)/POWER(1024,3) table_sizegb FROM information_schema.tables ...


2

The values you're trying to insert are above the max allowed for a signed INT column in MySQL: 3088727691 > 2147483647 You can either switch it to an unsigned INT to make the max value 4,294,967,295 or a signed BIGINT to give you the room to hit up to 9,223,372,036,854,775,807.


0

MySQL has replication cabapilities, sure you has revised the MySQL documentation. This means that MySQL copies data from master to slave, not both directions. You proposed "just click a button", if you don't want a "do it yourself" solution maybe can try with a database tool like MySQL workbench. In any case it is dangerous to synchronize two databases ...


0

With the mysql client you can also do: mysql> tee import.log mysql> source .\test.sql then view the import.log file created in the current directory.


5

I am not sure what you mean by Bindings will be done in another function. You have an EXECUTE statement that is going to invoke your query right there and then, and that statement is not providing parameter values for your :name parameters. That is what the error message is about. Your EXECUTE statement must have a USING clause, as per the manual: ...


2

You are trying to insert 4 values in 5 columns: :Id => Id :uId => uId :name => name :address => address ??? => phone You can either add or remove Phone Add Phone: INSERT INTO tableX(Id, uId, name, address, phone) VALUES (:Id, :uId, :name, :address, :phone) If Phone allows NULL and you don't know its value (...


0

Before version 5.6, the optimizer did not know how to automatically add an index to a subquery. Temp tables are (often) the workaround. CREATE TEMPORARY TABLE o ( PRIMARY KEY(customer_relacional_id) ) ENGINE=InnoDB SELECT customer_relacional_id, COUNT(*) AS orders, MIN(created_at) AS created_at FROM order_2016 ...


0

You are treating Handler like an AUTO_INCREMENT. Why not get rid of ID? Or use it instead of having an extra column? Edit Since you want to "reset to 0" the number at the start of the year... Assuming this is what you want, then converting it to a TRIGGER should work: START TRANSACTION; -- To keep other connections from overlapping SELECT @year := ...,...


0

SELECT SUBSTRING_INDEX(name, '(', 1) FROM test; UPDATE test SET name = TRIM(SUBSTRING_INDEX(name, '(', 1)); (assuming you want to get rid of the space, too)


0

That ALTER will copy the table over, requiring briefly enough space for a copy of the table. InnoDB does a pretty good job of keeping its BTrees somewhat free of wasted space. Hence the action won't do much good. Be sure that innodb_file_per_table is still on; otherwise you will be moving the table into ibdata1, which is quite hard to shrink. Certain ...


0

You should also check that these two lines are commented on the Master side (for networking issues) as shown below. # skip-networking # bind-address = 127.0.0.1 For complete details on master slave replication, follow this link.


0

If you are trying to rollback to previous state after making logical changes i.e. DDL/DMLs - then xtrabackup can help. However, so is making a raw copy of the data while MySQL is shutdown before making the changes. Once you are done with the changes, stop MySQL then restore the previous copy, start it back up. If you are looking for a more complex point in ...


0

Restart the mysql services with innodb_file_per_table=on in my.cnf and rename table to another database and again to original data base then it will create file per table option


1

MyISAM tables have many problems and limitations, but one very convenient aspect to them in cases like this, is that the underlying .frm (table metadata) and MYI/MYD index and data files can be easily copied around since there are no transaction logs to worry about. If you don't care about the InnoDB tables at all, you could avoid the need to shrink the ...


1

Unfortunately, MySQL is lacking window or analytic functions which make complex grouping queries easier. For your use case, though, it should be possible to get it working with a single query. Doing a simple row-based group of your sample dataset: MySQL [test]> select orderId , category, count(*), sum(subtotal) from OrderLineItems group by 1,2; +-------...


1

Would it be better to use a NOT IN or NOT EXISTS query? Yes, you would most likely benefit from an IN() (or EXISTS()) instead of the join and IS NULL because you're effectively only using the tableREPORTS data as a filter. This should work for what you need to do: SELECT [Call_Num], [Call_Cust_Num], [Call_Cust Name], [Call_Status]...


-1

Perhaps you are hitting full disk conditions? This bug could be related: https://bugs.mysql.com/bug.php?id=68895


0

You need to restart mysql to see the changes you made. Go to Task manager and click services tab. In the services tab, you can find mysql to restart..


0

How about somethig like this : SELECT a.id , user_login as 'last_active_user' , discussion_sub , todo_list_title , todo_title , count(comment) , MAX(CAST(comment_posted AS CHAR)) FROM abc_discussions a LEFT JOIN abc_todo_list b ON comment_table_type = 'todo_list' AND comment_table_id = b.id ...


0

My solution of this would be use relation tables instead of merging ids, use uniqueness for tables phone number, email, and website etc. and insert with IGNORE command like Insert ignore into emails values (5,a.a@a.com); If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a ...


0

For your web application build on PHP 5.5 and MySQL 5.1, having 150-200 concurrent connection,you should plan at least following configuration. [i] RAM - Start with 2 GB and upscale if performance is low. [ii]vCPU - Start with 03 vCPU core [iii] HDD - SSD RAM/vCPU is upgradeable anytime with 10 minutes of downtime. It is always trade off between ...


1

Yes, you are right. Memory is used by dormant databases I mentioned this in the following posts over the years Nov 13, 2015 : #1041 - Out of memory issue in mysql Apr 22, 2014 : Do Inactive MySQL Databases Consume Memory? Apr 21, 2014 : Adding new tables -- memory usage increases Lots of tables ? Lots of columns ? Of course, that is less memory for ...


0

change timestamp to char(19) String comparison is more streamlined and accurate that comparing a timestamp that may/may not include taking timezone into account. As a super bizzare example, see the StackOverflow post from Jon Skeet. tinyint to ENUM (I noticed it's overzealous with ENUMS in general) TINYINT is a integer with the range -128 to 127 ...


0

Can anyone advise on how to go about recovering the data? If I browse the ibdata1 file I can see most of the data (there appear to be a few rows missing). Are there any tools that can read the ibdata1 file and extract data row by row? Please don't do this... you may end up corrupting innodb data files. It is better to follow what the error log says.. "...


0

This is a common issue with systems that need to retain large/indefinite amounts of historical data online. You have a few options depending on your predilections/restrictions. Stretch Database in SQL Server 2016 is designed specifically to solve this type of problem. Historical data gets moved the Azure so the local dataset is much smaller plus ...


0

1) Although I prefer tcpdump myself I would ask what is the reason not to enable slow logs because it is not that big of an overhead. With reasonable thresholds it's actually very useful. If you want to be super cautious you can safely enable it with a conservative long_query_time and slowly dropping it. The variable is dynamic so you can change it on the ...


0

Use Innodb unless you have a specific reason not to. If your coming from Oracle you will find its internal behaviour familiar therefore learning curve is shorter. For what you described you probably will want to enable binary logs (check which format suits your use case better: row, statement or mixed). For hot backups I recommend to use percona-...


3

SELECT a AS result FROM tableX UNION DISTINCT SELECT b FROM tableX ;


0

why you need use subqueries ? You can use joins for get data SELECT u.name AS user, d.name AS departement, m.year FROM main AS m INNER JOIN department AS d ON m.departement_id = d.id INNER JOIN users AS u ON m.user_id = u.id ; I created a sample fidle http://sqlfiddle.com/#!9/74a4e2/1


0

Option 1: Use a different account which have only sufficient permission. Don't use DBA rights account until that is required. Option 2 : enable the auditing to track all the changes in databases and made by whom. I hope this will full fill the compliance requirement.



Top 50 recent answers are included