What MySQL storage engine and table schema should I use? I am making a web project and I created necessary and the most important table, but I am not sure that I did it right.

In this site users will post data like text, picture, text's category, etc. I created a table like this:

CREATE TABLE `feed` (
  `f_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `u_id` int(10) unsigned NOT NULL,
  `f_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `f_loc` varchar(50) NOT NULL,
  `f_lang` varchar(2) NOT NULL,
  `f_cat` smallint(2) unsigned NOT NULL,
  `f_text` text NOT NULL,
  `f_img_ids` text NOT NULL,
  `f_img_flag` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `f_type` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `f_status` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`f_id`),
  KEY `u_id` (`u_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Is this table schema good or does it need to be improved?

I also want to record every feed's statistics and add some columns like f_views, f_likes, f_favorites, etc. These columns will be updated frequently as users interact with the site.

What are your suggestions?

If you're updating frequently, you'll want innodb, which supports row-level locking.  Your stats can go in a different table, which will be more efficient since it's not as wide as your 'feed' table.

Your data types look pretty good (unsigned, small/tiny).

I'm not a fan of your column names.  Why not 'feed_id', etc.?  Will you have no other "noun" in the system that could be abbreviated as "f"?

If f_cat is a foreign key, it should be cat_id.

There's basically no difference between a VARCHAR(50) and a VARCHAR(255), so you might think about giving yourself more room if you might need it.

Since f_lang is NOT NULL and only two characters wide, it's a small waste to make it a VARCHAR.  I'd prefer to see it as a CHAR(2), which saves the lookup.  Or normalize it and make it a foreign key over to a language table.

You've only indexes f_id and u_id.  Is that really all you'll be coming in with?  If not, think of composite indexes that match your where clause.

Hope that helps.


securely exposing sql server 2008 for windows authentication My company wants to have approximately 100 of their sales people (distributed around the country) to be able to run stored procedures from excel and return the data onto the spreadsheet.

We have sql server 2008. i need to figure out a safe way to do this.

i will create a form in excel where the user can push a command button to refresh the data based on the parameters that they choose.

how can i ensure that the connection from excel to the sql server is secure?
how do i run a stored procedure from excel?

i found this to be very good information: http://office.microsoft.com/en-us/excel-help/connect-to-import-sql-server-data-HA010217956.aspx

  Windows Authentication    Select this option to use the Windows user
  name and password of the current user. This is the most secure method,
  but it can affect performance when many users are connected to the
  server.

however, i would like your input on this.

yes, the sales reps do have windows logins, but can i use this solution if they will actually be entering specifying the data criteria, then sending the criteria over into the stored procedure and then getting the data from the server?

The words "securely", "exposing", "outside" and "database" do not belong together.

Under no circumstance should you ever expose a SQL server to the internet.  It's a very bad idea.

So your question really should be: How do we give access to our SQL server to remote locations?

The answer: VPN.  The remote users should establish a VPN connection to your network.  From there you can give them the appropriate access to the database.


Why are correlated subqueries sometimes faster than joins in Postgres? This relates to the dataset described in Postgres is performing sequential scan instead of index scan

I've started working on adapting the import logic to work with a more normalised schema – no surprises here it's faster and more compact – but I've hit a roadblock updating the existing data: adding and updating with relevant foreign keys is taking an age.

UPDATE pages
SET id_site = id
FROM sites
WHERE sites.url = pages."urlShort"
    AND "labelDate" = '2015-01-15'

NB pages."urlShort" and sites.url are text fields, both are indexed but currently have no explicit relationship.

There are around 500,000 rows for each date value and updates like this are taking around 2.5 hours. :-(

I looked at what the underlying query might look at:

select *
from pages
join sites on
sites.url = pages."urlShort"
where "labelDate" = '2015-01-01'

This takes around 6 minutes to run has query plan like this:

Hash Join  (cost=80226.81..934763.02 rows=493018 width=365)
  Hash Cond: ((pages."urlShort")::text = sites.url)
  ->  Bitmap Heap Scan on pages  (cost=13549.32..803595.26 rows=493018 width=315)
        Recheck Cond: ("labelDate" = '2015-01-01'::date)
        ->  Bitmap Index Scan on "pages_labelDate_idx  (cost=0.00..13426.07 rows=493018 width=0)
              Index Cond: ("labelDate" = '2015-01-01'::date)
  ->  Hash  (cost=30907.66..30907.66 rows=1606466 width=50)
        ->  Seq Scan on sites  (cost=0.00..30907.66 rows=1606466 width=50)

There are two things I'd like to know:

Can I adjust the update to run faster based on the above?
What parts of the query plan are telltales for running slow? Or do you always have to run EXPLAIN ANALYZE to find it out?

Here's the output from running EXPLAIN ANALYZE during the update:

Update on pages  (cost=65108.49..1545071.72 rows=4037902 width=331) (actual time=48181951.584..48181951.584 rows=0 loops=1)
  ->  Hash Join  (cost=65108.49..1545071.72 rows=4037902 width=331) (actual time=4075.973..1200902.835 rows=1909499 loops=1)
        Hash Cond: ((pages."urlShort")::text = sites.url)
        ->  Seq Scan on pages  (cost=0.00..1056057.95 rows=4037902 width=317) (actual time=0.025..456909.895 rows=2053904 loops=1)
              Filter: ((id_site IS NULL) AND ("labelDate" < '2015-09-01'::date))
              Rows Removed by Filter: 12105346
        ->  Hash  (cost=30907.66..30907.66 rows=1606466 width=41) (actual time=4061.106..4061.106 rows=1606489 loops=1)
              Buckets: 2097152  Batches: 2  Memory Usage: 74179kB
              ->  Seq Scan on sites  (cost=0.00..30907.66 rows=1606466 width=41) (actual time=0.024..869.068 rows=1606489 loops=1)
Planning time: 3.767 ms
Execution time: 48181966.394 ms

I've tried different memory settings (from 128MB to 1024MB shared_buffers) but they don't seem to make much difference.

To start, a correlated subquery can't return more than one row. Take for instance.

SELECT t.x
FROM ( VALUES (1),(2) )
  AS t(x)
LEFT OUTER JOIN (
  VALUES (1),(1)
) AS y(x)
USING (x);
 x
---
 1
 1
 2
(3 rows)

Versus the same query written as a correlated subquery

SELECT t.x, (
  SELECT *
  FROM ( VALUES (1),(1) )
    AS y(x)
  WHERE y.x = t.x
)
FROM ( VALUES (1),(2) )
  AS t(x);
ERROR:  more than one row returned by a subquery used as an expression

That tells the planner something useful, and it can optimize that. Adjusting for this, by reducing the inner table to one row -- you'll see the plans:

EXPLAIN ANALYZE SELECT t.x
FROM ( VALUES (1),(2) )
  AS t(x)
LEFT OUTER JOIN (
  VALUES (1),(1)
) AS y(x)
USING (x);

                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=0.05..0.10 rows=2 width=4) (actual time=0.019..0.021 rows=3 loops=1)
   Hash Cond: ("*VALUES*".column1 = "*VALUES*_1".column1)
   ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=4) (actual time=0.002..0.003 rows=2 loops=1)
   ->  Hash  (cost=0.03..0.03 rows=2 width=4) (actual time=0.005..0.005 rows=2 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Values Scan on "*VALUES*_1"  (cost=0.00..0.03 rows=2 width=4) (actual time=0.001..0.003 rows=2 loops=1)
 Planning time: 0.107 ms
 Execution time: 0.046 ms
(8 rows)

Correlated subquery method.

EXPLAIN ANALYZE SELECT t.x, (
  SELECT *
  FROM ( VALUES (1) )
    AS y(x)
  WHERE y.x = t.x
)
FROM ( VALUES (1),(2) )
  AS t(x);

                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Values Scan on "*VALUES*"  (cost=0.00..0.05 rows=2 width=4) (actual time=0.014..0.017 rows=2 loops=1)
   SubPlan 1
     ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.003..0.004 rows=0 loops=2)
           One-Time Filter: (1 = "*VALUES*".column1)
 Planning time: 0.097 ms
 Execution time: 0.043 ms
(6 rows)

This follows vaguely your above examples. So when is a correlated subquery better than a join? Not sure, but the correlated subquery gets to skip the hash join.

You have in your plan this

Filter: ((id_site IS NULL) AND ("labelDate" < '2015-09-01'::date))

That's not even the same SQL you've got above. That makes me call into question the whole thing.

I would do this though

CREATE INDEX ON pages ( labelDate ) WHERE id_site IS NULL;

If you don't have an index on id_site IS NULL or id_site then it seems likely you're just forcing a seq scan there.


Equivalents to `ora_rowscn` in other databases? Do other databases other than Oracle have ora_rowscn (http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns007.htm) equivalents, or do you have to implement them yourself in your fields, code and stored procedures?

ora_rowscn is a pseudo-column that is attached to all tables in Oracle created using some specific settings, which is used when doing an optimistic offline-lock.

As I understand the question, the scenario is one where users read values from the database outside a transaction, and later attempt updates. The requirement is that the database detect when a lost update might occur.
SQL Server does not provide a direct equivalent for the Oracle pseudo-column ora_rowscn because it does not exclusively use row versioning to implement transaction isolation levels (so row versioning information is not always present). Nevertheless, there are a number of ways to implement optimistic concurrency control with SQL Server.
Using Row Version
One option is to add a rowversion column to the table. Often, this column will be given the same name in all tables (e.g. RV). The general strategy is to read the rowversion value when reading data, and to check that the value is unchanged when updating the row later on:
CREATE TABLE dbo.Example
(
    SomeKey     integer PRIMARY KEY,
    SomeValue   integer NOT NULL,
    OtherValue  integer NOT NULL,
    RV          rowversion NOT NULL
);

INSERT dbo.Example
    (SomeKey, SomeValue, OtherValue)
VALUES
    (1, 100, 1000);

-- Read data
SELECT SomeValue, RV
FROM dbo.Example AS E
WHERE SomeKey = 1;

-- Time passes

-- Attempt update
UPDATE dbo.Example
SET SomeValue = 123
WHERE SomeKey = 1
AND RV = 0x0000000000273711;

If the row has been modified, the UPDATE statement makes no changes and no error or warning is returned. It is up to the developer to check the number of rows affected by the statement, and take appropriate action (such as re-reading the source row, and alerting the user to the conflict).
Using an optimistic cursor
Server-side cursors are often used in the scenario we are discussing. SQL Server contains native support for OPTIMISTIC server-side cursors via a number of interfaces (e.g. API, ODBC, Native Client). If a rowversion column is present, SQL Server uses that to implement optimistic concurrency, without the developer needing to retrieve and check the rowversion value explicitly. Using a Transact-SQL cursor to demonstrate:
DECLARE UIcursor
    CURSOR GLOBAL
    SCROLL DYNAMIC
    OPTIMISTIC
FOR
    SELECT SomeValue
    FROM dbo.Example
    WHERE SomeKey = 1
    FOR UPDATE OF SomeValue;

OPEN UIcursor;

-- Read data
FETCH FIRST FROM UIcursor;

-- Time passes

-- Attempt update (note no rowversion reference needed)
UPDATE dbo.Example
SET SomeValue = 321
WHERE CURRENT OF UIcursor;

If the row has been changed since data was read, a warning and an error is returned:
Optimistic concurrency check failed. The row was modified outside of this cursor.
Msg 16947, Level 16, State 1, Line 1
No rows were updated or deleted.

If the table does not contain a rowversion, SQL Server transparently uses a reliable checksum to detect row changes. The same warning and error is generated if the checksum comparison fails. Using rowversion is more efficient than using the internal checksums, but both are equally reliable.
Note that a real implementation would be quite unlikely to use a global Transact-SQL cursor as shown above, but the concepts are the same when using whatever server-side cursor support is available within your development framework.


Why does copied MySQL database have different data than source? I am trying to migrate database A to database B. I have tried various methods for this, including...

Using Navicat, do Tools > Data Transfer, copying structure and data from A to B
Using Navicat, delete all tables from B, then drag and drop all tables (structure and data) from A to B
Using Sequel Pro, export A to SQL file, then import SQL file into B

Option 1 always fails, and I'm not sure why. The error message just says "failed".

Options 2 and 3 seem to work fine, but then some tables have slightly different data, which makes absolutely no sense to me. For example, here is a row of data from database A...

node    book    0   438 2255    und 0   foo NULL    full_html

...and here is the same row in database B...

node    book    0   438 2255    und 0   bar NULL    full_html

The "foo" and "bar" are HTML content for web page #438 at revision #2255. I have no idea how the HTML content for the same exact revision can be different, when database B should be a straight copy of A.

Mysteriously, "foo" represents the most recent version of page #438, and "bar" actually reflects the prior version of page #438. How is this even possible?

Also, if any of you DBAs happen to be familiar with Drupal, note that this is for a Drupal site. Incidentally, when I try to restore a backup on this site (a ZIP or SQL file made with the Backup and Migrate module), I get an unhelpful "site encountered an unexpected error" message and nothing else in the error log. The same ZIP and SQL files restore just fine in other test environments, so something about this one site just seems wonky.

Honestly, I expected you to say MyISAM since I've never seen InnoDB generate a backup that seems inconsistent with what you think is in the table.

As a DBA, I don't trust my data to anything that I can't watch while it's working and peer into its intermediate stages, so I rarely use GUI tools.  My approach to this issue would be to use mysqldump to extract the data into a file and then review that file manually.  Satisfied with the sanity of it, I would load it onto the target server with the mysql command line client and see what the results are.

The specific option I'd suggest with mysqldump in this case would be --skip-extended-insert, which generates one insert statement for each row row in each table.  This is in contrast to the normal format, which combines groups of rows into a smaller number of insert statements as an optimization for faster restoring... a file created with this option will restore somewhat more slowly than one created without it, but the tradeoff is that the files are substantially easier to parse with your eyeballs and see what the data looks like during this intermediate stage of the process.

Since my bet on MyISAM didn't pan out so well, I'm really unsure whether to put money on the dump file looking right and some kind of strangeness on the target server to scramble it up on the way in... or for the dump file to come out looking wrong, though I really will be surprised if the dump file comes out looking wrong... but then again, if the dump file looks right, I'd be pretty surprised if it doesn't restore correctly.

After reviewing the file, if the content looks right (i.e., find the line in the file that represents the 438/2255 row you mentioned above, and see which version it represents... and make sure there's only one such line in the dump file) load it onto the target server and see what the results look like.  If the contents of the dump file don't look right, then of course, restoring it would be unnecessary and we need to look at the origin server for something strange.


Inject aggregation function inside a procedure Is it possible to inject the name of a function (AVG, MAX...) as a procedure parameter?
CREATE PROCEDURE test(func TEXT)
LANGUAGE PLPGSQL
AS $$
BEGIN
DROP TABLE IF EXISTS foo;
CREATE TEMPORARY TABLE foo AS SELECT
func(bar.column_a) as func_column_a
FROM bar;
END
$$;

You have to use dymnamc sql for that purpose
CREATE PROCEDURE test(func TEXT)
LANGUAGE PLPGSQL
AS $$
BEGIN
DROP TABLE IF EXISTS foo;
EXECUTE 'CREATE TEMPORARY TABLE foo AS SELECT' || func ||'(bar.column_a) as func_column_a
FROM bar;';
END
$$;


Is hosting the back end in the cloud supposed to significantly slow performance? I moved my back-end from a local .accdb source to Microsoft's online Access App. Ever since then, forms have been taking more than 20 times longer to load. Is this normal? Or is there something wrong with my connection?

I'm using Access 2013.

Well, if the backend is sitting on an external server, your network speed now plays a major part in the connection. Accessing local files off harddisk is fast, but accessing it of an internet connection is slower.

Try testing your app using a faster internet connection and see if there are differences. If there are minimal differences, then it could be the server hosting your app that is slowing it down. But my main bet will be the internet.


Updating Multiple Rows with Data from Column A into Column B? I have about 60,000 rows that I'm needing to update the information from column_a to column_b.  For example, 60,000 customers need their arrival_time to match their departure_time.  I understand that I can do this with one row and it work with a nested select statement in an update statement.  When trying to update multiple rows though I believe I'm getting stuck on having to have 60,000 unique key identifiers.

This was what I used to update 1 of the records, I'm using Oracle SQL:

UPDATE patient
SET discharge_dt = (SELECT admit_dt
                    FROM patient
                    WHERE pat_seq = 'XXXXXX')
WHERE facility_id = 'X'
    AND pat_seq = 'XXXXXX'

Apologize for the confusing description, not really a database administrator myself.

Any help on this would be greatly appreciated.

If you just want the discharge_dt to be overwritten with the admit_dt from the same row, it sounds like you just need

UPDATE patient
   SET discharge_dt = admit_dt
 WHERE facility_id = 'X'
   AND pat_seq IN (<<query that returns the 60,000 keys>>)


Mysql import csv not complete by Mysql bench I upload the csv file which has more than 5 thousand rows  table data for option import wizard by Mysql Workbench. As the result the output table only 335 rows, have no idea how to overcome the limitation.

It all comes down to the contents of your CSV-file - and that in turn is determined by the used program that created it in the first place ...

Check if all lines have the desired number of values per line, in combination with:

Check if escape-characters (if any) are there .. and you give them in mysql-workbench as well ...

special case for Microsoft Excel - it used to create ONE escape sequence for first occurance, 2 Escape sequences for 2nd occurance and so forth ... I had to use a php-script to import them, where I had to hand-twiddle until mysql was satisfied with the input-format

compare those successful lines with other lines in the csv - What is the difference between them?


What learning resources are there for part-time DBAs? Luckily, this isn't a current issue for me, as in my current workplace we're pretty well supplied with DBA knowledge in the team.  However, in small development teams, it's not uncommon in my experience for one of the team to be nominated as the de-facto DBA.  For a small team there's rarely enough work for a full-time DBA (and/or there are DBAs, but the production DBAs don't want to get involved with supporting the dev/test environments), so a tester or programmer will be expected to pick up that role, and I've seen people getting suddenly landed with "hey, Joe's leaving, so you're now our new DBA.  No, we have no training budget, you'll have to teach yourself. Have fun!"

What resources can you recommend for someone who finds themselves suddenly in the position of having to get up to speed with a development DBA role?  What basic tasks should they aim to tackle first, just to keep things ticking over?

If your going to be "The DBA" (part or full time) you should learn as much as you can before the former DBA leaves. I think people in this situation should talk to anyone who knows about the database (the data flow, the design choices, common task, etc.), it is very hard to try to learn the db even if documentation exists.

Also, the person should have something like a dba profile, If you don't like it you would be doing something that brings no excitement and you will have no satisfaction from your work.


why should I join 2 tables with 1 table apart I could not solve this problem on hackerrank , and had to look up the solution. I was joining the wrong tables.
There are 4 tables:

hackers
challenges
submissions made by hackers and their scores
difficulty table with levels.

the task description:

Write a query to print the respective hacker_id and name of hackers
who achieved full scores for more than one challenge.

Submissions table fields: submission_id, hacker_id, challenge_id, score
challenge table fields:   challenge_id, hacker_id, difficulty_level
The way i joined: hackers + challenges, challenges + difficulty , challenges + submissions.
select
hackers.hacker_id, name

from submissions inner join challenges on submissions.challenge_id = challenges.challenge_id
inner join difficulty on difficulty.difficulty_level = challenges.difficulty_level
inner join hackers on

challenges.hacker_id = hackers.hacker_id -- here is wrong part!

where difficulty.score = submissions.score and difficulty.difficulty_level = challenges.difficulty_level
group by hackers.hacker_id, name
having count( challenges.challenge_id)> 1
order by count( challenges.challenge_id) desc, hackers.hacker_id

However, the right way was almost same - except that i should have joined
submissions + hackers by hacker_id
vs
not submissions + challenges by challenge_id.
The correct way:
select
hackers.hacker_id,name

from submissions inner join challenges on submissions.challenge_id = challenges.challenge_id
inner join difficulty on difficulty.difficulty_level = challenges.difficulty_level
inner join hackers on submissions.hacker_id = hackers.hacker_id
where difficulty.score = submissions.score and difficulty.difficulty_level = challenges.difficulty_level
group by hackers.hacker_id, name
having count( challenges.challenge_id)> 1
order by count( challenges.challenge_id) desc, hackers.hacker_id

What's the logic behind joining submissions + challenges by challenge_id vs
submissions + hackers by hacker_id?
why does it produce different result? A hacker makes submissions , so it should not matter whether i join challenge+submission + hackers or
submission + hacker + challenge...

It's a bit of a guess because you haven't provided the link to the challenge (in markdown you have to make links like [this problem](http://whatever.com) but clicking your link just takes to http://this%20problem, so I guess the markdown is broken
You've said you have tables:
Hacker - I guess this is like a user table
Challenge - I guess this is a problem to solve
Submission - I guess it's an answer a hacker has made to a problem

So we can clearly have "one hacker may answer many challenges" and "one challenge has many hackers answering it" ie a many many relationship so we need a table in the middle to split it down and track which hacker answered which challenge, and this is the Submissions table doing this breakdown
This interesting part here, and possibly put in deliberately to trip people up, is that Challenge also has a hacker Id column. For some reason hacker rank's tables track a hacker-challenge relationship that is nothing to do with a submission ie the hacker ID in the challenge table is something where there can be only one hacker for that challenge, such as the hacker that created it, or the hacker that first got perfect score on it, or the hacker who is responsible for marking the answers on it, or the hacker that most recently took it..
Whatever the reason, you said you joined on that hacker ID which in my mind is the wrong thing to do because it bears no relation to the question of multiple hackers who have submitted: it could be that the hacker in the challenge.hacker_id has never even made a submission on the challenge, they simply wrote it and have never answered it. It therefore cannot give you the answers the challenge seeks. It's probably meant to catch you out and it's one reason why when I'm designing a database schema I aim to put the reason for a column into its name in cases like this; ie in the Challenge table I might have called it author_hackerid or moderator_hackerid to describe not only where it links to (hacker table on hacker ID) but why it links there (author, moderator, firstwinner, recenttaker) especially if there are multiple people.. (though in such a case it may make more sense to have another table, like submission) that splits hacker and challenge, and gives a reason for why that hacker is related to that challenge)
Remember, that just because two columns are named the same it doesn't mean they are alternative join options. Even columns that have participated in a join before are not alternative, if it's a left join. If it's an inner join, fair game:
a INNER JOIN b ON a.id = b.id INNER JOIN c ON b.id = c.id
a INNER JOIN b ON a.id = b.id INNER JOIN c ON a.id = c.id
                                              ^

but these can give different results:
a LEFT JOIN b ON a.id = b.id LEFT JOIN c ON b.id = c.id
a LEFT JOIN b ON a.id = b.id LEFT JOIN c ON a.id = c.id

That small but critical difference in the second query of both tables left joining to a might be huge


Count rows for each latest status of tickets ticket_number |          datetime          | status
---------------+----------------------------+---------
             1 | 2020-08-03 03:52:58.048196 | replied
             1 | 2020-08-02 19:55:49.121455 | new
             2 | 2020-07-30 03:52:58.048196 | pending
             2 | 2020-07-28 20:15:41.213842 | replied
             2 | 2020-07-26 03:52:58.482911 | new
             3 | 2020-06-17 19:55:49.394628 | closed
             3 | 2020-06-14 03:52:58.513141 | replied
             3 | 2020-06-11 19:55:49.242859 | new
             4 | 2020-05-14 07:13:50.527481 | new
             5 | 2020-05-13 11:24:38.558921 | new

The above table ordered by datetime. After ordering I need to group by ticket_number so that each ticket appears only once, and pick the status for the latest datetime per ticket. Then I need to count rows for each status.
Ticket 1 would have a status of replied - replied_count = 1,
Ticket 2 would have a status of pending - pending_count = 1,
Ticket 3 would have a status of closed - closed_count = 1,
Ticket 4 would have a status of new - new_count = 1,
Ticket 5 would have a status of mew - new_count = 2
Expected Result:
replied_count  |  pending_count  |  closed_count  |  new_count
---------------+-----------------+----------------+------------
             1 |               1 |              1 |          2

Use
WITH cte AS ( SELECT DISTINCT
                     ticket_number,
                     FIRST_VALUE(status) OVER (PARTITION BY ticket_number
                                               ORDER BY datetime DESC) last_status
              FROM test )
SELECT last_status, COUNT(last_status)
FROM cte
GROUP BY last_status

fiddle
for to obtain needed statistic in vertical form. If you need pivotted output strictly then use CROSSTAB() or similar, or emulate it using 4 CASEs.


SQL Server: Order of Rows We all know that a simple statement such as:

SELECT * FROM stuff;

should not produce an ordered result. However, when I try to demonstrate this, it always comes out in primary key order.

Moreover a statement such as:

SELECT thing,whatever FROM stuff
GROUP BY thing,whatever;

always appears to sort thing by the last field in the GROUP BY clause, which is not at all helpful.

The question is, under what circumstances does SQL SERVER order the result even if unasked, and what can I do to stop this?

I am trying to demonstrate to my students that order is indeterminate unless specified, but this is not helping my case.

I admit that I am working with a small set of sample data.

Thanks

This article by Conor Cunningham (Software Architect, SQL Server Engine at Microsoft) should answer your questions:

No Seatbelt – Expecting Order without ORDER BY

Some extracts below:

The hard part here is that there is no reasonable way for any external user to know when a plan will change .  The space of all plans is huge and hurts your head to ponder.  SQL Server's optimizer will change plans, even for simple queries, if enough of the parameters change.  You may get lucky and not have a plan change, or you can just not think about this problem and add an ORDER BY.
[...]
There are lots of situations where plans can change in the Optimizer - for more complex queries, there can be thousands of plan choices or more, and each of them has a case when it would likely be picked.  For each of those plans, the sort for that plan can be different if you don't specify it.
So, my advice for the day:
If you need order in your query results, put in an ORDER BY.  It's that simple.  Anything else is like riding in a car without a seatbelt.


sql agent job notification email without configuring database mail Is there any way to send sql agent job notification email without configuring Database Mail?

Currently I am using Database Mail but due to security concern, i need to disable Database Mail feature.

Thanks.

You can easily invoke a Powershell script:
(Change the FromEmailAddress, ToEmailAddress and SmtpClient information)

$computerName = gc env:computername
$emailFrom = $computerName + “<EmailFromAddress>.com”
$emailTo = "<EmailToAddress>.com"
$subject = "Testing email"
$emailBody = "This is an example of sending email via Powershell"
$message = New-Object Net.Mail.MailMessage($emailFrom, $emailTo, $subject, $emailbody)
$message.Priority = "HIGH"
$smtpServer = "<YourSmtpServer>.com"
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($message)


Performance of query with complex where clause on 10M rows We have a query that destroys our production server when run.

It is part of a reporting functionality and the bad part looks as follows:

SELECT DISTINCT
    mt.ID           AS ID
FROM
    [dbo].[MyTable]    mt
WITH (NOLOCK)
WHERE
    (@aVariable           IS NULL
      OR (CONVERT(VARCHAR(22), mt.Date1, 112) >= CONVERT(VARCHAR(22), @date1, 112))
    AND (@status            IS NULL
    OR @status <> 2
    OR  (   @status = 2
        AND (  SELECT COUNT(*)
               FROM
               MyTable mt2
               WITH (NOLOCK)
               WHERE
                  mt2.CaseID = mt.CaseID
                  AND mt2.Date1 > mt.Date1
             ) = 0
        )
    )
    AND (@aSecondVariable IS NULL
           OR (CONVERT(VARCHAR(22), mt.Date1, 112) <= CONVERT(VARCHAR(22), @date1, 112)))
    AND (@aThirdVariable  IS NULL
           OR (CONVERT(VARCHAR(22), mt.Date2, 112) >= CONVERT(VARCHAR(22), @date2, 112)))
    AND (@aFourtVariable  IS NULL
          OR (CONVERT(VARCHAR(22), mt.Date2, 112) <= CONVERT(VARCHAR(22), @date2, 112)))

Furthermore, the table and indexes are created as follows:

CREATE INDEX [MyIndex] ON [dbo].[MyTable]
    ([AColumn], [AColumn2], [Date1], [Date2])
WITH (FILLFACTOR = 90)

MyTable has about 80 columns and a single column Primary Key: (ID).

The MyTable mt consists of about 10.000.000 rows. There is an index on the columns which contain aVariable, aSecondVariable, aThirdVariable and aFourthVariable. About half of the values of the date columns are null. In the index they are on place 3 and 4.

When we run the query on one server (without users) it performs really well. When we run it on production (with users) it takes too long and times out.

We are wondering how this can be. The execution plans are the same on both servers. We thought that the result might be cached somewhere or that the memory which is free (2GB RAM) is insufficient.

We are no experts on database performance and hope some real DBAs can provide us with their views. Thanks.

A few suggestions:

Remove the DISTINCT as the ID is the primary key. There is no way you'll get duplicate rows in the result.
Do not convert the datetime columns. This makes your conditions non-sargable and the query will always do a table scan. The variables would need no conversion either if they are declared as dates but that is not a problem for sargability.
Use close-open ended ranges for date and datetimes, as @Aaron suggests in the comments. Please read his blog post: Bad habits to kick : mis-handling date / range queries .
Use NOT EXISTS instead of (SELECT COUNT(*) ...) = 0 to check if there is no rows matching some condition.
Drop the WITH (NOLOCK) hints. Another blog post: Bad habits : Putting NOLOCK everywhere and a question in this site with several valuable points: Is NOLOCK always bad?.
Add appropriate indexes. I'd guess separate indexes on (Date1) and (Date2) or an index on (Date1, Date2) would be ok but that needs testing. An index on (CaseId, Date1) will be useful for the subquery.
(Minor note) always use the schema prefixes. Another blog post by @Aaron Bertrand: Bad habits to kick : avoiding the schema prefix.
Add OPTION (RECOMPILE) (as @Mikael Eriksson suggested). This basically tells the optimizer to not rely on cached plans but spend some time for each query run to recompile the query - i.e. produce a new plan according to the new parameter values. With 7 variables that can change the query substantially, this seems a very good option. Read the article from @Paul White for more detailed explanation of the "Parameter Embedding Optimization", parameter sniffing and also other options and advantages: Parameter Sniffing, Embedding, and the RECOMPILE Options.

The query rewritten:

SELECT
    mt.ID
FROM
    [dbo].[MyTable] AS mt
WHERE
      (@aVariable       IS NULL OR mt.Date1 >= CAST(@date1 AS DATE))
  AND (@aSecondVariable IS NULL OR mt.Date1 < DATEADD(day, 1, CAST(@date1 AS DATE)))
  AND (@aThirdVariable  IS NULL OR mt.Date2 >= CAST(@date2 AS DATE))
  AND (@aFourtVariable  IS NULL OR mt.Date2 < DATEADD(day, 1, CAST(@date2 AS DATE)))
  AND ( @status IS NULL
     OR @status <> 2
     OR ( @status = 2
          AND NOT EXISTS
              ( SELECT *
                FROM dbo.MyTable AS mt2
                WHERE mt2.CaseID = mt.CaseID
                  AND mt2.Date1 > mt.Date1
              )
        )
      )
OPTION (RECOMPILE) ;

The part about @status can be written in a slightly more compact way.

I don't think this will make much difference (if any) in performance and it may look obfuscated but I add for completeness this version as well:

SELECT
    mt.ID
FROM
    [dbo].[MyTable] AS mt
WHERE
      (@aVariable       IS NULL OR mt.Date1 >= CAST(@date1 AS DATE))
  AND (@aSecondVariable IS NULL OR mt.Date1 < DATEADD(day, 1, CAST(@date1 AS DATE)))
  AND (@aThirdVariable  IS NULL OR mt.Date2 >= CAST(@date2 AS DATE))
  AND (@aFourtVariable  IS NULL OR mt.Date2 < DATEADD(day, 1, CAST(@date2 AS DATE)))
  AND NOT EXISTS
      ( SELECT *
        FROM dbo.MyTable AS mt2
        WHERE @status = 2
          AND mt2.CaseID = mt.CaseID
          AND mt2.Date1 > mt.Date1
      )
OPTION (RECOMPILE) ;


Should I use Indexes on mySql table? I have a table in mysql, in which searching, insertion, deletion and updation is done quite frequently and I have read that indexes affection insertion and updation performance. so should I use Indexes for faster searches?

I have a table in mysql, in which searching, insertion, deletion and updation is done quite frequently and I have read that indexes affection insertion and updation performance. so should I use Indexes for faster searches?

Yes, there is an overhead in having indexes when changing data.  As well as updating the base data table, the DBMS also has to update the index structures as well.  That overhead, however, is small.  Remember the all changes are [quickly] written to memory first and only flushed [slowly] to disk some time later.

Indexes exist primarily to improve query performance and can do so by Orders of Magnitude.  Imagine retrieving a single record by its unique ID:

If that field is indexed then the DBMS can quickly skip through the index [blocks], then "hop" across to the data page to get any additional fields.
Without the index the DBMS has to scan each and every record in the table.  If that table has millions of records, that's going to be really, really slow.  Even with a "limit 1" clause, telling the DBMS to stop at the first match, you'll still be reading half the table, on average.

So Indexes are a trade-off.  They can make you [huge] gains on the querying front, while making [small] losses on the update front.  The only person who can definitively tell you whether it's worth your while to index your fields is you, based on your database and your Application.  Test that Application with and without indexes and compare the results.

That said, I strongly suspect you'll come back in favour of using them :-)


DBMS_REDEFINITION don't copy data? Is there an option to use Oracles online table redefinition feature but only copy a subset of the data from the source table?

I mean - the source table has 2M records and I want to only copy those that have ID > something. The problem is that START_REDEF_TABLE seems to sync all 2M records. Is there a way how to specify whics records should be synced over?

If I understood you don't need to do it online.

In this case, how about a CREATE TABLE AS SELECT using a select which is focusing on the subset of data?

Then you just do a quick rename, recreate the indexes and done.


How to add SQL Server 2014 instance? I followed this link to install SQL Server 2014. When I clicked on "New installation or add features to an existing installation" instead of proper box, I have to choose "Browse For Folder". When I choose folder where my installation is, nothing happens. Any idea why is this happening?

You can use the following link to install sql 2014 offline files
MS SQL Server 2014


MySQL - Connection blocked because of many connection errors - How to find root cause? I am developing a web application and am running into the dreaded host xxx blocked because of many connection errors message. There have been numerous questions on SO pertaining to this error and common wisdom is to flush hosts to clear the error and then bump up MySQL's max_connections setting in order to avoid this issue in the future. A summary of the issue from the MySQL manual is as follows:

  The number of interrupted connect requests allowed is determined by
  the value of the max_connect_errors system variable. After
  max_connect_errors failed requests, mysqld assumes that something is
  wrong (for example, that someone is trying to break in), and blocks
  the host from further connections until you execute a mysqladmin
  flush-hosts command or issue a FLUSH HOSTS statement. See Section
  5.1.3, “Server System Variables”.

I'd like to go a step further and determine the root cause of the issue for my application. I am relatively new to MySQL and would like to know what is the best way to trap such errors - does MySQL provide any logs or information as to which connections or queries failed or are the any tracing tools I can use to ascertain this information?

Thank you

JP

The advice you've seen about max_connections is a distraction from the issue you're seeing, because it has nothing to do with connection errors.

You can log the failed connections to the mysql error log by issuing this:

SET GLOBAL log_warnings = 2;

You can also make this a permanent setting in my.cnf so that it persists after a restart.

http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_log-warnings


replica set unable to add a node I am trying to create a replica set. I had create a primary node then I am trying to add a secondary node but it gives an error message

  Quorum check failed because not enough voting nodes responde                                            d; required 2 but only the following 1 voting nodes responded:...

Anyone know what the problems is?

Simple. It means that your node cannot connect that other node. Check firewall settings between those nodes.

If you go to the command line, telnet node.ip.addr.ess 27107 should work.


Preserving create_time when loading a mysqldump file I want to make a copy of a MySQL database but preserve the information_schema.create_time for each of the tables. I haven't been able to find an option to do that with mysqldump.

There's a response for this here: MySQL - mysqldump. preserve update_time and modify_time

As @RolandoMySQLDBA said:

  There is no way to reload the INFORMATION_SCHEMA manually. All the tables are dynamic entities.


Legacy CE vs current I recently updated a SQL server to the current version. I have major performance issues with some queries for example this query takes 8 seconds with legacy CE:
But 5 minutes with CE 150:
https://www.brentozar.com/pastetheplan/?id=Bkj1RbGrP
I updated statistics for the whole DB and ran a full scan update to the tables in this query.
Any idea how can I fix this performance issue?

Microsoft has guidance for your scenario where upon upgrading you experience query performance degradation. You may not be able to follow the exact steps outlined here if you were upgrading from a version without Query Store, but the general principles are still the same. It is expected that some queries will run slower after upgrade as Microsoft makes changes to the cardinality estimator. A first defense against that is to find the queries that are running for too long and to force them to use a previous plan. Methods of doing that:

Forcing a previous good plan with Query Store
Setting compatibility level at the database level or query level
Enabling the legacy CE at the database level or query level

For the root cause of why this particular query is slow, 98% of the query's time is spent on a nested loop join against Object4. It is hard to give specifics for an anonymized plan, but the following broad principles apply:

Use a temp table or some other method to improve the cardinality estimate coming into the nested loop join. The optimizer is likely picking a loop join because the estimate is just 1 row.
Define an index on Object4 which allows for more effective seeking against that table. This can only be a partial fix and won't get the query execution time down to 8 seconds.
If required, force a different join type for the table access on Object4. The nested loop join reads a total of 173,354,476 rows from a table with just 38,775 rows. A hash or merge join would be more efficient.
If required, achieve a different join order.

As a final note, you really can't expect to get good cardinality estimates for query constructs like this:
Function2(Function3(Object3.Column38)) LIKE Function2(Function3(Object2.Column11)) + ?

It may be true that the legacy cardinality estimator gives you good performance just due to luck. To keep the same performance on a newer CE you may need to use the invasive techniques that I described above.


Remove The Blank Spaces I was trying to figure a MYSQL query in PHP Admin to only show rows without any blank spaces. I can write a query to not show blank spaces in one column but in two columns I am having and issue, and three columns as shown below.

Name          Phone                Email
Tom           800-555-1212         ttest@hotmail.com
Mike                               mtest@hotmail.com
John          800-666-1212
              800-777-1212         atest@hotmail.com
Kate          800-888-1212
Sue           800-555-2222         stest@hotmail.com
Phil                               ptest@hotmail.com

To filter out the blank spaces so it will be this way:

Name          Phone            Email
Tom          800-555-1212      ttest@hotmail.com
Sue          800-555-2222      stest@htomail.com

You should check that there isn't any empty column: <> ''

SELECT Name,
       Phone,
       Email
FROM   YourTable
WHERE  Name <> ''
AND    Phone <> ''
AND    EMail <> '';


Oracle Enterprise Manager (OEM) locking SYSMAN account Why does Oracle Enterprise Manager reset my SYSMAN account to LOCKED(TIMED) status?

I've tried rebuilding Enterprise Manager, and re-installation of Enterprise Manager, which always works, until the next reboot, and back to where we started...

Benoit, you actually had a partial answer, everything you said was correct, but after next reboot, the account locked. After several hours of more research, the following issue was discovered, with the appropriate resolution.

Enterprise Manager had the default SYSMAN password stored for its credentials, and was trying to lock in with that password in rapid succession (300 times a minute or so according to the Event Log). Hence, after several rapid tries with the same wrong password, Oracle locks the account.

Setting a new password in SQL*PLUS or SQL Developer doesn't reset the password used to login to Enterprise Manager, to reset the password in enterprise manager, you need to open a command prompt and do the following:

emctl config oms sso -remove
emctl stop oms
emctl start oms

During the above commands, it'll prompt you for the current SYSMAN password, which you can enter, and it should remove the SYSMAN password used by your Enterprise Manager Console, replacing it with the one you just entered.


linked list in SQL and Trees Though SQL is more affiliated with table like operations and not so much with recursive, say we would like to implement the linked (or double-linked) list concept (like we have for instance in C).
Is there any way to do this efficiently, considering that we can have items moving around from any place into any place on a linked list?
Some solution using CLR?
Or is it really something that should never be brought to SQL Server?

Note that this question evolved also into a linked lists VS trees discussion

Though I pinned SQL Server, this is an academic like question, so a solution in any other is also good, even if we just get to the conclusion that this is something that should never be brought to the database.

A linked list is just a very simple directed acyclic graph.  There is no reason why this is in any way difficult, or should be avoided for sql server.
Think about it, tree structures are more complex than linked lists.  Every implementation of a forum on the internet that stores data in a relational database has implemented the basics of a linked list.  On this very page, answers form a linked list.  They can be added and deleted.  They can be moved in position (aka, ranked by votes).
The particular representation to be used depends only on the trade-offs you wish to make between  maintaining the list (insert, update, delete) and retrieval.
--Works great for INS/UPD/DEL, In order retrieval isn't the best.
CREATE TABLE Item (id int identity, next int, prev int)

--makes in order retrieval fast, deletes are a problem, inserts may require re-numbering.
CREATE TABLE Item (id int identity, position int)

--Works great for in-order retrieval, and allow cheap insertion/deletion,
--certain edge cases might be tricky to handle.
CREATE TABLE Item (id int identity, position Decimal(24,12))
--for inserts, use the average of the before and after, for deletes, just delete.

Update:  Question is about trees vs linked lists in SQL server
SQL Server has the HierarchyId data type which is designed for easing the implementation and querying of tree like structures.
CREATE TABLE Item (id int identity, NodeId HierarchyId)


Pb connection from client to postgresql server I've no problem to access to postgresql server from local machine with two methods (localhost or specific ip ).
But when i try to connect from client machine to the postgresql server (in other pc using network lan), i have this error : can't connect to the server.
These steps were verified in computer which include postgresql server:

listen_adresses='*'  ==> OK in postgresql.conf file
host all all 0.0.0.0/0 md5 => Ok in  pg_hba.conf file
_ postgresql rule is inserted in inbound windows firewall including these parameters : protocol type : TCP, local port: specific ports: 5432, action: allow connection, profiles: domain, private and public all checked
I've verified with ping  all it's ok
but when I try to connect from client machine to postgresql server the problem persist.
Thank you in advance for your answers!

I found the solution to my problem.
I've added ODBC to the client machine with IP server + allowing port 5432 and every thing ran fine.


Does a SQL Server bit column really use a whole byte worth of space? I was poking around SSMS and noticed the "size" of my INT columns were 4 bytes (expected) but I was a bit shocked to see my BIT columns were a whole byte.

Did I misunderstand what I was looking at?

How many bit columns do you have defined in the table? I found this on MSDN, it says 8 or less bit columns are stored as one byte.

http://msdn.microsoft.com/en-us/library/ms177603.aspx


Why this result in MySQL is different than Excel or Calc? I've been very confused because this calculation gives different results.

Select (10/110*48576000)

The result is:  4415999.9558

But if I do it in Excel using = 10/110*48576000 the result is 4416000. Even if you count it in Calc, 10 divide by 110 and multiply by 48576000, the result is 4416000.

Can someone explain me this weird behaviour?

Note I'm using Mysql 5.5.46 in Windows Server 2008.

This is due to MySQL converting calculations to float which is at best an approximation of a number.  If you round the solution then 4415999.9558 rounded to 0 decimal places is 4416000.

In MS SQL the calculation results in 0 because the result is an integer and the steps are

10 /110 = 0
0 * 48576000 = 0

however

Select 1.0 * 10 /110 *48576000
4415995.584000

If you are explicit about the data types you get the correct answer i.e.

declare @a  decimal(18,10) = 10;
declare @b  decimal(18,10) = 110;
declare @c  decimal(18,10) = 48576000;
select @a /@b *@c
4416000.00000000000


selecting data after filtering out I am using RDBMS MySQL 5.7. I have 3 tables with the following structures:

Table A - primary table - products:

+----+-----------+-------------+------------+--------+
| id | title     | description | createdAt  | status |
+----+-----------+-------------+------------+--------+
|  1 | Product A | Desc A      | 2019-07-19 |      1 |
|  2 | Product B | Desc B      | 2019-07-04 |      1 |
+----+-----------+-------------+------------+--------+

Table B - product_highlights -> productId and highlightId are foreign keys.

+----+-----------+-------------+
| id | productId | highlightId |
+----+-----------+-------------+
|  1 |         1 |           9 |
|  2 |         1 |          10 |
|  3 |         1 |          11 |
|  4 |         2 |           9 |
|  5 |         2 |          10 |
|  6 |         2 |          11 |
|  7 |         2 |          12 |
+----+-----------+-------------+

Table C - product_categories ->  productId and categoryId are foreign keys.

+----+-----------+------------+
| id | productId | categoryId |
+----+-----------+------------+
|  1 |         1 |          5 |
|  2 |         1 |          7 |
|  3 |         2 |          5 |
|  4 |         2 |          7 |
|  5 |         2 |          9 |
+----+-----------+------------+

I need to select categories and the number of their tours after the filtration process.
Currently, I am using a subquery for the filtration process then, selecting categories for each tour. I was wondering if any optimization can be applied to this query?

 SELECT
        pc.categoryId,
        COUNT(pc.productId)
    FROM
        product_categories pc,
        (
        SELECT
            t1.productId AS s1
        FROM
            product_categories t1
        JOIN
            product_highlights t2
        ON
            t1.productId = t2.productId
        WHERE
            t1.categoryId IN(7, 5) AND t2.highlightId IN(9, 10, 11)
        GROUP BY
            t1.productId
        HAVING
            2 * 3 = COUNT(
                DISTINCT t1.categoryId,
                t2.highlightId
            )
    ) productsIds
    WHERE
        pc.productId = productsIds.s1
    GROUP BY
        pc.categoryId

The resultset should be:

+------------+---------------------+
| categoryId | COUNT(pc.productId) |
+------------+---------------------+
|          5 |                   2 |
|          7 |                   2 |
|          9 |                   1 |
+------------+---------------------+

I gave it a quick look, but the query itself looks fine AFAIK tell. What I would suggest is adding a couple of indexes:

CREATE UNIQUE INDEX X1 ON product_categories (categoryId, productId);
CREATE UNIQUE INDEX X2 ON product_categories (productId, categoryId);

CREATE UNIQUE INDEX X4 ON product_highlights (productId, highlightId);

I would also recommend the following change for the tables:

CREATE TABLE products
( productId int NOT NULL,  -- keep the name the same throughout the model

-- drop attribute id, what is the purpose of that?
CREATE TABLE product_categories
( productId` int NOT NULL
, categoryId int NOT NULL
,   primary key (productId, categoryId) -- Skip index X2 if this is added

-- drop attribute id, what is the purpose of that?
CREATE TABLE product_highlights
( productId int NOT NULL
, highlightId int NOT NULL
,    primary key (productId, highlightId) -- Skip index X4 if this is added


frm-92100:your connection to the server was interrupted frm-92100: error I use my using internet explorer & JInitiator 1.3.1.13 using oracle 11g and dev9i windows server 2008 R2. The app was running fine for last 2 years but suddenly this happen.

I have tried and read almost every page i found from google search but it doesn't help me.

what I tried
I set NLS_LANG to a valid value like AMERICAN_AMERICA.AR8MSWIN1256 in the default.env file which is being used. then it not allow me to login anymore. Also try to set it on environment/registry the result is same.

Only the login form is not loading but all other form run manually from my app.

Please help me.

As you said you did try with both option

 1. Set NLS_LANG to a valid value like AMERICAN_AMERICA.AR8MSWIN1256 in
    the default.env file or any custom environment file which is being
    used.

 2. You can go to regedit and also set the NLS_LANG value to the one
        mentioned above and check if this resolves the issue.

As per ABBASGOL'S BLOG FRM-92100 : your connection to the server was interrupted If the Above Solution Doesn’t Work For you Try The Below One :

1) FRM-92100 occurs when invoking a new forms session from existing form session, or another URL is invoked from the parent browser window of the forms applet?

To work-around use the applet parameter separateFrame=”True”. This parameter is set in the static html file or formsweb.cfg
2) If using Oracle Jinitiator, try clearing the Jinitiator JAR or jcache:

