I am new here and am new to SQL Server (Express). I used the SSMA tool and it converted Access date/time fields to datatime2. The data contains duration data so there is no need for a date. I want to SUM the field but I need to change to a Time or numeric data type due to 8117# errors when I SUM. SQL Server will not allow a conversion using the designer / ALTER commands as there are always implicit/explicit conversion errors. Thank you for any possible solutions you may have to offer.
samedi 28 février 2015
Failing to ensureIndex on sharded Mongo
I run a mongoDb, sharding it on 4 different shards (+ 1 router and + 1 config). Sometime ago I had to replace a shard, and everything went ok. But now, every time I run:
db.summoners.ensureIndex( { plus: 1 } )
I get this error:
{
"raw" : {
"10.132.156.211:27017" : {
"numIndexesBefore" : 3,
"note" : "all indexes already exist",
"ok" : 1
},
"10.132.156.218:27017" : {
},
"10.132.145.51:27017" : {
"numIndexesBefore" : 3,
"note" : "all indexes already exist",
"ok" : 1
},
"10.132.157.195:27017" : {
"numIndexesBefore" : 3,
"note" : "all indexes already exist",
"ok" : 1
},
"10.132.137.214:27017" : {
"numIndexesBefore" : 3,
"note" : "all indexes already exist",
"ok" : 1
}
},
"ok" : 0,
"errmsg" : "{ 10.132.156.218:27017: \"result without error message returned : {}\" }"
}
The problem is that the given shard, 10.132.156.218:27017, does not exists anymore.
It's not even listed on the shard list!
{ "_id" : "shard0000", "host" : "10.132.156.211:27017" }
{ "_id" : "shard0002", "host" : "10.132.145.51:27017" }
{ "_id" : "shard0003", "host" : "10.132.157.195:27017" }
{ "_id" : "shard0004", "host" : "10.132.137.214:27017" }
What should I do?
PS:: MongoDB shell version: 2.6.5
--
mongos> db.summoners.stats()
{
"sharded" : true,
"systemFlags" : 1,
"userFlags" : 1,
"code" : 11002,
"ok" : 0,
"errmsg" : "exception: socket exception [CONNECT_ERROR] for 10.132.156.218:27017"
}
Redshift table not showing up in tables for schema?
On Redshift, why doesn't my table show up in the following query? It definitely exists, as shown by the next query I run. I want a way to list all tables for a schema:
mydb=# select distinct(tablename) from pg_table_def where schemaname = 'db';
tablename
-----------
(0 rows)
mydb=# \d db.some_table
Table "db.some_table"
Column | Type | Modifiers
-----------------+-----------------------------+-----------
...correct info shows up here...
...but nothing showed up above?
list of items or menu in mysql workbench
i am creating a database for an android tourism app. i am working on MySQL workbench, i want to create a menu in my database, for example i want to list the cities of my country, or list the branches of any place around the country. i read about ENUM & SET datatypes here, and how they are evil to my database http://ift.tt/1DnjNtH
what do you all think, what datatype do i need to represent a menu in my database?
How to reinstall a legacy DB2 Server 7.x for Linux?
We have to move our legacy database dump to a new Linux installation. I know DB2 7.x is out of support for over a decade, but is there a way to obtain a DB2 7.x Server installation media for Linux? Barring that, what other options do we have?
How do I untar a file on a host that doesnt use cpanel only phpmyadmin?
I have a tar file that I have uploaded to my host(which happens to be godaddy), but I have found out that they will not untar the file for me or do they use cpanel, only phpmyadmin panel is used. How can I get this file to be untared. This is a very important file that is essential for my website, so this is something I need to get resolved, any help would be greatly appreciated.
mysql select query slow down exponentially as table grows
I have the following query running on mysql 5.6 on my CENTOS server
select tb1.field2, tb1.field1, tb2.field1
from dns.table1 tb1, dns.table2 tb2
where tb1.field2 = tb2.field2
and tb1.field1 != tb2.field1
All relevant fields are indexed. on tables of 10 million records, it finishes within 400 econds, however, on 100 million records, it finishes within 10 hours!
Why? I suspect file swapping within mysql, what can be optimized on mysql? would increase buffer pool help?
Is PDO_OCI (PHP PDO using Oracle Instant Client) classed as ODBC?
I have a question that's been concerning me for some time.
We use Oracle at our company and we're about to upgrade to a newer release. I understand that Oracle have some additional licensing if you wish to connect to the database via ODBC.
My question is, we have a web application that connects to the Oracle database using PDO_OCI (PHP PDO using the Oracle Instant Client) - is this classed as accessing the Oracle database via ODBC?
Primary keys: composite and singular
In general when I design tables I always include a single unique field, even if it there is already a unique combination of columns available to create a primary key. This field is usually either a UUID (if application-generated) or a sequence (if database-generated).
Some questions:
- are there any downsides to this approach (apart from storage)?
- the sole exception to this rule is (for me) the join table that exists only to join other tables. Is there an argument to be made that a join table should also have a single field acting as primary key?
Selecting elements in alphabetical order in MySQL
Suppose I have a table called actor that has fields: actor_id, first_name, last_name. Currently, my table has entries like:
83 BEN WILLIS
152 BEN HARRIS
I would like to write a query that groups the table by first_name and if there are actors that share the same first name, only the row with the last name that comes first alphabetically will appear.
Meaning only 152 BEN HARRIS will appear and not 83 BEN WILLIS.
I tried to use the GROUP BY function, but I realize I needed something more. How would one go about doing this?
How to rollback transaction if row changed?
I am using Postgres 9.3.
When I update row in a table my flow looks like this:
- Fetch old row (ex.
SELECT * FROM tbl WHERE id = 1) - Validate new data in scope of old data (old row fetched in point 1) - this is done by my app outside of postgres.
- Update row with new data (ex.
UPDATE tbl SET .... WHERE id = 1)
The problem is that old data may change during point 2 so validation, even if passed may be outdated during point 3 (updating row). I know I could perform update like this UPDATE .... WHERE id = 1 AND column1='oldValue' to ensure certain fields didn't change, however my validation (also comparising new and old values) is very complex and I can't "write" it in SQL UPDATE statement.
So I thought about something like this:
BEGINSELECT * FROM tbl WHERE id = 1- Perform validation on my side
UPDATE SET=... WHERE id = 1COMMIT
And I would like to COMMIT fail if row with id = 1 changed during transaction execution. Is it possible to use transaction like this? If not what is other solution?
extract state changes from snapshot-based archive table
I have a table snapshot, storing snapshots every time an object stored in another live changed one of its attributes. I need to find all objects that changed a specific variable to a specific value in a given time frame.
For example, assume the following layout:
CREATE TABLE snapshot
(
"timestamp" timestamp with time zone NOT NULL,
person text NOT NULL,
haircolor text NOT NULL,
city text NOT NULL,
CONSTRAINT snapshot_pkey PRIMARY KEY (person, "timestamp")
)
Here, I want to find all persons that moved (from any other city) to NY between 2006-02-01 and 2006-02-14. I want all of them, even the ones that moved in on 2006-02-03 and moved out on 2006-02-05 again. (And of course I do not want to list people who only changed their haircolor in the given time frame).
What would an efficient query look like?
MYSQL Thread Historical Data
We had to force a restart on our database server today and we're now trying to find out what the server was doing at the time of the shutdown. In our error logs we have found 5 instances where the query that was executing had to be forced out.
[Warning] /usr/sbin/mysqld: Forcing close of thread ####### user: 'username'
Is there a place that the thread number is stored with the action is what executing? Searching around I've only been able to find results where the process is still running. Also is this message indicative of an issue or potentially a query could have just been executed when I executed the shutdown?
BEST RELATIONAL DATABASES APPROACH FOR DELETE: use DELETE or use a valid/non-valid COLUMN?
When it comes to databases, which is the best technical approach to use when a user want's to delete his profile/delete some of his products from a shopping card:
Actually delete the rows from the relational tables that have his id, using DELETE.
Don't DELETE any row, but change the value of a boolean COLUMN/enum COLUMN (let's call it valid/non-valid) from true to false/0 to 1 - this implies that whenever you wan't to select all the existing users/products you need a WHERE clause to filter only the ones that have true on this particular COLUMN.
I am asking this because I have spoken with two experienced DB developers and when I told them that I am really deleting rows from the tables in my MySQL database, they were shocked! One of them told me that she was taught never to delete already inserted rows from tables.
So I would like to find which is the best professional approach for this action.
Connect java application with cloud storage using sql
I want to create an application in java and I want to use online storage but how I can connect that application with cloud storage
vendredi 27 février 2015
PostgreSQL 9.x: a clustered index on column A is the same as creating a table ordered by A?
Reading the official PostgreSQL documentation for version 9.0 I read an interesting escamotage that performs better than CLUSTER for big tables:
The CLUSTER command reorders the original table by scanning it using the index you specify. This can be slow on large tables because the rows are fetched from the table in index order, and if the table is disordered, the entries are on random pages, so there is one disk page retrieved for every row moved. (PostgreSQL has a cache, but the majority of a big table will not fit in the cache.) The other way to cluster a table is to use:
CREATE TABLE newtable AS
SELECT * FROM table ORDER BY columnlist;which uses the PostgreSQL sorting code to produce the desired order; this is usually much faster than an index scan for disordered data. Then you drop the old table, use ALTER TABLE ... RENAME to rename newtable to the old name, and recreate the table's indexes. The big disadvantage of this approach is that it does not preserve OIDs, constraints, foreign key relationships, granted privileges, and other ancillary properties of the table — all such items must be manually recreated. Another disadvantage is that this way requires a sort temporary file about the same size as the table itself, so peak disk usage is about three times the table size instead of twice the table size.
The problem is that this suggestion doesn't appear in > 9.0 versions of the official documentation.
My question is if this escamotage is still valid for 9.1, 9.2, 9.3 and 9.4 because I'm stucked with a CLUSTER operation over two big tables (on of ~750milion rows and the other of ~1650milion rows) and average disk write/read speed is 3MB/s due to the CLUSTER algorithm explained in the official doc. It's a slow process over big tables, so I'd like to avoid it doing the "create ordered table over index-associated-column" trick. This will saves me days of DB processing.
MySQL:How much aprox disk space required for bin_log of the 80GB of DB?
I had total 80GB of database in MySQL server,and there is huge amount of trasaction are happend on daily basis,I need to be enable a bin_log so I required a recommendation how much disk space is reqired for that?
Mysql connect without username/password
I build a new website for a client (WordPress is underlying) and have access trough FTP to his webspace. He does not provide or even know more informations than this because he had taken it from an former employee.
Is it possible to get know if any MySQL database exist on localhost and connect to it without the knowledge of an username or password?
I dont think that the web space host could help me with this problem, because they switched there business orientation a while ago and don't provide any new web space at all.
Granting permission on an altered table in Teradata
I have a table, which has two columns say A and B as part of the Primary index PI_AB. I have to alter the length of B column - increase it.
I was planning to alter the primary index to just have A, then drop the column B, re-create or alter it to desired length and then again make it the part of primary index.
I cannot drop the table because it has certain grants which I won't be able to give it gain. So, I wanted to know will altering the table like this, affect the grants given to other users? Is there any other way to do it, without affecting the grants present on the table?
Any help will be appreciated.
User can see Oracle tables via ODBC that her account does not have access to
We have a user whose Oracle account, according to ALL_TAB_PRIVS, has access to tables A, B, and C. However, when the user creates an ODBC and connects to Oracle via Microsoft Access (using her Oracle credentials in the ODBC), she can also view data from D, E, and F. We know that she isn't sharing someone else's credentials because we had her create a new ODBC and a new MS Access database, and the issue is still happening. One of our DBA's ran a query (I don't know which system views or tables) that shows her username as having used MSACCESS.EXE and odbcad32.exe to connect to Oracle, but nothing more specific than that.
This is worrisome. How is ODBC apparently bypassing Oracle security?
EDIT: To clarify, the tables in question are not in the user's schema. We keep many business tables in a particular schema S and then grant users access to just those tables in that schema. Consequently, most of the end users' schemas are empty. Our workflow generally goes like this: 1. User X requests access to tables on S schema. 2. Oracle account X is created on the server. 3. X is granted access to specific S tables.
Can't connect to MS SQL Server
I'm trying to set up Peer-to-Peer Transactional Replication and I'm stuck at "Add a New Peer Node" step - I can't connect to another server.
In server name field I specify serverIP\DBNODE2 and SQL Server credentials.
I'm 100% sure, that credentials are correct, but I'm not sure about Server name part.
When I try to connect I get the following error: 
Any ideas what am I doing wrong?
How to limit the range of an insert-update to only look at certain rows/ rows with field value: X?
I posted something similar before, but am still unsure of the technical implementation --- I'm so close to being there to the finished product.
I have a table of tickets, and a stream of ticket updates sent to that table in an insert/ update fashion.
The problem is with each passing week, or day, etc ..... the table of tickets gets bigger and bigger, making the daily "stream" insert/update lookup take longer, and longer, and longer.
There is a way around this (I think).
Tickets where status = "closed" will never see an update --- they don't have to be looked up by insert/ update, in other words. They are also the vast majority of tickets.
So my question --- how the hell do I prevent a lookup of these tickets?
At first I'm thinking ... add a key .... the first one is simply update where ticket_id = ticket_id .... I could add another ... status <> "closed". But I'm not sure if checking this, and ticket_id, would actually save time. Would it? Or does it matter in which order the update keys are?
Second idea is to have two tables ... an active table, and archive table. Send all the updates through the active table, and then maybe once a day, copy all "closed" ticket rows from the active to the archive, and delete them in the active table. This definitely seems like a massive pain ... but it's a possibility. All lookups would require a table union as well using this option (or maybe view).
I'm not sure ... what do you guys think? Anyone have better ideas or thoughts? Thanks.
Stored procedure to check if username exists
I'm trying to write a stored procedure
So far I have this, but it always returns 1
CREATE DEFINER=`root`@`localhost` PROCEDURE `USER_EXISTS`(IN `USERNAME` VARCHAR(64) CHARSET utf8mb4)
SELECT CASE WHEN MAX(user_id) IS NULL THEN '0' ELSE '1' END User_exists
FROM `dbname`.`tablename`
WHERE `username` = USERNAME
The goal is to perform this via AJAX so the user can see whether registration is likely to succeed before submitting their registration form
Combining multiple rows into single column
The return from my SQL statement displays an example below:
id 1 | status | id 2 | name | category
001 | open | 011 | john | person
001 | open | 011 | john | male
How would I combine the multiple rows to the category column? Like the example below.
id 1 | status | id 2 | name | category
001 | open | 011 | john | person, male
Write output from dynamic queries in PL/pgSQL to CSV file
PG_CMD="psql -d portal -U portal -c "
PG_CMD_TP="psql -d portal -U portal -t -P format=unaligned -c "
abc()
{
$PG_CMD "
DO \$$
DECLARE
srowdata record;
customer_list varchar[];
customer_schema varchar(100);
portal_row a.portal%ROWTYPE;
var1 varchar(100);
temp varchar(100);
BEGIN
customer_list=ARRAY(select cname from customer);
FOREACH customer_schema IN ARRAY customer_list LOOP
EXECUTE format('select %s.portal.*,%s.p_fb_config.*,%s.p_gplus_config.*,%s.p_linkd_config.*,%s.p_localum_config.*,
%s.p_sms_config.*,%s.p_twt_config.*,%s.p_webform_config.*,%s.p_wechat_config.*,%s.p_clickthrough_config.*,%s.splash.*
from %s.portal left outer join %s.p_fb_config on %s.portal.pid = %s.p_fb_config.pid left outer join %s.p_gplus_config
on %s.portal.pid = %s.p_gplus_config.pid left outer join %s.p_linkd_config on %s.portal.pid = %s.p_linkd_config.pid left
outer join %s.p_localum_config on %s.portal.pid = %s.p_localum_config.pid left outer join %s.p_sms_config on
%s.portal.pid = %s.p_sms_config.pid left outer join %s.p_twt_config on %s.portal.pid = %s.p_twt_config.pid left outer join
%s.p_webform_config on %s.portal.pid = %s.p_webform_config.pid left outer join %s.p_wechat_config on
%s.portal.pid = %s.p_wechat_config.pid left outer join %s.p_clickthrough_config on
%s.portal.pid = %s.p_clickthrough_config.pid left outer join %s.splash on %s.portal.pid=%s.splash.pid;', customer_schema,
customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,
customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,
customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,
customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,
customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,
customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema) INTO srowdata;
raise notice ' %: %', customer_schema,srowdata;
END LOOP;
END;
\$$";
}
abc
I have written anonymous block in plpgsql. Now, instead of using raise notice I want to write this output in CSV format. I tried COPY but it accepts argument as a query.
Determine parent id when moving hierarchical data to different tables?
I've got several tables which contain category and tag relationships for different posts. I'm trying to move the categories from the posts table into their respective tables while maintaining their relationships and hierarchical position. One of the steps in accomplishing this, is getting the parent id for each set of categories.
Here is the database schema:
Here is some sample data but I have omitted some columns for simplicity.
posts
+--------+------------+------------+------------+------------+
|post_id | category1 | category2 | category3 | category4 |
+--------+------------+------------+------------+------------+
| 1 | television | series | comedy | station |
+--------+------------+------------+------------+------------+
| 2 | television | reality | comedy | station |
+--------+------------+------------+------------+------------+
meta
+-------+-----------+
|meta_id| meta_name |
+-------+-----------+
| 1 |television |
+-------+-----------+
| 2 | series |
+-------+-----------+
| 3 | comedy |
+-------+-----------+
| 4 | station |
+-------+-----------+
| 5 | reality |
+-------+-----------+
Since both posts have a parent category of television, I need to somehow set all the sub-categories with that parent. Like this.
meta_data
+------------+------------+------------+
|meta_data_id| meta_id | parent |
+------------+------------+------------+
| 1 | 1 | 0 | <--is parent category, so stays 0
+------------+------------+------------+
| 2 | 2 | 1 |
+------------+------------+------------+
| 3 | 3 | 1 |
+------------+------------+------------+
| 4 | 4 | 1 |
+------------+------------+------------+
| 5 | 5 | 1 | ^--remaining categories should be 1
+------------+------------+------------+
I'm trying to build my query something like this...
UPDATE meta_data SET parent =
(SELECT posts.category1, posts.category2, posts.category3, posts.category4
FROM posts
LEFT JOIN meta ON meta.meta_name = websites.category1
LEFT JOIN meta_data ON meta.meta_id = meta_data.meta_id)
WHERE ...
If you could please just guide me on how to accomplish this I would really appreciate it.
Configure Oracle DB to store backups on External Storage
How can we Configure Oracle DB to store backups on External Storage ? Oracle DB is a vm machine on ESXi Host
Let me know if you need more details.
Please advise.
Long running DBCC CHECKDB
I am on SQL 2014 Standard doing offline DBCC CHECKDBs (so on a box other than production). For the most part, my process is going pretty quick but for some reason I have this small DB (6gbs) and its taking hours to do the DBCC. Last time it ran it took 9 hours. It seems to freeze at different %'s completion when checking sys.dm_exec_requests. On this same server I'm doing CHECKDBs on databases in the terabytes range without a problem. I am using the following hints in the CHECKDB at this time (and, yes, eventually I'll do full instead of physical only)
WITH NO_INFOMSGS, ALL_ERRORMSGS, PHYSICAL_ONLY, TABLOCK
What could be messed up with this one DB that is causing it to take forever?
Database Design Question? Relationships and Form Data
I am working on a project that requires to store evaluation form data. Essentially, the a reviewer will fill out a form based on the performance of the person being evaluated. Now there is well over 40 questions in this form. They are all divided up into their own subset of questions.
Now that you have a general idea of what I am storing, I will go over what I have right now.
I knew that making a table that had 40 columns was not a smart way to structure it, so I decided to split the table up.
I have a tables called Form_Headers, Form_Header_Details, Form_References, and Form_Data.
Each row in Form_Data has an ID that points to it's referencing ID in Form_References. It also has an ID that references to Form_Header_Details which is a table that has IDs for all the form input names. Example: How do they interact with their peers?. Then each Form_Header_Details references its Form_Headers which are the categories for the Form_Header_Details.
It works, but I feel as if the table Form_Datais very messy. It's essentially all the form entries referencing other tables to connect it all together.
Is there a way to clean this up?
Query parent table and get child tables columns
I have two tables, one inherits the other:
CREATE TABLE parent (
col1 INTEGER
);
CREATE TABLE child (
col2 INTEGER
) INHERITS( parent );
Is there any way to query the parent table, such that it gets all of the columns of the child tables
// Insert some data
INSERT INTO parent( col1 ) VALUES( 1 );
INSERT INTO child( col1, col2 ) VALUES( 2, 2 );
// This query returns just the parent columns
SELECT * FROM parent;
| col1 |
| ---- |
| 1 |
| 2 |
// My desired query would return all columns in parent and child
SELECT.... // Some query, with desired result:
| col1 | col2 |
| ---- | ---- |
| 1 | NULL |
| 2 | 2 |
All users are having NO Privileges in phpMyAdmin
By mistake, I changed all the privileges of user 'root' in my MySQL Database. Not even a single privilege is having for that user. Now if I log in with another user I am able to access only one database. But I have nearly 25 databases created with 'root' user. If I want to access all other databases I need to GRANT Privileges for the root user again but all other users are not permitted to execute GRANT statement. So is there any way to grant permissions for the root user again. The all other Databases contained in my DB Server are very much important. So Please help in resolving this issue.
How do I do an outer join between all nested subqueries? (getting an error of 'OUTER' is not a valid input for this position)
I have this query:
SELECT AVG(a.score) AS Average1, AVG(b.score) AS Average2, AVG(c.score) AS Average3
FROM
(SELECT subfactor.score, dateID, EmployeeID
FROM employeescore
LEFT JOIN subfactor
ON employeescore.SubFactorID = subfactor.SubFactorID
LEFT JOIN factor
ON subfactor.FactorID = factor.FactorID
WHERE DateID='Jan2015' AND employeescore.EmployeeID='1' AND subfactor.FactorID='A1')a
OUTER JOIN
(SELECT subfactor.score, DateID, EmployeeID
FROM employeescore
LEFT JOIN subfactor
ON employeescore.SubFactorID = subfactor.SubFactorID
LEFT JOIN factor
ON subfactor.FactorID = factor.FactorID
WHERE DateID='Feb2015' AND employeescore.EmployeeID='1' AND subfactor.FactorID='A1')b
ON a.EmployeeID = b.EmployeeID
OUTER JOIN
(SELECT subfactor.score, DateID, EmployeeID
FROM employeescore
LEFT JOIN subfactor
ON employeescore.SubFactorID = subfactor.SubFactorID
LEFT JOIN factor
ON subfactor.FactorID = factor.FactorID
WHERE DateID='Mar2015' AND employeescore.EmployeeID='1' AND subfactor.FactorID='A1')c
ON b.EmployeeID = c.EmployeeID;
Basically I want to select the averages of the three scores and I want to display the output regardless if one of the joined sub queries doesn't have any records. But I keep getting an error of ('OUTER' is not a valid input for this position). How can I repair?
Subselect of MAX(Version) takes many minutes, though there's only ~20K records
I have a pretty complicated data model. I can't understand most SQL examples without an explanation of what's being modeled, so I'll try to explain.
Mainlines -> Releases -> Overlays -> Calibrations <- Parameters
Calibrations, then, is the child of the the Mainline-Release-Overlay chain, and Parameters. This is the set I want to return.
Now, the complexity is that -- to save space -- we are storing the base Mainline's calibrations, and then just the DIFFERENCES when changes happen at the Release and Overlay levels. This produces a "base" set of Calibrations of about 16K rows, and then a few hundred changes each Release, and perhaps just a few changes each Overlay. Deletions of parameters happen. To keep track of this, Calibrations has a Status field (tinyint) which is set to 1 for a deletion.
Overlays get versioned successively. So, to get a complete "calibration," we need to query the Calibrations table for the latest version of a Parameter's data, up to a particular Overlay version number. Parameter metadata may change, but the names stay the same, so these Calibrations may refer to differing versions of Parameters, albeit with the same name.
Up till now, the following has worked perfectly, and instantaneously (with the following caveats):
SELECT c.Parameter_ParameterID, p.Designation AS Name, c.Data, o.Version
FROM Calibrations c, Parameters p, Overlays o, Releases r
WHERE r.Mainline_MainlineID = 9
AND o.Release_ReleaseID = r.ReleaseID
AND c.Overlay_OverlayID = o.OverlayID
AND c.Parameter_ParameterID = p.ParameterID
AND o.Version =
(SELECT MAX(o1.Version)
FROM Parameters p1, Calibrations c1, Overlays o1, Releases r1
WHERE r1.Mainline_MainlineID = 9
AND o1.Release_ReleaseID = r1.ReleaseID
AND c1.Overlay_OverlayID = o1.OverlayID
AND c1.Parameter_ParameterID = p1.ParameterID
AND p1.Designation = p.Designation -- New condition
AND o1.Version <= 68)
AND c.Status != 1 -- Changed from boolean to tinyint
ORDER BY Version DESC, Name
The problem has come in since we have started (properly) tracking Parameter versions, so the inner match for the Parameter can no longer by on the ParameterID, but rather the Designation (i.e., Name). At first this wasn't a problem, but it is now that Calibration Status is no longer a boolean for deletion. Now it can also be set to 2 for newly introduced, and 3 for reinserted.
Adding these two changes have caused this query to take 10-15 minutes to run! How is that even possible? Even if I contorted the query to connect every row to every other row, it shouldn't take this long!
There are two maddening things here. One is that, when this initially became a problem, I put an index on the Parameters Designation field, and it made it work again. Now, deleting and recreating it doesn't make a difference. Also, there's still a special case, in that, if I have exactly 2 Mainlines in the database, it still runs instantaneously. If I have only 1 or more than 2, it takes forever.
I've tried variously commenting out sections, like the o1.Version <= condition and the c.Status != condition, and sometimes, it works again. I can't get a handle on the differences in the various cases, and I'm at the end of my rope. I know I'm a tiny trick away from getting it to run fast again, but it's apparently beyond me, and this is a show-stopper for my beta plans next month.
I've been programming for 35 years, but I've never had to deal with SQL like this before, and SSMS's tools for showing explain plans mean nothing to me. I have no reputation on this site, so I don't have rep to spend, and I hope it's not a violation of this site's TOS, but I'd PayPal someone $100 who can fix this for me, and help me understand the fundamental issues.
Imported a new month of data, now getting Numeric data overflow error when running query
We have an ETL job that brings in monthly revenue, and I've never experienced any issues with the data in terms of not being able to return results. The ETL job succeeded, but when I run a query to sum up total revenue for last month, I get this error:
Numeric data overflow (result precision)
We are running Amazon Redshift. I've never seen this error, and it happens whether I try to sum up total revenue, or get a distinct list of territories, for instance (without trying to return numerical data). The revenue data is stored as NUMERIC(38,7). I don't have permissions to query the raw source tables since they reside in another team's data warehouse, but I'm wondering what could be happening since the load into our database worked just fine.
Sample query I'm trying to run:
SELECT SUM(total_revenue)
FROM monthly_revenue
WHERE date_trunc('month',revenue_reporting_date) = '01-JAN-2015'
This is the full error it spits out (from SQL Workbench):
ERROR: Numeric data overflow (result precision)
Detail:
-----------------------------------------------
error: Numeric data overflow (result precision)
code: 1058
context: 64 bit overflow
query: [redacted]
location: numeric.hpp:149
process: query1_192 [pid=18716]
-----------------------------------------------
Location: File: /home/awsrsqa/padb/src/sys/xen_execute.cpp, Routine: pg_throw, Line: 5696
Server SQLState: XX000
Execution time: 6.58s
1 statement failed.
I've tried running this via SQL Workbench and Aginity, as well as within our own ETL tool. All are giving the same error. Any ideas?
Oracle Linked Server only works with Sql Login
I'm having this weird error for some time.
When I login to sql server and use the linked server
SELECT * FROM OPENQUERY(MEGA, 'SELECT 1 FROM DUAL')
I receive this error
OLE DB provider "OraOLEDB.Oracle" for linked server "MEGA" returned message "ORA-01017: senha/nome do usuário inválido; log-on negado". Msg 7303, Level 16, State 1, Line 2 Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "MEGA".
One must think it's a username/password error but when I recreate my linked server login it still doesn't work, only for a non Windows Authentication login (aka SQL Login)
Oracle Linked Server only works with Sql Login
I'm having this weird error for some time.
When I login to sql server and use the linked server
SELECT * FROM OPENQUERY(MEGA, 'SELECT 1 FROM DUAL')
I receive this error
OLE DB provider "OraOLEDB.Oracle" for linked server "MEGA" returned message "ORA-01017: senha/nome do usuário inválido; log-on negado". Msg 7303, Level 16, State 1, Line 2 Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "MEGA".
One must think it's a username/password error but when I recreate my linked server login it still doesn't work, only for a non Windows Authentication login (aka SQL Login)
Error with FUNCTION ( SQL Server 2008 ) - ''Cannot find either column...'
Friends,
I have this Function:
create function [dbo].[fn_xxx]
(
@Nome varchar(100),
@Descricao varchar(500)
)
RETURNS @tbDados table
(
Qtd int
)
as
Begin
declare @CMD as nvarchar(max)
declare @Tabela varchar(100)
declare @CampoDescricao varchar(100)
--- *** Identifica os campos e tabelas
set @Tabela = 'tbl' + @Nome
set @CampoDescricao = 'Desc' + @Nome
set @CMD = '
insert into @tbDados (Qtd) values
select @Qtd = COUNT(*) from ' + @Tabela + ' where ' + @CampoDescricao + ' = ''' + replace(@Descricao, '''', '`') + ''''
exec sp_executesql @CMD
return
end
I'm trying to help developers here, but with no success.
When we execute it ( select dbo.fn_xxx ('processador','teste')) it give us this error:
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_xxx", or the name is ambiguous.
I've been searching through the internet, and found some results, but it was all about the code.
can someone help me how to figure out this error in this function?
Thanks
EDIT:
If i execute it with:
SELECT * FROM [EasyTablet].[dbo].[fn_xxx] (
'processador','teste'
)
GO
It shows me this error:
Only functions and some extended stored procedures can be executed from within a function.
When does SQL Server follow the Minimum Password Age policy?
According to this SQL Server 2008 whitepaper, SQL Server should only pay attention to the Minimum Password Age policy when the Check_Expiration (Enforce Password Expiration) option is turned on. Page 7 in the whitepaper specifically says "CHECK_EXPIRATION uses the minimum and maximum password age part of the Windows Server 2003 policy, and CHECK_POLICY uses the other policy settings". However, that's not what I'm finding. It looks like you only need Check_Policy (Enforce Password Policy) turned on.
For example, I have a SQL Login (using SQL Server authentication) with Enforce Password Policy turned on, but Enforce Password Expiration turned OFF. My domain's password policy has Minimum Password Age set to 1. I can use sp_password to change the password once, but when I try to change it a second time, I get this error:
Server: Msg 15114, Level 16, State 1, Line 1 Password validation failed. The password for the user is too recent to change.
Is the documentation in this whitepaper simply wrong? Why is this happening when Check_Expiration is off? I also found this site which also says that Minimum Password Age should only be enforced when Check_Expiration is on. I think this is true for MAXIMUM Password Age, but I'm finding that Minimum Password Age is still getting enforced when Check_Expiration is off.
Is there something funky about my configuration? Should Minimum Password Age really be enforced when Check_Expiration is off (but Check_Policy is on)?
When does a port get assigned if SQL Server is set to TCP Dynamic Ports
I'm trying to determine when a dynamic port is assigned to SQL Server. If the TCP/IP Properties have the TCP Dynamic Ports enabled, does that port get assigned at the time the Server is installed or does it get assigned each time the instance is started. Is it therefore possible that a new port will get assigned each time the instance is restarted? I'm mostly curious about the 2008 through 2014 versions.
Best place to keep backup scripts
Where is the best place to leave the Database Backup scripts, currently I let them on the Backup Job itself, however I wonder if it's best to leave on a Database
SSMS returns results immediately; ADO takes longer
I am running a query that returns ~250k results.
- It is basically
SELECT * FROM my_schema.my_view(and the view is somewhat complex, and all table references in it are two-part) - I am not passing any parameters
- I have tried setting
ARITHABORTtoONand usingOPTION (RECOMPILE) - Within 2 seconds of executing, SSMS has returned ~23k results
- SSMS takes ~1m to return all ~250k results
- With ADO and the same query, it takes ~1m to obtain the first result from the
DataReader PARAMETERIZATIONis set toSIMPLE
I have read http://ift.tt/V20pUB and about a couple dozen other SO/DBA answers trying to make sense of this but I just can't seem to get my results streaming any faster. It almost seems like when I use ADO, SQL Server tries to store all of the results in a temporary table before returning.
What else can I check or try to get my results streaming immediately like they do in SSMS?
Join to a Culture Table vs Select Culture ID
I have a query that is executed several hundred thousand times a day. Due to legacy support, the incoming variables on this SP cannot change. Currently it looks like this:
Select id,
name,
otherdata
FROM Table1 t1
--<Various joins with aliases>--
INNER JOIN Culture c on c.id = t1.ID_Culture
WHERE
c.CultureTag = @CultureTag -- Usually 'en-US'
My question is: Would it be more efficient to remove the join to the culture table, and instead, SELECT the value of the primary key on the culture table and use THAT in the where clause? Would it be negligible?
Why would a query work slower when a where clause is added?
I've got two databases and both have got the same view over the same table which has the same indexes.
The view selects the top location for a given IMEI from a locations table.
CREATE VIEW [dbo].[LatestDeviceLocation]
AS
SELECT DISTINCT t.Imei, t.Accuracy, t.UserId, t.Lat, t.Lng, t.Timestamp
FROM (SELECT Imei, MAX(Timestamp) AS latest
FROM dbo.DeviceLocation
GROUP BY Imei) AS m INNER JOIN
dbo.DeviceLocation AS t ON t.Imei = m.Imei AND t.Timestamp = m.latest
GO
I'm querying the view with a very simple select with what seems like a very simple where clause.
SELECT TOP 1000 [Imei]
,[Accuracy]
,[UserId]
,[Lat]
,[Lng]
,[Timestamp]
FROM [dbo].[LatestDeviceLocation]
Where [Timestamp] > '2015-02-19T00:00:00.000Z' AND [Timestamp] < '2015-02-26T23:59:59.999Z'
On my live server when I query my view I get data back in < 1 second. When I add a where clause Where [Timestamp] > '2015-02-19T00:00:00.000Z' AND [Timestamp] < '2015-02-26T23:59:59.999Z' that jumps up to approximately 1 minute.
On my test server which has 10x more data (350k+ locations shared by approximately same number if Imei numbers as the live site, 25) the query returns data in < 1 second with or without the where clause.
I've looked for locks and can't see any.
I've re-created the index incase it was corrupted and no difference.
I've completely removed the index, performance didn't change.
This is the index that I've used on both servers.
/****** Object: Index [GangHeatMapIndex] Script Date: 02/26/2015 22:38:38 ******/
CREATE NONCLUSTERED INDEX [GangHeatMapIndex] ON [dbo].[DeviceLocation]
(
[UserId] ASC,
[Timestamp] ASC,
[Imei] ASC
)
INCLUDE ( [DeviceLocationId],
[Accuracy],
[Lat],
[Lng]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Edit: I've just realised that I wasn't looking in the right place for locks. It is taking out object locks when querying. I'm trying to work out how to write my view with "no lock" built into the view.
Edit 2: I've attached the execution plans, top on is with the index, bottom is without.
Edit 3: More executions plans, this time all on the live server, with the index re-added, with and without where clauses.
Edit 4:
I've changed the view to use a common table expression as follows and the performance is much better.
WITH cte
AS (SELECT Rank()
OVER (
partition BY dloc.[Imei]
ORDER BY dloc.[Timestamp], devicelocationid DESC) AS arank,
dloc.*
FROM [dbo].[DeviceLocation] AS dloc)
SELECT [Imei], [Accuracy], [UserId], [Lat], [Lng], [Timestamp]
FROM cte
WHERE arank = 1
Including the device DeviceLocationId in the order by prevented any duplicates occurring in the final result.
corrupt Access database file linked with SQL
I have a corrupt Access table and i don’t know how to handle the problem. When I am making any changes of the record in this file, the rows in the table shows me only ‘#Deleted’ message instead of my data. All other files opens with no problem, only this one has such weird behavior. This table also linked with SQL server. I ensured the second DB has a primary key and Access is able to recognize it. Just in case I relinked the table, but problem still persists. What else can I do?
Use EMCLI to update OEM Oracle Home Path
Is is possible to use the Enterprise Manager Command Line Interface (EMCLI) to change the Oracle Home Path?
We recently upgraded many databases, listeners, and ASMs, and I'd like to automate this process:
- Select the target
- Go to Target Setup --> Monitoring Configuration
- Change "Oracle Home Path", click Next, click Submit.
I looked through the EMCLI Verb Reference and could not find a way to do this.
Why adding SQL Server user to "Perform volume maintenance tasks" improves database resizing so much?
If I want to create 5GB database with
CREATE DATABASE [test]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'test', FILENAME = N'E:\2012\test.mdf' , SIZE = 5529600KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'test_log', FILENAME = N'E:\2012\test_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
it takes 1 minute on my SSD.
But when I add SQL Server user to Perform volume maintenance tasks
it takes only 1-2 seconds.
Why is that? Can someone explain to me what are the reasons for this?
Query results that not are in previous query result
I want to retrieve from my table the id_product of the products which haven't got some attribute and it's value is not the ones i want
For example i want the id_product of the products in which attribute = 'Product Type' and value != 'Motherboard' AND value != 'Intel Motherboard'
I have the following table: Atr_basic
column | type | attributes
---------------+----------------------+-----------------------------------------------------------
id | int | (identity) primary key
attribute | varchar | not null
value | varchar | not null
category | int | not null
id_product | int | not null
The query:
SELECT id_product
FROM Atr_basic
WHERE attribute = 'Product Type'
AND value != 'Motherboard' AND value != 'Intel Motherboard' AND category = 140
The problem with this query it's only give me the id_product from the products they have Attribute = 'Product Type' but some product doesn't have Attribute = 'Product Type'
I managed to do this query but it takes 120seconds to complete:
SELECT id_product
FROM Atr_basic as ab
WHERE id_product
NOT IN (SELECT a.id_product
FROM Atr_basic as a
WHERE a.attribute = 'Product Type'
AND a.value != 'Motherboard'
AND a.value != 'Intel Motherboard'
AND Category = 140)
Very high CPU caused by mysql service
A running mysql service uses CPU in a crazy way. (See attached screenshot from htop)
I followed the instructions here with no luck.
My server specs:
- CPU: 1900Mhz . Intel Hexa-Core Xeon E5-2420
- Number of CPUs: 2. Number of cores: 6.
- RAM: 8GB
- Hard disks: 4 × 300GB SAS
- RAID 10
- OS: Cent OS 6.5
Anything I can do?
SHOW PROCESSLIST; output 
DB query:
SELECT COUNT(*) AS expression FROM (SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.language AS node_language, node.created AS node_created, users_node.picture AS users_node_picture, users_node.uid AS users_node_uid, users_node.name AS users_node_name, users_node.mail AS users_node_mail, taxonomy_term_data_node.tid AS taxonomy_term_data_node_tid, taxonomy_term_data_taxonomy_term_hierarchy.tid AS taxonomy_term_data_taxonomy_term_hierarchy_tid, 'node' AS field_data_field_news_photo_node_entity_type, 'node' AS field_data_field_op_section_term_node_entity_type, 'node' AS field_data_body_node_entity_type, 'user' AS field_data_field_user_realname_user_entity_type, 'user' AS field_data_field_user_organization_user_entity_type, 'user' AS field_data_field_profile_picture_user_entity_type, 1 AS expression FROM node node LEFT JOIN users users_node ON node.uid = users_node.uid LEFT JOIN (SELECT td.*, tn.nid AS nid FROM taxonomy_term_data td LEFT JOIN taxonomy_vocabulary tv ON td.vid = tv.vid LEFT JOIN taxonomy_index tn ON tn.tid = td.tid WHERE (tv.machine_name IN ('sections')) AND (td.language IN ('ar', 'und')) ) taxonomy_term_data_node ON node.nid = taxonomy_term_data_node.nid LEFT JOIN taxonomy_term_hierarchy taxonomy_term_data_node__taxonomy_term_hierarchy ON taxonomy_term_data_node.tid = taxonomy_term_data_node__taxonomy_term_hierarchy.tid LEFT JOIN taxonomy_term_data taxonomy_term_data_taxonomy_term_hierarchy ON taxonomy_term_data_node__taxonomy_term_hierarchy.parent = taxonomy_term_data_taxonomy_term_hierarchy.tid LEFT JOIN field_data_field_news_photo field_data_field_news_photo ON node.nid = field_data_field_news_photo.entity_id AND (field_data_field_news_photo.entity_type = 'node' AND field_data_field_news_photo.deleted = '0') WHERE (( (node.status = '1') AND (node.type IN ('news')) AND (field_data_field_news_photo.field_news_photo_fid IS NOT NULL ) AND (node.language IN ('ar')) )))
How to put the results of a query into a variable?
I have written a Postgres anonymous block. In which I have written join query on multiple tables like:
select a.portal.*, a.p_fb_config.*, a.p_gplus_config
from a.portal
left outer join a.p_fb_config on a.portal.pid = a.p_fb_config.pid
left outer join a.p_gplus_config on a.portal.pid = a.p_gplus_config.pid;
Now I want to catch this value in variable. So how should I declare that variable?
I need something like:
portal_row a.portal%ROWTYPE;//for 3 tables
Why am I able to query a remote database without a dblink?
While researching a security concern, I discovered that users are able to query a remote database without the need of a dblink.
However, this was only true for databases that had no value for DB_DOMAIN. If the local database's DB_DOMAIN was qualified with "foo.com", then a user could not query the remote DB and required a dblink.
I could not find any documentation that explains why this happens and am a very new DBA. What is it about Oracle that allows this to happen? Is the only way to prevent this to qualify every database's DB_DOMAIN with "foo.com"?
Example of accessing information from remote DB without a dblink below:
From DB: FooB
SQL*Plus: Release 10.2.0.5.0 - Production on Thu Feb 26 16:05:21 2015
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter db_domain;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string
From DB:FooA
SQL*Plus: Release 10.2.0.5.0 - Production on Thu Feb 26 16:08:25 2015
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter db_domain;
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
db_domain string
SQL> select sysdate from dual@FooB;
SYSDATE
---------
26-FEB-15
SQL> select db_link from all_db_links;
no rows selected
SSIS Export Ragged Right Fixed Width format File
I created a ssis project to export a fixed width file to an external system. This file has 12 fields and they all position correctly per the external system requirements. I use Ragged Right format because this file can have hundreds of records and need them on there own line. Somehow when loading this file into the external system it fails. The strange thing is that if i delete a space at the end and then try to reload this file into external system it works. Is Ragged Right format causing an extra column? What can i do to fix this?
best way to query for a list and then run a query for each result in that list (postgre)
I am new to databases in general and particularly new to postgresql (v 9.3). I often find myself in the situation that I first run a select statement which will return a list of users stored in an array. I then want to run a query for each user identified in the array. Right now I do this with a php for loop, but I know there must be a way to do this with prepared statements and looping within postgre. However I have not been able to find a good tutorial on how to do this, particularly how to store the results. Can anyone point me to such a resource or provide short sample code to combine a query for an array followed by a query for each item of that array and then returning the amalgamated results of these queries?
Any help would be appreciated.
Optimizing queries for 25+ million rows
I am using MS SQL, and I have to run several queries on the same table on different criteria. At first I ran each query on the original table although they all share some filtering (i.e Date, status). This took a lot of time (around 2 minutes).
There are duplicates in data rows, and all indexes are NON-CLUSTERED. I am only interested in 4 columns for my criteria and the result should output the count only, for all queries.
columns needed: TABLE, FIELD, AFTER, DATE, and there is an index on each of DATE and TABLE.
After creating a temp table with only the fields I need, it went down to a 1:40 minutes, which is still very bad.
CREATE TABLE #TEMP
(
TABLE VARCHAR(30) NULL,
FIELD VARCHAR(30) NULL,
AFTER VARCHAR(1000) NULL,
DATE DATETIME,
SORT_ID INT IDENTITY(1,1)
)
CREATE CLUSTERED INDEX IX_ADT ON #TEMP(SORT_ID)
INSERT INTO #TEMP (TABLE, FIELD, AFTER, DATE)
SELECT TABLE, FIELD, AFTER, DATE
FROM mytbl WITH (NOLOCK)
WHERE TABLE = 'OTB' AND
FIELD = 'STATUS'
Runnig this -> (216598 row(s) affected)
Since not all queries rely on date range, I didn't include it in the query. The problem is that it's taking well above 1 minute to insert only. The above insert took 1:19 mins
I want to run something like this for several queries:
SELECT COUNT(*) AS COUNT
FROM #TEMP
WHERE AFTER = 'R' AND
DATE >= '2014-01-01' AND
DATE <= '2015-01-01'
It's a problem with the insert more than that of the selection, but the temp has way less rows than the original table which could be better than going through the table several times.
How can I optimize this?
T-SQL bulk insert fails on specific word "DENY" in string-data
I have CSV files structured as string ; string ; string
First string is quoted with "". The other 2 strings are not.
I'm bulk inserting them into a table using nchar(x) for each of the 3 columns with a sufficiently large value for x.
BULK INSERT [table] FROM 'import.csv' WITH (FIELDTERMINATOR = ';')
This has been working for a couple of years.
Since a few days the import is giving issues on some rows. After investigation each of the failing rows gets a conversion/type mismatch error on the 3rd column.
The thing that has changed about the data is that in the middle of this 3rd string the text (DENY) is now present in each of the failing rows. This was previously never the case.
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1019, column 3 (Rights).
Any ideas why the presence of the substring (DENY) causes this to happen ?
(I know it is a SQL reserved word, but why is it parsed in this position ?)
How to work around it ? I still need those rows to be imported, including that text.
Sample content of column 3:
(I)(OI)(CI)(F)
(I)(OI)(CI)(DENY)(WDAC,WO,S,DC)
(I)(OI)(CI)(F)
The 2nd line gives the error. The other 2 import fine.
(And in case you are wondering: Yes, it is ICACLS output that I am parsing.)
Multiple records and determine if a combination if values exist and write this to a new column
I'm struggling with a problem.
An order can have multiple lines and accordingly a type (F=Footwear/N=Non-FTW,Mixed=Both) I need to determine if the order consists of which type and create a new column with this information.
ORDR | TYPE
-----------------------------------------
#31900440220151005215630000 | F
#31900440220151005215630000 | N
#31900440220151005215630000 | N
#31900440220151005215680000 | N
#31900440220151005215680000 | N
#31900440220151005215680000 | N
#31900440220151005215680000 | N
#31900440220151005215680000 | N
#31900440220151005215710000 | F
#31900440220151005215720000 | F
#31900440220151005215740000 | F
What I want to have is ultimately:
ORDR | TYPE | New column
------------------------------------------------
#31900440220151005215630000 | F | Mixed
#31900440220151005215630000 | N | Mixed
#31900440220151005215630000 | N | Mixed
#31900440220151005215680000 | N | Non-FTW
#31900440220151005215680000 | N | Non-FTW
#31900440220151005215680000 | N | Non-FTW
#31900440220151005215680000 | N | Non-FTW
#31900440220151005215680000 | N | Non-FTW
#31900440220151005215710000 | F | FTW
#31900440220151005215720000 | F | FTW
#31900440220151005215740000 | F | FTW
I thought of partition over but currently i'm stuck.
Migrating Oracle to MySQL: MySql Workbench Not Available
I am currently trying to migrate information from a query of a remote Oracle database to a local MySQL database. I have poked around and have found it has been frequently suggested to use the Migration tool in MySQL Workbench. However, I am developing through ssh and only have control of the linux cloud environment I'm working in through the terminal.
Any suggestions would be greatly appreciated.
mysql import failed caused by overgrown general log file
I have a table about 70G in size. I took a backup using mysqldump.
In the new server, I performed a restoration.
time mysql -uroot -p TDB < TDB.sql Enter password: ERROR 3 (HY000) at line 58155: Error writing file '/app/mysql/admin/tmp/MLLVx92e' (Errcode: 28 - No space left on device)
Further checked, showed that the general log file in /app has increased tremendously.
show variables 'general_log_file' general_log_file | /app/mysql/admin/mysql.log
Does importing using mysql causes general log file to grow exponentially?
split string into multiple rows with multiple columns
i have a string with user information. I have to write a function that takes string as input and inserts into some table.
Input string contains multiple rows with multiple columns like following
inputString= "1,cassey,1222,12-12-12:2,timon,,02-02-12:3,john,3333,03-03-12"
what i want is to create insert from this...
any one has idea, how it can be achieved????
How to restore transaction log with multiple files
I restore database logs like :
RESTORE LOG [paytrn] FROM DISK = N'C:\backup\log.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 100
GO
RESTORE LOG [paytrn] FROM DISK = N'C:\backup\log.trn' WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 100
GO
But have many files , is there any way to restore all files without write restore for every file? Something like :
RESTORE LOG [paytrn] FROM DISK = N'C:\backup\log.trn' WITH FILE = ALL, NORECOVERY, NOUNLOAD, STATS = 100
Some script which will be update transaction log in sequential order
Display a one-to-many relationship in one row
I have a "one to many" relationship in two tables:
Table "products" contains: product_id, product_descr
Table "packing" contains: product_id, package_id, package_desc
One product has often more than one package. I want to create a query in Microsoft Access, where I get all the package options for one product in one row:
product_id package_id1 package_desc1 packageid2 package_desc2 packageid_n package desc_n
The products do have a different number of packages, it's not a fixed number.
statistics are up to date, but estimate is incorrect
When I do dbcc show_statistics ('Reports_Documents', PK_Reports_Documents) I get the following result for Report ID 18698:
For this query:
SELECT *
FROM Reports_Documents
WHERE ReportID = 18698 option (recompile)
I get a query plan that makes a Clustered Index Seek on PK_Reports_Documents as expected.
But what baffles me is the incorrect value for Estimated Number of Rows:
According to this:
When the sample query WHERE clause value is equal to a histogram RANGE_HI_KEY value, SQL Server will use the EQ_ROWS column in the histogram to determine the number of rows that are equal to
This is also the way I would expect it to be, however it seems not to be the case in real life. I also tried some other RANGE_HI_KEY values that were present in the histogram provided by show_statistics and experienced the same. This issue in my case seems to cause some queries to use very unoptimal execution plans resulting in an execution time of a few minutes whereas I can get it to run in 1 sec with a query hint.
All in all: Can someone explain me why is EQ_ROWS from the histogram not being used for the Estimated Number of Rows and where does the incorrect estimate come from?
A bit more (possibly helpful) info:
- Auto create statistics is on and all the statistics are up to date.
- The table being queried has about 80 million rows.
PK_Reports_Documentsis a combination PK consisting ofReportID INTandDocumentID CHAR(8)
Update 1: The query seems to load a total of 5 different statistics objects, all of which contain ReportID + some other columns from the table. They have all been freshly updated. RANGE_HI_KEY in the table below is the highest upper bound column value in the histogram.
+-------------------------------------------------------------------------+----------+--------------+--------------+---------------------+--------------+------------+----------+---------------------+----------------+
| name | stats_id | auto_created | user_created | Leading column Type | RANGE_HI_KEY | RANGE_ROWS | EQ_ROWS | DISTINCT_RANGE_ROWS | AVG_RANGE_ROWS |
+-------------------------------------------------------------------------+----------+--------------+--------------+---------------------+--------------+------------+----------+---------------------+----------------+
| PK_Reports_Documents | 1 | 0 | 0 | Stationary | 18722 | 0 | 2228,526 | 0 | 1 |
| _dta_index_Reports_Documents_42_1629248859__K1_K63_K14_K13_K22_K23_72_6 | 62 | 0 | 0 | Stationary | 18698 | 0 | 2228,526 | 0 | 1 |
| _dta_stat_1629248859_1_1_59 | 76 | 0 | 1 | Stationary | 18686 | 50,56393 | 1 | 0 | 13397,04 |
| _dta_stat_1629248859_1_22_14_18_12_6 | 95 | 0 | 1 | Stationary | 18698 | 0 | 2228,526 | 0 | 1 |
| _dta_stat_1629248859_1_7_14_4_23_62 | 96 | 0 | 1 | Stationary | 18698 | 56,63327 | 21641,5 | 0 | 14526,44 |
+-------------------------------------------------------------------------+----------+--------------+--------------+---------------------+--------------+------------+----------+---------------------+----------------+
jeudi 26 février 2015
Mongo DB accessesNotInMemory issue with RAM size
I am using Mongo Replica set and it seems to be working fine but when i check db.serverStatus i saw that there are lots of accessesNotInMemory for all The DBs in my mongo instance.
I checked MY RAM and the configuration is as follows:
RAM size : 32 GB
Total Indexes in all DBs : 12 GB
Data in all DBs : 80 GB
Top command shows me 18 GB memory uses and Mongo process is using 51% of available memory.
Could you please tell em why I am getting these accessesNotInMemory even so much memory is still free.
More about my application is writes user activity on my website.
I have two systems in place at DB.
1. Sharded Set UP : Set takes all load for each write operation.
2. Processed Data Set UP: Store Data resulted from various aggregation script.
Here I am talking about second set up which takes data at each hour when my aggregation script run.
So here is the sue case for this:
1. Data inserted/Updated in various logical DBs at every hour.
2. All read APIs served through this MongoD instance.
3. No Direct/ frequent write is done on the DB.
I made some changes in my APIs to reduce index overhead for sorting purpose. I am not sorting data in DB query i pull it for the query range and sort it in java.
This is my application behaviour.
TOP command output
top - 10:41:20 up 38 days, 20:05, 3 users, load average: 0.00, 0.00, 0.00
Tasks: 175 total, 1 running, 174 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.1%us, 0.1%sy, 0.0%ni, 99.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 33103424k total, 18615444k used, 14487980k free, 243916k buffers
Swap: 3884024k total, 63524k used, 3820500k free, 16571920k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3700 root 20 0 157g 16g 16g S 2.0 52.9 1378:56 mongod
serverStatus output
firstset:PRIMARY> db.serverStatus({workingSet:1});
{
"host" : "MongoDB33209",
"version" : "2.6.4",
"process" : "mongod",
"pid" : NumberLong(3700),
"uptime" : 3344876,
"uptimeMillis" : NumberLong("3344876756"),
"uptimeEstimate" : 3297769,
"localTime" : ISODate("2015-02-27T05:12:09.797Z"),
"asserts" : {
"regular" : 0,
"warning" : 0,
"msg" : 0,
"user" : 169,
"rollovers" : 0
},
"backgroundFlushing" : {
"flushes" : 55747,
"total_ms" : 3522610,
"average_ms" : 63.18922991371733,
"last_ms" : 13,
"last_finished" : ISODate("2015-02-27T05:11:26.453Z")
},
"connections" : {
"current" : 32,
"available" : 19968,
"totalCreated" : NumberLong(253960)
},
"cursors" : {
"note" : "deprecated, use server status metrics",
"clientCursors_size" : 1,
"totalOpen" : 1,
"pinned" : 0,
"totalNoTimeout" : 8,
"timedOut" : 137
},
"dur" : {
"commits" : 30,
"journaledMB" : 0,
"writeToDataFilesMB" : 0,
"compression" : 0,
"commitsInWriteLock" : 0,
"earlyCommits" : 0,
"timeMs" : {
"dt" : 3073,
"prepLogBuffer" : 0,
"writeToJournal" : 0,
"writeToDataFiles" : 0,
"remapPrivateView" : 0
}
},
"extra_info" : {
"note" : "fields vary by platform",
"heap_usage_bytes" : 217828936,
"page_faults" : 7206470
},
"globalLock" : {
"totalTime" : NumberLong("3344876757000"),
"lockTime" : NumberLong("4239567675"),
"currentQueue" : {
"total" : 0,
"readers" : 0,
"writers" : 0
},
"activeClients" : {
"total" : 0,
"readers" : 0,
"writers" : 0
}
},
"indexCounters" : {
"accesses" : 3716993293,
"hits" : 3716993312,
"misses" : 0,
"resets" : 0,
"missRatio" : 0
},
"locks" : {
"." : {
"timeLockedMicros" : {
"R" : NumberLong(580972483),
"W" : NumberLong("4239567675")
},
"timeAcquiringMicros" : {
"R" : NumberLong("17175041218"),
"W" : NumberLong(1530253880)
}
},
"admin" : {
"timeLockedMicros" : {
"r" : NumberLong(4113636),
"w" : NumberLong(0)
},
"timeAcquiringMicros" : {
"r" : NumberLong(221171),
"w" : NumberLong(0)
}
},
"local" : {
"timeLockedMicros" : {
"r" : NumberLong(877458682),
"w" : NumberLong("4483497401")
},
"timeAcquiringMicros" : {
"r" : NumberLong("56622055369"),
"w" : NumberLong("5434791334")
}
},
"ProcessDB" : {
"timeLockedMicros" : {
"r" : NumberLong("8406271110"),
"w" : NumberLong("11271116631")
},
"timeAcquiringMicros" : {
"r" : NumberLong("39367371311"),
"w" : NumberLong(374059462)
}
},
"Month" : {
"timeLockedMicros" : {
"r" : NumberLong(377),
"w" : NumberLong(0)
},
"timeAcquiringMicros" : {
"r" : NumberLong(12891),
"w" : NumberLong(0)
}
},
"test" : {
"timeLockedMicros" : {
"r" : NumberLong(1084729),
"w" : NumberLong(0)
},
"timeAcquiringMicros" : {
"r" : NumberLong(220763),
"w" : NumberLong(0)
}
},
"Author" : {
"timeLockedMicros" : {
"r" : NumberLong(9542350),
"w" : NumberLong(103769)
},
"timeAcquiringMicros" : {
"r" : NumberLong(2776615),
"w" : NumberLong(2249)
}
},
"MonthDB" : {
"timeLockedMicros" : {
"R" : NumberLong(38255),
"W" : NumberLong(0),
"r" : NumberLong(575420785),
"w" : NumberLong("9184847978")
},
"timeAcquiringMicros" : {
"R" : NumberLong(301940),
"W" : NumberLong(0),
"r" : NumberLong(108467712),
"w" : NumberLong(530000052)
}
},
"DateDB" : {
"timeLockedMicros" : {
"r" : NumberLong(289821),
"w" : NumberLong(0)
},
"timeAcquiringMicros" : {
"r" : NumberLong(81323),
"w" : NumberLong(0)
}
},
"History" : {
"timeLockedMicros" : {
"R" : NumberLong(38311),
"W" : NumberLong(0),
"r" : NumberLong("5662251982"),
"w" : NumberLong("22748548802")
},
"timeAcquiringMicros" : {
"R" : NumberLong(461922),
"W" : NumberLong(0),
"r" : NumberLong("2213086691"),
"w" : NumberLong(791624945)
}
},
"analytics" : {
"timeLockedMicros" : {
"r" : NumberLong(22922884),
"w" : NumberLong(340925)
},
"timeAcquiringMicros" : {
"r" : NumberLong(1668266970),
"w" : NumberLong(25641)
}
},
"Week" : {
"timeLockedMicros" : {
"R" : NumberLong(20),
"W" : NumberLong(0),
"r" : NumberLong(487853692),
"w" : NumberLong("9679431619")
},
"timeAcquiringMicros" : {
"R" : NumberLong(421116),
"W" : NumberLong(0),
"r" : NumberLong(268862498),
"w" : NumberLong(732862695)
}
}
},
"network" : {
"bytesIn" : 68321919874,
"bytesOut" : 78858702480,
"numRequests" : 205834437
},
"opcounters" : {
"insert" : 114008924,
"query" : 467646,
"update" : 87192211,
"delete" : 1,
"getmore" : 10010301,
"command" : 24489356
},
"opcountersRepl" : {
"insert" : 0,
"query" : 0,
"update" : 0,
"delete" : 0,
"getmore" : 0,
"command" : 0
},
"recordStats" : {
"accessesNotInMemory" : 1258276,
"pageFaultExceptionsThrown" : 5,
"admin" : {
"accessesNotInMemory" : 0,
"pageFaultExceptionsThrown" : 0
},
"analytics" : {
"accessesNotInMemory" : 22,
"pageFaultExceptionsThrown" : 0
},
"Author" : {
"accessesNotInMemory" : 0,
"pageFaultExceptionsThrown" : 0
},
"History" : {
"accessesNotInMemory" : 11482,
"pageFaultExceptionsThrown" : 0
},
"MonthDB" : {
"accessesNotInMemory" : 94981,
"pageFaultExceptionsThrown" : 0
},
"ProcessDB" : {
"accessesNotInMemory" : 223495,
"pageFaultExceptionsThrown" : 1
},
"Week" : {
"accessesNotInMemory" : 56823,
"pageFaultExceptionsThrown" : 4
},
"local" : {
"accessesNotInMemory" : 144146,
"pageFaultExceptionsThrown" : 0
},
"test" : {
"accessesNotInMemory" : 0,
"pageFaultExceptionsThrown" : 0
}
},
"repl" : {
"setName" : "firstset",
"setVersion" : 3,
"ismaster" : true,
"secondary" : false,
"hosts" : [
"192.168.33.209:27017",
"192.168.33.210:27017"
],
"arbiters" : [
"192.168.33.211:27017"
],
"primary" : "192.168.33.209:27017",
"me" : "192.168.33.209:27017"
},
"workingSet" : {
"note" : "thisIsAnEstimate",
"pagesInMemory" : 141578,
"computationTimeMicros" : 61338,
"overSeconds" : 1265
},
"writeBacksQueued" : false,
"mem" : {
"bits" : 64,
"resident" : 17115,
"virtual" : 161640,
"supported" : true,
"mapped" : 79885,
"mappedWithJournal" : 159770
},
"metrics" : {
"cursor" : {
"timedOut" : NumberLong(137),
"open" : {
"noTimeout" : NumberLong(8),
"pinned" : NumberLong(0),
"total" : NumberLong(1)
}
},
"document" : {
"deleted" : NumberLong(30605),
"inserted" : NumberLong(114008924),
"returned" : NumberLong(258188745),
"updated" : NumberLong(87192211)
},
"getLastError" : {
"wtime" : {
"num" : 0,
"totalMillis" : 0
},
"wtimeouts" : NumberLong(0)
},
"operation" : {
"fastmod" : NumberLong(32233943),
"idhack" : NumberLong(0),
"scanAndOrder" : NumberLong(4)
},
"queryExecutor" : {
"scanned" : NumberLong("464652125632"),
"scannedObjects" : NumberLong(209424414)
},
"record" : {
"moves" : NumberLong(1207213)
},
"repl" : {
"apply" : {
"batches" : {
"num" : 0,
"totalMillis" : 0
},
"ops" : NumberLong(0)
},
"buffer" : {
"count" : NumberLong(0),
"maxSizeBytes" : 268435456,
"sizeBytes" : NumberLong(0)
},
"network" : {
"bytes" : NumberLong(0),
"getmores" : {
"num" : 0,
"totalMillis" : 0
},
"ops" : NumberLong(0),
"readersCreated" : NumberLong(22)
},
"preload" : {
"docs" : {
"num" : 0,
"totalMillis" : 0
},
"indexes" : {
"num" : 0,
"totalMillis" : 0
}
}
},
"storage" : {
"freelist" : {
"search" : {
"bucketExhausted" : NumberLong(0),
"requests" : NumberLong(168481860),
"scanned" : NumberLong(335886028)
}
}
},
"ttl" : {
"deletedDocuments" : NumberLong(0),
"passes" : NumberLong(55658)
}
},
"ok" : 1
}
Postgres Join that allows for an empty table
I have the following schema, and I'm having a lot of trouble joining these tables properly. I'm trying to get all folders that don't have a file updated in the last 10 mins in them. But I want to include any folders that don't have any files.
create table folders {
id int
table_name string
};
create table files {
id int,
folder_id int,
file_name string,
last_updated timestamp with time zone
};
My naive attempt so far is something like the following (this is a re-creation from the CLI):
Select t.folder_name from folders t join files f on (f.folder_id = t.id) where last_updated < 15m group by t.id
I can get either all folders to show up OR the latest file for each, but not both.
Any help would be greatly appreciated
Identification of other entities.
Given the following scenario, what three other entities (and their attributes) would be of importance to this model?
SCENARIO:
The transport authority in Jamaica has recently added train transportation to the database of transportation through the assistance of overseas investors. They require a n updated database to store all the information on their trains, buses and personnel. They also want to keep track of delays and the number of travelers on a daily basis. Each train has an id number, type, production year and capacity. Employee information such is staffId, employment date, position and salary needs to be stored. Each bus has an id, capacity, type, number of kilometers on a full tank should be recorded. Bus drivers has id, name, dob, salary, hire_date. A bus driver does not drive a specific bus but may drive any bus. Each bus/ train makes a stop on each route. The sequence of stops on each bus/train route is needed. Each route has a unique route number and name.
For each route and departure time, on each day, record which physical train/bus was used (This is always a single bus or train - several trains can be coupled together). Timetable information is important as one need to know the planned arrival and departure times for each route and every stop. Timetable statistics which is the actual arrival and departure times for every bus/train, on every stop on every day is also required. Periodically surveys are being made that record the destinations of all travelers in a given bus/train at a given time (between two stops). Manning of the bus/train is important, therefore it should be taken into account who has worked on a particular vehicle at a given time, and this may change at any time during a route.
Information also needs to be stored about the supervisor in charge of each route. A route may have only one supervisor and a supervisor may be in charge of many routes. Bus/train drivers all have a supervisor to report to who may or may not be the supervisor of the route that they are manning.
Hi this is my requirement can you help me?
Student sid name address phno 1001 abc nellore 1342425 1002 xyz allur 98876545
mark sid m p c 1001 45 34 34 1002 62 13 78
fee sid fee 1001 3000 1002 5000
search for sid:1001 it will showing like that name address phno m p c fee abc nellore 134235 45 34 34 300
Schemas with the same name in a Redshift cluster?
Is it harmful in some way to have multiple schemas with the same name in a database cluster, specifically Redshift?
I want to create multiple databases, and then create a schema "tables" or "data" or something for each database. Is there any potential problem with that?
How to execute cursor in PostgreSQL?
This is my program for cursor, but I am not getting how to execute the cursor on a database.
emp=# CREATE or replace function cursor_demo() returns integer as $$
declare
emp_rec employee%rowtype;
emp cursor for select * from employee;
comm numeric(6,2);
begin
loop
fetch emp into emp_rec;
if emp_rec.deptno = 5 then
comm:=emp_rec.salary*0.2;
else if emp_rec.deptno=8 then
comm:= emp_rec.salary*0.5;
else if emp_rec.deptno = 10 then
comm:=emp_rec.salary*0.3;
end if;
end if;
end if;
raise notice 'emp_rec.ename||emp_rec.deptno||emp_rec.salary||comm.';
exit when not found;
end loop;
close emp;
end;
$$ language'plpgsql';
writing pgsql output to csv file
PG_CMD="psql -d portal -U portal -c "
PG_CMD_TP="psql -d portal -U portal -t -P format=unaligned -c "
abc()
{
$PG_CMD "
DO \$$
DECLARE
srowdata record;
customer_list varchar[];
customer_schema varchar(100);
portal_row a.portal%ROWTYPE;
var1 varchar(100);
temp varchar(100);
BEGIN
customer_list=ARRAY(select cname from customer);
FOREACH customer_schema IN ARRAY customer_list LOOP
EXECUTE format('select %s.portal.*,%s.p_fb_config.*,%s.p_gplus_config.*,%s.p_linkd_config.*,%s.p_localum_config.*,
%s.p_sms_config.*,%s.p_twt_config.*,%s.p_webform_config.*,%s.p_wechat_config.*,%s.p_clickthrough_config.*,%s.splash.*
from %s.portal left outer join %s.p_fb_config on %s.portal.pid = %s.p_fb_config.pid left outer join %s.p_gplus_config
on %s.portal.pid = %s.p_gplus_config.pid left outer join %s.p_linkd_config on %s.portal.pid = %s.p_linkd_config.pid left
outer join %s.p_localum_config on %s.portal.pid = %s.p_localum_config.pid left outer join %s.p_sms_config on
%s.portal.pid = %s.p_sms_config.pid left outer join %s.p_twt_config on %s.portal.pid = %s.p_twt_config.pid left outer join
%s.p_webform_config on %s.portal.pid = %s.p_webform_config.pid left outer join %s.p_wechat_config on
%s.portal.pid = %s.p_wechat_config.pid left outer join %s.p_clickthrough_config on
%s.portal.pid = %s.p_clickthrough_config.pid left outer join %s.splash on %s.portal.pid=%s.splash.pid;', customer_schema,
customer_schema, customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,
customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,
customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,
customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,
customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,
customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema) INTO srowdata;
raise notice ' %: %', customer_schema,srowdata;
END LOOP;
END;
\$$";
}
abc
I have written anonymous block in pgSQL.Now instead of using raise notice I want to write this output in 'csv' format.I tried 'COPY' but it accepts argument as a query.Please help.
Cannot identify syntax errors
Newbie here. I'm trying to execute this line in sql server:
CREATE TABLE dummy_driver
but I'm receiving the error message Incorrect syntax near 'dummy_driver' . I can't seem to locate the error.
Additional code with line:
INSERT INTO Person (first_name, last_name)
Select f_name, l_name FROM driver;
CREATE TABLE dummy_driver
INSERT INTO dummy_driver,
Select * from driver;
Inscription à :
Articles (Atom)