texts:
I have a Postgres table with ~2.1 million rows. I ran the below update on it:

WITH stops AS (
    SELECT id,
           rank() OVER (ORDER BY offense_timestamp,
                     defendant_dl,
                     offense_street_number,
                     offense_street_name) AS stop
    FROM   consistent.master
    WHERE  citing_jurisdiction=1
)

UPDATE consistent.master
SET arrest_id=stops.stop
FROM stops
WHERE master.id = stops.id;

This query took 39 hours to run. I am running this on a 4 (physical) core i7 Q720 laptop processor, plenty of RAM, nothing else running the vast majority of the time. No HDD space constraints. The table had recently been vacuumed, analyzed, and reindexed.

The whole time the query was running, at least after the initial WITH completed, CPU usage was usually low, and the HDD was in use 100%. The HDD was being used so hard that any other app ran considerably more slowly than normal.

The laptop's power setting were on High performance (Windows 7 x64).

Here's the EXPLAIN:

Update on master  (cost=822243.22..1021456.89 rows=2060910 width=312)
  CTE stops
    ->  WindowAgg  (cost=529826.95..581349.70 rows=2060910 width=33)
          ->  Sort  (cost=529826.95..534979.23 rows=2060910 width=33)
                Sort Key: consistent.master.offense_timestamp, consistent.master.defendant_dl, consistent.master.offense_street_number, consistent.master.offense_street_name
                ->  Seq Scan on master  (cost=0.00..144630.06 rows=2060910 width=33)
                      Filter: (citing_jurisdiction = 1)
  ->  Hash Join  (cost=240893.51..440107.19 rows=2060910 width=312)
        Hash Cond: (stops.id = consistent.master.id)
        ->  CTE Scan on stops  (cost=0.00..41218.20 rows=2060910 width=48)
        ->  Hash  (cost=139413.45..139413.45 rows=2086645 width=268)
              ->  Seq Scan on master  (cost=0.00..139413.45 rows=2086645 width=268)

citing_jurisdiction=1 only excludes a few tens of thousands of rows. Even with that WHERE clause, I'm still operating on over 2 million rows.

The hard drive is whole drive-encrypted with TrueCrypt 7.1a. That slows things down a bit, but not enough to cause a query to take that many hours.

The WITH part only takes about 3 minutes to run.

The arrest_id field had no index for foreign key. There are 8 indexes and 2 foreign keys on this table. All other fields in the query are indexed.

The arrest_id field had no constraints except NOT NULL.

The table has 32 columns total.

arrest_id is of type character varying(20). I realize rank() produces a numeric value, but I have to use character varying(20) because I have other rows where citing_jurisdiction<>1 that use non-numeric data for this field.

The arrest_id field was blank for all rows with citing_jurisdiction=1.

This is a personal, high end (as of 1 year ago) laptop. I am the only user. No other queries or operations were running. Locking seems unlikely.

There are no triggers anywhere in this table or anywhere else in the database.

Other operations on this database never take an abornmal amount of time. With proper indexing, SELECT queries are usually quite fast.


texts: What are the differences between leaf and non-leaf pages?

texts:
I've been running some index usage reports, and I'm trying to get a definition of Leaf and Non-leaf.  There seem to be both Leaf and Non-leaf Inserts, Updates, Deletes, Page Merges, and Page Allocations.  I really don't know what it means, or if one is better than the other.

If someone could give a simple definition of each, and also explain why Leaf or Non-leaf matters, it would be appreciated!


texts: Opening an Oracle database with a corrupted redo log

texts:
I have a database that is not open. And I have a redo log that is corrupted.

What would be the best way for me to open the database? This is a testing db and I am not concerned with data loss - but I need to get the database open and working again.

Some additional notes:
Trying to switch logs tells me that the log is required for recovery
Attempting to recover fails
Trying to open gives the error msg that the log is corrupted

Additional clarification:
I do need some of the data in the db; but I don't need any of the recent data (about a year worth of data). There is some configuration data which I do want.
I don't know the structure of the db, and I have no script to re-create it.


texts: sum only different id's values with any aggreagte function in postgresql


texts: Easiest way to import access db (.accdb) into SQL Server 2016?

texts:
I have a .accfb file from an access 2016 database and am trying to import this database into a SQL Server 2016 developer edition instance.

While I found posts online discussing using import/export data tools in SQL Server and also migration assistant for access, neither have worked out for me.

Does anyone know how to easily move a database from access 2016 to sql server 2016? More specifically an "accdb" file type?

All suggestions are greatly appreciated!


texts:
I tried to backup the IBM DB2 (LUW) Database using this command:

./db2 backup database DBEMP to /home/user1/db2bkup

But I got this message:

  SQL1035N  The database is currently in use.  SQLSTATE=57019

Then I tried this:

./db2 backup database DBEMP online to /home/user1/db2bkup

And I got this message:

  SQL2413N  Online backup is not allowed because the database is not
  recoverable or a backup pending condition is in effect.

What does it mean? Is there alternative way to backup the database online? I can't stop the database because it's being used.


texts: Importing True/False as tinyint failing when last field

texts:
Here is a line from the file I am loading.

"dev"|"APSET3454"|"apset3454.xxx.xxx.xxx"|"TWS01"|31111|True|False|False|"1"|True|"Win2012R2 64 bit ESC:712482-2267174 IEMID:84520"|"2017-12-20T15:06:15.390+0000"|"WINDOWS"|"9.4.0.04"|"FTA"|"America/Chicago"|2602|"2019-02-27T13:15:08.000+0000"|30|0|""|True|True|False|False|"LINKED"|True

And here is the load command I am using.  Seems to work fine except that the last field is always comming in as a zero(false)

LOAD DATA LOCAL INFILE "/autohome/astools/Programs/FTA_api/Workdir//nm2.20190305103959.csv"
INTO TABLE  fta_status.fta_info COLUMNS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"' LINES TERMINATED BY '\n'
(TWSINSTANCE,CPUID,NODENAME,FTA_DOMAIN,TCPPORT,@AUTOLINK,@BEHINDFIREWALL,@FULLSTATUS,
SERVER,@INCLUDEINPLAN,DESCRIPTION,@MODIFIEDDATETIME,OS,VERSION,TYPE,TIMEZONE,
SYMRUNNUMBER,@TIMESTARTED,FTA_LIMIT,FENCE,ACCESSMETHOD,@TCPLINK,@JOBMANUP,@WRITERUP,
@WRITERDS,LINKSATUS,@WORKSTATIONINPLAN)
SET AUTOLINK = (@AUTOLINK = 'TRUE'),
BEHINDFIREWALL = (@BEHINDFIREWALL = 'TRUE'),FULLSTATUS = (@FULLSTATUS = 'TRUE'),
INCLUDEINPLAN = (@INCLUDEINPLAN = 'TRUE'),
MODIFIEDDATETIME = DATE_FORMAT(STR_TO_DATE(@MODIFIEDDATETIME,'%Y-%m-%dT%H:%i:%s'),'%Y-%m-%d %H:%i:%s'),
TIMESTARTED = DATE_FORMAT(STR_TO_DATE(@TIMESTARTED,'%Y-%m-%dT%H:%i:%s'),'%Y-%m-%d %H:%i:%s'),
TCPLINK = (@TCPLINK = 'TRUE'),JOBMANUP = (@JOBMANUP = 'TRUE'),
WRITERUP = (@WRITERUP ='TRUE'),WRITERDS = (@WRITERDS = 'TRUE'),
WORKSTATIONINPLAN = (@WORKSTATIONINPLAN = 'TRUE');

I do get warnings about the 2 datetime fields

  Level: Warning
    Code: 1292
    Message: Truncated incorrect datetime value: '2017-12-20T15:06:15.390+0000'

So if someone would like to help with the date_format STR_TO_DATE conversion as well :-)


texts: Cannot Login to Fresh Installation of SQL Server 2012

texts:
I just deployed a fresh installation of SQL Server 2012 on a Windows Server 2012 Datacenter R2 environment. The installation completed with 0 warnings and 0 failures.

During the installation I specified Integrated Windows Authentication so it did not prompt me for a local SA password. I added the domain security group that should be granted administrative rights as well.

Immediately after the installer completed, I launched SMSS and am greeted by this error when attempting to connect.

After clicking okay and then pressing "Connect" a second time I get this dialog.

The TechNet article relating to this issue says to ensure that Named Pipes is behind TCP/IP in the protocol order, which it is.

Any help would be greatly appreciated.

UPDATE
I tried switching to SQL Authentication mode by launching in Single User Mode, enabling the 'sa' account and setting the password but the errors remain the same.


texts:
I have a table of geometry values where some rows intersect other rows.
I need a list of rows where the geometry overlaps other rows, but I'd like the list to be as concise as possible.
Here's the setup:
USE tempdb;
DROP TABLE IF EXISTS dbo.t;

CREATE TABLE dbo.t
(
    n varchar(100) NOT NULL
    , i geometry NOT NULL
);

INSERT INTO dbo.t (n, i)
VALUES ('poly1', geometry::STGeomFromText('POLYGON ((1 2, 1 4, 1 5, 4 6, 1 2))', 4326))
     , ('poly2', geometry::STGeomFromText('POLYGON ((1 2, 1 3, 2 5, 4 6, 1 2))', 4326))
     , ('poly3', geometry::STGeomFromText('POLYGON ((7 9, 8 7, 9 6, 7 9))', 4326))

SELECT t1.n
    , t2.n
FROM dbo.t t1
    INNER JOIN dbo.t t2 ON t1.i.STIntersects(t2.i) = 1
WHERE
    t1.n <> t2.n;

The output looks like:

n
n

poly2
poly1

poly1
poly2

However, I'd like only a single row for brevity.  i.e. because poly1 overlaps poly2 and poly2 overlaps poly1 I'm getting two rows returned where I'd like only one, as in:

n
n

poly1
poly2


texts: Do I need client certs for mysql ssl replication?

texts:
I'm setting up mysql replication using SSL, and have found two different guides.

The first one creates both client and server certs, while the second one only creates server certs.

I don't know enough about SSL to understand the implication of one option over the other.  Should the slave be using the client certs or the server certs?


texts: PHP - mysqli::real_connect(): (HY000/2002): Invalid argument in file

texts:
On a work project, I'm load-testing a php service on MySQL 5.6 loaded on a beefy Google Compute Engine VM.  After I exceed a certain amount of requests per second in my load test, MySQL starts giving me errors like:

mysqli::real_connect(): (HY000/2002): Invalid argument in

I've confirmed that we're passing the same arguments to mysqli real_connect both before and after those warnings start where the connection fails.

