texts: Sql Server 2008 R2: Simple recovery model with transaction log backups

texts:
I'm busy reviewing my SQL Maintenance plans and I'm a bit confused with the recovery model option on the databases, specifically related to point in time recovery.

Here is my scenario:

I have two maintenance plans

One that does hourly transaction log backups
another that does a daily full backups (but first a transaction log backup before the full backup)

All databases are set to use the simple recovery model.

Here are my Questions

Seeing that the recovery model is set to simple and msdn states that the simple recovery model does not support point-in-time recovery - Does this mean that I won't be able to use my transaction log backups to restore the database in a disaster to an hour before it happened?
Which backup should be done first, the database backup or the transaction log backups?

Articles that I'm busy reading say I should do the database backup first and then the transaction log backup else I will get maintenance plan errors, but I'm currently first backing up my transaction logs and then data databases and I'm not getting any errors.

My final solution (Maintenance Plan)
Set all databases to use the FULL recovery model. Then:
Hourly Maintenance Plan:
- Check sufficient hard drive space for backups
- Make Transaction Log Backups
- Archive transaction log backups (zip the .trn files)
- FTP the archived transaction log backups offsite

Daily Maintenance Plan:
- Check sufficient hard drive space for backups
- Do a full database backups followed by transaction log backups
- Archive backups (zip the .bak and .trn files)
- FTP the backups offsite

Weekly Maintenance Plan:
- Check database integrity
- Rebuild indexes
- Cleanup history older than 4 weeks
- Delete .bak and .trn files older than 4 weeks
- Delete archived backups (zip file) older than 4 weeks

If any task fails a notification email is sent. A success email is also sent when the daily and weekly task finish successfully.

Note of testing your maintenance plans. My first backups got corrupted by the ms windows ftp.exe not configured correctly to use passive mode. SO CHECK YOUR BACKUPS :)


texts: out of shared memory error when doing pg_dump

texts:
I have a postgres database with 3 schemas and around 1200 tables. When I try to take a schema only pg_dump of it using the command, pg_dump -h localhost -p 5432 -U postgres -d my_test_db -s, I sometimes get error

  ERROR:  out of shared memory
  HINT:  You might need to increase max_locks_per_transaction

This case also occurs in a random manner, when I re-run the same query, it works well. Is there any ways to overcome this


texts: Check for blocked queries periodically and cancel them

texts:
I have series of update queries which are executed every 5-10 seconds. Sometimes it happens that one of these will block all the pending update queries as for some reason it just freezes.

Since I can afford to lose an update is there are way to periodically check what queries are pending in the database, say for example for more that 1 minute, and subsequently cancel them?


texts: how to check how much time PostgreSQL database takes for making connection?


texts:
SELECT *
FROM   SameLogTable
WHERE  ID_Table IN (SELECT ID_Table-1
                    FROM   SameLogTable
                    WHERE  <SameCondition>) OR
       ID_Table IN (SELECT ID_Table
                    FROM   SameLogTable
                    WHERE  <SameCondition>) OR
       ID_Table IN (SELECT ID_Table+1
                    FROM   SameLogTable
                    WHERE  <SameCondition>)

This query runs on a Logging Table, and I want to select particular events, but also the event preceding and following those events.
This solution feels ugly and inefficient, what would be the better way to write the for this?

Example: If I am interested in lines with ID 4, and 23 of a LogTable, I want to get the following result:

ID    Column1    Column2    ...
3     ...        ...
4     ...        ...
5     ...        ...
22    ...        ...
23    ...        ...
24    ...        ...

These are all lines from the Same LogTable, except I specify Lines 4 and 23 using a WHERE , and I want the Select to automatically return rows 3,5 for 4 and rows 22, 24 for 23.

To Summarize the results:

MyQuery:     16s
UNION ALL:    4s
Join:        ~0s

Thanks for the replies!


texts: mysql trigger on update with insert statement

texts:
I am trying to create a simple trigger which is handled on update operations. The trigger is checking before update if a record of an update exists, then if not it is created.

DELIMITER ;;
CREATE TRIGGER trig
BEFORE UPDATE ON table
FOR EACH ROW
BEGIN
IF old is null then
INSERT INTO table(table.col1, table.col2) VALUES ('new.table.col1','new.table.col2');
END IF;
END;
;;
DELIMITER ;

Why is it not adding a record if update is trying to update some non existing record?


texts:
I have problem with connect ApacheHttpServer with Oracle. All work on OpenSuse 12.3

At first I install Oracle instal client 11.2. Set environment variables:

ORACLE_HOME=/usr/local/instantclient_11_2/
LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
PATH=$ORACLE_HOME:$PATH
TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_HOME LD_LIBRARY_PATH PATH TNS_ADMIN

After that I compile apache http :

#apr
./configure --prefix=/usr/local/apache2/apr
make
make install

#apr-util
./configure --prefix=/usr/local/apache2/apr-util --with-apr=/usr/local/apache2/apr/
make
make install

#httpd
CPPFLAGS=-IORACLE_HOME/include LDFLAGS=-LORACLE_HOME ./configure --prefix=/usr/local/apache2/httpd --with-apr=/usr/local/apache2/apr/ --with-apr-util=/usr/local/apache2/apr-util/
make
make install

Unfortunately, the installation was not successful because mod_dbd_oracle.so didn't created. I think I was connected with that:

#./configure output:

checking oci.h usability... yes
checking oci.h presence... yes
checking for oci.h... yes
checking for OCIEnvCreate in -lclntsh... no
   setting LIBS to "-lnnz11"
checking for OCIEnvCreate in -lclntsh... no
   nulling LIBS
   setting LIBS to "-lnnz10"

I could not find a way how to solve. So I decided to use Oracle with ODBC driver. I install unixODBC 2.2.12-219.1.1.

After that I modife to odbc.ini and odbcinst.ini:

#odbc.ini
[xe]
DRIVER = oraclevb
DSN = xe
ServerName = //192.168.56.101:1521/xe
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10`enter code here`
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MaxLargeData = 0
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
StatementCache=F
CacheBufferSize=20
UseOCIDescribeAny=F

 #odbcinst.ini:
[oraclevb]
Description = Oracle ODBC
Driver = /usr/local/instantclient_11_2/libsqora.so.11.1
Setup =
FileUsage =
CPTimeout =
CPReuse =

I chcek how it work with command:

isql -v xe user passowrd

All was OK. So I again compile Apache Http Source. Modify httpd.conf to use ODBC:

LoadModule authn_dbd_module modules/mod_authn_dbd.so
LoadModule authn_core_module modules/mod_authn_core.so
LoadModule authz_host_module modules/mod_authz_host.so
LoadModule authz_user_module modules/mod_authz_user.so
LoadModule authz_dbd_module modules/mod_authz_dbd.so
LoadModule authz_core_module modules/mod_authz_core.so

LoadModule access_compat_module modules/mod_access_compat.so
LoadModule auth_basic_module modules/mod_auth_basic.so
LoadModule dbd_module modules/mod_dbd.so

DBDriver odbc
DBDParams "datasource=xe"

<Directory "/sample">
     AuthType Basic
     AuthName "private area"
     AuthBasicProvider dbd
     AuthDBDUserPWQuery "SELECT password FROM USERS WHERE name = %s"
     AuthzDBDQuery "SELECT group FROM GROUPS user_name = %s"
     Require dbd-group grup1 grup2
</Directory>

Start Apache HTTTP Server and I found in error logs:

[Sun Jul 14 15:19:07 2013] [dbd_odbc] SQLConnect returned SQL_ERROR (-1) at dbd/apr_dbd_odbc.c:1130 [unixODBC][Driver Manager]Can't open lib '/usr/local/instantclient_11_2/libsqora.so.11.1' : file not found 01000
[Sun Jul 14 15:19:07.332394 2013] [dbd:error] [pid 13374:tid 140250220050240] (20014)Internal error: AH00629: Can't connect to odbc: [dbd_odbc] SQLConnect returned SQL_ERROR (-1) at dbd/apr_dbd_odbc.c:1130 [unixODBC][Driver Manager]Can't open lib '/usr/local/instantclient_11_2/libsqora.so.11.1' : file not found 01000

I don't know what I can do with that. Any suggestions would be very helpful. Maybe there is another way that conduct authorization and authentication using the Oracle database. Or other Apache module?


texts:
I have the following MySQL tables:

games
genres
game_genres

Games can have many genres (action, fighting, horror), so they're in a lookup table with game_id and genre_id. When I pull in my list of games, I want to combine these tables and get the text genre name into a comma separated column. I'm able to GROUP_CONCAT the genres from the lookup table into a column, but struggling to make those join into the plain text version. So if a game called "Street Fighter" was associated with the Fighting and Action genres, it would pull up a row with an aliased column name of genres and would be Street Fighter, Action. I'm also using "themes" in the same manner as genres, but I'm assuming whatever method will help me do what I need to genres will work for themes too.

Here is the query I'm currently using:

SELECT
    games.*,
    GROUP_CONCAT(DISTINCT genre.genre_id) genres,
    GROUP_CONCAT(DISTINCT theme.theme_id) themes
FROM
    retro_games games
LEFT JOIN retro_game_genres genre ON games.game_id=genre.game_id
LEFT JOIN retro_game_themes theme ON games.game_id=theme.game_id
    WHERE games.game_id='1375'
    GROUP BY games.game_id


texts:
I get periodic slows where the CPU goes high for a period of time which I can occasionally simulate via a query. It almost seems like it happens when the data falls out of cache which seems to happen more than I'd expect. The ram stays at 3.1GB of 3.5GB. Yes, that's probably too low but the system had been running for a couple years without much issue until recently (not sure if over period of time or a specific event). I'm trying to decide if we need to pay for more ram, CPU, or disk IO to our hosting provider. Would low ram cause CPU to go high? CPU: AMD Opteron Processor 4171 HE 2.10 GHz

Begin Edit
It looks like sqlserv process is not causing the high cpu but aahStorage.exe is. I believe this is the process that retrieves data from proprietary files on disk for Wonderware System Platform. So I'm not sure if it's slow Disk IO, low memory available, etc. causing it.
End Edit

I created the following query

SELECT
REPLACE(counter_name, '(KB)', '(MB)'),
CASE WHEN counter_name LIKE '%(KB)%' THEN CAST((cntr_value/1024) as varchar(1000)) + ' MB' ELSE CAST(cntr_value as varchar(1000)) END
FROM
(
    select TOP 1000
    ('MemoryMgr: ' + counter_name) as counter_name,
    cntr_value
    FROM sys.dm_os_performance_counters
    WHERE object_name='SQLServer:Memory Manager'
    UNION ALL
    select TOP 1000
    ('BufferMgr: ' + counter_name) as counter_name,
    cntr_value
    FROM sys.dm_os_performance_counters
    WHERE object_name='SQLServer:Buffer Manager'
) sub1

And have the following results;

