Friday, May 10, 2013

[how to] Oracle Hangs on Book Order Process

[how to] Oracle Hangs on Book Order Process

Oracle Hangs on Book Order Process

Posted: 10 May 2013 06:31 PM PDT

Why did our oracle server hang on Book Order Process? When we look at the v$sqlarea, the sqltxt is incomplete.

How to "swap" out production tables in MySql

Posted: 10 May 2013 07:07 PM PDT

I have a MySQL table that holds a few thousand records which go out of date very quickly. A few times a day we run a slow process which needs to pull data from multiple sources and re-populate the table.

The catch is the table needs to be available all the time. My initial idea was to do the slow population process on a temp table, then drop the live table and rename the temp table to replace the live table.

Is it possible to lock the live table before the drop/rename process so that queries will be delayed and not lost?

Or is this the wrong approach?

'Offline' MySQL backup - server not starting

Posted: 10 May 2013 04:14 PM PDT

The MySQL server is no longer starting because the partition is too full. The issue seems to be ibdata (40GB). I wanted to follow these instructions (at 'InnoDB Infrastructure Cleanup')

Now I already failed at the first step because I cannot use mysqldump when the mysql server cannot be run.

I am very new to all this, so please excuse me for noobish questions

how find all position with patindex in

Posted: 10 May 2013 04:12 PM PDT

how find all position with patindex in tbale or variable?

declare @name nvarchar(max)  set @name ='ali reza dar yek shabe barani ba yek  dokhtare khoshkel be disco raft va ali baraye 1 saat anja bud va sepas... ali...'  select patindex('%ali%',@name) as pos

up select return 1 but i want all results

    pos     =====      1      74      113

AlwaysOn Architecture - SQL Server 2012 for creating a dev environment?

Posted: 10 May 2013 08:41 PM PDT

I was thinking if the AlwaysOn architecture in SQL Server 2012 can be leveraged somehow to create a replica for DEV and QA environment properly in addition to setting up HADR using the same architecture?

In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table

Posted: 10 May 2013 01:24 PM PDT

On Windows, I upgraded from 5.1 to 5.5 no problem.

  1. Copied my 5.1 data folder into my 5.5 instance
  2. Started mysqld skipping grants
  3. Ran mysql_upgrade

All good, but going from 5.5 to 5.6:

  1. Copied 5.5 data folder to 5.6 instance
  2. Started mysqld skipping grants
  3. Ran mysql_upgrade

but I get:

C:\Users\QAdmin>mysql_upgrade  Looking for 'mysql.exe' as: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe  Looking for 'mysqlcheck.exe' as: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlcheck.exe  Running 'mysqlcheck' with connection arguments: "--port=3306"  Running 'mysqlcheck' with connection arguments: "--port=3306"  mysql.user_info                                    OK  Running 'mysql_fix_privilege_tables'...  Running 'mysqlcheck' with connection arguments: "--port=3306"  C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlcheck.exe: Got error: 1130: Host 'localhost' is not allowed to connect to this MySQL server when trying to connect  FATAL ERROR: Upgrade failed

If I look at the mysql.user table it is completely empty.

  • Has anyone seen this or know what is going on?
  • During the "upgrade" the user table gets erased and when it tries to connect it can't?

Thanks.

Using a keywords table with multiple other tables

Posted: 10 May 2013 04:09 PM PDT

Using MySQL

I need help determining how to model a Keywords table to three different tables (Products, Certifications, and Exams) - I have included my model which is using what I refer to as intermediary tables (what would they be called), but im not sure that is correct. Can someone tell me if this is correct, or show me an example of how to do it correctly? Thank You!

Data model

Installation of SQL Server RAID 10 vs RAID 5

Posted: 10 May 2013 01:20 PM PDT

I am new to setting up SQL Servers and Vms. My predecessor used Raid 5 for C Drive where the SQL installation resided and RAID 10 for both database files and database logs.

I was wondering whether that is correct or should RAID 5 be utilized for SQL database files instead?

Any help?

SSRS 2008 R2 setup issue

