texts: Oracle Forms 11g R2 connect to Oracle 11g

texts:
Thank in advance!

I have 64bit Windows 7 with installed Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit.

I installed Oracle WebLogic 10.3.6 how it described here. But it's install 32bit jdk1.6 with it, I couldn't uncheck it and I don't know why:

After this, when I had tried to install Oracle Forms 11g R2, on the 5th step it thrown error:

  "INST-07408: Unable to install or configure the product on a 32-bit JVM on a 64-bit machine"

I've been thinking about it for long time, but the best thing I did it just spoof 32bit jdk1.6 on 64bit jdk1.7 right in folder with 32bit jdk1.6. It worked for me and I was able to install Oracle Forms 11g.

After this I copied the tnsnames.ora from

  'C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN'

to

  'C:\oraclexe\Middleware\asinst_3\config' (yeah, it's already 3rd instance)

After this I run Oracle Forms and trying to connect (system, root), but I receive the error

  'ORA-12154: TNS:could not resolve the connect identifier specified'

But I can connect from sqlplus (system, root), which I run from

  'C:\oraclexe\app\oracle\product\11.2.0\server\bin\'

Also I can connect from Sql Developer.

And sqlplus from

  'C:\oraclexe\Middleware\Oracle_FRHome1\BIN\'

throw error

  'Message file UTILITY_MSG\spw.msb not found!'

I'm trying to solve this problem for the second straight night!! Help me please!:)


texts: Why does DataContext.SubmitChanges() Exist?

texts:
I'm not sure if this goes here or on Stack Overflow, but I figure someone here might know it.

Why did the developers of LINQ decide that not only do you need

ctx.InsertOnSubmit(item);

but you also need

ctx.Items.SubmitChanges();

Did they do this so you could execute several queries on one trip to the database or am I missing something?


texts: What did I miss in EXPLAIN that hinted at server crippling loads?

texts:
I reviewed EXPLAIN ANALYZE output for a query and found a couple subqueries were scanning the whole table. This is done to get only the most recent record in those tables related to an appointment.
After some research, I decided that it would be reasonable to use a lateral join on those subqueries to dramatically reduce the amount of data scanned. EXPLAIN ANALYZE suggested the cost of the whole query with lateral joins would be about a quarter of the original. So we proceeded.
Within two hours of deploying the query change, our DB server was maxed out at 100% and basically unresponsive.  Reverting the query to use subqueries scanning the tables restored the CPU usage to something sane.  Our DB is running in AWS RDS for PostgreSQL using a t2.xlarge.  Performance insights showed a substantial increase in ClientWrite. See .
The query using subqueries along with the EXPLAIN output: https://explain.depesz.com/s/wES6.
select appointments.*,
    reportSnapshots.created_at as latestSnapshotTime,
    responses.created_at as latestResponseTime
from appointments
    left join (
        SELECT DISTINCT ON  (appointmentId) created_at, appointmentId
            FROM reportSnapshots
            ORDER BY appointmentId, created_at DESC
    ) reportSnapshots on appointments.id = reportSnapshots.appointmentId
    left join (
        SELECT DISTINCT ON  (appointmentId) created_at, appointmentId
            FROM responses
            ORDER BY appointmentId, created_at DESC
    ) responses on appointments.id = responses.appointmentId
where appointments.organizationId = 16 and appointments.locationId = '51'
    and appointments.cancelled = false and appointments.filteredIn = true
    and start between '2021-05-04T00:00:00-06:00' and '2021-05-04T23:59:59-06:00'
    and appointments.locationId in (61,60,140,53,138,130,133,131,55,51,100)
group by appointments.id,
    reportSnapshots.created_at,
    responses.created_at
order by start ASC, start ASC, id ASC
limit 100

The query using lateral join along with the EXPLAIN output: https://explain.depesz.com/s/B2vp.
select appointments.*,
    reportSnapshots.created_at as latestSnapshotTime,
    responses.created_at as latestResponseTime
from appointments
    left join lateral (
        SELECT DISTINCT ON  (appointmentId) created_at, appointmentId
            FROM reportSnapshots
        WHERE reportSnapshots.appointmentId = appointments.id
            ORDER BY appointmentId, created_at DESC
    ) reportSnapshots on appointments.id = reportSnapshots.appointmentId
    left join lateral (
        SELECT DISTINCT ON  (appointmentId) created_at, appointmentId
            FROM responses
        WHERE responses.appointmentId = appointments.id
            ORDER BY appointmentId, created_at DESC
    ) responses on appointments.id = responses.appointmentId
where appointments.organizationId = 16 and appointments.locationId = '51'
    and appointments.cancelled = false and appointments.filteredIn = true
    and start between '2021-05-04T00:00:00-06:00' and '2021-05-04T23:59:59-06:00'
    and appointments.locationId in (61,60,140,53,138,130,133,131,55,51,100)
group by appointments.id,
    reportSnapshots.created_at,
    responses.created_at
order by start ASC, start ASC, id ASC
limit 100

Obviously, I did not understand what the EXPLAIN output was telling me about the queries. What did I miss that could have told me the DB load would be higher with the lateral join query despite a lower cost?


texts: File is getting generated into folder, But not a csv file

texts:
For the below SP, normal text file is getting generated instead of csv file.
ALTER PROCEDURE [dbo].[GenerateCDR_Test]
AS
BEGIN
DECLARE @FileName varchar(50),

 @bcpCommand varchar(2000)

SET @FileName = 'C:\TransactionsData\'
              + ('Tansactions_'+  (CONVERT(VARCHAR, GETDATE(), 112) + '_'
             + CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR) + '_'
             + CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR) + '_'
             + CAST(DATEPART(SECOND, GETDATE()) AS VARCHAR))  + '.csv');

SET @bcpCommand = 'BCP '+'"SELECT * FROM [LinkVisaCard].[dbo].[CDRGeneration]"' +' queryout '

SET @bcpCommand = @bcpCommand + @FileName + ' -U abc -P password -w'

EXEC master..xp_cmdshell @bcpCommand
END


texts: In Teradata SQL how to get random number between a pair, coming from field/column?

texts:
I am recently got stuck upon a simple problem in Teradata: how to get random number between a pair of number coming from field/column?
The function RANDOM( lower_bound , upper_bound ) apparently is restricted to only accept fix number rather than field / column name.
--THIS ONE FAILS:
WITH TRIAL(CATEGORY, VAL_MIN, VAL_MAX) AS (
SELECT 'A', 0, 3 FROM CTE UNION ALL
SELECT 'B', 1, 9 FROM CTE UNION ALL
SELECT 'C', 2, 5 FROM CTE UNION ALL
SELECT 'D', 1, 12 FROM CTE
), CTE(DUMMY) AS (SELECT 'X')
SELECT T.*,
RANDOM(T.VAL_MIN, T.VAL_MIN) --HERE'S THE PROBLEM
FROM TRIAL T;

--THIS ONE WORKS:
WITH TRIAL(CATEGORY, VAL_MIN, VAL_MAX) AS (
SELECT 'A', 0, 3 FROM CTE UNION ALL
SELECT 'B', 1, 9 FROM CTE UNION ALL
SELECT 'C', 2, 5 FROM CTE UNION ALL
SELECT 'D', 1, 12 FROM CTE
), CTE(DUMMY) AS (SELECT 'X')
SELECT T.*,
RANDOM(1, 7) --HERE'S THE PROBLEM
FROM TRIAL T;

I'm pretty sure there's simple solution for this. Greatly appreciate any help.


texts: Programatically testing slave status - What fields in SHOW SLAVE STATUS indicated slave status is OK / in ERROR?

texts:
We use pingdom and a few internal heartbeat mechanisms on our product.

We would like to add a 'are our slaves running and in good shape' check.

What fields in SHOW SLAVE STATUS indicate that the slave where SHOW SLAVE STATUS was executed on is not (or is) replicating correctly?

The 2 we are currently testing are:

Slave_IO_Running (must equal Yes)

Slave_SQL_Running (must equal Yes)

Seconds_Behind_Master (must be integer, and less then 10 seconds behind master)

Any others we should be testing?

Thanks again!


texts: Specific format/formula when changing column datatype

texts:
Can one modify a column's datatype using a very specific conversion format?

For example, when converting from datetime to varchar I'd like to use the yyyy-MM-dd hh:mm:ss format, not the system default which is 109 I believe.

Is there any way to achieve this without creating a temporary column?


texts: How to get the best time from submited values?

texts:
I have a MySQL table, and i'm trying to figure how can i get the first result for each user.

For example:

Email               Time    DateSubmission
usera@email.com   | 0:14  | 28/2/2019 00:10
usera@email.com   | 0:10  | 28/2/2019 00:08
usera@email.com   | 0:10  | 29/2/2019 00:10
userb@email.com   | 0:10  | 28/2/2019 00:04
userb@email.com   | 0:11  | 28/2/2019 00:17
userc@email.com   | 0:11  | 28/2/2019 00:18

I've tried to use distinct, order by's but can't find how to get results like:

Email               Time    DateSubmission
userb@email.com   | 0:10  | 28/2/2019 00:04
usera@email.com   | 0:10  | 28/2/2019 00:08
userc@email.com   | 0:11  | 28/2/2019 00:18

Can anyone help?


texts: RING_BUFFER_CONNECTIVITY - LoginTimers - Error 17830 logs when releasing unused database connection in MS SQL

texts:
Good day, can anyone please help me with my problem?

I have this program  that uses QLOLEDB (also tried using the MSOLEDBSQL) to connect to local sql server express 2014 (ver 12.0.2). The program always create 2 connections, one for IDBCreateCommand and one for iOpenRowset which is used for fastload. The programs is functioning well but it keeps on creating RING_BUFFER_CONNECTIVITY LoginTimers logs in sys.dm_os_ring_buffers when I try to release the database connections. No error were created in SQL server logs.

Using the SQL profiler, I am able to know that the logging of RING_BUFFER_CONNECTIVITY happens during Audit Logout of the database connection, though I have 2 connections, only one of them is creating the RING_BUFFER_CONNECTIVITY log and it only happens when releasing connections that were not use (does not matter if it is using IDBCreateCommand or iOpenRowset). Using connections for running SQL query will not log RING_BUFFER_CONNECTIVITY when they are released.

The issue is also happening in MS SQL server 2016 express (ver 13.0.4001) but not in MS SQL server 2012 express(ver 11.0.5058), I am really running out of idea what is causing this. Is there some added feature in SQL server 2014 and later versions that warns user of unnecessary database connections that were created? Like connections that were created but not even use? (I tried proving this by creating database connections using MS SQL management studio then closing it afterwards and it does not create a RING_BUFFER_CONNECTIVITY log... but looking at the sessions created using OLEDB and MS SQL management studio in sys.dm_exec_sessions.. sessions created using OLEDB has memory_usage = 0, while the latter has memory_usage = 3).

What am I missing? Any help is greatly appreciated..

Below is the sample record created in sys.dm_os_ring_buffers:

Thank you


texts: Can't connect to postgresql database with SSL using GSSAPI