For versions 1.3.x.x.x : Control Panel – Jinitiator Control Panel – Cache Tab Page – Clear JAR Cache button

3) If using MS Internet Explorer check that permissions / security options are set correctly

Launch Microsoft Internet Explorer.
Select the Tools -> Internet Options from the top level menu.
Click the SECURITY Tab:
Choose Local Intranet Zone from the list.
Click Custom Level.
Select Java Permissions – Custom from the list.
Click Java Custom Settings.
Select the Edit Permissions tab.
Choose Enable for Run Unsigned Content.
Choose Enable for Run Signed Content.
Select the ADVANCED tab.
Scroll down to Java VM.
Check the Java console enabled option.
Select the CONNECTIONS tab.
Click the LAN Settings button.
If IE is using a proxy server : Check the Bypass proxy server for local addresses check box. Click the Advanced button, and enter the local domain in the Exceptions box. Use the form *.domain.com, where domain.com maps to your local domain.
Click OK to exit the dialog.
Click OK to save the changes and exit IE.
Restart the computer.


How to run merge agent job step on tsql I'm having a problem with the merge replication and I want to try and run the job as a script on ssms so I can see the errors (I've tried almost everything else)
Original post

Here is the script

-Publisher [Publisher\Instance] -PublisherDB [Database] -Publication [Publication _name] -Subscriber [Subscriber\Instance] -SubscriberDB [SubscriberDB]   -Distributor [Distributor \Instance] -DistributorSecurityMode 1

