How to delete a datafile if the father-tablespace does not exists? I just know Oracle basic and I would like to know about the datafiles.
I have some files ".dbf" associated with Tablespace that already were deleted. I don't know how I can drop that datafiles and have some free space in my server.

Look:
This is my dbf :

And this is the father-tablespace:
select tablespace_name from dba_data_files where file_name = '/u01/app/oracle/oradata/orcl/BGBA_GAL126344.dbf';

Result:

If I try to drop that tablespace, It says that the tablespace does not exists.

How can I drop that datafile if the father-tablespace does not exists?

The TS associated with your file is 'BGBA_GAL126344', but you are issuing your DROP TABLESPACE on 'GAL126344'.

'BGBA_GAL126344' != 'GAL126344'

Why were you trying to DROP USER?  Neither files nor tablespaces are owned by users.

Why do you repeatedly issue the same failed DROP TABLESPACE command?

FWIW, in 20+ years of working as an Oracle DBA, I have never heard the term 'father-tablespace'.  I think the more correct term would be a file's 'associated tablespace'.


SQL Server Index, difference between Average fragmentation and Total fragmentation Using SQL server, If i navigate to a table in the object explorer, go to its indexes, go to its properties, and view the fragmentation tab, the top section has 2 lines for me, the Page fullness in percent, and the Total Fragmentation in percent.

I also have a query i occasional use for index information. Trimming out some unrelated fields, this is the query:

SELECT t.name, ix.name, avg_fragmentation_in_percent
FROM sys.indexes AS ix
INNER JOIN sys.tables t ON t.object_id = ix.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.dm_db_index_physical_stats  (db_id(), NULL, NULL, NULL, 'DETAILED') ps ON t.object_id = ps.object_id AND ix.index_id = ps.index_id
WHERE  ix.index_id > 0
    AND ix.name IS NOT NULL

When i run this query, i get 3 columns, the first 2 pointing to the index itself, and for this query, the third is a fragmentation in percent for that index.

These 2 numbers are Usually they are very close, sometimes identical, but it also can be and frequently is different. I have had cases where the discrepancy is massive, over 50% in some cases, but normally it is something like <1% vs 15-30%. The queries result is almost always the higher percent.

Rebuilding the index will completely or nearly zero both of these numbers (though not necessarily will both be zero). So i now have 2 numbers for fragmentation on an index. According to the documentation for sys.dm_db_index_physical_stats and Index Properties(FragmentationPage) Both are supposed to be the logical Fragmentation of the index, so they should always be the same number. How is it that i am getting different numbers for what should be the same statistic?

There's a big difference between your query and the query the UI sends (which you can spy on using server-side trace, Extended Events, etc). In yours, the last argument to sys.dm_db_index_physical_stats is DETAILED:

... sys.dm_db_index_physical_stats( ..., 'DETAILED') ...

Whereas the UI is on a budget and sends SAMPLED instead:

... sys.dm_db_index_physical_stats( ..., 'SAMPLED') ...

DETAILED scans all of the pages, while SAMPLED only scans 1 percent. That's right, 1%. You pay for more accuracy obviously, but relying on the info about every 100th page to make any judgments about the state of the index is, well, not very useful. You could always change your manual query to SAMPLED if your goal is to just match the uselessness of the UI. :-)

(In fairness, this topic about the UI properties page should explicitly document the method that is used to determine fragmentation. I have a confirmation from Microsoft's Pedro Lopes that he will keep it in mind for an upcoming doc revision.)

Just never use or trust the UI. Never mind that it's a pain in the rear to look at more than one index at a time, it's also cumbersome to figure out what it's even doing, and you always have to worry about whether it is still doing what you used to think it did... it's just not worth the occasional expediency in looking up one thing rather than having a verifiable, repeatable query to run...


MySQL Subquery returns more than one row I have a table namely tbl_plays. It has the following columns and data

id(PK)|    gameid    |    drawid    |   etc
----------------------------------------------
   1  |     1509     |      73      |   etc

I am running this simple query.

SELECT id FROM tbl_play WHERE id = 1 OR gameid = 0

This query is run as a subquery which returns the id column data to a variable.

Now my question, why do I get the error SUBQUERY RETURNS MORE THAN ONE ROW where as it returns only one row. In Workbench it returns an editable result-set, and myAdmin shows edit/delete buttons.

Does it have anything related to the fact that I am fetching a PK column data? If it does, then how can I over come this.

Regards

P.S: id column has all unique values.

You need to add the LIMIT clause
SELECT id FROM tbl_play WHERE id = 1 OR gameid = 0 LIMIT 1;

This is an oversimplified answer.
If there is a row with PK=1, you find it.
If not, a full table scan may ensue because of gameid not being indexed.
You are better off splitting the query with a UNION. Perhaps, like this:
SELECT id FROM
(
    SELECT id FROM tbl_play WHERE id = 1
    UNION
    (SELECT id FROM tbl_play WHERE gameid = 0 LIMIT 1)
) A ORDER BY id LIMIT 1;

Since I do not know the data, I cannot predict any performance results.
Give it a Try !!!
UPDATE 2014-01-23 11:01 EST
The reason it works because of the goal I set for the query : Retrieve one value.
First look at the UNION
    SELECT id FROM tbl_play WHERE id = 1
    UNION
    (SELECT id FROM tbl_play WHERE gameid = 0 LIMIT 1)

The first SELECT can return at most one row.
The second SELECT can return at most one row because of the LIMIT 1.
In the worst case scenario, the UNION will have two rows.
Now look at the whole query:
SELECT id FROM
(
    SELECT id FROM tbl_play WHERE id = 1
    UNION
    (SELECT id FROM tbl_play WHERE gameid = 0 LIMIT 1)
) A ORDER BY id LIMIT 1;

The outer part of the query will receive either 0, 1, or 2 rows.
Then, the part ORDER BY id LIMIT 1 forces the outer query to choose the first value.


Huge index for covered queries (indexOnly: true) or (indexOnly: false) with collection scan I have two questions, I'll give you the facts, then my questions.

The actual index I'm using for one of my queries has the following characteristics :

Multikey (contains 4 keys).
The average key length is 14 characters.
2 indexes values contains 2 letters (country code and state code).
1 index values contain numerical values (between 0 and 100).
The index size is : ~395 MB.

Here's an example of what should be indexed (I don't know how mongodb actually is storing it's indexes, so I'm going to represent it like if it was a collection document):

{
    "geolocation.statecode": "AL",
    "personnu_field": 50,
    "geolocation.countrycode": "US"
    "field1.sufield": "Awesome value"
}

Informations about the collection concerned by the index :

The collection is a 6M documents.
Fast growing.
It's actually a collection of Twitter users with some additional business related fields.
field1 (see the example given above) is an array of subdocuments.
avgObjSize: 3 KB.
totalIndexSize: ~2.9 GB.
storageSize: ~19 GB.

I've done an explain to this long query :

db.crawler_users.find(
    {
        "geolocation.statecode": "AL",
        personnu_field: { "$lte": 65, "$gte": 30 },
        "geolocation.countrycode": "US"
    },
    {
        personnu_field:1, _id:0
    }
).hint(
    {
        "geolocation.countrycode" : 1,
        "personnu_field" : -1,
        "geolocation.statecode" : 1,
        "field1.sufield" : 1
    }
).explain()

And here's the result:

{
    "cursor" : "BtreeCursor geolocation.countrycode_1_personnu_field_-1_geolocation.statecode_1_field1.sufield_1",
    "isMultiKey" : true,
    "n" : 216,
    "nscannedObjects" : 788609,
    "nscanned" : 788609,
    "nscannedObjectsAllPlans" : 788609,
    "nscannedAllPlans" : 788609,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 128,
    "nChunkSkips" : 0,
    "millis" : 127451,
    "indexBounds" : {
        "geolocation.countrycode" : [
            [
                "US",
                "US"
            ]
        ],
        "personnu_field" : [
            [
                65,
                -1.7976931348623157e+308
            ]
        ],
        "geolocation.statecode" : [
            [
                    {
                    "$minElement" : 1
                },
                    {
                    "$maxElement" : 1
                }
            ]
        ],
        "search.keyword" : [
            [
                    {
                    "$minElement" : 1
                },
                    {
                    "$maxElement" : 1
                }
            ]
        ]
    },
}

As you can see the query takes much time to be executed (>2 minutes). And it's hitting the collection. even if the chosen fields already exists on the index.

I have two questions somehow related :

Why is indexOnly: false. Isn't it supposed to be a covered index query? (see the explain later)
I need to retrieve some additional fields from the collection (the id and the profile_picture url). Should I add them to the index to avoid hitting the collection, even if I'll never have to query them?

Why is indexOnly: false. Isn't it supposed to be a covered index
  query? (see the explain later)

I believe this is a result of the isMultiKey : true field in the explain results.  Basically, currently indexOnly is never true when isMultiKey is true.

This is a known problem in general with multi key indexes.  You can find the relevant bug here:

https://jira.mongodb.org/browse/SERVER-3173

As well as some decent explanation in the linked/dupe bug here:

https://jira.mongodb.org/browse/SERVER-7595

I think you have done some manual munging of the fields here for some reason, but I would guess that search.keywords is the problem here.  Try an index without that as the final field and see if that performs better.

  I need to retrieve some additional fields from the collection (the id
  and the profile_picture url). Should I add them to the index to avoid
  hitting the collection, even if I'll never have to query them?

I'd recommend a separate index for those queries rather than massive single index.  If you end up with too many fields in the index you are going to lose most of the benefit by simply having to scan through a massive index instead of a collection.  An index that big will also likely have performance issues for updates/writes.


PHPMyAdmin - Missing value in the form I'm currently trying to learn MySQL and have a problem that I was unable to solve.

I started to use the query window to give certain commands like:

CREATE DATABASE ijdb;

The problem is that whenever I do a command in the Query Window, I get the following:

Any hints on how to debug this? Tried numerous wamp servers on windows and all of them give me the same pop-up when trying to use PHPMyAdmin Query Window.

If It helps:
I'm currently using : BitNami Wamp Stack 5.4.24-0 and latest version of Google Chrome.

Thanks in advance!

Close this Query window.
Click home button in the top left corner below the phpMyAdmin logo.

Click on the database ijdb, to RUN QUERIES on DATABASE ijdb
CLick the tab SQL which is second from left in the menu.
Enter the query and run it. It should work now.

Read this reference book - PHP & MySQL: Novice to Ninja


Max connections SQL Server Express could have? I have SQL Server 2005 Express edition installed on one of my client's machine, and lots of other users are also connected to that SQL Server through the C# application that I have provided them as an interface.

Sometimes a pc get connected to SQL Server and sometimes just throw the exception

  SQL Server not found....

I believe the Express edition reaches the maximum connection limit of it and then gives error.

Please guide me how could I solve this issue.

Any white paper that help me know which edition have how many number of parallel connections,

I am open to upgrade to SQL Server 2008 or SQL Server 2012, but prefer Express edition. I could go for other versions as well if they really pay me back in this context, otherwise Express edition is doing well.

It seems with Windows 7, you can have up to 20 concurrent connections but no more.

