texts: Worth Splitting MySQL Server Into 2 MySQL Servers To Increase Performance

texts:
Context
I am using an AWS RDS server to host my MySQL DB.

I work for a footfall company, we have our system installed all over the UK and every time a visitor is seen we write to the DB. We also provide a portal to our customers allowing them to view all the data as readable reports. Customers only use the portal once a week on average so there aren't anywhere near as many reads on the DB as writes.

RDS Stats

The graph above shows the read and write IOPS as you can see the write IOPS is loads higher then read IOPS.

Question
On the portal we don't show any of todays stats and at midnight every night we run a load of cron jobs which summarises all the data.

I am thinking of separating my RDS Server into 2. One would contain all the summarised data which the portal would read from to show the stats. The other would contain all of today's data and it would constantly be written to. At midnight the cronjobs would them summarise the data from the DB containing today's data and store the summarised data in the portal DB.

The idea behind this is to make the portal as fast as possible by removing all the writes.

I am no MySQL guru so this may have no effect, the RDS server can handle all the writes and reads to the DB with the current specs. If the current RDS server can handle the requests I am presuming this won't have an effect?

We are currently just trying to optimise our performance so any advice is really helpful.


texts: How do i increase the total MyISAM indexes?

texts:
I have just made a check for my server with mysqltuner.

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
[--] Data in InnoDB tables: 2G (Tables: 54)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 54

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 11h 57m 48s (46K q [1.089 qps], 21K conn, TX: 12M, RX: 4M)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 7.2G global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 7.5G (63% of installed RAM)
[OK] Slow queries: 0% (8/46K)
[OK] Highest usage of available connections: 7% (7/100)
***[OK] Key buffer size / total MyISAM indexes: 2.0G/96.0K***
[OK] Query cache efficiency: 48.4% (22K cached / 46K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 1% (4 temp sorts / 247 sorts)
[OK] Temporary tables created on disk: 17% (212 on disk / 1K total)
[OK] Thread cache hit rate: 99% (7 created / 21K connections)
[OK] Table cache hit rate: 35% (102 open / 289 opened)
[OK] Open file limit used: 4% (47/1K)
[OK] Table locks acquired immediately: 100% (2K immediate / 2K locks)
[OK] InnoDB data size / buffer pool: 2.7G/5.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries

Please advise me a way to increase total MyISAM indexes (96K) to an effect number.
Thanks.


texts: Is restoring a mysql database backup faster at removing white space than running OPTIMIZE?

texts:
I have a myisam database running on MariaDB 10.x.  I'm pretty sure it has a lot of whitespace that I'd like to trim down.  I was planning to run OPTIMIZE <TABLE NAME>; but a thought occurred to me: would it be faster/easier to just collect a backup and restore it?

For example...

1) Run: mysqldump -u root --hex-blob --add-drop-database --databases FooDatabase > /tno/FooBackup.sql

2) Then run: mysql -u root -vvv < /tmp/FooBackup.sql

The white space wouldn't be in the backup file so presumably it would be compact upon restoring... and that way, MariaDB wouldn't need to go row by row checking for stuff.

Is that a bad idea?  Admittedly, the backup/restore wouldn't check for errors or do the other things that optimize does but if I'm ONLY interested in eliminating white space, wouldn't it be faster?


texts: Moving data from Oracle to SQL Server : Table size optimization

texts:
I am loading data using SSIS from Oracle to an SQL Server database. One table I moved had a size of 4 MB under Oracle but when loaded to SQL Server, the size grows to 7.96 MB!

I analyzed the size (in KB) depending on the rows inserted and got the following results:

Rows    SQL Server  Oracle
10      16          64
50      48          64
100     88          64
150     128         128
200     168         128
250     208         128
300     248         128
350     288         192
400     328         192
450     368         192
500     408         192
550     448         256
600     488         256
650     528         256
700     568         256
750     648         320
800     688         320

I am suspecting that the datatypes used in SQL Server should be readapted. Here is a summary of the structure of the tables used in Oracle and SQL Server:

Oracle_DataType     SQL_Server_Datatype     #OfColumns
DATE                datetime                2
INTEGER             int                     1
NUMBER(1,0)         numeric(1, 0)           3
NUMBER(15, 5)       numeric(15, 5)          52
NUMBER(2, 0)        numeric(2, 0)           1
NUMBER(3, 0)        numeric(3, 0)           5
NUMBER(3, 1)        numeric(3, 1)           1
NUMBER(5, 0)        numeric(5, 0)           1
NUMBER(5, 2)        numeric(5, 2)           4
NUMBER(6, 0)        numeric(6, 0)           1
NUMBER(8, 3)        numeric(8, 3)           2
NUMBER(8, 4)        numeric(8, 4)           1
NUMBER(9, 0)        numeric(9, 0)           7
NUMBER(9, 3)        numeric(9, 3)           6
VARCHAR2(1)         nvarchar(1)             1
VARCHAR2(12)        nvarchar(12)            1
VARCHAR2(13)        nvarchar(13)            1
VARCHAR2(14)        nvarchar(14)            1
VARCHAR2(16)        nvarchar(16)            3
VARCHAR2(20)        nvarchar(20)            1
VARCHAR2(24)        nvarchar(24)            1
VARCHAR2(80)        nvarchar(80)            1

Do you have any idea if it will be possible to reduce the size in SQL Server so it could, at least, matches the size in Oracle?

Thanks,


texts: Remove duplicate records from table, leaving one copy

texts:
I have inherited a MySQL table from one of my colleagues, and am in need of a little advice.

The table has the following structure:

| PHONE_NO   | ACC_NO | GET_MAIL | DATETIME_CRE         | INT_ACC | MAIL |
| 0113313313 | 000001 |   YES    | 2003-01-01 19:00:00  | 9000001 | N/A  |

There is no primary key, as the table was not created with one. I cannot recreate the table as:

It would take far too long to complete (over 2 million records and counting)
This is a mission critical table, meaning taking it down would result in my client losing money for the duration.

I need to be able to remove duplicated rows from the table with a query, without inserting a primary key. "Duplicated" in this sense refers to all columns within the row being identical to another row.

EDIT
This is the result of show create table query, as requested by  ypercube;

