texts: SQL SERVER 2005 slow Performance on Windows Server 2008

texts:
Recently I have upgraded my server to new one. But because of compatibility issues with my old Windows Server 2003 R2 32 bit, I have also upgraded it to Windows Server 2008 R2 64 bit.

(I have install Windows Server 2003 32 bit & SQL Server 2005 32 bit on new hardware and run without problem though sometimes Windows freezes because of compatibility issue with the new hardware).

After upgrade to Windows Server 2008 R2 64 bit, I still use the old SQL Server 2005 R2 32 bit. I can install SQL Server 2005 R2 32 bit on Windows Server 2008 R2 64 bit and make it run. The problem is that it runs slower than on my old Windows 2003.

What I did for this migration:

Detached the LDF & MDF file and kept a backup.
Install new Windows and SQL Server instance.
Attached back the LDF & MDF file.

Is there anything I have missed or is there also compatibility issue using 32 bit SQL Server running on 64 bit Windows Server?


texts: SQL Server 2005 Index Rebuild/Reorganize

texts:
I have checked my huge db and run some shrink tasks for 5 or 7 times. I got about 11 GB freespace. This DB is used for offline reporting operations. Before, when we create a report it was growing about 2 or 3 GB ( this is also strange but there about 6 millons lines of CDR logs imported and processed ) but last time we tried. Even if the free space is about 11 GB. Reports can not be generated. Disk spaces being empty unexpectedly.

So make some search and see some advices to check the indexes. According to the indexes fragmentation there may be some disk spaces issues.

Firstly; does index fragmentation causes disk spaces unefficient use ?
Second; what would happen if I make index reorganize or rebuild ? Of course there is a risk of DB crash always but would it be harmfull for the DB ?


texts:
I'm new at creating databases, and this error has me dumbfounded, as I am super new with DB admin things (I mostly do reporting type queries). I created a new database through pgAdmin3 GUI, and I'm trying to create DB objects in there using SQL but am getting a:

ERROR: relation "replays" does not exist
SQL state: 42P01

I looked through the manual but did not find anything very helpful, though I suspect it may have to do with search_path somehow. Here is a screenshot. Any idea what I'm doing wrong please?


texts: Logging and Replaying database updates/inserts SQL Server 2008

texts:
I am in the process of migrating our client's database to the Azure Cloud SQL Server and setting up data sync etc. All of this is ok, but I need to reduce the amount of time the client's system needs to be down for to complete this process.

I was hoping to be able to set up a process that records all database inserts/updates from the time I create the backup which will be used in to migrate the data to azure, and then replay them on the database once it's in the cloud.

Is there a tool for this? Is SQL Profiler good enough for this?


texts: Most efficient way to compare strings in SQL Server 2014?

texts:
Hope everything is going ok.
So I'm in an awful situation. I have these two tables, and I need to check that there are not duplicated people. The duplicates are shown to the user in the front end:

Table 1: Id, SSN, First Name, Last Name, Birth Date, Age Gender, Address (yeah, this includes everything: street, cp, city), Email, Cellphone.
Table 2: SSN, First Name, Last Name, Gender, State (its a tinyint) and other data that isn't relevant (a bunch of datetimes)

So, the problem is that the data from table 1 was sent to us in a csv. But this csv wasn't generated by a computer, someone just sat and typed around 4,000 records. So it's likely that there are errors in the NSS. While false positives do not matter that much (there will be an extra page where the user can double check if those two people are exactly the same), it is really important that the system doesn't throw false negatives.
Another problem is that the data from second table will be captured by external users (a bunch of people that are not part of my organization), and I have to consider that they can have typos as well (in the SSN, capitalization, perhaps event accents that the ones who captured the data on the first table didn't include). Both datasets aren't reliable. Yet, the less reliable is the first one, at least we are building the page where users will enter the data that'll fill the second table and we can place some validation to make sure they don't include extra whitespaces or things like that.
Currently, im comparing like this:
SELECT NSS, CONCAT_WS(' ', FirstName, LastName) AS Name, Email, Cellphone
 FROM dbo.Table1 T1
     JOIN dbo.Table2 T2 ON (T1.SSN=T2.SSN
ON (TRIM(T1.FirstName) COLLATE Latin1_General_AI_CI LIKE '%'+TRIM(T2.FirstName)+'%'  COLLATE Latin1_General_AI_CI
AND TRIM(T1.LastName) COLLATE Latin1_General_AI_CI LIKE '%'+TRIM(T2.LastName)+'%'  COLLATE Latin1_General_AI_CI))

While it gets the job done, the performance is terrible (leading wildcards). It takes up to 4 minutes to give the user the result (the users of that page are people of my organization, so in the worst case scenario I can simply tell them that the results are going to take a while whenever they want to check it, it'll be slower if they compared it manually anyway). So I'm trying to find a faster way to do it which doesn't sacrifice it's reliability. Sadly, asking for a second and cleaner csv is out of the question (lots of papers and time that we don't have).
The first table haves like 4,000 entries. For the second one, we are expecting it to have between 2,500 and 3,000 records at the end of the registration.
Thanks for your time! Take care!


texts: Selecting words that are phonetically similar with different beginnings

texts:
I'm noticing a pattern with the returned results of the MySQL soundex function: if it starts the same and ends different, it's a match, if it starts different and ends the same, it isn't. Examples:

SELECT
  SOUNDEX('off') AS off_soundex,
  SOUNDEX('oph') AS oph_soundex,
  SOUNDEX('oaf') AS oaf_soundex;

All return "O100", which would be a match. However:

SELECT
  SOUNDEX('cite') AS cite_soundex,
  SOUNDEX('sight') AS sight_soundex;

returns S300 and C300 - not a match.

SELECT
  SOUNDEX('sity') AS sity_soundex,
  SOUNDEX('city') AS city_soundex;

Also return S300 and C300. What this means is that someone can mistype "city" as "cite" and find city but not if they mistype it "sity". Quite why "sight" is a match for "sity" is just strange: since soundex("height") returns H230, I would have thought that "sight" would return "S230" - of course this would break the "sight" "cite" match and fixing it would break the "cite" "city" match. It's as if they're trying to shoehorn a one-to-one relationship into phonetics where one does not really exist.

What I'm wondering is, short of writing a lookup table of known words and likely mispellings, is there a better solution to this?


texts:
Our server & Backup data got crashed and we had a recovered the data using the recovery Tool. Currently I am having .BAK Files.
When we are trying to rebuild the DB using the recovered .bak file getting the Errors

when i try restoring through query

RESTORE DATABASE dbName
   FROM DISK = N'Path\To\Backup.bak'
   WITH MOVE 'dbName_DataFileLogicalName' TO 'Path\To\Data.mdf',
        MOVE 'dbName_LogFileLogicalName' TO 'Path\To\Log.ldf',
       REPLACE,
       STATS = 10

i get the error as

  Msg 3241, Level 16, State 0, Line 1 The media family on device
  'D:\DB\NASH_20160718.bak' is incorrectly formed. SQL Server cannot
  process this media family. Msg 3013, Level 16, State 1, Line 1 RESTORE
  DATABASE is terminating abnormally.

please help thanks !!


texts: Implications of Changing to a Static Port

texts:
I currently have an SQL Server hosting two production instances which have numerous web apps accessing them. As far as I know, none of these apps are using a specific port in their connection strings, nor am I aware if this even matters.

My questions is, what are the implications of assigning theses instances to static ports? Will this break my apps? Are there other adjustments I need to make or consider before doing this?

Reason for asking? My infrastructure team has requested that I assign static ports for the purpose of assigning SPNs for Constrained Delegation rights. Thanks. ;)


texts: Column sort order in CREATE INDEX statement?

texts:
When creating an index one can determine if an index column is sorted using ascending or descending order. BOL says that

[ ASC | DESC ]
Determines the ascending or descending sort direction for the particular index column. The default is ASC.

What is the primary reason for choosing between ASC or DESC?


texts: What if we skip the slave by counter 1 when it failed at event 1 of a transaction which has events 2 and 3

texts:
We have a master-slave replication which has all Innodb data.

I ran 3 update statements under one transaction on master.

 =============
 start transaction
 update T1
 update T2
 update T3
 end transaction
 =============

Through replication,these went on to slave.

With some error, replication stopped while executing first update(update T1) on salve.

Now if we issue set global sql-slave-skip-counter=1 on slave and start slave, will it skip update T2 and update T3 also ?

NOTE: All tables T1, T2 and T3 are Innodb tables

Thanks in advance.


texts:
I have a script that detaches databases for archival when they meet certain criteria. The script is executed by SSIS under the local Administrator account by the Ghost of DBA Past. Per design it (by this I meant detaching databases in general) removes all other ownership and makes ownership exclusive to the local Administrator account. Here's where it gets weird:

I can't seem to change permissions back except via two methods:

Windows GUI properties/Security etc.
Copying the files to a sub directory that has inherited different permissions.

What I can't get to work:

PowerShell Set-Acl
PowerShell Move-Item
Moving/cutting the files in Windows GUI
icacls

I understand why ownership changes, but not why it's so difficult to edit the permissions afterwards, and why the files seem to essentially be hidden from command line tools.

Reason for the change is that sometimes data needs to be put back online before it goes to tape for whatever reason, and I can't attach the database(s) without changing permissions first.

Thanks


texts: SSIS: Check file for row delimiter and dynamicly update row delimiter in Flat File Connection Manager at run time

texts:
I am importing about 50 or so txt files into a SQL Server table. All the files have the same layout except that for some files the row delimiter used was {CR}{LF} and for others {LF} was used.

Ideally I will be asking the person who creates the txt files to stick with the same delimiter moving forward but I would like to know if there is a way that I can scan the file (maybe using a C# script) in order to capture the delimiter used in the file to then be able to update the Flat File Connection Manager's Row delimiter dynamically at run time.

Is this possible? If so, does anyone have an example of how to accomplish (or a better solution)?


texts: SQL Backup/Restore and Disaster Recovery solutions with VMware

texts:
Is vSphere Replication safe for SQL databases?

We are a virtual environment (Cisco hardware + VMware hosts + HP san).
We have a primary and disaster recovery datacenter, with SAN-level replication already setup for every 4-hours.

There are several HA/DR solutions for SQL - alwayson, logshipping, clustering, replication etc. But we want the easiest administrative solution that can deliver good RPO & RTO. So we're looking to replicate at the VM-level instead
of SQL-level strategies.

Apparently, any VM-level snapshot/backup solution needs to be VSS (Microsoft's volume shadow service) aware to ensure highly transactional applications like Exchange or SQL Server comes back up in a consistent state after restore.

VMWare's SRM(Site Recovery Manager) gives you the choice of array based replication or vSphere Replication. I read here that vSphere Replication is VSS aware and can offer utmost 15 minute RPO, which is very acceptable for most of our applications.

Does anyone else use this for enterprise oltp applications with 15-minute DR RPO (not high-availability) ? Does it freeze IO on SQL server for unusable period of time ? Does it definitely ensure consistency when VM turns back on at the DR site ?


texts: In postgres, is using a lock better than transaction when reading data?

texts:
I have a race condition in the code for my database, and I'm wondering how I can prevent this using either locks or transactions? (And which is best)

I'm using an ORM to update values, however the ORM doesn't support either locks or transactions (though it does support native queries so I'll have to do some of that).

But the code can be summarised to the following:

  I wish to update (of an object A) the reference to table X, from id 1 to 2. - The orm keeps these as simplistic numerical ids, no foreign keys.

  The maximum number of references to an id in table X is based on the corresponding "spots" column in that table.

  So before updating first the amount of free spots is checked, then some further checks are done (calculatedPrimeNumber(1000)). And finally the field is updated.

Or in real terms: I have a tables entries which have a reference to events.

Whenever I wish to update the event of an entry, I check how many free spots there are in the event. (based on events.spots and the amount of entries that have the reference to said entry).

I do some further checks. And then update it.

Now the problem starts when there are multiple requests to update the event data at once: suddenly it can happen that two requests succeed, even though there is only a single spot in the event.

What is the best way to prevent this? Using transactions or how should i lock the database?

So far I can only find the ability to have locks within a single transaction/command. However this would not allow for server level checks as in:

entries = rawQuery('SELECT * FROM public."entry" WHERE id=' + newEventId)
events = rawQuery('SELECT * FROM public."event" WHERE id=' + newEventId)
if (events[0].spots < entries.length) {
    //stop handling, return error that updating cannot happen
}
//DO SOMEFURTHER CHECKS
rawQuery('UPDATE public."entry" SET "event"=' + newEventId + ' WHERE id=' + entry.id)


texts:
We have a relatively low-volume Postgres database with continuous archiving set up to compress each WAL segment and send it to S3. Because it's a low-volume system, it hits an archive_timeout every 10 minutes or so and archives the mostly-unused WAL segment, which used to compress very well as it was mostly just zeroes.

However, Postgres recycles its WAL segments to avoid the cost of allocating new files at each WAL switch, which is useful in a high-load situation but it means that after a burst of heavier-than-normal activity our WAL segment files are now full of junk from previous segments and do not compress very well at all. We're storing a lot of copies of all of this junk.

Is there a way to reduce the amount of space we're using to keep our WAL archive? Some suboptimal possibilities:

Prevent Postgres from recycling the WAL segments somehow, so it starts out with a zeroed file each time. The docs do not indicate that there is an option for doing this but I might have missed it.
Have Postgres zero the WAL segment file when it starts/finishes using it. Again, the docs do not seem to suggest this is possible.
Externally zero or remove some of the WAL segment files while they're not in use. Is there a safe way to determine which files this is?
Zero the unused portion of the segment before archiving it using the output from pg_xlogdump to find where the junk starts. Possible, although I don't fancy it. At least by doing this in the archive command you can be sure that Postgres isn't going to reuse the file.
Only archive the used portion of the segment file, again by interpreting the output of pg_xlogdump somehow, and then pad it with zeroes during restore. Also sounds possible although I don't really fancy it.


texts:
I am new to MySQL administration, and this week a customer came to me with a problem. They created an application with Wamp in a Windows Server environment.

For some reason when listing the tables in the MySQL Workbench, an error pops up saying could not be fetched.

And in the output window, the following error is thrown:

They also reported that a stored procedure has disappeared, and when trying to compile it again in MySQL, another error is generated:

ERROR 1031 (HY000): Table storage engine for 'proc' doesn't have this option

Some tables were using the MyISAM engine, so I changed it and now all are as INNODB, and the ROW FORMAT parameters are like Do not Use.

Other information that may be important, this base was developed in a version 5.1, and this base with problems is in version 5.7.14.

UPDATE:  I just tried to run mysqldump, to import on another server.
And this error was thrown:

d:\wamp64\bin\mysql\mysql5.7.14\bin>mysqldump.exe -u root --routines  --all-data
bases --force > dump20170912.sql
mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'bejarti'': Table s
torage engine for 'proc' doesn't have this option (1031)
mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'berbigier'': Table
 storage engine for 'proc' doesn't have this option (1031)
mysqldump: Got error: 1031: Table storage engine for 'proc' doesn't have this op
tion when using LOCK TABLES
mysqldump: Couldn't execute 'show create table `proc`': Table storage engine for
 'proc' doesn't have this option (1031)
mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mysql'': Table sto
rage engine for 'proc' doesn't have this option (1031)
mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'new_schema'': Tabl
e storage engine for 'proc' doesn't have this option (1031)


texts: Oracle RAC Failover functionality clarification

texts:
we are running two node active/active Oracle11g R2 RAC cluster i need to replace public network cables on each node. I am planning to do as given below:

Setup:

OS: oracle Solaris 11.1 Sparc
Database : oracle11g R2 RAC dual node cluster using ASM
Middle tier : Microsoft Dot net apps servers
Client tier :  Internet explorer ( BUI )

Public NW Cable replacement Action Plan:

Prepared and ensured the 2 new Public network cables one for each node
Check and verify the Database status using srvctl
first from node 1 i will remove the cable from both (switch port & server port)
simultaneously and replace it with the new cable with in few seconds.
Check and verify the instance status on the new cable (node1) and wait for 5 minutes
Repeat the steps 2-3 for the node2.
Check and verify the instance status and wait for some time to ensure the both the
instacnes are up and running
Test the application

Note:
Initially check and ensure the Apps servers tnsnames.ora  configuration ( SCAN Names / VIP/node's local ip  ) Using SCAN-Names recommended if not observe carefully the TAF behaviour on the node side and the application side as well )

Kindly review the above steps and guide me.

MShafi - Senior System admin
Abu Dhabi - UAE


texts: How to find MySQL users causing high load?

texts:
I have a MySQL server with multiple websites running on it. These sites are mostly the same software, but since recently MySQL is under very high CPU load.

I'm pretty sure there is 1 MySQL user that is causing the higher load, but how can I identify the resources used per user?

I know I can limit the number of connections and number of queries per user, but before enforcing limits, I want to know what the current numbers are.

How can this be measured (in MySQL 5.7)?


texts: I would like to ask if MS Access database supports working in networks & multi-user access?


texts:
unitid | row1    | row2   | row3   | row4    | row5    | row6
-----------------------------------------------------------------
200    | sample1 |sample2 | sample3| sample4 | sample5 | sample6

expected OUTPUT

200
sample1
sample2
sample3
sample4
sample5
sample6


texts:
At the moment I have a table setup that looks somewhat like this:

create table tbl_locationcollections
(
   id int(11) PRIMARY KEY IDENTITY, --(Primary Key),
   name varchar(100) not null,
   cleanname varchar(100) not null,
   typeid int(11) not null (foreign key)
)

create tbl_locationcollectiontypes
(
   id int(11) PRIMARY KEY IDENTITY,  --(Primary Key)
   type varchar(100)
)

a type would be something like: country, state, city etc etc.

I would then join to the various type tables like this:

create tbl_states2locationcollection
(
   id int(11) PRIMARY KEY IDENTITY,  --(Primary Key)
   stateid int(11) not null, --(foreing key)
   locationcollectionid int(11) not null --(foreign key)
)

with other tables like tbl_cities2locationcollection etc etc.

Is this style of seperation better for speed and readability than having a table such as:

create tbl_locations2collection
(
   id int(11) PRIMARY KEY IDENTITY, --(Primary key)
   locationid int(11) not null, --(foreign key to the type of: state, country, city)
   typeid int(11) not null --(foreign key)
)

where all the different types are mixed in together.

the only downside i can see for not having a mixed table, is having to create a specific table for each type that is created in the future.


texts:
I was just studying about the 3NF and the BCNF and then encountered a statement that:

  3NF ensures lossless decomposition while BCNF does not.

How can we verify this using the basic definition of BCNF and 3NF?


texts: Decrypt the DSN section of RsReportServer.config file

texts: The DSN section of the RsReportServer.config file contains the encrypted connection string to the PowerBI Report server databases. I noticed that the DSN string changes everytime I make any changes using report server configuration manager. Is there a way to decrypt the DSN section.


texts: SQL Server: Exporting data objects While exporting Database.

texts:
Say I want to copy a database from one place to another. I can use, e.g.

Insert * From DB1 Into DB2

Where DB2 is the target database. Question: will this insert query also copy all

the data objects: Indexes, triggers, etc.  from DB1 into Db2 ? If not, will

the export Wizard to the job? Anything else?

Thanks.


texts: Ensure percentages are between 0 and 1, inclusive (using a single function)

texts:
I have percentages in a condition table:

create table condition (percent_decimal number(3,2));
insert into condition values (-0.01);
insert into condition values (0.1);
insert into condition values (1);
insert into condition values (1.1);
commit;

PERCENT_DECIMAL
---------------
          -0.01
             .1
              1
            1.1

I want to select the values, but modify them to present them as percentages between 0 and 1 (inclusive):

Convert -0.01 to 0
Leave .1 as is
Leave 1 as is
Convert 1.1 to 1

I can successfully do this using the greatest and least functions:

select
    percent_decimal,
    least(1,greatest(0,percent_decimal)) as percent_modified
from
    condition

PERCENT_DECIMAL PERCENT_MODIFIED
--------------- ----------------
          -0.01                0
             .1               .1
              1                1
            1.1                1

However, I'm wondering if there is a more succinct way of doing this--with a single function.

I'm often pleasantly surprised by the clever techniques that DBA-SE members come up with. When I think I've come up with the simplest way to do something, someone comes up with something even simpler.


texts: Max Server Memory configured but it increases more of its configuration

texts:
Following a previous post I've written, I've configured my server with the following parameters:

I put 22497 as MAx Server Memory, disable AWE, since it runs in 64 bit environment and it's a production server.
During a reboot, it starts from the min configuration, and slowly increase memory consumption until almost midnight that reaches the max memory configuration.
Until then if I monitor memory consumption, the task manager reports that memory increases to 25 GB of RAM.  Yesterday we removed AWE, reboot the instance, and it started slowly increasing memory Until around 17:00pm , it was using almost 25.2 GB of RAM:

Today after a reboot, it is now using 11GB.
I now, that in a previous post said this memory consumption is "Normal Behaviour" of SQL Server, but why it suddenly use more of the max memory I set as maximum?
UPDATE 2021-03-31:
As for Today March 31, 2021 08:09AM, my server is using 25.3 GB of RAM:

Using Aleksey Script This are the numbers SQL Reports is using:

Resource Monitor says SQL Server IS using all the RAM:

Yesterday at 17:00 it was using 13 GB

And this is the behaviour of the server, That's why I'm asking if this is normal or not, what is making the server use 25.3 GB if it's limit should be 22.5 GB (rounded per my calculations).  This is the issue.
I know Microsofts literature says  a lot of stuff, but I haven't get a clue of why this behaviour, I've already check  Stored Procedures, Indexes, Jobs, etc.. and I don't see what is making the SQL Server use more of the assigned maximum server memory.
I disabled AWE, since this instance is running in a Virtual Machine in Hyper V, 64 bit Environment, (Windows 2008 R2 64 Bit, Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor) and I can't figure what's going on with this behaviour.


texts: SQL Server Agent/Properties/Alert System/Mail Session/Test is disabled

texts:
In order to enable operator notifications I navigated to SQL Server Agent/Properties/Alert System/Mail Session, checked "Enable mail Profile", selected Mail System and Mail Profile. At this time Test button next to the Mail Profile was disabled, so I clicked OK and decided to return to this configuration dialog later.

This worked and notification are being sent but when I returned to this configuration dialog later the Test button is still disabled.

Can anyone explain that?


texts: Oracle 12 Grid installaton falsly detecting pre-existing ASM installation

texts:
I seem to have run into a rather strange problem during my attempt to install Oracle 12 Grid Infrastructure:

System details: Windows2012
Oracle 12c
Grid infrastructure for ASM (stand-alone)

I installed the grid infrastructure, but due to a network problem, it didn't finish successfully. So, I used the deinstall tool to delete it. So far so good. So, now I'm trying to rerun the install.  During the first step, if I select 'Install and configure Oracle Grid Infrastructure for a Standalone Server', I get the following error:

[INS-40404] The installer has detected a configured instance of Oracle Grid infrastructure on the server

My assumption (which may or may not be correct) is that the previous deinstall didn't clean up everything properly.

However, if I select Upgrade Oracle Grid Infrastructure or Oracle Automatic Storage Management' I get the following:

[INS-40406] The Installer has not detected an existing Oracle Grid Infrastructure software on the system.

(Which sort of makes sense... the software was deleted.)

Anyone know how to clean this up? I've done some searching, and did find a reference to a problem under Linux where they recommended removing a file called 'ocr.loc' from /etc; however, since I'm under Windows, I'm not sure if there is anything equivalent. I've also found plenty of references to using the setup program with a "deinstall" parameter but that doesn't work either.


texts:
Hi I'm doing a dimensional modeling exercise in college and I'm trying to convert the total_price entry in two of the database tables to euro so they will be in the dimensional table as euro. The two tables are tournament1 and tournament2 and the dimensional table is dim_tournament. Here is the code I am trying to run:

insert into Dim_tournament (Tournament_SK, total_price, Tournament_DESC)
Select
  seq_Tournament.nextVal,
  total_price,
  t_descriprion
From (
       select
         t_id,
         cast(to_char(total_price, 'C9,999,999.00')as integer),
         t_descriprion
       from Tournament1
       union all
       select
         t_id,
         cast(to_char(total_price, 'C9,999,999.00')as integer),
         t_descriprion from Tournament2
     );

This is the error I keep getting when running this:

Error report -
SQL Error: ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

Any help would be very much appreciated.


texts: structuring media uploads (users, images, videos) using sequelize.js

texts:
(using pseudocode to simplify)

say I have a User (users table)

User {
  id: PK
}

and I want to have them upload images and videos (media). I want to be able to differentiate between images or videos but they have overlapping fields beyond their media types. so that I can query for all of their media, or just videos or images.

I am unsure of the best way to structure this. here are two options I came up with

Option 1: use media table with a media_type column (enum values video and image)

Media {
  id: PK
  media_type: enum (image, video)
  uploader_id: FK (users.id)
  url: VC
  length: Int (null for images)
}

sequelize:

user.getMedia(); // get all media
user.getMedia({ where: { media_type: 'image' } }); // get images
user.getMedia({ where: { media_type: 'video' } }); // get videos

I could also use the scope (on media_type) and alias (Images, Videos) options on the Model.associate to create getMedia(), getImages(), getVideos() methods.

Option 2: separate images and videos tables

Image {
  id: PK
  uploader_id: FK (users.id)
  url: VC
}

Video {
  id: PK
  uploader_id: FK (users.id)
  url: VC
  length: Int
}

sequelize:

user.getVideos();
user.getImages();

So my questions are:

which option (of the above 2 or another you have) is preferred and why?
if option 1 is chosen does it become unmanageable if I add more media types?
how do I do getAllMedia() for the second option? The only thing I can think of is to write a custom prototype method that queries each table and reduces them (in order of upload date). this seems incorrect.
say I also keep track of all the User's likes from the media they view. option 1 would allow me to keep the media.id FK on the likes table. option 2 appears to lead me to image_likes and video_likes which seems redundant.


texts:
I have setup SSRS scaled out deployment by using two nodes cluster configured in always on availability group. Now i can access the SSRS web portal via the AG listener name via port http protocol.
Example: http://aglistenername.contoso.local/Reports/Browse
My question is, is there any way I can configure the SSRS web portal URL with https protocol?
Thank you.


texts:
I'm trying to implement MySQL replication in production after I successfully implemented it on my dev environment. Basically, what I did is export the dump using the following :

mysqldump --single-transaction --flush-logs --master-data=2 --disable-keys --no-autocommit --databases mydatabase > mydatabase.sql

When I imported the file the configuration on the slave looked like this :

[mysqld]
datadir=/data1/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

The problem is that the ibdata1 file is HUGE (384G). After I restored it, I added the following in my.cnf and restarted MySQL :

# replication implementation
log-bin=mysql-bin
binlog_format=mixed
read-only=1
relay-log=mysql-relay-bin

My question is : should I go ahead with this if the ibdata1 file is so big or should I add these options before I perform the import? Thanks in advance for any insight.


texts: assign "all values" instead of "null" in Conditional Where clause

texts:
I have to extract from a table based on user input. I have a table named Vehicle_Register with vehicle details and users are allowed to filter the data using vehicle type (car, bus, truck...). If no filer is selected all the data should be displayed.

Now for this I have to use two queries, 1 with filter and 2 for all:

select * from vehicle_register where vehicle_type = 'car';
select * from vehicle_register;

Can these two queries be merged? Just want to know how to use a conditional where clause using "case" or "if" statement but to assign vehicle_type = all types when no filter is selected.


texts: How to keep the structure of the Tablix when there is no data to show

texts:
We have a SSRS report which shows the result of a dataset in a tablix. If the query in the dataset returns no data, the header of the Tablix gets displayed but not the tablix cells below the header. I know there is a property to NoRowMessage, but in fact it is not what we want. Because when I specify a message, SSRS shows the message instead of the empty tablix.I must show the tablix structure as it is, but with empty cells.

I even set the rule for each cell value of the tablix using IsNothing function, to show blank (""), if the value is null, but it didn't help.

Do you have any idea?
Thanks for your help in advance.


texts:
I have a very write intensive application that I want to optimize. Additionally I don't care about losing data in a crash, as long as there is no corruption in the existing ones.

Is it safe to run with journal disabled in this case? What other options do I have to increase insert performance?


texts: Trouble Connecting to SSIS in SQL Server 2017 (14.0.1)

texts:
Setting up a new server with SQL Server 2017 (14.0.1000) and I just installed SSIS (14.0). As of right now I am unable to connect to SSIS from SSMS and receiving the following error message:

Connecting to the Integration Services service on the computer "localhost" failed with the following error: "Class not registered".

This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance.

For help, click: http://go.microsoft.com/fwlink/?LinkId=506689

I've confirmed that the versions are compatible, tried doing an uninstall/reinstall of SSIS, all the while confirming SSIS is running in SQL configuration manager, but still the problem persists.

Update: I've also tried following the steps here
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/hh213130(v=sql.110)?redirectedfrom=MSDN
to no avail.


texts:
We have been experiencing an issue with a sequence object we have that is called a lot. It is used to assign a value prior to a record being inserted into a table.
Today I saw that 700+ sessions (all trying to get the next value from the sequence object) were being blocked by a session that was trying to get the next value from the sequence object with a wait type of PAGELATCH_EX. The other 700+ sessions were waiting on LATCH_EX.
When I looked into the wait resource it was referring to sys.sysobjvalues.
I'm curious as to why this may be happening and if other people have seen this. We recently changed the CACHE value for this sequence from the DEFAULT (50) to 200. I assumed this would improve the performance of calling the sequence given we use it so heavily but possibly I was wrong.
We are using SQL Server 2012 SP4.


texts: SQL server DB status runnable, sleeping, suspended, running and background meanings

texts:
I am not able to get the meanings of DB Session  status. I searched it for some references, but not getting any help.
Can anyone explain these status (runnable, sleeping, suspended, running and background) what actually means, that would be help me a lot.
Thanks in advance.


texts:
What I want to diagnose is how much disk read/write is being done over time per table(each table has a single file).

I have monitoring for the total disk read/write and monitoring for slow query log, general queries log.


texts: Is it better to store images in a BLOB or just the URL?

texts:
Possible Duplicate:
  Files - in the database or not?

I was wondering if there's any good reason to still use blob fields in a database. A couple of years ago I worked with a DB with a bunch of images in it, the DB was very slow and I couldn't see any good reason to keep the images inside the DB, so I got the images out and stored the filenames instead.

Was this a smart move? What would you do in my place?


texts: Database design - need someone to guide me

texts:
I'm probably doing this wrong, but here it goes.

I'm trying to build something like very basic CRM. I know there are plenty of them, but I want something to learn actually.

Requirements:

Some companies are never contacted before. That's why I created two additional tables contact_emails and company_emails.
The contacted company may have assigned contact (person).
A person can have phones and emails.
One person can be assigned to multiple companies
One company can have multiple persons

Diagram:

Questions:

Am I on the right track?
What can be improved and how?


texts: Hazards of Upgrading SQL Server Express 2008 to R2

texts:
A client of mine reached the 4GB database limit on a SQL Server 2008 Express.
As a test I installed the R2 version on another machine and restored a backup of the database.

I'd like to upgrade the actual server itself, though I'm worried about what any of the pitfalls/consequences could be in addition to compromising the system stability.

This is because, additional critical applications (including an ERP I don't fully understand) are running on this machine which would require calling another company to re-install/setup.

I've given the client 3 options:

Get new hardware in order to host the R2 server there (independent of current server).
Use current setup that was a test.
Take the risk and install the update to R2 on the actual server risking the 5% (or however much it is) risk of anything in the system going unstable.

I've also thought about cloning the whole computer using clonzilla (ghost) etc, but as I'm supporting remotely this would require I travel ~ 500km.

Also, this is a serious problem as they cannot add new records to this database.


texts: Object Auditing with Availability Groups

texts:
We are about to implement Availability Groups and it brought up a question.  We currently use auditing to see which objects are no longer used in production.  We also take a backup each night to use the next day for reporting.  Obviously, once we go to Availability Groups we won't need this as we will use a read-only database for reporting.  Because of our current setup, we don't know if an object is only used in the reporting database since the audit isn't on that database.

Question: Will auditing on an Availability Group register when someone uses an object on one of the secondary servers and not the primary?


texts:
I need to return a 0 or 1 dependent on previous records.  Example table:

DECLARE @x TABLE(ProductID INT, Failed bit, SampleDate date, LevelCode int);
INSERT @x VALUES
(101, 0, '20151201', 1),
(101, 1, '20151205', 2),
(101, 0, '20151206', 3),
(101, 1, '20151208', 2),
(102, 1, '20151202', 1),
(102, 0, '20151204', 2),
(102, 0, '20151205', 3),
(103, 0, '20160101', 1),
(103, 1, '20160102', 2),
(103, 0, '20160103', 2),
(104, 0, '20160101', 1),
(104, 0, '20160102', 2),
(104, 0, '20160103', 3);

The only thing we care about on the last record is the LevelCode (i.e. last record of each ProductID). Whether the last record passed/failed doesnt matter. We then look at all other records for that ProductID (so all records before the last record) and if there was a failure with the same LevelCode as the last record we set IsLastRunSameLevelAsPreviousRun to 1 else 0:

 ProductID     IsLastRunSameLevelAsPreviousRun
 101           1
 102           0
 103           1
 104           0

If there are no failures for a ProductID the IsLastRunSameLevelAsPreviousRun should return 0.

Any help or tips are very much appreciate.


texts:
There's a SQL Server performance counter for 'Non-atomic yield rate'.

The documentation simply states that this is

  Number of non-atomic yields per second.

I'm having trouble finding an explanation for what a non-atomic yield is.

What is a non-atomic yield in the context of SQL Server?


texts:
So I was creating a distributed availability group and noticed I wanted to change the name of the AG so I tried to run DROP AVAILABILITY GROUP.  But it was already seeding and was just stuck in "Cancelling" status. So I restarted the SQL Service to get it to quit trying to seed.  But now the AG is gone on the primary but is still in the sys.availability_groups table as a record, so when I try to recreate it, it says that it already exists.

Is anyone aware of how to fix that without having to create a ticket with Microsoft since Adhoc updates aren't allowed on system tables anymore.


texts:
I have my SQL Server 2008 R2 installed running on 32GB ram. I need to decrease the limit SQL Server uses to 28 GB and I have set the same to 28GB but it is still taking more. I have seen posts saying max memory is for buffer pool only. Ultimately what I require is the OS utilization should not go above 90% which is why I kept the max memory to 28GB hoping the OS utilization would come down.. but its not happening. What are the solutions?

I have run this query:

SELECT
    type, virtual_memory_committed_kb, multi_pages_kb
FROM
    sys.dm_os_memory_clerks
WHERE
    virtual_memory_committed_kb > 0 OR multi_pages_kb > 0

Output:

type                        virtual_memory_committed_kb  multi_pages_kb
MEMORYCLERK_SQLBUFFERPOOL   29169592                     416
OBJECTSTORE_LOCK_MANAGER    131072                       0
MEMORYCLERK_SQLSTORENG      53312                        17744
MEMORYCLERK_SQLCLR          10612                        14792
MEMORYCLERK_XE_BUFFER       4224                         0
MEMORYCLERK_SQLUTILITIES    120                          0

Query:

select * from sys.dm_os_process_memory;

Output:

physical_memory_in_use_kb   30599208 ~ 29G

large_page_allocations_kb 0

locked_page_allocations_kb 0

total_virtual_address_space_kb 8589934464

virtual_address_space_reserved_kb 39439648

virtual_address_space_committed_kb 31066216 ~ 30G

virtual_address_space_available_kb 8550494816

page_fault_count 16996946

memory_utilization_percentage 99

available_commit_limit_kb 32705384

process_physical_memory_low 0

process_virtual_memory_low 0


texts: PostgreSQL GIN index not used when ts_query language is fetched from a column

texts:
I've got a table that stores some multilingual content:

CREATE TABLE search (
  content text NOT NULL,
  language regconfig NOT NULL,
  fulltext tsvector
);

CREATE INDEX search_fulltext ON search USING GIN(fulltext);

INSERT INTO search (language, content) VALUES
  ('dutch', 'Als achter vliegen vliegen vliegen vliegen vliegen vliegen achterna'),
  ('dutch', 'Langs de koele kali liep een kale koeli met een kilo kali op zijn kale koeli-kop.'),
  ('dutch', 'Moeder sneed zeven scheve sneden brood'),
  ('english', 'I saw Susie sitting in a shoe shine shop. Where she sits she shines, and where she shines she sits.'),
  ('english', 'How can a clam cram in a clean cream can?'),
  ('english', 'Can you can a can as a canner can can a can?');

UPDATE search SET fulltext = to_tsvector(language, content);

To make sure I always search in the correct language I use these queries:

SELECT FROM search WHERE fulltext @@ to_tsquery(language, 'shine');
(1 row)

SELECT FROM search WHERE fulltext @@ to_tsquery(language, 'vlieg');
(1 row)

Because hardcoding the language doesn't give the correct results:

SELECT FROM search WHERE fulltext @@ to_tsquery('dutch', 'shine');
(0 rows)

SELECT FROM search WHERE fulltext @@ to_tsquery('english', 'vlieg');
(0 rows)

The problem however is that PostgreSQL doesn't use the GIN index when using the first set of queries and instead does a sequential scan:

(Note: I've disabled scanning using SET enable_seqscan = OFF; for these examples because of the low amount of rows)

EXPLAIN ANALYZE SELECT * FROM search WHERE fulltext @@ to_tsquery(language, 'shine');
---
Seq Scan on search  (cost=0.00..17.35 rows=2 width=136) (actual time=0.040..0.044 rows=1 loops=1)
    Filter: (fulltext @@ to_tsquery(language, 'shine'::text))
    Rows Removed by Filter: 5
Planning time: 0.039 ms
Execution time: 0.064 ms
(5 rows)

While it does when hardcoding a language:

EXPLAIN ANALYZE SELECT FROM search WHERE fulltext @@ to_tsquery('dutch', 'vlieg');
---
Bitmap Heap Scan on search  (cost=12.63..23.66 rows=82 width=0) (actual time=0.044..0.044 rows=1 loops=1)
  Recheck Cond: (fulltext @@ '''vlieg'''::tsquery)
  Heap Blocks: exact=1
  ->  Bitmap Index Scan on search_fulltext  (cost=0.00..12.61 rows=82 width=0) (actual time=0.037..0.037 rows=1 loops=1)
        Index Cond: (fulltext @@ '''vlieg'''::tsquery)
Planning time: 0.128 ms
Execution time: 0.065 ms
(7 rows)

So my question is: Is it at all possible to use a column in the ts_query to use the correct language config and still have Postgres use the GIN index?

I'm using PostgreSQL 9.4.

EDIT:

Here's the execution plan from the real table:

Using a column for language config:

Seq Scan on search  (cost=0.00..8727.25 rows=188 width=0) (actual time=0.725..352.307 rows=1689 loops=1)
  Filter: (fulltext @@ to_tsquery(language_config, 'example'::text))
  Rows Removed by Filter: 35928
Planning time: 0.053 ms
Execution time: 352.915 ms

When hardcoding the language:

Bitmap Heap Scan on search  (cost=28.65..4088.92 rows=1633 width=0) (actual time=0.514..10.475 rows=1684 loops=1)
  Recheck Cond: (fulltext @@ '''exampl'''::tsquery)
  Heap Blocks: exact=1522
  ->  Bitmap Index Scan on search_fulltext  (cost=0.00..28.24 rows=1633 width=0) (actual time=0.333..0.333 rows=1684 loops=1)
        Index Cond: (fulltext @@ '''exampl'''::tsquery)
Planning time: 0.180 ms
Execution time: 10.564 ms

EDIT #2

Tried it with Postgres 9.5, same result


texts: Duplicate key value error message on a database for a writing groups scenario

texts:
I am trying to create a database as portrayed in the following diagram:

This is the SQL-DDL code I have written:

CREATE TABLE Publishers
(
    PublisherName      VARCHAR(50)     NOT NULL,
    PublisherAddress   VARCHAR(50)     NOT NULL,
    PublisherPhone     VARCHAR(50)     NOT NULL,
    PublisherEmail     VARCHAR(50)     NOT NULL,

    CONSTRAINT pk_Publishers PRIMARY KEY (PublisherName)
);

CREATE TABLE WritingGroup
(
    GroupName    VARCHAR(15)     NOT NULL,
    HeadWriter   VARCHAR(15)     NOT NULL,
    YearFormed   CHAR(4),
    Subject      VARCHAR(15)     NOT NULL,

    CONSTRAINT pk_WritingGroup PRIMARY KEY (GroupName)
);

CREATE TABLE Books
(
    GroupName        VARCHAR(15)     NOT NULL,
    BookTitle        VARCHAR(50)     NOT NULL,
    PublisherName    VARCHAR(50)     NOT NULL,
    YearPublished    CHAR(4)         NOT NULL,
    NumberPages      INTEGER         NOT NULL,

    CONSTRAINT pk_Books               PRIMARY KEY (GroupName,BookTitle),
    CONSTRAINT books_pub_fk1          FOREIGN KEY (PublisherName) REFERENCES Publishers (PublisherName),
    CONSTRAINT WritingGroups_Books_fk FOREIGN KEY (GroupName) REFERENCES WritingGroup(GroupName)
);

INSERT INTO publishers VALUES('P01','12345 h st ','+1 215 837 0825','e1@gmail.com');
INSERT INTO publishers VALUES('P02','6789 s st ','+1 215 967 9719','e2@gmail.com');

INSERT INTO books VALUES('GroupName1','title 1 ','P1','1994',50);

SELECT * FROM books

However when running the query I get the following error message:

  [Exception, Error code 30,000, SQLState 23505] The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'PK_PUBLISHERS' defined on 'PUBLISHERS'.
      Line 38, column 1

Can anybody see where the mistake is? This is my first time writing a database and I really dont see where I am going wrong. Thanks in advance.


texts: Is table partitioning improving performance? Is it worth it?

texts:
I have just been involved in a project on which I will have to develop a data migration process and a web interface that uses an already existing SQL Server DB. This DB was developed by another person several years ago, it has around 100 GB of data and it is increasing every 10 minutes (it stores 10 min data from several units -> 144 records per day per device). Several tables have around 10 milion rows.
The point is that I think the main tables have been designed in a way that is not the most efficient or appropiate for the kind of queries that will be usually executed. Now I need to proof if what I say is better than what it is already implemented.
The DB is extensive in number of tables, but the structure can be simplified by the following diagram:

The Date_Id field is generated automatically by a function using the DateTime field. There are two indexes in both tables. The cluster index for each table contains the PK fields in the same order. The second index for the Unit table contains only the Unit_Id field, while the second index in UnitData contains the Unit_Id and DateTime fields in this order.

However, I think the design should be this one:

In this case only a clustered index for the PK fields will be needed. For this DB design the usual query would be something like:

SELECT ud.*
FROM Unit u, UnitData ud
WHERE u.Unit_Id = ud.Unit_Id and ud.DateTime >= 'dd-MM-yyyy'
ORDER BY ud.Unit_Id, ud.DateTime

Now comes the thing that I really don't understand: I've been told that the only reason for having a Date_Id column is to use it as partitioning column for this table. I've asked about the real necessity of having this table partitioned and the asnwer was "to run queries more efficiently when wanting daily or monthly data". I didn't know to much about partitioning before this, so I checked these links:

http://msdn.microsoft.com/en-us/library/ms190787.aspx

How Does Table Partitioning Help?

Improve performance by partitioning

Considering that the ideal query would be filtering by device and datetime, the questions are:

What do you think would be the most efficient and ideal query for the first DB design (with partitioning)?
Do you really think that the most efficent query against the first DB design is better than the second one (the one I wrote above)?
If the previous one was affirmative, do you really think the improvement is worth enough having two extras fields (Id and Date-Id) and an extra index?

Thank you very much!!


texts: Deciding what values should be used in a data warehouse dimension?

texts:
I'm beginning to look at data warehousing and I'm coming across examples where, for instance, we have check payment transactions. I understand that this should be the "fact" and we would have dimensions around it that would describe the fact (account, product, etc.). However, it also contains things like "memo line" and string "transaction id" values which, as strings, don't really belong in the fact table.

My question is, should there be a separate payment "meta" dimension that contains these descriptors, or is it ok to say that something like the memo can't be reported on since it's different every time, so we should leave it out of the warehouse. I know it's somewhat of an abstract question, but I'm not finding much on this in my reading. Any suggestions would be greatly appreciated.


texts: unable to run step 3 of syspolicy_purge_history job on SQL Server 2012

texts:
After installing an instance of SQL Server 2012 (SP3) on a server that had previously had SQL Server 2008R2 on it, I began encountering the following error while attempting to run step 3 of syspolicy_purge_history:

  A job step received an error at line 1 in a PowerShell script. The
  corresponding line is 'import-module SQLPS  -DisableNameChecking'.
  Correct the script and reschedule the job. The error information
  returned by PowerShell is: 'File C:\Program Files (x86)\Microsoft SQL
  Server\110\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be loaded
  because the execution of scripts is disabled on this system. Please
  see "get-help about_signing" for more details. File C:\Program Files
  (x86)\Microsoft SQL
  Server\110\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be loaded
  because the execution of scripts is disabled on this system. Please
  see "get-help about_signing" for more details. '

I logged into the server as the SQL Server Agent account and ran the SQLPS executable directly as administrator. I saw that the Process scope was set to Restricted with all others Undefined. After changing group policy for the computer to RemoteSigned, the error changed to the following:

  A job step received an error at line 1 in a PowerShell script. The
  corresponding line is 'set-executionpolicy restricted -scope process
  -Force'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Security error. '

This occurs even after modifying the job so that it is simply running Get-ExecutionPolicy -List. I've checked the registry tag and tried both deleting the registry key as well as changing it to Unrestricted and Bypass.

The only thing I haven't tried yet is attempting to upgrade the instances to 2014. It is a production box. Any suggestions other than disabling that step or running it using xp_cmdshell?


texts:
I have two mysql installations, one local and one on Amazon RDS. On my local, I can view info on the queries in the Client Connections section:

However, in the RDS connection it does not allow me to view details (there is no section on the right or place to click "View Details"):

Is there something I need to configure to be able to see the detailed query information, or why doesn't that show up in my RDS MySQL deployment?


texts:
Assume that there is a table with clustered index

create table [a_table] ([key] binary(900) unique clustered);

and some data

insert into [a_table] ([key])
select top (1000000) row_number() over (order by @@spid)
from sys.all_columns a cross join sys.all_columns b;

By inspecting storage statistics of this table

select st.index_level, page_count = sum(st.page_count)
from sys.dm_db_index_physical_stats(
    db_id(), object_id('a_table'), NULL, NULL, 'DETAILED') st
group by rollup (st.index_level)
order by grouping_id(st.index_level), st.index_level desc;

one can see

index_level page_count
----------- ----------
8           1
7           7
6           30
5           121
4           487
3           1952
2           7812
1           31249
0           125000
NULL        166659

that table takes 166659 pages in total.

However table scan

set nocount on;
set statistics io, time on;
declare @cnt int;
select @cnt = count(1) from [a_table];
set statistics io, time off;

produces

Table 'a_table'. Scan count 5, logical reads 484367, ...
CPU time = 1757 ms,  elapsed time = 460 ms.

almost three times higher number of logical reads in comparison to space taken by table. When I examined query plan, I noticed that SqlServer used parallel index scan. And this is where first part of the question arises.

How parallel index scan is performed that makes SqlServer to do so much logical reads?

Specifying option (maxdop 1) to suppress parallelism

set nocount on;
set statistics io, time on;
declare @cnt2 int;
select @cnt2 = count(1) from [a_table] option (maxdop 1);
set statistics io, time off;

resulted to

Table 'a_table'. Scan count 1, logical reads 156257, ...
CPU time = 363 ms,  elapsed time = 367 ms.

Comparing statistics for parallel and non-parallel index scan in this case leads to a conclusion that sometimes it is better to avoid parallel index scan. And this is where the second part of the question arises.

When should I worry about parallel index scan? When should it be avoided/suppressed? What are the best practices?

The above results are obtained on

  Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)


texts:
I have previously saved a copy of /var/lib/mysql/ddms directory ("ddms" is the schema name). Now I installed a new MySQL on a freshly installed Ubuntu 10.04.3 LTS by running apt-get install mysql-server, I believe version 5.1 was installed. After I copy the ddms directory under /var/lib/mysql, some of its tables work fine, these are the tables with an associated set of three files: a .frm file, a .MYD file and a .MYI file.

However, there are two tables with a different set of files: a .frm file and a .ibd file. These two tables didn't show up in the table list in phpMyAdmin. When I look at the error log, it says:

[ERROR] Cannot find or open table ddms/dictionary_item from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.

Please help with restoring these two tables. Thanks.


texts: Common TVF in SQL server to get results from differnt schema

texts:
I have been using SQL Server for past one month and I need a suggestion from SQL Server folks to help me on this use case.
The tables below are just to explain about the idea that I am looking for.
I have tables in different schema like
MyDb.dbo.Festivals
MyDb.India.Festivals
MyDb.China.Festivals
MyDb.USA.Festivals

I am writing a table valued function without any schema prefixed in it like
CREATE FUNCTION getFestivals()
RETURNS TABLE
AS
RETURN
(
 SELECT * FROM festivals
)

As I haven't applied any schema, it defaults to dbo and creates the TVF as dbo.getFestivals(). Now I have created synonyms for all other schemas
CREATE SYNONYM India.getFestivals  FOR dbo.getFestivals;
CREATE SYNONYM USA.getFestivals  FOR dbo.getFestivals;

I tried to query like select * FROM MyDb.India.getFestivals() and still it returns the festivals from dbo.festivals and not india.festivals. I understand that though the synonyms were created it just executes the select query in the dbo schema context and not in india schema context.
I want suggestions on how to have a common table valued function that will query based on the schema prefixed, i.e. MyDB.India.getFestivals() should get festivals from India and MyDB.USA.getFestivals() should return festivals from USA.
Question
Is there a way I can have a table valued function that can query based on the schema context?
The only possible way I can think of is to create the same table valued function in all schemas.
Caveats

I have to stick to table valued function only and the above use case is a sample scenario to explain my problem.


texts: A methodology for tracing a query that sporadically runs for hours instead of seconds

texts:
Once a few weeks or months, but always on the same weekday, one query from a stored procedure used in a daily batch job may visibly become stuck and run for about 200 minutes until the application kills its connection. This is a very trivial textbook query which joins 5 tables and calculates a sum() on one field to assign to a variable, so there is no point in providing it here. The total number of records to be summed is normally with 1-2 dozens. This query has no parameters, so parameter sniffing is (fortunately) out of question.

I have looked at a number of things:

There are no online users in the DB. It is used purely as a data dump.
There is no job that runs simultaneously. The last activity on the DB is a tranlog backup that completes about 10 min earlier. The next activity starts after about 2 hours, it is a full backup which completes fine after a few min (while this query is still going nuts).
There are following locks while this query is running, all in GRANT status:

OBJECT lock on one of the tables requested in Sch-S mode
HOBT.BULK_OPERATION lock on another table in S mode
OBJECT lock on yet another table requested in IX mode

The query has (NOLOCK) hint on all joined tables. It is in RUNNABLE state and according to sp_whoisactive, which runs every 15 min on the server, the CPU count of the query in question is constantly increasing. The CPU grows to about 1000x of the standalone query execution. The IO of the query from sp_whoisactive is about the same as if I ran it standalone, but the reads count is enormous, 10000x times over standalone execution. It feels like the query is in an endless loop. But when I get into the office in the morning, the job re-runs within a few seconds.

The plan reported by sp_whoisactive is the same as standalone. There is an optimizer hint for creation of an index when I run it, but as the query standalone time is around 200ms, I am not concerned about this hint. Implementing it seems to shave another 10ms off that time.
There are no errors reported by DBCC CHECKTABLE on any of the involved tables or DBCC CHECKDB.

The only point of suspicion I may have about the plan is that it has two parallelism icons. But adding option (maxdop 1) only increases its execution from 200 to 400ms.

There are 5 tables involved, all different sizes, the largest is around 1*10^6 records, not overly large. We refresh statistics and indexes daily at 4am and the job runs at midnight before any large volumes of data would be imported. The job itself only adds 1-10 records.

Forcing the execution plan stored for the hung query leads to the same result: stuck query, huge CPU and IO. Letting the optimizer do its magic, or forcing the plan that I get standalone (they are the same) it completes in a split second.

...

Long story short: I was allowed to run a nightly scheduled trace in production for the duration of the "normal" execution time of this job in order to try to troubleshoot this. We do not know when the next occurrence will be, only hope that it will follow the pattern which we observed - every few weeks.

My million dollar question is what events should I trace for? I am only a SQL developer and do not have the typical DBA skills, so I would not know what innards of SQL server should be exposed through a trace to catch the culprit red-handed.


texts: What happens if data gets inserted in the middle of a transaction?

texts:
I'm new to database and I'm lost with transaction. For example
in a situation where I have a table sensor_log that receives INSERT constantly and I want to move data to two other tables in one transaction.

BEGIN;

INSERT INTO sensor_log_a
SELECT id, location
FROM sensor_log
INNER JOIN sensor_location_to_insert USING (location);

INSERT INTO sensor_log_b
SELECT id, location
FROM sensor_log
INNER JOIN sensor_location_to_insert USING (location);

COMMIT;

Is there a risk that data is different between sensor_log_a and sensor_log_b if data gets inserted during the transaction or the BEGIN;..COMMIT; prevent this?


texts: joining two tables and counting joined rows


texts: LIKE or CHARINDEX to check String Contains a Substring

texts:
To check a string contains a sub string or not we can use [LIKE] or  [CHARINDEX].
I know that like uses indexing in case we have name%.(while %name% may not use index) .Also charindex does not use index.
 For example I want to get all rows containing employeename as 'av'

select * from employee where CHARINDEX('av', employeename)>0

OR i can do same by using LIKE

select * from employee where employeename LIKE '%av%'

In this case which one is better to use? Charindex or like? Why?


texts: Can I rely on the MSDTC to guarantee a distributed transaction was completed?

texts:
I've come across a bug using the TransactionScope from the .NET library, where we're getting a TransactionInDoubt exception. After doing a lot of research into something I don't know much about, I've figured this happens when there is a communication error between the database, and we're unsure if the transaction completed.

Trying to handle this error quickly became a pain however, there seem to be a lot of possible problems that arise.

Basically, I want to know if there is a guarantee that a distributed transaction across multiple databases will be committed or rolled-back on ALL databases, not committed on one and not the another.

From my knowledge, the MSDTC controls a distributed transaction and handles this all for me. However reading up on it a little, it seems if there is a communication problem while trying to confirm the commit to one of the databases, then the commit won't be performed for that database, but the others will be committed.

There are plenty of resources online that discuss this, and it's often a lot of conceptual talk, my simple question is: Can I rely on the MSDTC, or do we have to come up with a separate solution to the problem?


texts: SSIS Script to split string into columns

texts:
I have a dataset (log file) with a number of columns; one of them is "Other-Data" below (unordered string) and need to parse the string to create the derived columns according the u value (U1, U2, U3, etc...). The output columns should be something like:

U1   U2  U3                  U4   U5   etc.
null odw odw : CH : de : hom null null
EUR  sss DE:de:hom           null null
EUR  crm crm                 null null

Other-Data:

u3=odw : CH : de : hom;u2=odw : Product : DSC-HX20V;~oref=http://www.bidl.ch/lang/de/product/dsc-h-series/dsc-hx20v
u1=EUR;u2=sss:Checkout-Step4:Orderacknowledgement;u3=DE:de:hom;u11=1;u12=302338533;u13=SVE1511C5E;u14=575.67;~oref=https://shop.bidl.de/shop/bibit/success.do
u15=1187;u13=SVE14A1C5E~VAIOEWY401;u11=1~1;u10=843.9~121.14;u9=1038~149;u3=crm : FI : fi : hom;u1=EUR;u2=crm : Checkout : Order acknowledgement;~oref=https://shop.bidl.fi/shop/bibit/success.do

Can anyone help with this?


texts: Cassandra pool warning displaying continuously

texts:
I am using the Cassandra driver for python in Spyder. I am trying to fetch some data from Cassandra table. Here is my code:

from cassandra.cluster import Cluster
cluster=Cluster(['some_ip'])
session=cluster.connect('some_key_space')
df_filtered_10m=session.execute("some query")

This is all working fine and I am getting the desired results. The problem is that in the console, this message is continuously popping up:

  WARNING:cassandra.pool:Error attempting to reconnect to 10.0.10.91,
  scheduling retry in 256.0 seconds: [Errno None] Tried connecting to
  [('10.0.10.91', 9042)]. Last error: timed out

I have tried cluster.shutdown but it not working as well.
How to get rid of it?


texts: How to get balance of of remaining medicine batch wise

texts:
I have this query:
select itemcode, batchno, eXpirydate,
    CASE
         WHEN type= 1 THEN sum(qty)
         WHEN type= 2 THEN sum(qty*-1)
    END AS balance
FROM strans
group by itemcode, batchno, EXpirydate,type

It gives me these results:
   itemcode  batchno    EXpirydate    balance
1  0000002  123         2023-01-01     500
2  0000002  123         2023-01-01    -100
3  0000002  321         2024-01-01     500
4  0000002  321         2024-01-01    -150

I want to get the following results:
   itemcode  batchno    EXpirydate    balance
1  0000002  123         2023-01-01     400
2  0000002  321         2024-01-01     350


texts: Question on turning on GTID for MySQL master and slaves

texts:
I came across the interesting article by Facebook's Engineering team where they were able to deploy GTID in MySQL at a large scale : https://code.facebook.com/posts/1542273532669494/lessons-from-deploying-mysql-gtid-at-scale/

Their idea to enable GTID is to keep GTID off on the master, and on on all the slaves, and then later promote one of the slaves as the new master and then turning on GTID on the original master :

"We worked around this problem in fb-mysql by relaxing the constraint that gtid_mode must match between master and replicas. We permit a gtid_mode=ON replica to have a gtid_mode=OFF master, and we suppress assignment of GTIDs to transactions on servers with read_only=ON (i.e., replicas). This permits a high-availability deployment strategy as follows, for each replica set:

On each replica, one at a time, restart MySQL to enable gtid_mode. Afterward, we are in a state where every replica has gtid_mode=ON, but the master still has gtid_mode=OFF.
Perform a master promotion as normal, repointing the replicas and original master to a new master. The original master's replication will intentionally break when started, since it still has gtid_mode=OFF.
Restart the original master to enable gtid_mode. It will now be able to replicate from the new master, and the entire replica set now has gtid_mode=ON. "

From what I understand about GTID in 5.6, either all the machines in your cluster need to have GTID on, or they need to have GTID off. So is Facebook able to have a GTID-ON master and GTID-OFF slaves because they have specially modified their MySQL to do so? This would otherwise not be supported under a normal MySQL 5.6 release right?

Thank you


texts:
Have a weird question / (bug?) here. Using PHP My Admin I installed via apt-get on Ubuntu 13.10

Versions are as follows:

Server version: 5.5.35-0ubuntu0.13.10.2 - (Ubuntu)
Apache/2.4.6 (Ubuntu)
Database client version: libmysql - 5.5.35
PHP extension: mysqli Documentation
PHPMyAdmin Version information: 4.0.6deb1

Under the Stats page it is showing the following:

Questions since startup: 2,144
per hour: 405
per minute: 7

Here is the weird thing - about 5 minutes ago it said 5000+ questions since start-up.

I am the only user with access to the database besides the "WWW User" I have created for my PHP pages. My web server is not open to the public - port 80 is blocked on my router, and I have password protection set up in my Apache2.conf file. In order to access my LAMP server on the fly I use ConnectBot for Android to tunnel port 8080 to my computer's port 80. I am not running 405 queries per hour! I have only probably ran at most 20 on the database in the last hour including queries made by PHP scripts. The only way someone other than me could be accessing my LAMP server is if they were using a wifi sniffer and cloning their MAC address to bypass MAC filtering to get past my router - but my router shows nothing unusual connected.

Additionally - it is showing the following, which really has me concerned:

show binlogs    73  13.8    3.40
show tables 34  6.4     1.59
show variables  28  5.3     1.31
show master status  27  5.1     1.26
show status 27  5.1     1.26
show slave status   27  5.1     1.26

With the exception of show tables, I did not know these commands existed. I have not ran a show tables command in several days.

Is someone hacking my database? Or is PHPMyAdmin reporting inaccurate information?


texts: Auto Update Stats not updating Statistics

texts:
Am using SQL SERVER 2012 I have my Auto Update Stats ON in my database.

From the below link I learned that, the Auto Update stats will fire for every SQRT(1000 * Table rows) change in table rows.
https://blogs.msdn.microsoft.com/srgolla/2012/09/04/sql-server-statistics-explained/

I created a table with 1000 records

SELECT TOP 500 Row_number()OVER (ORDER BY (SELECT NULL)) rn,
                name
INTO   stst
FROM   sys.objects

Creating statistics

CREATE STATISTICS rn
    ON stst (rn)

CREATE STATISTICS name
    ON stst (name)

Checking the created Statistics

DBCC show_statistics('stst', rn)  -- Rows 500

DBCC show_statistics('stst', name) -- Rows 500

As per the formula

select SQRT(1000 * 500) -- 707.106781186548

So If I add/modify 707.106781186548 records in my table auto update statistics should fire

Add 1000 more records to my table which should be more than enough to fire auto update stats

INSERT INTO stst(rn,name)
SELECT TOP 1000 Row_number()OVER (ORDER BY (SELECT NULL)) rn,
                a.name
FROM   sys.objects a

To fire the auto update stats

Select * from stst

Checking the statistics

DBCC show_statistics('stst', rn)  -- Rows 500

DBCC show_statistics('stst', name) -- Rows 500

Unfortunately still the Rows is 500 only.

Even after inserting 1000 records into my table which is obviously greater than 707.106781186548 while performing SELECT why Auto Update stats didn't fire ? What am I missing here


texts: Why is SQL Server 2008 R2 Management Studio making me login again?

texts:
I login to SQL Server 2008 management studio each morning, it works all day just fine. When I come in the next morning and try to run a query, it asks me to login again.

Where is the setting defined which controls this "login timeout"?


texts: ERROR 1 (HY000): Can't create/write to file (Errcode: 13 - Permission denied)

texts:
I've been struggling for a couple of days with this now and have been trying to use this thread amongst other google searches to resolve it but can't seem to get it to work.

When I run this SELECT query (Ubuntu Server 15.10 & MySQL 5.6)...

SELECT column1 ,column2
FROM table
INTO OUTFILE '/home/user/mysql_temp/file.csv';

I get this permission error...

ERROR 1 (HY000): Can't create/write to file '/home/mysql_temp/file.csv' (Errcode: 13 - Permission denied)

From my searching, I understand this to be a system permissions issue rather than a MySQL problem but I can't seem to find a solution which fixes it for me.

Checking apparmor shows that mysqld IS in enforce mode...

apparmor module is loaded.
6 profiles are loaded.
6 profiles are in enforce mode.
   /sbin/dhclient
   /usr/lib/NetworkManager/nm-dhcp-client.action
   /usr/lib/NetworkManager/nm-dhcp-helper
   /usr/lib/connman/scripts/dhclient-script
   /usr/sbin/mysqld
   /usr/sbin/tcpdump
0 profiles are in complain mode.
2 processes have profiles defined.
2 processes are in enforce mode.
   /sbin/dhclient (676)
   /usr/sbin/mysqld (1285)
0 processes are in complain mode.
0 processes are unconfined but have a profile defined.

So I edited /etc/apparmor.d/usr.sbin.mysqld and added the following lines and then restarted apparmor...

/home/user/mysql_temp/ rw,
/home/user/mysql_temp/* rw,

I also changed the owner:group of mysql_temp to mysql and gave it full permissions...

sudo chown mysql:mysql mysql_temp

...

drwxrwxrwx 2 mysql   mysql       6 Apr  1 22:02 mysql_temp

After this, I still receive the same error.

Have I missed something? Misunderstood some of the instruction? Would be grateful for advice. Thanks.


texts: Select one column with same value and check another column's value

texts:
I have a table with following structure (rest of the columns omitted for brevity) :

+------------+-------+
 | Cust_ID    | Status|
 +------------+-------+
 | 312        | A     |
 | 312        | D     |
 | 312        | D     |
 | 345        | A     |
 | 345        | D     |
 | 345        | A     |
 | 678        | D     |
 | 678        | D     |
 | 678        | D     |
 +------------+-------+

We can observe that same Cust_IDs have multiple Status in different rows.
Now I want to pick Cust_IDs for which all Status row is D. So in the above case, the Cust_ID 678 should be listed.

Can anybody help me out in writing such query?


texts:
I have the following graph:

B granted select on D and D granted select on E and E granted select on G.
B has grant select with grant option and has granted select D with grant option.
D has grant select with grant option and has granted select E with grant option.
E has grant select with grant option and has granted select G with grant option.

When I try to revoke the GRANT SELECT on D, I get an error that requires the use of CASCADE.

Code:

EXECUTE AS USER = 'userB';
GO
REVOKE GRANT OPTION FOR SELECT ON EMP FROM userD;
GO
REVERT;

Error:

  To revoke or deny grantable privileges, specify the CASCADE option.

What I need is to remove the GRANT SELECT without removing the GRANT SELECT from E and G.


texts: Configuring Availability group between Two SQL Server Failover Cluster Instances

texts:
I am having two Windows clusters. Two SQL cluster instances are there in each two node cluster (

Data centre 1 :Windows Cluster 1-SQL2016CLUS1

Node 1
Node 2
SQL 2016 failover cluster instance1 -SQL2016DB01
SQL 2017 failover cluster instance2 -SQL2017DB01

Data Centre 2: windows cluster 2-SQL2016CLuS2

Node 3
Node 4
SQL 2016 failover cluster instance1 -SQL2016DB02
SQL 2017 failover cluster instance2 -SQL2017DB02

Now I want to configure "Availability Groups" between two SQL 2016 cluster instances(SQL2016DB01 & SQL2016DB02)
How to configure it?

For configuring "Distrubuted Always On Availability groups" We need to create AOAG right(with in same data centre)? How to create AOAG, because i have only one instance


texts: Efficient PostgreSQL Updates Using a Temporary Table

texts:
I've written a small program to import product-detail updates from file which takes much longer than expected. (I'll use trimmed-down examples for the sake of brevity.)

The program does the following:

Reads in data from file.
Performs certain modifications and creates an in-memory file.
Creates a temporary table to hold the processed file data.
COPYs the modified data into the temporary table.
Updates the actual table from the temporary table.

This all works fine, except the UPDATE query takes ~20 seconds for a small file of ~2000 rows.

The temporary table looks like this:

CREATE TEMPORARY TABLE tmp_products (
  product_id integer,
  detail text
);

And my update query is really straightforward:

UPDATE products
SET detail = t.detail
FROM tmp_products t
WHERE t.product_id = products.product_id

To speed things up, I tried the following with very little success:

Create a BTREE index on the temporary table.

CREATE INDEX tmp_products_idx
  ON tmp_products
  USING BTREE
  (product_id);

Creating a HASH index:

CREATE INDEX tmp_products_idx
  ON tmp_products
  USING HASH
  (product_id);

Neither index improved the update time significantly. Then I thought perhaps clustering the table would help, but that meant I couldn't use the HASH index. So I modified the queries in the program to use a BTREE index and then CLUSTER/ANALYZE:

CREATE INDEX tmp_products_idx
  ON tmp_products
  USING BTREE
  (product_id);

-- Program inserts data

CLUSTER tmp_products USING tmp_products_idx;
ANALYZE tmp_products;

This didn't help anything either. I took one more stab at it by using both a BTREE and HASH index hoping that the CLUSTER would use the BTREE and the UPDATE would use the HASH:

CREATE INDEX tmp_products_btree_idx
  ON tmp_products
  USING BTREE
  (product_id);

CREATE INDEX tmp_products_hash_idx
  ON tmp_products
  USING BTREE
  (product_id);

-- Program inserts data

CLUSTER tmp_products USING tmp_products_btree_idx;
ANALYZE tmp_products;

And again, nothing helped. I'm still right where I left off - 20 seconds for 2000 rows. Normally 20 seconds for an update is tolerable in my workplace, but the 2000-row file is a small sample I'm using for testing. Larger files will take way too long.

A few details about the products table if they matter:

Rows: ~630k
Columns: 54
Indexes: 19
Triggers: 14
Table Size: ~1.2GB
Index Size: ~2.2GB

I strongly suspect the bottle-neck is in one ore more of the triggers, however I'm not able to remove/modify those triggers. Is there anything I can do to improve the efficiency of my update?


texts: MongoDB fsyncUnlock won't unlock occasionally on Windows

texts:
We have a MongoDB replica set installed on Windows servers, with a scheduled backup job. MongoDB is configured to use journaling.

The job runs db.fsyncLock() via script on MongoDB (primary server), then performs a Kaminario file-system snapshot, then runs db.syncUnlock() via script.

The lock/unlock are separately, therefore not using the same connection.

The problem is that the db.fsyncUnlock() doesn't always work, leaving the database locked to writes until it is manually unlocked via shell.

We attempted to perform snapshots without using fsyncLock(), however, our tests showed that:

Restoring a single database-only resulting in the data having "holes" in it (some of the writing thread had non-committed changes followed by committed changes)
Restoring the entire database instance the restore failed due to a lock and missing journal, despite the journal being enabled and present

Whereas, with fsyncLock() the tests passed. The tests were performed with writeConcern=2, as we want to ensure persistency in case of a crash on MongoDB.

My questions are:

a. How to guarantee success of the fsyncUnlock() after performing the snapshot?

b. Is there any way to guarantee a consistent backup without using fsyncLock()?

Cross submitted on Server Fault.

Edit:
After further investigation, it looks like the problem was with the snapshot hanging, not the unlock. However, that still doesn't explain the fsync requiring the lock.


texts: MySQL replication still failing after new import

texts:
I had an issue with my MySQL replication from Master to Slave. An App we had by mistake wrote to the Slave and deleted some data as well, causing errors during the replication (delete errors, primary key not found errors etc.- error codes 1032, 1062 etc.).

Skipping all the errors didn't work as it seemed the data was beyond repair and once all the errors from multiple tables were skipped the replication continued failing as above. To this end we exported the data from the Master using MySQL Workbench (after following all the steps of stopping, granting permissions, flushing privileges, locking the master and then exporting the data through MySQL Workbench and unlocking the databases).

https://www.lexiconn.com/blog/2014/04/how-to-set-up-selective-master-slave-replication-in-mysql/ (example guide)

The .sql files were ported to the Slave, Slave was stopped, changed the MASTER to MASTER_HOST and imported all the data through the MySQL Workbench on the Slave. The database is rather large (30 odd Gigs) so this process takes over 24 hours. Once the Slave is then Started, again the errors appear (Duplication errors, cannot delete errors, Primary Key errors etc.).

Initially we used mysqldump to extract the data and MySQL to to re-import the data but this failed on the MySql importing complaining of errors in the SQL files (which are too large to open in any notepad and investigate). So we went for the WorkBench as an export and import (which seems to both function without any errors).

I cannot find any research online that deals with something like this, a failure of replication after clean export and import or where to go from here. The .sql files created by the WorkBench do all have Drop Tables commands before create tables, so surely the tables are deleted and created fresh so should not have any Primary Key concerns etc.

Does anyone have any guidance for this as any test we do has to run for 24 hours before we see the failure.

Much appreciated.


texts: Would snapshot replication avoid the "repl table shuffle" when making schema changes?

texts:
I support an application that sits upon a SQL 2008 database and has an admin tool that allows app admins to do some field customization. This issues an ALTER TABLE command when saved, and since the app admin user has db_owner rights on the database, this would typically succeed. (Note this is a vendor app, so we can't change the internal behavior.)

The problem is that we are also using transactional replication to push some tables from this database to a (non-trivial) reporting environment, so ALTER TABLE commands are failing on these replicated tables.

This, in my experience, is expected behavior, so I recommended doing the "repl table shuffle" to apply changes (remove tables from replication, alter tables via app, re-insert tables, run snapshot job, monitor distribution job). This works and seems like the appropriate strategy for pushing changes into production, but for our dev environment seems overly DBA labor-intensive, especially for the amount of ongoing customization and testing the dev team has planned.

We've discussed whether snapshot replication would work any better for the dev environment, since the dev reporting environment doesn't have to stay immediately up-to-date, nightly push or "on-demand" is ok. Part of the testing cycle will be making sure that changes to the environment don't break existing reports.

So:

If we switch to snapshot replication, will the ALTER TABLEs continue to fail, or will the snapshot simply take each table in its "current form" to push to the subscriber?
Are there security changes, short of sysadmin, that I could give the app admin user that would allow these ALTERs to succeed, despite replication?
Are there other, better better ways to support this without breaking either the vendor app, or the required replication to the reporting environment?

(Edit: We are replicating about 80GB of tables of a 2TB database, and the subscriber database contains other non-replicated mapping/reporting tables.)


texts:
I have a mysql server which has 60gb of ram and it uses too much system resources. Top command result;

Mem:   64555285k total, 64151125k used

I think there is something terribly wrong with my mysql conf.Here is my mysql conf;

[mysqld]
max-allowed-packet=400M
max_connections=8000
query_cache_size = 50M
thread_cache_size =32
table_open_cache = 1000
innodb_buffer_pool_size = 35G
innodb_log_buffer_size = 70M
join_buffer_size= 8G
tmp_table_size = 64M
max_heap_table_size = 64M
query_cache_type = 1

Is there anything wrong with my mysql conf?


texts: How to connect to remote PostgreSQL server

texts:
I wrote a web app, using java + spring+ PostgreSQL, which I have up and running.

I have a tester for my system and I want him to be able to connect to my database directly to check the result of interaction with application. As always, the web is littered with articles on how to connect, but one simple thing they don't mention is whether a client should install some app to connect to the database? I get the bit about making correct settings for the database, but if a mere mortal enters the following in their windows console:

psql -h <host> -p <port> -u <database>

they will get 'psql' is not recognized as an internal or external command,
operable program or batch file. I understand I don't get that error because I operate via shell and my home path is set to psql.exe, but again the client doesn't need to install PostgreSQL to access remote a PostgreSQL database. It just doesn't click in my head.

How can a client connect to database via command prompt without installing anything?


texts: How to use the value from record like as varchar?

texts:
I'm trying update a old table with result of selec for another tables but get a error using this sintaxe (e.g.):

DO
$$
DECLARE
    _roles refcursor;
    _onerole varchar;
    _record RECORD;
    _oldid integer;
    _currentid integer;
BEGIN
    OPEN _roles FOR SELECT role FROM roles WHERE id IN (SELECT idrole FROM user_roles WHERE iduser=4);
    EXECUTE format('SELECT oldid FROM users WHERE id = 4') INTO _oldid;
    EXECUTE format('SELECT id FROM oldtable WHERE id = $1') USING _oldid;
    FOR _record IN _roles LOOP
        RAISE NOTICE 'The role is = %', _record;
        EXECUTE FORMAT ('UPDATE oldtable SET ''%s'' = 1 WHERE id = $1', _record) USING _oldid;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

So, this return a sintaxe erro because the correspondent value for _record contain parentheses and apostrophe (e.g.: UPDATE oldtable SET '(rolename)' = 1 WHERE id = 2). Of course, these type is record and not varchar...
Well, how to use the value from record like as varchar?

For more information if needed
TABLE USER

id | user
--------------
1  | 'user1'
--------------
2  | 'user2'
--------------

TABLE ROLES

id | role
----------------
1  | 'role'
----------------
2  | 'role2'
----------------
3  | 'role3'
----------------
4  | 'rolex'
----------------

TABLE USER_ROLES

iduser | idrole
----------------
1      | 1
----------------
1      | 2
----------------
1      | 3
----------------
1      | 4
----------------
2      | 3
----------------
2      | 2
----------------

TABLE WRONG_OLD_TABLE   :)

iduser | role1 | role2 | role3 | role_x | role_y ...
-----------------------------------------------------
1      |1      |1      |1      |1      |1      |
-----------------------------------------------------
2      |0      |1      |1      |0      |0      |


texts:
We need to manipulate a set of records returned from a select query. This can be done in :

Either by saving the whole record to a table variable and then do the required process.
We can do like this

Select * from
   (select * from table where conditions) AS results
   where results.RowNum between ....

So what is the best method in this case.. using a table variable or the 2nd method.
Is there any performance issues in using the second method compared to first?


texts:
We have a vendor that has jobs set up to execute a stored procedure that builds a table in TempDB, creates indexes on it, inserts 100s of GB of data into it, does whatever it needs to with the data, then drops the table.  It does this process repeatedly throughout the day.

The server is a VM with 16 processors and 120 GB of RAM.  TempDB has 8 data files that are 100 GB each and 1 log file that's 50 GB.  We determined these sizes after the drive ran out of space a few times in development.  I've got the user db data files, user db log files, as well as TempDB files on their own drives which are all SSDs.  This app is the only one using this server so sharing TempDB isn't a problem.

Is this a problem?  This is the first time I've seen an app that did so much work in TempDB.  What problems should I be watching for with this setup?


texts: MariaDB/InnoDB: What to do on "Locks in Status-Log but no locked table found"

texts:
We migrated lately from MySQL to MariaDB.

SELECT VERSION()

  10.0.13-MariaDB-log

When executing this:

SHOW ENGINE INNODB STATUS

I get (shortened):

...
---TRANSACTION 279257007, ACTIVE 23981 sec
10 lock struct(s), heap size 2936, 18 row lock(s), undo log entries 903
MySQL thread id 119634, OS thread handle 0x7f62c96e0700, query id 48830504 XXX.XXX.XXX.X admin cleaning up
Trx read view will not see trx with id >= 279257008, sees < 279256916
TABLE LOCK table `db1`.`table_a` trx id 279257007 lock mode IX
TABLE LOCK table `db1`.`table_b` trx id 279257007 lock mode IX
...

I guess this means that this transaction was running for several hours. However, when executing SHOW OPEN TABLES, all tables show no-lock. When doing SHOW FULL PROCESSLIST, the result of the thread 119634 is:

  Command: SLEEP, State: '', Info: Null, Progress: 0

I have no idea what to do. When running a script I get lock-timeouts. But just increasing the timeout, I suspect will not help either.

Question: How can I get resolve this issue? Or, how should I tackle this?


texts:
When trying to start MySQL 8.0 it complained about partially upgraded DD. Furthermore, it complained about not being to open a table S/AD.ibd which no longer exists there because we moved it to S/AD.ibd. We reverted back to MySQL 5.6 and it seems to be running fine now. It looks like it failed during converting the .frm files into the information_schema, and I suppose this is why we were able to revert back to 5.6.

My questions:

1) Does anyone have any idea what this could be?
2) Are there MySQL diagnostic tools I can run against our 5.6 instance to see if there is inconsistent state stored about our AD.ibd table?

Here is the full log if it's of interest:

  The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
  information that should help you find out what is causing the crash.
  2018-06-22T15:02:56.236394Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.11) starting as process 1831
  2018-06-22T15:02:58.482400Z 1 [ERROR] [MY-011014] [Server] Found partially upgraded DD. Aborting upgrade and deleting all DD tables. Start the upgrade process again.
  2018-06-22T15:02:58.864512Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
  2018-06-22T15:02:58.864621Z 0 [ERROR] [MY-010119] [Server] Aborting
  2018-06-22T15:03:00.755015Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.11)  MySQL Community Server - GPL.
  2018-06-22T15:06:15.015157Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.11) starting as process 2225
  2018-06-22T15:06:17.585241Z 1 [ERROR] [MY-012592] [InnoDB] InnoDB: Operating system error number 2 in a file operation.
  2018-06-22T15:06:17.585309Z 1 [ERROR] [MY-012593] [InnoDB] InnoDB: The error means the system cannot find the path specified.
  2018-06-22T15:06:17.585330Z 1 [ERROR] [MY-012216] [InnoDB] InnoDB: Cannot open datafile for read-only: './S/AD.ibd' OS error: 71
  2018-06-22T15:06:17.585354Z 1 [Warning] [MY-012019] [InnoDB] InnoDB: Ignoring tablespace LD/AD because it could not be opened.
  2018-06-22T15:06:18.511449Z 0 [Warning] [MY-010727] [Server] System table 'plugin' is expected to be transactional.
  2018-06-22T15:06:18.627162Z 2 [Warning] [MY-010772] [Server] db.opt file not found for S database. Using default Character set.
  mysqld: /export/home/pb2/build/sb_0-28122056-1523171044.09/rpm/BUILD/mysql-8.0.11/mysql-8.0.11/sql/dd/upgrade/table.cc:1076: bool dd::upgrade_57::add_triggers_to_table(THD*, TABLE*, const String_type&, const String_type&): Assertion `t->
  get_event() >= t_type && (t->get_event() > t_type || t->get_action_time() >= t_time)' failed.
  15:06:19 UTC - mysqld got signal 6 ;
  This could be because you hit a bug. It is also possible that this binary
  or one of the libraries it was linked against is corrupt, improperly built,
  or misconfigured. This error can also be caused by malfunctioning hardware.
  Attempting to collect some information that could help diagnose the problem.
  As this is a crash and something is definitely wrong, the information
  collection process might fail.


texts: What is the difference between 2 phase locking and strict 2 phase locking?

texts:
2 phase locking is used to ensure isolation of database transactions (by ensuring serializability). But there is another term called Strict 2 phase locking. I find it hard to understand the difference.

What is the difference between the two?


texts: Very large Transaction Log File - When is it normal?

texts:
I have a server that is running extremely low on disk space and I dug into to determine if something is not functioning properly or if it simply is a healthy database that just happens to be growing.

I discovered that an ldf for a particular DB has reached 116gb (more than half the available space on the drive). However everything seems to be operating as it should in order to properly maintain the ldf size:

-Nightly Full backups
-Morning log backups

I also tried shrinking the ldf to free any unused space and got almost a single mb back. All things considered would this be a case where we just need to either A)increase disk space or B)move this DB's ldf to a different location?

Any feedback is greatly appreciated!


texts: Transactional Replication from Copy Only Backup

texts:
Is it possible to use a copy only backup when initializing a subscriber from a backup?

I've been trying it but getting all kinds of issues with:

Trying to update records that don't exist
Inserting records that already exist

Steps taken:

Created a pull publication (live server)
Created a copy only full backup
Restored backup on reporting server
Created a pull subscription from backup (live server)
Created procedures on reporting server from the result of sp_scriptpublicationcustomprocs
Created a pull subscription on subscriber (reporting server)

Example error: The row was not found at the Subscriber when applying the replicated UPDATE command for Table '[dbo].[table]' with Primary Key(s): [table_id] = 1234

Any help would be greatly appreciated.