Login permissions outside of sa - SQL Server Where I'm at, our DBA team is split up into admins, BI, DEV. We are wanting to have our admins have SA rights and the other two teams have everything but sa and to take away the ability to change server settings (min/max memory, file locations, etc...)

I have played around with different server-level permission combos, but they force the need to give individual rights on the database(s) and we are looking to avoid this since these groups are still in our DBA on-call rotation.

I also found the Control Server securable. While this does everything I want it to, it also gives the ability to change server settings (Alter Resources). This would be the perfect answer if only it didn't give the ability to change server side settings. I have tried running a DENY on Alter Resources, but Control Server trumps this DENY.

Is there a way to do what I'm needing in SQL Server that I haven't thought of?

I believe I have tested this thoroughly, but wanted to ask the community before I go back to my team lead. I hope I've given enough information, but let me know if I need more.

what about:

removing them from the sa server role
give them the following server roles: dbcreator, securityadmin
give them db_owner on every user database they need
If they need to use SQL Agent, on msdb give them
SQLAgentOperatorRole
If they deploy SSIS packages, on SSISDB, give them ssis_admin

then you wait for someone's complaining and give also what they need.

Plus:

Well, I know that this is not the best script (10 min...lol), but will solve your problem to give permissions on every database. Maybe you can improve it to solve your problem.

EXEC sp_msforeachdb @command1 =
N'
    use ?
    if DB_NAME() not in (''master'',''tempdb'',''model'',''msdb'',''SSISDB'')
    CREATE USER [<DOMAIN\MyGroup>] FOR LOGIN [<DOMAIN\MyGroup>]
'

EXEC sp_msforeachdb @command1 =
N'
    use ?
    if DB_NAME() not in (''master'',''tempdb'',''model'',''msdb'',''SSISDB'')
    ALTER ROLE [db_owner] ADD MEMBER [<DOMAIN\MyGroup>]
'


Database Log File Size Growth So Huge Unexpectedly My database has two log files. Their sizes are growing very fast.  My backup sizes are becoming massive. I know that when I take full backup of my database, the log files are emptied but it is not. I can't control it.How can I control log growth?

Full backup doesn't clear the logs, I suppose your database is not in simple recovery mode as logs are increasing and its filling up rapidly. Do you take transnational log backup anytime in a day or week?

Ideally Full backup should be taken every day, Differential backup few times in a day and log backup few times in an hour however it also depends upon RPO and RTO.

One thing to note here is transaction log backup helps you in storing active part of transaction as illustrated below:

  A transaction log backup allows you to backup the active part of the
  transaction log.  So after you issue a "Full" or "Differential" backup
  the transaction log backup will have any transactions that were
  created after those other backups completed.  After the transaction
  log backup is issued, the space within the transaction log can be
  reused for other processes.  If a transaction log backup is not taken,
  the transaction log will continue to grow.

Please read recommendation from Microsoft on backup and frequency:

  A database administrator typically creates a full database backup
  occasionally, such as weekly, and, optionally, creates a series of
  differential database backup at a shorter interval, such as daily.
  Independent of the database backups, the database administrator backs
  up the transaction log at frequent intervals. For a given type of
  backup, the optimal interval depends on factors such as the importance
  of the data, the size of the database, and the workload of the server.
  For more information about implementing a good strategy, see
  Recommendations in this topic.

You may read more about RPO and RTO from this link as nicely explained by Mr. Paul Randal.

You may also check best practice for Transaction log autogrowth and other details:

https://www.sqlshack.com/sql-server-transaction-log-administration-best-practices/


Mysql alter/write lock database I runned this in mysql:

UPDATE db SET Insert_priv='Y', Create_priv='Y' WHERE Db='wordpress_fixed';

But this not preventing from inserting more data to database wordpress_fixed. Is it supposed to work that way?

If you manually update any mysql.X table regarding privileges will require a FLUSH PRIVILEGES to effect the change.