Posted: 10 May 2013 03:54 PM PDT

I have installed SSRS 2008 R2 on my desktop and server. When I hit the reports link on my desktop

http://mypc/Reports_mypc/Pages/Folder.aspx

all I get to see is this home page of the desktop ssrs instance

I cant create a new folder or data source or anything of the sort

On the server where I am attempting to set up SSRS 2008 R2, all I get is a white screen that shows the virtual folder name in large fonts, followed by the version of the reporting services server on the next line. This is not leaving me any clues as to what needs to be fixed. On both pcs I am using the credentials of the local admin. Any clues on what needs to be fixed?

How can i setup my postgreSQL database on another harddrive than my system one on OS X?

Posted: 10 May 2013 03:12 PM PDT

I am on OS X 10.8.3, I've install PostgreSQL from macports, but I've realized that I have not enough space on my system drive for the DB I am about to populate with data.

Is there a way to create new database on another drive/partition (not the system one, where postgres is installed)?

How can I do that. Thanks in advance.

How to remove all hidden parameters from Oracle Database

Posted: 10 May 2013 05:01 PM PDT

How do I remove all hidden parameters from Oracle Database before upgrading? I'm using the SQL query below. So what do you recommended?

SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\'

Here are parameters:

_fast_full_scan_enabled  _gby_hash_aggregation_enabled  _like_with_bind_as_equality  _optimizer_push_pred_cost_based  _sort_elimination_cost_ratio  _sqlexec_progression_cost  _trace_files_public  _b_tree_bitmap_plans

Regards,

MySQL DB Design

Posted: 10 May 2013 04:42 PM PDT

I am designing a db for a Social Network type website where users enter lot of information varying from family member details, education, employment, personal favorite such as TV, movie, music, food, books etc using InnoDB and expect exponentially increasing writes but far few reads. I already have 26 tables.
My question is it better to have large number of individual tables for example as TV, movie, music, food, books or put them under one big table as MyPersonal Favorite to reduce the number of tables as I fear that there will be 26 individual disk I/Os to write one persons information with my current design

Can a surrogate key and a primary key be in the same table?

Posted: 10 May 2013 03:53 PM PDT

For example we have:

Surrogate key: ABC123  Primary key: 1  Name: James

Is this legit in a Data Warehouse table?

How to add additional tables/fields into a query with multiple inner joins?

Posted: 10 May 2013 12:10 PM PDT

Given this query (many thanks to RolandoMySQLDBA from question Select records that do not have associations outside a certain list), how do I add other fields from other tables into my result?

SELECT desired_categories.*   FROM      ( SELECT nid, title, COUNT(1) AS all_categories           FROM node           GROUP BY nid      ) AS A     INNER JOIN      ( SELECT nid, COUNT(1) AS good_categories           FROM node      INNER JOIN field_data_dir_phys_category AS category ON node.nid = category.entity_id          WHERE category.dir_phys_category_tid           IN (498,499,500)           GROUP BY nid      ) AS B USING (nid)    INNER JOIN       ( SELECT DISTINCT nid, title           FROM node       ) AS desired_categories USING (nid)    WHERE all_categories = good_categories;

I have about 5 fields from 5 other tables I need access to. All tables use the node id nid to indicate their relationship. I suspect it's possible to simply add a join in for each table but I haven't had any luck yet.

Select records that do not have associations outside a certain list

Posted: 10 May 2013 12:41 PM PDT

I have a mySQL table of nodes and each node can be associated with many categories. I have a list of category ids that are acceptable (498, 499). I want to select only nodes who do not have categories that are not in the list of acceptable category ids.

In this example, I should get Toby and Joseph back, but not Susan...

enter image description here

I was initially trying this, but the IN clause does not exclude other categories:

SELECT n.nid, n.title, category.dir_phys_category_tid  FROM node AS n  INNER JOIN field_data_dir_phys_category   AS category   ON n.nid = category.entity_id  WHERE n.type = 'dir_physicians'   AND n.`status` = 1   AND category.dir_phys_category_tid IN ( 498,499 )