If you need more, you'll have to put your SQL Server installation onto a Windows Server OS.

Also see this other SO question on the same topic - SQL Server Express does not limit the number of max connections (which have a 32'768 limit on any edition of SQL Server)


Linked Server Insert Permission Msg 229, Level 14, State 5, Line 1
The INSERT permission was denied on the object 'object_name', database 'database_2', schema 'dbo'.

Above is the error. I have a linked server set up (set up in prod which links to test server). I want to insert a data from prod to test. INSERT statement is working on database 1 but error above occurs on database 2. Is there a way to check the permissions of local user (prod) to linked server objects (test)? Where should I check for the missing set up?

Sorry, I was in a hurry to ask it since it was urgent. For those who will encounter it, this may help:

Check the user that was used in the linked server. That permission in the linked server (in my case, on the test) should be set up properly.

In my case, it has no access on database_2.


Location of the mdf file of the database I have a database Project. My problem is where can I find the .mdf and _log.ldf of my database so that I can transfer my database to another user. I am using Microsoft SQL Server 2008 Management Studio

My Ssms.exe is stored in this path

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe

There are few ways to determine the location of the SQL Server mdf file(s) and associated log file(s).

Open Enterprise Manager, right click on the database you are interested in and select properties. Select the Files section and scroll across to the Path and FileName columns.
Open a query window and run the relevant query below and view the Physical_Name column.

SQL 2005 or later:

SELECT * FROM sys.database_files

SQL 2000:

SELECT * FROM dbo.sysfiles

Open a query window and run sp_helpfile and view the FileName column.

Of course, as these files are in use by SQL Server you should not attempt to copy the files to a different location.

The best method is to perform a backup from within Enterprise Manager by right clicking on the database you are interested in and selecting Tasks -> Backup.

Alternatively you can detach your database, copy the files and then attach.

A third alternative once you have a copy of the database running elsewhere, is to set up log shipping or replication.


Window functions cause awful execution plan when called from a view with external parametrized 'where' clause I had this issue long time ago, I found a workaround which suited me and forgot about it.

But now there's that question on SO so I'm willing to bring this problem up.

There's a view that joins few tables in a very straightforward way (orders + order lines).

When queried without a where clause, the view returns several million lines.
However, noone ever calls it like that. The usual query is

select * from that_nasty_view where order_number = 123456;

This returns about 10 records out of 5m.

An important thing: the view contains a window function, rank(), which is partitioned exactly by the field using which the view is always queried:

rank() over (partition by order_number order by detail_line_number)

Now, if this view is queried with literal parameters in the query string, exactly as shown above, it returns the rows instantly. The execution plan is fine:

Index seek on both tables using the indices on order_number (returns 10 rows).
Calculating windows over the returned tiny result.
Selecting.

However, when the view is called in a parametrized way, things get nasty:

Index scan on all tables ignoring indices. Returns 5m rows.
Huge join.
Calculating windows over all partitions (about 500k windows).
Filter to take 10 rows out of 5m.
Select

This happens in all cases when parameters are involved. It can be SSMS:

declare @order_number int = 123456;
select * from that_nasty_view where order_number = @order_number;

It can be an ODBC client, such as Excel:

select * from that_nasty_view where order_number = ?

Or it can be any other client that uses parameters and not sql concatenation.

If the window function is removed from the view, it runs perfectly quickly, regardless of whether or not it's queried with parameters.

My workaround was to remove the offending function and reapply it at a later stage.

But, what gives? Is it genuinely a bug in how SQL Server 2008 handles window functions?

This appears to be a long standing issue that keeps resurfacing in one form or another and is still present in SQL Server 2012.
Some posts discussing it are

Performance Tuning 101 - 2005
Plan Regression with Sequence Projections - 2008 R2 @Paul White
Common Table Expressions (CTEs), Window Functions, and Views - 2012 @Jonathan Kehayias
The Problem with Window Functions and Views

All current versions of SQL Server up to and including 2012 are not able to push the filter on a partitioning group past the sequence project for a parameterised predicate except if option(recompile) is used (if 2008+).
An alternative to the recompile hint would be to rewrite the query to use a parameterised inline TVF as suggested by @a1ex07)


Stack dumps when turning on Temporal Table SYSTEM_VERSIONING I am running SQL Server 2016 SP1 (13.0.4001) on Windows Sever 2012 R2. I have the below table.
CREATE TABLE [SubEx].[Ingredients](
    [IngredientId] [int] IDENTITY(1,2) NOT NULL,
    [IngredientName] [nvarchar](328) NOT NULL,
    [IngredientShortDescription] [nvarchar](328) NOT NULL,
    [PortionUOMTypeId] [smallint] NULL,
    [WISRTypeId] [smallint] NOT NULL,
    [WISRSubTypeId] [smallint] NULL,
    [FlavorTypeId] [smallint] NULL,
    [StatusTypeId] [smallint] NOT NULL,
    [IsMandatory] [bit] NOT NULL,
    [IsDownloaded] [bit] NOT NULL,
    [CreatedDT] [datetime] NOT NULL,
    [CreatedBy] [int] NOT NULL,
    [LastUpdateDT] [datetime] NULL,
    [LastUpdateBy] [int] NULL,
    [Deleted] [bit] NOT NULL,
    [DeletedDT] [datetime] NULL,
    [DeletedBy] [int] NULL,
    [CreatedUserName] [nvarchar](136) NOT NULL,
    [UpdatedUserName] [nvarchar](136) NULL,
    [DeletedUserName] [nvarchar](136) NULL,
    [ValidFrom] [datetime2](2) GENERATED ALWAYS AS ROW START NOT NULL,
    [ValidTo] [datetime2](2) GENERATED ALWAYS AS ROW END NOT NULL,
 CONSTRAINT [PK_Ingredients_IngredientId] PRIMARY KEY CLUSTERED
(
    [IngredientId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY],
    PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
) ON [PRIMARY]

GO

ALTER TABLE [SubEx].[Ingredients] ADD  CONSTRAINT [DF_Ingredients_IsMandatory]  DEFAULT ((0)) FOR [IsMandatory]
GO

ALTER TABLE [SubEx].[Ingredients] ADD  CONSTRAINT [DF_Ingredients_IsDownloaded]  DEFAULT ((0)) FOR [IsDownloaded]
GO

ALTER TABLE [SubEx].[Ingredients] ADD  CONSTRAINT [DF_Ingredients_Deleted]  DEFAULT ((0)) FOR [Deleted]
GO

ALTER TABLE [SubEx].[Ingredients] ADD  CONSTRAINT [DF_ValidFrom]  DEFAULT (dateadd(second,(-1),sysutcdatetime())) FOR [ValidFrom]
GO

ALTER TABLE [SubEx].[Ingredients] ADD  CONSTRAINT [DF_ValidTo]  DEFAULT ('9999.12.31 23:59:59.99') FOR [ValidTo]
GO

ALTER TABLE [SubEx].[Ingredients]  WITH CHECK ADD  CONSTRAINT [FK_Ingredients_TlkpFlavorTypes_FlavorTypeId] FOREIGN KEY([FlavorTypeId])
REFERENCES [SubEx].[TlkpFlavorTypes] ([FlavorTypeId])
GO

ALTER TABLE [SubEx].[Ingredients] CHECK CONSTRAINT [FK_Ingredients_TlkpFlavorTypes_FlavorTypeId]
GO

ALTER TABLE [SubEx].[Ingredients]  WITH CHECK ADD  CONSTRAINT [FK_Ingredients_TlkpStatusTypes_StatusTypeId] FOREIGN KEY([StatusTypeId])
REFERENCES [SubEx].[TlkpStatusTypes] ([StatusTypeId])
GO

ALTER TABLE [SubEx].[Ingredients] CHECK CONSTRAINT [FK_Ingredients_TlkpStatusTypes_StatusTypeId]
GO

ALTER TABLE [SubEx].[Ingredients]  WITH CHECK ADD  CONSTRAINT [FK_Ingredients_TlkpWISRCategoryTypes_WISRTypeId] FOREIGN KEY([WISRTypeId])
REFERENCES [SubEx].[TlkpWISRCategoryTypes] ([WISRTypeId])
GO

ALTER TABLE [SubEx].[Ingredients] CHECK CONSTRAINT [FK_Ingredients_TlkpWISRCategoryTypes_WISRTypeId]
GO

ALTER TABLE [SubEx].[Ingredients]  WITH CHECK ADD  CONSTRAINT [FK_Ingredients_TlkpWISRSubCategoryTypes_WISRSubTypeId] FOREIGN KEY([WISRSubTypeId])
REFERENCES [SubEx].[TlkpWISRSubCategoryTypes] ([WISRSubTypeId])
GO

ALTER TABLE [SubEx].[Ingredients] CHECK CONSTRAINT [FK_Ingredients_TlkpWISRSubCategoryTypes_WISRSubTypeId]
GO

When I run the below script to enable SYSTEM_VERSIONING for my temporal table, I receive the below error message and a stack dump.
ALTER TABLE SubEx.Ingredients
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = SubEx.IngredientsHistory));

Msg 596, Level 21, State 1, Line 8 Cannot continue the execution
because the session is in the kill state.
Msg 0, Level 20, State 0,
Line 8 A severe error occurred on the current command.  The results,
if any, should be discarded.

Am I doing something wrong or do I need to submit a bug with Microsoft?

This might be your problem:

A dump file is generated when you enable system-versioning on a table in SQL Server 2016

Try installing CU1 of Sql 2016 SP1


Database design experienced view I want to make a database that stores countries, their respective states, and cities.  I am confused how to normalize these.  Can anyone help?

My ideas:

Table 1  Countries (id pk, name , code)
Table 2  State (id pk, name, country_id fk, code)
table 3  Cities (id pk, name, state_id fk, pin_code)

I want to store hotels along with their address(es):

table 4 address (id pk, street_address, city_id fk, state_id fk, country_id fk)
table 4 Hotel (id pk, name, address_id)

Are there any changes for future proofing I should make, or any other mistakes I've made?

Well, if you want/need to break it out that way, I would go:

Country(id,name,code)
Region(id,name)
Sub_Region(id,name)
City(id,name)
Address(id,country_id,region_id,sub_region_id,city_id,street_address,postal_box,appt_suite_number,floor_number,postal_code)

That should cover most of your scenarios US and world-wide. (ie, region could cover states, provinces, etc. Sub region can cover counties, sub-provinces, etc. Postal code instead of zip code so it can work world wide.) And I suppose if you really wanted to get fancy you could break out the street "type" (ie, street, avenue, boulevard, circle, etc.). But I don't know if you need that level of granularity or not?


Handling SQL updates for production deployments I'm researching for quite a long time now (about two months) and haven't found a good approach for this, so I'll ask you experts out there in the hope of some enlightenment.

I run a pretty traditional LAMP web application in the cloud. The source code is VCS'ed with Git, as well as database schema (split SQL files for structure and default data).

Deploying a new environment is quite a joy, development or production. Dev environments are deployed with Vagrant plus some nice shell provisioning script I wrote. Production environments are deployed using a subset of the Vagrant provisioning script, which is also very easy to do.