i've tried exec and then entering that script but the problem is that this is a Replication Merge step and not tsql step.
Is there any way to run this script using tsql ?

In SQL Server parlance, an "Agent" is an external executable that runs outside the SQL Server process. What you see in the job step are the command line parameters for that executable, not a T-SQL script.

The Merge Replication Agent is actually an executable: replmerge.exe. If you want to execute this from outside the SQL Agent job, you would open the command prompt on the server, and run the replmerge.exe command, followed by the contents of the job step:

replmerge -Publisher [Publisher\Instance] -PublisherDB [Database] -Publication [Publication _name] -Subscriber [Subscriber\Instance] -SubscriberDB [SubscriberDB] -Distributor [Distributor \Instance] -DistributorSecurityMode 1

For more info on the replmerge executable, and the available parameters, see the online documentation.


How to run update query without interrupt other processes I want to update a table and what I want to do is when other processes want to use this table, It will pause my update query and when other processes complete, it will continue to update.Is there any way to do this?

I am using Mysql 5.5 by the way.

That's the way InnoDB works anyway. Take a look at the MVCC (multi-version concurrency control - aka record-versioning or record-shadowing) mechanism which most databases use today.

If you are doing a long update and somebody wants to read a record, what the person doing the read will see is the database as it was before the update started. Then, when that long update commits, all subsequent queries will see the system post-update. This way, InnoDB's transactions are ACID, but futhermore, writers don't block readers and vice versa.