texts:
With postgresql 12.4, I've got this in pg_hba.conf:
hostssl all all 192.168.0.0 255.255.0.0 gss map=myrealm

but whenever I try to connect with:
psql 'postgresql://hostname/database?sslmode=require'

I get
2020-11-26 16:03:37.934 GMT [9585] FATAL:  no pg_hba.conf entry for host "192.168.215.220", user "username", database "database", SSL off

If I replace hostssl with host I can connect (with GSSAPI).
How do I get psql to connect with SSL? I have ssl = on in my postgresql.conf and I have valid server.crt and server.key in the PGDATA directory.
Thanks


texts:
hi i have an issue im trying to solve using the following to do a insert statement

begin;
with t1 as (values (2),(3),(4))

,in1 as (insert into table123 (id,val,num)
select nextval('table123_id_seq'),t1.val,42
from t1
on conflict do nothing returning *)

,ins2 as (insert into table456 (id,num,fkey_id)
select nextval('table234_id_seq'),333, (select table123.id from table123 where val = 2)
from in1
on conflict do nothing returning *)
;commit;

what I get is 3 entries into table456 where I want only one

is there a clever way to do returning where X or something similar to avoid the duplication?

currently i workround just divide the insert through 2 queries ins1a with val=2 and ins1b with t1.v


texts: grant permissions in oracle from one database to another

texts: I have two databases, employeedb and contractordb. The employeedb has a user called employeeuser and contractordb has a user called contractoruser. (These are the users I log in with in Oracle SQL Developer). When I am logged into contractordb as contractoruser and I issue the command grant select on table_name to employeeuser; it fails saying 'ORA-01917: user or role 'employeeuser' does not exist'. I get the same thing if I log in as SYS. If I use the GUI and go to "Privileges->Grant" I also don't see the user there. How can I allow employeeuser to select from one of contractoruser's tables?


texts:
We had a log file that wouldn't stop growing even though we do backups.  Turns out that replication had been turned on for that database.  None of us DBAs did that (at least as far as we know).  So, two questions.

Can you tell after the fact who or what turned on replication.  (We noticed that when we turned off replication there were no entries in the log.)
What operations can turn on replication WITHOUT the executioner (there's a better word for that) of the operation to know that they are turning on replication.

Thanks in advance.  We have fixed the problem but want to make sure it doesn't repeat itself.


texts: What memory should be given to the variables of my.cnf ?

texts:
Installing a MySQL server is quite easy. However, after installing, configuring the server is an important task for a DBA.

Having 2GB of RAM and 250GB of disk space, how should I tune MySQL server so that it performs well?

There should also be a proper configuration of the InnoDB Engine.


texts: View multiple Database Servers through single SSMS connection

texts:
Recently I've been handed the keys to an environment with several servers, each running multiple instances of SQL server. These span many version of SQL server over the years.
I have documentation I'm creating, which is currently absent from my predecessor. However this previous DBA connected to a single instance (e.g. DB-Server\Instance) via SSMS and saw the entire catalogue of all SQL servers which could be expanded into their instances. This is extremely useful as often tickets can span multiple servers in a short period of time.
When I connect to this DB-Server\Instance I simply get one instance and the handful of databases on it.
This seems fundamentally a simple issue which could be solved by some basic knowledge from someone else - hoping that's the case!


texts: Error when installing JServer JVM in Oracle XE on Linux AMI

texts:
I'm trying to install OJVM on Oracle 11g (11.2.0.2.0) on Amazon Linux AMI (20016.09).  And I'm getting the following error after running initjvm.sql:

PL/SQL procedure successfully completed.
create or replace java system
begin if initjvmaux.startstep('CREATE_JAVA_SYSTEM') then
*
ERROR at line 1:
ORA-29538: Java not installed
ORA-06512: at "SYS.INITJVMAUX", line 23
ORA-06512: at line 5

Running
SQL> select comp_name, version, status from dba_registry;

shows this:
COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ --------------------------------------------
Oracle Application Express
4.0.2.00.09                    VALID

Oracle XML Database
11.2.0.2.0                     VALID

Oracle Text
11.2.0.2.0                     VALID

COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ --------------------------------------------
Oracle Database Catalog Views
11.2.0.2.0                     VALID

Oracle Database Packages and Types
11.2.0.2.0                     VALID

JServer JAVA Virtual Machine
                               LOADING

COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ --------------------------------------------
Oracle XDK
11.2.0.2.0                     VALID

The install is started and up until that point shows no errors, and everything seems to run and complete successfully, and the dba_registry shows an in progress install.  Anyone have any ideas on what the issue could be?


texts: Query with Indexes slower than without Indexes

texts:
I'm using MySQL 5.6 and my storage engine is InnoDB.

I have a table with 1 million rows containing with the columns:

ID (primary key)
FirstName
LastName
foreign_key_id (foreign key, NOT NULL)
foreign_key_id2 (another foreign key, default NULL)

The rows are seperated under:

25% with foreign_key_id value 1 and foreign_key_id2 NULL
25% with foreign_key_id value 1 and foreign_key_id2 NOT NULL
25% with foreign_key_id value 2 and foreign_key_id2 NULL
25% with foreign_key_id value 2 and foreign_key_id2 NOT NULL

With the following indexes:

index foreign_key_idx on foreign_key_id
index foreign_key_2_idx on and foreign_key_id2
composite index foreign_key_comp_idx on (foreign_key_idx, foreign_key_2_idx)

I perform the following queries:

  Query 1 - without indexes:

SELECT *
FROM table tbl
IGNORE INDEX(foreign_key_idx, foreign_key_2_idx, foreign_key_comp_idx)
WHERE tbl.foreign_key_id = 1 AND tbl.foreign_key_id2 IS NOT NULL

  Query 2 - with indexes (no composite index):

SELECT *
FROM table tbl
IGNORE INDEX(foreign_key_comp_idx)
WHERE tbl.foreign_key_id = 1 AND tbl.foreign_key_id2 IS NOT NULL

  Query 3 - with composite index (no other indexes):

SELECT *
FROM table tbl
IGNORE INDEX(foreign_key_idx, foreign_key_2_idx)
WHERE tbl.foreign_key_id = 1 AND tbl.foreign_key_id2 IS NOT NULL

The results:

  Query 1 (no indexes) performs a full table scan and uses 1 million records with a
  total duration of 0.37 seconds.

  Query 2 (indexes, no composite index) performs a non-unique key lookup on foreign_key_idx index and
  uses 500K records with a total duration of 0.6 seconds.

  Query 3 (composite index only) performs an index range scan on composite index and uses 480K
  records with a total duration of 0.13 seconds.

What I really don't understand is: why is query 2 (with indexes) always performing slower than query 1 (without indexes)? I'm really really stuck and need some help...

I've tested the queries above with different amount of rows, like 1k, 10k, 20k, 50k, 100k, 200k, 250k, 500k, 1M etc, always with the same ratio (25%), and the results where the same (query 2 always performing slow)

Thank you in advance, really appreciate any kind of input!

Edit (2 May 2016)

SHOW CREATE TABLE COMMAND:

CREATE TABLE `table` (
   `ID` int(11) NOT NULL AUTO_INCREMENT,
   `FirstName` varchar(255) NOT NULL,
   `LastName` varchar(255) NOT NULL,
   `foreign_key_id` int(11) NOT NULL,
   `foreign_key_id2` int(11) DEFAULT NULL,

   PRIMARY KEY (`ID`),
   KEY `foreign_key_idx` (`foreign_key_id`),
   KEY `foreign_key_2_idx` (`foreign_key_id2`),
   KEY `foreign_key_comp_idx ` (`foreign_key_id`,`foreign_key_id2`),

   CONSTRAINT `foreign_key_idx` FOREIGN KEY (`foreign_key_id`) REFERENCES `table2` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `foreign_key_2_idx` FOREIGN KEY (`foreign_key_id2`) REFERENCES `table3` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 ) ENGINE=InnoDB AUTO_INCREMENT=1515998 DEFAULT CHARSET=latin1

EXPLAIN PLANS:

Not sure if important, but table2 has 20 records and table3 also 1 million.


texts: Date a backup was taken even if restored to another server

texts:
I have a data warehouse platform that is loaded from backups of a set of application databases restored locally onto the server.  We have a requirement to know what time the backup was taken - i.e. the as-at time on the source system that the data loaded into the EDW was taken.

This question discusses getting last restore dates.

The table msdb.restorehistory has a column backup_taken_from.  Will this still hold the backup date if the backup is restored onto another server - i.e. is this populated from metadata in the backup files?


texts:
Is there a way to specify these options in configuration file?

I've tried to add them into [mysqld_safe] section but it doesn't work:

[mysqld_safe]
numa-interleave = 1
flush-caches = 1

-

# ps -ww -lfC mysqld_safe
F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
4 S root     17180     1  0  80   0 - 26549 wait   15:06 pts/1    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/mysql.pid

Please note that I'm using Percona server 5.5.30-30.2

EDIT: 28 Aug 2013 - 08:34:

Unfortunately I can't find this line in /etc/init.d/mysql:

# grep -n "interleave" < /etc/init.d/mysql
# echo $?
1

I'm able to manually invoke these options:

mysqld_safe --datadir=/mnt/data/mysql --pid-file=/mnt/data/mysql/db3.improve.pid --numa-interleave --flush-caches

but with option file there is still default policy

# cat /proc/$(pidof mysqld)/numa_maps | cut -d ' ' -f 2 | uniq
default

There must be some way other than hard coding these options into init script or manually staring MySQL daemon...

EDIT: 29 Aug 2013 - 08:38:

I've just tested it with Percona Server 5.5.28-29.1.

I still don't see numa related entries in /etc/init.d/mysql however there are in mysqld_safe script:

# grep -n "interleave" /usr/bin/mysqld_safe
21:numa_interleave=0
67:  --numa-interleave          Run mysqld with its memory interleaved
215:      --numa-interleave) numa_interleave=1 ;;
777:# Set mysqld's memory interleave policy.
780:if true && test $numa_interleave -eq 1
785:    log_error "numactl command not found, required for --numa-interleave"
788:  elif ! numactl --interleave=all true
794:  cmd="$cmd numactl --interleave=all"
795:elif test $numa_interleave -eq 1
797:  log_error "--numa-interleave is not supported on this platform"

I've tested other options like nice, open_files_limit and they work fine so it looks like the only option is to hard-code it into init script


texts:
We have been having issues with one of our Windows environments. It uses MySQL and has been running on MyISAM only. We need to create a few InnoDB tables but the engine won't start. We tried changing the my.cnf file and removing the innodb-flush-method, which seems to change the error we get from an invalid flush method (we had 0-Direct) to this (after using the force recovery option):