CREATE TABLE `callerQue` (
  `PHONE_NO` varchar(30) DEFAULT NULL,
  `ACC_NUMBER` varchar(40) DEFAULT NULL,
  `GET_MAIL` varchar(6) DEFAULT NULL,
  `DATETIME_CRE` datetime DEFAULT NULL,
  `INT_ACC` varchar(40) DEFAULT NULL,
  `MAIL` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1'


texts: PostgreSQL 9.5 won't start after Windows 10 fall update

texts:
I've installed Windows 10 Fall update (1709) and now my PostgreSQL 9.5 server won't start. It worked yesterday before the update and I haven't made any changes to the configuration.

I've checked event viewer and found following error messages:

2017-10-19 11:32:32 CEST LOG:  invalid value for parameter "lc_monetary": "Czech_Czech Republic.1250"
2017-10-19 11:32:32 CEST LOG:  invalid value for parameter "lc_numeric": "Czech_Czech Republic.1250"
2017-10-19 11:32:32 CEST LOG:  invalid value for parameter "lc_time": "Czech_Czech Republic.1250"
2017-10-19 11:32:32 CEST FATAL:  configuration file "C:/Program Files/PostgreSQL/9.5/data/postgresql.conf" contains errors

It seems like Microsoft changed locale name with Fall update, I couldn't find any list of available locale names so I decided to install Postgres 10 and it confirmed my suspicion, the postgresql.conf in Postgres 10 now reads:

# These settings are initialized by initdb, but they can be changed.
lc_messages = 'Czech_Czechia.1250'          # locale for system error message
                # strings
lc_monetary = 'Czech_Czechia.1250'          # locale for monetary formatting
lc_numeric = 'Czech_Czechia.1250'           # locale for number formatting
lc_time = 'Czech_Czechia.1250'              # locale for time formatting

I changed the config values for PostgreSQL 9.5 server to 'Czech_Czechia.1250' and it started ok, but the problem is that now I can't connect to any database, pgAdmin III gives me following error:

Is there a way to get the data back? I can't create dump or run pg_upgrade since the databases now have invalid locale and I can't connect to them. Maybe there is a way to change the locale of the database manually? In theory, it shouldn't cause any problems since its only different name for the same encoding.


texts: How can I add the local administrators group to SQL in Windows Server 2012?

texts:
I'm running Microsoft SQL Server 2014 on Windows Server 2012. I would like to grant all local administrators (the box is not on a domain) sysadmin rights to SQL Server, but am having difficulty.

I can create a login and assign BUILTIN\Administrators, but it does not actually allow administrator users to connect. It only works if I assign specific users as ServerName\UserName.


texts: a domain \user part of 2 different Domain security groups can piggy back both AD groups to get complete access?

texts:
Scenario:

domain\user has permissions to connect (authenticate) to a SQL server using domain\security_group_1 which is not mapped to any DBs.
domain\user is also a part of domain\security_group_2 which is an orphaned database principal in all DBs on same SQL server ( meaning domain\security_group_2  corresponding login is not available to authenticate on the SQL server ) with DML permissions on the DB level.

In this case it has been observed that domain\user can connect to the SQL server and is able to perform DML operations even though its individual object  principal (server/DB principal) does not have access to perform such operations by itself.

Does this sound like a security flaw within SQL server?


texts: How to identify the column used to partition a table from the Postgres system catalogs

texts:
Given a table created as so...

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

How do I identify which column it's been partitioned on? - in this case 'logdate' solely by querying the postgres catalog.

I've looked in the obvious places in the catalog (pg_class, pg_index) but nothing springs out.

(Using version 10.5)


texts: Ordering of table is based on Clustered index or non clustered primary key?

texts:
I have created a table with primary key on name column, the ordering of the table is based on name column

create table TestPrimaryKey (Name varchar(20) primary key,id int identity)

insert into TestPrimaryKey (SomeText) values ('john')
insert into TestPrimaryKey (SomeText) values ('ryan')
insert into TestPrimaryKey (SomeText) values ('jennifer')
insert into TestPrimaryKey (SomeText) values ('martha')
insert into TestPrimaryKey (SomeText) values ('luke')
insert into TestPrimaryKey (SomeText) values ('alex')
insert into TestPrimaryKey (SomeText) values ('wayne')

Now I'm changing the clustered index on primary key to nonclustered index, still the ordering is based on the name column

    begin tran
    alter table dbo.TestPrimaryKey drop constraint PK__TestPrim__BC2905030401D947
    alter table dbo.TestPrimaryKey add constraint PK__TestPrim__BC2905030401D947 primary key nonclustered (SomeText)
    commit tran

Now I'm creating a unique clustered index on the other (id) column, still the ordering is the same

    create unique clustered index CI__TestPrim__BC29050354394FAA on TestPrimaryKey(id)

Now I'm dropping the clustered index from the table

    drop index CI__TestPrim__BC29050354394FAA on TestPrimaryKey

But now the ordering changes from the "name" column to the "id" column. I need to know why that happens and also if the table has a non clustered primary key and a clustered index column will the ordering be based on primary key column, clustered index column or both.


texts: Duplicate key violates unique constraint

texts:
When I use this query everything is fine:

UPDATE users
   SET f_name='Mike',
       l_name='MyLastName',
       username='blabla',
       "password"='asdfsdaf',
       "class"=12,
       lang='en'
 WHERE id=50;

I created a view which combines two tables:

CREATE OR REPLACE VIEW students_data AS
SELECT students.id,
       users.f_name,
       users.l_name,
       users.username,
       users."password",
       users."class",
       users.permission,
       users.sessionid,
       users.lastlogin,
       users.lang
  FROM students
  LEFT JOIN users ON students.id = users.id;

And I also created this rule:

CREATE OR REPLACE RULE students_data_update AS
    ON UPDATE TO students_data DO INSTEAD
       UPDATE users
          SET f_name = new.f_name,
              l_name = new.l_name,
              username = new.username,
              "password" = new."password",
              "class" = new."class",
              permission = new.permission,
              sessionid = new.sessionid,
              lastlogin = new.lastlogin,
              lang = new.lang;

When I execute this query I get an error:

UPDATE students_data
   SET f_name='Mikaa',
       l_name='MikeL',
       username='blabla',
       "password"='asdfsdaf',
       "class"=12,
       lang='en'
 WHERE id=50;

  ERROR: duplicate key violates unique constraint "username" SQL
  status:23505

I have no idea why I get this error, username is a unique column but I shouldn't give any problems when I update this column.

-- Table: users

-- DROP TABLE users;

CREATE TABLE users
(
  id serial NOT NULL,
  f_name character varying,
  l_name character varying,
  username character varying NOT NULL,
  "password" character varying NOT NULL,
  "class" integer NOT NULL,
  permission integer NOT NULL,
  sessionid character varying,
  lastlogin integer,
  lang character varying(5),
  CONSTRAINT users_pkey PRIMARY KEY (id),
  CONSTRAINT "class" FOREIGN KEY ("class")
      REFERENCES "class" (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT username UNIQUE (username)
)
WITHOUT OIDS;


texts:
Since PostgreSQL 9.0, each large object has its own access rights that are granted with:

GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
  ON LARGE OBJECT loid [, ...]
  TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

The SELECT permission allows for opening the object in read-only mode, and for UPDATE, the doc says:

 For large objects, this privilege allows writing or truncating the object.

But if a large object belongs to user1, I can't find how to grant the permission to user2 to delete (lo_unlink) this object.
If user1 says GRANT UPDATE on LARGE OBJECT xyz TO user2; and user2 runs SELECT lo_unlink(xyz);, it's denied with:

 ERROR:  must be owner of large object xyz

Is it really the case that only the owner a large object is ever able to delete it, or I'm missing something?

My use case is a db that stores mailboxes that are shared between different db users. All the tables with mail contents and large objects with mail attachments are owned by a "master" user, and other users may or may not delete messages depending on their database role.

Deleting a message means deleting everything related to it, including attachments stored in large objects. So it would make sense that a non-owner should be able to delete large objects, just like he can delete from other tables if he has been granted the DELETE privilege on these tables.


texts: Is it recommended to run a Oracle schema backup when there are active sessions?

texts:
I need to backup two schemas in a Production database (Oracle11gR2) that are being accessed by an application and some other systems. These schemas are quite small and exp will be used for the export.

Are there any best practices or recommendations for backups using exp? I cannot afford to have the users face any interruption during these backups. For example, has anyone experienced the backup utility hang?


texts: Installing Oracle 32 or 64 bit on a Windows Server 2019 to Import data into SQL server and use with Power BI

texts:
I am an Oracle noob and really struggling with this concept of 32 and 64 bit business with Oracle.

All I really want to do, is import data on a daily basis from an Oracle 12c DW into a local 2019 SQL Server and then connect Power BI on top of it.

The reason being that we are looking to move out of Oracle and into SQL Server and I need to PoC.

So far I have looked high and low and can't seem to figure out WHAT to INSTALL to access WHAT in oracle.

All I really need, if I have understood correctly is to install a Oracle CLIENT to be able to then use SSIS to build my ETL routines. BUT when I install 32bit client, it says need 64 bit client and when I install 64bit client it says it wants an ODAC. I have installed that too and still no success.

I am also struggling to understand what folder level goes into the PATH in the system environment variables.
It is so complicated in comparison to SQL Server, so I could really use your help in the following questions.

Given that SSMS is 32bit - Can I just install 32bit oracle client and ODAC - if so, which software do I download from Oracle and install - Can't tell the diff between xcopy and other versions (and can you tell me how to update the path)
Do I need to install x64 bit at all, given that I am on a 64bit workstation? If so, again, which software do I download.

My end goal is to be able to:

Connect to ORACLE using SQL SERVER SSIS and import data on a daily basis via ETL routines
as a temporary back up also connect to ORACLE using Power BI.


texts: Are there disadvantages to using Full-Text Search in SQL Server? Is there much of an increased CPU or memory load on the server?


texts: Table with persisted computed CLR originated data marked with has_unchecked_assembly_data = 1, but DBCC CHECKTABLE does not unset this same flag

texts:
We have an OrderLines table with columns:

Quantity int not null
QtyCancelled int not null
QtyBackorder int not null
QtyPicking int not null
QtyPacking int not null
QtyShiped int not null

with computed and persisted column FulfillmentStatusId as:

dbo.clr_GetFulfillmentStatusByLineQuantities(Quantity, QtyCancelled, QtyBackorder, QtyPicking, QtyPacking, QtyShiped)

"clr_GetFulfillmentStatusByLineQuantities", of course, being mapped through a scalar function to an ASSEMBLY in the database with the associated method (C#) marked with:

[SqlFunction(DataAccess = None, IsDeterministic = true, IsPrecise = true, SystemDataAccess = None)]

With reference to this question, I've run DBCC CHECKTABLE against this table multiple times and it still remains as has_unchecked_assembly_data=1. This was true when the column was first added to the table, but I believe the DBCC CHECKTABLE command succeeded prior to the migration to SQL 2016.

While the table has been in use for a long time with has_unchecked_assembly_data=1, there seems to be no impact on the table's functionality. I am concerned about ALTER ASSEMBLY throwing an exception when not using WITH UNCHECKED DATA, then throwing another exception when I did use that option, but appearing to update the assembly anyway "according to MVID".

Sorry if I ramble, I don't ask too many questions and I mostly lurk.

Thank in advance, however!

Update (20180917T17:34-05:00):
Thanks to Sean for suggesting an initial direction. The result of DBCC CHECKTABLE ('OrderLines') WITH EXTENDED_LOGICAL_CHECKS was two lines similar to:

Msg 2537, Level 16, State 106, Line 1
Table error: object ID 1486732449, index ID 1, partition ID 72057594403946496, alloc unit ID 72057594422755328 (type In-row data), page (1:1430554), row 5. The record check (valid computed column) failed. The values are 38 and 0.

So I executed a REBUILD on the primary key of the table (which is index ID 1 indicated above), which succeeded. However, rerunning DBCC CHECKTABLE('OrderLines') WITH EXTENDED_LOGICAL_CHECKS again returned similar errors, only this time with different partition IDs and allocation unit IDs (as one might expect).

The extreme solution might be to drop, recreate, and repopulate the table with identity insert, "nuke the site from orbit" so to speak, but for obvious reasons, that's extreme.

What is the correct solution to "there appears to be inconsistent computed column data in the table's clustered index"?


texts:
I have a question regarding TDE on SQL Server 2017 for example.

I created the master key
Created the certificate
Created the database encryption key ( now the database is encrypted)
Backed up the certificate
Alter database
set encryption ON.

Thats great, I could backup up the database and restore it on another server.
Let's say i don't want TDE anymore.
Why when i set ENCRYPTION OFF it still tells me, when i try to restore it on another server (After a new backup of course, with encryption off, only with the "database encryption key", that the server coudnt find the certificate thumbprint?
I can remove certificate and master key and  it fix the "Problem".
I would like to know whats the difference between:
USE DATABASE
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM =aes_256
ENCRYPTION BY SERVER CERTIFICATE MyCert

and
ALTER DATABASE DATABASE
SET ENCRYPTION ON

TDE only encrypt the DATA FILE right? and not data, so someone can't steal my file and restore it on another server.
Thanks.


texts: Mongo express show more than 10 rows per collection

texts:
I'm a massive noob so please excuse me on this question if it's too dumb

I use mongo express to visualize my database and do some direct changes. The thing with mongo express is that it shows only 10 rows per collection. And the pagination systems isn't really helpful if you thousands of rows.

My question is simple: does anyone know of any way to see more than 10 rows per collection? I'm looking for an effect similar to Datatables without having. Is there a plugin or anything you can help me with?


texts: Why is SAN Replication is Causing Random Database Corruption?

texts:
Having a strange issue with a disaster recovery technology we're trying to implement.  The environment in both datacenters is the same having VMWare and Dell Equallogic SANs, which are the same versions.

When we replicate from one datacenter to another, random databases are getting corrupted somehow and end up in SUSPECT mode.  Each time we try this method, different databases will get corrupted.  Is this a behavior in SQL that is causing this?  Is this the software used in the SAN to replicate causing these errors?

I've been able to change the state of the database to EMERGENCY mode and perform DBCC CHECKDB, but it is a different problem and database each time.  Some of the errors I've found are index issues and data mismatch problems.  I'm still in the process of checking other databases to see if I can find a pattern.  If find other things, I'll be sure to post if it'll help.

I've heard of people implementing this procedure successfully and it is the last task in the project to figure out before we can close the project charter.

I was really hoping that we could just use the built-in features of SQL server like mirroring or AO-AGs.

The versions of SQL are 2008 R2 and 2012.  We're in the process of installing some brand new SQL 2014 servers.  Also, they're all Standard, not Enterprise.

Any input or things I could try would be of great help, thanks in advance!!

Edit#1 8/6/15 12:50 PM CST -
The following are some of the error messages I've found in the Windows Event Viewer, which is more or less what DBCC CHECKDB produced.

  EventID 605 - Attempt to fetch logical page (1:22620) in database 26 failed.  It belongs to allocation unit 72057594239385600 not to 72057594249412608
  EventID 824 - SQL Server detected a logical consistency-based I/O error: incorrect paged (expected 1:1230; actual 0:0).  It occurred during a read of a page (1:1230) in database ID 58 at offset 0x0000000099c000 in file 'D:\Mydatabase.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail.  This is a severe error condition that threatened database integrity and must be correct immediately.  Complete a full database consistency check (DBCC CHECKDB).
  EventID 7886 - A read operation on a large object failed while sending data to the client.  A common cause for this is if the application is running in READ UNCOMMITTED isolation level.  This connection will be terminated.
  EventID 608 - No catalog entry found for partition ID 72057594383564800 in database 23.  The metadata is inconsistent.  Run DBCC CHECKDB to check for a metadata corruption.

Edit#2 8/6/15 2:24 PM CST - Received info that restoring .bak files of the databases in SUSPECT mode fixes the matter.


texts: MySQL Index for searching for NULL values in text fields

texts:
I need to do the simple query SELECT id FROM table WHERE blob_field IS NULL.

blob_field is a longblob. If I create an index on blob_field with prefix length 1, will this be sufficient to use for this query?


texts: Anemometer - query on analysing the slow logs

texts: I'm new to SQL anemometer. I need to know whether this tool would be able to query to the source database and analyze the slow query logs on real time or not?


texts: Postgres query not using indexes with conditions from multiple tables

texts:
I'm having trouble with a query that I expect to use my indexes, but it is doing a sequential scan instead. Here's my query with explain:

explain SELECT invoice_uuid AS uuid FROM invoice_tcgplayer_line_items
JOIN invoice_tcgplayer_details USING(invoice_uuid)
JOIN card_inventories ON card_inventories.uuid = invoice_tcgplayer_line_items.inventory_uuid
JOIN cards ON cards.uuid = card_inventories.card_uuid
WHERE order_number = 'bcfong28' OR cards.name ~* 'bcfong28' ;

QUERY PLAN

------------------------------------------------------------------------------------------------------------------
Hash Join  (cost=2087.79..2383.92 rows=3 width=16)
   Hash Cond: (invoice_tcgplayer_line_items.invoice_uuid = invoice_tcgplayer_details.invoice_uuid)
   Join Filter: ((invoice_tcgplayer_details.order_number = 'bcfong28'::text) OR (cards.name ~* 'bcfong28'::text))
   ->  Nested Loop  (cost=2064.62..2349.22 rows=2549 width=31)
         ->  Hash Join  (cost=2064.57..2103.20 rows=2549 width=32)
               Hash Cond: (invoice_tcgplayer_line_items.inventory_uuid = card_inventories.uuid)
               ->  Seq Scan on invoice_tcgplayer_line_items  (cost=0.00..29.65 rows=2549 width=32)
               ->  Hash  (cost=1734.18..1734.18 rows=94395 width=32)
                     ->  Seq Scan on card_inventories  (cost=0.00..1734.18 rows=94395 width=32)
         ->  Index Scan using cards_pkey on cards  (cost=0.06..0.10 rows=1 width=31)
               Index Cond: (uuid = card_inventories.card_uuid)
   ->  Hash  (cost=19.31..19.31 rows=1103 width=35)
         ->  Seq Scan on invoice_tcgplayer_details  (cost=0.00..19.31 rows=1103 width=35)
(13 rows)

It's doing a sequential scan on card_inventories which has a little under 100k rows in it. If i remove the cards.name ~* part of the WHERE clause (and do a SELECT on cards.name instead), it DOES use the primary key index of card_inventories.uuid. Why is this?

Here's the table description for card_inventories:

\d card_inventories
                       Table "public.card_inventories"
    Column     |            Type             | Collation | Nullable | Default
---------------+-----------------------------+-----------+----------+---------
 uuid          | uuid                        |           | not null |
 card_uuid     | uuid                        |           | not null |
 condition     | text                        |           | not null |
 language      | text                        |           | not null |
 quantity      | integer                     |           | not null |
 created_at    | timestamp without time zone |           | not null |
 updated_at    | timestamp without time zone |           | not null |
 sort_order    | text                        |           |          |
 offline       | boolean                     |           | not null |
 buylist_price | integer                     |           |          |
 overstock     | boolean                     |           | not null | false
 playset_price | integer                     |           | not null | 0
Indexes:
    "card_inventories_pkey" PRIMARY KEY, btree (uuid)
    "buylist_price_condition_idx" btree (buylist_price, condition)
    "buylist_price_idx" btree (buylist_price)
    "card_inventories_card_uuid_idx" btree (card_uuid)
    "condition_idx" btree (condition)
Foreign-key constraints:
    "card_inventories_card_uuid_fkey" FOREIGN KEY (card_uuid) REFERENCES cards(uuid) ON DELETE CASCADE

I do have a trgm fulltext index on cards.name as well so that I can use ~*.


texts: Example optimisic offline lock in SQL/PL?

texts:
Can someone show me an example of an optimistic offline lock in
an Oracle Stored Procedure?


texts: SQL: How to Select Multiple Row Data for the Same ID?

texts:
My data is like this:

ID      col1
0000001 00020
0000001 00023
0000001 00019
0000001 00010
0000003 00030
0000003 00020

I want to find which IDs have a combination of 2 values in the column 'col1' (appearing at least once each); for example, for the values 00020 and 00023 here I would get the ID 0000001 only.


texts: Latin1 to UTF8 on large MySQL database, minimal downtime

texts:
Let me lay out the basic scenario for the problem I'm up against.

I'm running MySQL 5.1 and have a single database with 2 large MyISAM tables (9M and 20M rows respectively).
Each of these tables has several VARCHAR and TEXT columns which are currently set to the latin1 character set and latin1_ci collation.
These tables are actively read / written to, so we want to avoid as much downtime as possible.

I'm confident in actually converting the data between the two character sets (using ALTER TABLE to convert columns to their binary counterparts, then back to the correct original column type in the correct charset). What I'm not sure about is how to go about the conversion without locking the entire table for the duration (which could take hours / days due to the number of rows and indexes).

One idea is to set up replication to a slave; turn off the replication; convert everything; re-enable replication and let the slave catch up; then finally promote the slave to master.

However, this doesn't solve the issue of how to set up replication in the first place. From what I've read, even mysqlhotcopy & xtrabackup still require table locks for MyISAM tables.

Is there another option I've missed, or am I going to have to turn off my entire application in order to set this up? Thanks for any advice.


texts:
Our new project is to write a customer order management system.  My specific task is to import customer orders from various different sources - XML, CSV, XLS... etc.

Our old DBA has left the company and I have stepped up to the plate. I have plenty of SQL experience but for the first time I'm required to design a brand new system with a feasible data structure to hold the orders of many different customers, which we import into the database.  I don't want to have to change the database every time we have a new customer order with a slightly different structure.

The problem is that every customer wants to provide us with an output from their computer system, and every customer's orders are different.  Our plan is to write a script for each unique customer to take their data file and transform it into our orders table.

So far so good, but what will my customer orders table look like?  Every customer is going to have their own unique fields, some will be mappable to generic fields (like order number, delivery date) for example.. but if I'm to build a solid foundation, I need ideas to store this data in a flexible and scalable way.

Does anyone have any experience with this kind of thing?  Any ideas or tips would be very much appreciated.  Thanks for your time.


texts: Cannot find column “ssma_oracle” user-defined function or aggregate “ssma_oracle.db_get_next_sequence_value”. The name could also be ambiguous

texts:
We have migrated our database from Oracle to SQL Server, but when we have open any trigger we have this error in line this syntax ssma_oracle. db_get_next_sequence_value
when I change with this syntax
sysdb.ssma_oracle.db_get_next_sequence_value('sys',N'dbo', N'SEQ_POP_AFF')
it work , but the company don't want to change the script
We have installed SSMA but this isn't having resolved the problem
Problem Example in picture below


texts: PostgreSQL extending a variable-width bit string type?

texts:
PostgreSQL supports Bit Strings,

SELECT B'01010101', B'01010101', '01010101'::bit(8), X'CC';
 ?column? | ?column? |   bit    | ?column?
----------+----------+----------+----------
 01010101 | 01010101 | 01010101 | 11001100

SELECT pg_typeof(B'01010101'), pg_typeof(B'01010101'), pg_typeof('01010101'::bit(8)), pg_typeof(X'CC');
 pg_typeof | pg_typeof | pg_typeof | pg_typeof
-----------+-----------+-----------+-----------
 bit       | bit       | bit       | bit

The type bit is typed such that I can create a function that accepts a bit of any length regardless (variable-length), and return a Bit String of variable-length:

CREATE FUNCTION type_test(_x bit)
RETURNS bit AS $$
  SELECT _x
$$ LANGUAGE sql;

SELECT type_test(X'CC'), type_test(X'CCCC');

Let's say I want to do an operation though that requires a bit-shifting algorithm, or a scratch pad bigger than the input type, how would I go about declaring that for internal use to the function. Like if I want to create a function that given X'CC' (8 bits) could use a 10 bit scratch pad.

This would almost do what I want,

CREATE FUNCTION type_test(_x bit)
RETURNS bit AS $$
  SELECT ('0' || _x || '0')::bit
$$ LANGUAGE sql;

SELECT type_test(X'55'); -- returns 0

But, it doesn't work because bit is bit(1) so the cast to it truncates the input to a single bit.

Given bit(n), how do I create a bit(n+2) to work with.


texts:
I am trying to compare the database name using the ? operator in the sp_msforeachdb function.  Here is a snippet:

EXEC master..sp_msforeachdb '

USE [?];
IF NOT EXISTS (
    SELECT db_name(database_id) as [database]
    FROM sys.dm_database_encryption_keys
    WHERE [Database] = ''?'')
BEGIN
...
'

Basically, I want to be able to find the databases that do not have an encryption key and begin/end a transaction ELSE begin/end a different transaction.


texts:
Does running UPDATE Statistics on SQL 2008 r2 with full scan on a large table 50 million rows (non partitioned)? I am trying to confirm the following behavior.

a) takes any locks during the entire run? Not sure but I think no locks are taken however a sr. dba told me that it takes schema modify lock (hmm...)

b) cause blocking -- I don't think so

c) cause heavy IO -- as each and every page will be read into the memory and pollute the data page buffer cache. So generally you should do this off hours.


texts: When I try to update/set a datetime I get an invalid mysql syntax 1064

texts:
I copy a value from the pre-existing values.

I update the date.  I do an update query.

It has an issue and the issue seems to be the time.

Here's my query:

UPDATE xxxxx_eb_events
Set event_date 019-04-01 00:00:00;

event_date is of type datetime

And the error

    Error
    Static analysis:

    2 errors were found during analysis.

    Unexpected character. (near ":" at position 52)
    Unexpected character. (near ":" at position 55)
    SQL query:

        UPDATE gsti1_eb_events Set event_date 019-04-01 00:00:00

        MySQL said: Documentation

        #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '019-04-01 00:00:00' at line 2

It's complaining about the colons in the time but I copied and pasted them from the already existing entry.

I know 019 looks like a strange year, I have tried it with

UPDATE gsti1_eb_events
Set event_date 19-04-01 00:00:00;

And I have tried

UPDATE gsti1_eb_events
Set event_date 2019-04-01 00:00:00;

And I have tried enclosing all of them in single quotes


texts: Using :Connect I keep getting msg 911 - database [dbname] does not exist

texts:
I have a two part T-SQL script, the first part runs against a database on a different server, the second part is supposed to run on the local PC. I'm using SQLCMD mode and the :Connect keyword but every time I execute the command SSMS tells me it can't find the remote database in the local section of my code.

Connecting to localhost...
Msg 911, Level 16, State 1, Line 24
Database 'MYDBNAME' does not exist. Make sure that the name is entered correctly.
Disconnecting connection from localhost...

I'm obviously in the localhost section of the code so why does it want to find the remote server database?
Thanks,
Mike F.


texts: For SQL server 2008 R2 Analysis services, how can I move tempdb database files from one drive to another? At the time of installation, I didn't have a separate drive for tempdb. Now that I do have one, I need to move to the new drive.


texts:
I hope someone can help me . I 've Unistall all components sql server 2005 in the control panel but the configuration tools still there.
I try to open HKEY_LOCAL_MACHINE > Software > Wow6432Node > Microsoft > Microsoft SQL Server > 90 but did not find it .
how the solution of my problem ?


texts:
Whenever I try to do a DB query with a particular DB user (myuser) I get the following error:

  Access denied for user 'myuser'@'localhost' to database 'mydatabase' [1044]

I decided to check the grants for that user and saw that one of the grants could maybe benefit from an update so I tried to update it and got the following error:

  Error Code: 1728. Cannot load from mysql.db. The table is probably corrupted

I Google'd the error and found Cannot load from mysql.proc. The table is probably corrupted
, which recommends doing mysql_upgrade -u root -pxxx. I tried that and got this error:

  Error occurred: The mysql.session exists but is not correctly configured. The mysql.session needs SELECT privileges in the performance_schema database and the mysql.db table and also SUPER privileges.

I Google that error and found MySQL Upgrade Issue but the answer there isn't applicable - there appears to be just one mysql.session user when I do SELECT * FROM mysql.user;. So I figured maybe I'll just create the GRANTs myself:

GRANT SELECT ON `performance_schema`.* TO 'mysql.session'@'localhost';
GRANT SELECT ON `mysql`.`db` TO 'mysql.session'@'localhost';
GRANT SUPER ON *.* TO 'mysql.session'@'localhost';

Only problem: when I attempt to do so I get the same "Error Code: 1728. Cannot load from mysql.db. The table is probably corrupted" that I got before.

My question is...  what can I do to fix this? I have a full DB backup that I restored from without issue a few months ago and if there have been any DB changes that have occurred in the DB since then I'm okay with losing. Maybe my best option is to simply uninstall MySQL and re-install it? And if that is the case then are there any steps I can take to prevent this issue from cropping up again?

I'm running MySQL 5.7.25-0ubuntu0.16.04.2 on Ubuntu 16.04.5 LTS on a Digital Ocean droplet.

Any ideas?

edit: I also get this error when I try to create a new MySQL user:

CREATE USER username IDENTIFIED BY 'password';

I tried mysqlcheck -uroot mysql db and got this back:

mysql.db                                           OK

I tried ls -latr /var/lib/mysql/mysql/db* and got this back:

-rw-r----- 1 mysql mysql   65 Nov  8 03:55 db.opt
-rw-r----- 1 mysql mysql  440 Nov 10 17:51 db.MYD
-rw-r----- 1 mysql mysql 5120 Feb 11 11:54 db.MYI
-rw-r----- 1 mysql mysql 9582 Feb 11 11:54 db.frm

I note that there's not an *.ibd file idk.

SELECT * FROM mysql.db, while logged in as both SSH root / MySQL, works without issue, but all the "Error Code: 1728. Cannot load from mysql.db. The table is probably corrupted" errors are also coming to me while logged in as SSH root / MySQL root so idk.

edit2: I can't even do apt-get remove mysql-server as it gives me the same "mysql.session exists but is not correctly configured" that I was getting before...


texts:
I'm in the middle of a database server migration and I can't figure (after googling and searching here) how can I list the database privileges (or all the privileges across the server) on PostgreSQL using the psql command line tool?

I'm on Ubuntu 11.04 and my PostgreSQL version is 8.2.x.


texts: How can I display multiple column records in the same row?

texts:
I want to display all related information in to the same row column. Currently my output and query are the following:

Trader
Items

Prapor
Weapons

Prapor
Wearables

Prapor
Weapon Modifications

Prapor
Health and Buffs

Therapist
Weapons

Therapist
Wearables

Therapist
Weapon Modifications

Therapist
Health and Buffs

select NPC_Traders.Nickname as Trader, Classes.Class as Items
from NPC_Traders, Classes
where Items IN (
           select npc.Tradeables
           from NPC_Traders npc
           where npc.Tradeables LIKE '%1%' OR
                 npc.Tradeables LIKE '%2%' OR
                 npc.Tradeables LIKE '%3%' OR
                                npc.Tradeables LIKE '%4%');

I want to display the information such as:

Trader
Items

Prapor
Weapons, Wearables, Weapon Modifications, Health and Buffs

Therapist
Weapons, Wearables, Weapon Modifications, Health and Buffs

Is there a technique I can use to solve this issue?


texts: How to get number of matching colums count in the same table as another column?


texts: Database Default Locations doesn't persist

texts:
When I set the default location for the Data & Log in the Server Properties in SQL Server 2012, and click Ok, it resets back to the previous value immediately - if I reopen the Server Properties, the values has been reset.

How do I get it to persist? I'm trying to set it to a location on a separate HDD. The Backup folder location has been changed without issue.


texts: PostgreSQL/Psycopg2 indexing array in the size of thousands

texts:
I will have an array the size of 16,380, and I want to index them so I can call aggregate functions over them using 16,380 indices.

For example, I have a table:

+----+-------+----------------------------+
| id | stuff |   large_array_of_values    |
+----+-------+----------------------------+
|  0 |     5 | {1.0, NULL, 3.0, 4.0, ...} |
|  1 |     2 | {2.0, 3.0, 4.0, 5.0, ...}  |
|  2 |     1 | {3.0, 4.0, 5.0, NULL, ...} |
+----+-------+----------------------------+

I want to select all the values from large_array_of_values column with specific index, say index 15,123 and apply an aggregate function such as population standard deviation across index 15,123 of every row in the table.

E.g., in the above table the population standard deviation should be 0.5 across index 1 of each array on every row (NULL, 3.0, 4.0) which ignores NULL values in PostgreSQL.

My main question is how efficient is indexing such a large number of values in PostgreSQL?


texts:
We have a database in SQL Server 2012 with data of 1.3 TB in size. Our current edition is Standard and configured in a failover cluster mode. We are planning to upgrade our edition from Standard to Enterprise.

So my questions are:

Is it possible to upgrade without downtime? As I know maintenance plan need to execute in one node. In my case can I execute it on the passive node without downtime?
What is the best backup method for 1 TB database. Backup/ Restore is enough for this size?
Does it necessary to take backup and restore after up-gradation? I mean the possibility of data corrupt during the update of edition.
Our server has 16 core processor and we have 4 licenses of 4 core server (4 x 4 = 16). Should we put only one license copy?


texts: PostgreSQL not using index scan when a second ORDER BY column is present

texts:
I need to query the latest entry from a table with >130mn entries using PostgreSQL 9.5. There are two columns of interest, row_id (integer, the primary key), and row_time (timestamp, not null, with btree index).

This query works very fast:

EXPLAIN SELECT * FROM tbl ORDER BY row_time DESC LIMIT 1;
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Limit  (cost=0.57..0.88 rows=1 width=423)
   ->  Index Scan Backward using tbl_row_time on tbl  (cost=0.57..40503877.59 rows=131390688 width=423)

In case there are several rows with the same row_time I would like to sort by row_id as a tie-breaker. Unfortunately, the query plan then regresses to a sequential scan:

EXPLAIN SELECT * FROM tbl ORDER BY row_time DESC, row_id DESC LIMIT 1;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Limit  (cost=9602337.32..9602337.32 rows=1 width=423)
   ->  Sort  (cost=9602337.32..9930814.04 rows=131390688 width=423)
         Sort Key: row_time DESC, row_id DESC
         ->  Seq Scan on tbl  (cost=0.00..8945383.88 rows=131390688 width=423)

I don't understand why this is happening. It would be easy to retrieve the rows with the maximum row_time using the index (typically  less than 10 rows) and then sort just these. Instead, the full table is scanned, which takes minutes instead of the sub-millisecond time of the first query.

Creating a multicolumn index on (row_time, row_id) makes PostgreSQL chose an index scan again, but increases the size of the index considerably. A subquery to get MAX(row_time), then filter by it and sort the result by row_id is also fast but seems overly complicated for my simple goal. Am I missing something here?


texts: I have this two models, which one is better?

texts:
what is a better design??

Model 1:

+------+          +--------+
| type |-|--+     | Data   |
+------+    |     +--------+
| id   |    |     | Mach ID|
| type |    |     | Time   |
| len  |    +----<| type ID|
+------+          | data   | (binary max 8)
                  +--------+

Model 2:

+---------+
| Data    |
+---------+
| Mach ID |
| Time    |
| Type_1  |
| Type_2  |
| ...     |
| Type_n  |
+---------+

(there are other tables too, like machine and client)

all types are previusly defined, ex:

id 08 is the temperature1 and its a float 4 bytes.
id 16 is a sensor and its an int 4 bytes.
id 125 is status 2 bytes.

there is only 22 types defined, but types can grow in the future.
the problem is not all MachID have all the types.
If i do the model 2 a lot of fields will have null.

All i have to do is store these data and display graphics per MachID.
And to make graphs, i only need time vs type. so i can make sql selects to get tables like this

+------+--------+--------+-----+--------+
| Time | type_1 | type_2 | ... | type_n |
+------+--------+--------+-----+--------+
|      |        |        |     |        |

and graph, but getting this is more difficult with Model 1.

thanks for your help :)