For more detail about MySQL specifics, see the manual here - then get back to us if you have more specific questions. If you're really interested in this topic, then I suggest that you read this chapter of the manual which goes into (a lot of) detail about the architecture of the InnoDB storage engine.

The MyISAM engine exclusively uses a locking mechanism, but nobody should really be using it any more as it's now deprecated (if Bill Karwin says so, it must be true!).


Different way to do a LIKE WHERE condition? I have a query which works fine:

  SELECT
         month_date= month(SendTime) ,year_date =year(sendtime) ,MessageType
          ,COUNT(MessageType) AS MESSAGE_COUNT
  FROM Message
  WHERE year(SendTime)= 2014
      and messagetype NOT LIKE '%test%'
      and messagetype NOT LIKE '%ANOTHER THING%'
      and messagetype NOT LIKE '%STUFF%'
  GROUP BY month(SendTime),year(sendtime), MessageType

What I notice is with having LIKE conditions it has made the
the elapse time slower.

With the above query it runs at elapsed time .550, without them .154.

Is there a different way I can be able to cut this time and make query faster?

No messagetype is not index and i am not allow to change it.

Two things you can do:

Use SendTime >= '20140101' AND SendTime < '20150101' instead of the year(SendTime)= 2014. This makes the condition sargable and indexes can be used.
Add a composite index on (SendTime, MessageType) so the query does not have to do a full table scan but only a (partial) index scan.