When the development cycle imposes a change to the SQL schema (structure or data), things starts to break. In that case, developers changes the core SQL scripts so all the changes gets into the VCS, which is totally fine for brand new environments.

The problem sits within already deployed production environments: for those, I have to manually get all the patches for the SQL changes since the revision actually running in all production servers first so I can apply them manually after. This takes a long time to happen, is a quite fragile task and error prone.

Initially, I thought I can move the SQL changes away from the core SQL files smaller subsets, letting the core SQL files as a starting point. When there is something to change at SQL structure, I could just tell devs to create a new SQL file with only the changes from that development cycle.

So, for instance:

- structure_begin.sql: the SQL schema as it is now.
|- structure_devcycle1.sql: first set of changes to the structure
|- structure_devcycle2.sql: second set of changes to the structure, and so forth...

Then, by using Git hooks, I can selectively and automatically deploy them on production. But I don't know if this is a good approach. And here I can ask:

Does somebody out there have solved this puzzle?
What are the best practices for release management and deploy automation for SQL changes in production environments?
Are there any open source tools that can help in this process? (I'm aware of Sqitch, but I'm not quite sure if it is the right one for the job.)

I am not sure if you still need an answer, but I had the same problem with managing and deploying schema changes. Sqitch was the first thing I tried to mechanize migrations, but I did not really like it.

Just as you described, we have separate .sql files with CREATE TABLE and other instructions reflecting our database schema. We created simple tool to generate SQL patches when schema changes. Take a look at it: https://github.com/condograde/sqlibrist. There is detailed tutorial, how to use it.


How to get updated stats for a truncated table I want to check if tables are empty (after a truncate + reload) in a schema and I tried the following:

CREATE TABLE public.whatever (
    id,
    val
) AS VALUES
  (1, 2),
  (1, 2),
  (1, 2),
  (1, 2),
  (1, 2),
  (1, 2),
  (1, 2);

TRUNCATE public.whatever;
ANALYSE public.whatever;
VACUUM public.whatever;
-- stats table seems to be static in the current transaction
-- https://www.postgresql.org/docs/9.6/static/monitoring-stats.html
COMMIT;
-- no result, so it's empty
SELECT *
FROM public.whatever;

-- but this still shows some data in the table:
SELECT
  schemaname,
  relname,
  n_tup_ins
FROM
  pg_stat_all_tables
WHERE
  schemaname = 'public' AND relname = 'whatever'

When I drop and recreate the table (without values), the above shows 0 as n_tup_ins, so at least that workaround works.

DROP TABLE public.whatever;
CREATE TABLE public.whatever (
  id integer,
  val integer
);

Do I have a wrong understanding of the stats table or is this a bug?

We now used the following solution to ensure that all our raw data tables were filled:

CREATE OR REPLACE FUNCTION util.assert_table_not_empty(schema_tablename TEXT)
  RETURNS BOOLEAN AS $$
DECLARE
  result TEXT;
BEGIN
  -- * doesn't work if the first cell would be NULL
  EXECUTE 'SELECT 1 FROM ' || schema_tablename || ' LIMIT 1'
  INTO result;

  IF result is NULL
  THEN
    RAISE EXCEPTION 'assertion table not empty, failed for:
%', schema_tablename;
  END IF;
  RETURN result IS NULL;
END
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION raw_data.ensure_data()
  RETURNS VOID AS
$$
DECLARE
  tablename TEXT;
BEGIN

  FOR tablename IN SELECT table_schema || '.' || table_name
                   FROM information_schema.tables
                   WHERE table_schema IN ('raw_data')

  LOOP
    PERFORM util.assert_table_not_empty(tablename);
  END LOOP;
END;
$$ LANGUAGE 'plpgsql';

-- in the ETL parts:
SELECT raw_data.ensure_data();


clusterdb stopped working for PostgreSQL database in Ubuntu 12.04 server After a fresh PostgreSQL install (version 9.2) on my Ubuntu server 12.04, clusterdb utility stopped working. I used the same config files as I used with version 9.1.

Below is the actual command:

clusterdb -v -h localhost -p <port#> -U postgres -d <dbname>

And this is the response that I'm getting:

clusterdb: could not connect to database <dbname>: could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port <port#>?

I'm able to connect to the server locally and from outside. I can also use another utilities  such as pg_dump with no problems.

My pg_hba.conf file below:

# Database administrative login by Unix domain socket
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the replication privilege.
local all postgres md5
host all all x.x.x.x/x md5         #my network
host    all all  x.x.x.x/x  md5    #internal ip of the server

Are there any security changes were implemented in PostgreSQL 9.2 vs 9.1 that may prevent clusterdb to work properly?

It looks like something went wrong when you upgraded. You have to check that the port and listen_addresses are correct. Perhaps the listen_address changed and the instance isn't listening on the host anymore?

The reason that pg_dump and the other tools are still working is probably because they're connecting over the unix socket instead over TCP/IP. clusterdb should also be able to do that if you just leave out the -h and -p options.

The pg_hba.conf is not relevant because your client can't connect to the server at all. You can verify that the server is listening on the expected host and port with telnet <host> <port>.

http://www.postgresql.org/docs/9.2/static/runtime-config-connection.html


What does MBRContains and MBRWithin test? What does "contains" and "within" mean? MBRContains(g1, g2) is defined as,

  Returns 1 or 0 to indicate whether the minimum bounding rectangle of g1 contains the minimum bounding rectangle of g2. This tests the opposite relationship as MBRWithin().

MBRWithin(g1, g2) is defined as,

  Returns 1 or 0 to indicate whether the minimum bounding rectangle of g1 is within the minimum bounding rectangle of g2. This tests the opposite relationship as MBRContains().

What exactly does contains and within mean in that context?

These are DE-91M (Wikipedia) terms, and they were standardized with Simple Features

but, Wikipedia probably covers it best, with two definitions,

  a contains b: geometry b lies in a, and the interiors intersect.
  a contains b iff no points of b lie in the exterior of a, and at least one point of the interior of b lies in the interior of a

The within relationship is the inverse of contains, with one catch "polygons do not contain their boundary." Other than that, within(a,b) = contains(b,a). Though interestingly, linestrings do contain all the points along the linestrings, except the terminal points.

For more information see the Wikipedia,

https://en.wikipedia.org/wiki/DE-9IM


Mysql: update query with subquery Hello i need to perform the following query

Query

update Taxonomy
set sourceId = (
select id from TaxonomyMapping a where a.oldId =
    (
        select cm.en_ID
        from TaxonomyMapping ta
        join CategoryMapping cm on ta.oldId = cm.language_ID where ta.oldId = oldId limit 1
    )
) where id > -1;

The tables are as following:

Taxonomy(id, oldId, sourceId, name, language)

TaxonomyMapping(id, oldId, sourceId, name, language) Exact copy of Taxonomy

CategoryMapping(en_ID, language_ID)

What im trying to accomplish
The original taxonomy table has got categories in a certain language there are translations of it, but the need they are known in Category mapping, now i need to add the english translation to the sourceId, Can somebody help me with this? at the moment it fills one id for all im suspecting that the oldId(ta.oldId = oldId) i use is not the oldId of the table to update. Is it possible to do a query like this or should i search for another solution?

The question is a unclear on how the tables are to actually related to each other.

Since the query you've posted is not working, it's difficult to tell which element of the logic contains the error, and with a column called "oldID" it's hard to understand the necessary join logic.

I have attempted to interpret and rewrite your query based on trying to understand it from the innermost query to the outermost query, it seems like this might be what you are trying to accomplish.

My table "ta" is doing the same thing as your table "ta" seems to be trying to do, and my table "a" is doing the same thing your table "a" seems to be trying to do.  They are reversed in their order of appearance in the query because, as I mentioned, if I'm understanding your query correctly, the original query seems to be written inside-out.

UPDATE Taxonomy t
  JOIN TaxonomyMapping ta ON ta.oldID = t.oldID
  JOIN CategoryMapping cm ON cm.language_ID = ta.oldID
  JOIN TaxonomyMapping a ON a.oldID = cm.en_id
   SET t.sourceId = a.id
 WHERE t.id > -1;

However, if your "TaxonomyMapping" and "Taxonomy" tables are an "exact copy" (containing the same data) then table "ta" is not necessary, and all you actually need is this:

UPDATE Taxonomy t
  JOIN CategoryMapping cm ON cm.language_ID = t.oldID
  JOIN TaxonomyMapping a ON a.oldID = cm.en_id
   SET t.sourceId = a.id
 WHERE t.id > -1;

In fact, it seems like you could even do it without a permanent copy.

UPDATE Taxonomy t
  JOIN CategoryMapping cm ON cm.language_ID = t.oldID
  JOIN (SELECT * FROM Taxonomy) x ON x.oldID = cm.en_ID
   SET t.sourceId = x.id
 WHERE t.id > -1;

That last example works because the server materializes a temporary copy of "Taxonomy" called table "x" before executing the rest of the query.  By doing that, it's able to update the table with data selected from elsewhere in the same table, which you normally can't do... but since a copy is technically a different table, it works.  The temporary copy is usually created in memory and then moved to disk if it turns out to be large, but either way, it's automatically deleted as soon as the query is finished.  The down-side is that the new table won't have any indexes so this approach won't infinitely scale... but should still be faster than your original correlated subquery.

Of course, none of these will work if I have failed to understand the necessary logic.


CROSS JOIN with later PIVOT going rather slowly I have an operation that is going rather slowly, probably because my CROSS JOIN first creates a ton of rows for SQL Server to work on, which are later GROUPed and PIVOTed into far fewer rows and a bunch of columns.  The aim is to get the "times each record was downloaded" per "unique organization name", where the unique organization names are the unique OrganizationName entries in the AdamUser table and the times downloaded is the number of associated entries in the MaintenanceJobHistory table.  Lastly, Record is joined onto each row to output more info about that record.  Here's the query:

SELECT *
FROM (
    SELECT
        rec.[Id]                                      AS RecordId,
        CONCAT('Downloads_', usr.[OrganizationName])  AS OrganizationName,
        COUNT(hist.[Id])                              AS TimesDownloaded  -- To be aggregated by PIVOT
    FROM (
        SELECT
            innerRec.[Id]
        FROM
            [dbo].[Record] innerRec
        INNER JOIN
            [dbo].[RecordClassificationLink] innerLnk ON innerLnk.[RecordId] = innerRec.[Id]
        -- WHERE (classification ID is foo or bar), for optional classification filtering
        GROUP BY
            innerRec.[Id]
        -- HAVING COUNT(innerLnk.ClassificationId) = (number of specified classifications), for optional classification filtering
    ) rec
    CROSS JOIN [dbo].[AdamUser] usr
    LEFT JOIN (
        SELECT * FROM [dbo].[MaintenanceJobHistory] WHERE [CreatedOn] > '2016-01-01 12:00:00' AND [CreatedOn] < '2016-12-01 12:00:00'
    ) hist ON hist.[AccessingUser] = usr.[Name] AND hist.[RecordId] = rec.[Id]
    GROUP BY
        rec.[Id], usr.[OrganizationName]
) srcTable
PIVOT  -- Pivot around columns outside aggregation fn, eg. heading column [OrganizationName] & all other columns: [RecordId]
(
    MIN(srcTable.[TimesDownloaded]) FOR [OrganizationName] IN (...~200 columns...)
) pivotTable
INNER JOIN [dbo].[Record] outerRec ON outerRec.[Id] = pivotTable.[RecordId]