ps:
every month i get like 15000 records average per MachID.
and sorry for my bad english


texts: Which packages are required to install Oracle 11.2.0.3 on Solaris 11?

texts:
Where can I find information on which packages are required to install Oracle 11.2.0.3 on Solaris 11? The documentation only mentions Solaris 10. I tracked down the packages required for Solaris 10, and most of them are either included in the GA release of Solaris 11 or obsoleted.

There are however a couple of packages that are missing from the base install of Solaris 11:

SUNWarc (renamed to):

consolidation/osnet/osnet-incorporation (installed)
developer/library/lint (missing)

SUNWhea (renamed to):

consolidation/osnet/osnet-incorporation (installed)
system/header (misising)

So it looks like developer/library/lint and system/header are packages from required for Solaris 10 that are missing from the Solaris 11 base install.

The installer however doesn't seem to be looking for any packages whatsoever. Although it does check the kernel version (5.11) Does that mean all the packages needed are bundled in Solaris 11 and  it's safe to install the database?


texts: Best table schema to store daily hits for many items?

texts:
In my Sql Server 2005 database, I'm considering two ways of creating a table to track daily views on 100,000 items:

Option 1) Each day would insert a new row for each item. So the table columns would look like this:

| ItemID | Date | #ofHits |

That would mean the table grows by 100,000 rows per day.

Option 2) Each row would contain an entire month's hits per item. So the table columns would look like this:

| ItemID | Year | Month | Day01Hits | Day02Hits | Day03Hits | ... | Day31Hits |

That would mean the table grows by 100,000 rows per month.

Option 3) Perhaps you have a better idea altogether?

Keep in mind performance, such as when doing a sum of all hits between two dates (i.e. total hits for July 5th-Aug12th). Also, I'd want to keep at least 1 year of historical data.

On a related note, we're simply parsing the IIS logs nightly to get the hit data.

In summary:

Option 1 would probably be the easiest way to query and sum data across a date range. The downside is it would add 100,000 rows every day (or a million rows everyday if we had a million items).

Option 2 would create much less rows, but probably wouldn't be efficient to query and sum data across a data range.

Is there a better way to store daily hits for many items?

Update:
Unless someone has a better design, I'm considering this:
Use option 1, but create a new table every month (this will prevent the table from growing too large). So my table names would contain the year and month, like "Stats2012_01", "Stats2012_02", etc.
Then, to query across dates (such as total hits from July 5th-Aug12th) it'd be:

SELECT ItemID, SUM(Hits)
FROM (
    SELECT ItemID, SUM(Hits) AS Hits FROM Stats2012_06 WHERE Day>=5 GROUP BY ItemID
        UNION ALL
    SELECT ItemID, SUM(Hits) AS Hits FROM Stats2012_07 GROUP BY ItemID
        UNION ALL
    SELECT ItemID, SUM(Hits) AS Hits FROM Stats2012_08 WHERE Day<=12 GROUP BY ItemID
) s
GROUP BY ItemID

Update 2:
I just discovered partitioning, thanks to the suggested answer from MZDBA. I'll continue to look into partitioning, which may lead to combining Option 1 with partitioning.


texts: Include HostName in ODBC System DSN Connection

texts:
Not sure if this is a question for here or if it should go on Stack Overflow. If SO is a better fit, please migrate it.

Our finance team uses Dynamics GP. Occasionally we need to track the query that is being run by a user at a particular computer. I tried to setup an Extended Events session that we could just enable/disable and put in the filter sqlserver.client_hostname:

I can test it out through SSMS and it works fine, the events are captured. I then tried running Dynamics GP and noticed that no events were captured. Thinking I'd stuffed something up I went back to SQL Profiler and ran a trace and discovered that the HostName is not set:

Dynamics GP connects to SQL via a System ODBC connection.

Is there a way to configure the ODBC connection to send the host name to SQL? I've gone through all of the options and I can't find a tick box that will send it.


texts: What is the cost of having many columns as included columns in a nonclustred index?

texts: What is the cost of having many columns as included columns in a nonclustred index in SQL Server?


texts: How To Access My PostgreSQL Server From Another PC Securely?

texts:
I want to be able to connect from any PC anywhere to my Database.

Host

OS: Windows 10
PostgreSQL: 12.0

Currently all Tutorials say the same: (Thats what i also did)

Change listen_addresses to listen_addresses = '*' in the postgresql.conf
Add host all all 0.0.0.0/0 md5 in the pg_hba.conf

But they don't say how to connect from the Client Side and they don't pinpoint how secure it is to do so.

Let's say at the Host I Did the above steps and restarted.
I created a Database called TablesTest.

How do I connect to this database at the Client? How secure is it?


texts: Google Managed PGSQL DB migration to DigitalOcean Managed PGSQL DB

texts:
We are migrating from Google-Cloud PGSQL managed database to a managed PGSQL server on Digital ocean.
The dilemma we are facing is that both Google and Digital ocean are managed so we have no access to directories so we can't dump then restore. So how would we go about this? Is there a command that copies the data directly from Google-Cloud PGSQL to Digital ocean?
Digital oceans resources assume we have access to a directory.


texts: copy schema across different physical databases in db2

texts:
Is there any way to programatically copy across schema (i.e. tables,functions,views) etc from one database over to another one. Assuming both are running same versions of DB2.

I found a few utilities like
  i)  db2move -- i could not find a way to access it via jdbc. So is ruled out.
  ii) admin_copy_schema -- copies across schemas in same database.
  iii)db2look -- again a utility. no way to call it from jdbc.

Even if we find a way to run those utilites db2move or db2look produces files which then have to be copied over to the target machine - making it highly complicated to automate the whole thing.

Is there a simple way to clone over a database through jdbn


texts:
I'm the lone user of my computer. I haven't changed my MYSQL root user password, but it is somehow changed to empty password. Help me out !

My default sakila db is also gone. I specified engine=innodb for one of the tables.Would there be any effect


texts: What is Postgres lock behaviour when UPDATE WHERE clause does a table scan?

texts:
Say you have a large table with tens of millions of rows.
You want to UPDATE large_table SET col=value WHERE col=other_value... but col is not indexed and an EXPLAIN shows that this query will perform a seq scan over the whole table.
What is the lock behaviour here?  According to most accounts Postgres only locks the affected rows of an UPDATE query and does not have lock escalation. So does it search for the rows to update first, then only lock the found rows?  It seems like there would potentially be problems of other queries updating rows concurrently in that case though. Does it lock each row "as it finds them" i.e. locking rows progressively as it goes through the seq scan?
So I think the best case here is it locks rows as it finds them, and the affected rows (only) will be locked for up to as long as the UPDATE query takes to complete.
But I am worried that this query could instead end up blocking all writes to the table until it completes.
I have read this: https://habr.com/en/company/postgrespro/blog/503008/ and I think the worst case will not happen, but here https://blog.heroku.com/curious-case-table-locking-update-query is a possibly inaccurate representation of similar info that gives me some doubts.
The application only uses SELECT, SELECT FOR UPDATE and UPDATE queries (i.e. no other explicit locks taken apart from those). The table has foreign keys to other tables, and other tables have foreign key to this table.
We're on Postgres 11.


texts: "For each group of results fullfilling condition X, select only result with smallest column value A"

texts:
I have a table with data and dates like so:

DECLARE @p TABLE (P_key int, P_Data char(1), P_ValidUntil datetime)

INSERT @p VALUES (20, 'T', '2003-02-28')
INSERT @p VALUES (21, 'U', '2005-05-31')
INSERT @p VALUES (30, 'V', '2006-09-30')
INSERT @p VALUES (30, 'W', '2008-04-30')
INSERT @p VALUES (31, 'X', '2007-06-30')
INSERT @p VALUES (32, 'Y', '2005-01-31')
INSERT @p VALUES (32, 'Z', '2007-06-30')
INSERT @p VALUES (33, 'A', '2005-06-30')

Given: qKey (only multiples of 10), qDate

Find: All entries that match qKey in the first digit and have a date larger then qDate. Return only one result per P_key (the one with the next higher P_ValidUntil to qDate).

The current solution is:

DECLARE @qKey AS int;
DECLARE @qDate AS datetime;

SET @qKey=30;
SET @qDate='2006-01-01';

SELECT * FROM @p WHERE
@qKey = (P_Key/10)*10
AND @qDate <= P_ValidUntil

which returns:

30    V    2006-09-30 00:00:00.000
30    W    2008-04-30 00:00:00.000
31    X    2007-06-30 00:00:00.000
32    Z    2007-06-30 00:00:00.000

This is basically correct, except that there are two entries for 30 (both are bigger than @qDate). I only want the smallest date of these multiple hits in the result:

30    V    2006-09-30 00:00:00.000
31    X    2007-06-30 00:00:00.000
32    Z    2007-06-30 00:00:00.000

Thanks in advance!


texts: Restore master DB to default(as just installed) without backup

texts:
I know this is not a reasonable request but is it possible to reset master DB to the status it was just installed without backup?

I deployed a DB project which I didn't know it will mess with master DB so I didn't backup it in advance plus the deployment script has some defects so the process failed and left tons of rubbishes in master DB.

I did a bit search but most of the instruction requires a backup whereas I can't be sure for those don't request backup can work master DB around or not.

Is there any way else except reinstalled the instance all over?


texts:
Am running asp.net application.Am searching records.if i click the view button i got this error,

  an inconsistency was detected during an internal operation in
  database(database name) on page(some page).please contact technical
  support.reference number 6.

I Googled and i got the result as to run DBCC CHECKDB.
I run the following command in sql server 2005,

DBCC CHECKDB (database name) WITH NO_INFOMSGS, ALL_ERRORMSGS

my result was something like this

  Table error: Object ID 1424372489, index ID 1, partition ID 374822652149760, alloc unit ID 93347675439104 (type In-row data). Page (1:72872) is missing a reference from previous page (1:72871). Possible chain linkage problem.
  Msg 8928, Level 16, State 1, Line 1

  Object ID 1424372489, index ID 1, partition ID 374822652149760, alloc unit ID 93347675439104 (type In-row data): Page (1:109577) could not be processed.  See other errors for details.
  Msg 8978, Level 16, State 1, Line 1

  Table error: Object ID 1424372489, index ID 1, partition ID 374822652149760, alloc unit ID 93347675439104 (type In-row data). Page (1:109578) is missing a reference from previous page (1:109577). Possible chain linkage problem.
  Msg 8976, Level 16, State 1, Line 1

  CHECKDB found 0 allocation errors and 10 consistency errors in table 'some table' (object ID 1424372489).

  CHECKDB found 0 allocation errors and 5 consistency errors in table 'some table' (object ID 1432300808).

  CHECKDB found 0 allocation errors and 19 consistency errors in database 'database name'.
  repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (database name).


texts: Is it possible to extract (not reset) the sa or other passwords from SQL Server?

texts:
Let me start out that I do not need to reset the SA password, I know how to do that and there are plenty of resources on this site and elsewhere that give detailed step by step tutorials.

We have SQL servers set up on site at clients premises, the problem I am having is 3rd party companies are logging in to the server using our own service logins and doing data mining and data pushing. We are trying to stop this behavior (I know this is a impossible goal, but I am trying to convince other people of this fact).

We have figured out how they are getting our logins currently and will be fixing that, however what I would like to know is: Is there any known tool or process that can "extract" login passwords from a sql server instance (be it a offline attack or via intercepting and snooping a valid connection)?

I think there is likely a way to do it but management would like proof that it can be done.

Does anyone know of any set of tools or processes that it can be done with so I can demonstrate a proof of concept to management?


texts: How to model dependent categories (parent-child-relation)?