The query rewritten:

SELECT
    month_date = month(SendTime),
    year_date = year(SendTime),
    MessageType,
    message_count = COUNT(MessageType)
FROM Message
WHERE SendTime >= '20140101' AND SendTime < '20150101'
  AND MessageType NOT LIKE '%test%'
  AND MessageType NOT LIKE '%ANOTHER THING%'
  AND messagetype NOT LIKE '%STUFF%'
GROUP BY year(SendTime), month(SendTime), MessageType ;

Another option would be to add computed columns on year(SendTime) and month(SendTime) and an index on year(SendTime), month(SendTime), MessageType - and keep the original query!


Dropped connections, corrupted schema ... is MySQL sick? Lot's of problems and I wonder if it's all tracable to some root cause. I'm trying to load a simple schema related to product marketing. The symptoms are:

Not being able to forward engineer said schema mysql -u root -p dbeq < dbeq.sql due to loosing connection with the server. 11kb file, 13 tables, each table has at most 13 columns, and the sql file is the output of MySQLWorkbench, so pretty much gauranteed to be syntax error-free. It does SET foreign_key_checks=0 at the start, but I've checked the file table by table; it creates them all in the correct order so that no mismatched foreign key  dependencies occur.
Frequent **ERROR 2013 (HY000) at line 47: Lost connection to MySQL server during query type messages, even when pasting CREATE TABLE statements at the mysql shell (which are 50ms queries at maximum). Max packet is 16Mb and write timeout is one minute, so those can't be the constraints.
The database becomes corrupted by the last table each time (can't create table because it exists, can't drop it because it doesn't).
MySQL workbench can't forward engineer directly to the database either; the connection to the MySQL server gets dropped in under a second.

Do I have something wrong with MySQL? I'm going to try creating the schema on a different machine at home (Unfortunately SQLFiddle is down so I haven't been able to test the schema there).