2016-07-19 18:11:00 3416 [ERROR] InnoDB: Table dbhipcom/cardplus_erro in the InnoDB data dictionary has tablespace id 6, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2016-07-19 18:11:00 3416 [Note] InnoDB: 128 rollback segment(s) are active.
2016-07-19 18:11:00 3416 [Note] InnoDB: 5.6.27 started; log sequence number 1656382
2016-07-19 18:11:00 3416 [Note] InnoDB: !!! innodb_force_recovery is set to 3 !!!
2016-07-19 18:11:00 3416 [Note] Server hostname (bind-address): '*'; port: 3306
2016-07-19 18:11:00 3416 [Note] IPv6 is available.
2016-07-19 18:11:00 3416 [Note]   - '::' resolves to '::';
2016-07-19 18:11:00 3416 [Note] Server socket created on IP: '::'.
2016-07-19 18:11:00 3416 [Note] Event Scheduler: Loaded 0 events
2016-07-19 18:11:00 3416 [Note] D:\mysql\bin\mysqld: ready for connections.
Version: '5.6.27'  socket: ''  port: 3306  MySQL Community Server (GPL)
2016-07-19 18:11:17 50c InnoDB: Error: page 3 log sequence number 4853983
InnoDB: is in the future! Current system log sequence number 1656392.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: for more information.
2016-07-19 18:11:17 3416 [ERROR] InnoDB:  InnoDB: Unable to allocate memory of size 4294967016.

This is the my.cnf:

[mysql]

# CLIENT #
port                           = 3306
#socket                         = /tmp/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = MyISAM
#socket                         = /tmp/mysql.sock

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve

# CACHES AND LIMITS #
default_tmp_storage_engine     = MyISAM
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 100

# INNODB #
innodb-flush-method            = async_unbuffered
innodb-log-files-in-group      = 2
innodb-log-file-size           = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 1G
innodb_lock_wait_timeout       = 3600

# LOGGING #
#log-error                      = mysql-error.log
#log-queries-not-using-indexes  = 1
#slow-query-log                 = 1
#slow-query-log-file            = mysql-slow.log

# REPLICATION #
#log-bin
#binlog_format                 = MIXED
#expire_logs_days              = 7
#server-id                     = 1

How do we start InnoDB? Note we cannot change the default storage engine. Thanks in advance!


texts: After adding a slave to Master - Master configuration, slave syncs from only one master

texts:
Futher to my previous question,

Here is what we have

      +<------+
      |       ^
      V       |
S1<---M1      M2
      |       ^
      V       |
      +-------+

what happens is Upon making any drop, create, update or insert on M1, the slave S1 Syncs the changes in M1. But when we are trying to do the same in M2, we are not getting any changes synced to the slave S1. Any suggestions on how to do that?


texts: Verify SSAS Backup returning arguments instead of expected output

texts:
Following the link http://geekswithblogs.net/darrengosbell/archive/2008/09/13/ssas-verifying-backups.aspx I tried to verify ssas backup and executed below mentioned command in specified path as in link.

msmdsrv -j -b "\\path\ssasfile.abf"

But all it outputs is the arguments I passed in:

 -j -b \\path\ssasfile.abf

However, I was expecting output as mentioned in the post How to check the health of a SSAS database? And of its backups?. Any suggestion is much appreciated.


texts: How to implement search for world-wide Wikipedia/OSM places with alternative-names

texts:
I would like to implement a search-backend which returns a location-aware search results of all Wikipedia / OSM place names, while recognising place names in all languages.

Thus, if you search for "Vienna" or "Wien" from Europe it returns the location of Vienna, Austria, but if you do the same search from within the US, next to one of the many cities called "Vienna", that smaller city might appear above the EU one.

Technically, what I understand it needs two requirements from a DB system:

Geo-aware, thus for each search result it should return it's distance from a query point.
Handle the dozens of alternative-names of places in a smart way. For example "isafjo" should match "Ísafjörður" in an autocompliete.
Cache/index in a way that search results can be provided near real-time, thus allowing an autocomplete experience on the client side.

My ideas for selecting the DB system so far only met PostgreSQL and ElasticSearch, both of which supports geo-features.

I have the following questions:

If I go with PostgreSQL + PostGIS, how would you implement the alternative names? Would a table have as many fields as languages on Wikipedia, with mostly NULL values? Or would you put all the alternative names in a JSON field? Or this is exactly the use case for hstore? Or two tables with a join?
Would ElasticSearch or some alternative technology be better for this, compared to using PostgreSQL? How is the performance of PostgreSQL when used only for searching? I wouldn't be interested in full-text search, but only search in one of the alternative-names of a place (a dozen or so short strings).


texts: sys.dm_db_index_physical_stats slow on first run after rebuilding indexes

texts:
I have a 10GB database in SQL Server 2016 RC1 on Windows Server 2012 R2 where I just performed many insert and delete operations. Afterwards, I ran:

EXEC sp_MSforeachtable @command1="SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD"

Next I run:

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID('db'), NULL, NULL, NULL, 'DETAILED');

The first time I run this command, it finishes in 2:26 (mm:ss).
Subsequent executes finish in around 1:30.

I am not looking for a workaround. I need to know WHY this performance increase occurs because I have similar transactions that are operating the same way.

I have tried updating the statistics (using sp_updatestats) before running the SELECT query but it does not make much performance difference.

I have also tried to recreate the issue by clearing the SQL Server cache as described in https://stackoverflow.com/questions/1873025/how-can-i-clear-the-sql-server-query-cache and rebooting the machine. However, the query still executes in around 1:30 after this, so I do not believe this is a caching issue as suggested in similar posts.

I can only repeat the problem by doing all the inserts, deletes, and index rebuilding mentioned previously.

Extra information:

I am using the default fillfactor of 100 for all tables.
sys.dm_exec_session_wait_stats shows 132804 wait_time_ms for
PAGEIOLATCH_SH after the first execution and 85,635 after the second
execution.
sys.dm_os_latch_stats shows 147313 wait_time_ms for BUFFER
after the first execution with 54137 waiting_requests_count and 2344 max_wait_time
MAX DOP is 0 for database
8 GB RAM on Server
4 Core CPU (2.3 GHz) on Server

Is there something missing after the index rebuilding that is causing this issue? What other steps can I take to isolate the problem?


texts: Accept remote TCP/IP request from aLL IPs in SQL server

texts:
I have problem with access to SQL server with TCP/IP.

I would like to SQL instance accept client from all network adapter that machine has.

I go to configuration manager console and enable TCP/IP, set TCP port to 1433 (or any other port) , set “TCP Dynamic Port” to Blank and set “Listen All” to true. After these changes, I restart SQL server service and starting SQL service will fail. For starting SQL service I have to change “Listen All” to false and then service will start successfully.

But after disabling “Listen ALL” you have to set Adapter IP in “IP Addresses” tab and SQL will accept request only from that IP and if you don’t specify any IP, SQL doesn’t accept any TCP/IP request.

Now I need to know why enabling “Listen ALL” will lead to service failure. And how can I resolve this problem?

I ran “netstate –a” and I saw no application has port 1433 on TCP.

When “Listen ALL” was disable and I don’t added any specific IP Yet, I check SQL browser service(UDP 1434) with “portquery.exe” and SQL browser response doesn’t include any TCP port.

After I added 1 specific IP SQL browser service include TCP 1433.

And as I mentioned if I enable “Listen ALL” , SQL service will failure and after restart it can’t start.

After service restart failure I checked windows event viwer and I saw 4 Error as Following :

1.TDSSNIClient initialization failed with error 0xd, status code 0x10. Reason: Unable to retrieve registry settings from TCP/IP protocol's 'IPAll' configuration key. The data is invalid.

2.TDSSNIClient initialization failed with error 0xd, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The data is invalid.

3.Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

4.SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Finally , How I can Listen to all IPs?

Thanks


texts: SQL Server 2014 SSIS Package Ignores Script Task when running as AGENT

texts:
Alright, so here's what I have going on. SSIS Package that queries data from our production system and drops them into CSVs. Have a VB script task that uploads them to a third party. Needed a multi-post HTTP post with authentication and a couple specific fields sent in the header for the 3rd party so had to do a custom VB script instead of using the web destinations.

I have a ForEach loop that goes through, uploads each file via the script and then deletes it. Works great in BIDS. But when I upload it to the SSIS Package store the upload portion doesn't work. But doesn't throw an error anything since the files are still deleted.

The Query portion works, spits out the CSVs. The Script Task does nothing but the files are deleted.

Threw on all sorts of SSIS logging into the package and the script. Threw a "Hello World" log into the first line of the script and it's never fired.

What I've done:

Created Proxies with full access, linked it to new Creds and SQL logins. Nothing. Made the SQL login have rights to everything, nothing. Run the package as the new Proxy execution account and as the service account. Even changed the account the SQL AGENT service runs on (New SQL server, allowed to mess it up right now). Ran the package as 32-bit nothing. Thought it was tied to the ScriptTask reference being set as "Copy Local = True," but changed that and nothing.

Works A-OK in BIDS AND I've used this same script at another client's environment and it worked fine there. I'm not sure what else to try. Any ideas? I can upload code portions if need be.

SQL Help history


texts: Why does @@dbts increase after backup/restore?

texts:
I have a SQL Server 2005 database, containing some tables which have a Timestamp (or RowVersion) column.
I need to know the value of the current timestamp, so I use the following query: SELECT CAST(@@dbts AS BIGINT);

This will return, for example, 10505.

Immediately afterwards, without updating, inserting, ... anything, I do a BACKUP DATABASE and a RESTORE DATABASE and I run the SELECT query again.
Only this time, the result is 14000, while none of the timestamps in the tables have increased.

Why/how does this happen?


texts: PSQL trying to connect to wrong database

texts:
I have noticed recently a weird (at least to me) behaviour of psql when trying to run psql -l. The essential steps to reproduce it are:

Create a sample DB (I did it with docker and created a simple role with a password)
Export environment variables that are used by psql

PGPASSWORD=password
PGDATABASE=database
PGUSER=username

Edit the pg_hba.conf. And instead of having the default for local I replaced it with. Of course don't forget about reloading the configuration.

local   database          username                                  trust

Running psql works as expected but running psql -l results in

psql: FATAL:  no pg_hba.conf entry for host "[local]", user "username", database "database", SSL off

This seems to not take the environment variables into account when making a connection to the database.


texts: List all used data types of a table or whole database in Oracle DB

texts:
I am preparing a migration from Oracle DB to a SQLite DB.
I would like to know all used datatypes in the Oracle DB so I can check out the equal datatypes in SQLite.

I am sure using Information_Schema seems to be the right way but I don't know how to start.

The used version of the Oracle DB is 10g (10.2).

I am thanking in advance.


texts:
so my function looks like this:

create or replace function insert_stuff(a1 text, a2 text, a3 int)
returns table (id_i int, id_j int) as
$$ begin return query
with
i1(id1) as select insert_here(a1,a2),
i2(id2) as select insert_there(a3,a2),
select id1,id2 from i1,i2;
end; $$

I need the function to check if a1||a2 in table_1.a12 before running the inserts and if it is in skip that insert and move onto the next.

I tried:

create or replace function insert_stuff(a1 text, a2 text, a3 int)
returns table (id_i int, id_j int) as
$$ begin return query
with
i1(id1) as select insert_here(a1,a2),
i2(id2) as select insert_there(a3,a2),
select id1,id2 from i1,i2;
exception when select (select a1||a2) in (select a12 from table_1) then
end; $$