And here's the execution plan:

https://www.brentozar.com/pastetheplan/?id=ry8tXM3mg

It does give me the correct output, but I noticed that the Sort operation before the Stream Aggregate is taking a long time, and spills into tempdb.  This may be why the query takes ~5 minutes with the ~200 columns I have for the normal dataset, whereas my test dataset with 6 columns in the PIVOT only takes maybe half a minute.  In terms of how many rows it's dealing with, the two tables being CROSS JOINed (Record and AdamUser) have 38397 and 1017 rows respectively.

Is there a way I can speed this up, or is it necessary to take this long for the number of rows and columns I have?

I didn't find any way to speed up the PIVOT operation - it's just a big operation that takes time with a lot of data.  The one thing I did that seemed to speed things up a small amount (perhaps the addition concatenation operator implemented more efficiently in SQL Server) was to change CONCAT('Downloads_', usr.[OrganizationName]) to 'Downloads_' + usr.[OrganizationName].  The rest seems to be pretty much optimal.


Failover query in RAC Say I have the following tnsnames.ora in my two node RAC database,

DIVTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DIVTEST-scan.DIVCORP.com)(PORT = 1721))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DIVTEST)
)
)

And I create a service as following:

Service_name – DIVTEST
Failover_type - Basic
Failover_method - session
Failover_retries – 180
Failover_delay - 5

and the service in running in both the nodes.

For server side TAF, Should I edit my tnsnames.ora (above mentioned) on my database server as given below?

DIVTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DIVTEST-scan.DIVCORP.com)(PORT = 1721))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DIVTEST)
(FAILOVER_MODE=
(TYPE=session)
(METHOD=basic))))

OR just have the service running in both the nodes will ensure runtime TAF?

Should the service-name mentioned in TNS and the service which I create for TAF using srvctl be the same so that client need not make any changes in tns at their end?

11gR2(11.2) RAC TAF Configuration for Admin and Policy Managed Databases (Doc ID 1312749.1)

  Create Service

As you have already written.

  Start the service

No TNS entry needed on the server.

  Make a connection.

  The client connect string does not require any special TAF parameters.
  A simple tnsnames.ora entry as shown below will do.

mysrvb =
  (description =
   (address = (protocol=tcp)(host=secrac-scan)(port=1521))
   (connect_data=
    (service_name=mysrvb)
   )
  )

Of course, use the service that was defined with TAF.


SQL Agent Job dependency I have a requirement is SQL Agent Jobs where their is a job called Job 4 which dependent on 3 other jobs say Job 1, Job 2 and Job 3. So this Job 4 has to be triggered whenever either one of the 3 jobs i.e. Job 1, Job 2 and Job 3 gets succeeded. Since days I have been breaking my head on this but unable to figure anything out. Can you please help me on this?

Thanks in advance
Manoj

In each of Jobs 1, 2, and 3, add a second step. (Or a step after all of the other steps, if these are already multi-step jobs.)  Have that new step be:

exec sp_start_job 'Job 4'

And because you mention that you only want Job 4 to be started if the other jobs succeed, you should also adjust the Advanced properties of the existing final step in each of Jobs 1, 2, and 3 to be:

On success action: Go to the next step
On failure action: Quit the job reporting failure


How can I find the backup that requires the least amount of archivelogs to restore? Oracle 11.2.0.3 with daily full backup and hourly archivelog backup.  RMAN with a catalog.

If I take a backup at 11pm at night and it finishes 5am the next day and  I want to restore a backup to a different system, how can I identify either a time for 'set until' or an SCN that will require the least amount of archivelogs to be replayed?  11pm? After 5am?  RMAN is my least understood part of the database.

I have a weekly script that automates a duplicate from prod backup.  WHen I inherited it, it did a duplicate until time and was highly dependent on jobs completing on schedule so it could assume a good 'until time'.  I modified it to get a good scn and so duplicate 'until scn'.  I get the scn with this snippet of code querying the rman catalog, in a shell script.  If you are on windows, you'll need to adjust scripting technique, but principle is the same:

    # 2. Get the scn from the most recent archlog backup

#

rcvryscn=$(sqlplus -s rman/pwd@rmcat <<EOF

set echo off feedback off verify off trimsp on head off

col maxscn for 9999999999999999

select max(first_change#) maxscn

from rc_backup_archivelog_details a

join rc_database d on a.db_key=d.db_key

where d.dbid=$dwprdid;

EOF

)


REPLMERG.exe cannot connect to distributor We have a SQL Express 2016 instance that is doing merge replication to a SQL 2016 box. This is an upgraded instance on both sides. We upgraded from SQL Express 2012 to 2016 and we moved the database to a SQL 2016 instance.

Merge replication triggered by a batch file with the following parameters (server, distributor, and subscription info changed to numbers)

REM -- Declare the variables.
SET Publisher=1111111
SET Distributor=2222222
SET Subscriber=3333333\SQLEXPRESS
SET PublicationDB=44444444
SET SubscriptionDB=555555555
SET Publication=666666666

REM --Start the Merge Agent with concurrent upload and download processes.
REM -- The following command must be supplied without line breaks.
"C:\Program Files\Microsoft SQL Server\130\COM\REPLMERG.EXE" -Publication %Publication% -Publisher %Publisher%  -Subscriber  %Subscriber%  -Distributor %Distributor%  -PublisherDB %PublicationDB%  -SubscriberDB %SubscriptionDB% -PublisherSecurityMode 0  -OutputVerboseLevel 2  -SubscriberSecurityMode 1  -SubscriptionType 1 -DistributorSecurityMode 0  -Validate 3  -ParallelUploadDownload 1
pause;
exit

When the batch file is ran, it shows in the replication log file that it cannot connect to the distributor.

ERROR: ErrNo = 0x80045901, ErrSrc = <null>, ErrType = 9, ErrStr = The process could not connect to Distributor '2222222'.

I can manually trigger the replication from my account (I'm a DBA).

Am I just missing a username and password for this to automatically merge when the batch file is called or am I missing something blatantly obvious in my batch file?

REPLMERG.EXE . . . -DistributorSecurityMode 0  . . .

Per the command line parameters for replmerg.exe

-DistributorSecurityMode [ 0| 1]
Specifies the security mode of the Distributor. A value of 0 indicates SQL Server Authentication Mode
(default), and a value of 1 indicates Windows Authentication Mode.

But you did not include
-DistributorLogin distributor_login Is the Distributor login name.
-DistributorPassword distributor_password Is the Distributor password.


MySQL - NOT IN ... vs LEFT JOIN I am trying to understand in terms of performance which approach is better:

Query 1:

SELECT * FROM table1
WHERE col1 NOT IN(SELECT col1 FROM table2)

Query 2:

SELECT * FROM table1
LEFT JOIN table2 ON table1.col1 = table2.col1
WHERE table2.col1 IS NULL

The first query uses DEPENDENT SUBQUERY,The second uses simply index.

EXPLAIN tells you the difference.

If it shows DEPENDENT SUBQUERY it means that the subquery in IN() is executed once-per-row for the table1, that may be really many times if the table is big.

Different MySQL versions may apply different optimizations to the first query to get rid of the DEPENDENT SUBQUERY or at least minimize its performance impact:

use subquery cache - may be good if there are many duplicates in col1 values
change NOT IN into NOT EXISTS
materialize the subquery into a temporary table and (left)join to it
rewrite the query to left-join directly

If none of these is applied by your optimizer, use the second query because MySQL optimizes joins better (more so in older versions). Having a plan with DEPENDENT SUBQUERY in it is not effective in most cases.

IIRC the LEFT JOIN ... IS NULL may be evaluated as NOT EXISTS with specific optimization for that plan.

For both queries you should have an index on table2.col1 to allow fast execution of the subquery or fast detection of (non) existence of the row.


Tuning mysql 5.7.1 for predominantly write workload on huge partitioned innodb tables I recently got a task to tune MySQL for predominantly write workload on multiple innodb tables which are huge and partitioned.

About the application: It's Nimsoft suite, so the SQL queries are not written by developers and comes with the suite.

OS : Centos 7
MySQL Version : 5.7.20 Community server
RAM : 120 GB
cores: 24

Looking at the ouput of cat /sys/block/sda/queue/rotational and lsblk I suspect the hard disks are not SSD but I'm not sure because all I can see is VMWare virtual disk from my OS. So I will cross verify with storage team.

Observations so far:

1) No CPU pressure at all. ~10% of the CPU is used.

2)Looking at the MySQL workload with show processlist, I can see one thread is connected and executing continuously (processID is same and never goes to sleep).

3) Connected thread is only executing SQL

LOAD DATA CONCURRENT LOCAL INFILE 'data'
    INTO TABLE RN_QOS_DATA_098
    FILEDS TERMINATED BY ',' LINES

But I can see the Loading in table happens fast because immediately when I execute show processlist again I can see Loading happens in different table

LOAD DATA CONCURRENT LOCAL INFILE 'data'
    INTO TABLE RN_QOS_DATA_080
    FILEDS TERMINATED BY ',' LINES

Each these tables are InnoDB tables created with hundreds of partitions.

4) I enabled Slow query log to capture any long running queries and I couldn't see any select query appearing in slow log for a long_query_time value of 1s.

5)Looking at the iostat output , I don't see abnormal qsize or waitime for write operations.

6) innodb_buffer_pool is set to 40 GB but the top command shows mysqld is constantly consuming more than 90% memory.

Since it is a suite and not much control we have over the SQL that is executing and also there are no slow running selects to identify and optimize.

What are the settings at the server level/ mysql level can be done in this case to improve the overall performance?

All other info about this MySQL server , I have uploaded in pastebin

Globalvariables
my.cnf
IOSTAT
Globalstatus
Filesystem
ulimit
top
mysqltuner

A1: Almost always CPU pressure implies lack of INDEX or poorly formulated SELECT.

A2: Same pid -- so that connection is doing one LOAD after another without stopping?  That's OK.  One thing to check when you do the SHOW is the Time column -- it is the elapsed time for the current query if running, or the amount of time it has been idle (if it says Sleep).

A3: "...DATA_080" -- You have differently named tables, each with multiple partitions?  Or are you saying that "...DATA_080" is what you mean by "partitioning"?  There is a big difference.

A3: "InnoDB tables created with hundreds of partitions" -- inefficient.  How many such tables?  How many partitions?  What style of partitioning?  Let's see SHOW CREATE TABLE (with only a sampling of the partitions listed).

A4: Are the LOADs in the slowlog?  Crank long_query_time down to 0.3 to see if you catch something else.

A6: 40 GB vs 128GB of RAM.  You could increase the buffer_pool, but it probably won't change things much.  How much does top say that mysqld is using?  (I suspect the 90% is bogus.)