Using GRANT is the better way of of ensuring privs exists (and doesn't require FLUSH PRIVILEGES).


SQL database with temporal data Is it possible to do this in a SQL database

 SELECT * FROM Employee
    WHERE BirthDate BETWEEN DATE 1970-01-01 AND DATE 1970-01-01

The result that was said to from this query is all the employees that are born on
1970-01-01.
This query has been taken from the following book
Sorry if this question is lacking.

Some variety of this, depending on RDBMS. SQL Server and MySQL accept date literals like this:

SELECT * FROM Employee
    WHERE BirthDate BETWEEN '1970-01-01' AND '1970-01-01'

SELECT * FROM Employee
    WHERE BirthDate BETWEEN '19700101' AND '19700101'


PostgreSql JSONB SELECT against multiple values I have a very simple JSON table which I populate with some sample data:

CREATE TABLE jsonthings(d JSONB NOT NULL);

INSERT INTO jsonthings VALUES ('{"name":"First","tags":["foo"]}');
INSERT INTO jsonthings VALUES ('{"name":"Second","tags":["foo","bar"]}');
INSERT INTO jsonthings VALUES ('{"name":"Third","tags":["bar","baz"]}');
INSERT INTO jsonthings VALUES ('{"name":"Fourth","tags":["baz"]}');

CREATE INDEX ON jsonthings USING GIN(d);

And am attempting to use the index when running a SELECT.  A simple SELECT to obtain the rows where the value is a single item works just fine:

SELECT d FROM jsonthings WHERE d @> '{"name":"First"}';

But when attempting to run a query which matches more than one value of name I can't find out how to use the index.  I've tried:

SELECT d FROM jsonthings WHERE d->>'name' = ANY(ARRAY['First', 'Second']);
SELECT d FROM jsonthings WHERE d->'name' ?| ARRAY['First', 'Second'];
SELECT d FROM jsonthings WHERE d#>'{name}' ?| ARRAY['First','Second'];

and all of them show a sequential scan of the table (I'm using enable_seqscan=false to force index use if possible).  Is there some way I can rewrite the query so that it uses an index?  I'm aware that I could do:

SELECT * FROM jsonthings WHERE d @> '{"name":"First"}' OR d @> '{"name":"Second"}';

but then I have a variable-length query and I'm going through JDBC so would then lose the benefits of the query being a PreparedStatement.

I'm also interested in seeing a similar query against any of a number of items in the tags key, e.g.:

SELECT d FROM jsonthings WHERE d @> '{"tags":["foo"]}' OR d @> '{"tags":["bar"]}';

but using an ARRAY rather than multiple conditions and using an index.

This is on PostgreSql 9.4.

From docs (http://www.postgresql.org/docs/9.4/static/datatype-json.html)
try to use expression index:

CREATE INDEX idx_jsonthings_names ON jsonthings USING gin ((d -> 'name'));
SELECT d FROM jsonthings WHERE d @> '{"name": ["First", "Second"]}';


How to delete constraint inside a trigger function I have read a lot about it, but being new to Postgres, I am at a loss.

The trigger is :

CREATE TRIGGER foopolygon
BEFORE INSERT OR UPDATE OR DELETE ON polygon
FOR EACH ROW EXECUTE PROCEDURE maj_foo();

Part of the function is :

CREATE OR REPLACE FUNCTION maj_foo()
RETURNS TRIGGER AS
$BODY$
    IF TG_OP = 'INSERT'
     THEN

    ALTER TABLE points
    DROP CONSTRAINT bar;

    INSERT INTO points(gid,fookey)
    VALUES(0,NEW.fookey);

    EXECUTE 'UPDATE points SET fookey = ' ||  quote_literal(NEW.fookey)  || ' WHERE ST_Within(points.geom, $1)'
    USING NEW.geom;

  ALTER TABLE points
  add constraint bar
  foreign key (fookey)
  references polygon (fookey);

      COMMIT;
IF TG_OP = 'UPDATE'......

I think you want to set your foreign key DEFERRABLE INITIALLY DEFERRED so that you don't have to drop and recreate it every time there is an INSERT or other data change.  Maybe even this is not needed, but it depends on what happens in the rest of the transaction.

Then the rest is just

IF tg_op = 'INSERT
THEN
    INSERT INTO points (gid, fookey)
    VALUES (0, NEW.fookey);

    UPDATE points
    SET fookey = NEW.fookey
    WHERE ST_Within(points.geom, NEW.geom);
ELSIF ...

Notes:

you cannot COMMIT from inside a function
the DB is either PostgreSQL or Postgres, not Postgre.
logically, this is more an AFTER trigger than a BEFORE one.


Release unused space in SQL Server database I have a SQL Server database with roughly 1.5 TB test data (size of core tables are calculated using sp_spaceused 'tablename'). At the time of data creation process, I have allocated 350 GB to each data file, and my database contains 9 data files and 3 log files.
I want to shrink the database and release unused space before taking a back up. In the database shrink options it shows Available free space as 51%.
Using Management Studio tasks-> shrink options:

I shrunk individual data files and log files.
then, shrunk the database.

But still shows as 51% free space is available. Can anyone tell me what I'm doing wrong there or am I missing any steps on shrinking process?
What I'm looking for is, since there is a lot of unused space in the database, I want to release it, so the database size gets reduced.

You can script the SQL commands that SSMS generates. Without us knowing what commands you execute, we don't have much to go on.
Also, pls be specific about each file and the file size. You have 12 files. How large was each file before the individual file shrink, and what was the file size after the individual file shrink?
Did you use the TRUNCATEONLY options of the DBCC SHRINKFILE commands? That is the default in the GUI.
Finally, no need to do a "final" DBCC SHRINKDATABASE. Work with each file separately instead.


Ola Hallengren Transaction Log Backups of System Databases I am attempting to backup the transaction logs of master, model in addition to the msdb.

MSDB transaction log backs up without any issues, but the master and model do not.

Here is the script that I have included in a new Agent job (copied from DatabaseBackup - System_Database - Full) which I have named DatabaseBackup - System_Database - Log.

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'SYSTEM_DATABASES', @Directory = N'\\synologymb\sql backups' , @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 24, @CheckSum = 'Y', @LogToTable = 'Y'" -b

Per this Microsoft documentation:

For backwards compatibility with earlier versions of Microsoft SQL
Server, the recovery model of master can be set to FULL or
BULK_LOGGED. However, BACKUP LOG is not supported for master.
Therefore, even if the recovery model of master is changed to full or
bulk-logged, the database continues to operate as if it were using the
simple recovery model.

So that's why you can't get a transaction log backup of Master.
From that same document:

Best practice:  We recommend that you create only full database backups of model, as required. Because model is small and rarely changes, backing up the log is unnecessary.

While that doesn't explicitly say that SQL will prevent a transaction log of Model from happening, it certainly recommends against it.
Note: That's an old version (2008 R2) of the documentation. New versions (I checked 2017) don't repeat the detail about Master, but they also don't contradict it.


How do I best measure the query performance? I have 2 stored procedures, where the second stored procedure is an improvement of the first one.
I'm trying to measure by exactly how much that is an improvement.

Measuring clock time doesn't seem to be an option as I get different execution times. Even worse, sometimes (rarely, but it happens) the execution time of the second stored procedure is bigger than the execution time of the first procedure (I guess due to the server workload at that moment).

Include client statistics also provides different results.

DBCC DROPCLEANBUFFERS, DBCC FREEPROCCACHE are good, but the same story...

SET STATISTICS IO ON could be an option, but how could I get an overall score as I have many tables involved in my stored procedures?

Include actual execution plan could be an option also. I get an estimated subtreecost of 0.3253 for the first stored procedure, and 0.3079 for the second one. Can I say the second stored procedure is 6% faster (=0.3253/0.3079) ?

Using "Reads" field from SQL Server Profiler?

So how can I say that the second stored procedure is x% faster than the first procedure, no matter the execution conditions (the workload of the server, the server where these stored procedures are executed, etc)?
If it is not possible, how can I prove the second stored procedure has a better execution time than the first stored procedure?

I like to use the SQLQueryStress free tool when comparing a before and after scenario. With SQLQueryStress you can execute each stored procedure as many times as you like, and get the total average stats for all executions.

For example, you could execute each stored procedure 100 times, and then use the stats to back up your improvements. "Over 100 executions, my improvements save a total of 30 seconds and the stored proc does 1500 less reads per execution." I think you get the idea.

If there are parameters in the stored proc, it's always a good idea to double check that your improvements work with many different sets of parameters. SQLQueryStress does some cool stuff with letting you substitute parameters in your query to get a better overall picture of how the stored proc might be performing.

SQLQueryStress documentation: http://www.datamanipulation.net/sqlquerystress/documentation/documentation.asp


You need to create a temporary table with the merged data, then remove the old table and then rename the temporary table with the correct table name.

EXAMPLE:

CREATE TABLE table_temp SELECT user,is_su,last_login,GROUP_CONCAT(roles-for_groups) AS roles-for_groups FROM table1 GROUP BY user;

Check if table_temp is ok and then you can remove your old table.

DROP TABLE table1

The last step, rename the new/temporary table to the actual table name

RENAME TABLE table_temp TO table1;

PS: I'm assuming your table name is table1 because you never posted the name.


In MySQL replication, why aren't queries run on the slave when there was no USE statement on master? We have replication running from our master server to a slave. binlog_format is the default; mixed.

We have noticed something weird and are not sure whether it's a bug or intended behaviour. Certain statements are being ignored.

Ignored

DELETE FROM schema_name.table_name WHERE id = 123;

Works

USE schema_name;
DELETE FROM schema_name.table_name WHERE id = 123;

Also works!

USE different_schema_name;
DELETE FROM schema_name.table_name WHERE id = 123;

When I delete something without previously selecting a database on master, the query is ignored on the slave. But when I issue a USE schema_name on the master before the statement, even if it is a different schema, then it does run on the slave.

Regardless of the USE and whether they run on the slave, they always appear in the binary logs on master.

We noticed this as we have a script that tidies up old shopping carts that loops over a list of databases and issues DELETE FROM schema_name.carts WHERE created_at ..., but replication was breaking when an INSERT INTO carts SET cart_number = 'xxx' call took place on the slave; the cart number (unique column) no longer existed on master (after a successful check to validate such) but on the slave it still existed so it threw the error and broke replication.

Is this intended behaviour? What's the best method to ensure this kind of behaviour doesn't happen?

For reference and possibly meaningful, master is MariaDB (in a drbd cluster) and slave is Percona.

Please provide the relevant my.cnf entries.

If you have

binlog_do_db = B
-- Note:  That implies ignoring other dbs

and you do

USE A;  -- either explicitly, or due to the connection parameters
UPDATE B.tbl ...;

That UPDATE will be ignored.


UTL_TCP how to monitor number of connections? I have a leak somewhere in the number of UTL_SMTP connections being made and closed.  There is an underlying limit of 16 from its reliance on UTL_TCP.

I'd like to be able to monitor the connection usage to find the problem.

Where can I get a count of the number of open UTL_TCP connections?

UTL_MAIL strongly rely on TCP (Network) and the number of active TCP connections that can be open for a session is 16, per documented Bug 8262564.  Bug 8262564 got fixed in Oracle 11g 11.2.0.1 or later and also on Oracle 11g 11.1.0.7 as one-off patches.  Bug 8262564 is quoted as reference to state that the number of active TCP connections that can be open for a session is 16.  While UTL_MAIL does not have restrictions but the TCP that serves as the base for Mail related packages such as UTL_MAIL and UTL_SMTP does have an active session limit of 16.

You say: "the number of UTL_SMTP connections being made and closed". Are you sure about the closing part? When you open a mail connection, you have to explicitly close it:

--SMTP example
declare
my_conn utl_smtp.connection;

begin
-- open smtp connection
my_conn := utl_smtp.open_connection(
host => 'www.testlink.com',
port => 25);

-- Explicitly close the connection oppened
utl_smtp.quit(c => my_conn);

exception
when others then
raise_application_error (-20002,'An error has occurred.');

end;

Please, note that If UTL_MAIL throws an exception and emails are resend from the exception, the smtp connections are lift open and after 15 successive exceptions UTL_MAIL throws errors.
Connection is getting closed only in those cases when UTL_MAIL.SEND succeeds without any error. Since everything related to a connection is done within the UTL_MAIL package it is not possible to close the connection from outside the package.
I do not know a v$ to count open smpt/tcp connections for an Oracle session.
I think you can do it by yourself, for example using the following code:

set serveroutput on
alter session set smtp_out_server='<smtp_server_name:25>';

declare
cnt number := 1;
cnt1 number;
begin
for i in 1..20 loop
begin
UTL_MAIL.send('valid_email@domain.com','invalid_email@domain.com',NULL,NULL,'testing invalid email','testing invalid email' );
Exception
When Others then
UTL_MAIL.send('valid_email@domain.com','valid_email@domain.com',NULL,NULL,'send from exp block','send from exp block');
dbms_output.put_line(cnt);
cnt := cnt + 1;
end;
end loop;
End;
/

I found this code on Oracle Metalink knowledge base. After each error, you increase a counter to monitor the number of open connections.
Regards
Giovanni


How round in Postgres? when we use SUM on a data table gives a 1 or 2 cents of difference due to rounding in Postgres.

We are using Postgres 9.4, the field data types is numeric with 19,3
and the result have to be with two decimals.

for example:

 15.309
 10.111
  5.005

and the result must be: 30.43

the query is:

SELECT SUM(fcv_total_comprobante)::numeric(19,2)
FROM factura_venta
WHERE DATE(fcv_fecha_comprobante) BETWEEN '2015-09-01' AND '2015-09-30'

the result given is 30.42 (because is 30.425) and must by 30.43

which would be the best way to round a number to 2 decimal places?

Define the attribute/column being summed as type money or as type numeric.  If defining numeric you'll need to specify the percision.

http://www.postgresql.org/docs/9.4/static/datatype-money.html#DATATYPE-MONEY-TABLE


Concurrent INSERTs in XtraDB (InnoDB) I'm currently unable to insert rows concurrently in an XtraDB table on Percona Server 5.1.54. I had the same problem with a similar version of MySQL. My table definition is as follows.

CREATE TABLE `test` (
  `a` int(11) NOT NULL DEFAULT '0',
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM

If in on one connection, I execute something like INSERT INTO test VALUES (0, sleep(60)) and in a second process, INSERT INTO test VALUES (1, 0), the second insert will wait for the first to complete. Note that innodb_table_locks is off. Is there something I'm missing, or is this just not possible?

Update

As Randy points out below, I created a MyISAM table during testing and then assumed that concurrent inserts weren't possible for my other tables (which are InnoDB). I actually don't have a problem with InnoDB tables.

You might notice the problem is :

ENGINE=MyISAM

Try changing that to:

ENGINE=InnoDB


SPI postgresql - insert data into table from postgres.c I'm working inside postgres.c (inside exexc_simple_query) of postgresql 9.1 (line 1094 of http://doxygen.postgresql.org/postgres_8c_source.html#l00829 ). I need to insert some query information  in a table that I will check in a second moment.
I'm trying to do it with SPI using these three lines:

SPI_connect();
SPI_exec("INSERT INTO testvalues (11,6)", 5);
SPI_finish();

but when I start the server and I send a query I get segmentation fault:

LOG:  server process (PID 13856) was terminated by signal 11: Segmentation fault
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
FATAL:  the database system is in recovery mode
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted; last known up at 2013-07-14 00:40:22 CEST
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  record with zero length at 0/17D7368
LOG:  redo is not required
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections

Do you have any idea how I can fix it? Is there a better way to insert data from the postgres.c?

I found the error.
SPI functions must be inside the transaction and in my case between start_xact_command and finish_xact_command.


Single table or multiple tables for custom transfer queue Currently, I'm working on synchronizing data between two SQL Server databases on separate servers but on the same network. We'll call them A(the master database) and B(the one exposed to a web application for clients). Records updated in A must also then be updated in B, whereas new records inserted in B must also then be inserted in A. While the former is never true the other way around, there could be rare cases for the latter to be true from A to B; which is not accounted for at the moment.

To solve this, I've been developing a custom queue to transfer the data. I've linked the SQL Servers, then applied triggers to the tables so that they also copy the data to a transfer queue table. Then, I have SQL Server agent run a stored procedure periodically that checks for entries in that table, to which acts accordingly and deletes the transferred data from the queue.

My question is would it be better to maintain a single transfer queue table on each SQL Server Database or multiple queue tables for each trigger? While this doesn't really apply to B as the inserted fields to transfer are always the same, the master tables in A that could get new records inserted or updated have a variable amount of fields. I'm leaning more towards a single queue and set it up so that a trigger puts each field value as a separate record with a unique key that identifies that set so that the stored procedure can group them and insert/update them in the other database all at once. Is this the better/more efficient method or should I consider multiple queues? Thanks for any and all help!

There are a lot of options when it comes to data synchronization in SQL and there is no single best option. It all depends on what are the priorities for the solution, what is the workload, maintainability and so on.. there are also build in features like SQL Server Replication that you could use.

If you are concerned with efficiency of your solution, then I would suggest to perform tests to see how many transactions are you getting and is it a satisfactory result.
You have to bear in mind that triggers are not the fastest things in the world, so if this becomes a bottleneck - you might want to look for other solutions.

An option for replacing triggers might be adding a timestamp or last insert/update datetime column in your data that you would reference in the synchronization procedure processing only the ones changed since last sync would improve performance. This would mean you would have to synchronize whole rows unless you implemented some more advanced logic.

You should also design your conflict handling process in case the same information gets updated on both servers between synchronizations.


PostgreSQL drop view in case of unknown whether materialized or not I'm trying to drop a view, of which I cannot assure whether it is a MATERIALIZED VIEW or a VIEW.

DROP VIEW IF EXISTS myview;

throws an error, if myview is a MATERIALIZED VIEW and vice versa. What I am looking for is drop statement (or function) that does not care about if it has to drop a MATERIALIZED VIEW or a VIEW.

(hint: there seems to be neither drop nor drop-view tag, therefore drop-table)

My solution at this point is a function

CREATE OR REPLACE FUNCTION dropview(IN view_schema TEXT, IN view2drop TEXT)
RETURNS void
LANGUAGE plpgsql
AS
$$
DECLARE drop_stmnt VARCHAR;
BEGIN
SELECT
    CASE
        WHEN relkind = 'm' THEN 'MATERIALIZED VIEW'
        WHEN relkind = 'v' THEN 'VIEW'
    END
FROM pg_class
WHERE relname = view2drop
INTO drop_stmnt;

EXECUTE 'DROP ' || drop_stmnt || ' IF EXISTS ' || view_schema ||'.'|| view2drop || ';';
END;
$$;

so that with

SELECT dropview('myschema', 'myview');

doesn't have to care about MATERIALIZED VIEW or VIEW.


Reducing latency in read operations Table size 45 million rows
Schema: item name, categories and subcategories, order quantity and price.

What is the fastest way to put each item into an appropriate bucket?

For example: price range given will be: 500- 600
We have 10 buckets: 500-510, 510-520, ...590-600
We also have filters on particular category and subcategory, i.e. specific category and subcategory have to be only considered in a query.

By bucketing, it means that all items which falls in a said range, and belongs to the particular category and subcategory, there order quantity will be summed.

What will be an optimal way to construct the query? We have a query which does all the processing in one go using case and when. However, this takes around 18 sec or more mostly.

This has to be optimized. I have tried adding indexes, which is not helping much.

I would compute the buckets by FLOOR(price/10).  Should 510 be in the 50 bucket?  Or the 51 bucket?  Or both?

"put each item into an appropriate bucket" sounds like an INSERT statement.  But
"will be summed" sounds like a SELECT statement.  Which do you mean?

Assuming you are selecting all 45M rows, here is the optimal query:

SELECT  10 * FLOOR(price/10) AS 'price bucket',
        category,
        subcategory,
        SUM(order_quantity) AS 'quantity'
    FROM tbl
    GROUP BY 1,2,3

(Or do you need item in the GROUP BY?)

The real performance problem is that you need to read all 45M rows.

There might be a way to speed it up.  What is the data flow?  Do you insert rows and then never change or delete them?  If so a Summary Table that you build and maintain (daily?) would be much smaller and faster.


Patching of Windows Server 2012 running SQL Server 2014 We have patching on servers running SQL Server 2014. Good thing we have always on availability group. Bad thing, they are patching Primary Server first. Trying to understand how this is going to work.
1) Primary gets patched, DBs fail over to Secondary automatically.
2) Assuming all goes well, Primary will come back up and becomes secondary,
   DBS get back in Sync.
3) Next day, they patch secondary (which is at this point primary) and another
   fail over happens and roles change.

Does it sound correct?

No, this is backwards.

Typically, you wanna patch the secondary, let that sit around for a few days, then fail over to it manually.

Once things are up and running there, run some smoke tests. If all those pass, patch the primary, and fail back during your next maintenance window.

  "Primary gets patched, DBs fail over to Secondary automatically"

You don't have a dev environment to test this stuff on, huh?


always error "cannot insert value NULL" sqlserver I want to update table "tmpiscunitmap.tr_kpi_id" year_period=2016
with value from table "tmp_kpi.tr_kpi_id where year_period=2016"
with condition tmpiscunitmap.tr_kpi_id = tmp_kpi.tr_kpi_id on year_period=2015?

this my query and i get error "cannot insert value NULL into tmpiscunitmap.tr_kpi_id":

UPDATE tmpiscunitmap set tmpiscunitmap.tr_kpi_id=(
SELECT TOP 1
        b.tr_kpi_id
        FROM
        tmptr_kpi
        AS a
    INNER JOIN tmptr_kpi AS b ON a.tr_kpi_code = b.tr_kpi_code
    where
    a.year_period = 2015 AND
    b.year_period =2016 AND
    a.tr_kpi_id=tmpiscunitmap.tr_kpi_id)

Table tmptr_kpi:

Table tmpiscunitmap:

First observation is that your update statement has no WHERE clause. This means that it will update all rows of the table. Are you sure you want that?

From the comments, it seems that all rows have year_period = 2016 and you do want to update all of them. In my opinion, it wouldn't hurt to explicitly add this condition in the statement. The opposite, it would avoid any unwanted changes, in case you run it in the future, say next year when you have rows with 2016 and 2017.
Second, the error is due to for some rows, either the subquery returns a NULL value or it return no rows at all.

You can easily find the "missing" values (those that the subquery returns no rows) with this:

SELECT DISTINCT tr_kpi_id
FROM tmpiscunitmap AS t
WHERE NOT EXISTS
      ( SELECT *
        FROM    tmptr_kpi AS a
            INNER JOIN tmptr_kpi AS b ON a.tr_kpi_code = b.tr_kpi_code
        WHERE
            a.year_period = 2015 AND
            b.year_period = 2016 AND
            a.tr_kpi_id = t.tr_kpi_id
      )
--  AND year_period = 2016
 ;


Oracle DBMS Scheduler Email Notifications Dynamically Send Server and Instance Name We have two Oracle database servers. One for production with one instance on it, and another one with multiple instances for Dev, Test, etc. I have some jobs that run on production and email me when they fail. I had originally set the sender to be something like server1.prod@company.com, so that I knew what environment the error was coming from.

Problem is when we copy production to one of the test or dev environments to get them up to date, then the job errors out for whatever reason, but it says it is coming from server1.prod@company.com which is incorrect.

I would like the notification email to pick up the servername and instance name that it is sent from, so if it comes from test it would be server2.test@company.com. If that is not possible, then it would be fine to get this info in the subject line or body of the email.

I can get the server and instance name pretty easy with

SELECT  host_name, instance_name
FROM    v$instance;

,but I can't figure out how to get this to be used in the job email notifications.

I set these jobs up using the sql developer wizard, so if you can show me how to change it with that would be fine, or with pl/sql code, I am ok with either.

If none of this is possible, then is there I way I can make the jobs to not send out once they get to the non production environments at least because I really don't care if they fail there.

SYS_CONTEXT can be used to extract the host name of the database server and the instance name.

You simply call dbms_scheduler.add_job_email_notification in order to add the email notification like the following:

BEGIN
DBMS_SCHEDULER.add_job_email_notification (
  job_name => <JOBNAME>,
  recipients => <EMAIL>,
  subject =>
    SYS_CONTEXT('USERENV','SERVER_HOST') || ' ' ||
    SYS_CONTEXT('USERENV','INSTANCE_NAME') ||
    ' - Scheduler Job Notification-%job_owner%.%job_name%-%event_type%',
  events => 'job_failed');
END;

The 12c documention for all of the SYS_CONTEXT parameters can be found here.


MySQL SELECT query: i want show duplicated entries Example

My query is:

SELECT code_CAT FROM categories WHERE id_code IN(14,14,14,14);

output is:

53724

Only one row.

Instead I want this output:

53724
53724
53724
53724

How can I achieve this? I want this because I need to use the records on an Excel Spreadsheet.

If you really need it (I don't get why though), you can do

SELECT code_CAT
FROM categories a
INNER JOIN
(
  select 14 as id_code
  UNION ALL
  select 14
  UNION ALL
  select 14
  UNION ALL
  select 14
)b ON (a.id_code = b.id_code)


Advantage of using a database snapshot for reporting purposes What is the performance advantage of using a snapshot of a database for reporting purposes?

The way I see it, it would probably downgrade performance since for every write in the original database, another write would have to be done for the snapshot itself.

I can see that you'd use snapshots whenever you want to do reporting of data up to that point, but that doesn't fall into the performance category.

So again, is there a performance advantage?

Using a database snapshot located on your production OLTP server will, in all likelihood, make performance worse. There are two main reasons:

Extra write overhead. Snapshots are copy-on-write, so this will obviously impact performance.
A snapshot will share pages from the old database on disk, but it uses its own memory in the buffer pool. If you have a large table, it will end up consuming memory for both the OLTP database, and the snapshot, even for the unmodified pages. You can end up with a lot of additional memory usage because of this, which can lead to more disk I/O if your server doesn't have loads of free RAM.

So if you're trying to make reporting less intrusive on only a single server, you're probably better off just querying the OLTP database and using WITH (NOLOCK) hints, and then reigning things in with the resource governor if needed. Beyond that, look into some kind of scale-out system (log shipping, mirroring, etc.)


Database design/structure for address details I am trying to design database. I have one problem related to addresses. The will be different types which will have addresses, for example Companies, Persons.
I am not sure how to design my database. I have three options in my mind.

Option one: Create one Address table and create AddressRelation tables for each of above tables like below:

  1) Addresses table:

  ID
  Country
  City
  District
  Street
  Building
  Apartment

  2) Companies table:

  ID
  CompanyName
  ..........

  3) Company address relation table:

  ID
  CompanyID (Has foreign key to Companies table)
  AddressID
  AddressType (Legal, Factual) Status

  4) Persons

  ID
  Surname
  FirstName
  Patronymic
  ..........

  5) Persons address relation table

  ID
  PersonId (Has foreign key to Persons table)
  AddressID
  AddressType (Legal, Factual, Birth)
  Status