On the VM - memory & cpu have very low usage, we're not hitting limits in write iOPS ( our loadtest is write heavy ) and none of the charts on PMM are revealing anything that's going wrong at that time.

Any ideas why this might be happening?


texts: Unique Index/Constraint with multiple columns, one column is nullable

texts:
My questions is whether it's possible to have unique index comprised of multiple columns where one of the columns may contain NULL.

Example: A table named 'regulated_person' has a number of columns including LAST_NAME, FIRST_NAME, BIRTH_DATE and ALIAS. The data type for BIRTH_DATE is date, for the others it's String/VARCHAR. LAST_NAME, FIRST_NAME and BIRTH_DATE are not nullable, i.e. they require values. ALIAS serves as a tie-breaker for cases where two or more people have the same first/last names and are born the same day. Because the tie-breaking situations are not common I want to avoid the need to provide an ALIAS value unless it's necessary.

By way of background the primary key for the table is handled by an auto-increment generator. The purpose for the unique index is to provide a 'business key' that will identify the row without having to resort to the primary key. I'm working with MySQL 5.1.35 and Hibernate ORM ver. 4.3.10.

Any suggestions as to server/database providers apart from MySQL would be welcome.

Thanks in advance for any guidance, and also apologies in advance for any unintended violations of 'forum protocol' as this is the first time I've submitted a question ANYWHERE.


texts: How can I cap the total space that mysql / mariadb binary logs take up?

texts:
I recently had my disk space completely filled with mysql binary log files (mysql-bin.000123, etc).

I was able to resolve short term by with PURGE BINARY LOGS TO (via this answer).

To try and keep it from happening going forward I set lower limits on expire_logs_days and max_binlog_size. Yet, I'm still worried that a huge number of log files could be generated in a short period without a limit on the total space used by logs or the total number of logs allowed.

Is there something I'm missing? Or do I just need to keep setting expire_logs_days lower or turn off binary logging all together if it keeps being a problem?

EDIT:

I understand it's important to diagnose what's causing the logs to fill up quickly and solve that issue. This question is not about how to diagnose what's making them fill quickly, but rather - how do I safeguard against the disk filling to 100% and crashing the server in the case that something is setup wrong or an unexpected circumstance starts to fill these logs.


texts:
See this question here: Database Administrators meta

My knowledge of databases and SQL is based in most on university classes. Anyhow, I spent few monts (almost a year) in a company, where I was working with databases.

I have read few books and I have taken part in few trainings about databases such as MySQL, PostgreSQL, SQLite, Oracle and also few nonSQL dbs such us MongoDB, Redis, ElasticSearch etc.

As well as I said, I am begginer, with a lot of lacks of knowledge but today, someone told something, what is totally against my begginer's knowledge.

Let me explain. Let's take SQL database and create simple table Person with few records inside:

id | name   | age
-----------------
1  | Alex   | 24
2  | Brad   | 34
3  | Chris  | 29
4  | David  | 28
5  | Eric   | 18
6  | Fred   | 42
7  | Greg   | 65
8  | Hubert | 53
9  | Irvin  | 17
10 | John   | 19
11 | Karl   | 23

Now, it's the part, I would like to focus on - id is the INDEX.

So far, I thought it works in this way: when a table is being created the INDEX is empty. When I am adding new record to my table the INDEX is being recalculated based on some alghortims. For example:

Grouping one by one:

1    ... N
N+1  ... 2N
     ...
XN+1 ... (X+1)N

so, for my example with size = 11 elements and N = 3 it will be like this:

id | name   | age
-----------------
1  | Alex   | 24     // group0
2  | Brad   | 34     // group0
3  | Chris  | 29     // group0
4  | David  | 28     // group1
5  | Eric   | 18     // group1
6  | Fred   | 42     // group1
7  | Greg   | 65     // group2
8  | Hubert | 53     // group2
9  | Irvin  | 17     // group2
10 | John   | 19     // group3
11 | Karl   | 23     // group3

So, when I am using query SELECT * FROM Person WHERE id = 8 it will do some simple calculation 8 / 3 = 2, so we have to look for this object in group2 and then this row will be returned:

8  | Hubert | 53

This approach works in time O(k) where k << size. Of course, an alghoritm to organise rows in groups is for sure much more complicated but I think this simple example shows my point of view.

So now, I would like to present another approach, which has been showed me today.

Let's take once again this table:

id | name   | age
-----------------
1  | Alex   | 24
2  | Brad   | 34
3  | Chris  | 29
4  | David  | 28
5  | Eric   | 18
6  | Fred   | 42
7  | Greg   | 65
8  | Hubert | 53
9  | Irvin  | 17
10 | John   | 19
11 | Karl   | 23

Now, we are creating something similar to Hashmap(in fact, literally it is a Hash Map) which maps id to address of row with this id. Let's say:

id | addr
---------
1  | @0001
2  | @0010
3  | @0011
4  | @0100
5  | @0101
6  | @0110
7  | @0111
8  | @1000
9  | @1001
10 | @1010
11 | @1011

So now, when I am running my query: SELECT * FROM Person WHERE id = 8

it will map directly id = 8 to address in memory and the row will be returned. Of course complexity of this is O(1).

So now, I have got few questions.

1. What are the adventages and disadventages of both solutions?

2. Which one is more popular in current database implementations? Maybe different dbs use different approaches?

3. Does it exist in nonSQL dbs?

Thank you in advance


texts: I am trying to break replication between two SQL 2000 databases.  Can this be done while the databases are live? or do they need to be taken offline?


texts:
Original Question:

Oracle dump imported with zero rows

I have exported the oracle dump from source by using

exp username/password file=filepath/filname.dump

I tried to import the exported dump into another system/oracle DB by using the following command

created a new user in dest system

imp file=filepath/filname.dump fromuser=sourceuser touser=createddestuser

username:sys as sysdba

password:******

All tables imported successfully

But zero records in all imported tables.

This is the first time i have facing the problem.Please help me soon.Thanks in advance.

In Source DB there are two tablespace called tablespace 1 and tablespace 2,but in destination DB there is no such tablespace.So I created manually in dest system and Dump imported successfully with all rows.

Updated Question:

Should i need the same tablespace in destination system as same as source system for DUMP import??


texts: Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use?

texts:
I would like to use code that I developed in C# CLR to be used in all of the databases on the system so that I don't have to set each to trustworthy and turn CLR on and keep a bunch of the same code inside each one.

Is there a best way to do this from an administrative and security standpoint? The CLR functions are very basic like string breakers, email validation, url en/decode, base64, and etc. I would like only the dbo schema in each database to be able to access the functions.

Is there any simple way to do this?
Also I am not clear if the CLR dll is embedded and if I move the database, it tags along, or do I have to move the dll as well.

Thanks


texts: Database design for companyCode and branchCode

texts:
I'm using JPA, hibernate and mariadb and I've come across a db design situation like this:
There are two entities Company and Branch with one to many relationship, each company has many branches. Company will have companyCode in the format of "A01", "A02"..,"A99", "B01", "B02",...,"B99",..."Z01",...,"Z99". And Branch will have branchCode with format companyCode + "-" + "branchSequence" i.e. "A01-001", "A01-002"...
I have beat myself for a good solution and the only decent one I thought of is using table to store companySequence and branchSequence and use trigger or hibernate custom generator to populate companyId and branchId.
I want to ask is there any better way ? Performance is not a problem because Company and Branch table are only writable by admin.


texts:
I have a script to drop several objects in the database (including queues) -- when it partially fails I need to recreate the objects that have already been dropped to continue testing the script.  For example, if the queue doesn't exist, the script fails when trying to stop it. I want to use this for Rails migrations: new dev and test schemas won't have the queues or database links that the teardown script attempts to drop, for example.

Many frameworks allow one to run commands in a test or pretend mode (e.g. rails generate -p).  Is there any similar functionality in Oracle for testing commands that one cannot roll back?

I understand I can do conditional checking for the presence/state of database objects, but would prefer to avoid creating a bunch of checks and dynamic SQL if there's a better way.


texts:
How do you manually run mongod from the commandline, so it outputs all logging directly in your terminal?

I'm trying to diagnose a crashing Mongo 3.4.11 database on Ubuntu 16. When I try and start it via sudo service mongodb start it says:

Job for mongodb.service failed because the control process exited with error code. See "systemctl status mongodb.service" and "journalctl -xe" for details.

However, systemctl status mongodb.service gives me an equally useless message:

● mongodb.service - LSB: An object/document-oriented database
   Loaded: loaded (/etc/init.d/mongodb; bad; vendor preset: enabled)
   Active: failed (Result: exit-code) since Wed 2018-02-07 20:32:17 UTC; 26s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 2392 ExecStop=/etc/init.d/mongodb stop (code=exited, status=0/SUCCESS)
  Process: 2913 ExecStart=/etc/init.d/mongodb start (code=exited, status=1/FAILURE)

Feb 07 20:32:16 proddb1 systemd[1]: Starting LSB: An object/document-oriented database...
Feb 07 20:32:16 proddb1 mongodb[2913]:  * Starting database mongodb
Feb 07 20:32:17 proddb1 mongodb[2913]:    ...fail!
Feb 07 20:32:17 proddb1 systemd[1]: mongodb.service: Control process exited, code=exited status=1
Feb 07 20:32:17 proddb1 systemd[1]: Failed to start LSB: An object/document-oriented database.
Feb 07 20:32:17 proddb1 systemd[1]: mongodb.service: Unit entered failed state.
Feb 07 20:32:17 proddb1 systemd[1]: mongodb.service: Failed with result 'exit-code'.

Nothing's written to the log at /var/log/mongodb/mongodb.log.

I tried manually launching the daemon via:

sudo -u mongodb /usr/bin/mongod --config /etc/mongod.conf

but after a few seconds, this simply returned with:

Aborted (core dumped)


texts: SQL Server database has very small backup

texts:
I have a database of 216 GB. 75% is used by data, the rest is used by index and log. When I check free space in the database files I get values around 300 MB. The database is split over 3 data files. When I take a full backup without compression I get a backup of 38 GB.

Can anyone explain this? Can I recover any disk space from the database?

Update: If I try to restore the backup it requires over 200 GB of diskspace.

Update2: I got these values by running:

select Name,
(convert(float,size)) * (8192.0/1048576) File_Size,
(convert(float,fileproperty(name,'SpaceUsed'))) * (8192.0/1048576) MB_Used,
((convert(float,size)) * (8192.0/1048576) - (convert(float,fileproperty(name,'SpaceUsed'))) * (8192.0/1048576)) MB_Free
from sysfiles
order by fileproperty(name,'IsLogFile')