texts:
Idea / Goal
I have two tables: Article (represents a Product) and Category. The relation is like this: An Article can be assigned to only one Category but a Category may have at least one Articles, therefore, 1:n (n >= 1).
Now, I want to build a category tree that holds up to 5 dependent Category entries. The category-hierarchy is constant. I have given each Category a level field that tells me how deep I am in the tree (1-5, 1 being the main category, 5 being the last leaf possible). Now I still need to know how to navigate ... I thought that I could just set a parent_category field that points to the upper category. The main categories, however, do not have a parent_category respectively they are set to null.
With this approach, I could query the databases for each level. This would make sense since I want to navigate through my categories and then see the sub-categories on entering a category (imagine a URL like: %host%/category/sub-category).
Example
Please note: The data represented here is arbitrary. But the structure would be identical to this example below (I have omitted some other fields because they don't matter for this question):
Article

| id | name            | fk_category |
| -- | --------------- | ----------- |
| 1  | Apple           | 4           |
| 2  | Orange juice    | 3           |

Category

| id | name     | level | parent |
| -- | -------- | ----- | ------ |
| 1  | Fruits   | 1     | null   |
| 2  | Domestic | 2     | 1      |
| 3  | Juice    | 3     | 2      |
| 4  | Whatever | 3     | 2      |

The URL %host%/fruits/domestic would be resolved into a SQL-Query (simplified here) like:
SELECT name
FROM Category
WHERE level = 3

And then give me the names: "Juice" and "Whatever".
The URL %host%/fruits/domestic/whatever would be resolved into a SQL-Query (simplified here) like:
SELECT name
FROM Article
WHERE fk_category = 4

This should give me "Apple" (... and any other Articles that are within that category).
I am not sure yet how I should implement it that the Category also knows if it is a node without children. I could set a boolean for has_children.
Question
How would you model this relationship? Is it okay to model it the way I had in mind? Is there a better alternative? Does it violate any rules?


texts: SSIS - insert system date variable as a execute sql task parameter

texts:
I am trying to insert the system:starttime variable into a table by passing a parameter into an execute sql task.

I dont have much knowledge of SSIS data types, and I am currently having trouble making this functionality work. When I set the parameters datatype to nvarchar, the date is always inserted as 1/1/1900. When I use the DBDATE type, the insert fails with the following error

unsupported data type on parameter binding 0

So my question is how do I pass a SSIS date variable into an execute sql task so that SQL Server can interpret it correctly?


texts: Postgres using powershell scripts - where do trigger messages go?

texts:
I have a windows (10 pro) powershell script that reads messages from an external server and INSERTS them into a postges (v10.5) database table.

The table has an AFTER INSERT trigger defined on on it which includes some RAISE NOTICE statements to generate simple messages. If I manually insert a single record using pgAdmin then I can see the RAISE NOTICE in the 'messages' tab.

Where do the trigger messages go when inserting into the same table from a powershell script?
How can I make the messages from the trigger appear in the powershell console window?

UPDATE - Database connection is made like this:-

$DBConnectionString = "Driver={PostgreSQL UNICODE(x64)};Server=$MyServer;Port=$MyPort;Database=$MyDB;Uid=$MyUid;Pwd=$MyPass;Options='autocommit=off';"
$DBConn = New-Object System.Data.Odbc.OdbcConnection;
$DBConn.ConnectionString = $DBConnectionString;
$DBConn.Open();

and the insert is issued like this...

$insertDml3 = "INSERT INTO Message(rawMessage, loadedTime) VALUES ('$strLine',to_timestamp('$loadDate','DD-Mon-YYYY HH24:MI:SS'))"
$DBCmd = $DBConn.CreateCommand();
$DBCmd.CommandText = $insertDml3;
$DBCmd.ExecuteReader();


texts: Maximizing Availability on a Table When Changing Data Type

texts:
I'm looking for some advice on the best way to change the data type on a column from an INT to a BIGINT on a table with 220 million rows, as it has reached the upper limit of the INT data type.

The database is on SQL Azure DB, and there is a clustered index with a primary key constraint, a foreign key constraint, and a non-clustered index. The table the foreign key is from also has 3 non-clustered indexes which include the key.

What is the fastest/most efficient way to achieve this? Ideally in such a way as to minimize the length of time the table will be inaccessible.

I am planning to test by dropping the constraints and indexes, making the change, and recreating them, as well as moving the data into a new table and creating the appropriate constraints and indexes there, but I wanted to find out if anyone could offer some advice on this first. I've seen a few ideas online such as moving data in batches, but I'm not sure what will work best.  The table itself is only about 12GB, but I tested the whole operation on a local copy of the database and it generated 150GB of log.


texts: Should I use an index or a hash function on a primary key if I have 100000 rows of data?

texts: I have a table named PRODUCT that has 100,000 rows. I was wondering if either indexing, or a hash function on the primary key would result in better performance. I was also hoping someone could explain why either of these would be better.


texts: How to save mongodb data sub data more properly in this case?

texts:
I have a business collection.

The businesses are reviewed by users.

Each user can only review a business once. Future reviews will simply replace the former one.

This is how we store it:

 "Reviews" : {
    "gusyanto" : {
      "day" : "December 21, 2012, 8:08 am",
      "review" : "Tes review"
    },
    "sapi" : {
      "day" : "December 21, 2012, 8:18 am",
      "review" : "makanan ini sangat enak"
    }
  }

Alternatively we can also use this:

 "Reviews" : [{
      "userid" : "gusyanto",
      "day" : "December 21, 2012, 8:08 am",
      "review" : "Tes review"
    },
    {
      "userid" : "sapi",
      "day" : "December 21, 2012, 8:18 am",
      "review" : "makanan ini sangat enak"}
    ]
  }

So basically we want can store it as a dictionary where the key is the userid or we can store it as an array where userid is the field key. So the key in the second approach is static, namely userid.

We want to index the entries. For example, a user may want to know what businesses he has reviewed.

Which approach I should use?


texts:
I have 2 servers (master-slave) with Centos 7.3 (64 GB RAM) and MySQL Server 5.6.34 installed. MySQL keeps using more and more RAM; what I've been doing is stop/start MySQL so it releases the memory, before my SO kills it. Here's a graph of one of them (the master)

I've done minor changes in my.cnf to "help" using less memory, but it keeps going up always. Searched the web to find if there's a memory problem with this version but found nothing. One thins that it's mentioned in the changelog to 5.6.35 (https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-35.html) is:

  InnoDB: InnoDB failed to free memory used by the full-text optimizer
  thread. (Bug #24331265)

but I'm not using full-text indexes:

mysql> SELECT * FROM information_schema.innodb_ft_config;
Empty set (0.00 sec)

Some support in Rackspace recommended me to use jemalloc (For other reasons; there were performance issues with replication); so it was configured:

#vim /etc/systemd/system/mysql.service
Environment="LD_PRELOAD=/usr/lib64/libjemalloc.so.1"

And was running with that until May, 31 (the last restart in the graphic); when I removed it, hoping it was the cause, but today I found that the server once again is using 94% of my memory. Here's my cnf:

[client]
default-character-set = utf8

[mysqld]

## General
ignore-db-dir                        = lost+found
datadir                              = /var/lib/mysql
socket                               = /var/lib/mysql/mysql.sock
tmpdir                               = /var/lib/mysql/mysqltmp
port                                 = 3307

## Cache
table-definition-cache               = 4096
table-open-cache                     = 4096
thread-cache-size                    = 50

## Per-thread Buffers
join-buffer-size                     = 2M
read-buffer-size                     = 2M
read-rnd-buffer-size                 = 2M
sort-buffer-size                     = 2M

## Networking
interactive-timeout                  = 600
max-connections                      = 250
max-connect-errors                   = 1000000
max-allowed-packet                   = 16M
wait-timeout                         = 600

## MyISAM
key-buffer-size                      = 256M
myisam-sort-buffer-size              = 128M

## InnoDB
innodb-buffer-pool-size              = 35G
innodb-file-format                   = Barracuda
innodb-file-per-table                = 0
innodb-log-file-size                 = 512M
innodb_buffer_pool_instances         = 16

## Replication and PITR
binlog-format                        = MIXED
expire-logs-days                     = 2
log-bin                              = /var/lib/binlogs/fab65-bin.log
log-slave-updates                    = TRUE
read-only                            = 0
relay-log                            = /var/lib/binlogs/relay3309-log
relay-log-space-limit                = 16G
server-id                            = 65

gtid-mode                = ON
enforce-gtid-consistency         = TRUE

master-info-repository           = TABLE
relay-log-info-repository        = TABLE
sync-master-info             = 1

report-host              = fab65
report-port              = 3307

#Cambiar la codificacion de la DB
character-set-server = utf8
collation-server = utf8_general_ci
init-connect='SET NAMES utf8'

# Controls whether stored function creators can be trusted not to create stored functions that will cause unsafe events to be written to the binary log.
log-bin-trust-function-creators

# Increase the group_concat_max
group_concat_max_len             = 1M

# Modify path for secure load_file
secure_file_priv = '/var/backup'

[mysqld_safe]
log-error                            = /var/log/mysqld.log
open-files-limit                     = 65535

[mysql]
default-character-set = utf8
no-auto-rehash


texts:
In a database I have two tables:

The first has a field named taxonomy_id that is an integer
The latter has a field named ID that is a character varying

The two tables are related: if it exists a row in the first one with taxonomy_id = N it will exist a row in the second one with ID = N.toString.

Now, I would like to do the join between this two tables; naturally the "normal" join doesn't work because of the type mismatch.

Can you help me solving this?

I'm using PostgreSQL.


texts:
Very weird question:

Is there any way to artificially inflate the amount of time a query will take? An infinite loop would be great.

SELECT CLAIM.UNIT_PRICE FROM CLAIM WHERE CLAIM.DATE_PROCESSED>SYSDATE-10;

Needs to take enough time for our DBA to analyze something while it's running.


texts: Duplicate key for record error when repairing mysql

texts:
I'm trying to repair a table in my database.  Currently, my database is empty (no tables).  I found a backup of the database and grabbed three files.

comments.frm
comments.MYI
comments.MYD

I then put these files in the database folder: /var/lib/mysql/shf_main2

When I run a repair, I get this error a lot.

| shf_main2.comments | repair | warning  | Duplicate key for record at 36960720 against record at 152   |
| shf_main2.comments | repair | warning  | Number of rows changed from 182427 to 52

There are 123355 records in the backup.  When I check the table, there are only 52 records.  How can I recover all of them?

I have tried repairing by these methods which all give the same results:

SSH Access commands

myisamchk -r *.MYI
myisamchk -o *.MYI
myisamchk --safe-recover *.MYI

SQL Commands

REPAIR TABLE comments;

I do not understand why its getting a dulicate key for all by 52 records.  Thank you for any help.


texts: ON DELETE CASCADE not deleting entry for foreign key

texts:
I am running the below query in ideone

CREATE TABLE AUTHOR (
AUTHOR_ID NUMBER(5) PRIMARY KEY,
AUTHOR_NAME VARCHAR2(18)
);

INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1234, 'MC GRAW HILL');
INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1235, 'J K ROLLING');
INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1236, 'WILLAM');
INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1237, 'SHAKESPEAR');

---------------------------------
CREATE TABLE BOOK
(
BOOK_ID NUMBER(5) PRIMARY KEY,
BOOK_NAME VARCHAR2(15),
AUTHOR_ID NUMBER(5),
CONSTRAINT author_fk
FOREIGN KEY (AUTHOR_ID) REFERENCES AUTHOR(AUTHOR_ID)
ON DELETE CASCADE
);

INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)
    VALUES (10001, 'SOFTWARE DEVELOPEMENT', 1234);
INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)
    VALUES (10002, 'HARRY POTTER', 1235);
INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)
    VALUES (10003, 'TO BE OR NOT TO BE', 1237);
INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)
    VALUES (10004, 'PROJECT MANAGEMENT', 1234);

After creating these entries, when I try to delete the entries in AUTHOR Table by using following command:

DELETE FROM AUTHOR WHERE AUTHOR_ID = 1234;

Select * from book ;

10001|SOFTWARE DEVELOPEMENT|1234
10002|HARRY POTTER         |1235
10003|TO BE OR NOT TO BE   |1237
10004|PROJECT MANAGEMENT   |1234

and

Select * from author ;

1235|J K ROLLING
1236|WILLAM
1237|SHAKESPEAR

the entry from parent table is deleted but their is no delete cascade effect in child table (BOOK table).
Can someone help me what I am doing wrong.

Thanks,


texts: reseed auto_increment to specific number?

texts:
Is it possible to set the identity column of a table to start from a specific value?

I'm converting an ETL stage from mysql to a sql server database where a staging database loads records with a unique ID starting from the maximum value in the main database (which is not auto-incrementing). The mysql went like this:

SET @s = Concat('alter table table_1 auto_increment=',coalesce(@indexmaxt,0)+1);

Ideally this is what I want to do:

DBCC CHECKIDENT (table_1 , RESEED, @indexmaxt+1)

Is this possible or is this impossible in SQL server


texts: Why does PostgreSQL require its own user/group for installation?

texts: When installing PostgreSQL via MacPorts (and I assume via other means too), a postgres:postgres user/group is created. Are there any reasons why this user/group is needed?


texts: How to avoid index scans in SQL Server 2005?

texts:
I have a table named Cell which has columns like Cell_ID, CellValue and CellRow.

Cellrow can have values maximum 1 to 5. There is a clustered index on Cell_ID field. This table contains 100000 rows.

In my query I have to use the CellRow column in the WHERE clause like CellRow > 3 to get data from CellValue field. The problem is when I do it like this then it is always table scan that makes my query very slow.

I have also checked it by putting non-clustered index on CellRow field but still index scan because the CellRow field don't have so much values, it has only 1 to 5 values each time. I can't use CellID in the where clause.

I am using many tables in my query like

Table_1 is joining with table_2 by using clustered index column and table 2 is joining with table_3 by using non-clustered index column and table_3 is joining with Cell table by using non-clustered index.

The ending story is, I am getting different values from all the tables including cell table but when I use CellRow field in the WHERE clause, I am getting index scan.

Any solution :)

Thank you so much!


texts: Mongo initial sync failing with InvalidSyncSource

texts:
I'm trying to add a new 3.4 (RocksDB engine) member to a ~2 TB replica set currently consisting of 3.2 (WiredTiger engine) members and running into problems with the initial sync. Because of the different storage engine, the common suggestion of using a filesystem snapshot to bootstrap the new member won't work.

Replication starts fine, but eventually runs into InvalidSyncSource errors. Strangely enough, the log entries appear to claim that the OpTime of the primary server is more than 30 seconds behind that of the secondary, which sounds impossible to me. I have confirmed that the primary server remained in state PRIMARY throughout the entire operation, and that the clocks on all hosts are in sync.

First indication that something's not right (initial sync source is db-primary.xxxxxxxxxxxxxxx:27017):

2017-04-28T10:58:52.183+0200 I REPL     [replication-5] re-evaluating sync source because our current sync source's most recent OpTime is { ts: Timestamp     1493369879000|2, t: -1 } which is more than 30s behind member db-secondary.xxxxxxxxxxxxxxx:27017 whose most recent OpTime is { ts: Timestamp 1493369931000|2, t: -1 }
2017-04-28T10:58:52.183+0200 I REPL     [replication-5] Finished fetching oplog during initial sync: InvalidSyncSource: sync source db-primary.xxxxxxxxxxxxxxx:27017 (last optime: { ts: Timestamp 0|0, t: -1 }; sync source index: -1; primary index: -1) is no longer valid. Last fetched optime and hash: { ts: Timestamp 1493369932000|1, t: -1 }[3606797002863800866]
2017-04-28T10:58:52.183+0200 E REPL     [replication-5] Error fetching oplog during initial sync: InvalidSyncSource: sync source db-primary.xxxxxxxxxxxxxxx:27017 (last optime: { ts: Timestamp 0|0, t: -1 }; sync source index: -1; primary index: -1) is no longer valid

I'm not sure if the "Timestamp 0|0" in the 2nd & 3rd lines here are meaningful but they also look strange to me. Mongo happily continues to sync until it has copied all databases, after which this happens:

2017-04-29T01:16:52.317+0200 E REPL     [initial sync-0] Initial sync attempt failed -- attempts left: 9 cause: InvalidSyncSource: sync source db-primary.xxxxxxxxxxxxxxx:27017 (last optime: { ts: Timestamp 0|0, t: -1 }; sync source index: -1; primary index: -1) is no longer valid
2017-04-29T01:16:53.379+0200 I REPL     [initial sync-0] sync source candidate: db-primary.xxxxxxxxxxxxxxx:27017

What could be causing the initial sync to fail in this way? The only thing I can come up with is that an attempt to read the OpTime from the primary hangs for quite a while and returns late enough to be compared to a much more recent reading from the secondary, but I would think/hope that Mongo would account for that. In addition, there is a reliable 1 Gbit network link between all hosts involved, with no indication that anything went wrong on the network level.

Regardless of possible explanations, can anyone suggest an approach that might prevent this issue from occurring?

Note that I have succesfully performed this operation on a much smaller (~300 GB) replica set, but that might have just been lucky if the problem occurs randomly.


texts: Odd SQL Server hang when querying exec DMVs

texts:
The other day I ran into a very odd issue that when I execute this query:

select * from sys.dm_exec_cached_plans cp
inner join sys.dm_exec_procedure_stats ps
    on ps.plan_handle = cp.plan_handle

It would execute for a long time (10 minutes before I gave up which is much longer than I expected).  At the time I ran it I had recently cleared the procedure cache so there were likely no more than 20-30 cached plans.

When I cancel the query it continues to run and never cancels.

I then tried to use the KILL command on the spid. When I look at the query in sys.dm_exec_requests the last wait type was SOS_SCHEDULER_YEILD and the command is KILL/ROLLBACK.  Once again I waited for a long time (10 minutes or so)

In order to kill the query I tried to stop the SQL Server service through SQL Configuration Manager.  This hung.  The only way I could find to stop the query or the service was by using sysinterals tool pskill.  Not ideal but it a test installation so no big deal.

I tried to search to see if anyone else has seen this but can't find a search criteria that filters out the noise.

Ive seen this on two boxes in my environment, one SQL Server 2008 R2 SP1 and another SQL Server 2008.

I executed this again just now and it returned quickly (milliseconds).

Can anyone shed any light about this?  Am I missing something obvious or is this a bug.

[Edit: adding based on an answer from luvizuvi]
I could find no obvious blocking processes or other bottlenecks related to memory, disk, cpu, other processes, etc.  When looking at sys.dm_exec_requests this was the only command in a running state.

Thanks.


texts: MySQL order by is equal, in what order are the results returned?

texts:
I use
mysql  Ver 14.14 Distrib 5.5.44, for debian-linux-gnu (x86_64) using readline 6.3

I have rows that are ordered by an order column.

Turns out, many times, the order number is the same, say 1. But the results are returned in a fixed order.

SELECT * from mytable ORDER BY fooOrder;

Will return results ordered by what if fooOrder is always 1?

There is obviously an order, but I don't know which one.


texts: Best option to index an application log table for quick insertion and retrieval in date order?

texts:
Suppose I have an application trace log table with three columns: (id uniqueidentifier, message nvarchar(max), and started datetime2).

The primary key (clustered) is on [id]. I need to be able to insert records as quickly as possible, but I'm not sure if that's the best primary key. It makes sense because I'm using EntityFramework and that's the default, but the uniquidentifier type has pretty random values for each insertion, so it would insert rows all over the place when clusted on id. I think that's good, because it lowers page contention (vs inserting them all on the last data page all the time), but I'm not sure about that.  At the same time, I need a way of retrieving the rows quickly in order by [started].

My question is, would it be better to:
1. Change the primary key to be composite on (started, id) so it's the only index and the data pages are in order by date, or should I
2. Leave the primary key on [id] and add a unique non-clustered index on (started, id).

My reasoning for including both (started,id) in the 2nd option is so it can be a unique index that would include the clustering key (which is always implicitly included anyway). I don't want to include any other columns in the index because it would duplicate a lot of [message] data unnecessary, when all I'm really interested in is an index that speeds retrieval of rows in order.  Or, is there a better option than the one's I mentioned?


texts: MySQL 5.1.73 change default log location

texts:
How do you change the default location of the log_error file?  If you point it to a new path, will it automatically move the existing file? Or will it create a new file, and the original file can be deleted?  Software is installed on Windows.

Also, how do you change the default location of the innodb log files?


texts: I've noticed that SQL Server 2016 installations include a new default login called NT SERVICE\SQLTELEMETRY. I'm aware that for the free editions of SQL Server 2016 you agree in the license to allow your server to send telemetry information to Microsoft. I would guess that this login is associated with that work. As part of our new instance installation process we prefer to clean up and remove any unused system or default logins to avoid potential holes in security or clutter. Does this account do anything particularly important such that we'd want to keep it and if not would it break the license agreement to disable or delete it? I should note that our SQL Servers are generally not Internet facing.


texts: PostgreSQL: When should I use fillfactor < 100 for tables on SSD?

texts:
PostgreSQL documentation says that choosing smaller fillfactor lets table reserve space for future updates on the same page and

  "This gives UPDATE a chance to place the updated copy of a row on the
  same page as the original, which is more efficient than placing it on
  a different page."

Indeed this could improve UPDATE performance if cluster was stored on regular HDD drives, but mine is on SSD. As far as I understand setting fillfactor < 100 will not give me any performance benefit but only waste space on a (relatively) expensive storage.

Do you have a counterexample for my case?


texts: MS SQL: Can't connect to server with anything but 127.0.0.1 on the server machine

texts:
I'm not able to connect to sql server with anything but 127.0.0.1 on the server machine.  If I try to connect via actual IP on the network it does not work locally or remotely.

The environment is SQL Server 2005 on Windows Server 2000.  My goal is to connect to this SQL Server instance from a remote machine.

I've tried telneting to port 1433 using the actual IP and that fails.
I've tried turning off Windows Firewall completely

Any ideas or help is greatly appreciated!
Thanks


texts:
Is it a bad practice to always create a transaction?

For example, it is a good practice to create a transaction for nothing but one simple SELECT?

What is the cost of creating a transaction when it is not really necessary?

Even if you are using an isolation level like READ UNCOMMITTED, is it a bad practice?