Answer: modified from RolandoMySQLDBA's answer below, with formatting & naming changes

SELECT desired_categories.*   FROM      ( SELECT nid, title, COUNT(1) AS all_categories           FROM node           INNER JOIN field_data_dir_phys_category AS category ON node.nid = category.entity_id          GROUP BY nid      ) AS A     INNER JOIN      ( SELECT nid, COUNT(1) AS good_categories           FROM node          INNER JOIN field_data_dir_phys_category AS category ON node.nid = category.entity_id          WHERE category.dir_phys_category_tid IN (498,499)           GROUP BY nid      ) AS B USING (nid)    INNER JOIN       ( SELECT DISTINCT nid, title           FROM node       ) AS desired_categories USING (nid)    WHERE all_categories = good_categories;

SQL Server Import Job Removed Data After Job Failed

Posted: 10 May 2013 01:25 PM PDT

I have a SQL Server Job set up to import data from a table to a table on another server, using the Import/Export data wizard.

In one instance, the job failed (with, I believe a SQL connection issue), and with that, the data from both tables were deleted. The tables on both the source and destination server were removed.

Does this make sense? If anything, why would the data be removed from the source?

The source is SQL Server 2012 and the destination is SQL Server 2008.

MySQL : How to get rows where count() is null?

Posted: 10 May 2013 03:15 PM PDT

This is my SQL request :

SELECT COUNT( b0_.id ) AS sclr0, d1_.codeLieu AS codeLieu1, d1_.nomLieu AS nomLieu2, d1_.lngLieu AS lngLieu3, d1_.latLieu AS latLieu4, m2_.libelleMention AS libelleMention5, t3_.libelleType AS libelleType6  FROM Bac b0_  INNER JOIN Bachelier b4_ ON b0_.bachelier_cne = b4_.cne  INNER JOIN EtablissementBac e5_ ON b0_.etabBac_id = e5_.codeLieu  INNER JOIN Delegation d1_ ON e5_.delegation_id = d1_.codeLieu  INNER JOIN MentionBac m2_ ON b0_.mentionBac_id = m2_.codeMention  INNER JOIN TypeBac t3_ ON b0_.typeBac_id = t3_.codeType  WHERE m2_.codeMention IN ('TB',  'B')  AND t3_.codeType IN ('114',  '129')  GROUP BY d1_.codeLieu, m2_.codeMention, m2_.libelleMention, t3_.codeType, t3_.libelleType, t3_.abbrType  ORDER BY d1_.codeLieu ASC , b0_.mentionBac_id ASC , b0_.typeBac_id ASC