Output of mysqlcheck -u root -p dbeq (noting that it took 3 goes to create this many tables)

dbeq.AB_test_questions                             OK
dbeq.AB_test_responses_v0                          OK
dbeq.ABs                                           OK
dbeq.business_units                                OK
dbeq.customers                                     OK
dbeq.normalization_survey_customers
Error    : Table 'dbeq.normalization_survey_customers' doesn't exist
status   : Operation failed
dbeq.normalization_survey_questions                OK
dbeq.product_test_questions                        OK
dbeq.product_test_responses_v0                     OK
dbeq.products                                      OK
dbeq.project_managers                              OK
dbeq.survey_hashtable                              OK
dbeq.surveys                                       OK

Output of mysqldump -d dbeq

-- MySQL dump 10.13  Distrib 5.5.41, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: dbeq
-- ------------------------------------------------------
-- Server version   5.5.41-0ubuntu0.14.04.1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
mysqldump: Got error: 1146: Table 'dbeq.normalization_survey_customers' doesn't exist when using LOCK TABLES

Someone fetch me a big spoon, I've got a huge slice of humble pie to get stuck into.

I have no formal database training, but as a developer I have had to interact with databases (mainly via PHP). I've never been the person to create the schema. In debugging this, I was having a really hard time, reading many many SE posts (thanks Rolando, I've lost track of how many of your posts I've read this last week) and the MySQL manual. The trouble is that the errors were all so dang abstract - lost connection error, table doesn't exist error, etc.

So I just tried to run the schema on a machine that had never hosted that database. The server wouldn't take it, and so I knew it couldn't have been all the crazy silly things I had done with the data dictionary. SQLfiddle didn't like it either, and it was something to do with foreign keys because it balked at the tables with them (as did the server).

Turns out that it was just by luck that most of my FKs were uniquely named! I did not know you had to do that. All of the tutorials and lessons I had read never had a complicated-enough example that it became an issue. So after a quick lesson I changed the keys and presto everything works!

SQL's simple syntax and experience with other abstract "tables" of data (spreadsheets, R/Matlab) fools developers into thinking DBs are simple. Doing an enterprise-size schema by youself ought to be compulsory at least once in every developer's life. You guys deserve serious respect.


Best way to delete very large recordset in Oracle I manage an application which has a very large (nearly 1TB of data with more than 500 million rows in one table) Oracle database back end.  The database doesn't really do anything (no SProcs, no triggers or anything) it's just a data store.

Every month we are required to purge records from the two of the main tables.  The criteria for the purge varies and is a combination of row age and a couple of status fields.  We typically end up purging between 10 and 50 million rows per month (we add about 3-5 million rows a week via imports).

Currently we have to do this delete in batches of about 50,000 rows (ie. delete 50000, comit, delete 50000, commit, repeat).  Attempting to delete the entire batch all at one time makes the database unresponsive for about an hour (depending on the # of rows).  Deleting the rows in batches like this is very rough on the system and we typically have to do it "as time permits" over the course of a week; allowing the script to run continuously can result in the a performance degradation that is unacceptable to the user.

I believe that this kind of batch deleting also degrades index performance and has other impacts that eventually cause the performance of the database to degrade.  There are 34 indexes on just one table, and the index data size is actually larger than the data itself.

Here is the script that one of our IT people uses to do this purge:

BEGIN
LOOP

delete FROM tbl_raw
  where dist_event_date < to_date('[date]','mm/dd/yyyy') and rownum < 50000;

  exit when SQL%rowcount < 49999;

  commit;

END LOOP;

commit;

END;

This database must be up 99.99999% and we've only got a 2 day maintenance window once a year.

I'm looking for a better method for removing these records, but I've yet to find any.  Any suggestions?

The logic with 'A' and 'B' might be "hidden" behind a virtual column on which you could do the partitioning:

alter session set nls_date_format = 'yyyy-mm-dd';
drop   table tq84_partitioned_table;

create table tq84_partitioned_table (
  status varchar2(1)          not null check (status in ('A', 'B')),
  date_a          date        not null,
  date_b          date        not null,
  date_too_old    date as
                       (  case status
                                 when 'A' then add_months(date_a, -7*12)
                                 when 'B' then            date_b
                                 end
                        ) virtual,
  data            varchar2(100)
)
partition   by range  (date_too_old)
(
  partition p_before_2000_10 values less than (date '2000-10-01'),
  partition p_before_2000_11 values less than (date '2000-11-01'),
  partition p_before_2000_12 values less than (date '2000-12-01'),
  --
  partition p_before_2001_01 values less than (date '2001-01-01'),
  partition p_before_2001_02 values less than (date '2001-02-01'),
  partition p_before_2001_03 values less than (date '2001-03-01'),
  partition p_before_2001_04 values less than (date '2001-04-01'),
  partition p_before_2001_05 values less than (date '2001-05-01'),
  partition p_before_2001_06 values less than (date '2001-06-01'),
  -- and so on and so forth..
  partition p_ values less than (maxvalue)
);

insert into tq84_partitioned_table (status, date_a, date_b, data) values
('B', date '2008-04-14', date '2000-05-17',
 'B and 2000-05-17 is older than 10 yrs, must be deleted');

insert into tq84_partitioned_table (status, date_a, date_b, data) values
('B', date '1999-09-19', date '2004-02-12',
 'B and 2004-02-12 is younger than 10 yrs, must be kept');

insert into tq84_partitioned_table (status, date_a, date_b, data) values
('A', date '2000-06-16', date '2010-01-01',
 'A and 2000-06-16 is older than 3 yrs, must be deleted');

insert into tq84_partitioned_table (status, date_a, date_b, data) values
('A', date '2009-06-09', date '1999-08-28',
 'A and 2009-06-09 is younger than 3 yrs, must be kept');

select * from tq84_partitioned_table order by date_too_old;

-- drop partitions older than 10 or 3 years, respectively:

alter table tq84_partitioned_table drop partition p_before_2000_10;
alter table tq84_partitioned_table drop partition p_before_2000_11;
alter table tq84_partitioned_table drop partition p2000_12;

select * from tq84_partitioned_table order by date_too_old;


Ora2pg - migration Oracle db into postgreSQL without instalation of Oracle I have sql file with structure:create table,insert into table, contrains, etc.... This script was generated in oracle, and my job is tranform this script into compatible script for postgreSQL.

Does anyone know if can I use ora2pg without installing oracle? For example only with files .sql or oracle dmp? Or I must allways  first import sql file into Oracle db and then connect to oracle db in ora2pg config file

Thank you

DOCUMENTATION

You can use -i:

-i | --input file : File containing Oracle PL/SQL code to convert with
                    no Oracle database connection initiated.

Or set INPUT_FILE in ora2pg.conf:

  INPUT_FILE

  This directive did not control the Oracle database
  connection or unless it purely disable the use of any Oracle database
  by accepting a file as argument. Set this directive to a file
  containing PL/SQL Oracle Code like function, procedure or full package
  body to prevent Ora2Pg from connecting to an Oracle database and just
  apply his convertion tool to the content of the file. This can be used
  with the most of export types: TABLE, TRIGGER, PROCEDURE, VIEW,
  FUNCTION or PACKAGE, etc.

I did not test this though.


PostgreSQL crashes upon startup Every time I restart my Mac OS X Version 10.8.3. and open PSQL Version 9.2.4.3 (14), the PSQL terminal appears for a second and then shuts down.

Below is the info from the docs http://postgresapp.com/documentation

Uninstalling

What I do to remedy the problem is type these 3 lines of code in bash:
    open ~/Library/Application\ Support/Postgres  --> I delete the Var folder
    sudo sysctl -w kern.sysv.shmall=65536
    sudo sysctl -w kern.sysv.shmmax=16777216

When I restart PSQL, it works. The problem I have an empty database. All my work is gone.
How can I get PSQL running permantely so that I do not have to restart with an empty database?

Put those values in /etc/sysctl.conf and they will be read on boot

Mine is:

[codyc:~]$ cat /etc/sysctl.conf
kern.sysv.shmmax=1073741824
kern.sysv.shmall=1073741824


Postgres 8.4 VACUUM FULL performance I am trying to run a vacuum full analyze on a postgres 8.4 machine where it appears that autovacuum has not been able to keep up. The database is very large and under heavy load.

The problem started with postgres denying updates because it is out of transactions. My only option was to take our business offline and do a manual vacuum full.

The issue is that it is taking a really long time and I have been looking for a dependable way to speed it up. I don't want to arbitrarily make changes to the configuration files while the database is in an unknown state.

Here is the postgres.conf configuration for the server.

listen_addresses = '*'
port = 5476
max_connections = 1000

### logging
log_destination = 'syslog'

constraint_exclusion = partition

### work_mem for vacuuming
maintenance_work_mem = 128MB        # min 1MB
                                    # DEFAULT 16MB

# -----------------------------
# CHANGE THESE DEPENDING ON SYSTEM
# VALUES BASED ON 2G RAM
# -----------------------------

### RAM * 1/4
shared_buffers = 512MB              # min 128kB or max_connections*16kB
                                  # DEFAULT: 24MB
#### very import. all work happens here, needs to be as big as possible

temp_buffers = 64MB
#### session-local buffers used to access temporary tables on a per-session basis

### not more than RAM / connections
work_mem = 20MB                   # min 64kB
                                  # DEFAULT: 1MB
#### used for sorting in memory

### raise this if log complains or to reduce checkpoints
wal_buffers = 3MB                 # min 32kB
                                  # DEFAULT: 64kB
checkpoint_segments = 16          # in logfile segments, min 1, 16MB each
                                  # DEFAULT: 3
#### stores changes to shared buffer. higher value = fewer checkpoints/flushes

### RAM * 2/3
effective_cache_size = 1365MB
                                  # DEFAULT: 128MB
#### size of largest possible object

### multiplied defaults by 0.2
cpu_tuple_cost = 0.002                  # (same)
cpu_index_tuple_cost = 0.001            # (same)
cpu_operator_cost = 0.0005              # (same)
#### do this. based on older slower hardware.

### milliseconds vacuum will delay when its cost reaches threshold
vacuum_cost_delay = 20            # default = 0

# -----------------------------
# OPTIONAL DEBUG/LOGGING PARAMS
# -----------------------------

### uncomment the following 6 lines to enable query logging
#silent_mode = on
#log_min_duration_statement = 0
#log_duration = on
#log_statement = 'none'

Any ideas on what I can do to get this server back up asap would be greatly appreciated.

Edit

Here is the exact error message we are receiving, sorry about that.

ERROR:  database is not accepting commands to avoid wraparound data loss in database "live"

This particular error comes with a hint mentioned in the doc,
in Routine vacuuming (8.4):

  ERROR:  database is not accepting commands to avoid wraparound data
  loss in database "mydb" HINT:  Stop the postmaster and use a
  standalone backend to VACUUM in "mydb".

It's a critical situation which normally should not occur if autovacuum can do its job. When you're back online, you should try to find out why this could have happened (wrong vacuum settings?).

In the meantime, what must be done is simply what the HINT tells: VACUUM without additional parameter. As mentioned in the comments , VACUUM FULL is a different command, even though it's not obvious by its name. It rewrites entirely the tables, is much slower and not desirable in this situation.

More details at https://wiki.postgresql.org/wiki/VACUUM_FULL


Debugging error report percona 5.6 I'm running percona 5.6 on a new centos 6.4 server. Things seem to work OK. I personally only really use InnoDB tables for my databases, but only the standard 'mysql' table is in MyISAM format.

I checked the msyql-error.log and saw these messages:

2013-11-14 10:10:26 9013 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_2335_0.MYI'; try to repair it
2013-11-14 10:10:26 9013 [ERROR] Got an error from unknown thread, /mnt/workspace/percona-server-5.6-rpms/label_exp/centos6-64/target/BUILD/Percona-Server-5.6.14-rel62.0/storage/myisam/mi_write.c:226
2013-11-14 10:10:27 9013 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_2335_2.MYI'; try to repair it
2013-11-14 10:10:27 9013 [ERROR] Got an error from unknown thread, /mnt/workspace/percona-server-5.6-rpms/label_exp/centos6-64/target/BUILD/Percona-Server-5.6.14-rel62.0/storage/myisam/mi_write.c:226
2013-11-14 13:53:33 9013 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_2335_0.MYI'; try to repair it
2013-11-14 13:53:33 9013 [ERROR] Got an error from unknown thread, /mnt/workspace/percona-server-5.6-rpms/label_exp/centos6-64/target/BUILD/Percona-Server-5.6.14-rel62.0/storage/myisam/mi_write.c:226
2013-11-14 15:51:54 9013 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_2335_0.MYI'; try to repair it
2013-11-14 15:51:54 9013 [ERROR] Got an error from unknown thread, /mnt/workspace/percona-server-5.6-rpms/label_exp/centos6-64/target/BUILD/Percona-Server-5.6.14-rel62.0/storage/myisam/mi_write.c:226
2013-11-14 19:39:09 9013 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_2335_0.MYI'; try to repair it
2013-11-14 19:39:09 9013 [ERROR] Got an error from unknown thread, /mnt/workspace/percona-server-5.6-rpms/label_exp/centos6-64/target/BUILD/Percona-Server-5.6.14-rel62.0/storage/myisam/mi_write.c:226
2013-11-15 00:18:24 9013 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_2335_0.MYI'; try to repair it
2013-11-15 00:18:24 9013 [ERROR] Got an error from unknown thread, /mnt/workspace/percona-server-5.6-rpms/label_exp/centos6-64/target/BUILD/Percona-Server-5.6.14-rel62.0/storage/myisam/mi_write.c:226

This seems to be relating MyISAM tables, which is then the 'mysql' information table.

Any idea what might be causing this?

I checked the /tmp dir, and it has plenty of space available.

Actually, your problem comes from temporary tables, that may be created in MyISAM format (in fact, up to 5.5, this was the only format for temporary tables on disk), not from the system tables in the mysql database, that are mostly also in MyISAM format.

This may be caused by a new feature of 5.6, where subquery execution may generate indexes for faster processing.

By experience, do not trust what a simple filesystem inspection may tell you about available free space- in particular with files that are deleted very often, but they have still a file descriptor open.

There are many things that you could check here: temporary files created by your processes, integrity of your /tmp filesystem partition, if you have recently mounted/unmounted that partition, trying to change your /tmpdir, etc. Unfortunately Percona Server 5.5 SHOW TEMPORARY TABLES is not available in 5.6 (yet?).


SQL Server : cannot add subscriber to publication I am trying to configure replication as per the official tutorial from Microsoft. In the section Create a subscription to the transactional publication, see step 5.

The name of the subscriber instance is not displayed, so I click Add Subscriber, and then click Add SQL Server Subscriber from the drop-down list. Then the Connect to Server dialog box appears. I enter the subscriber instance name, switch to SQL Server Authentication, fill out the sa username and password, and then click Connect.

After a few seconds, an error dialog appears that reads:

  Cannot connect to DBSUB\SQLEXPRESS.

  Additional information:

  -> Failed to connect to server DBSUB\SQLEXPRESS. (Microsoft.SqlServer.ConnectionInfo)

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server)