but this just causes the query to stall out


texts:
I understand that when (for example) an update is issued, the data pages are first read into the buffer pool, locks are acquired and changes are written out to the log file in the buffer, then the log file is written to the disk and later at a checkpoint dirty pages in the buffer pool are flushed out and gets hardened on the drive.
What I don't understand is, doesn't this mean the data in the data pages on the disk is stale until a checkpoint? Where does SQL Server read the data from when a select query is issued between "commit transaction" and the checkpoint flush?
Thanks.


texts:
I can't seem to figure out what is causing the I/O Error:

$sqlite3 db.sqlite < data.sql
(3850) disk I/O error
Error: near line 2: disk I/O error
(3850) disk I/O error
Error: near line 3: disk I/O error
(3850) disk I/O error
Error: near line 5: disk I/O error
(3850) disk I/O error
Error: near line 8: disk I/O error
(3850) disk I/O error
Error: near line 9: disk I/O error

See the contents of data.sql below

$ less data.sql
.log stderr
PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;
CREATE TABLE "temp_user_C560B60C-B690-FF4A-8D35-C9A6D4EE2A6B_purchases" (
  "product_id" varchar(36) NOT NULL
);
INSERT INTO "temp_user_C560B60C-B690-FF4A-8D35-C9A6D4EE2A6B_purchases" VALUES ('C6E07AA2-5B01-11E4-9116-00166E0083A1');
INSERT INTO "temp_user_C560B60C-B690-FF4A-8D35-C9A6D4EE2A6B_purchases" VALUES ('C6E07AA2-5B01-11E4-9116-00166E0083AA');
END TRANSACTION;

I already saw this issue: disk I/O error in SQLite
but my issue generates a different error code after I inserted .log stderr at the top of the data.sql file and I am not using Virtualbox - I am not sure if the host is using another form of virtualisation.


texts: SQL Server 2016 Availability Group - 2 Nodes as Primary & Readable Secondary, 1 Node as DR

texts:
I want to create 3 nodes availability group on SQL Server 2016.

Node 1 and Node 2 is my primary server and stays in primary data center. Each nodes set to readable secondary replica.
Node 3 stays in DR Data Center. I wanna use node 3 when servers in primary data center (node 1 and node 2) are down.

How can I set up this configuration? Should I use 2 quorum disks and uncheck the server in preferred owners (in WSFC configuration)?


texts:
I like to know what exactly is the back-end programming? Does it include offline databases? Everyone who designs and creates databases and tables is a back-end programmer? What do they mean exactly by server side programming? By which languages they do this and may someone gives some examples about some of those programs that are written for server and we call them back-end programs? Maybe its good to ask for which kind of projects/programs/applications we need back-end programming?

Sorry if this is duplicated, broad or boring, but I couldn't find my answers any other places!


texts: Efficient database design for multiple price based on quantity and attribute

texts:
I have a data sample for products with different prices depending on quantity and attributes. The price might be updated occasionally (not too frequently).

PID | Name                | Type  | Colour  | Colour Print | Quantity: 1 to 149 |   150 to 199| 200 to 249 |250 to 499
1   |White ABC Product    |ABC    |White    |1CP           |2.34|2.07   |1.82|1.51
2   |White ABC Product    |ABC    |White    |2CP           |2.6 |2.31   |1.97|1.62
3   |White ABC Product    |ABC    |White    |3CP          |2.86|2.55|2.14|1.77
7   |Red ABC Product      |ABC    |Red      |1CP           |2.39|2.12|1.87|1.56
8   |Red ABC Product      |ABC    |Red      |2CP           |2.65|2.36|2.02|1.67
9   |Red ABC Product      |ABC    |Red      |3CP           |2.91|2.6|2.19|1.82
12  |White XYZ Product    |XYZ    |White    |1CP           |2.69|2.38|2.09|1.74
13  |White XYZ Product    |XYZ    |White    |2CP           |2.69|2.38|2.09|1.74
14  |White XYZ Product    |XYZ    |White    |3CP           |3.29|2.93|2.46|2.04
18  |Emerald  XYZ Product |XYZ    |Emerald  |1CP           |2.74|2.43|2.14  |1.79
19  |Emerald  XYZ Product |XYZ    |Emerald  |2CP           |3.04|2.71|2.32  |1.91
20  |Emerald  XYZ Product |XYZ    |Emerald  |3CP           |3.34|2.98|2.51  |2.09

So basically, I can design the database as the same structure as the above data sample and dump all data into the table (a lot of repetition) or I can do something like this:

Product Table:
---------
--Product id
--Product name {ABC, XYZ, PQR...so on}

Colour Table:
-------
--Colour id
--Colour value {white, red, emerald, and so on}

Colour Print Table
--------
--Colour print id
--Colour print value {1CP, 2CP, 3CP}

Quantity Table
--------
--Quantity id
--Quantity value {1-149, 150-199, 200-249,250-499}

Price Table
-------
--Price id
--Product id
--Colour id
--Colour Print id
--Quantity id
--Price

So the price table will look something like this:

Price table
price id |  product id |colour id | quantity id | colourprint id |  price
1        | 1           |1         | 1           |1               |2.34
2        | 1           |1         | 2           |1               |2.07
3        | 1           |1         | 3           |1               |1.82
4        | 1           |1         | 4           |1               |1.51
5        | 1           |1         | 1           |2               |2.6
6        | 1           |1         | 2           |2               |2.31
7        | 1           |1         | 3           |2               |1.97
8        | 1           |1         | 4           |2               |1.62
9        | 1           |1         | 1           |3               |2.86

Price is displayed to the user when they choose the options based on type of product, colour, colour print and quantity.

New products might also be added in the future. I'm looking to design the database in the most efficient way.

I find the first option easier to implement but not sure if it's the most efficient or if it's even correct.

Your input would be appreciated. Thank you!


texts: how to resolve mssqlsystemresource.mdf corruption?