A7: "to improve the overall performance?" -- Wait!  I have not heard what the performance problem is.  So far, it sounds like the only heavy task is lots of LOADs?

Thanks for the attachments!

Observations:

Version: 5.7.20-log
120 GB of RAM
Uptime = 17d 00:34:34
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

You are writing (and flushing) 990 blocks per second.  That is rather high, and implies that you do have SSD drive(s).
You asked about performance; this seems to be the limiting factor, in spite of iostat not pointing it out.
How many rows in a typical LOAD DATA?

The large number of partitions leads to the need to open lots of partitions (which are effectively 'sub-tables').  Let's work on that by discussing the partitioning.\
Meanwhile change some settings:

open_file_limit = 10000
table_open_cache = 5000
table_definition_cache = 3333

Are many of the 4001 tables partitioned?!

Increase both innodb_page_cleaners and innodb_buffer_pool_instances to 16.

If it turns out you are using SSDs, change innodb_flush_neighbors to 0.

innodb_log_file_size is 1G; change to 5G.  However, it is not necessarily easy to do so.  Skip this recommmendation if necessary.

Perhaps increasing bulk_insert_buffer_size to 32M would be beneficial, especially since you are doing a lot of LOAD DATAs.

You mentioned not many SELECTs in the slowlog; what about UPDATEs? --

Com_update = 197 /sec
Com_update_multi = 0.48 /sec

I guess this would explain why SHOW PROCESSLIST does keeps seeing a different table:

Com_load = 21 /sec
Innodb_rows_inserted = 4829 /sec

And, does that mean that the average file being loaded has 230 rows?
And, with only 20 Connections/second, there must be hundreds of LOADs in a single connection.

What's in the Stored Routines.

Several ALTER TABLEs per minute (on average) -- What's up?

Along with SHOW CREATE TABLE, please provideSHOW TABLE STATUS` for at least one of the many tables.  I want more details before judging these:

  OPTIMIZE TABLE `ca_uim`.`rn_qos_data_0028`; -- can free 82 MB
  OPTIMIZE TABLE `ca_uim`.`rn_qos_data_0029`; -- can free 82 MB
  OPTIMIZE TABLE `ca_uim`.`rn_qos_data_0035`; -- can free 112 MB
  (etc)

If you raise innodb_buffer_pool_size, don't increase it to more than 90G.

Details and other observations:

( Innodb_buffer_pool_pages_flushed ) = 1,456,312,527 / 1470874 = 990 /sec -- Writes (flushes)
-- check innodb_buffer_pool_size

( innodb_buffer_pool_size / _ram ) = 40960M / 122880M = 33.3% -- % of RAM used for InnoDB buffer_pool

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram ) = (8M / 0.20 + 40960M / 0.70) / 122880M = 47.7% -- Most of available ram should be made available for caching.
-- http://mysql.rjweb.org/doc.php/memory

( Opened_tables ) = 44,353,045 / 1470874 = 30 /sec -- Frequency of opening Tables
-- increase table_open_cache

( Opened_table_definitions ) = 26,430,658 / 1470874 = 18 /sec -- Frequency of opening .frm files
-- Increase table_definition_cache and/or table_open_cache.

( Table_open_cache_overflows ) = 43,864,852 / 1470874 = 30 /sec
-- May need to increase table_open_cache

( Table_open_cache_misses ) = 44,353,021 / 1470874 = 30 /sec
-- May need to increase table_open_cache

( innodb_buffer_pool_size / innodb_buffer_pool_instances ) = 40960M / 8 = 5120MB -- Size of each buffer_pool instance.
-- An instance should be at least 1GB. In very large RAM, have 16 instances.

( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 8 = 0.5 -- page_cleaners
-- Recommend setting innodb_page_cleaners to innodb_buffer_pool_instances

( innodb_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((8736963 + 1456312527) ) / 1470874 = 996 /sec -- InnoDB I/O
-- Increase innodb_buffer_pool_size?

( Innodb_os_log_written ) = 2,138,679,386,624 / 1470874 = 1454019 /sec -- This is an indicator of how busy InnoDB is.
-- Very idle or very busy InnoDB.

( Innodb_log_writes ) = 151,318,300 / 1470874 = 102 /sec

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1,470,874 / 60 * 1024M / 2138679386624 = 12.3 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size. (Cannot change in AWS.)

( Innodb_dblwr_writes ) = 27,805,868 / 1470874 = 19 /sec -- "Doublewrite buffer" writes to disk. "Doublewrites" are a reliability feature. Some newer versions / configurations don't need them.
-- (Symptom of other issues)

( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk.
-- Use 0 for SSD drives; 1 for HDD.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( local_infile ) = local_infile = ON
-- local_infile = ON is a potential security issue

( bulk_insert_buffer_size / _ram ) = 8M / 122880M = 0.01% -- Buffer for multi-row INSERTs and LOAD DATA
-- Too big could threaten RAM size. Too small could hinder such operations.

( (Queries-Questions)/Queries ) = (1084892343-158114534)/1084892343 = 85.4% -- Fraction of queries that are inside Stored Routines.
-- (Not bad if high; but it impacts the validity of some other conclusions.)

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (39274680 + 290107639 + 1191197 + 0) / 25 = 1.32e+7 -- Statements per Commit (assuming all InnoDB)
-- Low: Might help to group queries together in transactions; High: long transactions strain various things.

( Select_scan ) = 2,955,230 / 1470874 = 2 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( sort_buffer_size ) = 24M -- One per thread, malloced at full size until 5.6.4, so keep low; after that bigger is ok.
-- This may be eating into available RAM; recommend no more than 2M.

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (39274680 + 1191197 + 0 + 0 + 290107639 + 710124) / 1470874 = 225 /sec -- writes/sec
-- 50 writes/sec + log flushes will probably max out I/O write capacity of normal drives

( Com_alter_table ) = 121,112 / 1470874 = 0.082 /sec
-- Why so many ALTERs?

( binlog_format ) = binlog_format = MIXED -- STATEMENT/ROW/MIXED. ROW is preferred; it may become the default.

( slave_pending_jobs_size_max / max_allowed_packet ) = 16M / 4M = 4 -- For parallel slave threads
-- slave_pending_jobs_size_max must not be less than max_allowed_packet

( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)

( back_log / max_connections ) = 80 / 151 = 53.0%

You have the Query Cache half-off. You should set both query_cache_type = OFF and query_cache_size = 0 . There is (according to a rumor) a 'bug' in the QC code that leaves some code on unless you turn off both of those settings.

Abnormally small:

(Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) = 0.32
slave_net_timeout = 60

Abnormally large:

Binlog_cache_use = 105 /sec
Binlog_stmt_cache_use = 0.32 /sec
Com_create_trigger = 0.0073 /HR
Com_create_view = 0.015 /HR
Com_dealloc_sql = 0.082 /sec
Com_drop_trigger = 0.0073 /HR
Com_drop_view = 0.015 /HR
Com_execute_sql = 0.082 /sec
Com_insert_select = 0.51 /sec
Com_insert_select + Com_replace_select = 0.51 /sec
Com_load = 21 /sec
Com_prepare_sql = 0.082 /sec
Com_show_engine_mutex = 0.0049 /HR
Com_truncate = 0.24 /sec
Com_update = 197 /sec
Com_update_multi = 0.48 /sec
Handler_delete = 63 /sec
Handler_update = 2396 /sec
Innodb_buffer_pool_bytes_dirty = 1,866.6MB
Innodb_buffer_pool_pages_data = 2.59e+6
Innodb_buffer_pool_pages_dirty = 119,461
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 1.34
Innodb_buffer_pool_pages_total = 2.62e+6
Innodb_data_fsyncs = 211 /sec
Innodb_data_writes = 1127 /sec
Innodb_data_written = 33895601 /sec
Innodb_dblwr_pages_written = 989 /sec
Innodb_log_write_requests = 2631 /sec
Innodb_os_log_fsyncs = 103 /sec
Innodb_os_log_pending_fsyncs = 1
Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group = 2,496.0MB
Innodb_pages_created = 40 /sec
Innodb_pages_read + Innodb_pages_written = 1.48e+9
Innodb_pages_written = 990 /sec
Innodb_rows_deleted = 63 /sec
Innodb_rows_deleted + Innodb_rows_inserted = 4891 /sec
Innodb_rows_inserted = 4829 /sec
Innodb_rows_updated = 2396 /sec
Performance_schema_rwlock_instances_lost = 2.04e+10
Qcache_free_memory = 256.0MB
innodb_io_capacity_max = 6,000
join_buffer_size = 64MB
max_tmp_tables = 64
performance_schema_max_file_classes = 80
performance_schema_max_mutex_classes = 210

Abnormal strings:

bind_address = 0.0.0.0
have_ssl = YES
have_symlink = DISABLED
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_fast_shutdown = 1
innodb_large_prefix = ON
innodb_undo_directory = ./
log_bin_trust_function_creators = ON
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN

Schema Critique

decimal(28,2) occupies 13 bytes.  Consider whether some other datatype will be sufficiently precise for your application, yet save a lot of space:  BIGINT and DOUBLE each take only 8 bytes hold 16-17 significant digits; no decimal places for the former, floating decimal for the latter.
Does tz_offset need a 4-byte integer?  Shrink it, too.
What is the purpose of the PARTITIONing?  If performance, then show us the query that depends on partitioning.  (I may shoot down any performance benefit.)  If it is purging "old" data, then I strongly recommend switching to weekly partitions; this will save disk space and not hurt anything else, plus avoid some of the criticisms I had when looking at the STATUS.  You would not have to change the existing partitions, rather make weekly partitions going forward, thereby gradually improving things without having to take a big downtime to make the change.
Do not pre-build the partitions; do it "just-in-time".  But also have a "future" partition to collect anything that accidentally lands in the table before building tomorrow's partition.  More discussion in my blog .
When you don't explicitly specify a PRIMARY KEY, InnoDB creates a hidden 6-byte PK.  Please show me the main queries, so I can help devise a better set of keys, possibly avoiding that PK.  If necessary, I may recommend a MEDIUMINT UNSIGNED AUTO_INCREMENT (3 bytes, 16M max); this would easily allow 669,536 rows (as per `SHOW TABLE STATUS).

Crude estimates of space (MB per table) after implementing suggestions:

       current  proposed
Data      76       50
Index     95       60?
Free      70       10
         ---      ---
TOTAL    241      120

Shrinking the tables in half may cut the 990 writes/second in half.  (There are several unknowns, especially with indexing.)

Since Data_free for the ~100 partitions is about equal to the predicted savings due to OPTIMIZE TABLE, I expect that OPTIMIZE won't really save anything.  Each partition is always left with 4-7MB of "free" space.


SQL server log shipping - dry-run data modification script on secondary then resume log shipping Am log shipping a large, busy DB from server A (primary) to server B (secondary).

Nightly backups are taken, as well as diffs every X hours, and transaction logs are shipped and restored every 15 mins.

Occasionally I need to dry-run a script on server B, to assess it's impact, before running on server B.  Script is often DDL.

I do this by taking server B out of RESTORING mode by manually executing a restore command on a recent transaction log, and testing the script.

However, to then resume log shipping, I have to restore the most recent full db, and diff, before log shipping can resume.  This is very time consuming.

On server B, is there any way to instead effectively rollback to a previous transaction log (before I tried the script), and then resume log shipping?

Many thanks

On server B, is there any way to instead effectively rollback to a
  previous transaction log (before I tried the script), and then resume
  log shipping?

No there is no way to do this.  You can only bring a log shipped secondary to standby mode.  Which allow read_only operations.  This is done by keeping an undo file.  When you need to restore log files again contents from the undo files are applied first.

More details about undo file here:

In SQL server, How to see the standby file path when we restore the database in standby mode by Harsh Chawla.

For testing DDL you need to bring database to read_write mode which will undo uncommitted transactions without keeping a log of those pages. There is no way to revert to previous state as you can do in case of standby.


Storing large image files in database vs system files I need to store many large photos that will be uploaded daily in my application and retrieve those with id. There is no need to any special queries.
What is the best approach for storing this files consider to time that number of the images pass a million and side of images exceed the server disk size. What is the most manageable solution?

Using SQL FileStream or FileTable and storing files in separate database.
Using file system and saving images as files.
Using a FTP server.

Second option is much better that first for sure as for first option, there will be conversion of images to binary while saving and decoding the same while retiriving which is overhead.

So i would suggest store location of file in database and store images in file system.


Azure SQL - Server login vs. database login In my Azure SQL server, I see the SQL login that allows me to connect to the server. However, when I look at the Users defined for my database on that server, I don't see a user account with the same name (which I would expect). How am I able to access my database (which I am able to do), without a database user account?

Your login is mapped to different database user, most probably to dbo . You can check this by executing:

SELECT CURRENT_USER

You can also compare the output from following views (and find matching SIDs to figure out login to user mapping):

SELECT *  FROM sys.sql_logins -- you need to execute this on master azure db

SELECT * FROM sys.database_principals -- you need to execute this on the database of interest


SQL Server Merge Replication Computed Column Conflict Resolution I'm setting up a merge replication web publication for a table that needs to use the DateTime (Later Wins) conflict resolver so that the most recently modified row wins in any conflict. This table (and hundreds of other tables) use a datetimeoffset for their modified date as subscribers can be in many timezones. In order to get the required datetime value as a column I've added a ModifiedDateUTC datetime computed column which does a simple CONVERT to a datetime. The issue I'm seeing is that even though the article setup accepts this as a valid column, whenever I generate a conflict I get the error 'The specified conflict resolution column 'ModifiedDateUTC' could not be found.'

The problem is that replicate dcolumn drop from replicating. Then replicatinon of object (views, store procedures and function) fail in creating subscription. Then this column should e added manualy to table. Then after part wwich create object you should remove calculated column becaus ebcp fail with field to big error. Then after removal bcp pass.
But you don't have calculate column on subscriber. But now you can do that only through publisher. Then you should drop calculate column (but this is problem if you have FK otr index on it because you shoul drop them to). Then recreate calculated column (and recreate FK and index (index manualy on subscribers too). This is not normal.
Calculated column (persisted) should be replicated by definition (SQL staements) always in creation of snapshot and only not field with bcp and realy replicated data.


Send a trigger to an application upon a specific SQL Server server usage pattern I've been dealing with a vendor's unconfigurable application which is, unfortunately, the most feature-complete in its niche. I'm looking to augment its capabilities with on-top screen display. Its data access is extremely predictable allows me to infer reliably from query patterns what I need to know to provide a user with additional relevant information pertaining to their current view within the application. Specifically, it always executes TSQL statement (which is the easily identifiable part) with a named parameter (which is the part that defines what the user is viewing) that I'd very much like to get at.

So, in short, I'm looking to create a mechanism that:
 - Hook the query pattern (trivially expressible via wildcards)
 - Extract the named parameter value
 - Sends a message to the (remote) overlay application with the parameter value

Does SQL server have any facilities for any portion of this undertaking? Is there a better way I should be going about this? (Possibly lower down in the network stack.)

Edit: We're using SQL Server 2005, but upgrading to SQL Server 2008/2008R2 isn't out of the question.

There's a few gotchas in here: you've said that the queries will be short-lived, that you're going to have (relatively) high volume, that you're going to capture parts of the queries, and that you want to communicate back to the app tier.  This means you want a non-blocking, high-performance solution, and there's nothing built-in that will make this easy.  I'm going to just talk theoretically about something you could build, but it ain't gonna be easy.

Part 1: capturing the query data as it happens.  For this, SQL Server's Extended Events are probably the way to go.  Think of it as debug points in the SQL Server engine code where you can insert your own code.  As you learn about it, you'll find that you can gather all kinds of cool information about queries, but keep in mind that this is blocking code, and the more information you want to gather, the slower your queries go.  You won't be able to push data outside of SQL Server in this part - you're going to have to either write the data to an XML file, or put it in the ring buffer.  The ring buffer makes the most sense here since you'll need to query it in near-real-time.

Part 2: getting the data from the SQL Server's ring buffer to the client.  You could build something to push the data from SQL Server to the overlay tier, but this screams danger to me.  It couldn't be single-threaded, because you could hang on trying to send a notification to a client that's running slow, and miss everybody else's notifications.  Instead, what I think you want to do is have the overlay app detect on the client side when it needs to check the ring buffer, and then do that via a query.  You don't want all the clients continuously polling the ring buffer - performance nightmare there.


Azure SQL Server outdated version - how can I upgrade it to current version? I've connected to couple of Azure SQL Servers (those are hosting few Azure SQL Databases on them) in SSMS Object Explorer and see that version is 12.0.2000.8
select @@Version shows below:

Microsoft SQL Azure (RTM) - 12.0.2000.8   Feb 20 2021 17:51:58   Copyright (C) 2019 Microsoft Corporation

For comparison, my other box is SQL Server 2017 and version it shows is 14.0.3370.1
I expected to see Azure SQL Servers as 15.0.something  (correspond to SQL Server 2019), but they seem to correspond to SQL Server 2014 (version 12)
Questions:
Weren't Azure SQL Servers supposed to upgrade automatically to latest version ?
How can I upgrade them to version 15 manually ?

Azure SQL Database is automatically updated, but it uses a different version numbering scheme from “box” SQL Server.  It went from “11” to “12” several years ago during a major breaking change, and will stay on “12” indefinitely.
It always has the latest features and fixes for the SQL Server engine and is ahead of SQL Server 2019.  By the time a new version of SQL Server is released, the code has been running in production in Azure SQL Database for a long time.
You control the database compatibility level of your databases to opt-in to breaking changes and query optimizer upgrades.  The database compatibility levels map to the major releases of SQL Server.  Over time the default compatibility level for new Azure SQL Databases increases up as new compatibility levels are introduced.


Why when I JOIN to a casted field in an index view, the execution plan warns about "type conversion in expression"? I thought an indexed view was materialized and therefor stored on disk. If that's the case, I would've thought that any type conversions I'm doing in the view would materialize as that datatype on disk and I wouldn't have to worry about casting issues while using those fields in JOIN and WHERE predicates against the view.

Why is this not so?

An indexed view is expanded during query compilation unless the NOEXPAND table hint is used. The warning is generated on that expansion. In Enterprise Edition, the optimizer may later match the expansion back to the materialized view. This is a cost-based decision.

In anything other than Enterprise Edition (or equivalent) you must use a NOEXPAND hint to use the index(es) on a materialized view.

In Enterprise Edition, there are still good reasons to use the NOEXPAND hint:

Indexed Views and Statistics
Another Reason to Use NOEXPAND hints in Enterprise Edition


Pivot table with MySQL I'm trying to create a pivot table like output (cross table? Not entirely sure about the difference). The idea is to have rows, columns and summary values (specifically: distinct count).
The data looks like this:

Transactions table:

product_id
seller_id
seller_country
buyer_id

buyers table

buyer_id
buyer_country

There could be multiple rows for the same product, and the same buyer can buy from multiple sellers multiple times.

Buyer table has unique rows for each buyer, so while the same buyer can appear on multiple rows in the transactions table, he'll appear just on one row in the buyers table.

What I want to create is a matrix where the rows are for sellers' countries, columns for buyers' countries, and the values in the table count the number of unique buyers from each country who bought from each country. Count the distinct buyers each cell.

I tried several things but ultimately couldn't get past a row group by. Searched online for solutions but nothing was relevant for this very problem.
This is what I managed to do with MS access:

TRANSFORM Count(Dummy_users.buyer_id) AS CountOfbuyer_id
SELECT Dummy_transactions.seller_country
FROM Dummy_users LEFT OUTER JOIN Dummy_transactions ON Dummy_users.buyer_id = Dummy_transactions.buyer_id
GROUP BY Dummy_transactions.seller_country
PIVOT Dummy_users.buyer_country;

but I don't see PIVOT is acceptable anywhere in mysql (no search results which offer that solution), and also for some reason it broke when I tried to add the distinct element to the count.

Can you help me with the syntax please?

Here is some dummy data if needed:
transactions: https://docs.google.com/spreadsheets/d/1vHzjmSkaQ5G5lTdV_M6h7gp8zR4Kfqz70gL4TeID2jQ/
buyers: https://docs.google.com/spreadsheets/d/18HXZ_GotqEMDhxytYKxNeD4W6-PfqUvkcWh3DBuCYPA/

Many thanks!

Give this a try,tested on my machine

First make sure GROUP_CONCAT has plenty of space:

SET SESSION group_concat_max_len = 1000000;

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
    CONCAT(
      'COUNT(DISTINCT CASE WHEN buyer_country= ''',
      buyer_country,
      ''' THEN transactions.buyer_id END) AS ',
      buyer_country
    )
   )INTO @sql
FROM  transactions
LEFT JOIN buyers on transactions.buyer_id=buyers.buyer_id;

SET @sql=CONCAT('SELECT transactions.seller_country,',@sql,' FROM  transactions
LEFT JOIN buyers on transactions.buyer_id=buyers.buyer_id GROUP BY transactions.seller_country');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Link

Edit

ALTER TABLE transactions ADD key trans_idx (buyer_id)
ALTER TABLE buyers ADD key buyers_idx (buyer_id)


One big table vs few smaller Please take note that the example below is just an example, my scenario is way more complex and the way i'm trying to model it really makes sense

Let's say i'm creating a table for audit events in one of my apps - so all of the "event_created", "user_created" etc. kind of thing. The table contains several columns, some of them are a foreign keys to other tables. Over time, this single table can grow to several milion of records.

From performance perspective, is it faster and more performant to use a single table for all of them or to use separate table for each kind of event and operate on separate tables? Or it doesn't make much differrence? It might sound silly to create a separate table for each kind of event but you need to trust me that in my real world scenario, it really makes sense.

Denormalize only as a last resort

Do not denormalize your table design for imaginary performance problems. Avoid falling into premature optimization.

Design a proper structure. Generate fake data to populate the tables. Run tests under circumstances akin to your deployment scenario. If a significant performance problem is proven:

Research the nature of the problem using the EXPLAIN & ANALYZE features.

Examine use of indexes. Verify that existing indexes were used in queries as expected; if not, re-write your query with a different approach. Add an index where needed.

Study how to tune Postgres.

Learn to size caches.
Learn other ways to tune your Postgres server.
Review some conference talks, with online video or slides.
Search the official Postgres mailing lists.
Attend a Postgres user group meeting, or a Postgres conference.
Search DBA Stack Exchange.

Trying moving some logic from your app to the database server by writing functions in a language such as PL/pgSQL, where all the data is local to the executing code.
Install larger cache unit on your RAID device. Consider tuning the cache to allocate more to reading vs writing, as appropriate to your situation; they often default to 50-50.(By the way, be certain your RAID’s write-cache is battery-backed to avoid ruining your database or other files.)

Or, if using ZFS instead of RAID, learn to tune that to prioritize your database on faster drives.

Consider the use of partitioning to physically segregate rows in storage. Consider adding faster storage such as enterprise-quality solid-state storage on which you store your most frequented data, or store the data you most want to access quickly such as data used by the Big Cheese.

Partitioning comes with restrictions and trade-offs. So be sure you have no better way to solve your performance problem.
If you do go with partitioning, be aware that recent versions of Postgres (10, 11, 12 as I vaguely recall) have been dramatically improved with improvements with declarative partitioning.

Hire a Postgres expert for consultation in your testing and tuning.

Only after having exhausted all avenues to fixing a proven performance problem should you consider denormalizing.

Postgres is a powerful enterprise-quality database system. Several million rows on modern hardware with sufficient RAM and wise indexing should be no problem at all.

On the other hand, if your different types of events represent different entities, then they should be kept in separate tables. How do we know if similar kinds of rows are different entities or not? Clues might be found in asking: Do they have mostly the same columns with the same semantics? Do your users ever want to display or report together? Might you ever want to aggregate (calculate count, average, median, etc) together?

Be aware that as a product with a long history dating back to the days when computer hardware was much more limited in capabilities and configurations that today’s hardware, Postgres by default has quite conservative settings on initial installation. For example, by default Postgres runs on an older Raspberry Pi! So anyone running a larger database on more capable hardware should be doing some tuning.


Intervals query of huge data My question is more or less theoretical - what is the best approch to get query data by intervals.

The situation

I have a system that manage counters (thousands of counters - counts consumption of electricity/water etc...), every 15 minutes the counter is sampled and stored in the DB. there is one table that stores all counters data [id, counterID, Timestamp, CounterValue].

Now, I want to query the data about the Interval (hourly/daily/weekly/monthley) consumption.

The intervaled consumption defined as the first measurment (counting) in the start of the next interval minus the first measurment in the start of the current interval. since all measurments are agregated (counters) the delta between two counters represents the consumption in the interval.

Since there is ALOT of data and I want the queries to be fast I need a better way then just quering the whole table... (I think...)
quering the whole data (even with small amount of data - this query is slow...):

SELECT *, dateadd(hour,datediff(hour,0, dateOfData),0) as roundedTime
FROM (  SELECT *, ROW_NUMBER() OVER(PARTITION BY  counterId,datediff(hour, 0, dateOfData) ORDER BY dateOfData asc) row_number
        FROM counters_data) A
WHERE row_number = 1
Order by dateOfData;

I thought about some ways doing this:

Add [isHourly,isDaily,isWeekly...] boolean columns in the measurments table and when I store the data I'll update those flags.
Add tables for every interval with columns [id, date, counterId, counterDataRecord] so there will be tblDailyCounter, tblWeeklyCounter etc...

is there any other idea that Im missing?

what is the pros&cons of those aproches?

what is the most recomanded / best practice?

Well this answer depends on how your database is designed and licensed.

If you have an Enterprise license, you should take a look at partitioning. This way you may partition your data which will help you in querying those table. If you make a good partitioning, you'll be able to just read a small portion of the table from the disc, instead of querying everything.

I don't know how your application writes to your database. But maybe it's possible to flag the first record of an hour so that you don't need to filter it later on that way?

Another idea is a SQL Agent, which will perform those aggregations in the background for you. Maybe this could be hourly and write all aggregated values to another cumulative table, which could be queried by you even faster.


Character Strings related : string_units db cfg changed from SYSTEM to CODEUNITS32 To handle few special characters, we changed string_units database configuration parameter from default SYSTEM to CODEUNITS32.

Based on IBM DB2 Knowledge Center, there are three methods to achieve this.

1.Set the NLS_STRING_UNITS global variable

2.Update the string_units database configuration parameter

Alter the column by defining new character set ( CODEUNITS16 or CODEUNITS32)

We have used the second option and restarted the instance. Still it's not working as expected. Have we missed any steps?

Is the reorg required on the table? What happens to the already existing data ?

db2level - 10.5 fp5
OS : Windows

ALTER TABLE <schema>.<tabname> ALTER COLUMN <colname> SET DATA TYPE VARCHAR(20 CODEUNITS32);

The alter column set data type with CODEUNITS32 clause did the job.
If the string_units database configuration parameter is set to CODEUNITS32,
any new table without specifying this clause in column definition still works.

We have to perform the above alter only to the columns of existing table which was defined when string_units was set to default SYSTEM.

Hope it helps to other users.


Get time from Unix milliseconds timestamp My MySQL version is 5.6; it has a FROM_UNIXTIME function. But it works for timestamps with 10 digits: 1447430881.

mysql> SELECT FROM_UNIXTIME(1447430881);
        -> '2015-11-13 10:08:01'

Now I want to get the date from a milliseconds timestamp (e.g. 1556948248000), so using FROM_UNIXTIME I got NULL.

mysql> SELECT FROM_UNIXTIME(1556948248000);
        -> NULL

Isn't there a FROM_UNIXMILLITIME function?

Universal solution (for datetime >= '2001-09-09 04:46:40' - see comments).

SELECT FROM_UNIXTIME(@time * POWER(10, 9 - FLOOR(LOG10(@time))));

@time variable contains unix timestamp (any accuracy). For example, when it is 1556948248123, it is normalized to 1556948248.123 by the FROM_UNIXTIME's argument expression, and the result will be 2015-11-13 19:08:01.123000. The same result will be obtained when the variable is 1556948248.123 or, for example, 1.556948248123.

This variable is a placeholder - it can be fieldname or number literal.

You may create your own UDF if needed:

CREATE FUNCTION my_from_unixtime(arg DECIMAL(65, 30))
RETURNS DATETIME(6)
DETERMINISTIC
NO SQL
RETURN FROM_UNIXTIME(arg * POWER(10, 9 - FLOOR(LOG10(arg))));


SELECT on column value = X but if no rows available then any column value In MSSQL 2016 enterprise edition I have a table column which can contain an int. I'd like to query for 1000 rows where this column equal X, but if no rows then column may contain any value.

Eg:

SELECT TOP 1000 COLS FROM TABLE WHERE  <COL = 4 OR IF NO ROWS THEN COL = ANY VALUE>

How do I write the logic within < and >

Obtain non-'col=X' only if 'col=x' not exists:

SELECT TOP 1000 *
FROM table
WHERE col = CASE WHEN EXISTS (SELECT 1
                              FROM table
                              WHERE col = X)
                 THEN X
                 ELSE col
            END

Obtain 1000 records, 'col=X' first:

SELECT TOP 1000 *
FROM table
ORDER BY CASE WHEN col = X
              THEN 0
              ELSE 1 END


LOB Segment is listed in DBA_SEGMENTS, but has not corresponding row in DBA_LOBS I am trying to find the column (or table) that belongs to a rather big LOB segment:

select segment_name, segment_type, bytes
from dba_segments
where segment_name = 'SYS_LOB0000103936C00014$$';

returns:

SEGMENT_NAME              | SEGMENT_TYPE | BYTES
--------------------------+--------------+-------------
SYS_LOB0000103936C00014$$ | LOBSEGMENT   | 422877069312

However when I try to find the corresponding table:

select table_name, column_name, segment_name, tablespace_name, index_name
from dba_lobs
where segment_name = 'SYS_LOB0000103936C00014$$';

No rows are returned.

Is there any other place where the information about that LOB segment is stored?

This is an Oracle 11.2.0.4 (RAC) running RHEL 6.8

One possible reason is setting a LOB column unusable:

SQL> create table t1 (c1 number, c2 clob);

Table created.

SQL> insert into t1 values (1, 'A');

1 row created.

SQL> commit;

Commit complete.

SQL> select segment_name from dba_lobs where table_name = 'T1' and column_name = 'C2';

SEGMENT_NAME
------------------------------
SYS_LOB0000015673C00002$$

SQL> select segment_name from dba_segments where segment_name = 'SYS_LOB0000015673C00002$$';

SEGMENT_NAME
--------------------------------------------------------------------------------
SYS_LOB0000015673C00002$$

SQL> alter table t1 set unused column c2;

Table altered.

SQL> select  table_name, column_name from dba_lobs where segment_name = 'SYS_LOB0000015673C00002$$';

no rows selected

SQL> select segment_name from dba_segments where segment_name = 'SYS_LOB0000015673C00002$$';

SEGMENT_NAME
--------------------------------------------------------------------------------
SYS_LOB0000015673C00002$$

To find the table the orphaned LOB column belongs to:

select u.name, o.name TABLENAME, decode(bitand(c.property, 1), 1, ac.name, c.name) as column_name
from sys.obj$ o, sys.col$ c, sys.attrcol$ ac,sys.lob$ l,sys.obj$ lo,sys.obj$ io,
sys.user$ u,sys.ts$ ts
where o.owner# = u.user#
and o.obj# = c.obj#
and c.obj# = l.obj# and c.intcol# = l.intcol#
and l.lobj# = lo.obj# and l.ind# = io.obj# and l.ts# = ts.ts# and c.obj# =
ac.obj#(+)
and c.intcol# = ac.intcol#(+) and lo.name ='SYS_LOB0000015673C00002$$';

NAME                           TABLENAME                      COLUMN_NAME
------------------------------ ------------------------------ --------------------------------
BP                             T1                             SYS_C00002_16120712:10:58$

To remove the column and LOB segment:

SQL> alter table t1 drop unused columns;

Table altered.

SQL> select segment_name from dba_segments where segment_name = 'SYS_LOB0000015673C00002$$';

no rows selected

Based on: Orphaned Lobs after marking LOB column unused (Doc ID 461651.1)


Oracle Control and Webserver How does the Database Control work, since Apache is not running on this computer? The Url says https://localhost:1158/em/console/logon/logon. I do have a WAMP apache server on here but I have to shut it down to get the Oracle Control to work , other wise I get a 404.
Thanks

As previously mentioned by @Jack Douglas, Oracle Application Server (a J2EE container) is installed. You may want to do a tcpdump to see what applications are running on conflicting ports. You can find a list of ports used by Database Control here: Managing Oracle Port Numbers.

Some ports used by Enterprise Manager Database Control:

1158 - Http Port for Oracle Enterprise Manager Console.
3938 - Oracle Management Agent.
5520 - RMI port for Enterprise Manager Console.
5540 - JMS Port for Enterprise Manager Console.


JOINS - optimize cost of LIKE/OR criteria The Active table has the Account Name in 1 column and the Master table has the Account Name in any one of 6 separate address columns and I'm trying to match them.  Also, they don't necessarily exactly match, so I'm using the LIKE operator.

The Active table is about 250,000 records and the Master table is about 1,500,000 total records, but the account range specified in the WHERE clause is about 5,000 records.

The query takes a long time, so I assume I'm doing something dumb and so I'm trying to figure out a way to optimize it.  The COALESCE function was a late addition added out of desperation, so I'm not sure I'm even using it appropriately.  Originally everything was broken out separately with OR operators. Every field involved is nvarchar.

SELECT
Active.AcctNbr, Active.Name, Master.Addr1, Master.Addr2, Master.Addr3, Master.AcctNbr

FROM Active

INNER JOIN Master ON
Active.Name LIKE COALESCE (('%' + Master.Addr1 + '%'),('%' + Master.Addr2 + '%'),('%' + Master.Addr3 + '%'))

WHERE LEFT(Master.AcctNbr,3) = '123'

There are several factors that are contributing to your slow performance.
Using the like with the wild card on both sides of the text is going to cause the query to scan all of the rows in both tables multiple times to match your join predicate because the expression is not SARGable.  The LEFT operator in your where statement is also going to force your query to run in a single thread because again it's going to have to scan all of the rows in the master table to determine which rows will have the first three characters equal to 123.

There are a couple things you can try to get a little better performance out of the query.

The first would be to change your where statement from LEFT(Master.AcctNbr,3) = '123' to Master.AcctNbr like '123%'.  This will make the expression SARGable and should give you a seek on the master table assuming that you have the column AcctNbr indexed.

The second would be, building off the first change, to break the query out into three parts and union the results together.  This may not make a huge difference however it could help the query processor by technically providing it three independent queries and the query processor might decide to run the query in parallel.  (Going parallel might only happen if the query plan meets a certain threshold of cost based on statistics and the complexity of the statement along with the settings of SQL Server.)

   SELECT
#Active.AcctNbr, #Active.Name, #Master.Addr1, #Master.Addr2, #Master.Addr3, #Master.AcctNbr
FROM #Active
INNER JOIN #Master ON
#Active.Name LIKE ('%' + #Master.Addr1 + '%')
WHERE #Master.AcctNbr like '123%'
UNION
SELECT
#Active.AcctNbr, #Active.Name, #Master.Addr1, #Master.Addr2, #Master.Addr3, #Master.AcctNbr
FROM #Active
INNER JOIN #Master ON
#Active.Name LIKE ('%' + #Master.Addr2 + '%')
WHERE #Master.AcctNbr like '123%'
UNION
SELECT
#Active.AcctNbr, #Active.Name, #Master.Addr1, #Master.Addr2, #Master.Addr3, #Master.AcctNbr
FROM #Active
INNER JOIN #Master ON
#Active.Name LIKE ('%' + #Master.Addr3 + '%')
WHERE #Master.AcctNbr like '123%'

You might also have some hardware constraints depending on the # of CPU cores, amount of memory available and the speed of the disks your temp database is on.


P2P message system design Database noob here, just curious to see if my design could benefit from any improvements.

My goal was to keep this as simple as possible: a user can create an account, two accounts can message each other.

That's it.

To achieve this, I've use the following schema:

CREATE TABLE account (
    id         INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    email      VARCHAR(255) NOT NULL,
    first_name VARCHAR(255) NOT NULL,
    last_name  VARCHAR(255) NOT NULL,
    --
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    --
    PRIMARY KEY (id),
    UNIQUE (email)
);

CREATE TABLE message (
    id      INT UNSIGNED NOT NULL AUTO_INCREMENT,
    to_id   INT(10) UNSIGNED NOT NULL,
    from_id INT(10) UNSIGNED NOT NULL,
    body    VARCHAR(500) NOT NULL,
    seen    BOOLEAN NOT NULL DEFAULT 0,
    --
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    --
    PRIMARY KEY (id)
);

Using this schema, I want to be able to run two queries:

A snapshot. I want the message overview, similar to how Facebook does it; your most recent messages in each thread are listed.
A thread. I want to show the thread of messages between two users in a list.

I achieved a paginated list of #1 with the following query:

SELECT message.*, `from`.*, `to`.*
FROM message
LEFT JOIN account AS `to`
    ON `to`.id = message.to_id
LEFT JOIN account AS `from`
    ON `from`.id = message.from_id
WHERE message.id IN(
    SELECT MAX(m.id)
    FROM message m
    WHERE m.id < 999999999
        AND m.to_id = 1
        AND m.deleted_at IS NULL
    GROUP BY GREATEST(m.to_id, m.from_id), LEAST(m.to_id, m.from_id)
)
ORDER BY message.seen, message.id DESC
LIMIT 100

The query above for user with ID 1 will show me a snapshot of latest messages received from every user.

I achieved a paginated list of #2 with the following query:

SELECT message.*, `from`.*, `to`.*
FROM message
LEFT JOIN account AS `to`
    ON `to`.id = message.to_id
LEFT JOIN account AS `from`
    ON `from`.id = message.from_id
WHERE message.id < 999999999
    AND (message.to_id = 1 AND message.from_id = 2) OR (message.to_id = 2 AND message.from_id = 1)
    AND message.deleted_at IS NULL
LIMIT 100

The query above will give me all messages between user with ID 1 and user with ID 2.

Any thoughts? I'd love to hear of some obvious areas of improvement, and some possible explanation as to why so I can learn from it.

Cheers!

deleted and seen get in the way of optimizations
GROUP BY GREATEST(m.to_id, m.from_id), LEAST(m.to_id, m.from_id) seems strange.  Wouldn't this suffice:  GROUP BY m.to_id, m.from_id?
Since the user is either from_id or to_id, half the info is already known?
What is m.id < 999999999 for?
IN ( SELECT ... ) often optimizes poorly.
You say "pagination", yet I don't see the inefficient OFFSET not the better "remember where I left off".
Use realistic limits, not blindly VARCHAR(255).
Each table has 3 timestamps.  You will never use some of them.
Don't use SELECT *; instead, spell out the columns that you really need.
You will need more indexes, but let's wait to see what more of the SELECTs look like.
LEFT JOIN account assumes that the desired account might be missing.  If so, there a data integrity problem.  So switch to JOIN; this may aid in optimizations.
LIMIT 100 -- Isn't that too much to put on a UI page?
AND (message.to_id = 1 AND message.from_id = 2) OR (message.to_id = 2 AND message.from_id = 1) is hard to optimize.  Use UNION of two SELECTs instead of OR, and add (tentatively) INDEX(from_id, to_id).  The reason is that the Optimizer has almost no techniques for handling OR; it simply ignores indexes and falls back on a table scan.

This might work:

AND (message.to_id, message.from_id) IN ((1, 2), (2, 1))

Here's the Changelog entry that hints at such:

----- 2013-12-03 5.7.3 Milestone 13 -- Functionality Added or Changed -- -----

The optimizer now is able to apply the range scan access method to queries of this form:

SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));

Previously, for range scans to be used it was necessary for the query to be written as:

SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' )
                      OR ( col_1 = 'c' AND col_2 = 'd' );

For the optimizer to use a range scan, queries must satisfy these conditions:

Only IN predicates can be used, not NOT IN.

There may only be column references in the row constructor on the IN predicate's left hand side.

There must be more than one row constructor on the IN predicate's right hand side.

Row constructors on the IN predicate's right hand side must contain only runtime constants, which are either literals or local column references that are bound to constants during execution.

EXPLAIN output for applicable queries will change from full table or index scan to range scan. Changes are also visible by checking the values of the Handler_read_first, Handler_read_key, and Handler_read_next status variables.


Does TokuDB need any regular cleanup/maintenance? PostgreSQL has vacuum and InnoDB has purge. Does TokuDB have any required maintenance/cleanup operations? I couldn't find anything in the online docs: https://www.percona.com/software/mysql-database/percona-tokudb

Percona has a github page on the following

OPTIMIZE TABLE (which references another Percona Blog)
ANALYZE TABLE

There is lots of info explaining why.

For example, there are messages stored in non-leaf nodes of Fractal Trees. All those messages are pushed way down to leaf nodes. This simplifies retrieval of stats needed by the Query Optimizer.

Please read those links and learn way more.


Is there a free software that can import excel into mysql on mac? not csv format files, but .xls or .xlsx format excel files, is there any free software for mac, that can import data into mysql?

Full disclosure.. I am the author of the following program.
Give it a try and let me know what you think.
I have tried many applications including the excel add-in from MySQL, but was not satisfied with them, so I wrote my own.

The current version of my program is slow (just like the MySQL add-in), doesn't support international characters, and doesn't support command line parameters, but it is free and works well.

http://panofish.net/excel2mysql/

I am working on a much better version which will remedy the aforementioned shortcomings and should be available soon.


How to add foreign key in a table where column value can come from two different tables? I have three tables

create table main_comments (
   id serial primary key,
   comment text not null
);

create table sub_comments (
   id serial primary key,
   main_id integer not null,
   comment text not null ,
   foreign key (main_id) references main_comments(id) on delete cascade
);

create table report (
  id serial primary key ,
  comment_id integer not null ,
  table_type bool ,
  report text not null
);

in report table comment_id column's value can come from main_comments or sub_comments. so avoiding the confusion i add table_type column which tell from where comment_id value comes from like main or sub table. But now i want to add foreign key to report table.

I would suggest you structure your comments like this,

CREATE TABLE comments (
   comment_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   parent_id  int REFERENCES comments,
   comment text NOT NULL
);

Then you three advantages,

you don't have the problem about having to reference sub-comments AND comments
you can go more than one layer deep
the schema between subcomments and comments is in sync

For more information see the postgresql and hierarchy badges.


What is the impact of a large number of boolean columns in a table on storage and index performance in PostgreSQL? I have a lot of boolean attribute for entities in a table and am in need to store even more of them. Currently I have 17 such attributes (Other fields are four bigint fields). I am in need of adding about dozen or so more attributes.

Should I just go ahead and create boolean columns for each of the required attributes or are there better ways? How would that impact storage?

These fields will be searched over on in multiple combinations (so multi-column indexes are not going to help).

Thought (premature) about saving storage: How about I use a 64-bit integer and use each bit as a boolean value? I can use the bit operators for matching but will such operation use indexes since scan is not based on ordering or equality of integers?

I wouldn't bother.

Postgres has a record overhead of at least 23 bytes per record -- and that's not counting a bitmap to keep track of NULLs, or other block overheads which combined probably average another couple of bytes per record. See https://stackoverflow.com/a/26206031/391445
Your bigint takes 8 bytes of storage, versus 17 for the booleans.
Another option is to use bit strings, but these have an overhead of 5 or 8 bytes, so to store your 17 booleans you need 8 bytes anyway.
Your 4 other bigint fields take another 32 bytes of storage.

So expect to save only around 10% -- less if you have more non-boolean columns, but only slightly more as you add more boolean columns -- at the expense of making your code and data much more difficult to understand.

But if you have a lot of data (hundreds of millions of records) I would do some performance tests to compare.