MemoryMgr: External benefit of memory     0
MemoryMgr: Connection Memory (MB)         2 MB
MemoryMgr: Database Cache Memory (MB)     485 MB
MemoryMgr: Free Memory (MB)               128 MB
MemoryMgr: Granted Workspace Memory (MB)  0 MB
MemoryMgr: Lock Memory (MB)               2 MB
MemoryMgr: Lock Blocks Allocated          12328
MemoryMgr: Lock Owner Blocks Allocated    12703
MemoryMgr: Lock Blocks                    6751
MemoryMgr: Lock Owner Blocks              6855
MemoryMgr: Maximum Workspace Memory (MB)  725 MB
MemoryMgr: Memory Grants Outstanding      0
MemoryMgr: Memory Grants Pending          0
MemoryMgr: Optimizer Memory (MB)          1 MB
MemoryMgr: Reserved Server Memory (MB)    0 MB
MemoryMgr: SQL Cache Memory (MB)          11 MB
MemoryMgr: Stolen Server Memory (MB)      1226 MB
MemoryMgr: Log Pool Memory (MB)           1 MB
MemoryMgr: Target Server Memory (MB)      2000 MB
MemoryMgr: Total Server Memory (MB)       1841 MB
BufferMgr: Buffer cache hit ratio         1121
BufferMgr: Buffer cache hit ratio base    1121
BufferMgr: Page lookups/sec               7048998623
BufferMgr: Free list stalls/sec           0
BufferMgr: Database pages                 62196
BufferMgr: Target pages                   7176192
BufferMgr: Integral Controller Slope      21
BufferMgr: Lazy writes/sec                8896
BufferMgr: Readahead pages/sec            961391
BufferMgr: Page reads/sec                 1090105
BufferMgr: Page writes/sec                1308925
BufferMgr: Checkpoint pages/sec           1204609
BufferMgr: Background writer pages/sec    0
BufferMgr: Page life expectancy           134774

I'm having a hard time interpreting the results. Any help is appreciated. Thanks


texts: Oracle 11g not allowing to connect to. Error ORA-01122: database file 1 failed verification check

texts:
I am running Oracle 11g on Windows 7 Enterprise SP2 16 GB RAM, 1 TB HD. I can connect to Oracle using SQL Plus (see below),

C:>sqlplus sys/xxxxxxxxx@XE as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 9 18:52:26 2014
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

... but when JDeveloper or another commercial application try to connect to I get the following error:

ORA-01033: Oracle initialization or shutdown in progress ...

Any clue or help would be really appreciated.

Thanks,

XMLDeveloper


texts: why does create index with SET FOREIGN_KEY_CHECKS=0; and SET UNIQUE_CHECKS = 0; take a lot of time?

texts:
i am adding index to a very large table , around 25 M records and 60 GB of data.
i am using MYSQL version 5.6 InnoDB , my statement is :

SET FOREIGN_KEY_CHECKS=0;
SET UNIQUE_CHECKS = 0;
ALTER TABLE `my_table`
ADD   KEY `my_idx` (`site_id`,`name`);

and i am running it like this :

mysql --host=**.database.azure.com -u *** -p*** my_db < my_file.sql

but it take around 1 hour , the same time as without this statement , what am i missing ?


texts: How can I use expressions as arguments to EXEC?

texts:
I am building some scripts and found an annoyance to be that I can't provide expressions as arguments to EXEC.

Here is an example I have run into. I want to setup various items using a consistent format that includes the database name. I can't simply concatenate the strings when passing an argument

EXEC msdb.dbo.sp_help_schedule
    @schedule_name = 'FullBackup_'+@DatabaseName,
    ...

instead I have to declare a variable just for the final string and pass that...

DECLARE @ScheduleName varchar(100)
SET @ScheduleName = 'FullBackup_'+@DatabaseName
EXEC msdb.dbo.sp_help_schedule
    @schedule_name = @ScheduleName,
    ...

I have built up a command string dynamically when necessary which could achieve this but would rather not to do that every time. Is this a limitation in t-sql or is there a way around it?


texts: Schedule SQL Jobs from Central Management Server

texts:
I've set up a Central Management Server on a SQL Server 2016 instance.  I'm able to manually run queries against multiple servers, but I want to be able to run automated maintenance scripts.
Is there a way to set up a single SQL Job that will run a script against ALL registered servers?


texts: What to use, CASCADE DELETE rule or ON DELETE trigger for one to many constraint?

texts:
I would like to ask about how to handle this relationship. For better view I've tried to simplify and convert my situation into how I think StackOverflow question voting has (sorry if not :-)

I would like to add a constraint with CASCADE DELETE rule however I don't know if it's possible for this case. Another option is to create an ON DELETE trigger, so...

My question is:
What would you prefer, CASCADE DELETE constraint rule (if it's possible here) or ON DELETE trigger ?
Or would you create a DB model for this simple case (imagine StackOverflow's voting scheme) different way ?

Why am I asking:
I've read somewhere that CASCADE DELETE constraint rules are internally nothing else than triggers and that some people here don't prefer the constraint rules at all. I know, quite vague question, but I would like to have my DB clean from a not existing relationship records.

The important thing:
I'm creating the application which should be able to run on the SQL Server as well as on MySQL database engines, but both should support all, the CASCADE DELETE constraint rules as well as ON DELETE triggers, I guess.

Thanks!


texts:
I'm currently reading "SQL and Relational Theory" by C.J. Date. And although I'm quite far in the book, I've got a few basic questions. I want to know what the term "logical difference" means, The book does try to explain the term with examples, but does not really explain what it means (or maybe I'm understanding it wrong??)

Here is a small part from the book:

I've said that there's a logical difference between a relation and a picture of a relation. The concept of logical difference derives from a dictum of Wittgenstein's:

All logical differences are big differences.

I know what a logical difference means intuitively, I know what the difference is between a relation and a picture of a relation. What I want is a formal-like definition of the concept of a "logical difference" so I have a good idea of what it means.


texts: How to SELECT from SHOW TABLE STATUS results

texts:
I'd like to limit the rows and columns that come back from the

SHOW TABLE STATUS

command in MySQL 5.1.  Is there a way to get this same information through a SELECT statement so I can manipulate the results in a normal way?


texts: Why parameter for one query returning the same # of results as a different parameter, is extremely disproportionately slower

texts:
I am an application developer that has inherited a SQL Database from a previous developer. There is one view that is causing an issue. This view selects a bunch of different columns from different tables and contains 6 different inner joins. I am running a query on this view like so...

SELECT * FROM dbo.View where UserID = " + employeeId + "

When I run this query on all of the different employees it returns fine within a few seconds. For one employee however, It takes up to a minute or two even when the result set is nearly the same in terms of quantity and times out in the API. I have narrowed the cause to one of the joined tables which makes sense because this joined table has the most rows and makes a lot of connections. I don't know how to diagnose the problem any further.

What I have tried:

creating an identical table as the table that is causing the issue, copying over the same data, but with a new clustered index. The clustered index column I changed to is the column that is being used in the joins by the view. This didn't help. Is there something I need to do "run the cluster and sort" or something like that?

How would one of you move forward with further diagnosing?


texts: Can I pass data between stages in a maintenance task

texts:
I have a Maintenance plan that I wish to execute nightly. The purpose is to set an archived bit against records that are more than 60 days old. This bit all works fine, however I'd like to log (and email an operator) about how many records were affected.

I have a stored proc that does all the heavy lifting, and it is defined in an execute T-SQL task with an out parameter that contains the number of records affected.)

So how do I pass that value to a notify task, or log it?


texts:
I just installed Oracle database 12c on an Ubuntu 12.04. I followed this tutorial Installing Oracle 12c on Ubuntu 12.04 (64 bit)..a hard journey, but it’s worth it! by GemsOfProgramming (kudos to the guy who did it by the way). I haven't done much more than that.

After the installation I tried to connect sqldeveloper to the database, something I already did with 11g & Win7 and 11g & Ubuntu, however I'm getting this error:

Status : Failure -Test failed: IO Error: The Network Adapter could not establish the connection

I tried executing the netac command but it didn't work as a command I had to execute the bin, and the output was:

oracle@abdou-pc:/oracle/base/dbhome_1/bin$ ./netca

Oracle Net Services Configuration: No protocol specified No protocol specified Error: null

and here is the log:

http://pastebin.com/ir4ecG3u

thought this may also be of use:

root@abdou-pc:/home/abdou# oraenv

ORACLE_SID = [root] ?

ORACLE_HOME = [/home/oracle] ?

ORACLE_BASE environment variable is not being set since this

information is not available for the current user ID root.

You can set ORACLE_BASE manually if it is required.

