texts: is it possible to using index in unix datetime colum in MySQL 5.7 when using function

texts:
is it possible to using index in unix datetime colum in MySQL 5.7? I define my database table like this:
CREATE TABLE `report_envelope_app_list` (
  `created_time` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL
)

the created_time store unix timestamp,now this is my SQL:
 select max(user_id) as user_id
 from report_user
 group by user_id, date_format(FROM_UNIXTIME(created_time/1000), '%Y-%m-%d')

but now the colum created_time contains function and could not using the index, is it possbible to using index, I have to add a column to store date like %Y-%m-%d? I tried to add a generated column like this:
ALTER TABLE report_user
ADD COLUMN statistic_date varchar(16)
GENERATED ALWAYS AS date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d') STORED;

but failed.


texts:
I have a program that regularly exchanges a partition with a table.

MyTable is a partitioned table, and MyTableTemplate is an empty copy of this table, with the same structure but no partitioning.

The process is the following:

CREATE TABLE tmp LIKE MyTableTemplate;

INSERT INTO tmp VALUES (...);
// more inserts...

ALTER TABLE MyTable EXCHANGE PARTITION p1 WITH TABLE tmp;
DROP TABLE tmp;

It worked fine so far, until I needed to add a column to the table:

ALTER TABLE MyTable         ADD COLUMN someColumn TINYINT(1) UNSIGNED NULL DEFAULT NULL;
ALTER TABLE MyTableTemplate ADD COLUMN someColumn TINYINT(1) UNSIGNED NULL DEFAULT NULL;

Now when I run EXCHANGE PARTITION WITH TABLE, I get the following message:

  General error: 1731 Non matching attribute 'INSTANT COLUMN(s)' between partition and table

Oddly, if I do not add the column at the last position:

ALTER TABLE ... ADD COLUMN ... AFTER someField;

...then the partition exchange works fine!

What's this "INSTANT COLUMN(s) attribute" and what did I do wrong?

Is this a bug? I'm using MySQL 8.0.12.


texts:
I'm working with a Rails app backed by a MySQL database. One of the more-frequently used models is backed by a table with over 200,000,000 rows (and growing). We've been aware for a while that the way it's set up isn't scalable.

The feature I'm working on at the moment requires me to add a couple new columns to that table, as well as a compound index. My coworker did a test-run on her local dev machine and it took about an hour per column (didn't even test adding the index yet).

We're considering taking a different approach, building a new table with the needed columns, dumping the data from the old table to the new one, then pointing the Rails model at the new table instead. Feels like a short-term solution for a large looming problem, but I'm curious if anyone has insight on how to navigate this problem.

We've also talked about archiving the table's old records. It's a table that gets written to and read from a lot, but only with recent records (the app probably never needs to do anything with records more than a month old).


texts: pgpool 4.2.1 and postgres 11 streaming replication problem

texts:
I've setup a 2+2 pgpool and postgresql cluster.
two pgpool nodes in active/standby configuration  and a streaming replication between databases
All seemed work fine until after some failover and online recovery tests took place.
Suddenly I no longer able to  see the "streaming" status from either "show pool_nodes" or "pcp_node_info commands. all users have the pg_monitor role granted.
I'm unable to understand why I was able to see the "streaming" ( and async) repliction status , but not any more .
Could someone help me to understand why?
postgres=# show pool_nodes;
postgres=# show pool_nodes;
 node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | xxxxxxx  | 5432 | up     | 0.500000  | primary | 0          | false             | 0                 |                   |                        | 2021-03-16 01:37:19
 1       | yyyyyy  | 5432 | up     | 0.500000  | standby | 0          | true              | 0                 |                   |                        | 2021-03-16 01:37:19
(2 rows)

or from the pcp_node_info  command
[postgres@xxxxxxx ~]$ pcp_node_info --verbose -h localhost 0
Password:
Hostname               : xxxxxxx
Port                   : 5432
Status                 : 3
Weight                 : 0.500000
Status Name            : down
Role                   : standby
Replication Delay      : 0
Replication State      :  <<<=== it is empty
Replication Sync State :  <<<=== it is empty
Last Status Change     : 2021-03-16 00:02:59
[postgres@yyyyyyyy ~]$ pcp_node_info --verbose -h localhost 1
Password:
Hostname               : yyyyyyyy
Port                   : 5432
Status                 : 1
Weight                 : 0.500000
Status Name            : waiting
Role                   : primary
Replication Delay      : 0
Replication State      :
Replication Sync State :


texts: The most important oracle database performance metrics

texts:
Team,

I need to collect the top most 10 important Oracle database related metrics from Oracle OEM.
Can somebody help me define them? I could not find document that list the metrics for oracle database specific. This is for Host, Service and Web app stats Host, Service, Web app metrics from OEM

I'd really appreciate any insight.


texts: How to search a given date value in all tables of present in database

texts:
I need a query to search for a particular date 2017-04-01 in every table in my database.

My database has many tables and I don't know in which table this value 2017-04-01 exists.

Please suggest a query which will help me to find the given date value in all the tables present in the database.

Datatype is DATETIME.


texts: Correct way to re-use or recover databases from "PostgreSQL 9.3" to "PostgreSQL 9.5"

texts:
I was using PostgreSQL 9.3, then I installed PostgreSQL 9.5, then I unistalled PostgreSQL 9.3.

Now I want to move the database from the folder of PostgreSQL/9.3/data/base of PostgresSQL 9.3 to PostgreSQL/9.5/data/base of PostgresSQL 9.5.

When I open PostgreSQL 9.5 from pg_admin I can't find the database.

I know copy past is not the correct way, so what is the correct way to move this databases from pg_9.3 folder to pg_9.5 so I can use them like any database.

When I unistall the PostgreSQL 9.3 I still find this folders bin, data, lib, data - Copie.rar.

I am using Windows as an OS.


texts: PostgreSQL function thinks table reference exists when it does not

texts:
So I run this query:

SELECT f_copy_tbl('parts','parts1');

...while working on code based off of this answer and I get this error message:

  ERROR:  relation "parts1" already exists

pgAdmin III doesn't show the table (and I obviously reloaded the left column pressing F5 after clicking on the correct items because it's of cascading reload effect). Just to cover my bases I run the following query:

DROP TABLE IF EXISTS parts1 RESTRICT;

...to which psql returns:

  NOTICE:  table "parts1" does not exist, skipping

So what is parts1 a reference to if not a table?

How many queries? All the queries:

CREATE OR REPLACE FUNCTION f_copy_tbl(_tbl regclass, _newtbl text)
 RETURNS void AS
$func$
BEGIN

-- Copy table
EXECUTE format('CREATE TABLE %I (LIKE %s INCLUDING ALL);', _newtbl, _tbl);