Option two: Create only one AddressRelationTable and add trigger instead of foreign keys:

  1) Addresses, Persons, Companies table as above.

  2) Address relation table:

  ID
  OwnerID (OwnerID can be PersonID or ComanyID)
  AddressID
  AddressType (Legal, Factual, Birth)
  Status

Option three: Add foreign keys from Persons and Comapnies table to Address table:

  1) Address table is as above.

  2) Companies table:

  ID

  CompanyName
  ..........
  LegalAddressID (Has foreign key to Addresses table)
  FactualAddressID (Has foreign key to Addresses table)

  3) Persons table:

  ID
  Surname
  FirstName
  Patronymic
  ..........
  BirhtAddressID (Has foreign key to Addresses table)
  LegalAddressID (Has foreign key to Addresses table)
  FactualAddressID (Has foreign key to Addresses table)

Third option is my last option, because it is less flexible one and I don't want to add address details directly to my main tables.

Option two is good because using this I need to create less tables, but I don't want to use trigger instead of foreign key, and OwnerID can be from two tables.

First option is straightforward I think, but this time number of tables will increase significantly.

Please consider that there are actually three tables which will have address details, same scenario must be applied for contact details and this number can increase in the feature.
I tried to keep question as simple as possible so I don't provide details for third table and for contact details.