texts: How do I append all tables from one Azure Database to another?

texts:
I have two Azure databases (on different Azure instances) with the same schema. I need to append all the records from one of the databases to the other. All keys are GUIDs so should not collide.

Many tables have foreign key constraints. I have worked out the dependencies between tables and have the order in which I want the tables to be copied.

What tool allows me to append tables and specify the order in which the tables are copied? Should I use MS Data Migration Assistant? SSIS? Import and Export Wizard? Some other tool?


texts: Cannot rejoin or add instance to MySQL InnoDB cluster

texts:
I have a MySQL InnoDB cluster running where I had added three nodes, one is having the Primary role and the others are Secondary. Today morning, the two secondary nodes were removed from the cluster after I restored a backup in a test database on the primary server and then deleted the backup before it was replicated to the other two secondary nodes. Now, when I want to rejoin or add those secondary databases back to the cluster, they cannot be added. Below are the screenshots of the error messages I get.
When I want to rejoinInstance(), I get the below error:
MySQL  pdb1-mod:3306 ssl  JS > cluster.rejoinInstance('root@ddb-mod')
Rejoining the instance to the InnoDB cluster. Depending on the original
problem that made the instance unavailable, the rejoin operation might not be
successful and further manual steps will be needed to fix the underlying
problem.

Please monitor the output of the rejoin operation and take necessary action if
the instance cannot rejoin.

Rejoining instance to the cluster ...

Please provide the password for 'root@ddb-mod:3306': ************
Cluster.rejoinInstance: The instance 'ddb-mod:3306' does not belong to the ReplicaSet: 'default'.
(RuntimeError)

When I want to add it through the AddInstance() method, I get the following error:
 MySQL  pdb1-mod:3306 ssl  JS > cluster.addInstance('root@ddb-mod')
 Please provide the password for 'root@ddb-mod': ************
 The safest and most convenient way to provision a new instance is through
 automatic clone provisioning, which will completely overwrite the state of
 'ddb-mod:3306' with a physical snapshot from an existing cluster member. To use
 this method by default, set the 'recoveryMethod' option to 'clone'.

 The incremental distributed state recovery may be safely used if you are sure
 all updates ever executed in the cluster were done with GTIDs enabled, there
 are no purged transactions and the new instance contains the same GTID set as
 the cluster or a subset of it. To use this method by default, set the
 'recoveryMethod' option to 'incremental'.

 Incremental distributed state recovery was selected because it seems to be safely usable.
 Validating instance at ddb-mod:3306...

 This instance reports its own address as ddb-mod:3306

 Instance configuration is suitable.
 A new instance will be added to the InnoDB cluster. Depending on the amount of
 data on the cluster this might take from a few seconds to several hours.

 Adding instance to the cluster...

 WARNING: User 'mysql_innodb_cluster_622884765'@'%' already existed at instance 'pdb1-mod:3306'. It
 will be deleted and created again with a new password.
 ERROR: Unable to start Group Replication for instance 'ddb-mod:3306'. Please check the MySQL server
 error log for more information.
 Cluster.addInstance: Group Replication failed to start: MySQL Error 3092 (HY000): The server is not
 configured properly to be an active member of the group. Please see more details on error log.
 (RuntimeError)

I googled it many times but did not get enough help from there.
Any help is appreciated in advance. Thank you.


texts: "Request to run job dbxxxxx refused" error in merge replication

texts:
I'm doing merge replication whole of my database  but when I  create the subscription I encounter this error:

SQLServerAgent Error: Request to run job DBXX-XX--bazresiostani2-2 (from User sa) refused because the job is already running from a request by User sa.
Changed database context to 'DBXXX'. (Microsoft SQL Server, Error: 22022)

but I don't have this error when creating replication with some tables in database.
The publication database is 2008R2 and the subscriber database is 2005


texts: Unable to turn off automatic memory management (AMM)

texts:
i am running oracle 11.2.0.4 SE RAC on Red Hat linux 6, the database is using ASM for datafile management

i have been experimenting with turning on AMM and set the following parameters

alter system set pga_aggregate_target='0' scope=spfile sid='*';
alter system set sga_target='0' scope=spfile sid='*';
alter system set memory_max_target='8G' scope=spfile sid='*';
alter system set memory_target='8G' scope=spfile sid='*';

but now i wanted to turn off AMM on the instances and database, but when i make the required changes

alter system set pga_aggregate_target=2G scope=spfile sid='*';
alter system set sga_target=6G scope=spfile sid='*';
alter system set memory_max_target=0 scope=spfile sid='*';
alter system set memory_target=0 scope=spfile sid='*';

i receive this error when starting the instance

SQL> startup pfile='?/dbs/initdev12.ora';
ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account
ORA-00849: SGA_TARGET 6442450944 cannot be set to more than MEMORY_MAX_TARGET 0.
ORA-01078: failure in processing system parameters

if i set the MEMORY_MAX_TARGET back to 8G i can start the database but my parameters now look like this

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 6G
sga_target                           big integer 6G
SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 8208M
memory_target                        big integer 0
shared_memory_address                integer     0
SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 2G

any ideas what i'm doing wrong?

cheers
dave


texts: I'm using XAMPP on linux. I reinstalled XAMPP, but I didn't know that I need to export/import databases and not copy/paste lampp/var/mysql folder. PhpMyAdmin displays my database but it has no tables. I have mysql folder with all db files before I reinstalled XAMPP.


texts: Does PostgreSQL `CLUSTER` command also cluster extended storage?

texts: I'm trying to improve the locality of my data by using the CLUSTER command to periodically cluster records that share the same user_id.  My most important data is in extended storage.  Will extended storage be clustered also?


texts: Aggregate count over variable number of json keys

texts:
How can one get a list of json objects like this:

[
    {"band": "beatles", "bass": 1, "drums": 1, "guitar": 2},
    {"band": "metallica", "bass": 1, "drums": 1, "guitar": 2, "vocals": 1}
]

from this data on Postgresql 9.4?

drop table if exists people;
create temp table people(pk serial primary key, band character varying(15), fname text, tag character varying(15));

insert into people(band, fname, tag)
values
('beatles', 'john', 'guitar'),
('beatles', 'paul', 'bass'),
('beatles', 'ringo', 'drums'),
('beatles', 'george', 'guitar'),
('metallica', 'james', 'vocals'),
('metallica', 'lars', 'drum'),
('metallica', 'kirk', 'guitar'),
('metallica', 'robert', 'bass'),
('metallica', 'marty', 'guitar');

--Summary table
select band, tag, count(*)  as ct from people  group by band, tag order by band, tag

This is my closest attempt:

SELECT band, tags from (
    select band, row_to_json(row(tag, count(*))) as tags from people group by band, tag
) t

-- Which leads me towards:
SELECT band, array_agg(tags) from (
    select band, row_to_json(row(tag, count(*))) as tags from people group by band, tag
) t
GROUP BY t.band;

--This is close, but from *{f1:v1, f2:v2}* I need *{v1:v2}*

Here is how I arrived to that one:

select array(SELECT row_to_json(r) from (
    SELECT band, array_agg(tag) from people group by band
    ) r
)

select row_to_json(r) from (
    SELECT band, array_agg(tag), count(*) from people group by band
    ) r;

select row_to_json(r) from (
    SELECT band, json_agg(tag), sum(ct) from (
        select band, tag, count(*)  as ct from people  group by band, tag
    ) x group by x.band
) r;


texts:
a colleague deleted accidentally all transaction logs (SQLServer/Logs/*.ldf files) of our SQL Server 2016 instance. Now we cannot start SQL Server service anymore.
We tried to use a recovery data tool (https://www.cgsecurity.org/wiki/Undelete_files_from_NTFS_with_TestDisk) but not all files were there.

Here is the error that is shown in the Windows Event Logs:

Is there any way to recover/rebuild the logs or any other procedure to make it work again?

Thanks


texts: Performance refactoring: Trying to avoid the table scan

texts:
I have a proc containing a query joining a few tables and I am having some performance issues.

The main table (which is a huge table) has a PK and a few NC indexes.

CREATE TABLE [dbo].[TableA]
(
    [TableAID] [bigint] NOT NULL,
    [UserID] [int] NOT NULL,
    [IP1] [tinyint] NOT NULL,
    [IP2] [tinyint] NOT NULL,
    [IP3] [tinyint] NOT NULL,
    [IP4] [tinyint] NOT NULL
    CONSTRAINT [PK_TableA]
        PRIMARY KEY CLUSTERED  ([TableAID] ASC)
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [idx_1] ON [dbo].[TableA]
(
    [UserID] ASC
)

CREATE NONCLUSTERED INDEX [idx_2] ON [dbo].[TableA]
(
    [IP1] ASC,
    [IP2] ASC,
    [IP3] ASC,
    [IP4] ASC
)

This is the query with the poor perf:

SELECT DISTINCT a.UserID, a.IP1, a.IP2, a.IP3, a.IP4
    FROM [dbo].[TableA] a WITH (NOLOCK)
    JOIN [dbo].[TableB] b WITH (NOLOCK) ON b.UserID = a.UserID
    JOIN [dbo].[Tablec] c WITH (NOLOCK) ON b.CountryID = c.CountryID
    JOIN (
        SELECT
            IP1, IP2, IP3, IP4
            from
            @IPs
            ) as ip ON
                ((ip.IP1 is NULL) OR (ip.IP1=a.IP1)) AND
                ((ip.IP2 is NULL) OR (ip.IP2=a.IP2)) AND
                ((ip.IP3 is NULL) OR (ip.IP3=a.IP3)) AND
                ((ip.IP4 is NULL) OR (ip.IP4=a.IP4))

The definition of @IPs table:

DECLARE @IPs TABLE (
    IP1 int,
    IP2 int,
    IP3 int,
    IP4 int
    )

INSERT INTO @IPs(IP1,IP2,IP3,IP4)
SELECT      T.v.value('(IP1/node())[1]', 'int'),
            T.v.value('(IP2/node())[1]', 'int'),
            T.v.value('(IP3/node())[1]', 'int'),
            T.v.value('(IP4/node())[1]', 'int')
FROM @IPAddresses.nodes('//IPAddresses/IPAddress') T(v)

@IPAddresses is xml. Just found out that xml could send more IPs, so that means more than one row in the IPs table.

The issue is the number of reads on TableA. Even if I have a NC index for the IP columns, that join condition is forcing a table scan...

How can I improve the performance? How can I refactor this table/query?

I am still thinking if there is a simpler and better way to rewrite this code:

SELECT
        IP1, IP2, IP3, IP4
        from
        @IPs
        ) as ip ON
            ((ip.IP1 is NULL) OR (ip.IP1=a.IP1)) AND
            ((ip.IP2 is NULL) OR (ip.IP2=a.IP2)) AND
            ((ip.IP3 is NULL) OR (ip.IP3=a.IP3)) AND
            ((ip.IP4 is NULL) OR (ip.IP4=a.IP4))

...in case when we have more IPs.