texts:
Desperate for help, but fearing the worst here :(

Something crashed SQLSERVER 2005 this afternoon approx 3pm. Since then I have been trying to get running again.

Original log file messages told that master log file was corrupt. I had no recent backup so used the following command;

  start /wait \setup.exe /qn INSTANCENAME= REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=

Which seems to get me further down the road to getting SQL server started, but I now get the following message in the log;

  2014-08-0818:35:14.96 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
  ...
  Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
  2014-08-0818:35:14.96 Server Registry startup parameters:
  2014-08-0818:35:14.96 Server -d R:\MSSQL.1\MSSQL\DATA\master.mdf
  2014-08-0818:35:14.96 Server -e R:\MSSQL.1\MSSQL\LOG\ERRORLOG
  2014-08-0818:35:14.96 Server -l R:\MSSQL.1\MSSQL\DATA\mastlog.ldf
  ...
  2014-08-0818:35:18.09 spid5s Starting up database 'mssqlsystemresource'.
  2014-08-0818:35:18.09 spid5s Error: 17207, Severity: 16, State: 1.
  2014-08-0818:35:18.09 spid5s FCB::Open: Operating system error 3(error not found) occurred while creating or opening file 'R:\aSQL\MSSQL.1\MSSQL\DATA\mssqlsystemresource.mdf'. Diagnose and correct the operating system error, and retry the operation.
  2014-08-0818:35:18.09 spid5s Error: 17204, Severity: 16, State: 1.
  2014-08-0818:35:18.09 spid5s FCB::Open failed: Could not open file R:\aSQL\MSSQL.1\MSSQL\DATA\mssqlsystemresource.mdf for file number 1. OS error: 3(error not found).
  2014-08-0818:35:18.10 spid5s Error: 5120, Severity: 16, State: 101.
  2014-08-0818:35:18.10 spid5s Unable to open the physical file "R:\aSQL\MSSQL.1\MSSQL\DATA\mssqlsystemresource.mdf". Operating system error 3: "3(error not found)".
  2014-08-0818:35:18.12 spid5s Error: 17207, Severity: 16, State: 1.
  2014-08-0818:35:18.12 spid5s FileMgr::StartLogFiles: Operating system error 2(error not found) occurred while creating or opening file 'R:\aSQL\MSSQL.1\MSSQL\DATA\mssqlsystemresource.ldf'. Diagnose and correct the operating system error, and retry the operation.
  2014-08-0818:35:18.12 spid5s File activation failure. The physical file name "R:\aSQL\MSSQL.1\MSSQL\DATA\mssqlsystemresource.ldf" may be incorrect.
  2014-08-0818:35:18.14 spid5s Error: 945, Severity: 14, State: 2.
  2014-08-0818:35:18.14 spid5s Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
  2014-08-0818:35:18.14 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

There is no indication in event log what the problem is. I tried copying an older version of mssqlsystemresource.mdf & ldf to no avail.

Any help greatly appreciated as I cannot find anything more on the net.


texts: 25.000+ rows - MySQL SELECT consecutive numbers performance issue

texts:
I have tried to be as explicit as I could. Below you have the query explained in details

What I want to obtain?
 - I want to SELECT start_number and end_number from a set of consecutive numbers from a table

ex:

    I have a column with values: 1,2,3,5,7,8,9,10
    If I run the SELECT on column will return: (1,3), (5), (7), (8,10)

    So my result will look something like :

    [
      'start_number' => 1,
      'end_number'  => 3
    ]
    .............
    .........

The problem is that the table contains a large number of rows (25.000+...for now) and it takes ages to execute (I have limited the number of results returned but still didn't fixed the execution time)

    SELECT
        l.control_series AS start_control_series,
        (SELECT
                MIN(a.control_series) AS id
            FROM
                cards AS a
                    LEFT OUTER JOIN
                cards AS b ON a.control_series = b.control_series - 1
            WHERE
                b.control_series IS NULL
                    AND a.control_series >= l.control_series) AS end_control_series
    FROM
        cards AS l
            LEFT OUTER JOIN
        cards AS r ON r.control_series = l.control_series - 1
    WHERE
        r.control_series IS NULL
    ORDER BY product_id ASC
    LIMIT 1;

Answer returned:

 [
  'start_control_series' => "110",
  'end_control_series'  => '114',
]

MySQLWorkbench response: 1 row(s)  returned 304.829 sec / 0.000 sec (as you can see it takes ages)

Table (cards) has 3 columns:

row_id (int primary autoincrement)
control_series (bigint)
product_id (int)

Running MySQL 5.1, on a shared host

I'm want to return 10 results (LIMIT 1,10) in a fair amount of time

Thanks in advanced, hopefully someone can help me.


texts: nvarchar(max) converting to varchar and table optimization

texts:
I am working with a table that has all character types set to nvarchar some of them are nvarchar(max). We are converting all these to varchar and specifying a character width based upon the actual usage in production. The production data uses a range of 2 characters up to 900 characters of actual used width for any given column. We are going to add padding of 10% when applicable.

-- Insert statements for procedure here
UPDATE Listings WITH (ROWLOCK)
SET [SubType] = 'S'
WHERE @idSettings = idSettings AND
    (@idRetsClass = 0 OR idRetsClass = @idRetsClass)
    AND (@idRetsSetting = 0 OR idRetsSetting = @idRetsSetting)
    AND IsNew = 1 AND ([SubType] LIKE '%Single Family Home%' OR [SubType] LIKE '%Modular%' OR [SubType] LIKE '%Mobile Home%'
    OR [SubType] LIKE '% Story%' OR [SubType] = '' OR [SubType] = 'residential - S' OR [SubType] = '1 House on Lot' OR [SubType] = '2 Houses on Lot'
    OR [SubType] = 'Detached' OR [SubType] LIKE '%single family%' OR [SubType] = 'ranch' OR [SubType] = 'Semi-Detached' OR [SubType] = 'single' OR [SubType] = 'one family' OR [SubType] = 'Residential'
    OR [SubType] = 'Ranch Type' OR [SubType] = '2 or More Stories' OR [SubType] = 'Cape Cod' OR [SubType] = 'Split Level' OR [SubType] = 'Bi-Level' OR [SubType] = 'Detached Single'
    OR [SubType] = 'Single-Family Homes' OR [SubType] = 'house' OR [SubType] = 'detached housing'  OR [SubType] = 'det')

A large overhaul of this table that consists literally 140 (nvarchar) columns, 11 being MAX. I am dropping 30 indexes and recreating them afterwards.

My question is in what situations is varchar(max) preferred?

Only when you expect to have 4k or more characters?

What should I learn and prepare for when doing this?

Will this improve performance when a clustered index update that affects the clustered key has to update the all non-clustered indexes?

We are having update procedures timing out that are using 75% to 95% of the query execution plan & displayed plan for an a clustered index update.

Link to the actual execution plan


texts: How do I update and insert specific set of rows to an existing table using Merge in SQL Server 2008 R2?

texts:
I have a table in the database that I need to update based on the records being passed to it. I am given a table-valued-parameter that has the same schema as the existing table (for all intensive purposes). The requirements are:

If the TVP (source) contains a record with existing ID in the existing table (target) update that record, if it is not marked as deleted in the existing table and its modified date is more recent.

Conditions: source.Id = target.Id AND target.IsDeleted = 0
         AND source.Modified > target.Modified

If the TVP contains a record for which the ID does not exist in the existing table, insert the record to the table.

I have the following set up so far.

EDITED: This setup works to give the desired end result. I, however, don't believe this is the best way of getting the intended result. There are two select statements that calculate the source table for the Merge statement. I would like to avoid having to filter out the source table in this manner.

My question is this: is there a way to achieve the same result as the query below but without having to use two select statements as is currently used in the Merge statement?

IF OBJECT_ID('tempdb..#target') IS NOT NULL DROP TABLE #target
IF OBJECT_ID('tempdb..#source') IS NOT NULL DROP TABLE #source

CREATE TABLE #target  ( Id int, Modified int, IsDeleted bit, NoteText varchar(500) )
INSERT #target VALUES (1, 1, 0, '')
INSERT #target VALUES (2, 1, 1, '')
INSERT #target VALUES (3, 3, 0, '')

CREATE TABLE #source ( Id int, Modified int, NoteText varchar(500) )
INSERT #source VALUES (1, 2, 'Modified')
INSERT #source VALUES (2, 2, 'Modified')
INSERT #source VALUES (3, 2, 'Modified')
INSERT #source VALUES (4, 2, 'Modified')

-- ** NEED HELP WITH OPTIMIZATION HERE **
merge #target as t
using (select * from #source s where s.id not in (select id from #target where IsDeleted = 1 OR Modified > s.Modified)) as source
on t.id=source.id
when matched then update set Modified=source.Modified, NoteText = source.NoteText
when not matched then insert values (id, Modified, 0 ,NoteText) ;

SELECT * FROM #target;

-- Expected records in #target after operations
-- 1,2,0,'Modified'  ** UPDATED **
-- 2,1,1,''          ** NOT UPDATED BECAUSE ISDELETED = 1 **
-- 3,3,0,''          ** NOT UPDATED BECAUSE TARGET.MODIFIED > SOURCE.MODIFIED **
-- 4,2,0,'Modified'  ** INSERTED **

drop table #target;
drop table #source


texts: Is there a situation where MySQL creates a nonclustered index automatically?

texts:
MySQL creates a clustered index automatically when I create a primary key.

But is there a situation where MySQL creates a nonclustered index automatically?


texts:
At work we have 11g and the boss told me to make passwords case sensitive, but that's where I'm going, he doesn't want to set the SEC_CASE_SENSITIVE_LOGON to true in the production environment and he told me to find a way to make it through the profiles of the Oracle Database System or another way.

Is there actually a way to make case sensitive passwords through profile settings or another way?

What i believe is that if you don't switch the parameter of SEC_CASE_SENSITIVE_LOGON there is no way the database would let you make them, since this parameter is from the database system itself.

Also from what I've been searching the PROFILE doesn't have any of these functions.


texts: How to optimize query based on explain analyze in PostgreSQL?

texts:
I am running the following query via EXPLAIN ANALYZE but it is taking a bit of a long time to run. Based on the output of EXPLAIN ANALYZE, what are some things I can do to my table to make this query execute faster?

select count(amount), sum(amount)
from mytable
where color_code = 5 and shade_type = 'Light';

EXPLAIN ANALYZE output:

 Finalize Aggregate  (cost=946640.31..946640.32 rows=1 width=40) (actual time=4799.256..4799.257 rows=1 loops=1)
   ->  Gather  (cost=946640.09..946640.30 rows=2 width=40) (actual time=4799.191..4800.566 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=945640.09..945640.10 rows=1 width=40) (actual time=4797.002..4797.003 rows=1 loops=3)
               ->  Parallel Seq Scan on mytable  (cost=0.00..945573.39 rows=13338 width=6) (actual time=656.722..4791.404 rows=10103 loops=3)
                     Filter: ((color_code = 5) AND ((shade_type)::text = 'Light'::text))
                     Rows Removed by Filter: 4888180
 Planning time: 0.257 ms
 Execution time: 4800.661 ms
(10 rows)


texts: MERGE with OUTPUT doesn't seem to be doing the right thing

texts:
I'm adding a foreign key to a table, and removing any rows that violate the FK, copying them into a ModifiedTable_invalid table. As part of the script, I've got the following MERGE command:

MERGE ModifiedTable t1
USING TargetTable tt
ON t1.JoinColumn = tt.JoinColumn
WHEN MATCHED THEN
    UPDATE SET t1.FkColumn = tt.FkSource
WHEN NOT MATCHED BY SOURCE THEN DELETE
OUTPUT DELETED.* INTO ModifiedTable_invalid;

However, this command seems to be inserting EVERY row from ModifiedTable into ModifiedTable_invalid, not just those deleted by the MERGE command. What's going on, and how do I get it to only put the deleted rows in ModifiedTable_invalid?


texts: When should startup parameter -E be used?

texts:
We are starting to build a data warehouse system using SQL Server 2008 R2. One of the senior DBAs told us to consider using a startup parameter -E for the SQL Server service. I read from Books Online that:

  -E: Increases the number of extents that are allocated for each file in a filegroup. This option may be helpful for data warehouse applications that have a limited number of users running index or data scans. It should not be used in other applications because it might adversely affect performance. This option is not supported in 32-bit releases of SQL Server.

My question is how does this parameter help index and data scans? Should we use this parameter if there is a possibility that there are other databases (possibly in operative role) in the same instance?


texts:
I found many question, But no solutions.

An index can not be used to speed up queries where the search criteria starts with a wildcard:

LIKE '%text%'

An index can (and might be, depending on selectivity) used for search terms of the form:

LIKE 'text%'

So this means I simply cant use a full wildcard in a big table?


texts: Postgres: Dictionary to remove leading zeroes from alpha-numerical words

texts:
I am running a full text search in Postgres 10 over texts which include a variety of alphanumerical IDs (as well as 'real' text).
My clients want to be able to find those IDs without the need to enter the leading zeroes. As the number of digits vary for different IDs I do not want to go down the path of adding extra zero-padded search queries.

I was wondering if there is a way to remove leading zeroes using a dictionary. But this would require some wildcards to catch an alphanumeric expression with leading zeroes. My understanding so far is that this is not supported by the ispell/hunspell dictionaries supported by Postgres 10.

I am currently thinking about using regexes like /\W[0]+(:alnum:)+/ and adding the matches to the text before feeding the text to the search index. This way I would catch both the zero-padded original and the variant without leading zeroes. But it does feel a little clumsy...

Does anybody have a better idea on how to do this using a full text search dictionary (or something similar)?

The texts I am search look like this:

'Max Mustermann 00023131021 Wunderstr. 17 Trallerhausen 002ed2323'

Example - they are typically much longer in reality and contain up to 50 different IDs.
Here, I would like to hit on '00023131021', '23131021', '2ed2323 & Wunder:*', etc.


texts: Is our use case suitable for a NoSQL Database solution?

texts:
We are currently running a Python web app querying a SQL MariaDB (through SQLAlchemy). Unfortunately we have run into some hard limitations of MariaDB (number of joins are limited to 61) and are looking for alternatives. Our database is not very large (generally under 2GB and growing slowly) and the entries could actually very well be broken down to simple json objects, which made us look at a NoSQL solution.

Initially this seemed like a great solution. Queries were super fast (as the DB fit into RAM) and we were happy (for a few days).

I came here to seek advice if the NoSQL DB is a recommended solution for our needs, though. So let's talk about the data:

The nature of our data is that multiple group of entries / rows generally reference back to the same object, pretty much as if you had multiple versions that relate to a single object.

Assume a document entry could look like this:

_id: 5a31...
description: Object
    location: "XYZ"
    name: "ABC"
    status: "A"
    m_nr: null
    k_nr: null
    city: "QWE"
    high_value: 17
    right_value: 71
more_data: Object
    number: 101
    interval: 1
    next_date: "2016-01-16T00:00:00Z"
    last_date: null
    status: null
classification: Object
    priority_value: "?"
    redundancy_value: "?"
    active_value: "0"

Imagine the description.location will often need to be grouped and sorted so that I can only display the $last entry for each of these grouped entries. A common query may look like this (in MongoDB):

db.getCollection('a').aggregate(
    [{ $sort:
        {"description.location": 1}
     },
     { $group:
        {_id: "$description.location"}
     }]
)

I have unfortunately found that this particular query takes very long, when the DB does not fit into memory - even with an index for description.location present in the MongoDB. (For some reason the $group aggregation operation never appears to be using the available index, while $sort actually does use it).

Either way, is this data / layout / query strategy something that resonates well with a NoSQL DB ?


texts: Unable to connect to database engine using built-in administrator account

texts:
I am trying to connect to our SQL server 2012 database engine using SSMS but it fails with error 18456. I am logged in as the built-in administrator account on the windows server where the DB is installed and the database engine is configured to use Windows authentication. Details of the error in SQL server logs are as follows:

2016-08-30 11:04:01.290 Logon        Error: 18456, Severity: 14, State: 5.

2016-08-30 11:04:01.290 Logon        Login failed for user 'SERVERNAME\Administrator'. Reason: Could not find a login matching the name provided. [CLIENT: ]

I will appreciate any assistance resolving this problem.


texts: Sql server/azure connection string parameter for readonly access?

texts:
Is there a specific parameter that can be used in a SQL server/azure connection string to specify that the connection should be readonly?

In other words, only SELECT queries should work but INSERT/UPDATE/DELETE and DDL statements should return an error.

I'm aware of the ApplicationIntent=ReadOnly parameter, but my understanding is that its purpose is to connect to read replicas.
In my case, the target server might not have any read replica. In this case, would ApplicationIntent=ReadOnly be enough to make the conneciton read only even though it connects to the primary?


texts: How to execute this procedure in PL/SQL?

texts:
I have this table in the below format:

Persnbr | Userfieldcd | Value
01      | Port | Funds
01      | Vip1 | Systems
02      | Port | Bank
02      | Vip1 | Authority

This is how I want it:

Persnbr | Port  | Vip1
01      | Funds | Systems
02      | Bank  | Authority

As I dont know the all the fields in the userfieldcd column, I am trying to dynamically pivot the table. So I am using this procedure but I dont know how to call it in PL/SQL developer. I am using Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

CREATE OR REPLACE procedure dynamic_pivot_po(p_cursor in out sys_refcursor)
as
sql_query varchar2(1000) := 'select persnbr ';

begin
    for x in (select distinct userfieldcd from persuserfield order by 1)
    loop
        sql_query := sql_query ||
            ' , min(case when userfieldcd = '''||x.userfieldcd||''' then value else null end) as '||x.userfieldcd;

            dbms_output.put_line(sql_query);
    end loop;

    sql_query := sql_query || ' from persuserfield group by persnbr order by persnbr';
    dbms_output.put_line(sql_query);

    open p_cursor for sql_query;
end;
/

When I call the procedure using:

VARIABLE x REFCURSOR
BEGIN
       dynamic_pivot_po(:x)
    END
    /

it gives me

  ORA-00900: Invalid SQL statement.


texts:
MySQL database, I have a table which contains some column. First column is datatime type, like "2016-03-01 08:00:00", "2016-03-02 08:00:00".

What should i do to get everyday's 8 AM. from this table?


texts:
Not sure if/how it is intended to be done but i am trying to match up all the active document in my database with the author of it:

function(doc) {
    if (doc.data.active && doc.data.active == '1') {
        emit(doc.data._account.data.name, doc._id);
    }
}

the _account field in the db is a reference to a document with all the information about the author, including a name field but because this is a reference I cannot simply "go down".

I tried to look up the docs but kind of hard to find relevant documentation because reference match up to Reference documentation most of the time.


texts: Get table Data from single table but from different paths

texts:
A greet to all Experts here at StackExchange.

The problem I have faced seems to be a simple one, it's just my low knowledge of SQL that unables me from doing it.

I have a Table called 'ApplicationPlans' with data about plans in each application of mine.
This table is connedcted to the 'Applications' table with ApplicationId as foreign key.
The 'Applications' is connected to the 'UserRoles' table once directly and once through the 'Clients' table. Allowing us to set access levels on the application level and/or client access level.

The figure can be seen

My goal is to fetch data from 'ApplicationPlans' table for a user according to the roles he has.
Since this data is going to be in a grid, it's better that I dont use a UNION of two select queries like image

of course that is my idea, you might say it works with same performance and I will do it.

So that was my situation,
Any suggestions is appreciated

For sake of clearification I must add,
I need the those data from 'Applicationplans' which my user have access to. This is determined by first finding roles for this user and then in two phases:

Find clients that this user have access to, then find their applications and then find their ApplicationPlans.
Find Applications that this user have direct access to, then find ApplicationPlans that they have.

The result intended to be a union of these two sets together.


texts: Multiple subqueries in columns with joins to return multiple rows

texts:
I have a table which stores id(primary key) of users and not their names (Names are stored in another master table) and also the the department id of user and not the name of the departments and there is one more column joining year. Now I want to return the data like Name(actual name of user), Department(actual name of the department) and year by using subqueries. I tried but I am not getting any results.

main table
--------------------------
| user | department| year|
--------------------------
| 1    |  1        | 1992|
--------------------------
|  2   | 12        | 2007|
--------------------------
users table
--------------------
| pkey | name      |
--------------------
| 1    |  someone  |
--------------------
|  2   |  someName |
--------------------
department table
--------------------
| pkey | name      |
--------------------
| 1    |  Dep1     |
--------------------
|  12  |  Dep2     |
--------------------
expected result from query for main table with subquery for user and department by doing inner joins
--------------------------
| user     | department| year|
--------------------------
| someone  |  Dep1     | 1992|
--------------------------
| someName |  Dep2     | 2007|
--------------------------


texts: Does local.oplog.rs hold all data in mongodb replication?

texts: I'm trying to do backup full and incremental backup, I wonder does local.oplog.rs collecitons contain all info that I need to do backup and incremental backup?


texts: Question on Oracle OEM version and plugin version upgrade

texts:
I have a OEM 12c (12.1.0.4) running in my setup in which I have registered over 70-80 databases. I see the plugin updates with versions of each target (Oracle database, MS-SQL, MySQL) and I have the downloaded the plugin updates as well.

My question is that can I update my plugin which is higher version than my OEM itself?
If yes, should I update my plugin one version at a time? Say, my deployed oracle database plugin version is 12.1.0.4 and available downloaded versions for the same I have is 12.1.0.6 and 12.1.0.7. So I should update the 6 release first and then the 7? Also how to go ahead with the update?
Are there upgrades for the OEM itself? from 12.1.0.4 to a higher version? If yes, how to go ahead with it? Are there any MOS if you could share?

Thanks!!


texts: Zabbix partitioning autovacuum causes locking

texts:
I have a Zabbix database on a postgresql backend that grew to ~1TB of disk space. The Zabbix housekeeper (a process for removing old data from the tables) was running for over 18 hours so I used this script to enable partitioning.

But then - exactly at midnight - Zabbix stopped inserting data into the database, right when a new partitioned table should get created.

I found out that when I kill the running autovacuum process (mostly on the table history or history_uint) via pg_cancel_backend() the Zabbix server can insert again as some locks go away 1.

I understand that the vacuum process is needed in order to free up transaction ID's so I should NOT do this every day.

Is partitioning a bad idea? (at least in an already running setup?)
Should I have started with partitioned tables all along?
Should I throw out all our monitoring data, and insert it again into correctly partitioned tables? (a huge amount of work...)
Should I disable autovacuum on those partitioned tables? (assuming that the issue comes from a global lock on all schemas .public and .partitions)

The final goal is to reduce the load on the database and improve performance.

By enabling partitioning I was able to reduce the runtime of the housekeeper down to a few minutes/seconds via disabling the housekeeping of history and trend_data. But it's no positive result if the monitoring stops working after midnight...

Additional Information:

The Hardware is quite oversized (all SSD and performing only OK-ish). I guess because I don't really know what knobs to tweak I better not touch them at all.

1: Unfortunately, I only figured this out 7 Hours later, in the morning. Now I have 2 days of missing monitoring data from 00:00 - 07:00


texts: Copy result of a query to a new PostgreSQL database

texts:
I have a Postgres database A with 2 tables,  and I need to copy part of these tables (recent records in 1 month) to the new database with the same structures.

How to achieve this, as I know cross database references are not implemented. So I can't select records from the old database to copy to the new one.

I can code, so any ideas are welcome.


texts: Impact of using mirroring to migrate between SQL Server 2005 and SQL Server 2008 R2

texts:
I've got a DB which sits on a machine (SQL Server 2005) due for replacement very soon.

I'm planning on having the database mirrored to the replacement machine (SQL Server 2008 R2)  so that when the time comes to switch everything over, I can fail it over and quickly get things back up and running on the new machine.

If I then mirror the DB on the replacement machine (now the principal) to ANOTHER machine (SQL Server 2008 R2), will there be any impact or issues which could appear, which wouldn't if I'd have backed up and restored cleanly onto the new server?

(Assume that I've copied all the users/configurations/jobs etc over to the replacement machine, and that once the replacement machine is in use, the SQL 2005 machine will be removed  so it won't be failed back to)


texts: Filtered Stats and Incremental Stats on Partitioned table

texts:
We have just implemented table partition on a very large table in SQL Server 2014 , we used have filtered stats on the same table before partition not after the table partition we enabled Incremental Statistics but we are thinking to create filtered stats which would allow for a much more granular level of statistics....

At this point, i'm not sure if a partitioned table can have both Filtered stats and Incremental Stats and if we implement would it confuse the optimizer ? Also , can anyone help me on how to implement filtered stats on a partitioned table ?

Thanks in advance...


texts: SSRS report does not work with multiple parameters

texts:
My data set has the following where clause:

WHERE a11.PDPOID LIKE '%' + @POID + '%'

My parameter @POID properties are set to single value.  The report works, but if I change the parameter properties to allow multiple values then I get an error:

  Query execution failed for dataset 'DataSetDtRcvd'. incorrect syntax near ','.


texts:
How can I find in a column filled with numbers, the ones that are not used.

Table_A:

'id' int(11) unsigned NOT NULL AUTO_INCREMENT,
'client_code' int(4) unsigned NOT NULL,
'client_name' varchar(50) NOT NULL,
PRIMARY KEY ('id')

INSERT INTO Table_A ('client_code','client_name') VALUES (1,'Bob');
INSERT INTO Table_A ('client_code','client_name') VALUES (2,'Anna');
INSERT INTO Table_A ('client_code','client_name') VALUES (5,'Jim');
INSERT INTO Table_A ('client_code','client_name') VALUES (6,'Tom');
INSERT INTO Table_A ('client_code','client_name') VALUES (10,'Mattew');

If I SELECT 'client_code' FROM Table_A the output will be 1,2,5,6,10. How can I have the numbers that are not used?

(3,4,7,8,9,11,12...)


texts: Why isn't Postgres doing an index-only scan?

texts:
I am using Postgres 9.4. This is my table:

 id                | integer                 | not null default nextval('frontend_prescription_id_seq'::regclass)
 presentation_code | character varying(15)   | not null
 total_items       | integer                 | not null
 actual_cost       | double precision        | not null
 quantity          | double precision        | not null
 processing_date   | date                    | not null
 pct_id            | character varying(3)    | not null
Indexes:
    "frontend_prescription_pkey" PRIMARY KEY, btree (id)
    "frontend_prescription_5283da53" btree (pct_id)
    "frontend_prescription_528f368c" btree (processing_date)
    "frontend_prescription_cost_by_pres_code" btree (presentation_code, pct_id, actual_cost)

There are about 450m rows in the table. I have just run VACUUM ANALYZE, so the query planner should be up to date.

Here is my query:

EXPLAIN (ANALYZE, BUFFERS)
  SELECT SUM(actual_cost) AS cost,
    pct_id AS id, processing_date
  FROM frontend_prescription
  WHERE presentation_code='0501013B0AAAAAA'
  GROUP BY pct_id, processing_date;

And here is the output:

 HashAggregate  (cost=1537066.69..1537066.87 rows=18 width=16) (actual time=189604.446..189608.532 rows=9725 loops=1)
   Group Key: pct_id, processing_date
   Buffers: shared hit=17 read=418783
   ->  Bitmap Heap Scan on frontend_prescription  (cost=14473.73..1533678.60 rows=451746 width=16) (actual time=543.825..187857.510 rows=416328 loops=1)
         Recheck Cond: ((presentation_code)::text = '0501013B0AAAAAA'::text)
         Heap Blocks: exact=416287
         Buffers: shared hit=17 read=418783
         ->  Bitmap Index Scan on frontend_prescription_cost_by_pres_code  (cost=0.00..14360.79 rows=451746 width=0) (actual time=317.102..317.102 rows=416328 loops=1)
               Index Cond: ((presentation_code)::text = '0501013B0AAAAAA'::text)
               Buffers: shared hit=2 read=2511
 Planning time: 0.329 ms
 Execution time: 189610.440 ms

Why does Postgres need to do a heap scan? Isn't all the information needed for the query in the composite frontend_prescription_cost_by_pres_code index?

Readable version of the output: http://explain.depesz.com/s/oV4


texts: De-duplication method for fields that are identical except for Unique ID

texts:
I'm looking for a decent de-duplication method. Basically I have a table which has five fields plus a Unique ID (key) field. I've run a duplicate which detection query which finds any records where ALL fields match except the unique ID, which gives me this:

So the first two records are identical apart from the unique ID (end column), and the next two are identical, etc. I have a few hundred of these so rather than just clicking and deleting, what would be the quickest way of just deleting ONE of these two records? I've tried a couple of append queries, such as

INSERT INTO tblDupScanLines ( ScanTime, ScannerNo, ScanDate, EventName, PID, )
SELECT DISTINCT qryDupScanLines.ScanTime, qryDupScanLines.ScannerNo, qryDupScanLines.ScanDate, qryDupScanLines.EventName, qryDupScanLines.PID
FROM qryDupScanLines;

But I get stuck because I then don't have anything to dedupe them against on the main table (I've done something similar before but using MinOfID to delete whichever one had the lowest ID number) but trying to import the unique ID column as well would import ALL records and not the DISTINCT ones.

Can anyone help?


texts: What are the sp_send_dbmail return values?

texts:
I have an email failing with function return value of '101' and @@error value of '0' (no rows are added to sysmail_allitems).

Where can I find documentation on return codes for this function?

Example code showing how I obtain the above values:

exec @result = msdb.dbo.sp_send_dbmail
    @profile_name = 'OBFUSCATED',
    @recipients = @DL,
    @subject = 'OBFUSCATED',
    @body = @emailBody,
    @body_format='html',
    @query = @reportQuery,
    @exclude_query_output = 1,
    @attach_query_result_as_file = 1,
    @query_attachment_filename = @filename,
    @query_result_separator = @temp,
    @query_result_header = 1,
    @mailitem_id = @mailitem_id
    ;

    set @temp = @@ERROR;


texts:
We plan to incorporate Log Shipping for basic disaster recovery with reporting needs. Server A Source Database will log ship to Server B database. We will conduct log shipping every few hours.

Looking at setup, there is an option for "Use a Monitor Server Instance". Currently we do not have budget/time resources to setup third monitor server.

In that case, what is the second best recommendation for Monitor Server to store status/history of log shipping activities? On the source primary server or destination server ?

We are mildly concerned about performance/activity of Source Server. Sometimes CPU can get moderate high. So I think Destination Server is better option. Also, in case primary server fails, we have status/history of when last time transaction logs backup/shipped/restored. If monitor was on Primary server failed, we would not know this.

I am trying to research if there is any missing reason to place monitoring on Source Server.


texts: Is it possible to reduce the duplication in this simple query?

texts:
I'm new to SQL and so this is probably a stupid question but can I reduce the duplication in this query? My actual query involves more complex expressions. Right now I'm just doing string concatenation from the language I'm using to generate the long query but I think there must be a way to do this with plain SQL?
select
  x + y as a,
  x + z as b,
  (x + y) / (x + z) as c
from
  integers
order by
  z + (x + y) / (x + z) desc
limit
  10;

What I'd like:
select
  x + y as a,
  x + z as b,
  a / b as c
from
  integers
order by
  z + c desc
limit
  10;

-- Fails with: Query Error: error: column "a" does not exist

Sample table:
create table integers as
select x, y, z
from
  generate_series(1, 10) x,
  generate_series(x, 10) y,
  generate_series(y, 10) z;

Fiddle: https://www.db-fiddle.com/f/u3cVh7QsafWNE6ELWhvBc1/0


texts: How can clone database in mariadb prompt character?

texts:
mysqlword="wwww"
old="xxxx"
new="yyyy"
mysqldump $old  -u root -p$mysqlword   | mysql $new  -u root -p$mysqlword

Now new database was cloned as like old database.
Can i use some command to clone database when entering mariadb terminal?

mysql -u root -pwwww
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.56-MariaDB MariaDB Server
MariaDB [(none)]>

How can clone database in mariadb prompt character instead of inputing mysqldump $old  -u root -p$mysqlword   | mysql $new  -u root -p$mysqlword in bash terminal?


texts: Can we apply database encryption in mongodb community edition?

texts:
I know there's features in mongodb Entreprise edition that allow encryption. I'm using mongodb community edition for personal project and i want to keep my database safe and encrypted.
Is there any way to encrypt my database within this community edition? Thank you


texts: Best configuration for MySQL? InnoDB and MyISAM

texts:
I read that InnoDB is supposed to be better at writing into MySQL than MyISAM, however, I'm trying it out, by running 3000 MySQL queries(Inserting rows, 8 Columns + id)... but results haven't turned out how I'd hoped.
Using InnoDB the execution time is around 5.3 Seconds while for MyISAM it takes around 2.2 Seconds.

My current mysql configuration is:

innodb_io_capacity = 8000
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_log_buffer_size = 32M
innodb_log_file_size = 564M
innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances = 6
innodb_thread_concurrency = 0

Everything else is at its default value.
My server is currently running: Ubuntu 14.04 x64
Has 12GB RAM
6 Cores
And using an SSD (service provider says it provides 10,000 IOPS)

What would be the best configuration for InnoDB if I were to only use InnoDB, and what would be the best configuration for just MyISAM (like would it for some reason be faster if I were to disable InnoDB while not using it.)?

Right now just thinking of using my server for storing stuff, and will need to write more data, than reading it... while later on, thinking of reading more than writing.
Not sure if it matters, but right now running nginx (do not have apache on the server), php 5.6 and MySQL 5.6.19.


texts:
I'm doing MYSQL Replication for the first time.

Master mysql version: mysql  Ver 14.14 Distrib 5.1.52, for pc-linux-gnu (i686) using readline 5.1

Slave Version: mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

I already make a connection of master to slave but when an insert query execute, it shows Error 'Unknown Collation: '2048' on query. Default database is 'master'.

Already check for answers in google but no solution was made for 2048.
What should I do to fix it?

error query:  Error 'Unknown collation: '2048'' on query. Default database: 'master'. Query: 'INSERT INTO DOCTORS_LOG(cno,empno,name,dept,sex,age,civilstatus,empstatus,datehired,complaints,diagnosis,dte,personnel) values (cno,'69408','JEFFREY MONTERON','PRODUCTION/MACHINERY ENGINEERING','MALE','20','SINGLE','Contractual','2017-09-18','cold x 2 days
','A> AURTI
P> dynatussin 1 cap PO tid for 3-5 days',(Select sysdate()),'Dra. Joyce Guanin')'
1 row in set (0.00 sec)


texts:
I am brand new to OPENXML, and have 90 % of what I want to do working...stuck on this:

<ReportHost name="411.114.42.42">
  <HostProperties>
    <tag name="HOST_END">Wed Nov 11 20:48:43 2015</tag>
    <tag name="LastAuthenticatedResults">8675309</tag>
    <tag name="Credentialed_Scan">true</tag>
    <tag name="policy-used">AWT v6</tag>
    <tag name="ssh-auth-meth">MyPassword</tag>
    <tag name="ssh-login-used">MyLogin</tag>
    <tag name="local-checks-proto">ssh</tag>
    <tag name="host-ip">411.114.42.42</tag>
    <tag name="HOST_START">Wed Nov 11 20:00:01 2015</tag>
  </HostProperties>
</ReportHost>

My Desired results:

Wed Nov 11 20:48:43 2015
8675309
TRUE
AWT v6
MyPassword
MyLogin
ssh
411.114.42.42
Wed Nov 11 20:00:01 2015

I am able to get the name value ("Host_end", etc.), but not the content.

My query:

Select   *
from OPENXML (@hdoc,'/ReportHost/HostProperties/tag',1)
WITH (
    name varchar (500)
     )

I'd like to stick with OPENXML if at all possible, since I just spent the day learning it and getting to this final step.

Please let me know if I have provided enough info.


texts:
I had tried to start the MongoDB service using the following command in linux machine.

service mongod restart

I am getting the following error

Stopping mongod:                                           [FAILED]
Starting mongod: about to fork child process, waiting until server is ready for connections.
forked process: 1428
all output going to: /data/log/mongod.log
child process started successfully, parent exiting

I am having the config file as follows

dbpath = /data/db/
logpath = /data/log/mongod.log
logappend = true
fork = true
port = 27017
pidfilepath = /var/run/mongod.pid
rest = true
maxConns = 12000
oplogSize = 10000
directoryperdb = true
keyFile = /etc/mongod.key
master = true
smallfiles = true

Note

 service mongod status
 mongod dead but subsys locked

Any suggestion will be helpful.


texts: New SQL Server 2008 instance, load old instance DB's

texts:
BACK STORY:
I'm a developer with a local instance of SQL Server 2008 on my workstation. I just recently upgraded my motherboard and found out (much to my dismay) the old motherboard's RAID drivers apparently are not compatible with the new mother board so essentially, I'm unable to use the Windows instance I had with the old motherboard. Luckily I have a full backup of all it's contents.

My problem:
I want to install SQL Server 2008 on my new box but I want to load the old data files. I have quite a few databases I don't want to recreate. I have all the original MDF's and LDB's but no actual SQL Server "backups".

Is there a way I can do this?

Thanks for your help.


texts:
I have multiple database sessions from which one has been blocking others because of a frozen application. There are transactions running on multiple sessions. I need to kill the head blocker because of Lock request timeouts on other sessions. After killing that session other user have reported data loss. I am pretty sure they have committed their transactions but they still seem to have been rolled back. They can even proof it because some documents have been printed which are missing data afterwards.

Is it possible that committed transactions are being rolled back because of a rollback on another session? I would think not, but after reading the COMMIT documentation I have doubts. It says:

  If @@TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements
  @@TRANCOUNT only by 1 and the transaction stays active.

Reading the TRANCOUNT documentation it says:

  Returns the number of BEGIN TRANSACTION statements that have occurred
  on the current connection.

So is the current connection the same thing as a session or can multiple sessions share the same connection (maybe by pooling)? And if they can share, how does this relate to transactions? Can it be that these active transactions are being rolled back later on and even after they have been committed?

[edit] To be more clear, some users didn't get a Lock timeout because their data didn't conflict with the head blocker. They committed transactions but those transactions seemed to have been rolled back after killing the head blocker.

[Update 2018-03-29] After a new incident I had the opportunity to investigate the problem while it happened. The transaction logs concluded that the problem was a nested transaction that never committed. The application that caused the problem wasn't frozen so the user never noticed it until he closed the application. At that point he lost his data because the transaction was rolled back. I accepted the best answer possible and that was the one telling me it wasn't possible to roll back a committed transaction. I guess it really isn't possible, it's just hard to find the real problem.


texts: How to get the average value of each second? MySQL

texts:
I wonder how to output the average data of each second.
My data set is in millisecond and I can out them in millisecond by

SELECT * FROM some_table
where RealTime > '2019-09-10 23:00:00' LIMIT 200;

Below is the Data Format

AcX       RealTime
-15836   2019-09-05 15:02:37.502
-16666   2019-09-05 15:02:37.508
-17645   2019-09-05 15:02:37.512
...

But how can I get the average data in each second? Thx


texts:
I've been reading Snodgrass's "Developing Time-Oriented Database Applications in SQL" and I'm consulting other documents as well, but I'm willing to find more modern stuff about time-oriented design for databases and the tools available for that in Postgresql.

Also found this document.

I would be grateful just with any link or advice on the topic discussing:

Temporal and bitemporal tables
Coalescing
Validity, applicability time, transaction time
Primary keys, constraints and referential integrity in temporal DBs
Design and good design practices for temporal DBs
Native Postgresql solutions for all of the above

I have still more to read from the book and I've discovered some SQL:2011 issues about time, but they'are not implemented in Postgresql.

There's still some things I don't know how to handle or understand, for example, I found this article mentioning this:

CREATE TABLE travel_log (
 id serial PRIMARY KEY,
 name varchar(255),
 travel_range daterange,
 EXCLUDE USING gist (travel_range WITH &&)
);

which I understand it could be useful for not having overlapping rows, but even though I searched the Postgresql docs, I only read what the EXCLUDE does and that gist is just some type of INDEX, but I can't figure out which kind of syntax is the "WITH &&" thing and what does it provide.

I would be happy just having resources where I could learn about all this. Thank you very much.


texts: views restored from backup in a different server referring to linked servers

texts:
I have 3 SQL servers X, Y, Z and 2 databases X.A and Y.B.
There are (many) view in X.A that uses 4-level naming in their definition:

SELECT ... FROM Y.B.SCHEMA.TABLE

From this I infer that X contains a linked server definition for Y.
I am restoring X.A and Y.Bfrom backup files into server Z, so I now have
Z.A and Z.B available in the same server.
But these views obviously fail in Z due to not knowing what Y is.

I would like these views to work in the restored database Z.A, in such a way that they get the data from Z.B (and not from Y.B), without having to change the views (there are many such views). I am not sure if this is possible. I am able to do changes on server Z as needed, but I do not have any access to X and Y. The users of the view also only have access to Z.

I thought about defining a synonym Y in Z to point to localhost but it seems I need to refer at least to a schema or schema.table when creating a new synonym. Or can I have a synonym of just the server name?

I also thought about defining a linked server named Y in Z, but pointing to itself. Here I was only able to make a linked server named localhost pointing to itself, and this does not help solve my problem.

Is this at all possible? Can I convince SQL server to use Z when Y is referred to in the view definition?


texts: how to shrink(and also reduce size on disk) a very big log file in SQL server 2016

texts:
I have a datawarehouse with database log of 800 GB.now I want to shrink it or reduce the disk space occupied by Log.ldf file. I tried to shrink file in several ways - took full backup , performned transaction log back up, modified recovery mode from Full to Simple, executed dbcc command however none of them seems to help in reducing log size at disk.

I also detached database, deleted log file but due to memory-optimize file container I got error while I was trying to attach it again (I had read SQL server will automatically add a log file but seems it doesn't work when database has a memory_optimize file).

After all the above attempts database log file is still 800 GB and I am not really sure how to clean up space consumed by database log. Appreciate if I could get some expert opinion on this issue or guide me in case I am missing something.


texts: Validate XML using XSD inside TSQL function in SQL Server 2012?

texts:
I need to validate XML using XSD inside TSQL. To make this task semi automated I need to do this inside TSQL function.

To make validation I have to set value to XML and if it is not valid I will receive an error.

My problem is that inside the TSQL function I can not use TRY / CATCH. Is this the propper way and if it is even possible to do this?


texts: Sql logshipping Last_restored,last_copied file null in sysjobhistory

texts:
I have started log shipping for few dbs from production to DR recently, but it seems like the jobs just keep running and sometimes the copy job copies few files, sometimes it says the files already exists, the restore job sometimes says skipping backup files and sometimes restores the files , i feel it has something to do with "last_restored_file from msdb.dbo.log_shipping_monitor_secondary " being NULL, i am not sure why its not being populated by the monitor server.

Any ideas?


texts: Need to divide single column into multiple columns

texts:
My table has data with a single column:

1
2
3
4
5
6
7
8
9
10

And I want output like this:

1 2 3 4 5-first row
6 7 8 9 10 - second row

Another has two columns like this:

Column 1           Column 2
1                  a
2                  b
3                  c
4                  d
5                  e
6                  f
7                  g
8                  h
9                  i
10                 j

And I want it to display like this:

1 a 2 b 3 c 4 d 5 e-first row
6 f 7 g 8 h 9 i 10 j - second row


texts: Select rows based on a minimum in one column

texts:
Working with PostgreSQL 9.4, I want a result that takes into consideration only min() (all aggregate functions) values of distance on JOIN or WHERE. But its seems those things are not allowed. So, I am selecting the distance between two points and I want to filter it just to take the min() values into consideration.

To resume the question clearly, suppose these rows:

id; gid; distance; time_interval
142; 028; 62; "21:46:00"
200; 028; 53; "08:20:11"
128; 034; 92; "09:24:43"
179; 034; 70; "08:09:34"
194; 034; 92; "05:31:05"
199; 034; 88; "07:15:48"
200; 034; 61; "14:13:43"
202; 035; 24; "17:32:34"
200; 036; 76; "06:02:11"
154; 037; 97; "12:58:58"
154; 040; 30; "11:34:10"
132; 042; 80; "07:01:12"
142; 042; 67; "19:30:21"

How can I extract id, gid, distance and the time_interval that consider just the min(time_interval) values correspondent to that gid. The problem is that I just can see this with this query:

SELECT id, gid, distance , min(time_interval)
FROM dis
WHERE time_interval = min(time_interval)
GROUP BY id, gid, distance

The result should be:

id; gid; distance; time_interval
200; 028; 53; "08:20:11"
194; 034; 92; "05:31:05"
202; 035; 24; "17:32:34"
200; 036; 76; "06:02:11"
154; 037; 97; "12:58:58"
154; 040; 30; "11:34:10"
132; 042; 80; "07:01:12"


texts: Mongodb crash after multiple query attempts

texts:
Every once in a while our mongo service crashes () after displaying log of multiple messages like this:

Sun Apr  6 01:11:38.648 [conn2149] getmore prod_db.tweets query: { query: {}, $snapshot: true } cursorid:2408233643061247141 ntoreturn:0 exhaust:1 keyUpdates:0 numYields: 25 lock
s(micros) r:120718 nreturned:901 reslen:4197371 122ms
Sun Apr  6 01:11:38.769 [conn2149] getmore prod_db.tweets query: { query: {}, $snapshot: true } cursorid:2408233643061247141 ntoreturn:0 exhaust:1 keyUpdates:0 numYields: 22 lock
s(micros) r:73717 nreturned:905 reslen:4196587 1

If I understand correctly, the "numYields" means that it has tried "numYields" times to run the query, but yielded. However, I don't know which process might be blocking it, and not why it is crashing. Any idea?


texts: SQL server databases stuck in restoring state

texts:
I have a Sharepoint server. We had an issue with our backup tool and now some of my databases are stuck in restoring state!

Is it possible to stop the restoring process? and also, How can I make sure the database integrity has not been compromised?


texts:
I need to find unused index on a MariaDB database and do not have 'userstat' enabled, I found this query which is supposed to find unused indexes, but I am unable to get a full understanding of this as I am just a beginner, I have an understanding of joins, subqueries, informationschema, but I am not sure how this query is calculating if the index is unused, if you can explain on what is going on in this query that would be really helpful thank you.
MariaDB 10.3.28
  SELECT
  t.TABLE_SCHEMA,
  t.TABLE_NAME,
  s.INDEX_NAME,
  s.COLUMN_NAME,
  s.SEQ_IN_INDEX,
  ( SELECT MAX(SEQ_IN_INDEX)
    FROM INFORMATION_SCHEMA.STATISTICS s2
    WHERE s.TABLE_SCHEMA = s2.TABLE_SCHEMA
    AND s.TABLE_NAME = s2.TABLE_NAME
    AND s.INDEX_NAME = s2.INDEX_NAME
  ) AS `COLS_IN_INDEX`,
  s.CARDINALITY AS `CARD`,
  t.TABLE_ROWS AS `ROWS`,
  ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `SEL %`
FROM INFORMATION_SCHEMA.STATISTICS s
  INNER JOIN INFORMATION_SCHEMA.TABLES t
    ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
    AND s.TABLE_NAME = t.TABLE_NAME
WHERE t.TABLE_SCHEMA != 'mysql'
AND t.TABLE_ROWS > 10
AND s.CARDINALITY IS NOT NULL
AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00
ORDER BY `SEL %`, TABLE_SCHEMA, TABLE_NAME;


texts: SQL Server 2016 Cluster - mail works in alerts; sp_send_dbmail dosen't send in jobs

texts:
I upgraded from 2008 R2 to a 2016 cluster [Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)].  We have some scheduled emails that happen from the agent that are reporting success as the agent runs them, but no email is sent to recipients.  If I take the job out of the agent and run it in the query window it works fine.  But if the job executes it, it reports success but no email is generated.  Alerts however send out just fine from the agent.

Here is the start of an sp_send_dmail script

Select @asOfDate = yesterday_end from dbo.DTE_udfCommonDates(GETDATE())

SET @s = 'Historical Working Data as of '+CONVERT(VARCHAR(12),@asOfDate,107)
Set @b = @s
Set @f = 'HistoricalWorkingDataAsOf-'+replace(CONVERT(VARCHAR(12),@asOfDate,101), '/', '-')+'.csv'

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'user@domain.com',
@body= @b,
@subject = @s ,
@profile_name = 'Default Database Mail',
@query = '.....

Have any suggestions of some configuration we missed?  Things to try?


texts: Can a weak entity type be involved in an "is-a" relationship?

texts: Considering the scenario depicted in the diagram below, as the title says, can a weak entity type take part in an "is-a" relationship?