-- Fix serial columns, if any
EXECUTE (
SELECT concat_ws(E'\n'
       , string_agg('CREATE SEQUENCE ' || seq, E';\n') || ';'
       , string_agg(format('ALTER SEQUENCE %s OWNED BY %I.%I;'
                          , seq, _newtbl, a.attname), E'\n')
       , 'ALTER TABLE ' || quote_ident(_newtbl)
       , string_agg(format($$ALTER %I SET DEFAULT nextval('%s'::regclass)$$
                                 , a.attname, seq), E',\n')
       )
FROM   pg_attribute  a
JOIN   pg_attrdef    ad ON ad.adrelid = a.attrelid
                       AND ad.adnum   = a.attnum
     , quote_ident(_newtbl || '_' || a.attname || '_seq') AS seq  -- new seq name
WHERE  a.attrelid = _tbl
AND    a.attnum > 0
AND    NOT a.attisdropped
AND    a.atttypid = ANY ('{int,int8,int2}'::regtype[])
AND    ad.adsrc = 'nextval('''
         || (pg_get_serial_sequence (a.attrelid::regclass::text, a.attname))::regclass
         || '''::regclass)'
);

--Now populate `parts1` table with `parts` table's content...
SELECT f_copy_tbl('parts','parts1');
INSERT INTO parts1 SELECT * FROM parts;

--Now get primary key so we can fix the sequence...
--DECLARE PKEY VARCHAR;
--SELECT pg_attribute.attname INTO PKEY FROM pg_index, pg_class, pg_attribute WHERE pg_class.oid = 'parts1'::regclass AND indrelid = pg_class.oid AND pg_attribute.attrelid = pg_class.oid AND pg_attribute.attnum = any(pg_index.indkey) AND indisprimary;

--Now
--SELECT setval('parts1_' || INTO PKEY || '_seq', (SELECT MAX(pkey) + 1 FROM parts));

/**********
--Now get highest number of primary key *AND* increment it...
--DECLARE PKEY_NUMBER INTEGER;
--SELECT (PKEY + 1) INTO PKEY_NUMBER FROM parts1 ORDER BY PKEY DESC LIMIT 1;

--Fix primary key's autoincrement...
--ALTER SEQUENCE parts1_id_seq RESTART WITH PKEY_NUMBER;
*********/

END
$func$ LANGUAGE plpgsql VOLATILE;


texts:
I have the following trigger:

CREATE TRIGGER SalaryCannotDecrease ON Employees
AFTER UPDATE
AS
IF EXISTS (
            SELECT *
            FROM inserted, deleted
            WHERE inserted.Salary < deleted.Salary
            )
BEGIN
    RAISERROR 30002 'You cannot decrease salary';
    ROLLBACK TRANSACTION;
    RETURN
END;

And this trigger is firing when I try to:

UPDATE Employees
SET Salary = Salary * 1.05

What am I doing wrong here?


texts: Options for having same tables in 2 different data bases

texts:
I need to know what options I have for my next problem:

I have 2 databases (sql server 2012)

first for an ERP
second for an website

For the website's database, I want to use a table (customer) which is created in ERP database.

I need a copy in real time of that table, in website's database. When a customer is entered by erp, it should appear in website too. Vice-versa is not necessary

How can I do this?


texts:
Our vendor application database is very TempDB intensive.

The server is virtual (VMWare) with 40 cores and 768GB RAM, running SQL 2012 Enterprise SP3.

All databases including TempDB are on Tier 1 SSD in SAN.
We have 10 tempdb data files, each pre-grown to 1GB and they never auto-grow. Same with 70GB log file. Trace Flags 1117 & 1118 are already set.

sys.dm_io_virtual_file_stats shows over 50 Terabytes read/written on tempdb data & log files in past month, with cumulative io_stall of 250 hours or 10 days.

We have already tuned the vendor's code and SPs over past 2 years.

Now, we're thinking of placing tempdb files on RAM Drive since we have a ton of memory. Since tempdb gets destroyed/recreated when server is rebooted, it is an ideal candidate to place on volatile memory which also gets flushed out when server is rebooted.

I've tested this on a lower environment and it has resulted in faster query times but increased CPU usage, because the CPU is doing more work instead of waiting on slow tempdb drive.

Has anyone else put their tempdb on RAM in high oltp production systems? Is there any major disadvantage? Are there any vendors to specifically choose or avoid?


texts: How Can I Convert SQL Server 2012 Version 11.0.3000.0 to SQL Server 2012 Version 11.1.3000.0?

texts:
I have SQL Server 2012 with version 11.0.3000.0 .

I need upgrade my version to SQL Server 2012 SP1 because I need install Cumulation 4 in Sql Server 2012 Cumulative Update 4 for use PowerPivot in Multidimensional Database.

Before install Sql Server 2012 Cumulative Update 4 I need Upgrade my SQL Server to Service Pack 1.

How Can I Update My Version to SP1.

I sow this Link and download SQLServer2012SP1-KB2674319-x64-ENU.exe and SQLServer2012SP1-FullSlipstream-ENU-x64.iso but this not work in my Server.

I have this error :


texts: AWS MySQL RDS Replica out of disk space suddenly

texts:
We've got a MySQL RDS instance running on AWS, with a replica. From Cloudwatch I can see that there was a sudden drop in free storage space, of the total allocated storage space of 100GB. How can I investigate what is using the storage? And remedies to attempt to free up the space?

Free Storage Space dropped suddenly from 63GB to 4GB.

I've been querying the replica and suspect running an accidental cross join, which was later cancelled, caused the issue, as the cross join query was ran around the time of the drop.
Light queries still run, larger queries no longer run.

SQL client error for heavier queries now displays:

  SQL Error [3] [HY000]: Error writing file '/rdsdbdata/tmp/MYSKecWe'
  (Errcode: 28 - No space left on device)

There is also a warning in AWS console.

  The free storage capacity for DB Instance: smmainro is low at 3% of
  the provisioned storage [Provisioned Storage: 99.26 GB, Free Storage:
  3.35 GB]

I ran below query to see how much space was in use by the database. Results show data_length as 20,612MB, index_length as 11,954MB and data_free as 614,996MB. So it look slike the total database is about 32.5GB in size, which corresponds to how much data is occupied by the master db. Does data_free represent data which could be available but is currently not available?

SELECT
    sum((data_length)/1024/1024) AS data_mb,
    sum((index_length)/1024/1024) AS index_mb,
    sum((data_free)/1024/1024) AS free_mb
FROM
    information_schema.tables

Would rebooting the master database free up the space occupied by temporary data? From some reading it doesn't seem possible to just reboot just the replica database, is that correct?

Fyi running version 5.7.19-log of MySQL.
Here are some values from running SHOW GLOBAL VARIABLES:

innodb_buffer_pool_size 24,696,061,952 (24GB)
innodb_ft_cache_size    8,000,000 (8MB)
innodb_ft_result_cache_limit    2,000,000,000 (2GB)
innodb_ft_total_cache_size 640,000,000 (640MB)
innodb_log_file_size    134,217,728 (134MB)
innodb_max_undo_log_size    1,073,741,824 (1GB)
innodb_online_alter_log_max_size    134,217,728 (134MB)
max_binlog_cache_size 18,446,744,073,709,547,520 (18.5 Billion GB)
query_cache_limit   1048576
query_cache_size    1048576


texts: MySQL deadlocking on single-column table batch INSERT during transaction

texts:
I've been trying to work around this but haven't found a way besides retrying, which is inefficient for what should be a fairly simple, high-concurrency operation.

I have an InnoDB table on a MySQL 5.6/5.7 DB that has two columns... id + another column (with a unique index). During a transaction, I do a batch SELECT on this table, and then INSERT the missing rows thereafter. However, it's deadlocking due to activity in other simultaneous transactions.

Is there a solution? I've tried setting the transaction mode to READ COMMITTED without seeing any difference.


texts:
From my current query, I obtain this jsonb data :

values: "a1", ["b1", "b2"]

And I want to flatten it on one level only, like this :

values: "a1", "b1", "b2"

Here is a simplfied way to get data in a query (only 2 levels are possible, never more):

SELECT *
FROM jsonb_array_elements('{"test": ["a1", ["b1", "b2"]]}'::jsonb->'test');

I tried to use jsonb_array_elements but my problem is that : I don’t know if it is a json array or not ! Not expert in SQL, I did not find a way to code something like :

SELECT
    IF (is_json_array(list))
        jsonb_array_elements(list)
    ELSE
        list
    ENDIF
FROM jsonb_array_elements('{"test": ["a1", ["b1", "b2"]]}'::jsonb->'test');

For a «zoom out» view of my current data, here is a table-free working test :

with recursive search_key_recursive (jsonlevel) as(
    values ('{"fr": {"WantedKey": "a1", "Sub": [{"WantedKey": ["b1", "b2"]}], "AnotherSub": [{"WantedKey": "c1"}]}}'::jsonb)
    union all
    select
        case jsonb_typeof(jsonlevel)
            when 'object' then (jsonb_each(jsonlevel)).value
            when 'array' then jsonb_array_elements(jsonlevel)
        end as jsonlevel
    from search_key_recursive where jsonb_typeof(jsonlevel) in ('object', 'array')
)
select search_key_recursive.jsonlevel->'WantedKey'
from search_key_recursive
where jsonlevel ? 'WantedKey';

For after, I will use the result in an insert statement :

INSERT INTO table1
SELECT 'someText', value
FROM jsonb_array_elements('{"test": ["a1", "b1", "c1"]}'::jsonb->'test');


texts: mongodb - "replication oplog window" possibly reduced due to profiling - when profiling disabled no change?

texts:
PROBLEM

I have a 3 member replica set (no sharding) running mongodb version 2.4.8.

Using MMS (Mongo Monitoring) I can see  that on 7th July 2014 at 12:00 my "replication oplog window" dropped from 2777.78 hours to 68 hours at its lowest point.

Looking back at my log of changes on this date I can see database profiling was enabled that morning.

"db.setProfilingLevel(0)" to disable profiling was executed an hour ago on all collections and on all members of the replica set (PRIMARY FIRST).

QUESTION

An hour later my "replication oplog window" counter is still showing only 102 hours. Will this take time to increase again or should I be looking for another cause ?

Thank you
Scott


texts:
I have a large database with many relationships. The database can be rebuilt and a migration script for an already existing data base

For security reasons, the URL should not show the ID(int) only UUID

Is there any good advice for such a situation?

converting all IDs to UUID(if so how to do it efficiently?)
can I create an additional column in each UUID table and use it at the address? (if so how to do it efficiently?)
other suggestions?


texts: High Disk I/O after batch deleting from innodb table

texts:
I needed to delete many records from a 500GB innodb mysql table. I thought I made the right choice by running many delete statements to batch delete the data.

I deleted 10,000 rows at a time and it took around 28,000 iterations (280 million rows deleted) to complete. I had breaks between each delete to allow the read slave to not get too far behind.

Now that the deletions are complete, there are two mysql threads that are hammering the disks. One of them is reading, the other is writing. 85% of the I/O are writes.

I've read that there is a reordering operation that happens after a delete but I'm not sure if that's it. The processlist doesn't show anything out of the ordinary. Monitoring tools that have been watching the DB for some time don't show much of anything different either, with the exception that queries are taking longer, disk I/O is maxed out, and there is an increase in dirty buffer pool pages.

Is there anyway to find out what is going on and causing the high disk I/O? I'm also looking for a way to make it stop or decrease it's priority since the database is working fine otherwise.

MySql info: mysql  Ver 14.14 Distrib 5.6.30-76.3, for Linux (x86_64) using  6.2

The table in question does not have a primary key or any unique identifier. It has ~925 million rows in it after the delete.

EDIT 1:

Server has 32GB of ram.

The rest of the mysql data on this server is 150gb.

SHOW CREATE TABLE tablename
CREATE TABLE tablename (
  column1 bigint(11) NOT NULL DEFAULT '0',
  column2 bigint(11) NOT NULL DEFAULT '0',
  column3 bigint(11) NOT NULL DEFAULT '0',
  column4 datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  column5 varchar(64) DEFAULT NULL,
  column5 int(11) NOT NULL DEFAULT '32',
  column7 varchar(64) DEFAULT NULL,
  column8 bigint(20) DEFAULT '0',
  column9 bigint(20) DEFAULT '0',
  column10 bigint(20) DEFAULT '5',
  column11 date DEFAULT NULL,
  column12 enum('src','dst','unknown') NOT NULL DEFAULT 'unknown',
  UNIQUE KEY uniquename (column1,column2,column5,column7,column4,column12),
  KEY column1 (column1,column2,column5,column7,column4),
  KEY column11_index (column11)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Column names changed to generic names.

The goal of doing small batch deletes was to try and not interfere with normal daily operations on the database.

There is a read slave, it performed all the delete operations and it is not suffering from the same high disk IO that the master is experiencing.

EDIT2/Update:

The server has been rebooted. Once the mysql service started (even with no clients connected), it continued to use all available disk resources to write to disk.

Is there anyway to tell why it's writing to disk constantly?


texts: How to model that several variants of a merchandise item may affect its price?

texts:
I am an absolute beginner to relational data modelling. I hope my question will be acceptable here nevertheless.

Let’s say there’s a merchandise. An item of merchandise may have variants.
(While it also has attributes common to all variants).
I don’t want to limit in the data model what the variants can be.
I know the merchandise can vary in size and in color, but I don’t want the model to be limited to these two possible variations. There might be other variants (such as in material or process).
It is not clothing, and the variants may be priced differently.
While I expect mostly sizes to affect the price, I don’t want to impose any such limit.I also wish to anticipate that several variants could combine to affect the final price. Say that a different process could affect the price and different sizes too but colors not. But I want to avoid any fixed limitations in the model.This is what I don’t know how to model properly.

(BTW, variants are identified as such by adding a variant code to the base of their SKU.)


texts: Pulling data into Access from an external API

texts:
[Apologies in advance if this has been asked an answered elsewhere.]

I have recently dived into Microsoft Access 2016 to handle a relatively small (but still too big for Excel) database with somewhat sensitive (read should not live on the internet) information. So far so good.

What I would like to do is be able to pull down information from a publicly available API to update a few columns of a table.

Ex:
For each entry in this table take field A and submit it to the web API.
The API would then return a list of numbers which I then parse and plug into fields B, C, and D.

QUESTIONS:

Is this even possible inside Access?
Where should I look/what should I search for to figure out how to do it?

I would rather not have to pipe it through Excel or some third party service if possible. I am comfortable programming and don't mind picking up a new language but I can't seem to figure out where to start. Any help from "hit the _____ button." to "Searching for _____ will get you started." would be appreciated.


texts: Query Conflicts in Streaming Replication in Postgres 9.4

texts:
When using Streaming Replication in Postgres 9.4, when a query makes the standby Postgres "start to count" the max_standby_streaming_delay, changes being made in the primary:

Are dammed at the primary (not sent to standby) using the wal_keep_segments setting to limit that; OR
Are sent to standby and stored locally (where?) at the standby for later processing?

Scenario 1 would lead to a bigger data loss in the event of a crash in the primary. On the other hand, the scenario 2 may lead to a filesystem being completely filled if the allowed dalay is too big.


texts:
A created a new SQL Job in SQl Server Agent. I followed the steps mentioned in the article
http://msdn.microsoft.com/en-us/library/ms187910.aspx

Still it is in idle state. PLease note that the schedular is prepared to execute every 10 seconds.

Below is the Drop and create script

IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'test')
EXEC msdb.dbo.sp_delete_job @job_id=N'7ac1e514-fec2-4516-b716-f00107fb127e', @delete_unused_schedule=1
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 01/26/2012 14:22:27 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'test',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'No description available.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [rr]    Script Date: 01/26/2012 14:22:31 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'rr',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'insert into [datet](dat) values(getdate())',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N's',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=2,
        @freq_subday_interval=10,
        @freq_relative_interval=0,
        @freq_recurrence_factor=0,
        @active_start_date=20120125,
        @active_end_date=99991231,
        @active_start_time=203200,
        @active_end_time=215959,
        @schedule_uid=N'0ad45ad2-6d8f-4193-a687-f60bf7d5f0e5'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO


texts:
Trying to migrate SQL Server to Azure, MS Data Migration tool indicates need to delete SSIS. Using advice from Stackflow and others, go into programs and features and SSIS is not listed as a component.

Other advice indicates go into setup and remove components/features will be an option. Using the admin login I do not see that option.

Finally, I ran SQL Server features report and SSIS was not on it. It listed DB Engine services, SQL Server replication, Full text search, SSMS, Adv-SSMS and Data Quality Client.

At a loss on how to proceed.


texts: Is it possible to use two parameter files for same export command Oracle 9i

texts:
I want to export my database with the export exp command using a parameter file.
My constraint is that there is some tables I want to export without data, others with.
I'm having the idea of using two parameter files PARAMFILE1.dat and PARAMFILE2.dat.

PARAMFILE1
will contain TABLES parameter, mentioning tables to be exported without data, this by setting ROWS parameter to n.

PARAMFILE2
will contain TABLES parameter, mentioning tables to be exported with data, this by setting ROWS parameter to y (or not mentioning it at all, since it's Yes by default).

Is this possible ? If not, what is the best strategy for tackling this problem ?


texts: MySql show content of ibdata1 file, block rising exponentially, why is it growing so fast?

texts:
I have a database with most of tables being stored on the InnoDB engine, however an alert raised the other day showing up that the ibdata1 file was growing exponentially.

For what I searched so far, there is no way I could resize nor shrink this file rather than start fresh again and rebuild the entire database.

However, I've read that some of the cases where this file rises too fast is when there are several transactions that might not be committing fast enough for the database to manage the file but letting it grow.

Is there a way to see what is inside this file in order to know what is taking most of the space?

Besides, is there a way to put a limit on this file so we can control when rises and when not?


texts: Postgres 9.2 - postgres - FATAL: XX000: relation mapping file "global/pg_filenode.map" contains incorrect checksum

texts:
We have a ~400GB Postgres database  (v9.2.6) (running on CentOS 6.5) that had a problem with free space and with every command (ls, pwd etc) we were seeing a segmentation fault error.  We were able to read the disk and so we copied the pg_data directory and rebuilt the server.  In an attempt to place the pg_data back and start the Postgres service we saw the error:

postgres - FATAL:  XX000: relation mapping file "global/pg_filenode.map"
contains incorrect checksum

I know the easiest answer would be restore the latest backup.  Well, as you would know it, the last pg_base_backup was just over 3 weeks ago and we only retain 1 weeks worth of WAL logs.  So I do not have enough logs to bring the backup up to date.  I know this is a flaw here, I know I learned some lessons.

I tried to use the pg_filenode.map file from the 2 week old backup, but it resulted in the same checksum error.   I will note that a FULL VACUUM was done over this past weekend on one of the larger tables but completed before the failure.

Is there any chance I can recover from this error? Any ideas on possibly reading some of the smaller table data / schema / functions / views to help me in the rebuild process of a new clean slate database.


texts:
Lets say I have the following table

-----------------------------
| user_id   | comment       |
-----------------------------
| 2         | thats cool    |
| 2         | awesome       |
| 3         | i hate this   |
| 3         | okay          |
| 6         | this is weird |
| 6         | hello?        |
| 6         | what is it    |
| 9         | how are you   |
| 16        | too slow      |
| 16        | yes           |
| 17        | alrighty      |
-----------------------------

How can you select two rows per user_id? So my results would be:

-----------------------------
| user_id   | comment       |
-----------------------------
| 2         | awsome        |
| 2         | thats cool    |
| 3         | i hate this   |
| 3         | okey          |
| 6         | this is weird |
| 6         | hello?        |
| 9         | how are you   |
| 16        | too slow      |
| 16        | yes           |
| 17        | alrighty      |
-----------------------------

Is this possible with a single efficient query? Or are sub-selected necessary?


texts:
there is Postgres 9.6 on Redhat
I want to EXPLAIN ANALYZE slow query (quite complex one) and it takes too much time. My session with EXPLAIN ANALYZE consume 100% of one CPU and doesn't finish even after 24 hours.
How to analyze the issue if Postgres can't build an execution plan?


texts:
Suppose we have the following queries:

1.

    SELECT COUNT(*) FROM some_big_table WHERE some_col = 'some_val'

2.

    SELECT COUNT(*) FROM ( SELECT * FROM some_big_table WHERE some_col = 'some_val' )

Does any of the previous queries perform better? Or are they the same?

I'm using Postgresql 9.4 but, are there a big difference to another DBMS?

PS: I'm asking this question because SQLAlchemy, a Python based ORM, executes the COUNT operation by default on a subquery, but there is an option to force it to execute the COUNT directly on the query.


texts: "Mysql2::Error: Table doesn't exist" after power outtage

texts:
The power went out while my site was running and now when trying to access my site running locally on my mac I'm getting this error:

ActiveRecord::StatementInvalid: Mysql2::Error: Table 'mysite.users' doesn't exist:

Is there anything I can do other than dropping the database and recreating it?

EDIT:

I'm getting this when trying to drop the database:

rake db:drop
Couldn't drop MySite : #<Mysql2::Error: Unknown database 'mysite'>


texts: In a CASE Statement, which is faster: "WHEN [field] in (0)" or "WHEN [field] = 0"?

texts:
I'm looking at a stored proc that another guy has written and every WHEN clause is using an IN statement instead of =.  My gut tells me this isn't as fast, efficient, etc.

For example:

CASE WHEN a.Indicator in ('Yes') then 'something'
     WHEN a.STATUS in (0) then 'something 0'
     WHEN a.STATUS in (1) then 'something 1'
     WHEN a.STATUS in (2) then 'something 2'
     WHEN a.STATUS in (3,4,5,6,7,8) then 'something big'
END

Is it faster to use IN instead of =?


texts:
we consider a recovery from failure on running in the following way:

initially log analysis allows to determine the dirty pages

The second phase is to redo all operations validated before failure that are not properly written to the database files.

The third phase is to undo transactions in progress at the time of the outage, which had already made updates in the database files

1. T1:begin
 2. T1:write(P1)
 3. T1:write(P2)
 4. T2:begin
 5. T2:write(P3)
 6. T1:write(P5)
 7. T1:commit
 8. T3:begin
 9. T3:write(P2)
 10. T2:write(P1)
 11. T4:begin
 12. T4:write(P4)
 13. T2:abort
 14. T3:write(P3)
 *Failure*

After the analysis phase what are the active transactions and dirty pages.
What operations are beings replayed (REDO) and what order.
What operations are beings canceled (UNDO) and what order.


texts:
I am trying to auto-archive active database table to archive database table using stored procedure and event in MariaDB10. These are the steps I am following:

Create new table everyday just before midnight in archive database.
copy rows from active table to archive table.
delete all rows from active table.

This seems to work mostly except one issue. PLC is writing to active database table every second. When the above mentioned process is taking place, there is a gap of 2 mins between last entry of archive table and first entry of active table (meaning active table is loosing 2 mins worth of data i.e 120 rows). As per my understanding this 2 mins gap is when rows are copied over to arch table and deleted from active table. There are approximately 87K rows(256MB)generated in a day.

Question is how can I reduce this 2 mins gap (loss of rows/data)? Any help or pointers would be really helpful.

I just noticed that for step 2, I am using INSERT INTO with SELECT, probably this is locking the rows of active table hence PLC is not able to insert new rows to the active table thus loosing the 2 mins of data/rows. IT is innoDB engine. Is this thinking correct? If yes, any way to get around it?

PS: I have instrumentation engineering background (and minimal experience of DBA) and I have inherited this PLC-MariaDB setup. Original DB is not indexed, also, there is no auto-archiving taking place. Thus DB is growing in size and affecting the performance when doing any queries on DB. I have indexed the DB now and performing auto-archiving.


texts: SQL Server TRY..CATCH resume on terminating error

texts:
I'm trying to get a master stored procedure to execute through to completion, where the master SP calls child SPs to perform modular tasks that are independent of each other (i.e. - if one fails, that shouldn't halt execution of the others). One of these child SPs might fail, and I don't know in advance why it might fail, but one scenario I have encountered is that it's trying to select from a table that does not exist. I would like that error logged, and then for the master script to continue. I'm guessing that selecting from a non-existent table is a terminal error, and I want to know if there is a way to continue even on terminal errors.

(Yes, I know that I can interrogate the information schema to determine table existence, but I'm looking to catch errors that haven't been defensively coded against yet - today it could be a missing table, tomorrow it could be something else we haven't considered that brings the master script to a screaming halt.)

Example code:

DROP TABLE IF EXISTS my_error_log;
CREATE TABLE my_error_log (
    error_time DATETIME,
    error_msg VARCHAR(MAX)
);
GO

CREATE OR ALTER PROCEDURE failProc
AS
BEGIN
    BEGIN TRY
        -- This one fails, is caught, and continues...
        -- SELECT 1/0;
        -- This one fails, is caught, and terminates the master script...
        SELECT * FROM non_existent_table;
    END TRY
    BEGIN CATCH
        INSERT INTO my_error_log
        SELECT CURRENT_TIMESTAMP, ERROR_MESSAGE();
    END CATCH
END;
GO

CREATE OR ALTER PROCEDURE masterFailProc
AS
BEGIN
    SET XACT_ABORT OFF;
    BEGIN TRY
        INSERT INTO my_error_log SELECT CURRENT_TIMESTAMP, 'start';
        EXEC failProc;
        EXEC failProc;
        EXEC failProc;
        INSERT INTO my_error_log SELECT CURRENT_TIMESTAMP, 'end';
    END TRY
    BEGIN CATCH
        INSERT INTO my_error_log SELECT CURRENT_TIMESTAMP, 'fatal error';
    END CATCH
END;
GO

EXEC masterFailProc
GO

SELECT * FROM my_error_log;
GO

DROP PROCEDURE masterFailProc;
DROP PROCEDURE failProc;
DROP TABLE my_error_log;


texts: Database in SIMPLE recovery model full using SSIS bulk insert task

texts:
I have a database in SIMPLE recovery model with 10GB free.

The file I am trying to load through SSISs bulk load is 1GB.

The table is empty (truncated prior to each loading), is a heap and has no non clustered indexes. It is a table of varchar(255) fields.

When running the bulk insert task, I can watch the database size grow from 2GB to 12GB (max space available) where it runs out of space and returns the following message:

[Bulk Insert Task] Error: An error occurred with the following error message: "Could not allocate space for object 'dbo.tablename' in database 'DBNAME' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.".

Firstly, I thought because I was in SIMPLE recovery model, bulk loading isn't logged. I know that it is when there is data in the table AND a clustered index, but neither is the case here. I assume the Bulk Insert Task is included in this as it should use BCP. Am I wrong?

Secondly, it seems strange that a 1GB text file would result in 10GB in SQL Server varchar fields. This seems mighty strange. The data is not being transformed, it is being Bulk loaded into a table. Why is it growing so large?


texts:
I've tried to start my mongos instance with access control using the --auth option but I get Error parsing command line: unrecognised option '--auth'. How do I require authentication to connect to the mongos instance?

edit: I'm using mongos --configdb config/192.168.1.69:27017 --bind_ip=192.168.1.67 --fork --syslog to start mongos


texts: What operations result in automatic creation of SQL Server statistics?

texts:
My understanding is that creating an index automatically creates statistics for that column.
What else results in auto creation of statistics? For example - Does SQL Server create statistics for every column that has ever appeared in a WHERE clause?


texts: Why would there be varying response times for the same query?

texts:
Is there a reason why the same query executed a number of times have huge variance in response times? from 50% - 200% what the projected response time is?
They range from 6 seconds to 20 seconds even though it is the only active query in the database.
Context:

Database on Postgres 9.6 on AWS RDS (with provisioned IOPS)
Contains one table comprising five numeric columns, indexed on id, holding 200 million rows

The query:
SELECT col1, col2
  FROM calculations
 WHERE id > 0
   AND id < 100000;

The query's explain plan:
Bitmap Heap Scan on calculation  (cost=2419.37..310549.65 rows=99005 width=43)
  Recheck Cond: ((id > 0) AND (id <= 100000))
  ->  Bitmap Index Scan on calculation_pkey  (cost=0.00..2394.62 rows=99005 width=0)
        Index Cond: ((id > 0) AND (id <= 100000))

Is there any reasons why a simple query like this isn't more predictable in response time?
After doing a database dump to my own EC2 instance PostgreSQL server, and executing the above query continuously, I can see that the variance is quite small -- within +/- 10% of each other. So now thinking that we are not correctly configuring (or don't don't understand fully how the Provisioned IOPS works on RDS). Will look into this more.


texts: How to sort data using 2 conditions and keep the first set of data always at the top?

texts:
I have a simple table:

id, status, date

When status is null, the date is also null. Question: how do I get my data that's always sorted by status = null and then sorted by date? The tricky part is, I would like to paginate through results, so I need to be able to use LIMIT and OFFSET with this query.


texts: Update all tables containing column name

texts:
In SQL Server, I can find all tables containing a COLUMN_NAME:

SELECT *
FROM   INFORMATION_SCHEMA.columns
WHERE  COLUMN_NAME = 'COLUMN_NAME'

How can I update all of these columns like:

UPDATE <table> SET <column_name> = 123

The solution needs to work in SQL Server 2012 and newer. In my case the column will always be the same datatype: bigint

Do I need to use a CURSOR or similar?


texts: Which costing factors go into the optimizer choosing different types of spools?

texts:
Spoolum

In SQL Server there are several kinds of spools. The two that I'm interested are Table Spools and Index spools, outside of modification queries.

Read only queries, particularly on the inner side of a Nested Loops join, may use either a Table or Index spool to potentially reduce I/O and improve query performance. These spools can be Eager or Lazy. Just like you and me.

My questions are:

Which factors go into the choice of Table vs. Index Spool
Which factors go into the choice between Eager and Lazy Spools


texts: How does a database use the SYS schema even when we change the password?

texts:
From the thread What is the difference between sys and system accounts in Oracle databases?, I understand that sys schema is supposed to be used only by the database.

But still an administrator can change the password for the schema.

Can the database connect without the password? If not, how does it connect even after the password is changed?


texts: Error connecting to 10.0.17-MariaDB-1~trusty

texts:
I'm having problem connecting remotely to my MariaDB server.

I've tried more or less everything, and can open a socket with nc(nc myserver 3307) but with the mysql (mariadb) client I only get the following error:

mysql -h myserver -P 3307 -u user -pPassword
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 54

I have looked all over, but can't find the cause of this error.

My server is running Ubuntu 14.04.2 LTS (Trusty Tahr) and I installed mariaDB with:

apt-get install mariadb-server-10.0

Any suggestions or ideas? Local connections (both over socket and ip works fine).


texts:
Is there a way to start a SQL Server agent job if a table is not empty?

Basically, first I want to check a table, and if the table is not empty then start the job. If it is empty, then check every 1 hour to see whether it has the required information.

I want to run this job once a day once I have data in the table.


texts: POSTGRESQL 12: pg_basebackup isn't copying postgresql.conf and pg_hba.conf from master to standby

texts:
I am setting a master-slave architecture and have done the following steps thus far:

on master:

CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'replicator';

theowl@postgresql:/etc/postgresql/12/main$ sudo vi pg_hba.conf
host    replication     replicator      192.168.56.8/24         md5

sudo systemctl restart  postgresql

on standby:

theowl@postgresql:/etc/postgresql/12/main$ sudo systemctl stop postgresql
postgres@postgresql:/etc/postgresql/12/main$ ls -ltr
total 0

pg_basebackup -h 192.168.56.7 -U replicator -p 5432 -D /etc/postgresql/12/main/ -Fp -Xs -P -R
postgres@postgresql:/etc/postgresql/12/main$ pg_basebackup -h 192.168.56.7 -U replicator -p 5432 -D /etc/postgresql/12/main/ -Fp -Xs -P -R
Password:
48423/48423 kB (100%), 1/1 tablespace
postgres@postgresql:/etc/postgresql/12/main$ ls -ltr
total 80
drwx------ 2 postgres postgres 4096 Mar 12 16:35 pg_xact
drwx------ 2 postgres postgres 4096 Mar 12 16:35 pg_twophase
drwx------ 2 postgres postgres 4096 Mar 12 16:35 pg_stat_tmp
drwx------ 2 postgres postgres 4096 Mar 12 16:35 pg_stat
drwx------ 2 postgres postgres 4096 Mar 12 16:35 pg_snapshots
drwx------ 4 postgres postgres 4096 Mar 12 16:35 pg_multixact
-rw------- 1 postgres postgres  224 Mar 12 16:35 backup_label
drwx------ 3 postgres postgres 4096 Mar 12 16:35 pg_wal
drwx------ 8 postgres postgres 4096 Mar 12 16:35 base
drwx------ 2 postgres postgres 4096 Mar 12 16:35 pg_subtrans
drwx------ 2 postgres postgres 4096 Mar 12 16:35 pg_replslot
drwx------ 2 postgres postgres 4096 Mar 12 16:35 pg_notify
-rw------- 1 postgres postgres    3 Mar 12 16:35 PG_VERSION
drwx------ 2 postgres postgres 4096 Mar 12 16:35 pg_tblspc
drwx------ 2 postgres postgres 4096 Mar 12 16:35 pg_serial
drwx------ 4 postgres postgres 4096 Mar 12 16:35 pg_logical
drwx------ 2 postgres postgres 4096 Mar 12 16:35 pg_dynshmem
drwx------ 2 postgres postgres 4096 Mar 12 16:35 pg_commit_ts
drwx------ 2 postgres postgres 4096 Mar 12 16:35 global
-rw------- 1 postgres postgres    0 Mar 12 16:35 standby.signal
-rw------- 1 postgres postgres  268 Mar 12 16:35 postgresql.auto.conf

postgres@postgresql:/etc/postgresql/12/main$ cat postgresql.auto.conf
primary_conninfo = 'user=replicator password=replicator host=192.168.56.7 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'

postgres@postgresql:/etc/postgresql/12/main$ source ~/.pgsql_profile
postgres@postgresql:/etc/postgresql/12/main$ pg_ctl -D /etc/postgresql/12/main/ start
***waiting for server to start....postgres: could not access the server configuration file "/etc/postgresql/12/main/postgresql.conf": No such file or directory
 stopped waiting
pg_ctl: could not start server
Examine the log output.***

postgres@postgresql:/etc/postgresql/12/main$ cat ~/.pgsql_profile
export PATH=/usr/lib/postgresql/12/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin PAGER=less


texts: Long time, sporadic 'String or Binary data would be truncated.' Wisdom would be appreciated

texts:
TL;DR

How can I diagnose the source of a very inconsistent, un-reproducable 'String or Binary data would be truncated.' error, when I am quite positive the problem isn't related to user data being to large to fit into SQL objects?

I have a project that is a C# application which uses SQL Server Express 2005 as a local db engine.  The database is used to mostly cache data and very little processing or transforming is done within the local database.

The exception to this is when I need to prepare locally cached data to use when a user prints a report from their application.  In this case, the user calls a stored procedure and passes in a few variables.  The stored procedure starts by deleting all data associated with the document being printed.  In other words, it fully cleans the data cache before it begins.

Next, it goes through a very long and complex process of consolidating and building information.  Much of this information is stored in temp tables and after the process is finished, it copies the temp information into the report data tables.

The problem:  This application has existed for nearly 6 years.  About once every few months, since the inception of the application, a random user has called me telling me that they have received a 'String or Binary data would be truncated.' error.  Furthermore, their application has historically worked perfectly, some times for years.  Then, all of a sudden, they can't print from their application and they receive the above message from SQL Server.

After receiving the error, they can't recover from it.  In fact, every document they try to load (even one's that have worked minutes before the message appeared) are now impossible to generate because SQL Server keeps throwing this error.

I know and understand the nature of this error-- it's telling me that some data is larger than a specific field in which it's being inserted.  However, this doesn't make since in my context for two, main reasons.  Reason #1, I've never been able to reproduce this bug on any PC, but it is 100% consistent on the user's PC once it starts.  Reason #2, across the years I've never been able to force this error to occur, regardless of the inputs I've stored in my associated data tables or passed to the stored procedure as parameters.

The only way I've been able to fix this problem is to flag the user account to delete their local database.  Once the user deletes their db, they rebuild and sync it with the server and everything works perfectly again. The failing stored procedure also has a ludicrous amount of data checks and washing routines, all because of this dumb error.  I've never been able to isolate the problem nor solve it.

I know that this type of question typically begs for the posting of code but I can't provide that.  This is a BIG stored procedure and it contains some propitiatory details. Still, if anyone is aware of a SQL bug that could be caching something within the internals of SQL Server and produces this generic exception, I'd appreciate the information or your expert advice of where else to look for a solution.

Please ask questions.  I will comment and update my post as needed.  This annoyance has gone on long enough.


texts: How to split table records into aggregate and array in PostgreSQL

texts:
I have a table max_values in my PostgreSQL 9.5 database containing four columns.

id  values  lower_limit   upper_limit
1   15.3    112           142
2   18.1    142           172
3   17.5    172           202
4   16.8    202           232
5   15.7    232           262
6   19.3    262           292
7   21.4    292           322
8   21.4    322           352
9   21.4    352           382
10  21.4    382           412
11  21.4    412           442
12  21.4    442           472

with table structure as follows:

Create Table max_values as
(
id integer,
values numeric,
lower_limit numeric,
upper_limit numeric
);

Each 'ID' is unique identifier of 'values'. I need to split above table records into an aggregate and an array based on ID (expected output: 1 row and 2 columns). What I need to do is:

get the most prevalent/most repetitive value in 'values'
count IDs of 'values' other than most repetitive value
return the total ID count, lower limit, value and upper limit in an array
if there is no repetitive value then first column could be zero

The desired output could be like:

Most_repetitive    Remaining
21.4               6, 112, 15.3, 142, 142, 18.1, 172, 172, 17.5, 202, 202, 16.8, 232, 232, 15.7, 262, 262, 19.3, 292

Being a newbie, I am aware of some PostgreSQL functions such as mode () etc. but I would highly appreciate if someone could point me how to get the desired output for my table records?


texts: Select only records that have different values for a particular column

texts:
In my table there are some records that have different name values for the same account number and I want to extract only the records that have different values in the name field, but that share the same account number. Records that always have the same name associated with the account number I want to ignore, regardless of how many times they occur.

So, from this :

ID NAME    ACCNR
1  Peter   123456
2  Peter   123456
3  Jimmy   445566
4  John    987654
5  Robert  987654
6  Robert  556644
7  Sally   112233
8  Harry   987654

I would like to extract this :

4  John    987654
5  Robert  987654
8  Harry   987654


texts: Relations between a imeid, sim chip, and account from a cellphone company?

texts:
I've never really had a cellphone (that I owned the account to...long story), but at work we have them, and we want to get a series of relations between accounts, devices (mifi's, tablets, cellphones), sim chips, data plans, etc straightened out at work.

My personal theory on this, is that we need to put all the information into several tables in a database until the issues are resolved and the information there is valid.

And later maybe we could even add a history table if need be to remember who had them, where, and which plan / which department, and how much each plan cost.  We have plans in two companies Verizon and AT&T.

So what I am asking is, what are the basic relations between this information?

From what I understand the device id's (the IMEIDs) should be the unique key that uniquely identifies a device.

I'm not certain what identifies a plan, maybe an account number...maybe a phone number...

I'm not sure what all the variables are related to this, but I'm pretty sure I could normalize a database for it, if I would find out all the unique keys involved.

I also have an HR database that keeps track of our employees that I can do a join on to record a history of plans, phones, devices etc...


texts:
Does Visual Studio 2017 Community (.NET Framework 4.6.2) support Oracle 8i?

I am trying to connect with .NET Framework 4.6.2 to a production Oracle 8i database.

I googled it, but I can not find anything which is related to this topic.
I checked also this site but that is for Windows 2008 R2.

I tried Oracle 19 and Oracle 18 and it works. But I can't find what the oldest version is that is still compatible with .NET Framework (like Oracle 8 or Oracle 9) .

I tried to connect using ODP.NET Managed Driver but it was unsuccessful.

I am an Oracle Developer and Web Developer for the organisation. I have been given the task to test it if it's possible to connect on our  (TEST) database which is Oracle 8i (I believe). Our main database is Oracle 10, but this data is restricted and I cannot manipulate it. This part is for our DBA.


texts: How to exclude weekends and holidays in a query

texts:
I have been tasked with creating a flat file. One of the fields that I need is a date field. I need the disco date to be LAST_EVENT_PROC_DT + 12 unless that date falls on a weekend or holiday,if so the disco date would be the next business day. This date field is stored in my system as a Numeric field. I am trying to write it is a case statement using our holiday table and some weekend exclusion code.
This is the column I am trying to query:

LAST_EVENT_PROC_DT (numeric(8,0),not null)

It holds a date formatted like: 20150506

This is the flat file output:

'' + '|' +
'' + '|' +
cast(q.NEXT_EVENT_PROC_DT as varchar) + '|' + -- Due Date
cast(q.LAST_EVENT_PROC_DT +12 as varchar) + '|' + -- Disco Date = Due date +15
CONVERT(VARCHAR(10),GETDATE() +15,112) + '|' + -- Agency Send Date todays date + 15

I am using SQL Server 2014


texts: Increment a column in table based on another table

texts:
I have 2 table in my database.

Table #1

----------------------------
| name | id | overdue_days |
----------------------------

Table #2

-------------
| name | id |
-------------

This is to maintain a fine collection database where overdue_days in table #1 corresponds to how many days the fine submission by a particular id pending is done.

Table #2 receives every day the names of defaulters for that day.

My intention is to compare table #1 and table #2 and

Increment the overdue days for ids which are present in table #2 and are already present in table #1.
If a particular id is present in table #1 but not in table #2 , then set its overdue days to 0.
If an id is present in table #2 but not in table #1, insert a row for it with overdue days as value 1.

My desired result would be updated table #1 with the above conditions.

I can think of one way to use three separate join queries and cover each of the cases.

update T1
set overdue_days = overdue_days + 1
from table#1 T1
inner join table#2 T2
on T1.id = T2.id;

Is there an optimised way to do the above?


texts: Always Encrypted 'where' on multiple values

texts:
I understand why SQL won't allow this, I want to know if there is some way around this that will allow me to do a where on an encrypted column with multiple values?

I have tried to use a TVP, but SQL does not allow me to join on an encrypted column:

ALTER PROCEDURE GetUsers
    @Users [dbo].[Users_Type] readonly
AS
BEGIN
    SELECT *
    FROM [dbo].[Users] u1
    JOIN    @Users u2
    ON      u1.[Username] = u2.[Users]
END
GO

I've tried creating the TVP with an encrypted column, but SQL won't allow me to do so:

CREATE TYPE [dbo].[Users_Type] AS TABLE(
    [Users] [varchar](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
)
GO

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'ENCRYPTED'.

Is there any way to get around this limitation?


texts: Database Mail connection to exchange - Resets and timeouts

texts:
I am trying to send the results of a query in an email, and each time it takes longer than 5-10 minutes for me to recieve the message. The actual relay from Exchange takes less than one minute, but when I send larger emails (bigger than 200KB), Exchange doesn't even show that it recieved the email.

Wireshark reveals two issues on the client (SQL Server) side:

Database Mail is issuing RSET commands over and over again before sending any data. It takes several minutes before any actual DATA packets are sent.
After Database Mail has sent 14 DATA packets, Exchange immediately acknowledges the packets. Before Database Mail sends the next 14 packets, 10-30 seconds pass with no traffic.

Ultimately, this means that if I am trying to send an email larger than 200KB from SQL Server, I end up with a timeout from exchange after 5 minutes. Has anyone encountered this issue?

I have tried this on multiple machines, and using both SQL 2012 and 2008R2, getting the same results each time.


texts: Difference between partitioning and fragmentation

texts:
I'm trying to learn something about distributed databases and I stumbled upon the concepts of fragmentation and partitioning. It seems to me as if both do the same thing and produce similar results (deviding a relation horizontally/vertically/both).

So what's the difference between fragmentation and partitioning?


texts: How to setup two mysql servers with group replication to work individually, when no network

texts:
I have two mysql servers in two different places. Both servers must be able to read and write. All tables are designed to based on unique id (UUID) primary key. I want to sync these two databases live using a replication method when networks are available. But both servers must be able to work individually when networks are not available.

Which replication method is suitable for this situation ? If someone has already tried this, please help me to resolve this problem.

I have tried MySQL group replication, but when network disconnected i can only work on one mysql server which is bootstrapped. Othe one doesn't allow writes.


texts: Difference in behaviour between `DBCC TRACEON` and `QUERYTRACEON`

texts:
I am using SQL server 2014, but my database version is SQL server 2012. So I am trying to use trace flag 2312 to force the query optimizer to use cardinality estimator v120.

I have noticed through my own testing on a specific query:

With this trace flag active the query takes about 20 minutes, but in SSMS results come back all at once after 20 minutes
Without the trace flag active the query takes about 30 minutes, but the first few results come back instantly and continue to be added.

I have no issues with trace flags in SSMS, the trace flag seems to be properly set regardless of whether i set a global trace flag using DBCC traceon(2312, -1) or I add the option option (Querytraceon 2312).

The problem is when I try to run this query through my .NET application, if my query has the querytraceon option, then IDBCommand.ExecuteReader() seems to hang until the entire query completes, which seems like what I expect given my results in SMSS. However, if I globally enable the traceflag, but remove the querytraceon option from my query, then IDBCommand.ExecuteReader() finishes right away.

To me this seems like my query is not picking up the trace flag 2312, even though it is globally enabled for all sessions.

What could cause the discrepancy here?

I have added the xml execution plan for my plan with the trace flag enabled globally:
(broken into 2 parts due to size)

https://paste.city/98
https://paste.city/103

The plan for the trace flag enabled using querytraceon:
(broken into 2 parts due to size)

https://paste.city/100
https://paste.city/102

trace flag disabled:
https://paste.city/99


texts: SQL Grouping rows in a feed database table, output only grouped rows adding a group row

texts:
I'm trying to write an SQL query to work on a raw feed that's been imported to a MySQL database table.  The table's rows loosely resemble this example.

| sku        | group_name | description       | size | type   |
|------------|------------|-------------------|------|--------|
| EX-1000-XS | NULL       | Long text         | XS   | single |
| EX-1000-S  | NULL       | Long text         | S    | single |
| EX-1000-M  | NULL       | Long text         | M    | single |
| EX-1000-L  | NULL       | Long text         | L    | single |
| EX-1001-M  | EX-1001    | Another long text | M    | single |
| EX-1001-L  | EX-1001    | Another long text | L    | single |
| EX-1001    | NULL       |                   |      | group  |

I'm trying to get the ones without a group_name, but with the determined group name set, along with a group row. Ignoring stuff that already was in a grouping. So the output I want from the query is like this.

| sku        | group_name | description       | size | type   |
|------------|------------|-------------------|------|--------|
| EX-1000-XS | EX-1000    | Long text         | XS   | single |
| EX-1000-S  | EX-1000    | Long text         | S    | single |
| EX-1000-M  | EX-1000    | Long text         | M    | single |
| EX-1000-L  | EX-1000    | Long text         | L    | single |
| EX-1000    | NULL       |                   |      | group  |

I've figured out some substring function on the sku column, to get a "key" like value,

SELECT
  SUBSTRING_INDEX(
    sku,
    '-',
    LENGTH(sku) - LENGTH(REPLACE(sku, '-', ''))
  )
FROM
  products
WHERE
  group_name IS NULL
  AND type = "single";

No idea how to use that as a grouping key yet,

I set up a fiddle with the example, https://www.db-fiddle.com/f/ua6iwXGHcPDVKbCkqHvs2E/0


texts: Size, audit and rowcount of a table in Vertica

texts:
I need to find a compressed size, an audit and a row count for each table in some schemes.
For a compressed size and a row count I tried to use projection_storage:

SELECT anchor_table_schema,
   anchor_table_name,
   SUM(used_bytes),
   SUM(row_count)
FROM v_monitor.projection_storage
GROUP BY anchor_table_schema, anchor_table_name

But for empty table (0 rows) I got non-zero SUM(used_bytes) and SUM(row_count). So this way doesn't work properly.
And I calculate an audit by select audit('table_name') for each table, but it's very slowly.
So my questions:

Is there other way to find compressed size (and maybe a row count) of a table? Because I got 80000000 bytes instead of zero.
Is there more faster way to calculate an audit? I know vertica calculates audits every day for each table, but I can't find where results is stored.


texts: connection issue while connecting to Postgres via Putty

texts:
I am using below commands to login to our Postgres database and sometimes I face login issue
psql -h 127.0.0.1 -p 15432 -U user-name -d database-name

I often receives below error sometimes and sometime not, though password entered correctly each time.
Password for user user-name:
psql: FATAL:  password authentication failed for user "user-name"
FATAL:  password authentication failed for user "user-name"

If I run netstat -tnlp, I get this:
[xyz@innolx20122 ~]$ netstat -tnlp
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      -
tcp        0      0 127.0.0.1:15432         0.0.0.0:*               LISTEN      27247/ssh
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      -
tcp6       0      0 :::22                   :::*                    LISTEN      -

I don't understand why it errored out sometimes and is there any thing else that we can check?


texts:
I am trying to get my table to use row locks. I have already disabled lock escalation. The isolation  level is READ_COMMITTED_SNAPSHOT

alter table <TABLE_NAME> SET (LOCK_ESCALATION=DISABLE)
go
alter index <INDEX_NAME> ON <TABLE_NAME> SET (ALLOW_PAGE_LOCKS=OFF)
go

It's still not working after setting this.

Do I need to rebuild my table in order for it to not use page locking?


texts: How do I totally remove SQL Server 2017 on Ubuntu?

texts: The docs has instructions about how to remove SQL Server.  However, this leaves certain packages behind.  How can I remove all traces and uninstall SQL Server 2017?


texts: Putting back online SQL AlwaysOn servers

texts:
I got notification from System Administrators that they will upgrade some features on physical machines on which I have virtual machines (SharePoint and SQL servers).
SQL servers are in AlwaysOn mode, one primary and one secondary server.

Does it matter what SQL server they will put back online first or it doesn't matter?


texts: SQL Sever contains not finding specifics values

texts:
In my database i have a column named index_description_column in the table products, and i've a record with "2205607 GRAMPO FIX. ROLDANA 7/8" (1X9)" data.

When i do the following query it brings de record as expected:

SELECT *
FROM products
WHERE CONTAINS(index_description_column, '"GRAMPO*" and "FIX*" AND "1*"')

But if a do the following query to match the 7/8 part with doesnt bring nothing

SELECT *
FROM products
WHERE CONTAINS(index_description_column, '"GRAMPO*" and "FIX*" AND "7*"')

How can i made it work?


texts: Getting count(column) as well as a specific row in each group

texts:
I am working with SQL Server 2012.

I want to get the count of column as well as the rows with specific condition in each group.

The query I have in mind looks something like this:

select count(column1), (column2 where column2 contains 'page1')
group by column1

I know that the above query isn't correct but I want to show the idea.

Sample data

column1   column2
-------  --------
x1       'temp/page1_l'
x1       'temp/page2_f'
x2       'temp/page2_d'
x2       'temp/page1_k'
x2       'temp/page2_e'

Expected output

count(column1)  column2
--------------  --------------
2               'temp/page1_l'
3               'temp/page1_k'

How can I achieve that output?


texts:
I have a simple, one level parent child relation table, with following columns:

ID_Asset| Parent_ID_Asset | ProductTitle

I need output grouped by Parent followed by children, and also sorted by Parent and Children Name. My attempts in the fiddle. The parent must be first.

See here for details: https://rextester.com/PPCHG20007

CREATE TABLE [dbo].[Test](
    [ID_Asset] [int] NOT NULL,
    [Parent_ID_Asset] [int] NULL,
    [ProductTitle] [nvarchar](150) NOT NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (2, NULL, N'Live Maps Unity')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (3, NULL, N'mShare')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (4, NULL, N'Nessus Professional')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (5, NULL, N'Enterprise Server')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (6, NULL, N'PhantomPDF')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (7, NULL, N'Sharegate')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (8, NULL, N'ADONIS Server')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (9, NULL, N'Automated Intelligence AI Compliance Extender & AI Syncpoint')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (10, NULL, N'Agility BridgeChecker')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (11, NULL, N'Office Timeline')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (12, NULL, N'ThinkBuzan iMindMap 8 Ultimate')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (13, NULL, N'Total Management Suite')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (14, NULL, N'Webex Business Messaging and Advanced Meetings')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (16, 8, N'ADONIS Designer')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (20, 8, N'ADONIS Portal Module “Control & Release” Package XS')
GO

SELECT
    *
FROM
    Test
ORDER BY (
    CASE WHEN Parent_ID_Asset is null then ID_Asset else Parent_ID_Asset end), ProductTitle
GO

The desired output is (the relevant part):

ID_Asset   Parent_ID_Asset    ProductTitle
--------   ---------------    ---------------------------------------------------
   8            NULL          ADONIS Server
  16             8            ADONIS Designer
  20             8            ADONIS Portal Module “Control & Release” Package XS

Parent on top, followed by children in alphabetical order. The parent elements (with null in Parent_ID_Asset) must be also ordered alphabetically.


texts: Millions of reads for a single table used for reports

texts:
I am currently looking into a query that retrieves lots of records, and it's used for reporting different balances during each month of the year(periods).
The original query joins a few extra tables but the main culprit is this one:

SELECT [GLHistory].[BranchID] AS [BranchID]
      ,[GLHistory].[LedgerID] AS [LedgerID]
      ,[GLHistory].[AccountID] AS [AccountID]
      ,[GLHistory].[SubID] AS [SubID]
      ,MAX([GLHistory].[FinPeriodID]) AS [LastActivityPeriod]
      ,[FinPeriod].[FinPeriodID] AS [FinPeriodID]
  FROM [GLHistory] [GLHistory]
         INNER JOIN [FinPeriod] [FinPeriod]
                    ON [FinPeriod].[FinPeriodID] >= [GLHistory].[FinPeriodID]
 WHERE [GLHistory].CompanyID = 7
   AND (   [GLHistory].BranchID IS NULL
        OR [GLHistory].BranchID = 13)
 GROUP BY
       [GLHistory].[BranchID]
      ,[GLHistory].[LedgerID]
      ,[GLHistory].[AccountID]
      ,[GLHistory].[SubID]
      ,[FinPeriod].[FinPeriodID]
      ,[GLHistory].CompanyID

I need to get the information from last financial period and any other entry that exists in my table until the last financial period for an account.
Not sure what more, or how to structure the query in order to improve it.

Here is a script with the 2 tables schema and some data https://www.dropbox.com/s/swbqqzdey2tnkxz/script.sql?dl=0

And the full query can be found here: https://www.dropbox.com/s/scnav8flqpcrstd/SQLQueryGLB.sql?dl=0

Query plan presented here:queryplan

The output I am looking for is to get all the information for an account from the beginning period where I have an entry in GLHistory to the last period. And FinPeriodId might have 3 rows because its a row for each company

So it would be something like this, hope this time is right. You have an account for which there are values stored in GLHistory. For that account I need to get the information from GLHistory in a period, but also all other values until that period, so the values in GLHistory that are related to that account for each period until last activity period.  That is why I have FinPeriodId >= GLHistory.FinPeriodId.


texts: SQL Server engine in one place with database files on NAS

texts:
My office uses accountant programs which run on SQL Server 2008 R2. The SQL Server engine must be installed on one of the laptops (called "server") and the other laptops (called "clients") connect to it via local network. Database files are stored in the same place as the SQL Server engine, so on the "server" laptop. From the security point of view, this is the most ridiculous solution anyone could have so I'm trying to change that.

Is it possible to MOVE only the database files (.mdf) to the NAS which we own, so the SQL Server engine will still run on the "server" laptop but the database will be stored on secured NAS in other place?

Unfortunately it's impossible to install SQL Server on the NAS because it's Synology so it doesn't have MS compatibility (it does have MySQL though).


texts:
One of our production servers showing below error msg in Sql Server Log from last week,

  SQL Server has encountered 20674 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\Databasefile.mdf] in database [Database] (11). The OS file handle is 0x0000000000000828. The offset of the latest long I/O is: 0x00001c3bde0000
  and today in addition to the above one more msg,

  BobMgr::GetBuf: Sort Big Output Buffer write not complete after 60 seconds.

is this the indication of a serious issue? How can we resolve this?


texts: MySQL to SQL Server, How to replicate data in a set interval?

texts:
What would be the best way to replicate a database with several tables from a MySQL server to a MSSQL server, seeing as they are in different devices on the same network with a set interval of 10 minutes?

By best way, I mean a way that wouldn't completely copy all the rows with each iteration, a way that would only copy the latest entries since the last iteration.


texts:
I have three connected table in my design. Basic logic is when a category deleted, also delete records in category_product table which has deleted category's id in its cat_id column. But when I tried to create tables with query below, I get this error: "there is no unique constraint matching given keys for referenced table 'category_product'". I don't know what is wrong with this design or logic.

CREATE TABLE IF NOT EXISTS category_product (
    cat_id integer NOT NULL,
    product_id integer NOT NULL,
    CONSTRAINT PK_CATEGORY_PRODUCT_ID PRIMARY KEY (cat_id, product_id)
);

CREATE TABLE IF NOT EXISTS categories (
    id serial,
    name text NOT NULL,
    is_active boolean NOT NULL DEFAULT true,
    CONSTRAINT PK_CATEGORIES_ID PRIMARY KEY (id),
    CONSTRAINT FK_PRODUCTS FOREIGN KEY (id) REFERENCES category_product(cat_id) ON UPDATE NO ACTION ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS products (
    id serial,
    name text NOT NULL,
    description text NULL,
    photo text NULL,
    price money NOT NULL,
    is_in_menu boolean NOT NULL DEFAULT true,
    is_from_kitchen boolean NOT NULL DEFAULT true,
    is_deleted boolean NOT NULL DEFAULT false,
    CONSTRAINT PK_PRODUCTS_ID PRIMARY KEY (id),
    CONSTRAINT FK_CATEGORY_PRODUCT FOREIGN KEY (id) REFERENCES category_product(product_id) ON UPDATE NO ACTION ON DELETE CASCADE
);

So what kind of structure I need to achieve what I am trying?


texts:
I've installed MongoDB 3.0 in RHEL 6. From root user, I'm trying to start the mongo service but it is getting failed and the log file is not getting updated as well.

2015-10-13T09:53:26.133+0530 E NETWORK  [initandlisten] Failed to unlink socket file /tmp/mongodb-27017.sock errno:1 Operation not permitted
2015-10-13T09:53:26.133+0530 I -        [initandlisten] Fatal Assertion 28578
2015-10-13T09:53:26.133+0530 I -        [initandlisten]

***aborting after fassert() failure

The above is the last updated log.

I have another user 'oracle' from which also it is not starting.

I also tried running mongod --repair

It gives:

2015-10-13T11:02:59.473+0530 I CONTROL  [initandlisten] MongoDB starting : pid=11033 port=27017 dbpath=/data/db 64-bit host=vmrac2
2015-10-13T11:02:59.473+0530 I CONTROL  [initandlisten] ** WARNING: You are running this process as the root user, which is not recommended.
2015-10-13T11:02:59.473+0530 I CONTROL  [initandlisten] 2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten]
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] ** WARNING: You are running on a NUMA machine.
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] **          We suggest launching mongod like this to avoid performance problems:
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] **              numactl --interleave=all mongod [other options]
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten]
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/enabled is 'always'.
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten]
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/defrag is 'always'.
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten]
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] ** WARNING: soft rlimits too low. rlimits set to 16384 processes, 65536 files. Number of processes should be at least 32768 : 0.5 times number of files.
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten]
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] db version v3.0.6
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] git version: 1ef45a23a4c5e3480ac919b28afcba3c615488f2
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] build info: Linux ip-10-67-194-123 2.6.32-220.el6.x86_64 #1 SMP Wed Nov 9 08:03:13 EST 2011 x86_64 BOOST_LIB_VERSION=1_49
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] allocator: tcmalloc
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] options: { repair: true }
2015-10-13T11:02:59.474+0530 I STORAGE  [initandlisten] finished checking dbs
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] now exiting
2015-10-13T11:02:59.474+0530 I NETWORK  [initandlisten] shutdown: going to close listening sockets...
2015-10-13T11:02:59.474+0530 I NETWORK  [initandlisten] removing socket file: /tmp/mongodb-27017.sock
2015-10-13T11:02:59.474+0530 I NETWORK  [initandlisten] shutdown: going to flush diaglog...
2015-10-13T11:02:59.474+0530 I NETWORK  [initandlisten] shutdown: going to close sockets...
2015-10-13T11:02:59.474+0530 I STORAGE  [initandlisten] shutdown: waiting for fs preallocator...
2015-10-13T11:02:59.474+0530 I STORAGE  [initandlisten] shutdown: closing all files...
2015-10-13T11:02:59.474+0530 I STORAGE  [initandlisten] closeAllFiles() finished
2015-10-13T11:02:59.474+0530 I STORAGE  [initandlisten] shutdown: removing fs lock...
2015-10-13T11:02:59.475+0530 I CONTROL  [initandlisten] dbexit:  rc: 0

Things I have tried:

Deleting /tmp/mongodb-27017.sock but it says no file found with that name.
Changing the net.port in the config file to change the .sock file.
Setting unixDomainSocket : enabled: false


texts: Select all rows of all user logins, where user had logged in from more than 3 ip address within the past month

texts:
Our SQL Server UserLogins table has these columns: UserID, DateTime, IpAddress.
How can I select rows of users who have logged in from more than three different ip addresses within a specified time period (e.g., one month), ordered by User ID


texts: Innodb Isolation levels and locking performance

texts:
I am reading a lot about locking and isolation levels in MySQL and InnoDB.

I understood the technical differences and basic approach, but I still fail to understand a situation where for example READ UNCOMMITTED will give me better performance than READ COMMITTED.

Here is my simulation:

CREATE TABLE `t` (
    `i1` INT(11) NOT NULL,
    `i2` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`i1`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
insert into t values(10,10), (20,20), (30,30), (40, 40), (50,50),(60,60);

T1:

MariaDB [maint]> begin;
Query OK, 0 rows affected (0.00 sec)
MariaDB [maint]> update t set i2=100 where i1=60;
Query OK, 1 row affected (0.00 sec)

T2 with READ UNCOMMITTED:

MariaDB [maint]> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [maint]> select i2 from t where i1 =60;
+------+
| i2   |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

So the logic as expected, and no locks.

T2 with READ COMMITTED:

MariaDB [maint]> SET TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [maint]> select * from t where i1 =60;
+----+------+
| i1 | i2   |
+----+------+
| 60 |   60 |
+----+------+
1 row in set (0.00 sec)

The logic works as I expected, and again no lock.

So simply the only difference is that READ UNCOMMITTED just took the data before commit and READ COMMITTED took a snapshot.

Can someone help me create a simulation or explain a situation in which UNCOMMITTED will have no locks whereas COMMITTED would have? Same for REPEATABLE READ vs COMMITTED.


texts: What queries is Oracle executing, how frequently, and time-taken?

texts:
I have a (Java) web-application, that of course executes many queries (selects, inserts, updates, deletes) to the Oracle Database.

I want to find out the following for last 7 days:

    SQLQuery          Times-Executed   Average-Resp-Time    Average-Rows-Returned
====================================================================================
    SELECT whatever     981            330ms                1201
    UPDATE whatever     45             99ms                 0

Is this something Oracle can give me? If so, how can I get that?
If not Oracle, should I look at JDBC driver?


texts:
Has anyone been able to install SQL Server on Centos 6? I am running Centos 6.8, and I can only find information on Centos 7.

I know that SQL Server on Linux is still in its early stages. I’m curious to see how it runs.


texts: Why does Oracle keep working on MySQL 5.1

texts:
2 years ago the first version of Mysql 5.5 was released. What is the reason to continue working on MySQL 5.1 - the current release is Mysql 5.1.62

It looks like there are should be fundamental differences between them. I am confused which version to prefer.


texts: How to grant super privilege to the user?

texts:
I have created a user and given privileges to the user1.

`grant all privileges on db1.* to user1@'%' with grant option;

Am using mysql workbench to import dumps to my database. While importing dumps to database db1,error occurs stating that

ERROR 1227 (42000) at line 49: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

In that dumps all tables are imported successfully but the error occurs while importing routines to the database. Is there anything wrong with the privilege, I have given to the user1. Please advice.


texts:
I'm new to replication process.

In one of Rails project it's necessary to share records of one table between two applications.

Let's say share records between first application DB A and second application DB B.
I decided to move that logic on DB level.

Is is possible to configure MySQL replication in order to copy only particular records (e.g. records where need_to_replicate_to_B_flag = true)?
Or I should find another way to implement this?


texts: Does Sql Server caches parameterized anonymous blocks?

texts:
"Anonymous block" is a term from Oracle. But speaking of the thing for sql server
Declare
   ....
Begin
    begin trans
 . . .
    commit
End

If I send this SQL in parameterized form to the server via ADO.NET, will it be cached and reused or it will be recompiled every time?


texts: How to fetch data from a table which don't have a unique id


texts: Linked Server only for Select Statements?

texts:
In linked server concept can we run only select queries like below ?

SELECT * FROM OPENQUERY([SERVER_NAME], 'SELECT * FROM DATABASE_NAME..TABLENAME')

I have a job which runs through Linked server.

I have added the AOAG Listener as Linked server in the source server and this connects to PROD server and runs the query.

But the query is very big...so how this works through Linked server!


texts: Best database design for performing binary analysis

texts:
I have some binary data that I would like to do some statistical analysis on. Let's say I have a few thousand 32 bit values. I want to be able to efficiently do things such as calculate parity bits or look at how often a certain bit is a 1 or 0. My first thought is to put that data into a relational db such as postgres. However I'm not sure what would be the best schema for this type of data. I could:
a) store each bit as a column of type boolean
b) store the 32 bit value as one column of type bit string
c) store the 32 bit value as a hex encoded string
Option a seems like it would allow me to do direct queries such as count how many times bit 23 is 1 easily using direct SQL. However, is this an inefficient way to store the data, since each bit takes one byte of space in the db? What is the best data type to use to most efficiently store and perform these types of calculations on binary data?


texts: Will a SP which writes records fail if accessed by a db_datareader role?

texts:
I have a stored procedure which is accessed remotely by an app which is running on some kind of Java/WebSphere type platform. It takes one argument, returns some data and that's that.

Recently there has been some talk of it maybe running slowly - I'm pretty confident that there is no reason for the SP to be running slowly. But I added a log table to the DB and made the SP write to it on entry and again after the SELECT is complete so I can see how long the calls are taking.

This change works fine for my Windows Authentication log-in - and it also works fine when I use the application log-in which the remote app is using. However I am doing the connection from the .Net environment and not from Java.

When I changed the SP to do the logging I noticed that the IDs I was INSERTing into the log-table were jumping - indicating that failed INSERTs had been taking place - presumably caused by the remote-app (as that is the only thing that uses this SP).

The application log-on has only db_datareader role assigned to it. I have GRANTed EXEC to the application log-on of course.

Is it the case that the SP will not be able to INSERT a row because the accessing log-on has only db_datareader? And if so, how come the SP does the INSERT when I use that same log-on?


texts: How to set a value if certain columns are the same across multiple rows

texts:
I am doing a data migration and I end up with a temporary table as follows:

curid cuid  rtid    cd          dd      rm
10     4    4   2016-01-02  2016-07-02
16     4    4   2016-06-12  2016-12-12  Remarks Jun 12
18     5    3   2016-07-18  2017-07-31
8      5    3   2015-06-21  2016-06-30  Add some test
11     6    4   2017-01-01  2017-07-01
9      7    3   2017-01-01  2018-01-31

I need to split the data into two tables.
Based on that same data, it should look like this:

Table A
    id  curid cuid  rtid
    1    10    4    4
    2    18    5    3
    3    11    6    4
    4     9    7    3

That's one row per distinct (cuid, rtid) plus a curid value picked from each set of duplicates. id is just a sequential number.

Table B
    id curid    cd           dd         rm
    1   10     2016-01-02   2016-07-02
    2   10     2016-06-12   2016-12-12  Remarks Jun 12
    3   18     2016-07-18   2017-07-31
    4   18     2015-06-21   2016-06-30  Add some test
    5   11     2017-01-01   2017-07-01
    6    9     2017-01-01   2018-01-31

The actual curid is irrelevant as long as the records in Table B match the associated record in Table A (so we could even use a temp sequence or something to set the curid).


texts: "Insufficient privileges" while shutting down as sysoper

texts:
I can't shutdown the database with a common user that is granted sysoper.

There is clearly a discrepancy between this and PDBs that I am unaware of (and that the documentation also doesn't clarify), since if I do the same operations for PDB users a sysoper can shutdown/startup the PDB with no problem. Other examples I've looked at also indicate that nothing should be wrong. What am I missing?

Exact error message is:

ORA-01031: insufficient privileges

Thanks for any insights.


texts: What happens if a delete query times out SQL Server for php?

texts:
Technology

I am using the mssql extension to php (on linux) to send queries to SQL Server.

The Setup.

I have a table that builds up disposable data rather quickly. It took me longer than expected to build a cron to clean out this data every hour so it has been accumulating over months to be well over 1G of data not sure how many rows but probably over 1 million.

The Problem

So I ran a delete query for anything older than 1 day.

DELETE FROM CacheForms WHERE TimeStamp < 1503454349

However after 60sec the query times out and the script fails. (update the same happens at 10min.)

The Question

When this delete query times out (a php concept) what happens to the database? Does the database restore the deleted data from transaction log? or is the data truly deleted? Or does the query keep running in sql server?


texts: Setting up users and admins with schema privileges

texts:
I currently have a database that is split into two schemas: data and ref.  I have two database roles named Users and Admins.  Users need to be able to INSERT, SELECT, UPDATE, and DELETE from the data schema, and need to be able to SELECT from the ref schema.  Admins need to be able to INSERT, SELECT, UPDATE, and DELETE from both schemas.

All users and admins interact with the data via stored procedures.  Each table has a generated save, select, and delete stored procedure that the application uses (the stored procedure is in the same schema as the object it's hitting).

I previously had the database roles set up to execute against the schemas, but that's not really what I want, because then a user can insert, update, and delete against the ref schema via a stored procedure.

How can I set up security for the Admins and Users database roles to allow users to INSERT, SELECT, UPDATE, and DELETE against the data schema and to only SELECT against the ref schema while allowing the admins to INSERT, SELECT, UPDATE, and DELETE against both schemas?


texts: Is there any correct way to convert file system from ext4 to xfs in centos?

texts:
I have deployed MongoDB Sharding Cluster(os-centos) on Azure with 2 disks each VM. But default file system is ext4 and I want xfs file system because of performance. I want to convert that file system.

Is there any way of converting file system without any configuration changes in mongo?

I tried below steps:

detach disk
unmount dir
attach disk
create partition with xfs file system
changes on fstab file
mount dir


texts:
I need to store exact sums of bigint columns in a summary table. So I must use data type numeric for those columns.

Now I am wondering if setting the scale to 0 in this case makes sense (e.g. maybe some performance benefit?)

If so, then how can I set the scale to 0 and still keep the default precision:

according to the docs the data-type numeric() (without explicit precision and scale) supports:

  up to 131072 digits before the decimal point;up to 16383 digits after the decimal point

But when we explicitly set the precision and scale, the max. allowed value for precision is only 1000: i.e. 1000 digits before the decimal point, right?

  The maximum allowed precision when explicitly specified in the type declaration is 1000;


texts: calculating % difference for every row with the same ID

texts:
Right now I have a table that looks like this but with around 1 million rows and over 100 company names.

Date  |  Company  | Price
10/08/16  Exxon      2.00
10/08/16  Shell      1.95
11/08/16  Exxon      2.01
11/08/16  Shell      1.97

What is the best way to go about calculating the percent difference per day per company so that I end up with a table or view that looks like this.

Date  | Company | % difference
10/08/16  Exxon     .56
10/08/16  Shell     .24
11/08/16  Exxon     1.005
11/08/16  Shell     1.01

would it be better to store each company as a column or keep it in row format, also how would I write the function so its not recalculating all million rows every day when I import new information.


texts:
MySQL Shell provides an option called as 'tzUTC' which adds a line to the dump.
SET TIME_ZONE='+00:00'
I don't want such offset string be appended to my datetime. All my servers are running in CST and I want my time to be local so that it is easily interpretable.
Is there a way to dump my datetime in local timezone format?
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html


texts: Is it safe or possible to change SQL Server AutoGrowth when in a SQL Server AlwaysOn Cluster

texts:
I am wondering it is possible or advisable to change the AutoGrowth settings of a database while it is part of an AlwaysOn SQL Server 2016 database cluster. I realized I used the default settings when I want to use custom growth rates based on the suggestions by Brent Ozar.

I cant change them in the GUI so I am guessing its not possible but I wanted to check. Will I have to remove the database from the AlwaysOn group, change the growth settings and then add it back in?


texts: Alternatives to storing a record with exactly n multiple foreign keys from the same foreign table, where the relationships can't be repeated

texts:
Say there are entities called singulars, and entities called relationships.

It takes exactly two singulars to make up a relationships entity.

That pair of singulars can't be repeated elsewhere in relationships, in any order.

One way to model it could be this way:

+----------------+
|relationships   |         +----------+
+----------------+         |singulars |
|id              |         +----------+
|singular_id_1   <---------+id        |
|singular_id_2   <---+     |attribute1|
|pair_description|         |attribute2|
|pair_date       |         |          |
|                |         +----------+
+----------------+

With this pattern, it becomes necessary to check both foreign key fields in relationships for the existence of singulars, which could be on either side. The order doesn't matter, yet it is defined in the schema... So the queries end up with a number of AND/OR groups and cases.

Expanding on that approach could be to store two records for every pair, with the singular_id_[n] swapped on both sides. While that solves some querying complexities, it would introduce additional complexities to make it infeasible.

Using an intermediate table seems like one potential solution:

+----------------+       +-----------------------+
|relationships   |       |singulars_relationships|        +----------+
+----------------+       +-----------------------+        |singulars |
|id              <-------+relationship_id        |        +----------+
|pair_description|       |singular_id            +-------->id        |
|pair_date       |       |                       |        |attribute1|
|                |       +-----------------------+        |attribute2|
+----------------+                                        |          |
                                                          +----------+

So the records might end up something like this:

+----------------------------------+
|relationships                     |
+----------------------------------+
|id   pair_description   pair_date |
+----------------------------------+
|1    Fizz buzz blitz    2022-02-20|
|2    Blitz buzz fizz    2022-02-22|
+----------------------------------+

+----------------------------------+
|singulars_relationships           |
+----------------------------------+
|relationship_id   singular_id     |
+----------------------------------+
|1                 1               |
|1                 2               |
|2                 3               |
|2                 4               |
+----------------------------------+

+-----------------------------+
|singulars                    |
+-----------------------------+
|id   attribute1   attribute2 |
+-----------------------------+
|1    Fizz         Blitz      |
|2    Buzz         Foo        |
|3    Bar          World      |
|4    Blorg        Hello      |
+-----------------------------+

There, singulars_relationships is where the pairs are defined. If a singular_id exists in there, it is already in a pair. One problem that may arise with this pattern could be that three or more singular_id s could end up associated with a relationship_id, and the "exactly n" constraint would then be compromised.

Are there official terms for this type of scenario? And other theory and alternatives?


texts: Is "SET IDENTITY_INSERT" required to insert arbitrary GUID into [uniqueidentifier] column?

texts:
SQL Server 2016 and 2017, Standard Edition

I need to insert raw data (from another table) into a table that has two uniqueidentifier columns.

Does this require that I use SET IDENTITY_INSERT Carrier ON?

Here is the table:

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Parkingspace](
    [ParkingspaceId] [uniqueidentifier] NOT NULL,
    [AccountId] [uniqueidentifier] NULL,
    [ParkingspaceType] [smallint] NULL,
 CONSTRAINT [PK_ParkingspaceId] PRIMARY KEY CLUSTERED
(
    [ParkingspaceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Will the following work without the IDENTITY_INSERT option?  (I think it should but the DBA here is very married to the IDENTITY_INSERT option.)

INSERT INTO Parkingspace
  (ParkingspaceID,AcountID,ParkingspaceType)
  SELECT * FROM PRODDBCopy.Parkingspace