| Conditional Join - Specific Column Select Posted: 13 Oct 2013 08:17 PM PDT I have tables like these: Table Employee ( Emp_Id, Full_Name, ...) Table Order ( Submitter_Id, Recipient_Id, ...)
Then say that I have conditional join like this: select isnull(Emp.Full_Name, Emp2.Full_name) as Recipient -- , other fields from Order left outer join Employee Emp on Emp.Emp_Id = Order.Recipient_Id left outer join Employee Emp2 on Emp2.Emp_Id = Order.Submitter_Id -- where clauses
Brief explaination: The query will return the name of recipient from specific order. An order may has recipient id being set, or using submitter id if the recipient is not set. The condition: Both table Order and Employee has big amount of records inside, so joining them both is a costly operation. 80% - 90% of records in Order has recipient_id set, so joining to submitter_id can be useless operation. Using isnull as join condition can resulting in index scan (in my experience). Using subquery for column maybe help, but the cost can be high because the operation can be row-by-row. Is there any conditional join for case like this? |
| How do you disable anonymous login? - MySQL Posted: 13 Oct 2013 05:50 PM PDT According to the MySQL documentation, you can harden a MySQL server by adding passwords, or removing the anonymous accounts. If you want to prevent clients from connecting as anonymous users without a password, you should either assign a password to each anonymous account or else remove the accounts. Before hardening, my users table looked like this. mysql> select user,host,password from mysql.user; +------------------+-----------+-------------------------------------------+ | user | host | password | +------------------+-----------+-------------------------------------------+ | root | localhost | *F3A2A51A9B0F2BE246XXXXXXXXXXXXXXXXXXXXXX | | root | gitlab | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | gitlab | | | debian-sys-maint | localhost | *95C1BF709B26A5BAXXXXXXXXXXXXXXXXXXXXXXXX | | myuser | localhost | *6C8989366EAF75BB6XXXXXXXXXXXXXXXXXXXXXXX | +------------------+-----------+-------------------------------------------+
I've remove all anonomous accounts, so that the user table now looks like this. (I'm using puppet to manage the users, but puppet effectively performs a DROP USER command). mysql> select user,host,password from mysql.user; +------------------+-----------+-------------------------------------------+ | user | host | password | +------------------+-----------+-------------------------------------------+ | root | localhost | *F3A2A51A9B0F2BE246XXXXXXXXXXXXXXXXXXXXXX | | debian-sys-maint | localhost | *95C1BF709B26A5BAXXXXXXXXXXXXXXXXXXXXXXXX | | myuser | localhost | *6C8989366EAF75BB6XXXXXXXXXXXXXXXXXXXXXXX | +------------------+-----------+-------------------------------------------+
Why is it that I am still able to login to my test system without a username or a password?
What do I need to do to prevent any unwanted users from logging in? root@gitlab:~# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 98 Server version: 5.5.32-0ubuntu0.12.04.1 (Ubuntu) .... mysql>
Update: I've also just discovered that I can log in as root, without entering a password. Update2: I found this question which has some good information, but does not solve the issue. Theere are no anonymous users mysql> select user,host,password from mysql.user where user=''; Empty set (0.00 sec)
I log in as root@localhost mysql> select USER(),CURRENT_USER(); +----------------+----------------+ | USER() | CURRENT_USER() | +----------------+----------------+ | root@localhost | root@localhost | +----------------+----------------+
I do not have a default password, or skip-grant-tables defined in my.cnf root@gitlab:~# cat /etc/mysql/my.cnf |grep -i 'skip-grant-tables'|wc -l 0 root@gitlab:~# cat /etc/mysql/my.cnf |grep -i 'pass'|wc -l 0
Update3: I have tried performing these steps with puppet, (which should perform a flush privileges automatically). I have also manually flushed privileges and also tried restarting mysql. Update4:
I've also tried changing the mysql root password and flushed privileges. No luck, I can still log in as any user without a password. |
| What is the standard isolation level for a db used for money data? Posted: 13 Oct 2013 03:51 PM PDT When using a database server for monetary/financial data, I can only assume that using transactions is mandatory.
But what I am not sure is which is usually the isolation level? Is a READ-REPEATABLE used? Or only a serialized level? |
| MySQL server vs client isolation levels Posted: 13 Oct 2013 11:31 AM PDT What is the difference between setting the transaction isolation level in the client configuration and the server configuration?
If I set the client isolation level to be serializable while the server's is read-repeatable what is the result for the transactions? Both of the same client and with transactions of other clients on the same tables? |
| Parsing an XML field to connect products to multiple categories? Posted: 13 Oct 2013 10:43 AM PDT I receive an unstructured XML data feed from my vendor. It actually contains two files. The first one is - New Products;Hot Sellers;Jewelery;Glass Ware;Men's Jewelery;Women's Jewelery;Gold Jewelery;Sterling Silver Jewelery;Watches;Bracelets; Diamond Jewelry; Ring's; Gold Rings; Bulova; (and more (in fact there are over 200 categories which I am working on turning into Categories and multiple levels of subcategories) This is followed with all of the 3000+ products. Parsing most of this and generating a table is pretty straight forward (even though I am pretty new to this) the problem I have is that there is a category field that has multiple comma separated fields. Jewelery Women's Jewelery, Women's Watches, Watches, Gold Watches, Fashion Jewelery, Bulova I am looking for a straight forward (and simple) way to parse the field to extract and assign the respective product to all of the categories. Also, what is the best way to design the categories table. Right now I am looking at: Parent: Categories Children: 1.)Jewelry 2.) Glassware 3.)? Children of Jewelery: 1.) Men's Jewelery 2.)Women's Jewelery
Each of these children would contain a number of categories: Watches, Rings, Bracelets, Necklaces, Brand Names, Gold, Sterling Silver, Costume, Fashion, Etc. I have not yet selected a platform as this is critical to populating my store. So the first question is how do I populate my categories table from a semi-colon deliminted file? Secondly, what is the best way to parse a multi-populated (comma separated) single field to assign the product to ALL categories appropriate to said item? I am looking at using a Windows based Platform. |
| How to re setup my mysql like it was with all the data Posted: 13 Oct 2013 10:07 AM PDT My computer had slowed down and I made an image file backup. I then reinstalled windows. How can I use the files that I have backed up to give the newly installed mysql the same data it had last time? I havent got any exports. |
| How to set Innodb Buffer Pool Size Posted: 13 Oct 2013 07:40 AM PDT I have a database using innodb completely. Its now having a innodb_buffer_pool_size at 128MB. Which I know is very less for my user. But no matter what I do it won't change. I have added a value of 1G both in /etc/my.cnf and also in /etc/mysql/my.cnf and restarted the mysql service. It doesn't seem to update. My mysql keeps on stopping and when i run mysqltuner & tuning-primer.sh all point to the fact I have to increase the innodb_buffer_pool_size but it simply won't increase. Is there any other way to enforce this? |
| How to combine date and time to datetime2 in SQL Server? Posted: 13 Oct 2013 08:47 AM PDT Given the following components DECLARE @D DATE = '2013-10-13' DECLARE @T TIME(7) = '23:59:59.9999999'
What is the best way of combining them to produce a DATETIME2(7) result with value '2013-10-13 23:59:59.9999999'? Some things which don't work are listed below.
SELECT @D + @T
Operand data type date is invalid for add operator.
SELECT CAST(@D AS DATETIME2(7)) + @T
Operand data type datetime2 is invalid for add operator.
SELECT DATEADD(NANOSECOND,DATEDIFF(NANOSECOND,CAST('00:00:00.0000000' AS TIME),@T),@D)
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
SELECT CAST(@D AS DATETIME) + @T
The data types datetime and time are incompatible in the add operator.
SELECT CAST(@D AS DATETIME) + CAST(@T AS DATETIME)
Returns a result but loses precision 2013-10-13 23:59:59.997 |
| Does MySQL reindex on update Posted: 13 Oct 2013 10:13 AM PDT I have a users table with various fields which are getting updated frequently. i.e table have: userid - PRIMARY int profile_views - int ratings - int last_active - int ( to store UNIX timestamps for sorting purpose)
I also have an index on last_active to get the last active users. When user logins or performs a certain action last_active gets updated and same with profile_views and ratings. My question is does mysql reindex all table ro reindex index last_active when I update last_active index value or update profile_views or ratings? table: CREATE TABLE IF NOT EXISTS `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ip_address` varbinary(16) NOT NULL, `username` varchar(100) NOT NULL, `password` varchar(80) NOT NULL, `salt` varchar(40) DEFAULT NULL, `email` varchar(100) NOT NULL, `title` varchar(255) NOT NULL, `detail` varchar(500) NOT NULL, `genre_id` int(2) NOT NULL, `category` tinyint(1) NOT NULL, `profile_views` int(11) NOT NULL, `ratings ` int(11) NOT NULL, `last_active` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `genre_id` (`genre_id`), KEY `last_active` (`last_active`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4059 ;
and second table to join to CREATE TABLE IF NOT EXISTS `users_genre` ( `userid` int(11) NOT NULL, `genre_id` int(11) NOT NULL, KEY `userid` (`userid`), KEY `genre_id` (`genre_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Thanks |
| Is using DISTINCT as a hint in subqueries useful? Posted: 13 Oct 2013 06:42 AM PDT Does adding DISTINCT in the following example have any impact on the query running time?
Is it wise to use it as a hint sometimes? SELECT * FROM A WHERE A.SomeColumn IN (SELECT DISTINCT B.SomeColumn FROM B)
|
| What is Required to use Oracle Performance Manager to monitor SQL Server? Posted: 13 Oct 2013 09:45 AM PDT I am working with a customer who has several SQL Server and Oracle DBMS server instances. My customer would like to setup up performance data collection, monitoring and alerting for all of their databases. My strength is in SQL Server and I know lots of products/tools for doing this there, but there seems to be very few options for one tool or product that can do both Oracle and SQL Server at the same time. One option that has been suggested is Oracle Performance Manager, which apparently can also monitor SQL Server. I have been trying to read up on it, but there's a lot of doc out there and I am not very familiar with it's implicit context (the Oracle Product suite(s)) so I am having trouble filtering it down to the parts that I need right now to make a recommendation to my client. Here are the things that I am trying to figure out: Does Oracle Performance Manager fulfill any/all of these Performance functions for Oracle databases?: a. Performance Data Collection (ie., retained in a database) b. Performance Monitoring (live views of performance) c. Performance Alerting (live notification of exceeding configurable thresholds) d. Performance Reporting (this would be nice but not necessary, I know how to write a report from data tables, even in Oracle) Which can Oracle Performance Manager fulfill for SQL Server databases? What specific Oracle Product(s) does my customer need to have or purchase in order to get these functions? Are there any other requirements or restrictions that we should know about? Any help on this is much appreciated. |
| Mysql Server Login and Permission Deny Error Posted: 13 Oct 2013 02:11 PM PDT I have a problem with MySQL server. After upgrading MySQL server from 5.3 to 5.4, I am not able to log-in using my MySQL root password (Yes, I am able to log-in without password!). Also not able to grant permissions to users as well. Not able to change in databases using root account, while taking backup of database due to same. When I looked into MySQL databases using root account, it doesn't show anything. While on other server, I am able to find all entries. Kindly please help me to resolve this issue as it is critical. Log of mysql is as below. 130906 18:46:13 [Warning] Found invalid password for user: 'root@x.x.x.x'; Ignoring user 1843 130906 18:46:13 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it 1844 130906 18:46:14 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist 1845 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure 1846 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure 1847 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure 1848 130906 18:46:14 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure 1849 130906 18:46:14 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure 1850 130906 18:46:14 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure 1851 130906 18:46:14 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure 1852 130906 18:46:14 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure 1853 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure 1854 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure 1855 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure 1856 130906 18:46:14 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure 1857 130906 18:46:14 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure 1858 130906 18:46:14 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure 1859 130906 18:46:14 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure 1860 130906 18:46:14 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure 1861 130906 18:46:14 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure 1861 130906 18:46:14 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure 1862 130906 18:46:14 [ERROR] Column count of mysql.db is wrong. Expected 22, found 13. Created with MySQL 50045, now running 50532. Please use mysql_upgrade to fix this error. 1863 130906 18:46:14 [ERROR] mysql.user has no `Event_priv` column at position 29 1864 130906 18:46:14 [ERROR] Cannot open mysql.event 1865 130906 18:46:14 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler. 1866 130906 18:46:14 [Note] /usr/libexec/mysqld: ready for connections. 1867 Version: '5.5.32-cll-lve' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Atomicorp |
| SQL Server 2012 Page Compression - Determine compression ratio? Posted: 13 Oct 2013 05:20 PM PDT We're using SQL Server 2012 and have page compression turned on for a number of our larger tables. Is there a way we can determine the compression ratio we're getting? We'd like to be able to determine the benefits of compression. |
| linked server to specific access table Posted: 13 Oct 2013 08:26 AM PDT I have built a linked server which links my SQL Server database to an Access DB. Using this object someone is able to see all tables in Access. What I would like to do is to isolate it just to 1 table. So my question is how to build a linked server (from SQL Server) connected only to one specific table in Access? |
| Accumulo table design methodology Posted: 13 Oct 2013 11:26 AM PDT I am just getting started with Accumulo and NoSQL databases and I am looking for some discussion on table design. I get the key value structure that is seen in the manual. However, if I am trying to recreate a relational database, I am not sure how relationships work. Can someone explain to some degree how to setup and "Hello World" database (i.e., manager-employee database). I want to use key-value implementation. |
| Way to make differential backup from full different but from same database Posted: 13 Oct 2013 03:26 PM PDT I have the following situation: Our sql server is running in a cloud service. The database to be backed up have 60gb. We need to make a local copy of the database for some tests, and it need to be up to date, but 60gb is impracticable to download daily. We thought to make a full backup, download it one time, make differential backups in following days. Once a week, we can stop the database operations to make the last differential backup from last week full backup and make a full backup, and start again the database operations. In this moment, the local bd, when restored with the last diff backup (made when the db was with stopped operations), have the same data than the last week full backup. The idea behind this is, if is possible to use the diff backup from last full backup and restore it in our local database. If it is not possible, anyone have some idea of what we can do to get all the data, up to date, from our cloud database? |
| Disaster Recovery for PostgreSQL 9.0 Posted: 13 Oct 2013 07:26 PM PDT We have a number of PostgreSQL 9.0 servers. We use binary replication to have a host standby instance of those. The only problem is that is someone drops the master, with or without intentions, this will cascade to the replicas as well. I'm looking at the possible ways to avoid this. One possible option in seems to be Point in Time Recovery. I'm just wondering what could be a good design for this. Any ideas? Let's assume the master is compromised and we lose everything we have there. How can we avoid losing the replica or at least have a way to bring it back if it's dropped? |
| Oracle Patch Update Posted: 13 Oct 2013 12:26 PM PDT We have an Oracle RAC production environment with primary and secondary DB. Our DBA has asked to update oracle version from 11.2.0.1.0(64 bit) to 11.2.0.3(64 bit) with patch 6880880,10404530,16803769 and 16803775. In our current database we have Shared storage,ACL settings, security settings,Gateway/Heteregenous connectivity, Dataguard, Data broker, Backup policy and Oracle Client installed on other machines. DBA has estimated that he need to do installation, settings from scratch and test.. So, when the version is updated, do we really need to reconfig and install everything (Shared storage,ACL settings, security settings,Gateway/Heteregenous connectivity, Dataguard, Data broker, Backup policy and Oracle Client installed on other machines) ?? If yes its fine, but no then I need to justify it. I can understand testing would be required.. |
| Updateable Subscriptions: Rows do not match between Publisher and Subscriber Posted: 13 Oct 2013 09:26 PM PDT I have transactional replication with updatable subscribers set up in SQL Server 2008. It has 3 subscribers and 1 publisher. I had to setup replication again due to some errors related to the database and the application which uses the database. However, now I run into issues when I try updating a section in my application. It does not go through the with update and gives the following error: Execption Message: Updateable Subscriptions: Rows do not match between Publisher and Subscriber. Run the Distribution Agent to refresh rows at the Subscriber. Updateable Subscriptions: Rolling back transaction. The transaction ended in the trigger. The batch has been aborted. Exception Triggered by Method: OnError The update statement obviously doesn't go through. However, when I try it the second time it works. Replication is working. Everything seems to be replication. Can anyone explain why this error would occur and how I can resolve this issue. I would really appreciate the help!... |
| How to add rows/columns to the table in runtime in SSRS 2008 Posted: 13 Oct 2013 10:26 AM PDT Usually we design the table to have x number of rows and y number of columns in a report. But how can we create a report which adds the rows and columns dynamically at run time based on the result of the source query? For example I want to list stdentId, StudentName and any course each student has enrolled in. As the number of courses is different from one person to the other, I should add the rows and related column for courses at run time based on the query result. How can it be done? For example: 
Thanks for your help in advance. |
| How to disable oracle's MAX_ENABLED_ROLES limit Posted: 13 Oct 2013 04:26 PM PDT How to disable oracle's MAX_ENABLED_ROLES limit or expand the value of limitation. [oracle 10g (win32)] |
| In MySQL, does the order of the columns in a WHERE clause affect query performance,why? Posted: 13 Oct 2013 01:26 PM PDT I have a query that doesn't use any indexes: SELECT 32, guid, 1, 1, 1, 0, 5 FROM test WHERE level >= 20 AND ( ( fun_GetIndexValue(data, 354) >> 16 ) + ( fun_GetIndexValue(data, 355) >> 16 ) + ( fun_GetIndexValue(data, 356) >> 16 ) + ( fun_GetIndexValue(data, 357) >> 16 ) + ( fun_GetIndexValue(data, 358) >> 16 ) + ( fun_GetIndexValue(data, 359) >> 16 ) ) >= 1;
The level column has only about 80-90 distinct values, the table test has about million rows, and the data column is passed to the function, so I think the query can not use any indexes. But I found that if I put the level condition in the end, the query performs slower. Why is that? |
| Efficiently Filter Large Set With Disjunctions Posted: 13 Oct 2013 07:26 AM PDT Let's say I have a single table CREATE TABLE Ticket ( TicketId int NOT NULL, InsertDateTime datetime NOT NULL, SiteId int NOT NULL, StatusId tinyint NOT NULL, AssignedId int NULL, ReportedById int NOT NULL, CategoryId int NULL );
In this example TicketId is the Primary Key. I want users to be able to create "partially ad-hoc" queries against this table. I say partially because a few parts of the query will always fixed: - The query will always perform a range filter on an
InsertDateTime - The query will always
ORDER BY InsertDateTime DESC - The query will page results
The user can optionally filter on any of the other columns. They can filter on none, one, or many. And for each column the user may select from a set of values which will be applied as a disjunction. For example: SELECT TicketId FROM ( SELECT TicketId, ROW_NUMBER() OVER(ORDER BY InsertDateTime DESC) as RowNum FROM Ticket WHERE InsertDateTime >= '2013-01-01' AND InsertDateTime < '2013-02-01' AND StatusId IN (1,2,3) AND (CategoryId IN (10,11) OR CategoryId IS NULL) ) _ WHERE RowNum BETWEEN 1 AND 100;
Now assume the table has 100,000,000 rows. The best I can come up with is a covering index that includes each of the "optional" columns: CREATE NONCLUSTERED INDEX IX_Ticket_Covering ON Ticket ( InsertDateTime DESC ) INCLUDE ( SiteId, StatusId, AssignedId, ReportedById, CategoryId );
This gives me a query plan as follows: - SELECT
- Filter
- Top
- Sequence Project (Compute Scalar)
It seems pretty good. About 80%-90% of the cost comes from the Index Seek operation, which is ideal. Are there better strategies for implementing this kind of searching? I don't necessarily want to offload the optional filtering to the client because in some cases the result set from the "fixed" part could be 100s or 1000s. The client would then also be responsible for sorting and paging which might too much work for the client. |
| MySQL crashes with Signal 8 after "Table has no partition for value" error Posted: 13 Oct 2013 02:08 PM PDT We have a mysql database running well and suddenly it stops and below are the snippet of the log file. What is the best remedy in this scenario? 130202 1:08:59 [ERROR] Event Scheduler: [root@localhost][packets.ipByte_SecondAnalysis] Table has no partition for value 735266 130202 1:08:59 [Note] Event Scheduler: [root@localhost].[packets.ipByte_SecondAnalysis] event execution failed. 17:09:02 UTC - mysqld got signal 8 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388608 read_buffer_size=131072 max_used_connections=2 max_threads=151 thread_count=3 connection_count=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 338489 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x4223f70 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 7f6d2c794d98 thread_stack 0x40000 /usr/libexec/mysqld(my_print_stacktrace+0x2e)[0x78329e] /usr/libexec/mysqld(handle_fatal_signal+0x493)[0x66e2c3] /lib64/libpthread.so.0[0x3b4300f4a0] /usr/libexec/mysqld(_ZN12ha_partition21min_rows_for_estimateEv+0x4c)[0x8efe8c] /usr/libexec/mysqld(_ZN12ha_partition16records_in_rangeEjP12st_key_rangeS1_+0x35)[0x8efec5] /usr/libexec/mysqld[0x71efc7] /usr/libexec/mysqld[0x71f62f] /usr/libexec/mysqld[0x71f8ab] /usr/libexec/mysqld(_ZN10SQL_SELECT17test_quick_selectEP3THD6BitmapILj64EEyyb+0x1060)[0x727310] /usr/libexec/mysqld[0x5b02f5] /usr/libexec/mysqld(_ZN4JOIN8optimizeEv+0x4cd)[0x5b11fd] /usr/libexec/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x105)[0x5b4b25] /usr/libexec/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x174)[0x5ba3e4] /usr/libexec/mysqld[0x57ba37] /usr/libexec/mysqld(_Z21mysql_execute_commandP3THD+0x304e)[0x581d5e] /usr/libexec/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x19d)[0x58340d] /usr/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x12f1)[0x584eb1] /usr/libexec/mysqld(_Z24do_handle_one_connectionP3THD+0xd2)[0x613cd2] /usr/libexec/mysqld(handle_one_connection+0x50)[0x613de0] /lib64/libpthread.so.0[0x3b430077f1] /lib64/libc.so.6(clone+0x6d)[0x3b42ce570d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7f6cc8223980): is an invalid pointer Connection ID (thread ID): 43 Status: NOT_KILLED The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 130202 01:09:02 mysqld_safe Number of processes running now: 0 130202 01:09:02 mysqld_safe mysqld restarted 130202 1:09:02 [Warning] The syntax '--log' is deprecated and will be removed in a future release. Please use '--general-log'/'--general-log-file' instead. 130202 1:09:02 [Note] Plugin 'FEDERATED' is disabled. 130202 1:09:02 InnoDB: The InnoDB memory heap is disabled 130202 1:09:02 InnoDB: Mutexes and rw_locks use GCC atomic builtins 130202 1:09:02 InnoDB: Compressed tables use zlib 1.2.3 130202 1:09:02 InnoDB: Using Linux native AIO 130202 1:09:02 InnoDB: Initializing buffer pool, size = 1.0G 130202 1:09:02 InnoDB: Completed initialization of buffer pool 130202 1:09:02 InnoDB: highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 178008262046 130202 1:09:02 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 178008589529 130202 1:09:02 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 130202 1:09:03 InnoDB: Waiting for the background threads to start 130202 1:09:04 InnoDB: 1.1.8 started; log sequence number 178008589529 /usr/libexec/mysqld: File '/var/log/allQuery.log' not found (Errcode: 13) 130202 1:09:04 [ERROR] Could not use /var/log/allQuery.log for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it. 130202 1:09:04 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 130202 1:09:04 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 130202 1:09:04 [Note] Server socket created on IP: '0.0.0.0'. 130202 1:09:04 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it 130202 1:09:04 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure 130202 1:09:04 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure 130202 1:09:04 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure 130202 1:09:04 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure 130202 1:09:04 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure 130202 1:09:04 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure 130202 1:09:04 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure 130202 1:09:04 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure 130202 1:09:04 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure 130202 1:09:04 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure 130202 1:09:04 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure 130202 1:09:04 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure 130202 1:09:04 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure 130202 1:09:04 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure 130202 1:09:04 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure 130202 1:09:04 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure 130202 1:09:04 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure 130202 1:09:04 [Note] Event Scheduler: Loaded 2 events 130202 1:09:04 [Note] /usr/libexec/mysqld: ready for connections.
|
| Delete word, its meanings, its meaning's example sentences from DB Posted: 13 Oct 2013 05:26 PM PDT I have three tables as below (simplified for demonstration): words ===== integer id text word meanings ======== integer id integer word_id text meaning examples ======== integer id integer meaning_id text sentence
where, word_id stores id of the word in words table and meaning_id stores id of the meaning in meanings table. I am trying to figure out a sql query, given a word's id, to delete the word with all its meanings and example sentences all at one time. Is such sql query possible to compose? If so, how? Edit1: I am using SQLite3 as the database. Edit2: I figured the following solution which requires 3 sql queries in order: DELETE FROM examples WHERE meaning_id IN (SELECT id FROM meanings WHERE word_id=the_given_id); DELETE FROM meanings WHERE word_id=the_given_id; DELETE FROM words WHERE id=the_given_id;
I'm still looking for the answer to my question: is the whole process possible to be done in one query? |
| MySQL concurrent INSERTs Posted: 13 Oct 2013 09:26 AM PDT I have a MySQL database with InnoDB tables. There are different client processes making SELECT (to check the existence of a value) and INSERT or UPDATE (depending on the result of the select) statements. What I fear is a possible concurrent access to data causing only INSERTs and no UPDATEs. Is LOCK Table WRITE the only solution? |
| How can I optimize this query and support multiple SKUs? Posted: 13 Oct 2013 03:26 PM PDT My current query only can select one SKU at a time. I can leave salesite_id constant. If there is a way to also have varying salesite_ids that would be good too, but not necessary. Also any suggestions on indexes would be much appreciated also. SELECT available - ( SELECT COALESCE(sum(quantity), 0) FROM product_locks WHERE sku = 'sku1' ) - ( SELECT COALESCE(sum(quantity), 0) FROM orderlineitems INNER JOIN responses_authnets ON responses_authnets.id = orderlineitems.response_id WHERE sku = 'sku1' AND responses_authnets.salesite_id = 'site_id_1' ) AS free, available AS total, sku, on_hold FROM product_inventories WHERE sku = 'sku1' AND salesite_id = 'site_id_1';
|
| How to modify an update in Oracle so it performs faster? Posted: 13 Oct 2013 02:26 PM PDT I have this query: UPDATE ( SELECT h.valid_through_dt, h.LAST_UPDATE_TMSTMP FROM ETL_FEE_SCH_TMP d, FEE_SCHEDULE_HISTORICAL h WHERE h.FUND_ID = d.FUND_ID AND h.FEETYPE_NAME = d.FEETYPE_NAME AND h.BREAKPOINT_TYPE = d.BREAKPOINT_TYPE AND h.BREAKPOINT_QTY = d.BREAKPOINT_QTY AND h.LOW_BREAKPOINT_AMT = d.LOW_BREAKPOINT_AMT AND h.VALID_THROUGH = TO_DATE ('31-DEC-9999', 'dd-mon-yyyy') AND h.universe = 'DC' AND h.universe = d.universe AND EXISTS ( SELECT 1 FROM FEE_SCHEDULE s WHERE s.FUND_ID = h.FUND_ID AND s.FEETYPE_NAME = h.FEETYPE_NAME AND s.BREAKPOINT_TYPE = h.BREAKPOINT_TYPE AND s.BREAKPOINT_QTY = h.BREAKPOINT_QTY AND s.LOW_BREAKPOINT_AMT = h.LOW_BREAKPOINT_AMT AND s.universe = 'DC' ) ) updateTable SET updateTable.VALID_THROUGH = (SYSDATE - 1), updateTable.LAST_UPDATE_TMSTMP = SYSTIMESTAMP;
The trouble that I am having is that this query takes a long time to run. I don't know whether it is possible to run this on parallel, or it would be easier to update a cursor in a pipeline function. What would you suggest? This is all the information that I believe it is relevant. This is the execution plan of the internal select: Execution Plan ---------------------------------------------------------- Plan hash value: 57376096 --------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes| Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 306 | 8427 (1)| 00:01:42 | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 1 | 306| 8427 (1)| 00:01:42 | | 3 | MERGE JOIN CARTESIAN | | 1 | 192| 8426 (1)| 00:01:42 | |* 4 | INDEX RANGE SCAN | SYS_C000666 | 1 | 96| 2 (0)| 00:00:01 | | 5 | BUFFER SORT | | 3045K| 278M| 8425 (1)| 00:01:42 | | 6 | SORT UNIQUE | | 3045K| 278M| 8425 (1)| 00:01:42 | |* 7 | TABLE ACCESS FULL | FEE_SCHEDULE | 3045K| 278M| 8425 (1)| 00:01:42 | |* 8 | INDEX RANGE SCAN | FEE_SCHDL_IDX1 | 1 | | 1 (0)| 00:00:01 | |* 9 | TABLE ACCESS BY INDEX ROWID| FEE_SCHEDULE_HISTORICAL | 1 | 114 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("D"."UNIVERSE"='DC') 7 - filter("S"."UNIVERSE"='DC') 8 - access("H"."UNIVERSE"='DC' AND "S"."FUND_ID"="H"."FUND_ID" AND "S"."FEETYPE_NAME"="H"."FEETYPE_NAME" AND "S"."BREAKPOINT_TYPE"="H"."BREAKPOINT_TYPE" AND "S"."BREAKPOINT_QTY"="H"."BREAKPOINT_QTY" AND "S"."LOW_BREAKPOINT_AMT"="H"."LOW_BREAKPOINT_AMT") filter("H"."FUND_ID"="D"."FUND_ID" AND "H"."FEETYPE_NAME"="D"."FEETYPE_NAME" AND "H"."BREAKPOINT_TYPE"="D"."BREAKPOINT_UNIT_TY
Table data: --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- UNIVERSE|FUND_ID |FEETYPE_NAME |BREAKPOINT_TYPE|BREAKPOINT_QTY|LOW_BREAKPOINT_AMT|HIGH_BREAKPOINT_AMT|FEE_PCT|FEE_SCHDL_SEQ_ID|GROUP_ID|LAST_UPDATE_TMSTMP |VALID_FROM|VALID_THROUGH|INSERT_TMSTMP |JOB_ID| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- DC |DC9ZTPLPHO|DeferLoad |Percentage |4 |10000 |300000 |3.14 |780250 |null |1/4/2012 3:59:54 PM|6/23/2012 |12/31/9999 |1/5/2011 3:59:54 PM|666 | DC |DCE86Y8XFU|RedemptionFee|Percentage |9 | 100 |100500 |7.67 |780251 |null |6/4/2012 4:49:54 PM|11/12/2011|12/31/9999 |8/17/2011 2:00:54 PM|666 | DC |DCAYL0KONA|FrontLoad |Percentage |2 |50000 |601500 |5.00 |780252 |null |4/25/2012 4:49:54 PM|8/2/2012 |12/31/9999 |12/19/2012 9:59:00 PM|666 | DC |DC9ZTPLPHO|DeferLoad |Percentage |7 |80000 |900000 |2.24 |780252 |null |4/25/2012 4:49:54 PM|8/2/2012 |12/31/9999 |12/19/2012 9:59:00 PM|666 | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This is the script of the historical table: CREATE TABLE FEE_SCHEDULE_HISTORICAL ( UNIVERSE VARCHAR2(2 BYTE) NOT NULL, FUND_ID VARCHAR2(10 BYTE) NOT NULL, FEETYPE_NAME VARCHAR2(75 BYTE), BREAKPOINT_TYPE VARCHAR2(50 BYTE), BREAKPOINT_QTY VARCHAR2(10 BYTE), LOW_BREAKPOINT_AMT NUMBER(19,6), HIGH_BREAKPOINT_AMT NUMBER(19,6), FEE_PCT NUMBER(19,6), FEE_SCHDL_SEQ_ID NUMBER NOT NULL, GROUP_ID NUMBER, LAST_UPDATE_TMSTMP DATE NOT NULL, VALID_FROM DATE NOT NULL, VALID_THROUGH DATE NOT NULL, INSERT_TMSTMP DATE NOT NULL, JOB_ID NUMBER NOT NULL ); CREATE UNIQUE INDEX FEE_SCHDL_PK ON FEE_SCHEDULE_HISTORICAL(FEE_SCHDL_SEQ_ID); CREATE UNIQUE INDEX FEE_SCHDL_HST_IDX ON FEE_SCHEDULE_HISTORICAL ( UNIVERSE, FUND_ID, FEETYPE_NAME, BREAKPOINT_TYPE, BREAKPOINT_QTY, LOW_BREAKPOINT_AMT, VALID_FROM, JOB_ID ) CREATE INDEX FEE_SCHEDULE_HST_IDX2 ON FEE_SCHEDULE_HISTORICAL(LAST_UPDATE_TMSTMP) CREATE INDEX FEE_SCHEDULE_HST_IDX3 ON FEE_SCHEDULE_HISTORICAL(VALID_THROUGH) ALTER TABLE FEE_SCHEDULE_HISTORICAL ADD ( CONSTRAINT FEE_SCHDL_PK PRIMARY KEY (FEE_SCHDL_SEQ_ID) );
This is the other table: CREATE TABLE FEE_SCHEDULE ( UNIVERSE VARCHAR2(2 BYTE) NOT NULL, FUND_ID VARCHAR2(10 BYTE) NOT NULL, FEETYPE_NAME VARCHAR2(75 BYTE), BREAKPOINT_TYPE VARCHAR2(50 BYTE), BREAKPOINT_QTY VARCHAR2(10 BYTE), LOW_BREAKPOINT_AMT NUMBER(19,6), HIGH_BREAKPOINT_AMT NUMBER(19,6), FEE_PCT NUMBER(19,6), JOB_RUN_ID NUMBER NOT NULL, FILE_DATE DATE NOT NULL, CYCLE_DATE DATE NOT NULL )
The temporary table is the result of FEE_SCHEDULE_HISTORICAL minus FEE_SCHEDULE |