/usr/local/bin/oraenv: 223: [: unexpected operator

My Oracle installation is in /oracle/base/dbhome_1

I don't know what other info I should post, and I don't wanna flood you people, so I'm here and I can post any info you need on demand.

It seems to me that my Oracle db isn't even launched, and neither are the listeners, and there might be a problem with my environment variables.  I don't have much experience with the configuring part of the databases, and I know that Oracle isn't supported on Ubuntu ... but I like Ubuntu and it's just for learning purposes.


texts: How do I clear a table from SQL Server's cache?

texts:
I have a few tables in my database that should not be cached.

How do I tell SQL Server not to cache a table's pages or how do I flush a single table from the cache?

Flushing all the cache is not an option.

I'm using SQL Server 2008 and SQL Server 2008 R2.


texts:
I have a file with ids which looks like this:

10555
16452
17005
17511
17512
17513
17515
17516
17517
17518
17520
17521
17522
17523
17524
17525

This file was created with unix tools like grep and cut.

I would like to execute a SQL statement in PostgreSQL roughly like this:

SELECT * from my_table where id in file_with_ids;

Is there a simple way to do this?


texts: Checksum difference between table in MariaDb 10.4 and Mysql 8

texts:
We have a script that transfers databases between clusters. To make sure the data is still valid, we run a checksum on some of the tables. If those are ok, we mark transfer succesful.
The problem is that even though I notice the data is same, I get different result for servers.
So running this:
CHECKSUM TABLE db.table EXTENDED

For Mariadb it gives over 500k and on Mysql over 3 bilion.
This documentation says this should be the same but evidently, they are not.
What could affect the difference?
I do know about pt library but I would rather use checksum if possible.
Thank you, I appreciate all help


texts: How to copy a MySQL database which is a mixture of InnoDB and MyISAM to the same server?

texts:
Because mysqldump takes forever, we decided to just move around the files. Because the 2 databases are on the same server, a cp will be fast.

The problem is, from research, MyISAM is okay with being moved entirely, but InnoDB is not.

I read about this: http://www.mysqlperformanceblog.com/2009/07/31/copying-innodb-tables-between-servers/

The final question is, is there a way to move the entire database at once and make sure that both the InnoDB and MyISAM tables are good.

Any ideas? Thank a lot!


texts: Help with Query. Finding records that have the same relationships (MySQL)

texts:
I have a table of Listings that has a many to many relationship with a Taxons table. The table structure looks like this:

listings
----------------
id (int)
name (varchar)

listings_taxons
----------------
listing_id (int)
taxon_id (int)

taxons
----------------
id (int)
name (varchar)

My goal is to select all rows in the listings table that has a matching list of taxon ids. Each returned listing record must have a relationship with the two taxons, such that I get the record set containing the intersection of records between the two taxons.

Example: I have a listing called "Muffler" and it has the following taxons: "Ford", "Mustang", "Exhaust". If I query for all listings with "Ford" and "Exhaust" I should get all listings that have "Ford" and "Exhaust" as taxons.

How would I construct this query efficiently?


texts:
I want to ask your opinion in using MySQL triggers or transactions in a website.

Actually I have a history payment table with - UserId | OperationId | Comment | Credits | Sign (debit or credit). So each payment operation is inserted in this table.

However, it will be time consuming calculating each time the total credit amount of the user every time he do action. So I thought maybe is a good idea to keep the total credit amount for each user in a user profile table.

Here is the problem. How can I be sure that the total credit amount form the profile table will stay synchronized with the operations from payment history table ?

I thought using 2 methods:

MySQL triggers or
transactions coded in the source code

Which is more reliable? What if I have large database (over 100.000 users) ?

Do you have any suggestions to do this?

The BD MySQL engine is InnoDB.


texts: Merge two SELECT queries with different WHERE clauses

texts:
I have one table of services. I need to merge two SELECT queries. Both have different where clauses. For example

SELECT
  U_REGN as 'Region',
  COUNT(callID) as 'OpenServices',
  SUM(CASE WHEN descrption LIKE '%DFC%' THEN 1 ELSE 0 END) 'DFC'
FROM OSCL
WHERE
  ([status] = - 3)
GROUP BY
  U_REGN
ORDER BY
  'OpenServices' desc

This gives me result

Region    | OpenServices | DFC
Karaci    | 14           | 4
Lahore    | 13           | 3
Islamabad | 10           | 4

I have another query

SELECT
  U_REGN as 'Region',
  COUNT(callID) as 'ClosedYesterday'
FROM OSCL
WHERE
  DATEDIFF(day, closeDate, GETDATE()) = 1
GROUP BY
  U_REGN
ORDER BY
  'ClosedYesterday' desc

It gives me result

Region    | ClosedServices
Karachi   | 8
Lahore    | 7
Islamabad | 4

I need to merge both results, and show ClosedServices beside the DFC column.


texts: When is a result returned deterministic and when not?

texts:
if you execute a statement like this:

select first field1, field2 from exampleTable where field1 = '1';

then you get a warning like this:

  The result returned is non deterministic SQLCode=122

So far it's OK, because in this case you need an "order by" clause here.

But when you use the same statement inside of a BEGIN END block putting the values into some declared variables,

BEGIN
declare varField1 varchar;
declare varField2 varchar;

select first field1, field2 into varField1, varField2
from exampleTable
where field1 = '1';

END

the warning does not come up!
Why?

Is the "order by" clause not needed here? Is this statement suddenly deterministic?...

(using SQL Anywhere 12)


texts: Single collection with a key vs multiple collections

texts:
I am designing the DB for a project, where, I am sure that I would not be querying for data belonging to multiple values of a key x at the same time.  All queries will be for sure for a given value of x.  Is it better to create a single collection having an index for x, or is it better to create multiple collections having x as part of the collection name?

I want to optimize storage space, and well as ensure reasonable read query performance.


texts:
I have two tables:

parent with columns identifier (pkey) and period;
child with columns identifier (pkey), parent_identifier and period.

There are additional columns, but I did not list them here as they are not really relevant.

I want to select the periods as following:

If the parent matches some predicate, then pick parent.period.
If not, then pick child.period from each child.

I have the following query:

select   q.period
from     parent,
lateral  (
           select  parent.period
           where   <some complex predicate>
           union all
           select  child.period
           from    child
           where   child.parent_identifier = parent.identifier and
                   not(<the same complex predicate as above>)
         ) as q(period);

While this query works and returns the expected periods, I am wondering if this query could be rewritten in such way that <some complex predicate> does not have to be evaluated twice, to improve performance (albeit by a small factor) and general query structure to represent the if ... else ... from above. Ideally, I want to loop once through each parent/child row and evaluate the predicate just once.

I tried to introduce a case when <some complex predicate> then ... else ... end clause, but I fail to get it right. I am not sure if that is even possible in the FROM clause. Is this possible?

I also had select case when <predicate> then parent.period else unnest(array(select child.period from child where ...)) from parent in mind (though I did not try it, so it might be disallowed in a case when ... as well), but I am not sure if first creating and then unnesting an array is really optimal. Also, I am not really fond of set-returning functions, such as unnest, in the selection.


texts:
I have a problem with a query. I have a db with car and their spec, every model has a different car related to that model with its price. I want to create a query that gives me the min and max price for each models.

something like this:

+--------+----------+
| model  | price    |
+--------+----------+
| golf   | 4000     |
| golf   | 6000     |
| golf   | 10000    |
| panda  | 3000     |
| panda  | 5000     |
| panda  | 7000     |
+--------+----------+

the query give me this:

+--------+----------+
| model  | price    |
+--------+----------+
| golf   | 4000     |
| golf   | 10000    |
| panda  | 3000     |
| panda  | 7000     |
+--------+----------+

Can you help me?


texts: How to update price where value of rows exist in value of column in sql?


texts: Downside of large "max connections" in a connection pool from a web app

texts:
I understand that if you don't pool database connections at all, then you bear the overhead of establishing a new database connection every time you need to query the database, which will make your app slow, and that you save that overhead by pooling.

If you set your max pool size too small - let's say, to 1 connection max - then the downside of that is your app has to share that single database connection for all requests, and requests will have to wait for the previous one to finish with the database connection before they can fetch it from the pool and re-use it.

But what if you just set your max pool size really big, like 1000 connections max. Shouldn’t it then favour re-using connections over establishing new ones, and only max-out the pool size if it actually needs to in order to service demand?

What's the down side of making connection pools bigger than they need to be to service the app's demand, assuming unused connections are closed / removed from the pool after an idle timeout?


texts: Invalid connection error on SQL Server 2008

texts:
I have this third party application that is interfacing with a MS SQL Server and I have some issue migrating its database from a MS SQL Server 2005 to A MS SQL Server 2008. The application was provided by a manufacturing company (it interfaces with a machinery) but produced by someone else (let's say a third third-part). I tried to contact our provider (the machinery producer), but basically they said they cannot reach the application developer and the product is unsupported. So I am on my own to solve this thing.

Currently the database instance is located on a box with the following features:
-Virtualized Windows 2003 SBS Server
-SQL Server 2005 (9.4.5000.00)

It has to be migrated to the following:
-Virtualized Windows 2008 R2
-SQL Server 2008 R2 SP2 (10.52.4000.0)

Both SQL Server are listening on the standard 1433 port.
The application connects to the DB through SQL authentication on a local network.

I have successfully installed the server and the db (.mdf and .ldf files) on the new MS SQL 2008 server: I can query the db through Sql Server Management Studio and it returns correct results. The DB is really simple: few tables with few relationships: basically the application just needs to send a code and retrieve a string. I can also connect to the db through the SQL Server Management Studio (using both windows and SQL authentication), but the application fails to connect, issuing this error: "[DBNETLIB][ConnectionOpen (Invalid Instance()).] Invalid connection".

I am not a MS SQL Server expert (I use it just for this application), can someone help me out?
Thanks,
Gio


texts: Why are queries getting dropped from query store?

texts:
I see sometimes that queries are dropped from the query store. But I don't know why. The query store disk-space is not full and the query is far from the stale query treshold.

What I experience is that a query that I know has been executed has no history of getting executed in query store.

I will run this query to get the query_id:

SELECT   t.query_sql_text, q.query_id, object_name(q.object_id) AS parent_object, q.query_text_id, q.count_compiles
    FROM sys.query_store_query_text t JOIN sys.query_store_query q ON t.query_text_id = q.query_text_id
WHERE  t.query_sql_text LIKE  N'%part_of_query%'

And then I use the query_id in 'Tracked queries'.

But, sometimes, if I later in the day want to look at this query and run the above query again I get a new query_id and there is no trace of the old query_id.
This can even impact 'Total resource consumption'. If the new query_id was created 11:00 and consumed a lot of resources, then it will look like there was a sudden increase in resource usage at 11:00.

The queries I often look at involve a temporary table, which means that there is a batch_sql_handle stored with the query_id. I know that database_id is part of this and that a failover on an Always On AG would cause a similar situation. ref: https://milossql.wordpress.com/2019/10/21/dude-wheres-my-forced-plan-part-1/
I have been trying to find more information about what the batch_sql_handle is based on and how the auto cleanup is working, but have not found anything that helps me.

Size Based Cleanup Mode is set to Auto


texts: The publication 'My_publication' doesn't exist?

texts:
We are trying to test the success of Transactional replications.
by Googling , i found that i need to execute in our publisher this stored procedure :

sp_publication_validation

However , When i execute :

exec sys.sp_publication_validation
@publication=ReplicationFaouzi,
@rowcount_only=0,
@full_or_fast=0,
@shutdown_agent=0

I get this error :

  Msg 20026, Level 16, State 1, Procedure sp_MSpublication_validation, Line 57
      The publication 'ReplicationFaouzi' doesn't exist ?

When i run this sql :

SELECT publication
FROM MSpublications
ORDER BY publication

I get in Result :

ReplicationFaouzi

what is wrong ?

Update

By checking the code of this stored procedure i found that it calls un-found stored procedure  sys.sp_MSreplcheck_publish and sys.sp_MSrepl_getpublisherinfo :

exec @retcode = sys.sp_MSreplcheck_publish
if @@error <> 0 or @retcode <> 0
begin
    return (1)
end

EXEC @retcode = sys.sp_MSrepl_getpublisherinfo  @publisher      = @publisher,
                                                @publisher_type = @publisher_type OUTPUT,
                                                @rpcheader      = @cmd OUTPUT

sys.sp_MSreplcheck_publish exist under a database named model ,and i can't find sys.sp_MSrepl_getpublisherinfo!!

Update 2

My Team re-created the  replication database and  now  this sql command:

 SELECT [name] FROM sys.databases WHERE is_published = 1;

dosen't return any value , but this :

 SELECT [name] FROM sys.databases WHERE is_merge_published = 1;

returns 'Guichet'

Now, when  i run :

USE [Guichet];
GO
EXEC sp_helppublication;

I get nothing  !


texts: Problem with foreign key between 3 tables. (PgAdminIII)

texts:
I'm using pgAdmin III for PostgreSQL but I have a problem with foreign keys between 3 tables. My tables are:

Locale with a primary key IDnegozio (integer type, no need to make it serial type)
Orario with a primary key IDorario (integer type, no need to make it serial type)
OrarioToLocale with two columns: IDlocaleT & IDorarioT (both integer).

I'm trying to make a foreign key between IDlocale -> IDlocaleT and IDorario -> IDorarioT so, I can have the relation that a Shop can have multiples time.
The problem is that when I try to make these foreign keys, and an error occurs me:
"no unique constraint matching is given keys for referenced table" and I don't understand why. I tried to google but I didn't find any answer!
How can I do?

This is the code of the three tables:

CREATE TABLE public."Locale"
(
  "IDnegozio" integer NOT NULL DEFAULT nextval('"Locale_IDnegozio_seq"'::regclass),
  CONSTRAINT "Locale_pkey" PRIMARY KEY ("IDnegozio"),
)

CREATE TABLE public."Orario"
(
  "IDorario" integer NOT NULL DEFAULT nextval('"Orario_IDorario_seq"'::regclass),
  CONSTRAINT "Orario_pkey" PRIMARY KEY ("IDorario")
)

CREATE TABLE public."OrarioToLocale"
(
  "IDlocaleT" integer NOT NULL,
  "IDorarioT" integer NOT NULL
)

How can I do? Thank you advice!


texts:
Just to continue the question , I'm struggling to do left join and to keep equal sized time series generated in the previous step.

So I have customer_date_dimention table with equal time series for each cust_id.

cust_id, date

1, 2018-01-01

1, 2018-01-02

2, 2018-01-01

2, 2018-01-02

And then I have retention_weekly_intensity

cust_id, date, intensity
1, 2018-01-02,6

2, 2018-01-02, 10

and then I want to create the following result:

cust_id, date, intensity

1, 2018-01-01, 0

1, 2018-01-02, 6

2, 2018-01-01, 0

2, 2018-01-02, 7

The query is the following, but the verification gives different sized series

create table retention_weekly_intensity_balanced as
 select
 coalesce(t.cust_id, 'unidentified') as clientidno,
 dtd.d,
 coalesce(t.size_of_increased_intensity) as size_of_increased_intensity
 from customer_date_dimention dtd
 left join retention_weekly_intensity t

ON
 dtd.d = t.date
 and
 dtd.cust_id = t.cust_id
WHERE dtd.cust_id is not NULL
 ;

select cust_dt, count(*) from retention_weekly_intensity_balanced group by 1

How do I properly join the labeled series with other data?

Kind regards


texts: A question about oracle upgrade from 12.0.0.1 to 12.0.0.2 using dbua

texts:
I found this guide for upgrade.
After the upgrade, can I delete safely the old DB product dir
/var/oracle/app/oracle/product/12.1.0 ?


texts: Why do MySQL threads often show "freeing items" status when the query cache is disabled?

texts:
When I run SHOW PROCESSLIST, there are often a large number of INSERT/UPDATE threads in the "freeing items" state.

The MySQL manual suggests that at least part of the reason for a thread to be in this state involves the query cache - presumably it would be invalidating cached queries due to the changed data.

However, my query_cache_size is set to 0 which should disable the query cache altogether.

What other reasons would there be to have so many threads in this state?

The relevant tables use the InnoDB storage engine.


texts:
I'm trying to create a few functions to make the SQL queries between my SQLite and Postgres DB's the same.
One thing I would like to do is change a timestamp to a double via a function such as:
create function tz_to_double(a timestamp) returns DOUBLE PRECISION as $$
    SELECT EXTRACT(EPOCH FROM TIMESTAMP a);
$$ language sql;

Postgres however doesn't allow me to create this function. It errors out like so:
ERROR:  syntax error at or near "a"
LINE 2:  SELECT EXTRACT(EPOCH FROM TIMESTAMP a);

Would it be possible to create this function / use this as a param?


texts: How to take SQL Server databases backup using .bat script file with time stamp

texts:
I am using SQL Server Express edition, so there is no features for backup plan, I want to take databases backup using bat script, and create a scheduler to be run everyday. I have created .bat file but need to place a time stamp at the end of backup file like test_13_April_2017.

cd "C:\Program Files\Microsoft SQL Server\110\Tools\Binn"
SqlCmd -S SERVER02 -Q "Backup Database testdb To Disk='C:\backup\testdb.bak'"

Is there is any way to give time stamp at the end of every DB backup.


texts: Allow user to create databases on server and to see only the databases that are created by him, without creating login first

texts:
I want to implement the following use case using C# application and MS SQL Server Express 2014. I have a centralized database server in which there are already created some database (which have the same tables). In my c# application I want to allow users to create databases (with same format but different names) and users can assign other users to have access to the newly created database.
Also I want to assure that at the first use of the c# app the user shall not be able to see any database from the server address. Only after he executes the "Create db" button , he can view only the database which was previous created by him. Same thing shall happen for the other users when they will receive access on a certain database.
Another requirement is that the database creator and the other users mapped on the recently created database to not be able to have access on the database server via SSMS.
So an example can be : suppose you have 3 databases (A,B,C)  in sql server xyz . Now user AA opens the c# app and in the list of available database he see nothing so it creates a new one db1. After that db1 is present in the list. Then he assign users AB, AC to db1. Now AB, AC can see also db1. AA, AB, AC shall not have access to db1 database via SSMS.
What I've tried until now:

I have configured a laptop as a remote MS SQL Express 2014.
Using SSMS I first create a login, then I deny view to any database and after I allow access only on certain database

.
USE [master]
GO

CREATE LOGIN [xx] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

-- Step 2:(deny view to any database)
USE master;
GO
DENY VIEW ANY DATABASE TO [xx];

 -- step 3 (then authorized the user for that specific database , you have to use the  master by doing use master as below)
USE master;
GO
ALTER AUTHORIZATION ON DATABASE::DB1 TO [xx];
GO

Now I don't know if it is possible to allow the creation of databases on server and to map users on database without creating a login first. Also I don't know how to restrict the access for SSMS usage.


texts:
I'm trying to make "online Judge Environment"(like SPOJ). In which users have facility to see their past submissions for each question, their best code performance(like running time, if they succeed to answer correctly). User have their USENAME(which will be unique) and PASSWORD. Around 10,000 users are expected.

My Effort : there will be one 'login' table in which each column contains USERNAME and PASSWORD. For each question, there will be separate table, in which each column contains USERNAME, their respective code file, status(correct answer, compile error, runtime error,etc.), running time(if any). every question table may contain more than one entry for same USERNAME.

Is there any better design? Should I store code files(text files) in database itself, or somewhere in file system and just give location path of file in table?


texts: Problem with calculating an approximate space required for online index rebuild

texts:
I have a database called TestDB which is in simple recovery model. I performed index rebuild on my Test Server which has no other user transactions except me. Before an online index rebuild the sizes of mdf and ldf files were as below:

         total size          used             free
mdf       13261 MB         13261 MB             0 MB
ldf        9697 MB            25 MB          9672 MB

After rebuilding of the largest index only with fragmentation percentage equivalent to 91% (size of index was approx 5858 MB) mdf and ldf files became as below:

         total size           used             free
mdf       16854 MB          10061 MB         6792 MB
ldf        9697 MB            363 MB         9334 MB

The question is, if the size of the largest index was 5858 MB, and according to some books and resources on the internet which state that total size needed for rebuilding an index is equivalent to approx 120% of the largest index or maybe double size, then total size of mdf file after rebuilding should be just over 20000 MB, shouldn't it?

I calculated that like 5858*120%=7029 MB, and finally as we do not have space in mdf file then autogrowth will happen and final size of mdf will be 13261 MB + 7029 MB = 20.29 GB.

Here I use Ola's script for rebuilding an index:

EXECUTE dbo.IndexOptimize
@DATABASES = 'TestDB',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Indexes = 'ALL_INDEXES',
@MinNumberOfPages = 1000;

The index which is rebuilt is clustered index and key column is of INT data type.


texts: Is the average row size listed in Management Studio's index properties accurate?

texts:
I just inherited some SQL Server 2008 R2 databases and have reason to be reviewing the indexes on some tables. For a table with two columns such as:

CREATE TABLE My_Table ( Obj_Id int NOT NULL,
    Related_GUID uniqueidentifier NOT NULL )
ALTER TABLE My_Table ADD CONSTRAINT
    PK_My_Table PRIMARY KEY CLUSTERED (Obj_Id,Related_GUID)

When populated, the fragmentation tab of the index properties for PK_My_Table lists the average row size as 27.

Also on this table are some non-clustered indexes:

Create Index Ix_My_Table_1 on My_Table (Related_GUID)
Create Index Ix_My_Table_2 on My_Table (Obj_Id, Related_GUID)

Average row sizes for both of those is reported as 22, which is what I find really strange. Assuming we're talking about row data only and not the B-tree, and neglecting the issue of row overhead, then if the leaf node of the non-clustered indexes contains the index columns and the associated clustered index key, then shouldn't the lengths of the non-clustered index rows be at least:

Ix_My_Table_1 = Related_GUID (16) + Obj_Id (4) + Related_GUID (16) = 36
Ix_My_Table_2 = ( Related_GUID (16) + Obj_Id (4) ) X 2 = 40

And here's the kicker...

User Seeks:

PK_My_Table = 59,339
Ix_My_Table_1 = 68,182
Ix_My_Table_2 = 1,381,777

User Scans:

PK_My_Table = 0
Ix_My_Table_1 = 41,114
Ix_My_Table_2 = 1,343

My instinct is to drop the non-clustered indexes, but the evidence suggests they are better (more rows per page) and are obviously preferred by the optimizer over the clustered index. If the row size as reported by Management Studio is correct, then the usage of the non-clustered indexes makes sense, but how is this possible?


texts: Transfer Data between 2 organizational domain

texts:
Please consider the following scenario:

Company A manages the IT Infrastructure of Company B. Company A is located in US and has SQL Server 2014 Enterprise Edition. Company B is located in UK and has SQL Server 2008 R2 Enterprise Edition.

Company A uses Citrix Platform (Server located in Company B domain) as a secure access gateway.

Now, I would like to replicate data from Company B to Company A. I plan to install SQL Express 2014 on Citrix Platform gateway server and subscribe data from Company B SQL Server 2014.

Can I configure a pull subscription on Company A SQL Server box which will pick up data from Citrix Platform gateway server running SQL Server Express 2014?

Please advise.


texts: How should I model my database - cannot decide on multiple options

texts:
I apologize if this might be a rather beginner-level-question.
For my database design, I have worked out multiple options but cannot decide on which one I should implement. I am using 10.4.17-MariaDB.
The basic idea:
I have a Table called Article in which I want to store simple fields such as name (e.g. varchar), description (e.g. varchar), ..., etc. also more complex information like Producer, Price and so on.
I have worked out the following:
Implementation I (using just a FK)

Table: Article
| id (auto_inkr.) | fk_producer | ... |
| --------------- | ----------- | --- |
| 1               | 1           | ... |
| 2               | 1           | ... |
| 3               | 2           | ... |
| ...             | ...         | ... |

As defined, Producer(id: 1), is the Producer of Articles (1, 2, ...?).
Implementation II (using a lookup table)

Table: Article
| id (auto_inkr.)   | fk_article_producer | ... |
| ----------------- | ------------------- | --- |
| 1                 | 1                   | ... |
| 2                 | 1                   | ... |
| 3                 | 2                   | ... |
| ...               | ...                 | ... |

Table: Article Producer
| id (auto_inkr.)   | article_fk  | producer_fk |
| ----------------- | ----------- | ----------- |
| 1                 | 1           | 1           |
| 2                 | 2           | 1           |
| 3                 | 3           | 2           |
| ...               | ...         | ...         |

In this case here, I have implemented a mapping table. This would also allow me to have a many-to-many configuration. I don't have to implement it that way - it is up to my backend-code how I store the data.
Questions
Please keep in mind, that my Article table as of now will have like 7+ dependent tables (as shown in the example here: e.g. Producer). I want to design it, so I can write an efficient filter-function (query) for my text- respectively tag-based-search later on.

Which design should I choose and why?
What are the benefits or downsides of these approaches (best practice)?


texts:
I am trying to capture some data on CPU activity on a SQL instance.

Get-Process | Sort CPU -descending | Select -first 10 -Property ID,ProcessName,CPU

The PowerShell above give me the following output :

How do i modify the powershell to include a timestamp for each row. And output should look something like this.


texts: Why CDC records nothing while being enabled?

texts:
I enabled Change Data Capture on some tables in a Microsoft SQL 2008 database. Both of the following queries return 1:

select top 1 is_cdc_enabled from sys.databases where name = N'<DatabaseName>'

select top 1 T.[is_tracked_by_cdc]
    from sys.tables as T
        left join sys.schemas as S
        on T.[schema_id] = S.[schema_id]
    where S.[name] = N'<SchemaName>' and T.[name] = N'<TableName>'

but when I change the data of a concerned table, cdc.<SchemaName>_<TableName>_CT remains empty.

Why?


texts: What would be a optimal index for this query in mysql

texts:
I'm trying to optimize this query.

SELECT * FROM sales_reps
WHERE account_id = 1
AND (state = 'enabled')
AND (clients >= 5 OR revenue >= 10000)
ORDER BY name, platform, client_group, from_date

All the field in the ORDER BY clause are string except from_date which is a date. (account_id, name, platform, client_group, from_date) is a unique key combination.

I tried creating indexes with combination or those fields but when I do explain it doesn't seem to be using it. The table would have about 2 million rows for each account.

Any thoughts on how I could go about optimizing it?

Update 1: Here is the explain and result:

EXPLAIN SELECT *
FROM `sales_reps`
WHERE `sales_reps`.`account_id` = 1
AND state = 'enabled'
AND (clients >= 1 OR revenue >= 100)
ORDER BY name,platform,client_group,from_date

Here is the result

id select_type table      type possible_keys           key  key_len ref  rows   Extra
1  SIMPLE      sales_reps ALL  account_id_4,account_id NULL NULL    NULL 33215  Using where; Using filesort

account_id_4 is the index with the following keys account_id, state(10), name,       platform, ad_group, from_date, clicks, impressions

Update 2: Here is the full table structure:

CREATE TABLE `sales_reps` (
  `account_id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `platform` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `client_group` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  `state` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `status` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `max_cpc` decimal(8,2) NOT NULL DEFAULT '0.00',
  `match_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `first_page_cpc` decimal(8,2) DEFAULT '0.00',
  `top_page_cpc` decimal(8,2) DEFAULT '0.00',
  `quality_score` decimal(8,2) DEFAULT '0.00',
  `revenue` int(11) DEFAULT NULL,
  `clients` int(11) DEFAULT NULL,
  `conversions` int(11) DEFAULT NULL,
  `cost` decimal(8,2) DEFAULT '0.00',
  `average_position` decimal(8,2) DEFAULT '0.00',
  `campaign` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `phone_view` int(11) DEFAULT '0',
  `website_view` int(11) DEFAULT '0',
  `request_submit` int(11) DEFAULT '0',
  UNIQUE KEY `account_id_4` (`account_id`,`state`(10),`name`,`platform`,`client_group`,`from_date`,`clients`,`revenue`),
  KEY `account_id` (`account_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


texts: Visio 2010 and SQL 2012 - Reverse Enginnering

texts:
We have upgraded to SQL 2012 and Am trying to reverse engineer our Dbs into Visio 2010.  I setup a connecting using SQL 11 driver and Visio complains that that driver is not supported.

Any one run into this?


texts:
I have a VM which has 2GB Ram, (full specs)
And I am setting up a site which has one table in particular with over a million records.
There's little or no usage of this particular database (perhaps once or twice a day) but simply running mysql grinds the whole server to a halt. I've looked through the top results but nothing is really denting the CPU however the memory seems to be the issue.
The site isnt even live of taking requests yet. the memory situation looks like this:

# free -m
             total       used       free     shared    buffers     cached
Mem:          2006       1880        126          0          3         53
-/+ buffers/cache:       1823        183
Swap:         2047        345       1702

Are there any good pointers to tune mysql to stop hogging the system memory?

Thanks very much

EDIT: (requested by 8bit):

http://tny.cz/b41a0b12


texts:
I'm in the middle of designing a database, and have a question for the veterans in regards to scalability.

Currently, I have table A, which represents some object, and table B, which contains key-value pairs that describe objects in table A.

If an object can have fields such as:

type
color
taste
isState
etc

but doesn't necessarily have to have every field, is this an efficient model for scalability?  a year from now, properties of objects in table A may change -- I may need to add more or remove some, and this seems more manageable than schema updates.  However, it has the potential to create issues since all of my data is amalgamated into a single table.  If table A has 20, 000 records, and each of those records links to 10 different things in table B, I have 200,000 records stored in table B.

Will this cause indexing issues as the number of records in table A grows?  How does this compare in terms of a more traditional FKEY relationship design for things like a type and color table?

Any insight is most appreciated!


texts: Can a RDBMS connect to a remote server, execute a select query and copy them into a local table just with SQL commands?

texts: I'm sorry it may sound stupid but I've been wondering about how multiple databases are automated before the virtualization era. Can a DB Adminsitrator make a stored procedure to get data from a remote RDBMS and select them into a local table?


texts: Why does this simple transaction deadlock with MySQL?

texts:
I'm encountering intermittent deadlocks in the following proc:

DELIMITER $$
CREATE PROCEDURE pr_set_user_password (IN user_id INT, IN algorithm INT, IN hash VARBINARY(256))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;
        UPDATE `UserPassword` SET `Active` = 0 WHERE `UserId` = user_id;
        INSERT INTO `UserPassword` (`UserId`, `Active`, `Algorithm`, `Hash`) VALUES (user_id, 1, algorithm, hash);
COMMIT;
END $$
DELIMITER ;

This table has a composite index on UserId and Active (IX_UserPassword_UserId_Active).

Someone tried to run a load test that created many users in parallel, and they encountered the deadlock very consistently. Looking at the logs, I see that the deadlock occurs when this proc runs with adjacent user_id values.

I'm really looking for a real explanation of why this results in a deadlock. I've had various mixed and conflicting answers, none of which make sense, ranging from because one transaction has the lock and the other lock can't acquire it (even though there's only one lock in question) to that's just how MySQL works, deal with it.

See an example of the deadlock below:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-08-26 00:21:52 2b30caecc700
*** (1) TRANSACTION:
TRANSACTION 71925393848, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 4901, OS thread handle 0x2b30cc1d7700, query id 37117819 <server> update
INSERT INTO `UserPassword` (`UserId`, `Active`, `Algorithm`, `Hash`) VALUES ( NAME_CONST('user_id',72332427), 1,  NAME_CONST('algorithm',2),  NAME_CONST('hash',_binary'<data>' COLLATE 'binary'))
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 388 page no 117300 n bits 784 index `IX_UserPassword_UserId_Active` of table `users`.`UserPassword` trx id 71925393848 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 714 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 844fb48c; asc  O  ;;
 1: len 1; hex 81; asc  ;;
 2: len 4; hex 818106f9; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 71925393846, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 1
MySQL thread id 4909, OS thread handle 0x2b30caecc700, query id 37117815 <server> update
INSERT INTO `UserPassword` (`UserId`, `Active`, `Algorithm`, `Hash`) VALUES ( NAME_CONST('user_id',72332426), 1,  NAME_CONST('algorithm',2),  NAME_CONST('hash',_binary'<data>' COLLATE 'binary'))
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 388 page no 117300 n bits 784 index `IX_UserPassword_UserId_Active` of table `users`.`UserPassword` trx id 71925393846 lock_mode X locks gap before rec
Record lock, heap no 714 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 844fb48c; asc  O  ;;
 1: len 1; hex 81; asc  ;;
 2: len 4; hex 818106f9; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 388 page no 117300 n bits 784 index `IX_UserPassword_UserId_Active` of table `users`.`UserPassword` trx id 71925393846 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 714 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 844fb48c; asc  O  ;;
 1: len 1; hex 81; asc  ;;
 2: len 4; hex 818106f9; asc     ;;

*** WE ROLL BACK TRANSACTION (1)


texts: Mysql database replication (failover solution)

texts: Is there anyone know how to do "mysql database replication" with appfog ? In simple word, just want to use 2 databases sync (2 locations) and set one as master database, and other one as slave. if master server down, instantly slave server should act as master database. Actually databases are hosted on 2 shared servers. because lack of the space provided by appfog. :)


texts:
I am creating a stored procedure in sql server 2019 that needs to use multiple select statements to get a parent row and then its related data. I have the primary key clustered value for the parent so the first query will at most return 1 row.
Should I select everything into a temp table for the first query and then join my subsequent queries to the temp or should I just keep joining to the original table?
I am not sure if the overhead of creating the temp table will overshadow the overhead of joining to the actual table repeatedly.
I have looked at the performance plans and they come out to be the same and the statistics for reads/scans and time are about the same as well.
I think what I am trying to figure out is if I use the temp table, will it relieve pressure on the original table. The original table is heavily read and written to.
I should note that these statements will be inside of a Stored Procedure so I may potentially get a boost from Temporary Object Caching.
Assume table A has > 1 million rows and has 0-10 rows per entry in TableB and 0-10 per entry in TableC

Queries without temp table
declare @taID bigint=123

select
    ta.*
from
    TableA ta
where
    ta.ID=@taID

select
    tb.*
from
    TableA ta
    inner join TableB tb on ta.ID=tb.TableAID
where
    ta.ID=@taID

select
    tc.*
from
    TableA ta
    inner join TableC tc on ta.ID=tc.TableAID
where
    ta.ID=@taID

Queries with temp table
declare @taID bigint=123

select *
into #tmpA
from
    TableA ta
where
    ta.ID=@taID

select * from #tmpA

select
    tb.*
from
    #tmpA ta
    inner join TableB tb on ta.ID=tb.TableAID

select
    tc.*
from
    #tmpA ta
    inner join TableC tc on ta.ID=tc.TableAID


texts: Oracle 11g database archive log got increased suddenly in production server

texts:
My production db archivelog got increased suddenly one night. Around 200 MB of archivelog were generating in FRA within a minute. It got into normal condition after 15 hours from the start of this issue. We haven't done any unusual transactions during this time period.

There was no messages in alert or trace of db regarding this enormous increase of archivelog.

What could be the reason for this unusual behaviour of Oracle database?

Is this an issue with our database or Oracle ADF application or related to our servers?

Please let me if you require more inputs on this.

Any help will be appreciated.


texts: Maintaining or Re-Gaining access after SQL Server database restore without SYSADMIN rights?

texts:
I am struggling with maintaining or re-gaining access after restoring an SQL Server database (2008 R2, if that makes any difference). Here is what I am trying:

SQLServer login APPUSER is member of the dbcreator group, but not sysadmin
APPUSER can run CREATE DATABASE and will own the database - fine
SQLServer login APPUSER maps to the database's user APPUSER with dbowner permission if APPUSER runs RESTORE DATABASE using a backup that was created on a different server, then RESTORE DATABASE will complete fine, but APPUSER seems to loose all access to the database
I checked sys.databases/owner_sid of the restored database in
question and it matches sys.server_principals/sid of APPUSER. This makes me believe that APPUSER owns the database
I understand that inside the database, user APPUSER is no longer linked to the current SQLServer login APPUSER because the sid points to a user that was created on the other server and does not match the local APPUSER's sid

What I am trying to find is a way to have APPUSER restore a database and either maintain ownership or re-gain ownership without the need to have sysadmin rights. What does NOT work:

EXEC sp_change_users_login 'Update_One' ... - permission denied for APPUSER, works for SA
EXEC sp_change_users_login 'Auto_fix' ... - permission denies for APPUSER, works for SA
ALTER AUTHORIZATION ... - permission denies for APPUSER, works for SA

So what do I need to do so that my APPUSER can restore a database and use it, even if the DB was from another server without APPUSER having sysadmin rights?

From a permission management perspective, it should be sufficient to grant permission to a login so it can create and restore its own database (dbcreator) but not require sysadmin to restore if the database came from a different PC.

In general, after having restored the database as APPUSER, that login seems to have no access at all to the database. Any SELECT would fail as well.
This is surprising, since APPUSER has performed the restore and the content of sys.databases/owner_sid seems to indicate that APPUSER is indeed the owner. Granted, a different APPUSER (different sid) has originally created the database on another server, but this is what I am trying to fix. I have at least 3 different ways for fixing this with sysadmin permissions, but none through the APPUSER login.


texts:
I have common clause in most of the procedures like

Select * from TABLE A  + Joins  where <Conditions>

And
(
     -- All Broker
       ('True' = (Select AllBrokers from SiteUser where ID = @SiteUserID))
  OR
       (
         A.BrokerID in
                     (
                       Select BrokerID from SiteUserBroker where SiteUserID
                      = @SiteUserID)
       )
)

Basically, if the user has access to all brokers the whole filter should not be applied else it should get the list of brokers.

I am a bit worried about the performance as this is used in lot of procedures. Data has started reaching over 100,000 records and will grow soon, so can this be better written?

Any ideas are appreciated.


texts: MySQL 5.7 on W2K12 essentials - starts fine and then fails after restart

texts:
I have a W2K12 Essentials server that I'm trying to get MySQL 5.7 running on for in house development.  It installs just fine - I change install and data directories to two separate directories on the D:\ drive.

Service starts as normal.  I stop it, go in and change the Logon to Local System Account with Allow Service to Interact with Desktop (installer sets it to Network Service).  Restart service just fine.

Open up my.cnf to change the innodb_buffer_pool_size to 1G. Save changes.  Won't start (Services hangs on Starting).  Revert change back to 8M.  Service still won't start and eventually fails.

Event viewer shows "The MySQL57 service terminated unexpectedly.  It has done this 2 time(s)."

Task manager shows mysqld running but using only about 1.2mb of memory.

UPDATE - just uninstalled and reinstalled again.  Left the Log on as Network Service, but gave Network Service full permissions on my MySQL folder and my MySQLData folder.

Stopped and restarted numerous times.

Copy my.cnf to a safe place.  Change innodb_buffer_pool_size from 8M to 48M.  Won't start (again Task Manager shows 1.3mb memory usage).  Kill task.  Switch it back to 8M.  Still won't start.  Overwrite my.cnf with my backup copy and now it works again.  I am changing nothing else in that file and using notepad.exe to edit it.


texts: How to find the MAXSIZE for user databases from a connection to master in Azure SQL Database?

texts:
While it is simple enough to SELECT DATABASEPROPERTYEX(@DatabaseName, 'MaxSizeInBytes') when connected to a user database, I have been unable to find how to get this information for all of the databases when connected to master.

I want to complete this query:

SELECT  Name                =   d.name
        ,Edition            =   sdo.edition
        ,ServiceObjective   =   sdo.service_objective
        ,CurrentMaxGB       =   ds.?
  FROM  sys.databases d
        JOIN sys.database_service_objectives sdo ON d.database_id = sdo.database_id
        JOIN ? ds                                ON d.database_id = ds.database_id
 WHERE  d.name != N'master'
 ORDER  BY d.name;

However, I have not been able to find a DMV to use for "ds". Can you help?


texts: DateTime Conversion works for string value but for calculated value throws "out-of-range" error

texts:
So I keep getting an error and I have no clue what is prompting it. I have the SQL

STUFF(STUFF(STUFF(CAST(((ETADate * 10000) + ETATime) * 100 AS NVARCHAR),13,0,':'),11,0,':'),9,0,' ')

Which produces on the problem line the value :

'20161102 00:50:00'

When I call the following SQL, the DateTime conversion works just fine.

CONVERT(DATETIME, '20161102 00:50:00')

But when I try to put both together with the following:

CONVERT(DATETIME, STUFF(STUFF(STUFF(CAST(((ETADate * 10000) + ETATime) * 100 AS NVARCHAR),13,0,':'),11,0,':'),9,0,' '))

Then and only then, I get the error "The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value."

Does anyone here have any clue as to why the my nvarchar to datetime conversion works when I manually supply a string but fails when I try to convert from math that generates the exact same string? Any insight anyone has would be great because as of now this makes absolutely no sense.


texts: SQL Server Express Freezes for a couple minutes

texts:
I have SQL Server Express on an AWS micro instance. These instances have only 615MB of RAM. For the most part it performs fine. My application can make up to about 15 db connections.  However, every once in a while it just freezes for a couple of minutes.. my own application logs show all kinds of query time-outs, new db connection attempts failing because of an incorrect pwd (even though I am sure the attempt is using the correct pwd). But after a couple minutes everything turns out fine again.

I have checked eventvwr.msc logs but it doesn't reveal anything. I know there are not backups running at the time. And I have inspected SQL Server logs from SQL Server Management Studio but these too don't reveal anything.

Any ideas? Am I pushing the limits of SQL Server? I have read before the Express Editions run fine until certain connection thresholds get met. Could the number of db connections be an issue?


texts: MYSQL-BEFORE UPDATE Set value of Column A to value in Column B if Column A is to be updated

texts:
I have a nightly scheduled event which updates all rows in 'products' table with the newest 'unit_price' from a price list table where 'price_list.active-date' is >= the CURDATE(). What I need to do is create a trigger for the products table to set the field 'last_price' to the old unit_price before the update only on rows where unit_price is changing. Is this possible.

Scheduled Event:

CREATE EVENT `update_active_price` ON SCHEDULE EVERY 1 DAY
    STARTS '2015-02-09 00:00:00'
    ON COMPLETION PRESERVE
    ENABLE COMMENT 'updates nightly at midnight'
    DO
        UPDATE products p
        JOIN price_list pr ON p.product_id = pr.product_id
        SET p.unit_price = pr.amount
        WHERE active_date = CURDATE();

Currently non-working Trigger:

CREATE TRIGGER `update_last_price` BEFORE UPDATE ON `products`
FOR EACH ROW UPDATE products p
SET p.last_price = p.unit_price;


texts:
The table '/mnt/disks/data/tmp/#sql58de_a0f6_7' is full
The query is quite complex/big. Lots of joins in joins and unions, sorting, grouping, ...
1 out of 10 the result is returned. Otherwise it results in the above error.
CPU and memory increase but never max out while running the query.
There is plenty of disk space (100Gb free).
I have fiddled with:
innodb_log_file_size
innodb_log_buffer_size
innodb_temp_data_file_path
innodb_data_file_path
innodb_data_home_dir

max_heap_table_size
tmp_table_size

table_open_cache
table_definition_cache
thread_cache_size
sort_buffer_size

tmpdir and slave_load_tmpdir are set to /mnt/disks/data/tmp which has plenty of space and nevers runs out of space when running the query. innodb_tmpdir is set to NULL which means it falls back to tmpdir?
Some settings I've changed because of things I read about this issue. Others after mysqltuner advice.
But I could not let the query succeed consistently.
So: there is something running out of space. But what? And where?

Version: Ver 8.0.23 for Linux on x86_64 (MySQL Community Server - GPL)
Storage engine: InnoDB


texts:
On InnoDB cluster, I have tried to execute command 'show slave status' to get status of a slave.
But the system returns 0 row. So now I want to check the status of a slave, check which bin log file is reading on the slave for purging bin log on the primary server.
So how do I check the status of slave and delete applied bin log on primary safely?


texts: Some queries slower in MySQL 5.5 than 5.0?

texts:
We have an exceptionally inefficient process (1.3 million selects, 232k updates) that I'm now recoding to be 1 select and 50 updates. In the meantime, though, today's process ran.

We recently updated one of our slaves to MySQL 5.5, from MySQL 5.0, as a test run before we do it on our master. We're still running everything in MyISAM because we needed it up and running before attempting to do InnoDB optimization. The thing seems to be working fast enough, except for this process. The other slave, with identical hardware, finished it virtually in real time; this slave, however, is running over 2 hours behind because of it.

I'm curious, does anyone know if this type of thing (tons of tiny, simple updates) is slower in MySQL 5.5 for MyISAM in general? The settings between the two servers are as close as settings between a 5.0 and 5.5 can be, so it doesn't necessarily appear to be configuration. So while I can fix it by not having the horrible process, I'm worried that this is indicative of a deeper problem with our server. Should I be, or is it all kosher?

Edit: The problem appears to only be with large numbers of small queries. I just ran one and it was in the "query end" state for .133 seconds, or 89% of the query time. Only being able to run ten queries per second on a single thread seems pretty bad. Does 5.5 change things such that I need to modify my cnf file?


texts: import data from csv to mysql - good database structure

texts:
I have a collection of csv files with measurements with the following 54 columns:

SITE (string)
TIME (integer as yyyymmddhh)
PROD (double)
EST.0 (double)
...
EST.50 (double)

I want to read this to a mysql database. I would prefer a structure that would allow me to compute average and variance of values EST.0 .. EST.50, for each row.

What would be a good database structure for this data? Can I keep the columns as they are, or should I only have one "value" column? And if the former, how can I then get the averages and variances that I want?

From reading related questions, it looked like having so many value columns is not a good idea, so I was thinking about having a "value" column for all the double values, plus an "est_ID" column for an identifier, using -1 or NULL to denote the "PROD" value - does it make sense?

And what about the date-time column - will mysql understand it, or do I need to provide some information about date-time format?

As for handling multiple files, I guess that a small bash script calling mysqlimport should do the job nicely?

Thanks.

Michal


texts: How to check SQL database files for physical fragmentation?

texts:
I search the Internet and did not find clear explanation if I have to try defragmenting physical database files when my DB resides on SAN. I understand that there are tools and procedures inside some SANs that deal with that, but also there are SANs which lack that features.

My question has 3 parts:

How to check for physical fragmentation of SQL files?
Is it necessary to do this if the files reside on SAN?
Is it necessary to create a bigger physical database files to avoid fragmentation?


texts:
We have a proc that runs Truncate Table inside an Snapshot transaction.  This seems to be causing a LOCK_M_S lock that blocks the sys view sys.partitions.

Is there a convenient work around for this?  I like the efficiency of not taking excess logs that happens with truncate, but don't want to lock up my sys.partitions.

I'm happy to post the code upon request, but I'm pretty sure this is some behavior of Truncate being inside a Snapshot transaction. that i'm just unaware of.


texts: Tempdb data files are growing fast in SQL server 2014?

texts:
A few days back we have moved to 2014 from 2012 server and we kept the same settings as earlier like we are using 8 data files in Tempdb for better performance and all placed in single drive.

As this is data warehouse environment batch loads are running continuously and tempdb drive is getting full.Even though 98% free space in Database it could not able reuse space and throwing disk is full.

  Insufficient space in tempdb to hold row versions. Need to shrink the
  version store to free up some space in tempdb. Transaction
  (id=239368387 xsn=1273322 spid=126 elapsed_time=1590) has been marked
  as a victim and it will be rolled back if it accesses the version
  store. If the problem persists, the likely cause is improperly sized
  tempdb or long running transactions. Please refer to BOL on how to
  configure tempdb for versioning.

Question

Is there any specific settings for tempdb to avoid these issues in SQL Sever 2014?
Any recommendation for tempdb performance improvement like enabling trace flag -T1118 ?

In 2012 it was taking only 64 GB and now even 450 GB is not sufficient.

Server specification.

SQL Server detected 4 sockets with 15 cores per socket and 30 logical processors per socket, 120 total logical processors; using 120 logical processors based on SQL Server licensing. 512 GB RAM.


texts:
Possible Duplicate:
  How to take oracle 10g backup?

I followed this http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php link to create a db backup. I used this command to create db backup:

expdp scott/tiger@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

but I have this error message:

UDE-00008: operation generated ORACLE error 1033

ORA-01033: ORACLE initialization or shutdown in progress

please help me to resolve this issue.


texts: External compression tools for database backup files

texts:
Can I use external compression tools like WinZip, 7zip or winrar to compress rman backup files?

The DBMS is Oracle. A senior DBA claims that backups could be damaged by external compression tools. He argues that the tools shrink blocks which are not full and at a restore the database might have problems because control sums don’t match. I didn't find a source on that.


texts: Oracle 12.2.0 installation NLS_CHARACTERSET QUESTION

texts: Im creating fresh database 12.2.0 on linux(RH7.6) but not with GUI (dbca). Im installing it from scripts and which in file i can set parameter NLS_CHARACTERSET to EE8MSWIN1250 in pre-install process so I don't have to change it after install.


texts: Database design for ~ 2.7B rows per year / 26.000 data points every five minutes

texts:
The current situation

We're currently looking into a new product that will send device data back to us to interpret.

These are the numbers we're looking at:

Devices will most likely send data every 5 minutes
26.000 devices by the end of next year

26.000 INSERTS every 5 minutes. We will most likely have little control over the interval, so chances are that these 26.000 INSERTS are not evenly spread across these 5 minutes.
~ 2.733.120.000 data entries per year

Each packet will be JSON-formatted, with a size between 300 - 500 bytes.
We'er expecting about 8.000 new devices every year.

We currently manage several databases for our internal systems, but have little experience with volumes like this. We use AWS Aurora right now, which, in theory, should support 100.000 INSERTS p/s.

How will this data be used?

The data will primarily be used to create reports in our customer portals:

Real time reports of device metrics
Historical reports, IE.:

How did device stats look on Feb 2th, 2019?
What did week 12 look like?
Give me a summary of January's metrics
Show a graph of a specific column sum, grouped by month

The problem

To be quite honest, I find it pretty hard to make a solid choice, considering I don't have any hands-on experience with data-volumes like this.

Our current stack

We use a combination of AWS EC2 machines and an AWS Aurora cluster to manage our data. The ideal solution would be AWS-oriented.

Infrastructure I am considering:

Option #1: To keep things simple, storing everything into Aurora directly could be a good solution.

Option #2: But, to create a separation of our "real time" data and interpreted data, perhaps, something like this is better.

The actual questions

Is a MySQL-compatible database management system, like Aurora, suitable for something like this?
The incoming data will be used to generate "realtime" daily, weekly, monthly and yearly reports, aggregated per device. Would it be advisable to create separate tables for these different "perspectives" to make querying the data easier, or am I overcomplicating things and should I just store the measurements into one table?
Should we look into table partitioning?
Is there anything else that I did not mention, but we should look into?

If all of this is too vague, please let me know so that I can clarify the issue.

Would love to hear your thoughts.


texts: How to subtract an object from another in PHP/MySQL?

texts:
I am printing totalbooks with this code:

$query ="select sum(copies) as totalbooks from booksdetail";
$result=mysqli_query($con, $query);
while($row=mysqli_fetch_array($result)){
echo $row['totalbooks'];

and printing issued books with this code:

$query ="select count(id) as issuedbooks from issue";
$result=mysqli_query($con, $query);
while($row=mysqli_fetch_array($result)){
echo $row['issuedbooks'];

Now, I want to subtract issuedbooks from totalbooks.

Which code I should use?


texts:
I have a user who has DB_Owner permission on a particular database.
Now, that user needs to create a linked server for himself.
what level of permission do I need to grant to that user at the server level for him to create Linked Server?I don't want to grant him sysadmin access.
I gave following access:
Grant control server to login
grant alter any linked server to login

But didn't worked to create linked server.


texts:
Can the following be setup in Oracle11.2 for password authentication?

A new, non temp password should be atleast 24 hours old before it can be changed. System admin and authorized help desk personnel may change passwords of any age.
Users will change temporary passwords including system administrator issued passwords at the first login.

I don't see these conditions in oracle documentation.

Thanks in advance


texts:
I have a problem modeling a relationship whereby the child may or may not be related to a parent. For example. You have a Band database with a Tour(parent) table, and a Gig/Concert(child) table. Now, a Tour is a compilation of 1 or more Gigs, but a Gig may also be held off-tour. How do i model this without violating referential integrity?

E.G Tables:

    create table Tour(
    tourID int primary key,
    ...//
    );

    create table Gig(
    gigID int primary key,
    tourID int,
    ...//
    foreign key(tourID) references Tour.tourID
    );

PS: Newbie here


texts: I cannot do anything on replSet when auth is enabled

texts:
I have a mongodb cluster with one replSet, 3 config servers, and 2 mongos instances. I logged into mongos using (admin, pwd). admin is given ["userAdminAnyDatabase", "dbAdminAnyDatabase", "readWriteAnyDatabase"]. It works well. but when I logged into the replSet directly. I cannot pass authentication using (admin, pwd) again. I found that mongodb culster, replset, config servers, and mogos all have admin databases. They are different. What are the difference? what are the differences when doing authentication?

Can I create a (admin, pwd) in the admin database of the replSet? Will I have trouble when I add new replSet?

Thanks, Best,


texts: MongoDB Intermittent Little to No Insert/Write Activity

texts:
I have a MongoDB 3.2 WiredTiger Server running on Windows Server 2008 R2 Enterprise in Production while a MongoDB 3.2 WiredTiger Server running on Windows Server 2012 R2 in Testing. Production has 32GB and Testing has 16GB ram. MongoDB is the only thing running on these two servers.
We have a python script that runs in the morning and it ingests a large amount of data into a single collection.

EDIT: The data being ingested is from a large .csv file and not coming
directly from a SQL DB.

This happens every morning. There are two collections, Collections A and Collections B. One is active while the other is dormant. Each morning the dormant one gets loaded with the new data and becomes the active collection.
The problem is that the process to populate this collection takes about 12 hours to complete in Production while only taking about 4 hours in Testing. It's the same data, from the same .csv file, running the same python script, same python version, inserting into the same collections the same way.
Now Production is much more active while Testing has little to no activity. Though the resources on Production are not strained. The CPU usage is typically below 10% consistently. Using Mongostat and Mongotop it looks like our Production server can take 10s of seconds or more before doing any inserts while our Testing server is doing them practically every second.
Production:

Testing:

At first I thought perhaps this was due to an IO bottleneck with the HD. Our Production server was running on a 15k HDD at the time while Testing was on an iSCSI disk array which is much faster. So we worked on getting Production setup like that too. It improved the ingestion by maybe an hour.
I've spent weeks researching this and I am at a loss as to why our Production server take 3 times longer to ingest the same data as our Testing server. At this point it seems to have something to do with either the activity in Production or the difference in Operation System.
Can incoming activity prevent Mongo from making inserts even though there's plenty of CPU and disk IO to do both? Can the OS cause such a massive disparity? What else should I look at?


texts: Too many WAL files in archive folder PostgreSQL Streaming Replication

texts:
I have two Postgresql 9.5 servers configured in streaming replication. There are no errors in log files and the servers seem to be in sync.

MASTER postgresl.config

archive_command = 'test ! -f mnt/server/archivedir/%f && cp %p mnt/server/archivedir/%f'
wal_keep_segments = 32

Everything else is pretty much default. And yet I have 1600+ files in the archive directory! Because of space constraints I have dared to delete some of them. I would like to figure out what is causing this. I also see that there are only very old files on the slave for the same directory.

The only thing I can think of is that I recently did a restore on a database. I dropped the database, created it and did a backup restore. I did this several times. The slave seems to have caught up with the changes. But I have a ton of WAL files in the archive folder for each of the backup restores. Would appreciate some insight into what could be happening.


texts:
I'm working on an ERP project for an educational institution. They have two branch offices and we need to keep records not only for customers, but also class calendars, class reservations for teachers.

At the beginning I'll start with a cloud based web application. However, in the future I will need to replicate database to two additional physical addresses. Power outage and loss of internet connection are not rare in the city.

What replication model do you recommend? Master-Master, Master-Slave, or Mysql Group Replication? What about MySQL Cluster?


texts: SQL Server Management Studio highlights a word that does not appear to be a reserved word

texts:
I don't like to name objects with reserved word (i.e. Set) for all the obvious reasons.

So when SSMS highlights a word in blue, I rethink my naming of the object.

Every once and a while I see a word highlighted that does not appear to be in the list of reserved words. In this case the word pool.

Does SSMS use a more strict convention for highlighting reserved words?


texts: how can I create the pie chart that displays the Count of columns with filters like (  Active Paths / Courses / Sections /----- this is my column name )from 4 different tables and combined them in one query?


texts: Changing replication snapshot folder, require re-snapshot?

texts:
We have SQL Server 2012 SP2 running on Windows Server 2012.

We need to change the snapshot folder for all (including default) transaction replication publications.

I have read that this means the replication needs to be re-initialized?

Why is the case? If I update table msdb.dbo.MSdistpublishers with the new working_directory then I can't see why the replication should need to be re-initialized?

Any recommendations appreciated.


texts: Should I disable the guest user on system databases

texts: The guest user is disabled on our user databases but data security guys want us to disable it on system databases as well. Should I do it?


texts:
We have recently setup XA Transactions as per the documentation.

However, app team is facing the issues as below

Performance Test 1: XXXX Internal users [get XXXX token API ] – 3 concurrent users with three different accounts for 10 minutes duration. [04:46 - 04:57 AM EST] – No Errors

Performance Test 2: XXXX Federated users [get XXXX token API ] - 3 concurrent users with three different accounts for 5 minutes duration. [05:27 - 05:33 AM EST] – No Errors

Performance Test 3: XXXX Federated users [get XXXX token API ] - 3 concurrent users with 20 different accounts. [05:47 - 05:49:36 AM EST] – Errors were observed

and errors says as below

  {"error":"org.hibernate.exception.LockAcquisitionException: could not
  execute query"}

  {"error":": Unable to acquire lock after 10 seconds for key
  f:6038d4995878:ptuserfafn375759570511 and requestor
  GlobalTx:local:754. Lock is held by GlobalTx:local:732"}

I checked DPA Monitoring Tool and found blockings and deadlocks between 05:47 - 05:49:36 AM EST. Victim and Survivor are running the same SQL, from same machine except different Txn/Owner ID.

Checked and validated System/SQL Server error logs, with nothing unusual.

Any thoughts please?


texts: Substantial difference between database size under MySQL and actual size on disk

texts:
When I check the size of my databases under MySQL I get this:

MariaDB [(none)]> SELECT table_schema "Data Base Name",  sum( data_length + index_length ) / 1024 /  1024 "Data Base Size in MB",  sum( data_free )/ 1024 / 1024 "Free Space in MB"  FROM information_schema.TABLES  GROUP BY table_schema;
+--------------------+----------------------+------------------+
| Data Base Name     | Data Base Size in MB | Free Space in MB |
+--------------------+----------------------+------------------+
| alfresco           |         245.75000000 |      34.00000000 |
| drupal             |         892.15625000 |     216.00000000 |
+--------------------+----------------------+------------------+

When I check the size on the disk I get this:

$ sudo du -h --max-depth=1 /var/lib/mysql/
317M    /var/lib/mysql/alfresco
1.4G    /var/lib/mysql/drupal

If I combine both used and free space given by Maria DB and compare it with disk figures I have the following:

alfresco: DB=279MB  DISK=317MB (+14%)
drupal: DB=1100MB   DISK=1433MB (+30%)

Q: Is it normal to have that much overhead on the disk / Is there anything I can do to reduce it?

FYI I thought running a mysql optimize would help (using that command), it did reduce the size of the databases, but didn't change the size of files on the disk.

Additional info:

server:             ubuntu server 10.04 LTS
DB server:          MariaDB
DB engine:          InnoDB v10 (for all tables)
Table collation:    utf8_general_ci
Nb Drupal tables:   416  (0.80MB overhead per table)
Nb Alfresco tables: 84   (0.45MB overhead per table)


texts: Display multiple IP address values for one system in a single row

texts:
I cannot for the life of me get the systems with multiple IPs to display under one row. Below is the current SQL statement I have. systems with more than one IP show up as duplicated on the report.

SELECT SYS.Netbios_Name0 AS [Computer Name],
       SYS.User_Name0 AS [Last Login User],
       SYS.User_Domain0 AS [User Domain],
       Sys.Resource_Domain_OR_Workgr0 AS [System Domain],
       v_GS_Computer_System. Manufacturer0 AS [Manufacturer Name],
       v_GS_COMPUTER_SYSTEM.Model0 AS [Model Name],
       v_GS_COMPUTER_SYSTEM.SystemType0 AS [System Type],
       v_GS_PC_BIOS.SMBIOSBIOSVersion0 AS [Bios Version],
       v_GS_PC_BIOS.SerialNumber0 AS [Service Tag],
       v_GS_COMPUTER_SYSTEM.NumberOfProcessors0 AS [Active Processors],
       v_RA_System_IPAddresses.IP_Addresses0 AS [IP Addresses]

FROM v_R_SYSTEM SYS
LEFT JOIN  v_GS_COMPUTER_SYSTEM on SYS.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
LEFT JOIN v_GS_PC_BIOS on  SYS.ResourceID = v_GS_PC_BIOS.ResourceID
LEFT JOIN v_RA_System_IPAddresses on SYS.ResourceID = v_RA_System_IPAddresses.ResourceID


texts:
Let's assume I do a very simple query selecting 2 columns out of the 5 columns in a table. How do I name the query table for future use?
Sorry for asking something that sounds so obvious but have looked for answer everywhere and could not find any.


texts: Installing SQL Server 2012 Standard on a machine with more than 64GB RAM

texts:
I’m in the process of doing the specification for a new server that will host SQL Server 2012 including  SSIS, SSRS and SSAS

I’m aware SQL Server 2012 Standard has a limit of 64GB. However from what I’ve been reading today it looks like that limit is just for the database engine and we can install SQL Standard on a machine that has more than 64GB Ram; and SSIS, SSRS have their own separate 64GB limit.

Can anyone confirm that my understanding is correct?

thanks!


texts: Importing mysqlnd database to MySQL server

texts:
Importing a mysqlnd (5.0.12) database to MySQL server (5.1.66) causes error? Currently we are performing this task and encountering errors (like below):

  ERROR 1146 (42S02): Table 'xx-xxx-xxx-xxx' doesn't exist

  ERROR 1273 (HY000): Unknown collation: 'utf8mb4_unicode_ci'

  ERROR 1115 (42000): Unknown character set: 'utf8mb4'


texts: Approaches to prevent database slowdown when another process is hitting it hard

texts:
I'm a developer not a dba, but this task was given to me...
One of our databases is hit hard by a process which reads and modifies hundreds of thousands records onece or twice a day, during the time this process is running our other services who rely on this database get very slow.
What are some "common" approaches to solve this kind of problems?
Things I have thought that could work:

Using "NOLOCK" in the read queries
Using a cache service in front of the database, like Redis
Using database redundancy

My questions are: Could any of these 3 options works? and if so, what kind of results can I expect. What other options do I have?


texts: Added a unique index, but cannot find it in TABLE_CONSTRAINTS

texts:
I'm running the following command in my DB:

CREATE UNIQUE INDEX [UK_NAME1] ON [TABLE1] ([COLUMN1] ASC);

Now, I'm running the following query:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

And yet I can't find my unique index listed in there.

Am I using the wrong query? What other issue could there be?


texts: Use data from Mysql to ElasticSearch with Logstash

texts:
I'm using logstash for use my mysql database in ElasticSearch

My conf is the next.

input {
    jdbc {
        jdbc_connection_string => "jdbc:mysql://[ip]:3306/nextline_dev"
        jdbc_user => "[user]"
        jdbc_password => "[pass]"
        #schedule => "* * * * *"
        #jdbc_validate_connection => true
        jdbc_driver_library => "/path/mysql-connector-java-6.0.5.jar"
        jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
        statement => "SELECT * FROM Account"
    }
}
output {
    elasticsearch {
        index => "account"
        document_id => "%{id}"
        hosts => ["127.0.0.1:9200"]
    }
}

But I have some questions, I want to schedule more than one query, but the index will be always account.

Can I make a dynamic index for the output to elasticsearch? And how can I use more than one statement? (Export more than one table)


texts: AlwaysOn Availability Group Automatic Failover does not work

texts:
Playing with AG setup I have the WSFC up and configured with two nodes in one availability group called DevClusterOnline. Both nodes (DEV-AWEB5 primary, DEV-AWEB6 secondary) are running Windows Server 2008 R2.

If I check the health of my AG I get this:

Running the query below will return this result set:

select
    ar.replica_server_name,
    availability_group_name = ag.name,
    ar.availability_mode_desc,
    ar.failover_mode_desc
from sys.availability_replicas ar
inner join sys.availability_groups ag
on ar.group_id = ag.group_id
order by availability_group_name, replica_server_name;

If I disconnect DEV-AWEB5, I cannot connect to the Group Listener (DevListener), but I can ping it and it will respond to my ping. The replica - DEV-AWEB6 goes into a RESOLVING state and my DB is unaccessable. I can however, manually go into Management Studio and set Failover to DEV-AWEB6 and then I´m up and running again and DevListener will once again accept connections.

Considering that these facts confirm that the failover actually works, that I have synchronized commits and automatic failover configured I have no idea what if malfunctioning in my setup.

When I disconnect DEV-AWEB5 I expect that my replica will retain connection and thus, DevListener too. I expect that the automatic failover will allow me to connect to the AG Listener transparently. From an end-user perspective, using a Web system it should not be noticable that one of the DB servers go down.

I am stuck here, can anyone please enlighten me on what I am doing wrong?


texts: SSMS: Show execution plan for a stored procedure as a single tree

texts:
I execute a stored procedure with an option "Include Actual Execution Plan". The execution plan always is represented by multiple statements each of which has its own execution plan.

MSDN states the following:

  If the statement is a stored procedure
  or Transact-SQL statement, it becomes
  the root of the graphical execution
  plan tree structure. The stored
  procedure can have multiple children
  that represent statements called by
  the stored procedure. Each child is a
  node or branch of the tree.

In this case I expect to run a stored procedure and see only one tree where the root node is the stored procedure.

How it is possible to switch on such plan visualization for a stored procedure?

UPDATE:
It looks like an estimated subtree cost is not the same for actual and estimated execution plans.

Is there a way how one can see Estimated execution cost for the whole stored procedure and not only for its statements?


texts: MySQL on Unix, should administrator accounts join the operating system "mysql" group?

texts:
When I install MySQL via Yum, it automatically creates the mysql operating system user and group.  Then mysqld executes as mysql (instead of as root), and all the files in the data directory are owned by mysql:mysql.

This is obviously correct security practice, I understand why this happens and don't want to subvert it.

When I'm administering a MySQL system, I've just gotten in the habit of using sudo when I need to interact with a protected file (e.g., reading a binary log with mysqlbinlog, creating a backup tarball, etc).

Would it also work to add my OS user to the mysql group?  Are there major downsides of this I'm not considering?