The thing is, the server IS very much available. I am able to ping DBSUB as well as RDP into it.

The SQL Server Agent is running.

The SQL Server instance is configured to allow remote connections. (RDP to DBSUB, log into DBSUB\SQLEXPRESS with SQL Server Management Studio on DBSUB (as opposed to SQL Server Management Studio on DBPUB), Server Properties, Connections page, Allow remote connections to this server is checked)

Strangely, intentionally entering the wrong name (DBSUB, DBSUB\BLAH, etc) produces a different error.

Also note that this is not an Express instance. It is an old name from an upgrade.

I would very much appreciate help solving this issue.

Thank you in advance,

Update:
I have configured Aliases as per this article (sorry it's behind a microsoft login). The instructions were incorrect; however, after learning a bit about Aliases, I was able to correctly set it up. Unfortunately, I get a new error that states that Aliases are not a solution to this issue.

  Cannot connect to Subscriber.

  Additional information:

    SQL Server replication requires the actual server name to make a connection to the server.
    Connections through a server alias, IP address, or any other alternate name are not supported.
    Specify the actual server name, 'DBSUB\SQLEXPRESS'. (Replication.Utilities)

I noticed the SQL Browser service was not running. I enabled it on both Publisher and Subscriber and connected to it through DBSUB\SQLEXPRESS and now it works.

As discussed in the comments earlier, the replication couldn't be established is likely due to the instance name, that was being upgraded to a new host, but using back the same instance name as previous.

This issue can be fixed without reinstalling the SQL server, but you will need about a minute or so of downtime.

The following steps can be found in this link here.

(Basically I am copying the steps directly from the link here)

Case 1: For a renamed computer that hosts a default instance of SQL Server

sp_dropserver <old_name>;
GO
sp_addserver <new_name>, local;
GO

Case 2: For a renamed computer that hosts a named instance of SQL Server

sp_dropserver <old_name\instancename>;
GO
sp_addserver <new_name\instancename>, local;
GO

  For both cases, you have to restart your SQL server service afterwards.

Once done, then do a verification again by:

SELECT @@SERVERNAME


PostgreSQL IN operator with lots of values in an external file I have a file with a lot of identifiers, one in each line. I also have a DB with a superset of those id's. I would like to query the DB only with the id's of interest. Is there a way to "import" an external file or do I have to copy all 300 values into the IN expression? This is a large db.

Sure, either,

Use a CTE with a VALUES statement.
Use a TEMP table with an index.

Here is an example with the CTE.

WITH t AS (
  SELECT * FROM ( VALUES
    (1),
    (2),
    (3)
  ) AS t(table_id)
)
SELECT * FROM t
JOIN myTable
  USING (table_id)
;

Only slightly more complex is getting them into a temp table which permits you to index it. You can also use the Foreign Data Wrapper create a FOREIGN TABLE if you have an external file depending on the format. Try it without an index, and copy it into a table and see if an index speeds it up.


Getting data according to last hours from sqlite I want to get records out of sqlite database according to hours. following are my questions

1) I have to extract all data from sqlite for past one hour. I have tried following query but it provide me data for all the hours in present day

Query:

SELECT * FROM Table1 where Date >= datetime('now','-1 hours')

Where Table1 is my table name and Date is coloumn name of type DATETIME

Eg: there are following record in database

When I fire query in sqlite firefox browser tool it returns me

which I do not want.

What should be the query to get past 1 hour data from database

2) What should be query to get the value out of database according to every hours, like I have to get data for past 1 hour, then data of past 1-2 hour, the data of past 2-3 hour i.e an hour data between two hours?

Any Help will be appreciated.

Finally I found the solution to my own question

Following is the code which worked for me

DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = new Date();
        Calendar calendar = Calendar.getInstance();
        String startDate = dateFormat.format(date);
        String endDate = "";
        for (int i = 1; i <= 24; i++) {
            System.out.println("Start Date:- " + startDate);
            calendar.add(Calendar.HOUR_OF_DAY, -1);
            date = calendar.getTime();
            endDate = dateFormat.format(date);
            System.out.println("End Date:- " + endDate);
            String data = dbAdapter.getOutDoorHourlyData(startDate, endDate);
            System.out.println("Hourly Average:- " + data);
            startDate = endDate;
            endDate = "";
        }