My question is which option should I use and why? May be there are other great options which I don't imagine?

This is a spin-off from your option two.  It uses two design patterns, namely class table inheritance and shared primary key.

In addition to having two tables, one for companies and one for persons, have a third table, called contacts, that is a generalization of companies and persons, regardless of type.

Contacts.ID will be the usual kind of primary key, probably some kind of autonumber.  Data that pertains to companies and persons alike can be stored in this table.

In the Companies and Persons table, don't include an ID field to use as a primary key.  Instead, declare Companies.ContactID and Persons.ContactID to be primary keys in their own table, and also references to Contacts.ID.  This has the nice effect that it enforces the one-to-one nature of the IS-A relationships, namely a company is a contact and a person is a contact.  It also means that you can use ContactID in any other table and refer to the contacts and also to the companies table or the persons table, as the case may be.

Data that pertains to person, and only to persons, goes in the persons table.

Data that pertains to companies, and only to companies, goes in the companies table.

You can have a ContactsAddresses table that contains ContactID and AddressID so as to provide a many to many link between contacts (whether persons or companies) and addresses.


Database Normalization: A Case Of The NULLs I'm trying to learn about proper database management and am having a hard time with designing a database that doesn't rely on NULLs. If you think I'm crazy for embarking on this, it is because of SQL and Relational Theory: How to Write Accurate SQL Code (ch.4, p.79), where the author makes the case that simply because having NULLs produces unexpected results in certain situations, they should be avoided.

The alternative to NULL-able fields, as I understand, is to create 1::0-1 relationships for each field. This seems like it would make querying information very difficult. Is this the correct approach? Are NULLs, in fact, much safer than the author leads me to believe?

Would using a VIEW to represent the many 1::0-1 relationships be applicable and any more beneficial than simply having the NULLs in table in the first place?

My scenario is a user table with optional fields that may, or may not, be foreign key relationships to other tables. Obviously, making those fields NOT NULL means they must be required, but I can't put 0 in there since it breaks the foreign key. Other situations occur when trying to put 0000-00-00 for, say, a birthdate field, since MySQL doesn't allow me to do that.

Thus, I'm back to wondering if eliminating NULLs are, indeed, a best practice and provide the greatest flexibility and performance than the alternative.

I beg to disagree with this author.  The results are expected, but may not be the desired values.  Null values don't work well with logical comparisons as the value is unknown, as is the result of a comparison.

If you are using nullable columns as query criteria, you do need to handle them appropriately.  These are some of method I have used:

WHERE   (nullable = ? OR nullable IS NULL)
WHERE   coalesce(nullable, -1) = ?
WHERE   nvl(nullable, -1) = ?

EDIT: I do agree with the author that avoiding NULL values on searchable fields is desirable, but designing a database without NULL columns can result in other issues.  As design goals, avoiding duplicate and NULLs are appropriate,  However a database row is a representation of something else, sometimes an imperfect representation.

For code lists values like "Unspecified", "Unknown", "Not applicable" and "To be determined" may be appropriate.  If NULL has meaning, then it is may be appropriate to use that meaning rather than NULL for the column value.

You may also want to consider whether the data has value.  I do encounter data which has been gathered because it could be, but is neither used nor useful.  Consider the privacy issues or other legal issues resulting from gathering the data.

Nullable columns are common when subtypes occur in the same table.  Understanding the subtypes helps in deciding on the appropriate approach to handling these columns.  Using separate tables for each subtype can make the design more complex.


Equality query on NVARCHAR column yields multiple results in SQL Server 2012 I am in the process of moving a pet project from PostgreSQL (9.2.2) to SQL Server (2012 Standard).

I've noticed an interesting phenomenon when querying unicode words. Given the definition:

CREATE TABLE [word](
    [id] [int] IDENTITY(0,1) NOT NULL,
    [value] [nvarchar](255) NULL
 );

and the data:

insert into word (value) values (N'ῥύπῳ');
insert into word  (value) values (N'ἀπὸ');
insert into word  (value) values (N'ἀπό');
insert into word (value) values  (N'ἐπὶ');
insert into word (value) values  (N'ἐπί');
insert into word (value) values  (N'ὑπὸ');
insert into word (value) values  (N'ὑπό');
insert into word (value) values  (N'πίῃ');

insert into word  (value) values (N'λόγους');
insert into word  (value) values (N'λόγχῃ');
insert into word (value) values  (N'λόγων');
insert into word  (value) values (N'ἀλόης');

a query for a particular word will return near matches. For example:

select * from word where value = N'ἀπὸ'

returns:

id  value
102137  ῥύπῳ
102141  ἀπὸ
102142  ἀπό
102143  ἐπὶ
102144  ἐπί
102145  ὑπὸ
102146  ὑπό
102147  πίῃ

http://sqlfiddle.com/#!6/1ab66/1

However, the same pattern in PostgreSQL only returns the exact match. How can I get SQL Server to do the same?

(PostgreSQL fiddle link): http://sqlfiddle.com/#!12/c57a6/1

I have the distinct feeling that I'm missing something, but I'm not quite able to figure out what it is.