The result is:

QASDATA1    74124   73383,75    740,25
QASDATA2    69548   69114   434
QASDATA3    72288   71972,6875  315,3125
QASLOG1 374,8203125 112,1015625 262,71875

Update 3: Here is some more information about the backup itself, the query is below in the responses:

QAS x:\sql\SQL42\QAS\QAS_log_20121030.BAK   51 MB   160 MB  7 Seconds   2012-10-30 01:00:00.000 16269000022730200001    16271000002874600001    Transaction Log SQL42   FULL
QAS x:\sql\SQL42\QAS\QAS_data_20121025.BAK  38820 MB    224903 MB   3458 Seconds    2012-10-25 09:55:16.000 16265000012013400138    16265000012581700001    Full    SQL42   FULL


texts: Is there an option to ignore errors and continue copy in mysqldbcopy


texts:
I want to change the length of a replicated column.  I ran this:

exec  sp_articlecolumn
    @publication = N'DIONLINE-SQL2014'
    , @article = N'Valuation'
    , @column = N'WallConstruction'
    , @operation = N'drop'
    , @force_invalidate_snapshot = 1
    , @force_reinit_subscription = 1

Result: (1 row(s) affected)

Then this

alter table Valuation.Valuation
  alter column WallConstruction varchar(255) null

Result: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'WallConstruction' because it is 'REPLICATED'.

I've successfully used a very similar approach to perform a very similar task - altering the Valuation.Valuation.RoofConstruction column (except I removed that column from the publication via the UI) - so I'm puzzled why this won't work.

I plan eventually to add the column back into the publication.

What's going on here, or where do I look to find out?

Edit

Luckily the column I was trying to alter had not yet received any values, so I was able to do this:

alter table Valuation.Valuation
  drop column WallConstruction

alter table Valuation.Valuation
  add WallConstruction varchar(255) null

but that doesn't really seem like the most useful answer.  So if anyone has a better solution...


texts: Insert a group of consistent item with foreign key but colliding with existing items

texts:
Is there a way to insert a group of items that are dependent but consistent between them with unique primary keys and foreign keys but that are colliding with items in database.

For example, given a table A:

id  primary key,
name

and a table B:

 id primary key,
 name,
 id_a'  -- foreign key on A

I want to insert:

INSERT INTO A(id, name) VALUES (1, "a");
INSERT INTO B(id, name, id_a) VALUES (1, "b", 1);

But the keys A.id or B.id could be taken already. Is there a way to insert my elements with auto setting key and foreign keys in a consistent way without colliding with existing elements?


texts: How to insert multiple records using XML in SQL server 2008

texts:
i use SQL server 2008 in my web application back end. Apparently i iterate through all the records from the C# code whenever there is a multiple insertion scenario. i have never tried the multple insertion using XML. And i think after reading many blogs about XML manipulation using SQL server 2008 the process is pretty tideous ..
So my question is..

Is Insertion via XML much efficient than the traditional insertion?
Is the a generic way in which i can serialize a class in C# --> Manipulate XML in SQL -->Insert also Read data as XML--> Deserialize XML to usual object?


texts: Mongodb replica config slaveDelay but data still sync immediately

texts:
I've set slaveDelay to 600s but when the Primary data change, the Secondary data change immediately. Here's the curent configuration of repl with 2 server.

"members" : [
{
    "_id" : 0,
    "host" : “primary.host1:27017",
    "arbiterOnly" : false,
    "buildIndexes" : true,
    "hidden" : false,
    "priority" : 1,
    "tags" : {

    },
    "slaveDelay" : NumberLong(0),
    "votes" : 1
},
{
    "_id" : 1,
    "host" : “secondary.host2:27017",
    "arbiterOnly" : false,
    "buildIndexes" : true,
    "hidden" : true,
    "priority" : 0,
    "tags" : {

    },
    "slaveDelay" : NumberLong(600),
    "votes" : 1
}]

I don't know where did I go wrong, or how is the slaveDelay work, since the secondary data should update after 600s.


texts: How can I remove info message and column lines from SQL query result file?

texts:
For example, I have a query like below and I want to remove info/warning messages (like 1 rows affected) and header's lines (like -------------) rows from SQL output file. I can disable info/warning messages on SQLCMD with -m 1 -W parameters but I want to do this with T-SQL. How can I do that?

DECLARE @tab char(1) = CHAR(9)
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'sqldba',
    @from_address = 'DISPLAYNAME <hostname@domain.com>' ,
    @subject = 'SQL Instance Info' ,
    @recipients = 'to1@domain.com;to2@domain.com',
    @copy_recipients= 'cc1@domain.com;cc2@domain.com',
    @body = 'Results on attachment',
    @body_format = 'HTML',
    @query = N'
SET ANSI_WARNINGS OFF
SET NOCOUNT ON
    SELECT  SERVERPROPERTY(''ServerName'') as ''Instance_Name'',
    exc.local_net_address as ''IP_Address'',  exc.local_tcp_port as ''Port''
    FROM sys.dm_exec_connections exc
    WHERE  exc.session_id = @@SPID
    ',
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'InstanceInfo.csv',
    @query_result_separator=@tab,
    @query_result_width =32767,
    @query_result_no_padding=1,
    @exclude_query_output=1,
    @query_result_header=1

Results seems like below:

Instance_Name   IP_Address  Port
-------------   ----------  ----
HOSTNAME    123.456.789.0   1433
(1 rows affected)

It should be:

Instance_Name   IP_Address  Port
HOSTNAME    123.456.789.0   1433


texts: Order of foreign keys/cascades processing in SQL Server

texts:
I have a parent table and 2 (or more) children tables with foreign key defined with some cascade option. So when an entry from a parent table is being deleted, SQL Server will check 2 (or more) children tables to perform relevant cascade action. The question is - is there a specific order in which SQL Server will process those cascade actions - and, correspondingly, is there an order in which locks are taken on those children rows/pages/tables?
That seems quite relevant for deadlock investigations, however I couldn't find info in BOL or here.


texts: MySQL count limit on COUNT + GROUP BY results for performance benefit

texts:
I have a very large MySQL8 database, currently using the InnoDB engine, that I have to perform some COUNT() queries on. Specifically, something like this:
SELECT parameter, COUNT(*) FROM table WHERE ... GROUP BY parameter
Count operations are relatively slow, since they have to scan the full table (or in this case, the full results of the WHERE condition), so I'm wondering if I can put a limit on each result as a way to speed up the query.
For example, consider a table foo with a single indexed column country. If I want to query the distribution of countries in this table, right now I run something like this:
mysql> SELECT country, count(*) FROM foo GROUP BY country ORDER BY count(*) DESC;
+---------+----------+
| country | count(*) |
+---------+----------+
| US      |   401238 |
| CA      |    14316 |
| HN      |    10636 |
| MX      |     5534 |
...

... in my test table, which has ~500k entries, this takes ~3s to complete. However, if I only want the countries without the count, that query only takes a few milliseconds. This is not surprising because in that second scenario there is no COUNT() and hence mysql doesn't have to search the whole table. So to my question, is there any way I could say "if the count is higher than, say, 10k - stop counting for this country". I would expect a result like the following:
+---------+----------+
| country | count(*) |
+---------+----------+
| US      |    10000 |
| CA      |    10000 |
| HN      |    10000 |
| MX      |     5534 |
...

...and ideally a notable speed up, assuming MySQL could scan the table and once it hits 10k records for US, leverage the index to skip all 390k additional US entries and continue counting other countries?
I could also achieve this result by making many independent queries. E.g. I could run SELECT country FROM foo GROUP BY country and a subsequent SELECT * FROM foo WHERE country = bar LIMIT 10000 for each country bar returned by the first query, but since my actual database is only counting a subset using a WHERE condition, that WHERE condition would be run for every query which would add a lot of overhead.


texts: Is it possible for unique column to contain multiple null values SQL Server 2008

texts: I want to migrate data contained in an Oracle Table A to SQL Server. The table A contains a column CIN with unique constraint but mutliple null values. The problem is that SQL Server is rejecting the data. Is there any workaround for this ?


texts: Create Trigger that updates the same table before (or after ??) the Insert

texts:
I have just started using triggers and one thing that I would need to do is to create a trigger that updates the same table that's inserting the row.

TABLE:

CREATE TABLE `city` (
  `city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `city` varchar(50) NOT NULL,
  `country_id` smallint(5) unsigned DEFAULT '0',
  `country` varchar(150) DEFAULT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`city_id`),
  KEY `idx_fk_country_id` (`country_id`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6009 DEFAULT CHARSET=utf8;

Trigger:

DELIMITER $$
CREATE TRIGGER `populate_country_id` BEFORE INSERT ON `city`
FOR EACH ROW
BEGIN

IF NEW.country_id = 0

THEN
    UPDATE city
    INNER JOIN country B ON city.country = B.country
    SET NEW.country_id = B.country_id;

END IF;

END $$
DELIMITER ;

MySQL is just returning: Can't update table 'city' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Is there any way I can make this work?

Thanks everyone!


texts: Reviewing all code on a SQL Server Instance

texts:
Shortly our development team will be moving an existing SQL Server 2008R2 instance into our Production environment. Ahead of this I have been asked to review all the code on it for anything malacious (e.g. wiping their own order history from the sales database).

This has come out of the blue and I'm not sure it's particularly physically feasible but I'm willing to give it a good go, at least from the SQL side of things. The question is, where can code exist? My draft list is

Stored Procedures
Functions (all)
Table Triggers
Assemblies (shouldn't be any by our developers)
Rules (shouldn't be any by our developers)
System Database versions of the above, modified by users
SSIS Packages
Server Triggers
Unexpected LinkedServers
SQL Agent Jobs
SQL Agent Alerts


texts: DB for storing simple time-stamped values

texts:
I don't have that much experience in databases but I need to accomplish this as best as I can. It is rather specific.

Database will contain only data structured like this:
dataID-timestamp-value

Value could be of any datatype. There will be up to 20 million new entries per hour. Read queries would always look for one or few dataIDs in a specific time period, i.e. - yesterday between 12:00 - 13:00 or something like that. And these queries need to be fast on relatively weak hardware. Old data can be overwritten if space is scarce.

I am open to any suggestions, sql, nosql and so on.

Thank you.


texts: Starting mongod daemon with a config file for a shard

texts:
In a Linux machine, I changed the mongod.service file to run a config file I created.

ExecStart=/usr/bin/mongod --config /etc/some_name.conf

When I start mongod with this config file manually, it runs fine. Yet when I use this config file to run the mongod daemon, it fails.

I did run systemctl daemon-reload after the change, yet still failed.

Anybody also dealt with this issue?

Update

I've isolated the error in my log file to this line:

I STORAGE  [initandlisten] exception in initAndListen: IllegalOperation:
Attempted to create a lock file on a read-only directory: /data/db, terminating
I CONTROL  [initandlisten] now exiting

I've tried changing the ownership of the directory /data and /data/db to mongodb:mongodb but doesn't fix this error. Also changed the permissions to the directories with sudo chmod -R 775 /data but still same error when I run:

sudo service mongod restart
sudo service mongod status

Status is still failed and same error in log file.


texts: How to get the function name of a regprocedure in PostgreSQL?

texts:
I'm trying to write a function to get the name of the function associated with a given regprocedure variable, as follows:

CREATE OR REPLACE FUNCTION get_funcname(_fn regprocedure)
  RETURNS text AS
$func$
  SELECT proname::text
  FROM pg_catalog.pg_proc AS p
  JOIN pg_catalog.pg_namespace AS ns
    ON p.pronamespace = ns.oid
  WHERE p.oid = _fn;
$func$  LANGUAGE sql;

I expect to get my_func in return for

SELECT get_funcname('my_func(text, variadic text[])');

However, the above is giving me the error:

ERROR:  syntax error at or near "variadic"
LINE 1: SELECT get_funcname('my_func(text, variadic text[])');
                                ^
CONTEXT:  invalid type name "variadic text[]"

Can anyone help explain how to fix the function or syntax?


texts: Only need the latest row - should I keep old records?

texts:
I have a use case where I'm crawling a site to gather information. This is happening maybe every 12-48 hours. With each crawl, data to populate a new document is gathered. I only really care about the latest read - older reads may have some potential future value but I'm not really interested in designing an access pattern which needs to have access to these records.
I understand that keeping old records implies that the amount of storage I use will steadily grow overtime (though each row only contains a relatively small amount of data). What I want to know, is this; how can I get the best performance out of the system? Practically, I mean by that, what will see the least cost whilst still maintaining good performance. As far as I can see, I have a couple of options:

Never update an old record. Have a sort key with an id to indicate which is the latest version of a particular reading, and only take the latest readings. Presumably this carries some performance issues as it implies first reading from the database to determine the latest reading id.

Always upsert new rows - this would have the advantage of not having to do much work to determine the correct row - only the latest row will exist in the table so I can just take that. Better for storage I'm guessing, but I'd also guess that writing to the database will be more of a problem, as I'd need to first check if a row exists before writing it

I'm fairly new to DynamoDB so I wonder if anyone can point out what's likely to be the best way to optimize this


texts: Working example of OPENDATASOURCE command using SQL Authentication

texts:
I need to run some ad-hoc queries on several hundred servers that are not part of a domain.  Each server has a low-privilege SQL user account that has read-only access to a few tables of interest.

My idea was to have the names of these servers stored in a table, and use that table to drive an OPENDATASOURCE or OPENROWSET command.

The servers are either in different domains, non-trusted domains, work-groups, etc. A big mess.

I cannot for the life of me find an example of how to configure these commands to use a SQL Server account/password combination.

Note:  This happens in a sandbox, it is not meant for the real world.  It is simply a proof of concept, so security is not a major concern.  These servers are cranked out by a process that is simulating a real-world growth of a network.

For the record, I was able to get OPENROWSET to work:

SELECT *
FROM OPENROWSET('SQLNCLI',
   'DRIVER={SQL Server};SERVER=MyServer;UID=MyUserID;PWD=MyCleverPassword',
   'select @@ServerName')


texts: sys.dm_tran_version_store_space_usage reports always zero space used

texts:
I was learning about RCSI when I discovered something strange on Azure SQL Database.
The DMV sys.dm_tran_version_store_space_usage always reports 0 as the space used by the version store, even if I run a CRUD workload just before.
To demonstrate this behavior, I created a little test.
-- Server info
select @@version as sql_version;

-- Database info
select
    is_read_committed_snapshot_on,
    snapshot_isolation_state_desc
from sys.databases
where database_id = db_id();

-- Just to be sure the current database has its version store empty
select reserved_page_count as pre_workload_space_count
from sys.dm_tran_version_store_space_usage
where database_id = db_id();

-- Test workload
drop table if exists RCSI_TEST;

create table RCSI_TEST (
    id uniqueidentifier default newid()
);
go

insert into RCSI_TEST default values;
go 100

update RCSI_TEST
set id = newid();

delete from RCSI_TEST;

-- Metrics
select reserved_page_count as post_workload_page_count
from sys.dm_tran_version_store_space_usage
where database_id = db_id();

waitfor delay '00:01:30'; -- Just to be sure! ;)

select reserved_page_count as post_cleaning_space_count
from sys.dm_tran_version_store_space_usage
where database_id = db_id();

I ran this script both on SQL Server 2019 Developer Edition (on Docker) and on an Azure SQL Database (tier S0, 10DTU) and here are the results.
SQL Server 2019
sql_version
Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64)Sep 23 2020 16:03:08    Copyright (C) 2019 Microsoft Corporation    Developer Edition (64-bit) on Linux (Ubuntu 18.04.5 LTS) <X64>

is_read_committed_snapshot_on   snapshot_isolation_state_desc
1                               ON

pre_workload_space_count
0

post_workload_page_count
8

post_cleaning_space_count
0

Azure SQL Database
sql_version
Microsoft SQL Azure (RTM) - 12.0.2000.8     Oct  1 2020 18:48:35    Copyright (C) 2019 Microsoft Corporation

is_read_committed_snapshot_on   snapshot_isolation_state_desc
1                               ON

pre_workload_space_count
0

post_workload_page_count
0

post_cleaning_space_count
0

SQL Server 2019 appears to behave correctly, reporting 8 pages worth of data in the version store right after the workload and then be cleaned up after a minute or so.
On Azure SQL Database, though, the space used is always zero! Is that a correct behavior? What does it means?
Books Online reports this DMV to be compatible both with SQL Server and Azure SQL Database but also says "The following query can be used to determine space consumed in tempdb, by version store of each database in a SQL Server instance.". As far as I know Azure SQL Databases do have a limited scope to their parent instance by design. Can that be the root cause?
https://docs.microsoft.com/it-it/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-version-store-space-usage?view=sql-server-ver15
I know that Azure SQL Databases run under RCSI by default and also that they have a fixed amount of tempdb space based on the tier you get, so I was concerned about filling that up with long running transaction or other processes keeping lots of records in the version store for too long. But I cannot manage something I can't measure, right?


texts: Why is VACUUM FULL locking ALL databases on the cluster?

texts: From everything I have read about VACUUM FULL, I would expect it to lock the database I'm running it on, but it renders every database in the cluster inaccessible. Is there perhaps something we might have wrong in our configuration? I do have autovacuum on, but we have a few unwieldy databases I'd like to clean up thoroughly. This is PostgreSQL v10. Thanks for any insights.


texts: Unable to change innodb_buffer_pool_size MySQL 5.6 in CentOS 6.7

texts:
I have searched all over but couldn't find where service mysqld restart picks up the configuration file from:

sudo find / -name "*.cnf" | grep my

/usr/share/doc/mysql-community-server-5.6.33/my-default.cnf
/usr/share/mysql/my-default.cnf
/var/lib/mysql/auto.cnf
/var/lib/mysql/my.cnf
/etc/my.cnf

My MySQL 5.6.33 is running on 1 GB of InnoDB Buffer Pool whereas none of these files has that setting. I have setting

[mysqld]
innodb_buffer_pool_size = 4G

in the file copied from /usr/share/mysql/my-default.cnf to /etc/my.cnf - I did this:

service mysqld restart --defaults-file=/etc/my.cnf

Still not working.


texts: What is a cost of search for records in a table with an equality predicate on a non key attribute A and a static hash organization?

texts:
To speed up the search for records in a table with an equality predicate on a non key attribute A and selectivity factor fs, is preferable:

A sequential organization on a key attribute ?
A static hash organisation on a key attribute ?
An inverted index on A

I have to justify the answer and give an estimate of query execution cost in all three cases.
To my mind it was

log2⌈Npag(R)⌉
according to this :

? No info

it depends if it is a clustered or an unclustered index.

Professor answer :

Npag(R) "because indexing is on the key" and "according to the screenshot" (which is provided above, but for me he is wrong on the cell as far as it isn't a memory operation but a equality search)
Npag(R)
Yeah, it depends ...

I don't understand why is he saying the first and where does he take the second. My teacher uses this book he co-writted.


texts:
I truncated a partition, and would like to import it from backup.

How can I import a partition from the dump file?

Export log:

. . exported "XY_PROD"."XY_LOG_PLSQL"              69.16 MB  320459 rows
. . exported "XY_PROD"."XY_RESPONSE":"SYS_P25129"  2.198 GB   67804 rows
. . exported "XY_PROD"."XY_RESPONSE":"SYS_P26385"  973.4 MB   31742 rows
. . exported "XY_PROD"."XY_RESPONSE":"SYS_P25395"  2.184 GB   88242 rows
. . exported "XY_PROD"."XY_RESPONSE":"SYS_P25891"  3.366 GB  106484 rows

Tried:

INCLUDE=PARTITION:\"IN \(\'SYS_P25129\'\)\"
INCLUDE=PARTITION:"XY_RESPONSE":"SYS_P25129"
INCLUDE=PARTITION:\"XY_RESPONSE\":\"SYS_P25129\"

Getting:

ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-00920: invalid relational operator

The whole table is several GB. If there is a way, I don't want to import whole table.


texts: Is it possible to do a datapump import with a different user than did the export?

texts:
I have one db dump below is the log file for that dump

Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** PARALLEL=1 SCHEMAS=tmsmv DUMPFILE=tmsmv-20120706.dmp LOGFILE=tmsmv-20120706.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 146.1 MB
...........

I want to restore this dump using another user tmsmv using below command:

sql>conn / as sysdba
CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/home/..../';
GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO tmsmv;
GRANT READ, WRITE ON DIRECTORY SYS.DUMP_DIR TO EXP_FULL_DATABASE;
GRANT READ, WRITE ON DIRECTORY SYS.DUMP_DIR TO IMP_FULL_DATABASE;

And then importing with:

impdp tmsmv/***** schemas=TMSMV directory=DUMP_DIR dumpfile=tmsmv-20120706.dmp

While restoring I am facing below error message:

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation

Please help me to solve this issue.


texts: SQL Query returning same value while using where 1 condition

texts:
I have created a temp table and inserted the values as given below.

create table #temp( val int );

insert into #temp values(333);
insert into #temp values(222);
insert into #temp values(111);

On querying the below select statement I got 333 as the answer.

Select *
from #temp a
Where 1 =(
    Select COUNT(VAL)
    from #temp b
    where a.val <= b.val
);

Result:

val

333

Can you please help me understand how SQL server came to this solution.


texts: Using MongoDB mongorestore to append data to collection with existing records

texts:
We are trying restore documents in the dump that are not present in a collection and skip over documents in the existing collection using mongorestore.

This is how we run our restore command:

mongorestore --host localhost \
             --authenticationDatabase admin \
             -u "$MONGODB_ADMIN" \
             -p "$MONGODB_ADMIN_PASSWORD" \
             --db "$MONGODB_DATABASE" \
             $backup_dir/$base_filename/db_name

But we end up with this error when the dump contains the same documents as are present in the collection

  E11000 duplicate key error collection: db_staging.events index: id dup key: { : ObjectId('5886d6bddb4d087e65111111') }

The mongorestore version I am using is r3.0.13


texts: Is there any slowdown in casting from varchar to text?

texts:
In writing this answer, I'm just wondering if there is any slow down in casting from varchar to text or if that cast is just a compile-time trick. I've been told they're represented the same way and they're binary-coercable. Would it be faster to do

SELECT varchar1
FROM table
WHERE varchar1::text = 'foo';

Or,

SELECT varchar1
FROM table
WHERE varchar1 = 'foo'::varchar;


texts: Data Warehouse design for reporting against data for many time zones

texts:
We are trying to optimize a data warehouse design that will support reporting against data for many time zones. For example, we might have a report for a month's worth of activity (millions of rows) that needs to show activity grouped by the hour of the day. And of course that hour of the day has to be the "local" hour for the given time zone.

We had a design that worked well when we just supported UTC and one local time. The standard design of Date and Time dimensions for UTC and local time, id's on the Fact tables. However, that approach does not seem to scale if we have to support reporting for 100+ time zones.

Our Fact tables would get very wide. Also, we'd have to solve the syntax issue in SQL of specifying which date and time id's to use for grouping on any given run of the report. Perhaps a very large CASE statement?

I've seen some suggestions to get all the data by the UTC time range you are covering, then return it to the presentation layer to convert to local and aggregate there, but limited testing with SSRS suggests that will be extremely slow.

I've consulted some books on the subject as well, and they all seem to say just have UTC and convert on display or have UTC and one local. Would appreciate any thoughts and suggestions.

Note: This question is similar to: Handling time zones in data mart/warehouse, but I cannot comment on that question, so felt this deserved its own question.

Update: I selected Aaron's answer after he made some significant updates and posted sample code and diagrams. My earlier comments on his answer won't make much sense anymore as they referred to the original edit of the answer. I'll try to come back and update this again if warranted


texts: What is the right data type to store only -1 0 and 1 in postgres column?

texts:
I want to store only 3 states in a column (if it was 2, I would use a boolean)
I considered using an ENUM('up', 'down', 'none') but it seems ENUMs take some significant space
Is there a better way to store only -1 0 and 1 in a PostgresQL column?


texts:
I have come across the terms index ANDing or ORing while reading BOL. But there seems to be little explanation as to what they actually are.

The following is the link:

https://msdn.microsoft.com/en-us/library/ms187373.aspx

The paragraph the term appears:

  If  indexes are used in a single hint list, the duplicates are ignored and the rest of the listed indexes are used to retrieve the rows of the table. The order of the indexes in the index hint is significant. A multiple index hint also enforces index ANDing, and the query optimizer applies as many conditions as possible on each index accessed. If the collection of hinted indexes do not include all columns referenced by the query, a fetch is performed to retrieve the remaining columns after the SQL Server Database Engine retrieves all the indexed columns.

Can someone explain they they work or give a link for further reading?


texts: MySQL Replication - 1 Master - 2 Slaves. Inserting works for One, not the other

texts:
I am a Python developer, working with 3 databases, configured in Master -> slave,slave style.

Each slave has their own application associated with it.

I am not too familiar with database administration as it relates to replication, but I do have a working scenario between the Master, and one of the slaves

The first slave stays in synch at all times properly at this time.

The second slave however, fails on the first insert

Table creation queries, user creaiton all replicates fine, but when I input any values, the first slave gets the record, while the second slave does not.

I tried importing the master DB to the second slave, updates to records will replicate, but new records will not.

My research leads me to believe this is an issue with autoincrement but I have not been able to use this to fix the issue. As opposed to a hack and slash method which is not working I figured id ask here.

If there is any more info I can provide, please let me know.

I am on Windows. Using WAMP Server 2.5 (MySQL 5.6.17)

Thanks for reading

EDIT: I am using binlog_format=mixed


texts: Is it possible to return a custom error for a logon trigger?

texts:
I'm trying to get a logon trigger to return a custom error.  Thus far everything I have tried causes it to return the standard error:

My trigger looks like this:

CREATE TRIGGER Test_Logon_Trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN

IF ORIGINAL_LOGIN()= 'login_test'
    BEGIN
        raiserror('Custom Error',25,16)
        --PRINT 'Print'
        --ROLLBACK;
    END
END;

I have tried printing an error message and using a RAISERROR command separately and in combination.  I've also tried using the WITH NOWAIT clause on RAISERROR.  In all cases it just sends the message to the error log.  Does anyone know how to modify the error being returned displayed on the screen?  I'm even ok with returning a second error if necessary.


texts: Should you encrypt the system databases?

texts:
A finding from a STIG evaluation, CCE-19832-5 reports that the [master] database should be encrypted with a Database Master Key using an encrypted password that meets DoD standards.

This a result of an AppDetective scan; it states:

  Resource key 'database_containing_encrypted_key' not found.=[master].

I am not deliberately storing anything in the master database.

I've encrypted all user databases but my question is: do you encrypt the system databases, specifically master?  I am using TDE with SQL Server 2012.


texts: Why cp1256 collation is not 1 byte in MySQL?

texts:
I've read an article that cp1256 collation takes 1 byte for Arabic characters, but when I created an index on the column that was cp1256 with type of varchar(900), only 767 characters picked up for index purposes:

  CREATE TABLE col (
   id int(11) NOT NULL AUTO_INCREMENT,
   arabic varchar(900) CHARACTER SET cp1256 NOT NULL,
   PRIMARY KEY (id),
   KEY (arabic(767)
  ) ENGINE=InnoDB;

Arabic varchar(900) truncated to (767) for my index.
Why is that the case?


texts: MongoDB permission to view collections not working

texts:
I have 2 dbs:

admin
ops

I have a user with a readWriteAnyDatabase role but he cannot list the collections in the ops db.

I have another user with a root role and he succeeded in listing the collections for the ops db.

I try to view the collections in ops

The error I am getting:

  Not authorized to execute command: listCollections...

using mongodb: 3.4.7

In the mongoDB config I only added auto=true.

What can be the cause?


texts:
I have an Oracle 11.2.0.3 about 700GB in size running in noarchivelog mode in a pretty slow storage setup. It barely gives IOPS and there is resource crunch in terms of hardware resources. The transactions hitting the database is heavily read/write intensive and redo generation is quite high (near to 8MB/Sec) and naturally it is finding difficult to write to the disk.

What can be done in such a scenario? Can nologging in certain tables boost the performance? Data corruption because of nologging is a concern, I understand that and can be taken care of. But can someone give insight of how truly enabling nologging helps in this situation? or if it doesn't help, what are the other options I have?


texts: Missing files from mysql odbc driver installer

texts:
I'm trying to install the latest (5.2.7) version of the MySQL ODBC driver on Windows server 2008 R2, but when starting the installation, I get the following error:

MySQL Workbench installs fine and works well. I just need to connect to MySQL from my windows desktop application to import some data.

Note. I've installed Microsoft Visual C++ redistributable and rebooted, the error remains.


texts: What's the structure of a matchmaking server?

texts:
This is my current structure of my matches table:

id, title, description, max_players, host_username, host_ip

and there's another table called matches_players:

match_id, player_username

This requires two queries to get the matches list and the player count of each one, so is there a better way?

I'm using:

SELECT COUNT(*) FROM `matches_players` WHERE `match_id` = ?

to get the number of players in a match.

Server inserts one row to the matches table, and then another one to add the host to the matches_players table.

It is working, but it makes the code more complex, saving the matches list, then doing lot of queries to get the players number. I was wondering if there is a better way?


texts: SQL server Principal and Mirror logins and permissions Issue

texts:
In a mirroring setup where server A is my principal and server B is my mirror, I had some issues related to logins and permissions at the time of fail-over.

During fail-over Server B became my principal and Server A my mirror but I was facing login issues on server B and I had to recreate the logins on server B with the same permissions they had on server A.

What is the correct way to handle this scenario? Do I have to create the logins on server B every time I create a login on  server A or is there a better way to handle this?

Are there any scripts available to compare and synchronize logins?


texts: Get problem with Binlog_Do_DB in Master configuration

texts:
I have followed all the rules for replication. The steps are
I have changed the my.cnf file. My db name is new_db.

server-id   = 1
log_bin = D:/xampp/mysql/mysql-bin.log
binlog_do_db = new_db

Grant the replication for slave

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
USE new_db;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

After that the result is

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      481 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Binlog_Do_DB column is empty. new_db should be there.

I don't understand what I missed. Please help to solve the problem.


texts: Backing up & restoring 10-20 SQL Server databases to a ~synchronous state?

texts:
I need to backup up 10-20 SQL Server 2008 R2 databases with sizes between 10-50 GB, while they are online and used simultaneously by a single enterprise app. I also need to restore them to a state that is largely synchronized across all databases (I can afford up to a few seconds of desync between databases). The purpose is to capture production data for QA/DEV environments.

I would strongly like to not demand databases run in full recovery and to come up with a backup method that is dedicated to capturing data for QA environments and remains independent of a main backup process which is not under my control.

For my customers, it will take 1-2 hours to capture 20 full backups at ~30 GB each. This makes taking full backups sequentially unacceptable as the databases would be too desynchronized when running in simple recovery.

I'm looking for an idea better than these:

IDEA 1: SAN-level snapshot of VM disks. xcopy MDFs/LDFs from snapshot.

Once the copied files are attached to a different server instance, its recovery process should produce consistent databases that are snapshot pretty much simultaneously.
Googling around convinced me this is a bad idea, at least because I may get desync vs. master/msdb/etc.

IDEA 2: Orchestrate a complex backup & sync-restore across all databases

This requires me demanding databases run in full recovery, which I don't want. Start parallel backups for all databases well before the deadline (T0). Once T0 is reached, backup all logs (should take at most a few minutes). Take the resulting myriad of backups and try to restore them & roll logs forward/back to obtain a somewhat consistent state across databases, relative to T0.
This requires a lot of planning & scripting to have it used reliably so I would go to great lengths to avoid it.

Am I missing some other solution?

P.S.1: I would've loved being able to use db snapshots. The idea was to initiate a snapshot on each db (which should be over in seconds), then fully backup each one sequentially over the following minutes/hours. Then restore all of them on a different server and revert each one to the snapshot. AFAIK this scenario is not possible because snapshots can't be backed up along with the database. They can only be rolled back in place, on the server where they were created. In addition, they require Enterprise Edition which I don't have for all customers.

P.S.2: If you know of a 3rd party solution capable of producing cross-db synchronized backups please mention it.


texts:
Hello guys I've been all over the MongoDB documentation (http://docs.mongodb.org) and I can't find a solution to my problem. Imagine the following scenario:

I have 2 Nodes (1000Km apart). Both nodes can communicate with the other;
Node1 has several replicasets and therefore several Masters, Slaves and Arbiters;
Node2 has a replica (slave) and a hidden replica (hidden slave) of each Master in Node1.
Here's a diagram to help you guys understand

I want to know what might happen if:

The link between the two nodes fails;
My guess is the non hidden slaves in Node2 will be elected Masters. What happens when the link between nodes returns? 2 masters?

Node 1 fails completely;
Same scenario? 2 Masters when Node1 returns?

Is this the best way to do this? Any sugestions on how should I do it so I won't lose any information if Node1 fails completely (avoiding the two masters situation)


texts: Cannot run Sql Server Agent jobs with PowerShell script

texts:
The problem:
I have a customer that cannot run Sql Server Agent jobs with PowerShell script. It fails with this message:

Message
Executed as user: xxNC\SQLService. Unhandled Exception:
System.NullReferenceException: Object reference not set to an instance of an object.
at System.Environment.GetEnvironmentVariable(String variable, EnvironmentVariableTarget target)     at
System.Management.Automation.ModuleIntrinsics.GetExpandedEnvironmentVariable(String name, EnvironmentVariableTarget target)     at
System.Management.Automation.ModuleIntrinsics.SetModulePath()     at
System.Management.Automation.ExecutionContext.InitializeCommon(AutomationEngine engine, PSHost hostInterface)     at
System.Management.Automation.AutomationEngine..ctor(PSHost hostInterface, RunspaceConfiguration runspaceConfiguration, InitialSessionState iss)     at
System.Management.Automation.Runspaces.LocalRunspace.DoOpenHelper()     at
System.Management.Automation.Runspaces.RunspaceBase.CoreOpen(Boolean syncCall)
at Microsoft.SqlServer.Management.PowerShell .CustomRunspaceConfiguration.HandleAgentJob(RunspaceConfiguration config)     at
Microsoft.SqlServer.Management.PowerShell .CustomRunspaceConfiguration.Main(String[] args).  Process Exit Code -1073741819.  The step failed.

As I see it, it not about the powershell script itself. The problem is to execute a PowerShell script at all.

To test I have created a new job with some simple PowerShell, that I have testet on another Windows Server 2008 (Windows Server 2008 R2 Enterprise, with powershell 3.0), where it works. I can also run it from a PowerShell command on the problematic server. But when I create a Sql Server Agent job and run it, it is fails with the same message. So to me it seems that there is a problem with how PowerShell is configured compared to SQL Server…

Systeminfo:

Sql Server 2008 (SQL Server 10.50.2500)
Windows Server 2008 R2 Standard

I have checked the following:

1)  From PowerShell command line (and SQL Server PowerShell command, launched from sql management studio):
$PSVersionTable:

PS C:\Users\xxx> $PSVersionTable
Name                            Value
----                            -----
CLRVersion                      2.0.50727.5485
BuildVersion                    6.1.7601.17514
PSVersion                       2.0
WSManStackVersion               2.0
PSCompatibleVersions            {1.0, 2.0}
SerializationVersion            1.1.0.1
PSRemotingProtocolVersion   2.1

2)  Also this seems fine:
$Get-ExecutionPolicy  RemoteSigned

3)  If I run directly in SSMS a command like this:

xp_cmdshell 'PowerShell.exe $PSVersionTable'

I also get an error:

“The shell cannot be started. A failure occurred during initialization:
Object reference not set to an instance of an object.”
There is an error in the Event log, with this message: ‘ErrorMessage=Object reference not set to an instance of an object.’

So it must be the same error – I just do not have stacktrace from event log.

Now I am running out of ideas of where to look for the error. So any suggestions on where to look are very welcomed.
The only thing should be that PowerShell is only version 2.0. But I assume this is not that problem?
Maybe Sql Server Agent is not allowed to run powershell scripts? How do I check for that?
Or it could be a security issue? The owner of the job is 'sa'. While for the jobstep Run as entry it is "SQL Server Agent Service Account".

I have found very few posts on similar issue. Fx. Execute Remote Powershell script from SQL Agent but it does not solve my issue.

(I am mainly a .NET Developer so Sql Server and Powershell is not my strongest side).


texts: Which database to use when you have a 6 billion of rows inside and need to query rows with from list of IDs?

texts:
We are currently researching our case of storing the road distances between cities. Right now we have 6 billion of those distances.
Our structure right now in SQL Server is that we have a float which represents the relationship between cities.  For example if we have a city with Id 1 inside the Locations table and a city with Id 2 inside the same table, a row with distance from 1 to 2 will look like 1.2,'1000 miles'. That column is indexed.
So to get the distance from the city 1000 to the city 2535, we would find 1000.2535 inside the Distances.
Besides getting single distances, we need to select groups of 1000 distances from those 6 billion rows:
SELECT
  id
 ,distance
FROM
  Distances
WHERE
  id IN (1000.2535, 1.2, ...)

Right now we've only tested SQL Server on a local machine and it gives us around 300 ms for such query of 1000 rows, but only when we set a 50 ms timeout (this is needed for a lot of parallel requests from multiple users), if 50 ms timeout is not used it just grows exponentially like 300 ms for the first, 500 ms for the second, 800 ms for the third, etc.
And right now we taking a look at ElasticSearch specifically for mget.
So my questions are:

Which database would you recommend for such a use case?
What would you recommend besides what we've thought of maybe some other ideas like splitting into two different columns cities IDs, etc?
What would be the best ways to optimize such a database?


texts: MongoDB: Should I combine fields for performance in limited circumstances

texts:
I have a really simple MongoDB collection:

{
  "user": 1,
  "domain": "example.com",
  "apiAccess": true
}

There's an index on the "user" and "domain" fields. I always perform the same type of query on this collection and it only ever returns one result.

db.collection.find({"user" 1, "domain": "example.com"});

What I realised is because I always query on "user" and "domain" and because together they're unique I could just combine the field into one field. And I could make this the _id like so:

{
  "_id": "example.com_1",
  "apiAccess": true
}

This way I only query on one field and only need one index. I was thinking this would perform better due to these reasons however due to my limited understanding of MongoDB I'm unsure if this would be the case and I was wondering if someone could shed some light.


texts: Always On Synchronous Commit Mode With Large Delays

texts: For SQL Server Enterprise's Always On availability groups in Synchronous Commit mode, is there some amount of latency that can't be exceeded?  For example, can it work between very distant data centers, such as between the US and Europe?


texts: Get base table from materialized view in postgresql

texts:
I use materialized view in postgresql. when retrieving the list of materialized view (select * from pg_matviews), in the "definition" column, there is a query building the materialized view. how do i retrieve the list of base tables from "definition"?
example query materialized view :
CREATE MATERIALIZED VIEW rental_by_category
AS
SELECT c.name AS category,
    sum(p.amount) AS total_sales
   FROM (((((payment p
     JOIN rental r ON ((p.rental_id = r.rental_id)))
     JOIN inventory i ON ((r.inventory_id = i.inventory_id)))
     JOIN film f ON ((i.film_id = f.film_id)))
     JOIN film_category fc ON ((f.film_id = fc.film_id)))
     JOIN category c ON ((fc.category_id = c.category_id)))
  GROUP BY c.name
  ORDER BY sum(p.amount) DESC
WITH NO DATA;

i want to get list of materialized view like this:

rental_by_category
base table
payment
rental
inventory
film
film_category
category


texts: UNION ALL implicit conversion for VARCHAR(MAX) column

texts:
Is an implicit conversion in a UNION ALL when one of the data type is VARCHAR(MAX) something to worry about?

e.g.

DECLARE @value1 VARCHAR(1)= 'a';
DECLARE @value2 VARCHAR(MAX)= 'a';

SELECT @value1
UNION ALL
SELECT @value2;

If you look at the execution plan there will be a CONVERT_IMPLICIT warning:

  (Scalar Operator(CONVERT_IMPLICIT(varchar(max),[@value1],0))), (Scalar
  Operator([@value2]))

Is this going to behave as a normal conversion i.e. convert all the VARCHAR(1) columns to VARCHAR(MAX) and have an impact on performance? Is there a way to avoid it other than changing the table structure


texts:
I need to create a data model for routing data between points. For example, path between two cities which will include the two start and destination cities, way points (like smaller towns along the way), total distance between the two cities (distances between routing points do not need to be modelled though it would be good if that can be done too).

My current idea is like below.

Create one table containing the cities: city. Columns: city_id (primary key), city_name, etc.
Create a many-to-many table path with 4 columns: from_city (references city.city_id, to_city (references city.city_id), calculated column path = MD5(CONCAT(from_city, to_city)), distance to store the total distance for the route.
Create a table to store route points called town similar to the city table.
Create a man-to-many table route between path and town which will have three columns: town_id (references town.town_id), path_id (references path.path) and route_position which will be a value 1-n which will indicate the position of the route point in the actual path. For example, first route point will have number 1, second 2 and so on.

Questions:

Do you guys think this is a feasible model? Is there a better approach to this?
In the path table, is it better to use a MD5 hash or create a multiple-column index on from_city and to_city?

EDIT:

To give some context, I'm currently using a graph database to store this data and have a user-defined function that uses A* search to find the shortest path between points. Problem is as the graph gets denser, this computation becomes too slow. So, I think a lookup-based approach would be faster.


texts:
I wanted to make rotation inside my logs table to store last 100 rows only, and decided to use triggers, as in

create trigger rotate_log
after insert on logs
for each row begin
declare
  cnt int;
  set cnt = (select count(*) from logs);
  if cnt >100  then
    delete from logs limit 1;
  end if;
end

But it doesn't works because of deadlock caused by insert statement. So how to rewrite this with less work?

I am using mysql Ver 15.1 Distrib 10.1.35-MariaDB, for FreeBSD11.2


texts: Meaning of precision 1, scale 0 in error message?

texts:
What datatype does this error message refer to?

ERROR:  numeric field overflow
DETAIL:  A field with precision 1, scale 0 must round to an absolute value less than 10^1.

I'm getting the error listed above, it's coming from an insert trigger. Since none of my datatype are explicitly defined as NUMERIC(1,0) and since the maximum value is 10 to the power of 1 (i.e. 10) I'm guessing that this error is actually referring to a BOOLEAN field. However, I can't find confirmation of this.
UPDATE
Solved. My trigger is attempting to parse some strings into numbers. The error listed is returned when to_number() doesn't get a string in the expected format.
This returns 1.4 (correct) -
select to_number('1.4','9D9');

This returns the aforementioned error
select to_number('1d4','9D9');

Still not quite sure why it's complaining about NUMERIC(1,0) but at least I know why there's an error.


texts: Determine free space on "nonfixed" drive

texts:
I am writing a stored procedure that needs to send an email when a dvd rw drive is almost full.  I found
master.sys.xp_fixeddrives
but it only shows free disk space for fixed hard drives.  I have not been able to find something that will tell me the free space for "nonfixed" drives.  Is there a undocumented sp or function that I don't know about?


texts: Securely Send Database Mail via an Insert Trigger

texts:
I have spent about 30 hours so far trying to get sp_send_dbmail to work. I finally got SOMETHING to work, but I have serious doubts as to its security. I would like know a method which is better yet quick and easy to setup (I'm severely limited on time at this point), and the 'ideal' (best practice) solution.

Current SQL Server 2005 Configuration

- `After Insert` Trigger on table executes `sp_send_dbmail`
- Database Mail SMTP Account setup (with successful mail test)
- Database Mail Profile setup (with "Public Profile" ticked)
- "guest" account added to "DatabaseMailUserRole"

I found this post suggesting the "correct" solution and I read up on this article where I discovered that it's a pretty involved solution with too many things that can go wrong it seems, not to mention difficult to maintain.

My thoughts for an alternative solution are these.

1. Setup an Application Role in msdb (this would be my first time using one
    and not even sure if it IS a solution :P), and use it to ensure that email
    can ONLY be generated from the app and not by connecting directly to the server.
2. Abandon the trigger and instead run a job on the table to send email. Definitely
    NOT ideal as I would prefer the email to go out instantly, but at least this
    way I would be able to control the user context. Additionally this is similar
    to the current mechanism, except it would use a Stored Procedure instead of a
    DTS package.

The issues I've been battling these 30 hours and the reason for my inquiry are related to the security context under which the trigger was being executed. When I did my testing, everything worked great because I am a DBA, but when my users used the app responsible for generating the email, it would break because their credentials were not sufficiently high to for the trigger to execute the sp_send_dbmail proc, and therefore it broke the app. (Unfortunately my error handling as the SQL Server level is less than desirable at the moment).


texts: How to monitor the performance of a mysql database during development?

texts:
Is there a standard approach to monitor the performance of an application and its database, which is still in design?

What I'm doing so far: I develop queries and tests directly in mysql workbench and monitor the msec used per query. On top of that I'm using mocha tests in node.js to test the database api and also try here to measure the typical impact on the database.

The standard approach I know is: do your design and one day, as the amount of data has grown, there is a bad surprise, that the database needs to be optimized. I'm asking for good practices to monitor performance from the beginning.


texts: How to query if record exists for prior month in mysql

texts:
I have a table with custId and orderDate fields, and I need a query to display whether or not the custId in that record has another record in the table with an order from the prior month. For example:

custId   orderDate    hasOrderInPriorMonth
aaa      2017-11-01   false
bbb      2017-12-15   false
aaa      2018-01-29   false
bbb      2018-01-15   true


texts: Convert Oracle Query for Date to MySQL queries

texts:
Need to convert the following oracle queries to MySQL queries.

1)

select to_char(to_date(?,'DD-MM-YY'))
from dual;

2)

select to_char(next_day(to_date(?,'DD-MM-YY'),'FRIDAY'))
from dual;

3)

select to_char(to_date(?,'DD-MM-YY'),'DAY')
from dual;


texts: Teradata ODBC - version 14 and 15 side by side

texts:
Is there a way to install version 15.11 alongside version 14.10 of the ODBC Teradata drivers?

I've run the 15.11 install wizard but it seems to want to remove all previous versions automatically.

Ideally, when we upgrade a production server, we'd like to run both and have the option to run both and switch between them if the version 15 driver causes a fault.


texts: Postgres replication delay with inserts into large table

texts:
I have a large table (over 35 million rows, most rows contain TOAST'd columns) that I've isolated as the source of growing replication delays in my Postgres instance. When I'm doing inserts into this table (at a rate of 100s per second) I see the replication delay, both in time and bytes, grow. The only way I get the replication delay to come back down is to either stop doing inserts into this table or truncate this table and allow the inserts to proceed. Based on my testing this issue seems to pop up somewhere in the neighborhood of 30 million rows, and not before.

To give you an idea of volume, I'm normally shipping about 200 MB/s of xlogs to the replica, have already tested and verified I can do over 1 GB/s when this table is not an issue, so I know it's not a network problem.

Any ideas as to why this happens and what I can do to address it? Thanks to a previous round of trying to address this problem I've already turned on wal compression and use replication slots now.


texts: Remind customers of an event 7 days before the event based on a date

texts:
Scratching my head on something for sometime now and I can't seem to resolve it through MySQL. I have a table that has records of events. I want to remind customers of an event every month, 7 days before it's due date. I have 3 tables called customers, events and event_reminders.

To describe what these tables do:

Customers table - a list of customers
Events table - when the event was created and the day the reminder is set for
Event Reminders table - A list of date/time for when the reminder was sent out

My thought process is that based on the "reminder_day" field ( 1st, 2nd, 3rd, 30th) you create a custom date, passing in the field called "reminder_day". You then do an interval of -7 days and check if this exists in the event_reminders table for every month. My SQL statement so far is below:

SELECT c.customer_name, c.customer_email, REPLACE(e.reminder_day, RIGHT(e.reminder_day,2), '') as due_day
FROM customers c JOIN events e ON (c.customer_id = e.customer_id)
WHERE NOT EXISTS
(
 SELECT 1 FROM event_reminders r
 WHERE MONTH(r.reminder_date) != MONTH(CURRENT_DATE())
)

The query above is checking the event_reminders table, to check if the current month record exists for an event of a customer but I need it to do a check on the due_day alias with an interval of -7 days.

I hope some genius could help me and it is clearly defined on my end. Much appreciated!


texts:
I have setup log shipping with standby mode to ship every 15 minutes, but when the log is restored on the secondary server it kills any currently executing queries.

Is it possible to setup the transaction log restore to start only after any currently running queries finish and to not allow any new connections to the secondary while the log is restoring?


texts: View: Ignore a left join if it is not used?

texts:
I have a web service that references a view called gis_sidewalks_vw.

create table gis_sidewalks (
    id number(10,0),
    last_edited_date date
    );
insert into gis_sidewalks (id, last_edited_date) values (1, TO_DATE('2019/01/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
insert into gis_sidewalks (id, last_edited_date) values (2, TO_DATE('2019/02/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
insert into gis_sidewalks (id, last_edited_date) values (3, TO_DATE('2019/03/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
insert into gis_sidewalks (id, last_edited_date) values (4, TO_DATE('2019/04/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
commit;

create table maximo_assets (
    id number(10,0),
    lastsyncdate date
    );
insert into maximo_assets (id, lastsyncdate) values (1, TO_DATE('2019/04/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
insert into maximo_assets (id, lastsyncdate) values (2, TO_DATE('2019/03/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
insert into maximo_assets (id, lastsyncdate) values (3, TO_DATE('2019/02/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
insert into maximo_assets (id, lastsyncdate) values (4, TO_DATE('2019/01/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
commit;

create or replace view gis_sidewalks_vw as (
select
    s.id,
    s.last_edited_date as gis_last_edited_date,
    a.lastsyncdate as maximo_lastsyncdate,
    case
        when s.last_edited_date > a.lastsyncdate then 1
    end sync_needed
from
    gis_sidewalks s
left join
    maximo_assets a
    on s.id = a.id
);

select * from gis_sidewalks_vw;

         ID GIS_LAST_EDITED_DATE MAXIMO_LASTSYNCDATE SYNC_NEEDED
 ---------- -------------------- ------------------- -----------
          1 01-JAN-19            01-APR-19
          2 01-FEB-19            01-MAR-19
          3 01-MAR-19            01-FEB-19                     1
          4 01-APR-19            01-JAN-19                     1

The view has a left join and a calculated column:

case
    when s.last_edited_date > a.lastsyncdate then 1
end sync_needed

...

left join
    maximo_assets a

Scenario:

The view & the web service are multi-purpose.

Purpose #1:

Serve up only the rows where sync_needed = 1 to a cron task (synced weekly to a separate database).

Purpose #2:

Serve up all the rows in the view to a web map (map is in constant use).

Problem:

In purpose #1, it makes sense to join to the maximo_assets table and generate the calculated column.

However, in purpose #2, it does not make sense to join to the maximo_assets table and generate the calculated column.

Unsurprisingly, with purpose #2, I am experiencing performance issues in the web map due to the unnecessary join.

Question:

Is there a way to design the view so that it ignores the join to the maximo_assets table if the join is not being used?

For example:

select
    id,
    gis_last_edited_date
    --maximo_lastsyncdate
    --sync_needed
from
    gis_sidewalks_vw


texts: mysql - 35GB binlog files are cached in OS cache

texts:
I have a mysql database with 5.6.

Binary logs are enabled and it has 5 slaves.
GTID mode enabled.
Maximum binlog file size is 1GB.
sync_binlog = 0

When I check the free -gh

              total        used        free      shared  buff/cache   available
Mem:           117G         79G        1.4G         25M         37G         37G
Swap:            0B          0B          0B

37GB in Cache.

I wanted to check what are all related to Mysql. So I used fincore and I could see from mysql-bin.000557 to mysql-bin.000590 are in the OS Cache.

Total Cache from binlog is 35GB.

Is this expected behavior?

There is no replication lag in the slave. So why mysql needs to cache the old binlogs in Cache?


texts: Adding two columns results in concatenation instead of addition

texts:
I need help using addition.  When I do it populates using the concatenate function.

I used the following in my access database Total: [ANTI TERROR]+[Records] and instead of returning 1 + 1 = 2 it returns 11 - i.e. it's using the concatenate function.


texts: MySQL replication stopped working with error code Error_code: MY-003159

texts:
I am trying to setup Master-Master MySQL replication in MySQL 8.

It was working fine until recently. I am able to connect as replication user from slave host to master; however, when I start replication I get following error code in log:

  Error_code: MY-003159

Here is the mysql error when I show slave status\G:

               Slave_IO_State: Connecting to master
                  Master_Host: <server_ip>
                  Master_User: ruser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000030
          Read_Master_Log_Pos: 155
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000030
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 155
              Relay_Log_Space: 155
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 3159
                Last_IO_Error: error connecting to master 'ruser@<server_ip>:3306' - retry-time: 60  retries: 13
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID:
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp: 190126 20:50:58
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0

It was successfully connecting before and replication was happening. Suddenly when I checked status a week later, I saw the following error:

Last_IO_Error: error connecting to master 'ruser@<server_ip>:3306' - retry-time: 60

I tried doing setup with a new user. I get same error now. This new user is never able to connect. What am I missing?


texts: Counting Number of Database Connections on a Page

texts: I have a LAMP stack(mysql and php) wordpress site and I want to know the number of database connections opened when a page loads. I can find other numbers but I can't figure out the number for a specific page.


texts:
I have the following object model:

class A {
    List<Thing> orderedListOfThings;
}

interface Thing {
}
class X implements Thing {
   int attr;
}
class Y implements Thing {
   String value;
}
class Z implements Thing {
   B b;
}
class B {
   ...
}

The order of the list orderedListOfThings must be preserved. This list can contains X, Y, Z instances. There isn't other Thing implementations.

how this model could be design in a relational database ?
Is there any No-SQL database (key-value, columnar, graph, triple store) that fits better for this kind of model ?


texts: Do databases make a delete and an insert when having to update rows?

texts:
So today a professor told us that when the database has to make an update, internally (at low level) it makes a delete and then an insert with the updated fields. He then said that this is something made across all databases and then I started a discussion telling that I thought that it had no sense but I didn't had enough resources to support my position. He seems to know a lot but I can't understand why would dbs do that.

I mean, I know that if you update a field and you need more space for that row, then it may delete the row physically and put it at the end with the new data. But if for example you reduce the space used, why would it delete and re insert it at the end?

Is this even true? What are the benefits?


texts: How to recover auto committed transaction in Oracle with normal privilage

texts:
I have updated the table & need to rollback that changes.

I have tried Rollback work command but data still not rollback.
I have also tried Flashback command but it is not working because "row movement is not enabled"

Could you please help me out. I don't have DBA access as well, still I need to recover the data.


texts: Can bdr only replicate one database per server?

texts:
I have installed the new BDR-Solution with PostgreSQL 9.4 and it works like a charm.

My Problem: I want to add more databases to the replication but every time if I added the other database and restarted the servers one server is crashing. pg_hba.conf and postgresql.conf are correct.

So I read on this site that

  "You can replicate just one database from a server"

But on this site it sounds like an advantage:

  "You must explicitly configure each database you want to replicate."
  and "BDR replicates per-database, not per-cluster. So you don't have
  to split your databases up into different PostgreSQL instances just to
  control how they replicate."

this sounds to me like I could vsync more databases if I want...


texts: Why do SQL Server row estimates change when I add a join hint?

texts:
I have a query that joins a few tables and performs pretty badly - row estimates are way (a 1000 times) off and Nested Loops join is chosen, resulting in multiple table scans. The shape of the query is fairly straightforward, looking something like this:

SELECT t1.id
FROM t1
INNER JOIN t2 ON t1.id = t2.t1_id
LEFT OUTER JOIN t3 ON t2.id = t3.t2_id
LEFT OUTER JOIN t4 ON t3.t4_id = t4.id
WHERE t4.id = some_GUID

Playing around with the query, I noticed that when I hint it to use a Merge join for one of the joins, it runs many times faster. This I can understand - Merge join is a better option for the data that is joined, but SQL Server just doesn't estimate it right choosing the Nested Loops.

What I don't fully understand is why does this join hint changes all the estimates for all the plan operators? From reading different articles and books, I assumed that the cardinality estimations are performed before the plan is built, so using a hint would not have changed the estimations, but rather explicitly tell SQL Server to use a particular physical join implementation.

What I see, however, is that Merge hint causes all estimations to become pretty much perfect. Why does this happen and are there any common techniques to make query optimizer make a better estimate without a hint - considering that statistics obviously allow for this?

UPD:
anonymized execution plans can be found here:
https://www.dropbox.com/s/hchfuru35qqj89s/merge_join.sqlplan?dl=0 https://www.dropbox.com/s/38sjtv0t7vjjfdp/no_hints_join.sqlplan?dl=0

I checked the stats used by both queries using TF 3604, 9292 and 9204, and those are identical. However indexes which are scanned/seeked differ between the queries.

Besides that, I tried running the query with OPTION (FORCE ORDER) - it runs even faster than using merge join, choosing HASH MATCH for every join.


texts: AWS RDS: How do I diagnose this failure?

texts:
I have a simple RDS setup: one database, no replication. It has been running for around 2 years without any problems. CPU usage is generally less than 5%, sometimes boosting to around 10%.

Today, without any apparent reason or warning, my application lost connection with the DB. Looking at the log files, I could see the message "Recovery of the DB instance started..." and a few minutes later "Recovery of the DB instance complete". At that point my application was able to reconnect and work fine.

How do I go about diagnosing this further? The log file has about 30 lines in it, starting with "Giving 2 client threads a chance to die gracefully", then "Shutting down slave threads". After that, the service goes through a restart procedure.

Is it normal operation for RDS to 'recover the instance' after a failure like this? Presumably I could lose a few minutes of data?

Update:

The logs are no longer available, so I cannot post them.  Also, I notice that the freeable memory jumps up sharply from the time of the incident, which would seem to be a good thing.


texts: Trigger to prevent update if existing value is higher than inserted value

texts:
I need a trigger that will prevent from updating the price if the existing value is higher than the inserted value. If such operation transaction appears, the trigger should prevent value update but let transaction end (i.e. not roll it back). The one below doesn't work -- it doesn't let the update happen even if inserted value is higher than existing.
CREATE TRIGGER [dbo].[Prices_InsteadUpdate_Trigger]
ON [dbo].[Prices]
INSTEAD OF UPDATE
AS

SET NOCOUNT ON

IF UPDATE(PRC_Value) AND EXISTS (SELECT *
                                 FROM deleted d
                                 JOIN inserted i
                                     ON d.PRC_PrcId = i.PRC_PrcId
                                  WHERE d.PRC_Value > i.PRC_Value)
SELECT NULL

SET NOCOUNT OFF

Regards,
Przemek


texts: sql server: updating fields on huge table in small chunks: how to get progress/status?

texts:
We have a very large (100million row) table, and we need to update a couple of fields on it.

For log shipping, etc, we also, obviously, want to keep it to bite-size transactions.

Will the below do the trick?
And how can we get it to print some output, so we can see progress? (we tried adding a PRINT statement in there, but nothing was output during while loop)

The code is:

DECLARE @CHUNK_SIZE int
SET @CHUNK_SIZE = 10000

UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
where deleted is null or deletedDate is null

WHILE @@ROWCOUNT > 0
BEGIN
    UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
    where deleted is null or deletedDate is null
END


texts: Slow updates (26seconds!) with mysql, likely fix to use innodb_flush_log_at_trx_commit = 0?

texts:
I have periodic slow queries in a rails site. Happens mostly on updates / inserts.

This is reporting that it is taking 26 seconds for an update to occur. I see this in the logs, so, I'm not 100% sure that it is what it is taking on the MYSQL end, but, I assume it is.

From reading around and other stack overflows it seems like setting innodb_flush_log_at_trx_commit to 0 or 2 will make the writes faster at the expense of being less ACID compliant. Now, the site I work on does not process $$$, and even if it did, losing 1 seconds worth of transactions would not be an issue at all. These 30 second timeouts are far more of an issue.

My question really is, is it likely that this is really a innodb_flush_log_at_trx_commit issue? Also, just being paranoid here, can I still trust that transactions will be respected in the case that I change this value. I do still want that when I run something in a transaction that I can be guaranteed that it will complete or not complete .

Any help / pointers most appreciated!

Update

Id is the PRIMARY KEY in documents?
last_node is not in any index
The table has ~ 1.5 million rows
I do not think that this transaction is being run as part of any other.
The question about "what other operations are being run", that's the thing, I'm not sure how to find out the answer to that question. As a part of this particular transaction, nothing. This is a ruby on rails app, and what is happening here is that a single update to a row is being made (in a transaction). e.g. document.last_user_node = someID, document.save . It's not processing anything else.
Yes, InnoDB table.

So, my thought it that something else is locking up the database. It's possible that several updates to the same row are being made and maybe that is causing contention. Re MYSQL locking strategy, if you're updating a single row (in a transaction) you don't lock the entire table right?

Query cache settings

have_query_cache  YES
long_query_time   5.000000
query_alloc_block_size    8192
query_cache_limit 2097152
query_cache_min_res_unit  4096
query_cache_size  67108864
query_cache_type  ON
query_cache_wlock_invalidate  OFF
query_prealloc_size   8192


texts: Database engine that share a row among multiple tables

texts:
I'm wondering whether exists any database engine that can share cells or rows amongst many tables. For example:

TABLE Customer (
   CID: int
   Fullname: nvarchar(50)
   Age: int
)

TABLE Group (
   GID: int
   Name: nvarchar(50)
)

TABLE Customer_Group (
   CusName: nvarchar(50)
   GrpName: nvarchar(50)
)

In which, Customer_Group is designated for a certain query, like a column family in Cassandra. Now I want to insert to Customer_Group table a row whose CusName cell references to Fullname cell of a row in table Customer. Similar to GrpName.

As a result, when I update Fullname in Customer table, I don't have to update Customer_Group table, because it references to same cell address. And Customer_Group table itself doesn't have to store actual data for CusName but in fact is a pointer.

Does anyone know what database engine supports this idea? And is it a good idea though?


texts: Is it possible to import DBs information_schema, performance_schema and mysql from mysql-community-server-5.6.x to mariadb-server-5.5.x?

texts:
Is it possible to import the following DBs from an existing mysql-community-server-5.6.x configuration to a fresh mariadb-server-5.5.x :

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

I guess no here because the table structure of mysql.users should be different between mysqld and mariadb.


texts: MySQL: Is it possible to run a search on a specific database for a specific string?

texts:
I need to find a specific email address in a database.

I don't know in which table the email is set so I'd like to run a search on the whole database.
Is that possible?

Thanks