public String getOutDoorHourlyData(String startDate, String endDate) {
        double outdoorHourly = 0;

        Cursor cursor = _sqliteDB.rawQuery("Select AVG("
                + COLOUMN_NAME + ") from (Select * FROM "
                + TABLE_NAME + " where " + COLOUMN_NAME + " >= '"
                + endDate + "' and " + COLOUMN_NAME + " < '" + startDate
                + "')", null);

        try {

            if (cursor != null) {
                if (cursor.getCount() > 0) {
                    cursor.moveToFirst();
                    do {
                        outdoorHourly = cursor.getDouble(0);
                    } while (cursor.moveToNext());
                }
                cursor.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        String hourlyData = decimalFormat.format(outdoorHourly);
        hourlyData = hourlyData.replace(",", ".");
        return hourlyData;

    }

 }

I hope it will help someone in future


Implementing HA for SQL Server for a .NET Application that uses MS DTC My production environment uses MS DTC and SQL Server 2014. I must have an HA solution such as Mirroring or AG in place. (Not FCI).

I am trying to explain to my team that MS DTC is not supported by SQL Server with Mirroring / AG and what could go wrong. But all the examples I am seeing online are for cross database scenarios, which doesn't apply to us.

I am looking for a worst case scenario & likelihood of its occurrence. Ideally something that I could actually demonstrate to them. ie: creating a transaction that uses MS DTC, triggering a failover, showing what happens.

As a DBA, I would like to be able to explain to the business what are the real world implications of this lack of support for MS DTC from Microsoft, so that they can decide if the risk is worth the cost of:

Upgrading SQL Server to 2016 & AGs and/or
Changing how their application relies on MS DTC (I am told this is impossible).

I don't want to just parrot "Not supported" to them because that's unacceptable.

Any help appreciated.

Here is the most complete official-ish statement available about the risks and consequences of using DTC in an unsupported configuration with AGs:

  This means you could test a cross database transaction, force failover
  and everything work fine.   Test again it fails.   It could
  be as simple as the controlling DBID changes on the secondary and the
  outcome of the cross-database transaction can’t be properly
  determined.

  For Availability Groups you should avoid the use of DTC and
  cross-database transactions.  Relying or using them could manifest as
  a problem with the database recovery activities and that is certainly
  not what you want in a HADR environment.

https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/06/not-supported-ags-with-dtccross-database-transactions/

Note that all use of DTC with AGs was unsupported in SQL 2014 and before.  In SQL 2016 support was added for DTC transactions not involving multiple databases on the same instance.  In SQL 2017 that restriction was removed.


Mongo memory usage vs shard chunk size Given the available documentation here:

https://docs.mongodb.com/manual/tutorial/modify-chunk-size-in-sharded-cluster/

There is no mention of the potential impact of memory (RAM) usage by changing the default chunk size. I am aware that MySQL (and other databases) always read rows into memory as pages if at least one is required, and wondered if a similar thing might happen in mongo?

If anyone could help shed some light on any possible relationship between chunk size and memory usage that would be greatly appreciated.

There is no relation between those. Chunk size is just partition size in the storage. When you read data from chunk, you read just those documents to the memory, what your query requires.


Storage of final entry values in read only table I have two identical tables, one is for rows which are in a queue, and one is where I move my rows once they have been processed.(thousands of rows processed daily)

In my jobs table, I store ids to other tables and retrieve the values when needed through join statements, However once a row is processed, and I move this row into the final table, I am wondering whether I should just store the actual values here of what the ids refer to, instead of the ids like in the jobs table.

This final table is used encase I need to look up what happened in the past, a potential  problem with storing just the id here is that if I ever change the value in the parent table(what the id references), my final table will be referencing incorrect values as the value has changed since its original entry.

I have not managed to come across this scenario in books yet, so I do not know terminology for this, Or if/how its handled.

I am also curios if perhaps I am handling the referenced data completely wrong?
Perhaps I should not edit my original values once they are stored and every time I need to edit them, I enter a new row and start referencing that id? But this also doesn't sound like a good method.

Thanks in advance.

Your queue records depend on values of other tables referenced via foreign keys. The values you lookup in this reference tables is about to change over time.

You want to be able to check on processed records with wich values they were processed, especially which lookup values where used in the processing that were valid at the time of processing.

In that case you must copy the value in your archive/log table, otherwise as you pointed out your processed-table references the current lookup values, not the ones that were valid at the time of processing.

Versioning your lookup values based on start_dat,end_date depends on if you need the history of these tables even when nothing was processed with these values. If you only need the values if there are processed records i would go with the denormalized log table structure. Much simpler.


Update data of one table from another with comparing columns have same initial strings I have two tables:

NewData:

EName               Job        Sal

smith               clerk      2000
allen               sales      2000
jones.domain.com    Manager    6000

OldData:

EMPNO        ENAME                  JOB         HIREDATE       SAL

7369         smith.domain.com       clerk       17-DEC-80      1300
7549         allen.domain.com       sales       01-JAN-81      1800
7645         jones.domain.com       Manager     01-JAN-80      5000

I want to update columns Job and Sal of the table OldData from table NewData by using column EName. I tried it with LIKE and SUBSTR but not getting a good solution using them.

LIKE query-
UPDATE emp_backup
SET emp_backup.sal = (select t1.sal from t1 join emp_backup on t1.ename like emp_backup.ename+'%')

SUBSTR query-
UPDATE emp_backup
SET emp_backup.sal = (select t1.sal from t1 join emp_backup on SUBSTR(t1.ename,1,4)=SUBSTR(emp_backup.ename,1,4));

This should update for you as expected. Apologies for the number of character functions - I'm sure it could be improved via a REGEXP_SUBSTR call but I'm afraid I'm a bit busy at the moment.

MERGE INTO olddata old
USING (SELECT * FROM newdata) new
ON (  old.ename = new.ename
   OR substr(old.ename,1,length(old.ename)-length(substr(old.ename,instr(old.ename,'.'),length(old.ename)))) = new.ename)
WHEN MATCHED THEN UPDATE SET old.salary = new.salary, old.job = new.job

The first qualifier in the ON clause will match any that have the same format name and the second will take the string from OLDDATA.ENAME before the first dot to compare it to the NEWDATA.ENAME that doesn't contain the domain name.
Hope this works for you (it did for my quick tests on the data you supplied)

EDIT:
Here's my simple test:

  1* SELECT * FROM olddata
SQL> /

     EMPNO ENAME                          JOB                      SALARY HIREDATE
---------- ------------------------------ -------------------- ---------- ---------
      7369 smith.domain.com               clerk                      1300 17-DEC-80
      7645 jones.domain.com               Manager                    5000 01-JAN-80
      7549 allen.domain.com               sales                      1800 01-JAN-81

SQL> SELECT * FROM newdata
  2  /

ENAME                          JOB                      SALARY
------------------------------ -------------------- ----------
jones.domain.com               Manager                    6000
allen                          sales                      2000
smith                          clerk                      2000

SQL> MERGE INTO olddata old
  2  USING (SELECT * FROM newdata) new
  3     ON (  old.ename = new.ename
  4        OR substr(old.ename,1,length(old.ename)-length(substr(old.ename,instr(old.ename,'.'),length(old.ename)))) = new.ename)
  5  WHEN MATCHED THEN UPDATE SET old.salary = new.salary, old.job = new.job
SQL> /

3 rows merged.

SQL> SELECT * FROM olddata
  2  /

     EMPNO ENAME                          JOB                      SALARY HIREDATE
---------- ------------------------------ -------------------- ---------- ---------
      7369 smith.domain.com               clerk                      2000 17-DEC-80
      7645 jones.domain.com               Manager                    6000 01-JAN-80
      7549 allen.domain.com               sales                      2000 01-JAN-81

SQL>

EDIT:  Thanks to @Guarava for pointing out that the string operations can easily be removed. I was just overthinking it. The following MERGE is much more concise;

MERGE INTO olddata old
USING (SELECT * FROM newdata) new
ON (  old.ename = new.ename
   OR old.ename LIKE new.ename||'.%')
WHEN MATCHED THEN UPDATE SET old.salary = new.salary, old.job = new.job


index merge vs multi-column index So I have this query:

UPDATE ref_records SET count = count + 1 WHERE ref_url = 'http://www.example.com/' AND user_id = '32366' LIMIT 1

I have indexes set on each "ref_url" and "user_id" respectively. However, MySQL explain shows that it's merging the indexes:

Using intersect(user_id,ref_url); Using where

But why doesn't it do so in their actual column order (ref_url, user_id) ?
Also, would it help optimize this query by adding an index on (ref_url,user_id) or index on (user_id,ref_url) ?

Thanks.

Using intersect is almost always an indication that you should have a "composite" index on the columns mentioned.

In your case, the index can be either of these:

INDEX(user_id, ref_url)
INDEX(ref_url, user_id)

The order of clauses in the WHERE does not matter -- the Optimizer is free to reorder things.  It does not matter to the optimization.

On the other hand, the order of columns in a composite index does matter.  (But it does not happen to matter in this case.)

Further explanation:  For the "intersect" technique, this happens:

Find all entires in INDEX(user_id) for 32366; collect the PRIMARY KEYs for those rows.
Find all entries in INDEX(ref_urs) for 'http...'; collect the PRIMARY KEYs for those rows.
Using those two lists, "intersect" them.  That is, find out what entries are common to both.
Now, reach for the desired rows by using the PRIMARY KEYs.

With a composite index:

Drill down the BTree directly to the desired row(s).


Postgresql : How to login as a NOSUPERUSER and create a database? After logging in as Superuser "postgres" I created another user "p5" :

testdb=# CREATE USER p5 NOSUPERUSER CREATEDB LOGIN PASSWORD '123';
CREATE ROLE
testdb=# \du

p5        | Create DB
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

To login as postgres(superuser) I used to do :

revoltman@G41:~$ psql testdb postgres
Password for user postgres:
psql (9.5.10)
Type "help" for help.
testdb=#

How to login as user "p5" and create a database "db5" ? p5 already has CREATEDB rights ! Should I create "db5" first as a Superuser and then connect "p5" to "db5" ? If so then what is the use of p5 having CREATEDB rights ?

https://stackoverflow.com/questions/31819377/psql-login-fails-database-not-found?rq=1
I think I understood it. I should connect to an existing database to login as p5 user. Then I create another database "db5".
If postgresql could have allowed login and then allowed to create databases then it would have been an intuitive way.


Dependency preserving and lossless join decompositions I have a relation:

R = {A, B, C, D, E, F, G, H, I, J}

And a set of functional dependencies:

G = {AB -> C, A -> DE, B -> F, F -> GH, and D -> IJ}

I ended up decomposing into two decompositions.

D1 = {R1, R2, R3, R4, R5}

      R1 = {A, B, C}
      R2 = {A, D, E}
      R3 = {B, F}
      R4 = {F, G, H}
      R5 = {D, I, J}

D2 = {R1, R2, R3, R4, R5}

      R1 = {A, B, C, D}
      R2 = {D, E}
      R3 = {B, F}
      R4 = {F, G, H}
      R5 = {D, I, J}

How can I prove that D1, D2 are dependency preserving and lossless join decompositions?

Related question that got me as far as the decompositions but I am lost.
2NF decomposition + database normalization

As far as I understand the theory, if you can join your decompositions and arrive back at your relation with no loss, that's it.

It looks to me like AB, A, B, F, D are the keys to do so.