The database collation is SQL_Latin1_General_CP1_CI_AS (which is also the server's collation) on a local installation.

The collation determines the comparison semantics.

If I try

CREATE TABLE [word](
    [id] [int] IDENTITY(0,1) NOT NULL,
    [value] [nvarchar](255) COLLATE Latin1_General_100_CI_AS NULL
 );

It only returns ἀπὸ.

Changing the suffix to AI for accent insensitive returns ἀπό also.

On my install I have tried every collation and 1526 return 1 (presumably AS and BIN collations), 1264 return 2 rows (presumably AI) and 1095 return 8.

From a quick look through this last group looks to include all the SQL collations and 90 collations whereas all the 100 ones are in the first 2 groups so I presume this is some issue that has been fixed in the 2008 batch of collations. (See What's New in SQL Server 2008 Collations)

Script to try this yourself

DECLARE @Results TABLE
(
Count INT,
Collation SYSNAME
)

SET NOCOUNT ON;
DECLARE @N SYSNAME;
DECLARE @C1 AS CURSOR;
SET @C1 = CURSOR FAST_FORWARD FOR
SELECT name
FROM sys.fn_helpcollations();
OPEN @C1;
FETCH NEXT FROM @C1 INTO @N ;
WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT @Results
  EXEC('SELECT COUNT(*), ''' + @N + ''' from word where value = N''ἀπὸ'' COLLATE ' + @N)
  FETCH NEXT FROM @C1 INTO @N ;
END

SELECT *
FROM @Results
ORDER BY Count DESC


How to improve performance of a query that joins three tables? I want to fetch data from three tables: table A, table B and table C.

Table A in databaseA has the customerid column which is a primary key
Tables B and C in databaseB also have same column customerid (not primary key).

I want to retrieve the last transaction date in table A and table B for each customer in table A.

Expected result should have these columns:

customerID
Max(date in table B)
Max(date in table C)

The query below takes forever to run. How do I optimize the query to get desired result in less than 5 sec?

NB: tableA and tableB have about 10 million data each and both databases have the same character set.

select
   a.customerid,
   a.custname as name,
   max(b.lastdate) as lastdt,
   max(c.lastdate) as lastdtc,

     case
        when  max(b.lastdate) < date_sub(curdate(), interval 7 day)
          and max(c.lastdate) > date_sub(curdate(), interval 7 day)
                  then 'INACTIVE'
        when  max(c.lastdate) < date_sub(curdate(), interval 7 day)
         and  max(b.lastdate) < date_sub(curdate(), interval 7 day)
                  then 'DORMANT'
        when  max(c.lastdate) is null
         and  max(b.lastdate) < date_sub(curdate(), interval 7 day)
                  then 'DORMANT'
        else
                       'ACTIVE'
        end as Status
  from
           database1.table a
     inner join
           database2.table b
           on a.customerid=b.customerid
        left join
          database2.table c
           on a.customerid=c.customerid
           where a.customername like concat('$fromclient','%')
    group by a.customerid
         order by lastdt
    limit $fromclient offset $fromclient

Step 1:  Build a query that computes lastdt, lastdtc, and status.

Step 2:  Use that as a "derived" table to join to the other table(s) for the rest of the info.

The intent of Step 1 is to focus on the complexity of the GROUP BY and yield a much smaller table.  Then Step 2 has less work to do.


Need to Rewrite/optimize Slow Query I have a query with following explain plan

EXPLAIN SELECT COUNT(applicant.id) as applicantCount,
    application.name,applicant.application_id,application.id
FROM  applicant
  LEFT JOIN application
    ON applicant.application_id = application.id
  LEFT JOIN calendar
    ON applicant.application_calendar_id = calendar.id
  LEFT JOIN application_category
    ON application_category.id = calendar.category_id
WHERE application_category.id = '886760e6d0b6ab306b0914a60ac4265e'
GROUP BY application.name\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: application_category
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 38
          ref: const
         rows: 1
        Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: calendar
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: applicant
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2333634
        Extra: Using where; Using join buffer
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: application
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 38
          ref: eRecuitment_TestQueries.applicant.application_id
         rows: 1
        Extra:
4 rows in set (0.00 sec)

Query takes 30 seconds to complete where tables application, application_category and calendar are master tables which have 3 to 4 records only. The applicant table has about 2400000 records.

Is there any other approach to rewrite/optimize this query?

Index details

Table application

PRIMARY KEY (`id`)

Table applicant

PRIMARY KEY (`id`),
KEY `idx_abdul_test` (`application_id`)

Table Calendar

PRIMARY KEY (`id`)

Table application_category

PRIMARY KEY (`id`)

The last two LEFT joins can be rewritten as INNER joins (due to the WHERE condition on the application_category table.

If application (name) is indeed unique, there is no difference if you group by that column or by application (id) or even by applicant (application_id) - the last one provided there is a FOREIGN KEY constraint from applicant towards application.

So, you could add an index on applicant (application_calendar_id, application_id)

and rewrite the query as:

SELECT
    COUNT(*) AS applicantCount,
    application.name,
    applicant.application_id,
    application.id
FROM  applicant
  LEFT JOIN application
    ON applicant.application_id = application.id
  JOIN calendar
    ON applicant.application_calendar_id = calendar.id
  JOIN application_category
    ON application_category.id = calendar.category_id
WHERE application_category.id = '886760e6d0b6ab306b0914a60ac4265e'
GROUP BY applicant.application_id ;


Identity column jump in sql server restart I had many research on identity column jump in sql server restart.
I know the reason why this happen as it explained here.

I know the solution how to prevent this using either manually perform CHECKPOINT or –t272 flag (Which should not use).

  Why I am here - I am looking for the solution where there no need of
  human interaction to RESEED all them back to there valid value using manual script

I have come up with one solution for which I am not sure - is it good or not?

My Solution

When restart performed either manually or server crash, trigger below SQL script using powershell or bat file (configure using TaskScheduler trigger on windows logon).

Script

-- RESEED all column IDENTITY to 0
EXEC sp_MSForEachTable '
 Print ''?''
 IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
  DBCC CHECKIDENT (''?'', RESEED, 0)
 else
  Print ''Table does not have an identity value'''

-- RESEED to it's last value
EXEC sp_MSForEachTable '
 Print ''?''
 IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
  DBCC CHECKIDENT (''?'', RESEED)
 else
  Print ''Table does not have an identity value'''

Is this good solution? if no then what should be it? any one have other idea?

NOTE:

This gaps are not creating any issue in my application.

If you want something to run on server startup you can create a stored procedure in the master database and mark it as a startup stored procedure as long as the scan for startup procs configuration option is set. No need for powershell or batch files.

  I have come up with one solution for which I am not sure - is it good
  or not?

To determine whether it is a good solution we need to know the problem it is intended to solve. It turns out there is no problem to solve so no this is certainly not a good solution. If it ain't broke don't fix it.

Identity columns have never guaranteed a gapless sequence. If you in fact need one then you would need to roll your own incrementing solution that blocks other transactions until each allocated range has been successfully committed.

The code you posted will reseed all identity columns to 0 and then go through and reseed them all using the maximum value in the identity column? What happens if someone tries to insert rows into a table processed by the first step but not yet processed by the second? They will likely get a duplicate key violation as the insert tries to reuse identity values that have already been allocated to other rows in the table.


Optimal mySQL design for 500k+ products with up to 50 specs I facing a problem where I have to design a MySQL database capable of containing +500k products in approx. 1.500 categories, that can hold up to 50 specs. (like size, weight, speed, color, etc).
My goal is to find/use a design that is optimal (fast) for selecting products base on the input of up to 50 specs. Later on, I may implement precached on often use search patterns (specs), when I have collected statistics on that.
As my raw data is in Python (dicts/lists) I was initially thinking of using JSON-objects, but as I see it, this would result in huge overhead and maybe bad search performance. (Actually, I have no experience in using JSON in MySQL)
BTW The easiest would have been using MongoDB, but this is a no-go. I'm also trying to find a way to design it as generic as possible without having product-specific tables and like.
Based on the information above... What design/schema would be the optimal/best, regarding fast search in specs. ?
Is EAV (Entity-Attribute-Value) the way to go or is the a better way ?
If anybody knows if this question is answered somewhere, please link to it :-)
Thanks

A common request.  You are actually a step ahead of most questions tagged with [eav] -- you have thought about "common use patterns" and how to optimize once you gather that info.
You should be able to make an initial guess at what is most common, put them in columns for efficient searching, put flag-like attributes in a FULLTEXT-indexed column, then throw the rest into a JSON column for testing in the app.
More:  http://mysql.rjweb.org/doc.php/eav


mySQL Group Replication Error "ERROR 3092 (HY000)" The full error (on 2nd node) is:
mysql> START GROUP_REPLICATION;

ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.

This is after starting the 1st server without error.
The log shows:
2021-02-27T19:05:45.079426Z 16 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2021-02-27T19:06:10.874606Z 16 [System] [MY-013587] [Repl] Plugin group_replication reported: 'Plugin 'group_replication' is starting.'
2021-02-27T19:06:10.875454Z 17 [System] [MY-011565] [Repl] Plugin group_replication reported: 'Setting super_read_only=ON.'
2021-02-27T19:06:10.878134Z 16 [Warning] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the allowlist. It is mandatory that it is added.'
2021-02-27T19:06:10.878182Z 16 [Warning] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Automatically adding IPv6 localhost address to the allowlist. It is mandatory that it is added.'
2021-02-27T19:06:10.881114Z 18 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
2021-02-27T19:06:13.300659Z 0 [ERROR] [MY-011516] [Repl] Plugin group_replication reported: 'There is already a member with server_uuid 6227f63c-dd97-11ea-8989-86fbcb300464. The member will now exit the group.'

I am following these instructions.
The instructions say that the UUID should be the same for all servers.
My droplet is a LAMP Ubuntu 20.04 droplet. mySQL 8.0.23
Anyone run into this? Any thoughts on what's going wrong? I've checked and double-checked the procedure and made sure I followed it as best I could.

Turns out the 2 servers had the same server UUID since one was cloned from the other. Deleting auto.cnf on one server and restarting fixed the problem.
Newbie error for sure!


PostgreSQL Reduce Writes on Flash Storage (TBW) I'm currently a startup and using non server grade NVMEs/SSD on my database server.
My motherboard , CPU , DDR (ECC) etc are server grade except for the flash part.
I'm looking for ways to reduce the amount of writes on my Postgresql Database to preserve the lifespan of Flash Drives hopefully to reduce the failure rate.
My requirements

Write speed (Low Priority)
High Read speed & low latency (High Priority)
Low Writes (High Priority)

Questions

What kind of storage format do you recommend for a non-raided ubuntu system - ext4/xfs
What are the configuration settings I should look into
Any form of settings would improve usage of ram over flash drives
Should I reduce maintenance / vacuum frequency.
Block Size for Disk.
Separate disk for WAL ?

To reduce the number of checkpoints as much as possible, increase max_wal_size and checkpoint_timeout. The price is longer recovery time after a crash.
Make sure that shared_buffers is not too low, so that there is no memory pressure that forces dirty pages out to disk between checkpoints. You could experiment with setting bgwriter_lru_maxpages = 0, so that the background worker does not proactively clean out pages between checkpoints.
Set wal_compression = on so that full page writes are compressed (at the expense of CPU time).


Compile a PLSQL package code that was passed to an sqlplus session via a shell script? I have two files: (1) and (2)

(1) A Shell script - wrapper.sh

#!/bin/sh
echo "start of shell script"

FILE_NAME="XX_PKGb.sql"

sqlplus user/password @sql_deploy.sql $FILE_NAME

echo "end of shell script."

exit $RETURN_CODE

(2) A plsql script - sql_deploy.sql

set serveroutput on

set term on

set echo off

set feedback off

set define on

set verify off

define SQL_SCRIPT=&1

declare

  v_sql            varchar2(240);

begin

    v_sql := 'ALTER SESSION SET CURRENT_SCHEMA = XXEOM';

      --alter the schema (this works)

      EXECUTE IMMEDIATE v_sql;

      --compile the package (this throws an error)

      @@'&SQL_SCRIPT';

end;
/

exit;

The file XX_PKGb.sql is also present in the same location as the above two files. When I run the shell script, i see that the sqlplus session executes the sql_deploy.sql script, it was able to alter the schema, however, while compiling the package, it threw the below error. How do I compile the piece of code in this sql file please:

CREATE OR REPLACE PACKAGE BODY XXG_PKG
*
ERROR at line 15:
ORA-06550: line 15, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
( begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << continue close current delete fetch lock
insert open rollback savepoint set sql execute commit forall
merge pipe purge

Running XX_PKGb.sql (a package create statement) from with your PL/SQL block is not allowed. If you must do it there then it needs to be dynamic SQL.

The better way is to simply run it from SQLPlus;

set term on

set echo off

set feedback off

set define on

set verify off

ALTER SESSION SET CURRENT_SCHEMA = XXEOM
/
@XX_PKGb.sql
/
exit


How often is Postgres parsing a foreign table based on file_fdw? I have a foreign table defined with file_fdw, which reads the data from a CSV file. Postgresql parses the CSV file and converts the date for example from YYYYMMDD to YYYY-MM-DD. This works fine.

My question is how often gets the CSV file parsed? Does it get parsed on every select? Or does it get cached? And if so how long does it get cached?

The file is read whenever the foreign table is scanned, which can happen several times during processing of a query.

There is no caching except the file system cache of the operating system.


One to one relationship or another approach? I need some addition thoughts on how to approach a database design I'm working on.  The issue is I have three related items...

* Clan (has many games)
* Game (belongs to a clan, has many teams)
* Team (belongs to a game)

Clan -< Game -< Team

Each of them is also treated as a "Site" and they can only ever have one site.  Each site has a standard set of properties, but Clan / Game / Team each have their own unique properties.  It's like Clan, Game & Team are subtypes of a Site.  How would I best model this in a database as the relationships go?

Idea 1: A 1-to-0/1 relationship between multiple tables (doesn't feel like it's a good idea).

Sites
  object_id (primary key, uuid)
  + additional site properties
Clan
  object_id (primary key, foreign key sites.object_id)
  + additional team properties
Game
  object_id (primary key, foreign key sites.object_id)
  + additional team properties
Team
  object_id (primary key, foreign key sites.object_id)
  + additional team properties

Idea 2: Clan, Game & Team each have a column "site_id" that is a foreign key to "site.id".  This makes sense but doesn't provide a way in the database to enforce one Clan/Game/Team per site.

Idea 3: A relationship table, (id, item_type [clan,game,team], item_id, site_id), with a unique constraint on the site_id.  I'm sure this is a "no-no" as I can't do any foreign keys on item_id.

Idea 4: Multiple relationship tables (clan_site, team_site, game_site), still doesn't solve limiting a site to only one item.

Idea 5: Have clan_id, game_id and team_id columns added to the site table.  Although this would work for foreign keys, it doesn't seem right as only one of those columns would hold a value.  Can't think of a way to stop two or more columns being populated and since NULL is supposed to be unknown, would that mean I should really have: clan_id = 2, game_id = 0, team_id = 0 ?

I'm probably missing something obvious or looking at this from the wrong side. :)

To remove extra fields the best approach (In my eye of course) would be to insert site_id to clan,team and game tables.

In the site table we have site_id which is primary key(unique) and we have a field in the name of url.

  url field should be unique.
  one-to-one relationship between your three tables with site table.

This is the way to go.


Restricting Database Access/Functions to User Groups? We've been having issues with Databases being DROPed or RESTOREd WITH REPLACE with the incorrect backup etc...

I'm developing a plan right now on how to restrict access and I have some questions.

I'm familiar with SSMS but still am not super experienced with it, especially when it comes to "permissions".

We want everyone to be able to run querys/inserts/etc... on the databases and be able to take backups.

However, ideally we want them to have to submit a ticket to either restore a database or drop a Database completely. (And let me know if there's maybe something else I'm missing).

All the SQL Server instances are kept on a server. Lets call it TEST. On server TEST there are instances of SQL 2005/2008/2012/2008R2. Within server TEST there are separate Windows Server security groups; DEVS, QA, DBA, MANAGERS, etc.

Is there a way to restrict access to groups other than the DBA Group?  What would be the best way to do this?  We would want it restricted for the whole instance (Aka 2008/2012 etc..) instead of on a per database basis.

Server roles are made for this.

Read this for the details http://msdn.microsoft.com/en-us/library/ms188659.aspx

The dbcreator role looks like what you are looking for.


Setting a SQL Server instance back to multi user mode I had to restore a database, but some of the users were still connected, and had left for the day. Previously, I had set the database to single user, now I do not see that option.

So, I made a change at instance level, setting Properties/Connections/Maximum number of concurrent users to 1, disconnecting all other users from the server.

I restarted the SQL Server service, but now I am unable to access the database at all. The database is up and running, but I can't open a query window or right click on Properties to revert the change.

How can I fix this?

First thing, never set Max Number of Concurrent Connections on the server properties page to 1. This option can even prevent administrators logging in, but in any case the Dedicated Administrator Connection can still be used.

Restart services using SQL Server Configuration Manager, try to login and if not successful, use the Dedicated Administrator Connection.
Right click on the Instance name, and change the value for Max Number of Concurrent Connections to 0 (which means maximum) and restart services.

Sometimes, after setting Max Number of Concurrent Connections to 1, on login you may face the following error message:

  Cannot open user default database. Login failed.
  Login failed for user ‘UserName’

To fix this: In the login window, click on the Options button and in the default database item, select a database like tempdb and then press login.

If the database is in Single_User mode

Using T-SQL:

USE [master];
GO
ALTER DATABASE [YourDatabaseNameHere] SET MULTI_USER WITH NO_WAIT;
GO

Using SSMS:

Right click on required database --> Properties
On the left of Database properties window, click on Options
On the righthand side, scroll down and at bottom you will find a status section
Change the Restrict Access value to multi_user

If the database is in Single_User mode, and you want to change it to Multi_User, but can't access it as someone else already have the connection, then:

If you have ALTER ANY CONNECTION rights (part of sysadmin and processadmin fixed server roles), run SP_WHO or SP_WHO2, find the process using the target database and execute:

KILL processid (Ex: KILL 87)

then execute above T-SQL to bring your database back to multi_user.
Or, you can detach and then attach your database and execute the T-SQL above to bring it to multi_user. This is not recommended on production servers.


Why should I use InnoDB and MySql instead of XtraDB and MariaDB? Why should I use InnoDB and MySql instead of XtraDB and MariaDB, except that InnoDB and Mysql happen to be installed by default on my servers?

Why isn't MariaDB and XtraDB installed by default everywhere?

Performance enhancement may be possible without major upgrade to the next version of MySQL or a Migration to MariaDB.

MySQL is the default database by which everyone else creates forks or introduces new things

Different Storage Engines

TokuDB
PBXT
ScaleDB
and Others

Improved Storage Engines

XtraDB over InnoDB
TokuDB over InnoDB

New Features not available in Oracle's MySQL

Multisource Replication
Progress Meter on Commands : Is there a progress indicator for OPTIMIZE TABLE progress?

To be honest, some people upgrade to MariaDB for nontechnical reasons

It's the latest buzzword in the MySQL Universe in years
Redhat want to include MariaDB instead of MySQL in future releases
Monty Widenus really hates MySQL in the hands of Oracle
Hoping for Performance Improvements across the board just doing the upgrade (no due diligence)

Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
Oct 05, 2011 : Query runs a long time in some newer MySQL versions

EPILOGUE

Before deciding on moving over to MariaDB (or any other MySQL fork), you need to be sure you understand the features, improvements, and the configuration settings, and you actually follow through on the settings to make those improvements come to life. See my post How do I properly perform a MySQL bake-off?

In light of all this

If you can enhance performance without an upgrade, you save yourself needless work
If you really need AND UNDERSTAND features, then upgrade
If you benchmark MySQL and MariaDB on the same level playing field (same configurations), decide on which product based on the significant performance improvements (or lack of improvements).


What is this view being used in my query? I'm running EXPLAIN PLAN on this query:

SELECT DISTINCT l.item_num, l.repl_code
FROM item_w l
JOIN item i ON l.item_num = i.item_num
LEFT JOIN item_w r ON l.item_num = r.item_num AND l.repl_code = r.whse_code
WHERE r.whse_code IS NULL
  AND l.repl_code IN (SELECT DISTINCT whse_code FROM item_w)
  AND i.sa_item NOT LIKE 'Z%'
ORDER BY l.item_num, l.repl_code

The query works as desired but the plan is telling me that it is using objects named VW_DTP_F6D0ECAD, VW_DTP_53909802 and VW_DTP_69531A7E which I'm not able to find in my database.

What are these objects and how are they being created?

Views starting with VW_DTP showing up in the execution plan is the result of one of the cost-based query transformations available since 11.2, called distinct placement.

It happens with statements using the DISTINCT clause, just like yours. It is an absolutely acceptable an quite obvious execution plan to join the tables and filter the rows, and after that, find distinct rows. However, finding distinct rows at the time of accessing the tables, before joining them, may be more effective, and the database may choose to transform the query based on the estimated cost.


Mysqldump from production database, every morning Our Db is about 2GB in size.
We run a scheduled job at 3am every morning, when traffic is pretty low, where we export the whole db and sftp it off site.

Is it bad practice to do this on a production db?
The slow query log always grows with 10 or so queries when doing this. Open/opened tables also grows a bit.

So, is this not a good backup strategy? What's a good/standard alternative to this? Is it even bad for the db somehow?

Thanks.

EDIT

We're on a Linode 4096 where the DB is the only thing run on that server.
It has SSD's but not world class. We're using 2GB of the 4GB memory for the innodb_buffer_pool.
The command is as follows:

mysqldump --opt --u USER -pPASSWORD DATABASE | gzip > /path/to/database.sql.gz

since the database size is large you should make the dump command like the following:

mysqldump -u USER -p --single-transaction --quick --lock-tables=false --all-databases (or) DATABASE | gzip > OUTPUT.gz

--quick:This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out

--single-transaction: This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.

--lock-tables=false : option stops MyISAM tables (if they exsit) being locked during the backup.


Add foreign key constraint to an existing table column, referencing a column in a newly created table Let's say I have one table defined as this

CREATE TABLE sales (
    agent_id   integer references agents(agent_id),
    sale_date  date,
    amout      numeric(10,2)
);

Then an ETL process fills this table with data

INSERT INTO sales VALUES
    (1, '2019-01-01', 1031.11),
    (1, '2019-01-02', 525.44),
    (1, '2019-01-03', 323.99);

But later, I need to add a table with actual processed dates, such as

CREATE TABLE dates (
    date_idx   date primary key,
    year       integer not null,
    month      integer not null,
    day        integer not null
);

and want to add a foreign key constraint on the old table:

ALTER TABLE sales
    ADD CONSTRAINT sales_date_fk FOREIGN KEY (sale_date)
    REFERENCES dates (date_idx);

Naturally, I get the following error:

  ERROR: insert or update on table "sales" violates foreign key constraint "sales_date_fk" Detail: Key (sale_date)=(2019-01-01) is not present in table "dates".

I know I can work around this by deleting/truncating all data in sales or prefilling dates before adding the constraint, but I would prefer not to if I can avoid it.

Is there any way to accomplish this?

I'm using Postgres 11, but this fiddle running 9.6 shows what I've tried to explain here.

Select all distinct date from sales tables and insert data into the dates tables. After populating data into that table try to create the foreign key constraint.


Setting up Master-Master Replication in MySQL on a live system I've just recently taken on the task of looking after our mySQL databases, and have been given the task of setting master-master replication. We have a live server which has a mySQL database which is constantly receiving data and a backup server which has been configured as a replication slave to the master. What I need to do now is set the live server up as a slave to the backup. I have an idea of what is necessary, and I guess I'm just looking for someone to confirm I've got it right before I go and do it (or tell me a better way of doing it).

Create slave user on backup-server
Grant replication privileges to the user
stop slave on backup-server
set database lock(read only) on live-server so no new data can be added (and hence replicated to backup)
Get master status from backup-server
-open new ssh session to live-server and issue change master command to point at backup-server
start slave on live
unlock database on live
start slave on backup

create replication user on backup
configure replication on live as slave and backup as master
start slave
profit!


Are "column names" optimized in PostgreSQL JSONB jsonb_agg(*)? Using this SELECT:

select 'some_table' as table_name, jsonb_agg(t.*) as content
from some_table t;

If some_table had 1000 rows, are the "column names", which are repeated for each row in "content", sent over the network 1000 times? Or is that somehow optimized away? If the column names are really reapeated once per row, we would spend more bandwith for them, then for the values.

It is not optimized.  Each column name, as a JSON key, is sent in full, multiple times.  You could try connecting over SSL and turning on compression, but for security reasons that is disabled by default in most systems, and is getting increasingly difficult to turn on.

  If the column names are really reapeated once per row, we would spend more bandwith for them, then for the values.

If that is the highest cost you have to pay for gratuitous use of JSON, then consider yourself lucky.


EXCEL sheets batch input to Access I have a lot of Excel datasheet (in one file)

I want to batch input to Access

but I can't find a sample code to do it

the name of datasheet in access is same as in excel

is there any code of VBscript can do it?

maybe in access or excel vba?

or any simple software?

I would use SSIS to do this. Try these steps:

I would start out by creating an object variable to store your datasheets' names
I would then create a VB task to pull out the names of your spread sheets and put the results into the object variable
Then create a foreach loop that uses the object variable as the collection
create another variable and toss in the name of the current sheet
add a data flow task inside the loop that uses the the current sheet as your data source
add your access table as your destination.

Here is the VB script you can use to pull out the excel metadata. You need to input the variables from your foreach loop to get this to work.

   Public Overrides Sub CreateNewOutputRows()

    Dim excelFilePath As String = Me.Variables.ExcelFilePath.ToString()
    Dim strCn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" + excelFilePath + ";Extended Properties=Excel 8.0"
    Dim dtTables As DataTable
    Dim tableName As String

    Dim cn As OleDbConnection = New OleDbConnection(strCn)
    cn.Open()
    dtTables = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

    For Each row As DataRow In dtTables.Rows
        tableName = row("TABLE_NAME").ToString()

        OutputBuffer.AddRow()
        OutputBuffer.EXCELFILEPATH = excelFilePath
        OutputBuffer.TABLENAME = tableName

    Next

    cn.Close()
    OutputBuffer.SetEndOfRowset()
End Sub

It's a bit tricky but very do-able. Best of luck.


Unique indexed column in where clause and mysql execution after finding first row Suppose in a table there are 2 columns: login and password

login is indexed as unique index, password not indexed.

query: SELECT * FROM mytable WHERE login = 'Jhon'
query: SELECT * FROM mytable WHERE login = 'Jhon' AND password = '123'

After finding first row, will mysql stop or continue searching?

What will mysql do in first case 1. and 2. ?

If the constraint on login is unique... any serious sql database vendor will stop searching at the first and only possible occurence met.

In the second case, it will stop at the first and only possible occurence, and returns the record if the password is '123', nothing if not.


MariaDB ability to work in parallel across multiple db users How exactly does MariaDB handle the scenario with multiple users and one of them hits some sort of a limit. How would this affect other users?

I would assume one user's 120 second query would not shut down the db for others?

Any links to official documentations much appreciated.

Each connection is controlled by its own limits.  The limits are initialized by global settings, but run independently for each connection.

Separate connections will be accessing common resources, so there will some contention (CPU, I/O, etc), but not of the type you described.

"What you do in Vegas stays in Vegas."


want to download 3GB database table from my development mysql server I need to download database structure with data. Total size of the DB is more than 3GB. If i use phpmyadmin it is not working. I have also tried by increasing the execution time in php.ini. I need some tool to import and Export the data base. My database server is Mysql server.

My requirement is any tool that will help to download Large database and Upload to another server. It should hold the download when internet fails. IF internet is ready it should continue the download process.

The answer already existing haven't worked for me. My issue is I need to export and import data from one server to other. if Internet Connection fails. It should not start a fresh export. It should continue the incomplete process.

The simple answer is "use the command line tools", these will run until complete assuming you have enough disk space, to produce the backup then draw that down to where you want it (if not the same server) via HTPS, SFTP, or similar.

The other option of course, unless you need up-to-the-minute data, is to take a copy of the latest backup. If you don't have regular backups already then look into implementing them otherwise you are setting yourself up for a potential world of hurt later.

For more detailed/specific answer you'll need to update your question to describe you environment and what you have tried so far in more detail. Is this your own server or VM, a work machine you have limited access to, a shared host, ...? Linux/Windows/other? What actually happened on your attempts other than "didn't work"? A long delay then nothing, a long delay then an error message, an error message quickly, a fire is produced but is empty or incomplete, ...? If an error message: what was the text of that message?


What is the strict difference between key and constraint in SQL Server? In SQL Server, the 'keys' folder includes primary, foreign and unique keys, while the 'constraints' folder, for example, check and default. But unique can also be considered a constraint, so why it is kept under the 'keys' folder in SQL Server? Is it because it represents alternate keys in logical modeling?

I want to know the strict definition of constraint and key which I cannot find. Thanks.

A unique constraint and a unique index are physically implemented the same way and have the same end result. Since a unique constraint is actually physically enforced via a unique index, and since both can impersonate a primary key in terms of relationships with other tables, this is why you will see it under "keys" - even if you say ADD CONSTRAINT you've still created a constraint that acts and smells like a key.

What people tend to do is create them based on the logical thing that represents their intention. The typical guidance I see is as follows:

Is it being created primarily to enforce uniqueness? Then it's a constraint.
Is it being created primarily to improve performance of certain queries or operators? Then it's an index.

These aren't hard and fast rules, and of course you may want to use different rules. The salient part of these rules, though, is not where these things appear in Object Explorer, but rather what the script says about the intent (ADD CONSTRAINT ... UNIQUE vs. CREATE UNIQUE ... INDEX).

"Constraint" and "key" as words themselves overlap, but they are not interchangeable. A NOT NULL or DEFAULT or CHECK constraint does not relate at all to a key, and in fact two of those effectively violate the definition of a key. On the flip side, any type of key you can create fit the definition of a constraint, since that word is far less specific.


Why does SQL Server run a subquery for each row of the table it's qualifying? This query runs in ~21 seconds (execution plan):

select
    a.month
    , count(*)
from SubqueryTest a
where a.year = (select max(b.year) from SubqueryTest b)
group by a.month

When the subquery is replaced with a variable, it runs in <1 second (execution plan):

declare @year float
select @year = max(b.year) from SubqueryTest b
select
    month
    , count(*)
from SubqueryTest where year = @year group by month

Judging from the execution plan, the "select max..." sub-select is run for each of the millions of rows in "SubqueryTest a:, which is why it takes so long.

My question: Since the sub-select is scalar, deterministic and not correlated, why doesn't the query optimizer do what I did in my second example and run the subquery once, store the result, then use it for the main query?  I'm sure there's just a hole in my understanding of SQL Server, but I'd really like help filling it - a couple hours with google haven't helped.

The table is just over 1gb with almost 28 million records:

CREATE TABLE SubqueryTest(
  [pk_id] [int] IDENTITY(1,1) NOT NULL
  , [Year] [float] NULL
  , [Month] [float] NULL PRIMARY KEY CLUSTERED ([pk_id] ASC))

CREATE NONCLUSTERED INDEX idxSubqueryTest ON SubqueryTest ([Year] ASC)

The slow plan isn't calculating the MAX for each row in the outer query.

In fact it never explicitly calculates it at all.

It gives a plan similar to

WITH CTE
     AS (SELECT TOP(1) WITH TIES *
         FROM   SubqueryTest
         WHERE year IS NOT NULL
         ORDER  BY year desc)
SELECT month,
       count(*)
FROM   CTE
GROUP  BY month

Slow Plan (Estimated Row Counts)

You have a non covering index on year asc so it scans that backwards to get the rows in the first year (shows as a seek because of the implicit IS NOT NULL predicate).

Unfortunately it doesn't seem to differentiate between TOP 1 and TOP 1 WITH TIES when estimating row counts.

In this case it makes a huge difference. (estimated 2 key lookup vs actual 4,424,803) so you get an inappropriate plan.

Slow Plan (Actual Row Counts)

You could consider adding month into the index on year either as a key or included column to make the index covering. The benefit of adding it as a secondary key column would be that it could then feed into a stream aggregate without an additional sort (though for only 12 distinct values a hash aggregate would be fine anyway).

A non covering index on such a non selective column is really pretty useless for the vast majority of queries. The index is totally ignored by the "fast" plan which ends up doing a parallel scan on the whole table and evaluating the predicate on all 27,445,400 rows (in preference to performing the huge number of lookups).


MySql one time event never runs? Please have a look at below events

1) create EVENT Test1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 20 second ON COMPLETION PRESERVE ENABLE DO ...

2) create EVENT Test2 ON SCHEDULE EVERY 20 SECOND STARTS CURRENT_TIMESTAMP ON COMPLETION PRESERVE ENABLE DO ...

I expect event Test1 to run one time after 20 seconds but it never runs.
Event Test2 is working fine.

Any idea? Thanks.

Ok sorry it is the alter that is not working

At first I did

CREATE EVENT Test1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 20 second ON COMPLETION PRESERVE ENABLE DO

Then shortly after I did

ALTER EVENT Test1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 20 second ON COMPLETION PRESERVE ENABLE DO

I expected event Test1 to run again in another 20 secs but it didn't.


MySQL - Export two columns from large table without causing a lock I am a sysadmin who doesn't do a ton of dba stuff. For a project, I have access to a production server--which I am not the sysadmin for--with an enormous 40,000,000 row >10 GB MySQL InnoDB table. I want to export two small columns, one of which is an INT(11), and the other of which is a VARCHAR(20) from that table to a CSV or .SQL file (either is fine, I'll write a crosswalk for the next step no problem).

We can call the columns ColumnA and ColumnB and the table SampleTable.

MySQLdump is not the right tool for this because I can't specify the columns and I don't need to export a massive massive table just got two tiny columns.

I know I can do a SELECT INTO statement (either to create a new table with just the columns or to do a SELECT INTO OUTFILE to skip the intermediate step), but I am concerned that this will cause a table level lock on a production server. The table is InnoDB.

What's my best bet to avoid inconveniencing any live traffic on the server or locking anything?

Thanks

Many experts share many ways on how to overcome this problem. These are my suggestions to play a safe game.

Try to set the below command in a seperate session.

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
use db;
SELECT COLA, COLB into outfile '/tmp/data.csv' from TABLE_NAME;
COMMIT;
exit;

Doing by this way the SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent.When you say not-consistent it means recently changing records i.e.. DML transactions that are currently in process will not be read. I assume which is in your case it is acceptable. This is also called a “dirty read.” Otherwise, this isolation level works like READ COMMITTED.

If I were to be you, the below order is what I follow.

Check for non-peak hours for DB server also for the table that you mind to do select. By this the outfile reads/write, IOs shouldn't cost much to the server.
Manually run this in the server back ground. And keept monitoring the server health.

$ cat bg_loaddata.sh

mysql -uroot -p*** << EOF
use db;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT COLA, COLB into outfile '/tmp/data.csv' from TABLE_NAME;
COMMIT;
exit;
EOF

$ nohup ./bg_loaddata.sh > bg_loaddata.log &

If you see any inserts or updates that are taking longer time to complete in show processlist; You may wait for sometime, but if it is causing severe turbulence then goahead and kill the connection id in processlist for your select ...into outfile ...  [I'm 99% sure this will not happen, but always to be prepared for any back fires]

By this way you can be sure for N number of records to be retrieved it taks N number of seconds, and no issues can be faced by doing this way. And whenever you need this to be run next time put the script in cron and have a sound sleep. :) [Ofcourse you have monitoring metrics to wake you up]-> Just to be aware before someone surprises you with the NEWS.

All the best !!


In mysql if table fit into buffer pool is there any benefit to have partition? Lets say I have a table with 100M rows. All table have enough space on buffer pool. So is there any performance or others benefit to create partition on that table?

NB: Storage engine - InnoDB

If your purge strategy includes time-specific purge eligiblity, partitions could be helpful.  For example, if every quarter, you will purge everything over 5 years old, it will be in your best interest to have a quarterly data table plan in place.  Taking a table out of service is quicker/cleaner than DELeting a quarter's worth of data rows.  innodb_file_per_table=ON will be needed to be effective.  Filenames need to represent the datacontent, such as 2018Q1, 2018Q2 etc or possibly 2018W01 - W53 if you want calendar weeks to be able to purge.  Dream big, plan carefully.

For alternative to simple filenaming strategy as above,

Read more on: http://mysql.rjweb.org/doc.php/partitionmaint


I have to update all the rows of a Column in a table. Here is my query So My tables are

People:

People_name(VARCHAR 100),
People_id (INT, PRIMARY KEY AUTO INCREMENT)

Tracks:

Track_id(INT PRIMARY KEY),
People_id_references (int ,Foreign key references People(people_id)),
Track_title(VARCHAR 100)

top100:

artist varchar(100),
id(int, primary key),
track_title(varchar100)

All the columns in above tables are having values, except in people_id_references(this is in tracks table). top100 has many other columns, but for the sake of clarity I did not mention them, and it is no way connected to people or tracks tables. The data from top100 is present in track and people tables, this is done using some insert queries. I want to import values from people_id (it is in People table).

So my query is:

UPDATE tracks
SET People_id_Reference = (SELECT People_id
                           FROM People
                           RIGHT JOIN top100
                           ON
                           People_name=top100.artist LIMIT 1)
                           WHERE People_id_Reference IS NULL;

But that inserts same people_id into all rows of People_id_Reference. Is there any other way?

Try this:

UPDATE tracks t
INNER JOIN top100 t1 ON t.track_title = t1.track_title
LEFT JOIN People p ON t1.artist = p.People_name
SET t.People_id_Reference = p.People_id
WHERE t.People_id_Reference IS NULL;


Query not recognize alias I have the following query, the alias it's called DELAY, works fine, but when I try to run the first query adding AND DELAY < 0, the alias is not recognized.

I tried to create the alias QUERY AS DELAY or QUERY AS "DELAY" but no success.

The strange thing is that the alias DELAY in the order by doesn't generate errors.

 SELECT ITK_TCK_ID,
                   ITK_OPENED_DATE,
                   TO_NUMBER(ROUND(ROUND(GET_HS(ITK_OPENED_DATE,
                                                SYSDATE,
                                                ITK_NORMAL_BEGIN,
                                                ITK_NORMAL_END,
                                                ITK_WEEKEND_BEGIN,
                                                ITK_WEEKEND_END,
                                                ITK_FREEDAY_BEGIN,
                                                ITK_FREEDAY_END),
                                         GET_HS(ITK_OPENED_DATE,
                                                SYSDATE,
                                                ITK_NORMAL_BEGIN,
                                                ITK_NORMAL_END,
                                                ITK_WEEKEND_BEGIN,
                                                ITK_WEEKEND_END,
                                                ITK_FREEDAY_BEGIN,
                                                ITK_FREEDAY_END)) -
                                   ITK_EXPIRATION_TIME,
                                   2)) DELAY
              FROM INSTANTIATED_TICKLERS
             WHERE ITK_AR_SBP_ID = 'PEAJU_PP' -- P_WGR_ID
             AND DELAY < 0
             ORDER BY DELAY DESC;

Any suggestions?

That's because the WHERE executes before the SELECT. You could try calculating  DELAY inside the WHERE

WHERE ITK_AR_SBP_ID = 'PEAJU_PP' -- P_WGR_ID
AND TO_NUMBER(ROUND(ROUND(GET_HS(ITK_OPENED_DATE,
             SYSDATE,
             ITK_NORMAL_BEGIN,
             ITK_NORMAL_END,
             ITK_WEEKEND_BEGIN,
             ITK_WEEKEND_END,
             ITK_FREEDAY_BEGIN,
             ITK_FREEDAY_END),
        GET_HS(ITK_OPENED_DATE,
             SYSDATE,
             ITK_NORMAL_BEGIN,
             ITK_NORMAL_END,
             ITK_WEEKEND_BEGIN,
             ITK_WEEKEND_END,
             ITK_FREEDAY_BEGIN,
             ITK_FREEDAY_END)) -
        ITK_EXPIRATION_TIME,
      2) < 0


Local package won't compile when remote package recompiles. (Oracle) We have a package on a 10.2.0.4 database(A) and a package on an 11.2.0.2 database(B) that references A's package.  When something on A changes that requires the package on A to do an implicit re-compile, sometimes the package on B that should also implicitly re-compile goes invalid.  The error it shows is PLS-00907:

 Cause: PL/SQL is unable to find and load a library unit that was
        previously available.  This typically happens when you try to
        load a unit which references another library unit that is
        non-existent or invalid.
Action: Provide the needed library unit.

The strange thing is that the package on A is valid and can be recompiled successfully, yet recompiling the package on B is still unsuccessful.  It is only when we make an inconsequential change to the package on A that the package on B will then compile successfully.  This problem has been occurring every few weeks for the past several months.  It may have started when we upgraded B from 11.1 to 11.2.

The database link on B is a private database link using a specific username/password to connect to A.  The remote_dependencies_mode parameter is set to SIGNATURE on both databases.

This problem is similar, but not the same as MOS Bug 9719541, and Doc ID 166680.1.  Anyone have any idea what could be wrong?

It is obviously a bug, so should go to Oracle support.
As a workaround, are you able to switch that call on the 11gR2 to dynamic SQL to avoid the hard-coded dependency.

You might be able to do that through a wrapper package on the 11g side.
Rather than

11g package calls 10g package

it goes

11g package calls 11g wrapper and 11g wrapper (dynamically) calls 10g package.


Get ORA-01157 when startup database I created a datafile /tmp/encrypted_ts.dbf. When the machine started again, the datafile was gone. When I started it I got ORA-01157,  the details are:

ORA-01157: cannot identify/lock data file 54 - see DBWR trace file
ORA-01110: data file 54: '/tmp/encrypted_ts.dbf'

So I ran alter database datafile 54 offline drop;, and I got ORA-01516. How can I startup the database again?

Let me show you what Oracle says about your errors.

  ORA-01157: cannot identify/lock data file string - see DBWR trace file

  Cause: The background process was either unable to find one of the data files or failed to lock it because the file was already in use.
  The database will prohibit access to this file but other files will be unaffected. However the first instance to open the database will need to access all online data files. Accompanying error from the operating system describes why the file could not be identified.

  Action: Have operating system make file available to database. Then either open the database or do ALTER SYSTEM CHECK DATAFILES.

  ORA-01516: nonexistent log file, data file, or temporary file "string"

  Cause: An attempt was made to use ALTER DATABASE to rename a log file, data file, or temporary file; or to change attributes of a data file or temporary file (for example, resize, autoextend, online or offline); or to re-create or move a data file. The attempt failed because the specified file is not known to the database's control file or is not of a type supported by the request.

  Action: Specify the name or number of an existing file of the correct type, as appropriate. Check the relevant V$ table for a list of possible files.

In this case, you can recreate the tablespace that the datafile belongs to:

If the database is down, mount it.

STARTUP MOUNT;

Offline drop the datafile.

ALTER DATABASE DATAFILE 'full_path_file_name' OFFLINE DROP;

If the database is at mount, open it.

ALTER DATABASE OPEN;

Drop the user tablespace.

DROP TABLESPACE tablespace_name INCLUDING CONTENTS;

Recreate the tablespace.

I have tested the above steps to simulate your problem, created new tablespace with the datafile in /tmp directory, and it worked perfectly for me.


In Memory Database Remove File and FileGroup I have a SQL Server 2016 database and I configured in-memory table.
I want to drop this configuration, then I drop that table.
When I want to delete that file and filegroup, I got following error.

USE [InMem_Test]
GO
ALTER DATABASE [InMem_Test]  REMOVE FILE [InMemFile]
GO
ALTER DATABASE [InMem_Test] REMOVE FILEGROUP [InMemFileGroup]
GO

  Msg 41802, Level 16, State 1, Line 3 Cannot drop the last
  memory-optimized container 'InMemFile'. Msg 5042, Level 16, State 11,
  Line 5 The filegroup 'InMemFileGroup' cannot be removed because it is
  not empty.

Currently, the only way to remove Hekaton is to drop the whole database.
Probably not your first choice, but it doesn't look like Microsoft is willing to change course on that.