And this is the result (it's a screenshot):

http://nsa33.casimages.com/img/2013/05/10//130510034126658849.png

The thing is I get all the rows with the equivalent number (sclr0), but I also need rows with null values. The needed rows are defined by the columns libelleMention5, libelleType6. In other words, I want for each codeLieu1, all the possible combinations of libelleMention5 and libelleType6, and the equivalent number in the column sclr0 (which should be 0 if the cell is NULL).

How can I do that ?

Oracle RAC 11g R2 DB silent install: parameter needed for system class

Posted: 10 May 2013 01:08 PM PDT

I'm installing Oracle RAC DB 11gR2 using response file and silent mode. The installation is failing with the error message:

[INS-35421] This options installs a single instance database only
You have chosen to perform a Desktop class install on a cluster. This options will not install oracle RAC

Which parameter is needed for a server-class install? I tried changing the value for oracle.install.db.config.starterdb.control from DB_CONTROL to GRID_CONTROL, but I still get the same warning.

built-in XML functions in Oracle

Posted: 10 May 2013 11:40 AM PDT

I am trying to use built-in XML functions, but I'm getting an %s: invalid identifier error. Selects works fine without the XML functions.

Any ideas where is the mistake?

select id,full_name from users where id in(  with sample_data as  (   (select pm_id    from pm  where p_id=30369)  )  select cast(t.column_value.extract('//text()') as varchar2(40)) val    from sample_data,    table( xmlsequence( xmltype(      '<x><x>' || replace(pm_id, '#@#', '</x><x>') || '</x></x>'    ).extract('//x/*'))) t)

SQL Server - restore database with zero size log file

Posted: 10 May 2013 08:18 PM PDT

SQL Server backups contain meta-data about the size of the original log (LDF) file.

So if you backup a database that has a 5GB LDF file, when you restore it, you will get an (empty) 5GB LDF file. This often isn't needed (because the log file of the restored database contains practically nothing)

Often this causes problems with database restores on busy dev servers with not much space left if people *cough* haven't been too careful about shrinking log files before making the backup.

Is there any way to restore a SQL Server backup but tell it "Dont bother making that empty 5GB LDF file, thanks" ?

Shrinking log files with SMO

Posted: 10 May 2013 11:30 AM PDT

I know I can use the following t-SQL to shrink a log file:

BACKUP LOG db1 TO DISK = '\\server\share\db1_log1.trn';  DBCC ShrinkFile([db1_log], 0);

How do I do the same with SMO? I tried:

$server = new-Object Microsoft.SqlServer.Management.Smo.Server()    $dbBackup = new-object Microsoft.SqlServer.Management.Smo.Backup  $dbBackup.Action = "Log"  $dbBackup.Database = "db1"  $dbBackup.Devices.AddDevice("\\server\share\db1_log1.trn", "File")  $dbBackup.SqlBackup($server)    $db = $srv.Databases.Item("db1")  $db.TruncateLog()

But here, I found that the TruncateLog() method does not work with anything higher than SQL 2005. That link recommended using:

$db.RecoveryModel = RecoveryModel.Simple  $db.Alter()  $db.LogFiles[0].Shrink(0, ShrinkMethod.TruncateOnly)

However, switching to Simple Recovery Mode is going to break my log chain and I don't want to do that. Shrinking the log file doesn't break my log chain so I'm looking for an equivalent SMO method to do that. Does such a method exist?

Column partially determining accepted values of another column, should be somehow normalized?

Posted: 10 May 2013 03:06 PM PDT

I have a table that describes a set of properties (yes it's metadata, in this case there's a valid reason to have it in the database); among other things I tell the type of the data that can be assigned to them (Type) and a default value. The valid values for the default value are thus restricted by the Type column, but the default value is genuinely an attribute of the table, they are not uniquely determined by the Type column.

I'm not sure though, should this be somehow normalized or is it right as it is?

Edit: here's an approximate description of the current structure of the relevant part of the database, as requested. Don't mind the Values table, that's a separate issue. Just consider that ValueType restricts the set of DefaultValue permitted values.

Also, consider this only as an example, I'm interested in the problem in general, the problem being namely columns that limit the set of valid values of another column but that don't determine its exact value, thus as far as I understand don't constitute multivalued dependencies (but, as an aside, it would be useful to have an integrity constraint that enforce the limit - I'm not sure that this issue can be separated from the former).

Properties (  ID int PRIMARY KEY  Name varchar(100)  ValueType int REFERENCES ValueTypes(ID)  DefaultValue int REFERENCES Values(ID)  )    ValueTypes (  ID int PRIMARY KEY  Name varchar(100)  ...  )    Values (  ID int PRIMARY KEY  ...  )

Backing up a 400-500 GB database on MySQL/Amazon RDS?

Posted: 10 May 2013 08:20 PM PDT

We have an Amazon RDS small instance for a MySQL datawarehousing database. Database size will be around 400-500 GB very shortly. What is the best way to backup our database?

Can it be done efficiently with automatic backups and DB snapshots available with RDS?

Or should we consider some other options? What would those be? Using EC2, hosting in some other cloud, or using any other database?

Why would mysql "show global status" query be taking 15 minutes?

Posted: 10 May 2013 05:06 PM PDT

I'm reviewing the slow log, and on one of my slaves the average time for SHOW GLOBAL STATUS is 914s.

Any idea how to determine the cause of this?

Is there a way to export Oracle's UNDO?

Posted: 10 May 2013 06:06 PM PDT

I tried exp utility to dump all database. Looks like this exports only the last version of data skipping undo log. Using flashback queries I see:

01466. 00000 -  "unable to read data - table definition has changed"  *Cause:    Query parsed after tbl (or index) change, and executed             w/old snapshot

What I'm trying to do is to capture db changes, make backup for later use with the ability to flashback to timestamp.

With rman backup I have similar situation:

ORA-01555: snapshot too old: rollback segment number 3 with name "_SYSSMU3_2472002983$"  too small   01555. 00000 -  "snapshot too old: rollback segment number %s with name \"%s\" too small"  *Cause:    rollback records needed by a reader for consistent read are overwritten by other writers`.

Update: I managed to do what I needed only by increasing undo retention and direct copying of data files and control file modification on cloned instance.

What is the difference between int(8) and int(5) in mysql?

Posted: 10 May 2013 11:06 AM PDT

I found out, that if you have a field defined as INT(8) without ZEROFILL it will behave exactly as INT(5)

in both cases the maximum value is

−2,147,483,648 to 2,147,483,647, from −(2^31) to 2^31 − 1

or do i miss something?

I found this Question: http://dba.stackexchange.com/a/370/12923

The (5) represents the display width of the field. From the manual, it states:

The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.

The display width, from what I can tell, can be used to left-pad numbers that are less than the defined width. So 00322, instead of 322. TBH, I've never used it.

But it doesn't affect the storage size of the column. An int will take up more space than a smallint.

so there seems to be no difference then.

MySQL table relations, inheritance or not?

Posted: 10 May 2013 04:06 PM PDT

Im building a micro CMS. Using Mysql as RDMS, and Doctrine ORM for mapping.

I would like to have two types of pages. Static Page, and Blog Page. Static page would have page_url, and page_content stored in database. Blog page would have page_url, but no page_content. Blog would have Posts, Categories...

Lets say I have route like this:

/{pageurl}

This is page, with page url that can be home, or news, or blog... That page can be either Static page, and then I would joust print page_content. But it can also be Blog Page, and then I would print latest posts as content.

How should I relate these Static Page and Blog Page tables? Is this inheritance, since both are pages, with their URL, but they have different content? Should I use inheritance, so that both Static and Blog page extends Page that would have page_url? Or should I made another table page_types and there store information about available page types?

Is there a way to do data export from Amazon Web Services RDS for SQL Server to On-Premises SQL Server?

Posted: 10 May 2013 09:06 PM PDT

We have a large Amazon Web Services RDS for SQL Server instance and we would like to do incremental data transfers from RDS to On-Premesis SQL Server on a regular basis.

The On-prem server will be used to feed information into other systems on an acceptable 1-day delay.

However reading through the docs and searching on google, forums etc, we have not found a seamless way to do off-AWS data transfers using RDS for SQL Server.

Built-in SQL Server functions such as Change Data Capture (CDC) are turned off as well as Replication and off-site Backup/Restore services.

Is there a way to do this or is it a limitation of using RDS?

Cannot GRANT privileges as root

Posted: 10 May 2013 01:07 PM PDT

Because of having some problems, I decided to re-create all users except for root@localhost. This works fine, but the newly created user has no right to do anything. What I want is to simply give all rights to root at some local IP. I (as root@localhost) tried

CREATE USER 'root'@'10.0.3.210';  GRANT ALL ON *.* TO 'root'@'10.0.3.210';

The first command works, the second one fails with the message

ERROR 1045 (28000): Access denied for user 'root'@'localhost'

I don't get why root@localhost can't do everything, I'm sure I didn't mess with its privileges. From

SHOW GRANTS FOR 'root'@'localhost'

I get

Grants for root@localhost  GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO 'root'@'localhost' WITH GRANT OPTION  GRANT ALL PRIVILEGES ON `%`.* TO 'root'@'localhost' WITH GRANT OPTION

whatever this means. Am I missing a needed privilege? Can it be fixed?

I'm working with mysql Ver 14.14 Distrib 5.1.61, for debian-linux-gnu (x86_64).
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog